Rate Card as minidimension, attribute or fact

[lindascholtz]

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!

[BoxesAndLines]

Junk dimension or degenerate dimension seem to apply here.

[ngalemmo]

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.

[Sponsored content]