"Linking" two Fact tables for Cube users

Hello

I hope I can explain our problem, because I am not sure what terminology I am supposed to use:

We have the following dimensional model:

FactAccountBalances --> DimDate
--> DimAccount
--> DimProduct
&

FactGeneralLedgerBalances --> DimDate
--> DimGeneralLedger
--> DimProduct

DimProduct and DimGeneralLedger, as all of our dimensions, are conformed dimensions.

We have now modified the model as follows: FactAccountBalances --> DimGeneralLedger <-- FactGeneralLedgerBalances. This has enabled us to "link" (what's the correct term?) between the two business processes/events represented by the the two fact tables.

But, when we select a particular product and general ledger and try to display data from the two fact tables, our Cube appears to be performing a cartesian join for all products. In other words, although there is one row with all of the correct data, there are hundreds of other rows too showing the (correct) totals for the measures in these two fact tables.

Has anyone come across this before? What else do I need to add to the model to make it work with (SQL Serve Analysis Services) Cubes?

Yes, this is normal, which is why you do not join fact tables.

You need to first aggregate each fact to the common dimensions, then combine (join or union) on those common dimensions.

ngalemmo

Thanks for your reply. I need something to read with examples to grasp what you are saying.

Is what you mentioned covered in any of the Kimball books (I have all of them)?

It should be discussed in Toolkit. It is a fundamental aspect of using dimensional models.

I would recommend this book - 'Star Schema Complete Reference' by Christopher Adamson.

Thanks for the book recommendation which I have bought. It looks really good and Part II addresses this isses+design.