dimensional model help

hi, I've been in BI (slq server ssas) for a few years now and have recently come across a project that I need some help on desiging. and that is airline promotions.

an example of an airline promotion is the following:

promotion name: last minute discount
booking date from:1st Jan
booking date from:7th Jan
travel date from: 8th jan
travel date to: 31st jan
discount type: percentage
discount rate: 20%
from airports: ABC/DEF
to airports: GHI/JKL

so this is 1 (simplified) promtion and represents 1 line in a table. my first thought is ok, this is also 1 line is a fact table. however once we started to get user requirements we found that 1 line in a fact table wouldnt work.
for example: how many promotions do i have active on the X of jan or how many promotions do I have leaving from X airport or how many promotions have a travel date of X. these questions could possibly be anserwed with mdx calculations, however I feel it could be modeled to avoid these not so friendly calculations.

1 potention solution is to change the grain of the promotion by "exploding" the data, so 1 line for every combination of the offer. e.g 1 line for booking date 1st, 1 line for booking date 2nd and so on. Dimensions couldn then join directly the fact table.
The problem with this is the fact table will become very large very quick. 1 promotion could turn into more that 1000 lines. and we are talking about 10,000 different promotions which are updated very often (have to track updates).. so potentially could be 20-30million rows a week which is not managable right now.

any suggestions?

thanks

Hi,

did you think of creating another fact table which will store the days on which the promotions will be active, as you will have cases where the promotion is active for a month but not valid for weekends or a public holiday.


thanks

Himanshu

Given the data example you've given, the structure is fine to support any date queries, If you want to count promotions active on 5th of Jan you'd just write a query such as "booking date from" <= 5th Jan AND "booking date to" >= 5th Jan

The issue you have is having multiple airports in your from/to fields. You can solve this by having your fact table reference a bridging table to your airports Dim rather than the way you have modelled it. If you have commonly reusable groups of airports you can also consider predefining bridging table groups.

Hope this helps