Line# DD in Transacton Line Fact
3 posters
Page 1 of 1
Line# DD in Transacton Line Fact
After I read Kimball's dimensional modeling book and this article: http://www.kimballgroup.com/html/07dt/KU95PatternsAvoidHeader-LineItem.pdf , it seemed to be pretty straightforward to apply the idea of allocating the header level attributes down to line level fact. I even addressed some questions on the forum based on the papers.
However in one business reality, I have to model order item #, invoice item # that reset on order # or Invoice #, meaning item # is just a sequence number within its order. Obviously the item # is not really the NK for the transaction line and will be highly repeated small integers. So the item NK would be the combination of Order # and item #.
Now I am not sure if this highly repeated item # should be a DD in the item fact. The grain of the fact does not look right. To further complicate the issue, we have an item description attribute that looks like an free-entry text. My feeling is to separate it out into a single column dimension.
I could use the concatenation of item# and item # to cater for a proper item DD, but it's kind of partially redundant field. Any ideas?
However in one business reality, I have to model order item #, invoice item # that reset on order # or Invoice #, meaning item # is just a sequence number within its order. Obviously the item # is not really the NK for the transaction line and will be highly repeated small integers. So the item NK would be the combination of Order # and item #.
Now I am not sure if this highly repeated item # should be a DD in the item fact. The grain of the fact does not look right. To further complicate the issue, we have an item description attribute that looks like an free-entry text. My feeling is to separate it out into a single column dimension.
I could use the concatenation of item# and item # to cater for a proper item DD, but it's kind of partially redundant field. Any ideas?
hang- Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia
Re: Line# DD in Transacton Line Fact
If item number has no meaning in the business other than to provide uniqueness (as all good surrogate keys should) then you don't need it. Even so, I would be inclined to keep it.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Line# DD in Transacton Line Fact
I assume by 'item' you mean 'line'. The idea of storing an order number and order line number as degenerate values in an order line grain fact table (substitute any other document type: invoice, bill of lading, purchase order for 'order') is very common and considered standard practice. The line number itself is needed for handling updates and itegration with other facts (such as combining order facts with shipment facts).
Re: Line# DD in Transacton Line Fact
You are right, ngalemmo. I can see your point in Kimball's case study in his book with order number being repeated as DD on other fact tables like invoice and payment fact. However the line number is not part of the connection. So I guess the link only happens at header level and line number DD in the order fact is only for uniqueness, as B&L said, and including it in the fact table may not have dimensional significance but only provide transaction details for reporting purpose.ngalemmo wrote:The line number itself is needed for handling updates and itegration with other facts (such as combining order facts with shipment facts).
hang- Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia
Re: Line# DD in Transacton Line Fact
It depends on the business. For example, a CPG manufacturer will ship against lines in a order, you cannot rely on SKU because substitutions may occur (same product, different label, for example). So you need order line on the shipment to work back to the order to compare what was ordered and what was shipped.
The same sort of thing happens between purchasing and goods receipt.
The same sort of thing happens between purchasing and goods receipt.
Re: Line# DD in Transacton Line Fact
It makes sense now as we do have both order no and line no in other source transaction tables, such as purchase order, perchase requisition and invoice. So in dimensional store, I should include both order no and line no as DD in other applicable fact tables.
hang- Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia
Similar topics
» Order Line Fact
» Allocating Header Level FACT table Dimensions FKs into Line Level Fact
» A fact table for each service line of business?
» Is it possible to get a distinct order count with a transaction line sales fact table?
» Dear all. I can't figured out how how to linking in my structure the promotion/deal dimensions to the fact table avoiding dupplicates line
» Allocating Header Level FACT table Dimensions FKs into Line Level Fact
» A fact table for each service line of business?
» Is it possible to get a distinct order count with a transaction line sales fact table?
» Dear all. I can't figured out how how to linking in my structure the promotion/deal dimensions to the fact table avoiding dupplicates line
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum