Fact Table Dependency

[Anonymous]

Hi everybody..

I've been working on a data model with 3 fact tables: Sales, Invoices and Purchase Orders. My model is connecting the 3 fact tables with a LinkTable using their common fields (Time_Id, Manufacturer_Id, Retailer_Id, Product_Id). So far so good, but there is a dependency between Invoices Fact Table and Purcharse Orders Fact Table because an invoice is related with a purchase order and one purchase order can have none, one or many invoices. I dont know how to model this without creating some "Frankestein" (Synthetic Key, Circular Reference, etc). Any ideas?

More or less my fact tables are described like this:

INVOICES:

Invoice_Id

, Time_Id

, Retailer_id

, Manufacturer_Id

, Product_Id

, Purchase_Order_Id

, Invoice_Number

, Invoice_Date

, Invoice_Quantity

, Invoice_Unit_Value;

PURCHASE_ORDERS:

Purchase_Order_Id

, Time_Id

, Retailer_id

, Manufacturer_Id

, Product_Id

, Purchase_Order_Number

, Purchase_Order_Date

, Order_Quantity

, Order_Unit_Value;

SALES:

Time_Id

, Retailer_id

, Manufacturer_Id

, Product_Id

, Sales_Quantity

, Sales_Unit_Value;

NOTE: Time, Retailer, Manufacturer, Product are my dimensions.

[vinieme12]

Can you upload some dummy data in excel for all 3 fact tables  that best respresents your scenario?

[Anonymous]

Hi

How can I attach the excel file?

[vinieme12]

Here's a how to

https://community.qlik.com/docs/DOC-16454

[sdmech81]

HI,If tables are depended on each other then do join them like and try to create single fact,

PURCHASE_ORDERS

Purchase_Order_Id,

Rest of fields

left join

Invoice Tabel

Purchase_Order_Id,

Rest of fields

[jonathandienst]

Your best bet here is to concatenate the data into a single table with a 'source' derived key to differentiate:

Data:

LOAD

  Invoice_Id

  , Time_Id

  , Retailer_id

  , Manufacturer_Id

  , Product_Id

  , Purchase_Order_Id

  , Invoice_Number

  , Invoice_Date

  , Invoice_Quantity

  , Invoice_Unit_Value

  , 'Invoices' as Source

FROM INVOICES;

Concatenate(Data)

LOAD 

  Purchase_Order_Id

  , Time_Id

  , Retailer_id

  , Manufacturer_Id

  , Product_Id

  , Purchase_Order_Number

  , Purchase_Order_Date

  , Order_Quantity

  , Order_Unit_Value

  , 'Orders' as Source

FROM PURCHASE_ORDERS;

Concatenate(Data)

LOAD

  Time_Id

  , Retailer_id

  , Manufacturer_Id

  , Product_Id

  , Sales_Quantity

  , Sales_Unit_Value

  , 'Sales' as Source

FROM SALES;

This makes analysis simpler and avoids a link table which can perform poorly if your data set it large. You can use simple set expressions to select which data is required - eg:

=Sum({<Source = {'Sales'}>} Sales_Quantity)

[sdmech81]

Hey Sir,

Wont that concatenation spoils the relation btn 2 table..

Bcs even with flag also they ll act as 2 separate entities ryt?

[Anonymous]

Hi

Finally I could send you the data.

Thanks in advance!

[Anonymous]

Thanks for your answer. But what if I want to analyse how many units where ordered (Purchase Order) and how many units were bought (Invoice) using the Purchase_Order_Number dimension? I'm getting value for units ordered but zero for units in invoices in spite of there are units related to an order number.

[#10]

Hello Fernando,

have you found a solution for your Question?

I have in my Model that Problem too. I have 3 fact Tables und they are related.

What i did put in your Example is:

The Problem is that Invoice, Orders and sales are related and i don't know how to answer:

How many sales have generated orders ordered in April.

I mean, i don't know how can i relate the different "sources".