Question on how to model price lists
2 posters
Page 1 of 1
Question on how to model price lists
Thanks in advance for your help. I need to setup the following entities in a dimensional model.
• A Price List can have one or many Price List Items. Currently, there are 10,000 Price List Items.
• The Unit Price and Item Code live at the Price List Item level.
• Each Unit Price can be changed in the source system. On average, 150 Unit Prices are modified daily.
• Need to capture the EOB Unit Price on a daily basis.
• A Price List is tied to either the Partner or Organization level. Not both.
• A Partner has zero or many Organizations beneath it.
• An Organization can be linked to zero or many Partners.
The source system obliterates history each time a Unit Price is modified. I need to preserve Unit Price history so that the operational folks can run pricing analyses for both current state and history.
The operation folks also need to constrain upon Unit Price (where Unit Price > 100) and aggregate Unit Price across dimensions (min, max, average Unit Price).
I’m thinking that I need a rolling daily snapshot table that captures the previous X days as well as a monthly snapshot table that captures month end aggregate Unit Price values prior to the rolling table. The dimensions surrounding this fact table would be Item (item code, item desc.), Organization and Partner. This model does not store Unit Price in a dimension for query constraint purposes.
Do I need to be talked off the ledge or am I on the right track? Any and all feedback is welcome and appreciated.
• A Price List can have one or many Price List Items. Currently, there are 10,000 Price List Items.
• The Unit Price and Item Code live at the Price List Item level.
• Each Unit Price can be changed in the source system. On average, 150 Unit Prices are modified daily.
• Need to capture the EOB Unit Price on a daily basis.
• A Price List is tied to either the Partner or Organization level. Not both.
• A Partner has zero or many Organizations beneath it.
• An Organization can be linked to zero or many Partners.
The source system obliterates history each time a Unit Price is modified. I need to preserve Unit Price history so that the operational folks can run pricing analyses for both current state and history.
The operation folks also need to constrain upon Unit Price (where Unit Price > 100) and aggregate Unit Price across dimensions (min, max, average Unit Price).
I’m thinking that I need a rolling daily snapshot table that captures the previous X days as well as a monthly snapshot table that captures month end aggregate Unit Price values prior to the rolling table. The dimensions surrounding this fact table would be Item (item code, item desc.), Organization and Partner. This model does not store Unit Price in a dimension for query constraint purposes.
Do I need to be talked off the ledge or am I on the right track? Any and all feedback is welcome and appreciated.
kimballmethodologynovice- Posts : 1
Join date : 2013-08-27
Re: Question on how to model price lists
So no metrics means a factless fact table. You are only interested in relationships at the lowest grain, price list items. Assuming Price List has more attributes than just an id used to aggregate items, the Price List would also be a dimension, along with Org and Partner. You are on the right track with a daily snapshot. That fulfills all of your history requirements. A monthly snapshot is not aggregated, its just the last day of the month snapshot. If you want to constrain on unit price, create a banding dimension based on constraint (i.e. reporting requirements) criteria.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Similar topics
» OLTP Dailty Pricing tables: How best to model ceiling, floor and average daily price data?
» Dimensional Model question
» Insurance Claims Model Question
» Bridge table for patient diagnosis
» Question - creating a dimensional model for incident management
» Dimensional Model question
» Insurance Claims Model Question
» Bridge table for patient diagnosis
» Question - creating a dimensional model for incident management
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum