topic Re: Multiple Facts with same dimensions in QlikView

Multiple Facts with same dimensions

Wed, 25 Nov 2020 16:16:04 GMT

Good morning,

I have an application where the source database contains multiple fact tables that share common dimensions. For example:

Dimensions:

1. Products(ProdCode,Product,PackCode,StockCode,Description)
2.Customer(Customer,Name, Salesperson,Area,Branch,Region)

Facts:

1.Sales(SalesOrder,Salesperson,InvoiceDate,StockCode,Transction_Month) Transction_Year,Amount)
2.Budget(Transction_Month,Transction_Year,StockCode,SalesBudget)
3.CustemerInvoice(Customer,Invoice,InvoiceDate,SalesOrder,CustomerPoNumber,Salesperson)
4.CustomerPayments(Customer, Invoice, Transction_Month,Transction_Year, SalesOrder)

I have tried to use Generic Keys but I cannot get the desired solution, I am not even sure if I am following them quite correctly.

Can you suggest how best to resolve this so that each fact table is correctly associated with just the relevant dimension table?

Thanks

Re: Multiple Facts with same dimensions

neha_shirsath — Wed, 15 Mar 2017 09:13:01 GMT

Hi,

You need to create Link table for facts with composite key and concatenate all the facts in link table and link table will link to your dimension table.

for more understanding can you post some sample data.

Thanks & Regards,

Neha

Re: Multiple Facts with same dimensions

Wed, 15 Mar 2017 10:20:21 GMT

Hi Neha ,

Please find the attached data sample

Thanks

Re: Multiple Facts with same dimensions

annafuksa1 — Wed, 15 Mar 2017 10:26:37 GMT

Try to join tables with each others

Re: Multiple Facts with same dimensions

adamdavi3s — Wed, 15 Mar 2017 10:33:50 GMT

Both of the above answers are correct but you should decide on the best data model structure for your scenario.

Re: Multiple Facts with same dimensions

annafuksa1 — Wed, 15 Mar 2017 10:42:51 GMT

and test the model if you are getting correct information.

Re: Multiple Facts with same dimensions

adamdavi3s — Wed, 15 Mar 2017 10:49:57 GMT

Agreed!

Here is the link model out of interest, this is how I personally would do it anyway!

Re: Multiple Facts with same dimensions

anushree1 — Wed, 15 Mar 2017 11:48:00 GMT

Please find the attached and check this works.

I feel Adam's Model is more precise as its more flexible when new table needs to be added