SSAS / Design Help Please

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

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.