Item Costs - Dimension or Fact

For each item we have a few different cost types - frozen, standard, pending,...
In each pair (item,cost type) there are different cost buckets - material cost, material overhead, labour cost, overheads, outside operation costs, etc.
Some of the cost types are updated monthly, some yearly.
It is very hard to me to determine whetrher item costs and item costs per bucket are dimensions or facts.
I appreciate any suggestions about it!

Thanks,
Lachezar

I think Cost Types could be a part of a core dimension or a mini-dimension on its own. As of cost buckets,
if they are standard/constant values across various transaction/order lines, then you could have them in a cost bucket
dimension. (If one cost type maps on to one cost bucket, then probably they could fit in to the same dimension.) However, at the same time, the individual values would be part of the fact table as well - as you may want to aggregate these results or add in soe intelligence behind these measures.

I am not sure that I have clear explained what cost type and cost buckets mean. Let me give an example.
We have the following:
cost types - freezed (yearly update), standard (monthly update), pending (daily update)
cost buckets - material cost (MAT), labour cost (LB), overhead cost (OVR)
Item cost = Sum of cost buckets.
for the item A there are:
freezed cost - 100 USD (MAT - 80, LB - 15, OVR - 5)
standard cost - 120 USD (MAT - 90, LB - 20, OVR - 10)
pending cost - 110 USD (MAT - 85, LB - 20, OVR - 5)

In all inventory transactions I have only the item number and for the report purposes I have to now in the time for each transaction the item cost according to the different cost types.

My suggestion is to have the following snow flake model:

Item Dimmension (Item Number, Item Name, ...., Valid From, Valid To)
Item Cost Dimension (Item Number, Cost Type, Cost Amount, Valid From, Valid To)
Item Cost Bucket Dimension(Item Number, Cost Type, Cost Bucket, Cost Amount, Valid From, Valid To)

And because the Cost Amount per Cost Type is a sum from the cost buckets this both dimensions have to be synchronised per "Valid From" "Valid To" fields.

So, If I have inventory transaction fact, I can have in it only the key to the item number and based on it I can determine the different item costs.

Do you have any other suggestions?

I might be mis-interpreting your question, but this is what I would do.
If you always have just the three buckets (Material, Labour and Overhead), you can just include the cost of each in the Cost Type, creating a whole new dimension for the buckets would only complicate things.

You have the following dimensions:

DimItem(ItemKey, Item Number, ItemName, CostTypeKey, ..)
DimCostType(CostTypeKey, CostType, Cost Type Name, MaterialCost, LabourCost, OverHeadCost)

Itemkey and CostTypeKey would be integer surrogate keys (if ItemNumber and CostType are already surrogate keys you could use those).

Still I have the design problem - is the item cost dimension or fact?

The number of cost buckets is variable. I couldn't put them as attributes to the cost type dimension.
The number of cost types is variable as well . I couldn't put them as attributes to the item dimension.

It means that I have the relation chain item 1-N item cost types 1-N item cost buckets. Is each of them a dimension?

On the other site the item cost is included in the queries as a measure not as a filter.

Although at the beginning I was sure that item cost and item buckets are dimensions, now I think that they are facts.

Please advice

When you say the number of costs is variable, how many different attributes would you need to hold? Also, do you want to be able to report the costs accurately at a point in time? If the latter, I would put the costs on the fact table - we do this with our sales information. The sales invoice line fact table holds the various components of item cost plus the price the item was sold at. This enables us to work out the cost of sales, margin etc at the time of the sale. The item dimension holds the current selling price and cost price details in case business users what to do some comparison analysis.

Unless you are going to end up with hundreds of columns on you tables, I would put all attributes on the fact (and maybe dimension table) as separate columns and zero fill the cost columns that aren't relevant.

Cost is a fact. All of the other stuff would go into a dimension table.

If you needed to know the cost of each item at any given time, then you would create a fact table with one measure - cost. If you needed to know the cost of each item in a sale, then create a sales fact table with the measures "cost" and "quantity" where "cost" might be considered a deginerate dimension.

I can understand the confusion - cost is a measure or fact in a fact table that has a list of every product. You can use the "cost" to calculate things like the average costs. But it's also a descriptor in a transaction fact table, where summing the unit cost wouldn't tell you much and could provide incorrect information.

Having cost as a fact I would have the following structure:

(Item Key, Item Cost Type, Cost)

, where the Item Key is a SK to the Item Dimension . But in our case the Item Dimension is a SCD Type 2. It means that if some item attributes would be changed a new record in the item dimension would be created with new SK and new period of validity.
What would happen with the Cost Fact?
Do I need to create a new Cost Fact with the new Item Key or I need to create a new Cost Fact only when the cost was changed?

Nothing happens to the fact. Your fact represents that event as it occurred at that point in time. If you want your fact row to represent current state, you will need to add additional relationships from your dimension to your fact table. This is a common "enhancement" required to the dimensional model. That is, a change occurs in a SCD but no event or transaction has occurred to reference the new SCD row.

I am not sure I understand you.
Let me give you an more detail example:

1. There is an Item Cost Fact:
(Item Key1, Item Cost Type, Cost)

2. A few days after creation of this fact some attributes in Item Dimension are changed and because of SCD Type 2 there is a new SK
Item Key 2

3. After that an Invoice Line Fact is created:
(Invoice Number, Customer Key, Item Key 2, ....., Net Amount)

How can I determine the item cost which is actual when the invoice fact is created?

Probably it is better to design the Item Cost as a dimension and to connect this dimension to Invoice Line Fact...





There is a Invoice Line Fact

I can see why you would like to have your cost information as SCD2 attributes in your Item Dimension--that way the Item Dimension record that is current when an Invoice Line Fact record is created would always contain the cost information that is applicable at that time. Let's see if we can achieve that same functionality while still treating the cost information as a measure/fact.

You should probably create a Durrable Surrogate Key (DSK) for your Item Dimension in addition to the standard SK. The DSK stays constant even as the SK changes due to SCD2 changes. (A common implementation is to use the first SK for an Item as the DSK.)

Your Invoice Line Fact table would then refer to the current Item SK as expected. Your Cost Fact table, however, would always refer to the DSK. That "decouples" the cost facts from any SCD2 attribute changes that go on in the Item Dimension.

However, costs do change over time, so how do we handle that? Two options:

(Opt 1.) If costs change on a rigid schedule, make costs a periodic snapshot fact table. It would include a key based on the period (ex: MonthKey) which should exist in the Date Dimension. For any given date in your Invoice Line Fact table, you can then determine the period key (ex: MonthKey) from the date dimension and thus find the cost information that was relevant for that item at that time.

(Opt 2.) If cost changes are a little less regular, make your Item Cost Fact table a Type2 fact table. Add BEGIN_EFFECTIVE_DATE and END_EFFECTIVE_DATE fields to the Item Cost Fact table. The most current fact record (for a given cost type for a given item) would always have a far future high date in the END_EFFECTIVE_DATE field. When a cost changes, you create a new cost fact record and update the previously current record so its END_EFFECTIVE_DATE matches the BEGIN_EFFECTIVE_DATE of the new record.

With this design individual costs for an item can change independently over time, yet you will always be able get all the costs for an item on a given date (such as the date of a Invoice Line Fact record) by doing a query like this:

WHERE Item DSK = 1234
AND #target_date# >= BEGIN_EFFECTIVE_DATE
AND #target_date# < END_EFFECTIVE_DATE

Note the use of the Item Dimension Durrable Surrogate Key (DSK) to refer to the item. You will have this available in the Item Dimension once you join the Invoice Line Fact record to the Item Dimension using the regular Item SK.

(Assuming that (a.) that you don't need to track mid-day price changes and (b.) your Date Dimension SK is an inceasing integer, it would also be possible to implement this design using your Date Dimension SKs instead of database date/time fields.)

It is what I need ! Thanks a lot VHF!