"Linking" two Fact tables for Cube users

View previous topic View next topic Go down

"Linking" two Fact tables for Cube users

Post  amir2 on Wed Dec 19, 2012 11:40 am

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?


amir2

Posts : 29
Join date : 2010-07-29

View user profile

Back to top Go down

Re: "Linking" two Fact tables for Cube users

Post  ngalemmo on Wed Dec 19, 2012 12:23 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: "Linking" two Fact tables for Cube users

Post  amir2 on Wed Dec 19, 2012 12:29 pm

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)?


amir2

Posts : 29
Join date : 2010-07-29

View user profile

Back to top Go down

Re: "Linking" two Fact tables for Cube users

Post  ngalemmo on Wed Dec 19, 2012 7:50 pm

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: "Linking" two Fact tables for Cube users

Post  thedude on Thu Dec 20, 2012 5:53 am

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

thedude

Posts : 21
Join date : 2009-02-03
Location : London

View user profile

Back to top Go down

Re: "Linking" two Fact tables for Cube users

Post  amir2 on Thu Dec 20, 2012 12:54 pm

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

amir2

Posts : 29
Join date : 2010-07-29

View user profile

Back to top Go down

Re: "Linking" two Fact tables for Cube users

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum