Multiple Facts with same dimensions

[#1]

Good morning,

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

Dimensions:

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

Facts:

1. 1.Sales(SalesOrder,Salesperson,InvoiceDate,StockCode,Transction_Month) Transction_Year,Amount)
2. 2.Budget(Transction_Month,Transction_Year,StockCode,SalesBudget)
   3.CustemerInvoice(Customer,Invoice,InvoiceDate,SalesOrder,CustomerPoNumber,Salesperson)
3. 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

[neha_shirsath]

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

[#3]

Hi Neha ,

Please find the attached data sample

Thanks

[annafuksa1]

Try to join tables with each others

[adamdavi3s]

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

[annafuksa1]

and test the model if you are getting correct information.

[adamdavi3s]

Agreed!

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

[anushree1]

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