topic Re: Two Fact tables connecting to same dimension tables in QlikView

Two Fact tables connecting to same dimension tables

Mon, 05 Mar 2012 20:44:08 GMT

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

Two Fact tables connecting to same dimension tables

Mon, 05 Mar 2012 21:32:35 GMT

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;

Two Fact tables connecting to same dimension tables

Mon, 05 Mar 2012 23:23:16 GMT

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.

Two Fact tables connecting to same dimension tables

Anonymous — Mon, 05 Mar 2012 23:52:08 GMT

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.

Re: Two Fact tables connecting to same dimension tables

Tue, 24 Apr 2012 18:08:31 GMT

Consider also this document:

QlikView Data Modeling best practices