Aggregate Issue

[138006]

Hi,
I have a peculiar case

There are 2 fact tables. The key of bot the tables is same. Now that key has referential integrity with another master table which has all possible valid combinations of Col1 , Col2, Col3.

Now fact1 has records corresponding to records in the master table for all combinations.

However in fact 2, there are records with the id only for which Col2 is default/null.
Take an example of the Master table, there are 4 rows ,say

Id Col1 Col2 Col3
1 A B C
2 A E C
3 A - C

In fact1 there will be records for all 3 ids. In fact2 there will be record for only id =3.

Now, when I want to see Col1,Col2, Col3 and associated facts I have to join master with fact1.
When I want to see Col1 , Col3 and associated fact I have to join master with fact2.

How to do this. This is not a typical aggregate/summart table case. So am not sure whether Aggregate aware will work or not. If it works, what will be the strategy here? And how to take care of the contexts?

There are a lot of other dimension tables associated with both the facts --how to resolve this issue when those tables will also come in the SQL query?

Please throw some light

 

[sh4kil]

If ID column is the one you use to join the master table to the fact tables then there should not be a problem as it is conformant to both.

It is probably worth converting the Col B NULL value to something like No Value, a meaningful number, etc so Drill Down will work, etc.

When data is combined from multiple SQL staements (due to contexts) then you will get a merged block where some rows will come from Fact Table 1 and others from Fact Table 2.

 

[bertdijks]

Or use 2 different context for each of the two fact tables (and enable the creation of multiple sql's in the options of designer)