SSAS / Design Help Please

[Juggle]

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

[ngalemmo]

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.