Two Fact tables connecting to same dimension tables

[#1]

Hi All,

I have to two fact tables 1)Sales 2)orders and around 10 dimension tables.   The Primary keys fields in the 10 dimension tables are found in both the fact tables. So i when i reload the 10 dimension tables joins to both the fact tables and form  syntectic keys .... how do i overcome these.

Please find attachment

[#2]

Hi,

Try This.

Fact_Sales:
LOAD * INLINE [SalesID,SalesNum,BillID,ShipID
];

Fact_Orders:
LOAD * INLINE [OrdersID,OrdersNum,BillID,ShipID
];

Dimmension_Bill:
LOAD * INLINE [BillID,BillDate,BillQty
];

Dimmension_Ship:
LOAD * INLINE [F1,ShipID,ShipDate
];

Salesfact:
Load AutoNumber(ShipID,   
BillID) As Sales_Bill_ID,
SalesNum,
SalesID,
BillID,
ShipID Resident Fact_Sales;

OrdersFact:
Load AutoNumber(ShipID,   
BillID) As Sales_Bill_ID,
OrdersID,
OrdersNum
  Resident Fact_Orders;

DROP Tables Fact_Sales,Fact_Orders;

[#3]

One way would be to concatenate the 2 fact tables into a single table with a flag field to indicate which record type they are.

[Anonymous]

I agree with Matt and have had to do this a few times.  the problem you have is circular references when the two FACT tables start sharing the same DIM tables.

I use to build one big FACT table with a field identifying whether its a 'Sales' or 'Order' etc then all the other fields brought in as well.  it can get hard and a bit messy to maintain. 

Another way to go (using Matt's technique) is to keep your 2 FACT tables totally separate from each other and the DIM tables then create a smaller lookup table between them.

Then you end up with

• 2 Fact tables both connected to the newly created LookupTable (but not connected to your DIM tables)
• 1 lookup table that contains
•      the primary key for each FACT table
•      an identifier to tell you if its 'Sales' or 'Order'. 
•      all the Dimension keys (ie: dates, order id's etc)
• Your dimension tables.

I know this sounds a little complex but once you get it going it will work fantastic.

[#5]

Consider also this document:

QlikView Data Modeling best practices