April 10, 2007 at 10:46 pm
I'm developing a data warehouse for a fast moving consumable goods manufacturer. I'm trying to work out where to keep item costs, which can change daily. The options as I see them are:
1. Create a slowly-changing dimension called items, and obsolete rows as the item costs change (inefficient, not slowly changing)
2. Add a fact table just for item costs. Link this to items through a snowflake schema
3. Add dummy rows to the inventory transactions fact table recording the cost of an item at the end of each day
What are your thoughts?
April 12, 2007 at 8:29 am
I have a question about the item cost, even it changes daily, don't you need to keep a history of it, otherwise how do the users know what happen?
April 14, 2007 at 12:59 am
Sorry, that's the point I'm trying to make (but clearly missed). I need to track the item costs historically, and I'm trying to work out the best way to do this.
April 14, 2007 at 11:20 am
There first thing you should consider is the company's method of recognizing costs: standard cost, FIFO, LIFO, etc. Whatever method you chose must work with that.
One approach I have taken is to record the cost on each transaction. For example, if you have a sales table, record the sale price and the item cost. The advantage of this is that it will work with any method, because the complexity of finding the cost can be hidden in the fact table load process.
The following table makes it easy to compute total cost, gross margin, and other cost related items at any level of granularity you need.
create table dbo.T_SALE ( SALE_ID intnot nullidentity(1,1), SALE_DATE datetimenot null, LOCATION_ID intnot null, PRODUCT_ID intnot null, SALE_AMOUNT_UNITS intnot null, SALE_AMOUNT_DOLLARS moneynot null, SALE_COST_DOLLARS moneynot null constraint PK_T_SALE primary key clustered ( SALE_ID ) )
April 15, 2007 at 4:34 pm
Thanks Michael. I've decided to track the item costs in the fact table, and use the ETL scripts to split costs into material/labour/overhead costs.
There is an item cost on the item header in the ERP, so I won't need to do any cost calculation, regardless of the costing method used (even if it changes). I'm going to write a line to the fact table which is for a zero-quantity transaction but shows the cost breakdown of the item at the end of each day. This will cover the situations where there is a change in item cost but no transactions against the item over a few days, and also ensures I get the standard cost as opposed to the current cost or some other doctored cost (which is used on the odd occasion).
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply