Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

SSAS / Design Help Please

2 posters

Go down

SSAS / Design Help Please Empty SSAS / Design Help Please

Post  Juggle Tue Feb 26, 2013 5:02 am

I am looking at a datawarehouse design which has sales details which contain some commonality but also differ in some of the details they will contain / measure, currently I am looking at having multiple facts and a simple example is below.

FACT1 - Main order table

FACT2 - contains orders through channel 1

FACT3 - contains orders through channel 2

Fact2 and 3 will hook back in to the main fact 1 table, containing the amalgamated sales details, via the OrderPK/FK link and the supporting facts will contain data which relates only to that particular sales type.

How can I enforce the joins between fact tables in SSAS as I am getting duplication of the Fact1 revenue when querying the FACT2 for a specific call no? Or am going down the wrong route completely here with the design?

Any help would be greatly appreciated.
Thank you


Posts : 1
Join date : 2013-02-21

Back to top Go down

SSAS / Design Help Please Empty Re: SSAS / Design Help Please

Post  ngalemmo Tue Feb 26, 2013 6:19 am

In any dimensional model, relationships between fact tables are many-to-many. Wither you have an FK/PK relationship or not, it is still M:M.

An FK/PK relationship is misleading an a poor design. In a dimensional model different facts are merged based on common dimensions. To eliminate the M:M relationship, both fact tables are aggregated on those common dimensions of interest. This reduces the relationship to 1:1 and the two result sets are combined.

In your design, where you have header/detail types of relationships, the detail fact should have all the dimensions of the header fact. In other words, dump the ORDERPK/FK. Facts 2 & 3 should contain customer, supplier and date FKs.

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

Back to top Go down

Back to top

- Similar topics

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