September 19, 2008 at 9:42 am
Hi. I am at the beginning of a data warehouse project for my purchasing organization and am defining my measures and dimensions. I need an item dimension that accounts for different levels of pricing for a single product. A product may have anywhere from 1 to 5 levels of pricing based on a contract tier. My current design has a contract and a tier dimension. I also have a sales fact table that brings in item and sale price, but there's nothing in that data that identifies the tier. Would I need to include tier in my fact table?
In brief, right now I have the following structure, which isn't going to work for what I need...
Dim_Contract
ContractKey
ContractNumber
ContractName
.....
Dim_Tier
TierKey
TierName
TierDescription
...........
Bridge_ContractTier
ContractKey
BridgeKey
Item
ItemKey
ItemMfgProdNum
ItemName
ItemDescription
ItemUOM
ItemUOMPrice
Fact_Sales
Measure_Sales
Measure_Quantity
Key_Purchaser
Key_Vendor
Key_Contract
Key_Item
September 20, 2008 at 8:24 am
Tier is obviously a dimension that should have a key in the Fact_Sales so users can view information based on the tier.
Your saying you need a dim structure that supports multiple prices per item. First off I don't like that idea unless your prices will be forever fixed. You don't want to change the items base price then have to update all the item / contract tier pricing records or if you add a tier have to insert records for every item / new tier combination. An alternative is to store a multiplier that will allow you to determine the price for the given tier / contract.
I'm assuming that what you are storing in the sales_fact is actual price that was determined back in the transactional system. That is the information you will be aggregating. The user simply needs to know what the pricing tier was when that price was determined so they can analyze based on tier/contract. I don't see any need to store the actual price based on tier/contract. You just need to be able to say when this item was sold for xx dollars it was under this contract / tier.
Now if you need to display the price of an item to the user based on a contract / tier then you can calculate it (using the multiplier) when they need to see it.
It's always a balancing act between usability and scalability and performance. Also, think about how the user will access the data and ask your database these questions and see if the data structure supports them.
Please respond with your thoughts on this and add any additional info.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply