Question on how to model price lists

View previous topic View next topic Go down

Question on how to model price lists

Post  kimballmethodologynovice on Tue Aug 27, 2013 10:52 am

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.

kimballmethodologynovice

Posts : 1
Join date : 2013-08-27

View user profile

Back to top Go down

Re: Question on how to model price lists

Post  BoxesAndLines on Wed Aug 28, 2013 9:44 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum