Rate Card as minidimension, attribute or fact

View previous topic View next topic Go down

Rate Card as minidimension, attribute or fact

Post  lindascholtz on Mon Nov 07, 2011 12:27 pm

Hi

I am designing a data warehouse for a Marketing department which makes use of data from a Media Agency. On the sample data provided to me there is a rate card column which is an amount. This amount is used in some calculations but sometimes the Media Agency uses a flat rate and then the rate card column will not be used in the calculation.

There are also columns for date, time,program (during which program the advert aired), feature (news/sport),vendor (which channel or newspaper the advert was shown on/in) and these have been defined as dimensions.

The rate card does not change monthly or per program or per features - basically there is no pattern which can be determined. So for the same program,feature,day,month,duration, etc you can have two different rates.

There are currently 186 different rates (amounts ranging from 100 to 130 000) so I think I should stay away from a mini dimension as this will also only have one column? I have also considered adding this to the vendor dimension as an attribute and then making the vendor dimension slowly changing (Type 2) however because the rates change so randomly I cannot use a date range or current version as this wont make sense. So I am unsure if that would be the best approach.

If I do not add the rate card to the dimension then should it indeed be a fact as it cannot be used in aggregation calculations?

Any suggestions would be appreciated.

Thank you!

lindascholtz

Posts : 1
Join date : 2011-11-07

View user profile

Back to top Go down

Re: Rate Card as minidimension, attribute or fact

Post  BoxesAndLines on Mon Nov 07, 2011 2:16 pm

Junk dimension or degenerate dimension seem to apply here.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Rate Card as minidimension, attribute or fact

Post  ngalemmo on Mon Nov 07, 2011 11:57 pm

You are not doing the billing, right? So what do you care? All the DW needs to do is record what happened. You need a dimension for the rate method (rate card, flat, whatever...). The rate charged can be stored as a measure or you can call it a degenerate dimension, either way, it appears as a number in the fact table.

If you are also interested in keeping a history of rate card information, that would be another fact table with rate method as one of the dimensions.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Rate Card as minidimension, attribute or fact

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