topic Re: Fact Table Dependency in QlikView

Fact Table Dependency

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Fact Table Dependency

vinieme12 — Sat, 28 Jan 2017 07:08:30 GMT

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

Re: Fact Table Dependency

Anonymous — Tue, 31 Jan 2017 00:17:43 GMT

How can I attach the excel file?

Re: Fact Table Dependency

vinieme12 — Tue, 31 Jan 2017 05:43:33 GMT

Here's a how to

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

Re: Fact Table Dependency

sdmech81 — Tue, 31 Jan 2017 06:10:55 GMT

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

Re: Fact Table Dependency

jonathandienst — Tue, 31 Jan 2017 06:33:15 GMT

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)

Re: Fact Table Dependency

sdmech81 — Tue, 31 Jan 2017 07:42:08 GMT

Hey Sir,

Wont that concatenation spoils the relation btn 2 table..

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

Re: Fact Table Dependency

Anonymous — Tue, 31 Jan 2017 15:24:06 GMT

Finally I could send you the data.

Thanks in advance!

Re: Fact Table Dependency

Anonymous — Tue, 31 Jan 2017 15:33:00 GMT

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.

Re: Fact Table Dependency

Fri, 11 Aug 2017 09:42:35 GMT

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".

Re: Fact Table Dependency

Anonymous — Fri, 11 Aug 2017 14:21:15 GMT

Hola Sofía

Finalmente lo que hice fue una link table entre las tablas order, invoice y sales. Pero el problema ahora era el tema de las fechas ya que cada tabla de hechos tiene una fecha diferente (fecha de la orden, fecha de la factura, fecha de la venta).

Después de leer muchos foros, me tocó trabajar con un Master Calendar pero también dejar Calendarios particulares para cada tabla de hechos.

Espero que también hables español, jajajaja... sino me cuentas.

(I hope you speak spanish, if you dont, please reply and i will translate)

Re: Fact Table Dependency

Mon, 14 Aug 2017 07:15:25 GMT

Hola Fernando,

creo que nuestros nombres y apellidos nos delatan .

yo aun estoy a vueltas con mi solución, pues aun no lo tengo claro.

Por si quieres echarle un ojo a mi pregunta y comentar:

Concatenation of tables with different types of facts

Re: Fact Table Dependency

Anonymous — Mon, 14 Aug 2017 17:54:26 GMT

Hola Sofía

Jajajaja si... El modelo que yo estaba haciendo finalmente lo hice con un link table para poder relacionar las 3 tablas de hechos, aunque, como te dije, me tocó dejar 3 calendarios (1 para cada tabla de hechos). Si quieres me puedes enviar el .qvw con unos pocos datos para mirar mejor lo que estás haciendo a ver cómo te puedo ayudar.

cesar.rivadeneira@carvajal.com