Retail Data Mart - Price Grouping

View previous topic View next topic Go down

Retail Data Mart - Price Grouping

Post  technomorph on Tue Apr 23, 2013 12:02 pm

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




technomorph

Posts : 2
Join date : 2013-04-23

View user profile

Back to top Go down

Re: Retail Data Mart - Price Grouping

Post  ngalemmo on Tue Apr 23, 2013 2:29 pm

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Retail Data Mart - Price Grouping

Post  technomorph on Wed Apr 24, 2013 4:35 am

Thanks. This has got me on the right track.

technomorph

Posts : 2
Join date : 2013-04-23

View user profile

Back to top Go down

Ranged dimension

Post  sirfnet on Sun Apr 28, 2013 12:08 am

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


sirfnet

Posts : 1
Join date : 2013-04-28

View user profile

Back to top Go down

Re: Retail Data Mart - Price Grouping

Post  Sponsored content


Sponsored content


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