Joining Multiple Fact Tables

View previous topic View next topic Go down

Joining Multiple Fact Tables

Post  pparrish on Wed Oct 24, 2012 11:11 am

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.

pparrish

Posts : 1
Join date : 2012-10-24

View user profile

Back to top Go down

Re: Joining Multiple Fact Tables

Post  ngalemmo on Thu Oct 25, 2012 5:51 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum