modeling invoice_shipment fact table

We currently have an invoice_detail fact table which is at the line item (part) detail:

invoice_detail_fact

vendor_dim_id (FK)
channel_dim_id (FK)
part_dim_id (FK)
customer_dim_id (FK)
location_dim_id (FK)
invoice_date (FK)
invoice_id (degenerative attribute)
shipped_qty
sale_price_amt
cost_amt

Now, there is a need to report on the shipping data for that invoice. There may be 1 or more boxes required for each invoice. Each box will have height, weight, size, and fees associated with it.

I think there will be a need to link the invoice_box facts back to the invoice facts. I understand that these are at different grains; the invoice_detail is at the part level, the invoice_box is at the box level. Both are related to an invoice.

Do I create an invoice_header dimension and then an invoice_header fact so that I can link invoice sales data with invoice box shipping data?

Thanks in advance for any ideas! Kim

I would not create either an invoice_header dimension or an invoice_header fact.

It sounds like you need a "box_shipped" fact. This could have the same invoice_id (degenerate dimension) as your existing fact table. You could then drill across fact tables by using the invoice_id (degenerate dimension).

This satisfies the best practice of always using a dimension attribute to join two fact tables... in this case it is a degenerate dimension attribute.

Do I create an invoice_header dimension and then an invoice_header fact so that I can link invoice sales data with invoice box shipping data?

No. Just have common dimensions. Invoice_ID, Part_Dim_ID, etc...

You don't "link" fact tables. You combine them, if necessary, when you report. Facts are combined by summarizing each fact along common dimensions and joining the summarized sets.

[quote="ngalemmo"]

You don't "link" fact tables. You combine them, if necessary, when you report. Facts are combined by summarizing each fact along common dimensions and joining the summarized sets.

Thanks for clarifying that point... even with a common degenerate dimension you can't do a simple SQL JOIN on two fact tables, or you'll end up with a cartesian product. You need to have two subqueries which aggregate up to the same grain, then (as stated above) join the two subqueries.

Thanks for your input. I guess what I am still unsure about is that for the invoice box shipping data, I really only have an invoice_id, a box_id, and a date for "dimensional" data to view the measures (size, fees).

So, typically the primary key of the fact tables is a set of dimensions. Is it acceptable to use a degenerative id such as invoice_id, box_id as part of the primary key and only have one true dimension (that being the date)?

Yes. Your dimensions are what they are.