Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Retail Data Mart - Price Grouping

3 posters

Go down

Retail Data Mart - Price Grouping Empty Retail Data Mart - Price Grouping

Post  technomorph 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

Back to top Go down

Retail Data Mart - Price Grouping Empty Re: Retail Data Mart - Price Grouping

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

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

http://aginity.com

Back to top Go down

Retail Data Mart - Price Grouping Empty Re: Retail Data Mart - Price Grouping

Post  technomorph Wed Apr 24, 2013 4:35 am

Thanks. This has got me on the right track.

technomorph

Posts : 2
Join date : 2013-04-23

Back to top Go down

Retail Data Mart - Price Grouping Empty Ranged dimension

Post  sirfnet 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

Back to top Go down

Retail Data Mart - Price Grouping Empty Re: Retail Data Mart - Price Grouping

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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