Joining Multiple Fact Tables

[pparrish]

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.

[ngalemmo]

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.