November 10, 2008 at 10:06 am
This seems like it should be fairly simple, but I think I may be too close to the situation and missing something obvious.
I have a situation where the fact table will contain line item detail from sales orders so each fact will contain a quantity and extended price value for a given item# on a sales order.
The complication comes in knowing how to factor in the "kitting" which takes place. For Kit #ABC includes part numbers #111, #222, #333 and #444. When the kit is sold, all four of those parts will appear in the fact table as separate rows with a Quantity of 1. Those same items can be sold on their own (apart from the kit), in which case another row in the table might exist for part number #111 with a Quantity of 1.
Getting a sum of the number #111 sold works well, but what if I want to get a sum of the number of kit #ABC sold? Anything I do at the dimension end still seems to result in over counting since the quantity is at the individual item level (i.e. components of the kit).
One thought I've had is to have a ItemQuantity field and a separate KitQuantity field. The ItemQuantity field would be treated the same as described above. The KitQuantity field would act much like an allocation whereby the quantity of the kit ordered would be distrubuted among the kit components. In the example above, the KitQuantity would be 0.25 for each of the 4 items making up the kit. Extended price would be handled in a simliar fashion.
Does this approach make sense, or is there a better, more standard approach for dealing with the heirarchy of items/kits while maintaining the intregrity of the quantity and extended price fields?
Thanks!
Steve
April 10, 2009 at 9:41 am
I don't think the ItemQuantity, KitQuantity is a good idea because the same product could be part of multiple kits and you would not be able to distinguish between sales of one kit vs sales of another.
A better approach might be to assign a separate product record for the kit itself and use this in the Fact table instead of expanding the BOM in the Fact table. Your Product dimension would then need to be recursive in order to expand the kit when necessary. This way you can analyze the data either by kits or by lower-level products.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply