I have looked through the entire list of sites, and this is I think the best match. This is not really about database administration, more like database design. Please excuse me and point me to the correct site.
I am designing a database for a rudimentary BI system. At this moment I have hit a wall, which is this (explaining using dummy data):
Suppose my fact table contains this information:
John Doe flew from LAX to ATL on 1 Nov in flight AB-123
The dimensions and their attributes are:
- Flyer - name, club
- Airport - city, code
- Date - year, month, date
- Flight - code, std, delay, price
Now, from this I can easily generate a report like this:
Airport --> LAX DFW ORD ATL Total Gold 50 40 10 25 125 Silver 240 300 95 140 775 Bronze 1000 1500 800 1800 5100 Total 1290 1840 905 1965 6000
Using a query like:
select fd.club, ad.code, count(f1.id) from flyer fd, airport ad, fact1 f1 where fd.id = f1.fid and ad.id = f1.aid and month(f1.date) = 10 group by f1.club, ad.code;
But my problem comes from the fact that the "club" status of a flyer is a moving target. A flyer who is in Gold today could have been in Silver in October, so I am counting him in the incorrect group here. Thus, I imagine I need a separate fact table like this:
John Doe entered Bronze club on 8/15 John Doe entered Silver club on 10/20 ...
"Club" drops out as an attribute of the original flyer dimension. Instead, a new club dimension emerges.
And then to generate the report I need, I join these two fact tables.
Am I on the right track? Or is there an alternative, simpler solution to this? One alternative I could think of is to include the club in the original fact table, handling it during the ETL process. So the fact becomes:
John Doe of Silver Club flew from LAX to ATL on 1 Nov in flight AB-123
Please let me know which approach is better, or if there is a third one.