Joining Multiple Fact Tables
2 posters
Page 1 of 1
Joining Multiple Fact Tables
I have 3 Fact Tables that I would like to join into 1.
1. Billing Header (has aggregate facts at the invoice level which I will roll up using the child fact table not from the Header)
(Keys = division, type, invoice, invoice_line)
2 Billing Line (Keys = division, type, invoice, invoice_line) This also includes a delivery and shipment number incase there are split shipments on an order. Has Invoice Amt
3. Billing Expense ((Keys = division, type, invoice, exp_line) exp_amt (ie. freight charges)
Question:
1. How to model this into 1 Fact Table?
2. I thought about joining the Billing Line and Exp Line with a Union ????
or
3. I can load with an update
Problem with this approach:
4. I tried to use an ETL tool to left outer join to the Header but that creates a duplicate key issue because the Billing Line Table and the Exp Table can contain the exact same keys.
1. Billing Header (has aggregate facts at the invoice level which I will roll up using the child fact table not from the Header)
(Keys = division, type, invoice, invoice_line)
2 Billing Line (Keys = division, type, invoice, invoice_line) This also includes a delivery and shipment number incase there are split shipments on an order. Has Invoice Amt
3. Billing Expense ((Keys = division, type, invoice, exp_line) exp_amt (ie. freight charges)
Question:
1. How to model this into 1 Fact Table?
2. I thought about joining the Billing Line and Exp Line with a Union ????
or
3. I can load with an update
Problem with this approach:
4. I tried to use an ETL tool to left outer join to the Header but that creates a duplicate key issue because the Billing Line Table and the Exp Table can contain the exact same keys.
pparrish- Posts : 1
Join date : 2012-10-24
Re: Joining Multiple Fact Tables
Usually, when you implement invoicing, there is one line level fact, and possibly a second line/account level fact if there is some form of accounting distribution that takes place for each line.
If 'expenses' are simply additional charges tacked onto the invoice, they usually appear as rows in the line table with appropriate 'products' that represent the charges.
If 'expenses' are simply additional charges tacked onto the invoice, they usually appear as rows in the line table with appropriate 'products' that represent the charges.
Similar topics
» Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables
» Joining fact tables
» DegenerateDimension joining Fact tables
» Joining fact tables
» Joining Fact tables
» Joining fact tables
» DegenerateDimension joining Fact tables
» Joining fact tables
» Joining Fact tables
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum