About Syn

[#1]

Hi All,

Could your experts explain what is the following meaning?

$Syn 1 = LocaleId+TimeId
$Syn 2 = LocaleId+OrderId+TimeId
$Syn 3 = LocaleId+MemberId+OrderId+TimeId
$Syn 4 = $Syn 1+$Syn 2
$Syn 5 = $Syn 1+$Syn 2+$Syn 3
$Syn 6 = $Syn 4+$Syn 5

I was wondering why there exists syn4,5,6 .

Thanks.

[prieper]

In general it is advisable, not to tolerate synthetic keys but to replace & control the links between tables. Otherwise you always run into danger to have "strange" results and/or memory-problems due to cartesian products created.

HTH
Peter

[#3]

synthetic keys 4, 5, and 6 were created to handle overlaps between the previous synthetics.

ie: Synthetic 1 contains LocaleId and TimeId; Synthetic 2 contains these two and OrderId. Therefore another synthetic needs to be created (Syntheitc 4 in this case) to resolve the linking.

Just so you're aware, a document with this kind of linking schema will not do what you expect, even though the script appears to be running correctly to completion, making a selection on any of these tables will have results that are difficult to predict or understand.

In other words, these synthetics need to be addressed prior to further development.

[#4]

Hi Peter,

I always get the strange results when used syn key.

But how can I maintain a link table?

I have the following scenario,

Order:
load * Inline
[LocaleId,MemberId,OrderId,TimeId
1,1,1,1
1,2,2,1
];
Revenue:
load * Inline
[LocaleId,MemberId,OrderId,TimeId,Amount
1,1,1,1,30
];
ChargeTimes:
load * Inline
[LocaleId,OrderId,TimeId,ChargeTimes
1,1,1,1
1,2,1,0
];
Track:
load * Inline
[LocaleId,TimeId,Visits
1,4,10
2,2,10
3,1,10
];

You will find orders connects revenue with syn(LocaleId,MemberId,OrderId)

ChargeTimes uses syn(LocaleId,OrderId,TimeId)

Track uses syn(LocaleId,TimeId)

But after I load data into qvw, the results are not what I expected.

I use LocaleId as the selection: it has 1,2,3. three options.

But when I choose 1,2,3 and use a text to show:sum(Visits), it always gives me 0. But if I don't choose anything, Clear the selection, it will show the right num 30.

I don't know why, I really would like you to explain it and show me how do they join.

What I can do?

Isaac Li

[prieper · Accepted solution]

Hi Isaac,

not easy to solve, as I do not quite understand which data are behind.

You may simply concatenate the various data and build your formulas accordingly.

I tend to build a kind of backbone, which might be your orders table and then to assign the events to each of the orders.

HTH
Peter

[Oleg_Troyansky]

It looks like Orders and Revenue are at the same level of detail, and therefore they can be easily combined into a single table using Joining. This will simplify your structure a lot.

The rest can be solved with a concatenated Fact table, as Peter suggested, or, if you want to take a chance and tolerate the remaining Synthetic keys, you can try and leave the data structure as is.