Retail Data Mart - Price Grouping

Hi,

I have created a simple retail data mart. So far it it consiists of:

• F_Sales (Date, Store_ID, Product_ID, Sale_Price, ...)
  
• D_Date
  
• D_Store
  
• D_Product

The grain is line item - the individual item on a receipt. Line items roll up into transactions.
The business have asked for the ability to analyse transactions by predefined price groupings based on the sale price. E.g.

• £0 - £50
  
• £51 -£100
  
• £101 - £200

I am unsure about the best way to achieve this. I am not familiar with all of the dimensional modelling techniques, however my instinct is to create a seperate Price Grouping dimension which will be associated to each line item in the fact table. This should mean that everything will roll up across all dimensions, however technically the grain of the dimension is at a higher level i.e. the transaction level.

I hope this makes sense.

Any tips or pointers gratefully received.

TM

You do want a banding table, with min/max values, some descriptions and such, but you do not want to put FK references to it from the fact.

Bands will change, and change a lot more often than you might think, so typically the association into bands occurs a query time rather than load time. The banding table becomes an auxiliary to the query, rather than a full on dimension. You would join using the values and their relation to the value range on the banding row.

Thanks. This has got me on the right track.

There is alternative approach suggested in on of the blog of Alberto, Ranged Dimension. Have a look it can be used at load time so query time it will be easier to get results.

http://sqlblog.com/blogs/alberto_ferrari/archive/2007/09/06/dimensional-modeling-with-ranged-dimensions.aspx