modeling invoice_shipment fact table

View previous topic View next topic Go down

modeling invoice_shipment fact table

Post  kjfischer on Thu Aug 25, 2011 10:25 am

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

kjfischer

Posts : 28
Join date : 2011-05-04

View user profile

Back to top Go down

Re: modeling invoice_shipment fact table

Post  VHF on Thu Aug 25, 2011 10:46 am

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.


Last edited by VHF on Thu Aug 25, 2011 10:46 am; edited 1 time in total (Reason for editing : typo)

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: modeling invoice_shipment fact table

Post  ngalemmo on Thu Aug 25, 2011 10:49 am

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

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

View user profile http://aginity.com

Back to top Go down

Re: modeling invoice_shipment fact table

Post  VHF on Thu Aug 25, 2011 11:15 am

[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.

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: modeling invoice_shipment fact table

Post  kjfischer on Thu Aug 25, 2011 11:27 am

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)?

kjfischer

Posts : 28
Join date : 2011-05-04

View user profile

Back to top Go down

Re: modeling invoice_shipment fact table

Post  ngalemmo on Thu Aug 25, 2011 12:02 pm

Yes. Your dimensions are what they are.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: modeling invoice_shipment fact table

Post  Sponsored content


Sponsored content


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