Line# DD in Transacton Line Fact

View previous topic View next topic Go down

Line# DD in Transacton Line Fact

Post  hang on Wed Oct 26, 2011 1:58 am

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?

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Line# DD in Transacton Line Fact

Post  BoxesAndLines on Wed Oct 26, 2011 9:09 am

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Line# DD in Transacton Line Fact

Post  ngalemmo on Wed Oct 26, 2011 10:42 am

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

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

View user profile http://aginity.com

Back to top Go down

Re: Line# DD in Transacton Line Fact

Post  hang on Wed Oct 26, 2011 4:48 pm

ngalemmo wrote:The line number itself is needed for handling updates and itegration with other facts (such as combining order facts with shipment facts).
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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Line# DD in Transacton Line Fact

Post  ngalemmo on Wed Oct 26, 2011 4:59 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Line# DD in Transacton Line Fact

Post  hang on Wed Oct 26, 2011 7:13 pm

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

View user profile

Back to top Go down

Re: Line# DD in Transacton Line Fact

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