SSAS / Design Help Please

View previous topic View next topic Go down

SSAS / Design Help Please

Post  Juggle on Tue Feb 26, 2013 5:02 am

Hi,
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
ORDERPK|CUSTOMERFK|SUPPLIERFK| DATEFK|revenue|cost

FACT2 - contains orders through channel 1
CALLPK |CALLNO|Duration | TELNOFK| ORDERFK

FACT3 - contains orders through channel 2
INPERSONPK|LOCATIONSFK|Distance| Frequency|ORDERFK


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

Juggle

Posts : 1
Join date : 2013-02-21

View user profile

Back to top Go down

Re: SSAS / Design Help Please

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

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

View user profile http://aginity.com

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