Imagine we have received the results of a health survey on daily consumption habits of 3 different items, like the following:
Id | Date | Age | Country | CigarettesPerDay | CoffeesPerDay | BeersPerDay |
---|---|---|---|---|---|---|
1 | 2021-12-31 | 35 | US | 0 | 3 | 0 |
2 | 2021-12-31 | 22 | US | 5 | 5 | 1 |
3 | 2021-12-31 | 53 | US | 3 | 4 | 0 |
... | ... | ... | ... | ... | ... | |
11276 | 2021-12-31 | 44 | France | 3 | 4 | 0 |
I want to model this in a star schema model. In the fact table, I create foreign key relationships to date and item dimensions as well as a demographics dimension with country and age. I then sum up the number of respondents pr. demograhic group. If the number of respondents is above 100, I mark the group as being representative of the population. Finally I calculate the total and average consumption for each group.
DateId | ItemId | DemographicId | NumberOfRespondents | IsRepresentative | TotalConsumption | AverageConsumption |
---|---|---|---|---|---|---|
20211231 | 1 | 1 | 70 | No | 280 | 4 |
20211231 | 1 | 2 | 150 | Yes | 750 | 5 |
20211231 | 1 | 3 | 220 | Yes | 660 | 3 |
... | ... | ... | ... | ... | ... | |
20211231 | 3 | 1000 | 1 | No | 0 | 0 |
For instance, there was 70 respondents from demographic 1 (e.g. country = US, age = 18). They have on average consumed 4 of item 1 (e.g. cigarettes).
Generally we should strive to hold only facts and foreign keys in the fact table. However I personally don't think that a seperate dimension for the boolean flag provides any value. Can this flag be considered a generate dimension, or is it considered bad design to have it in the fact table?
case when NumberOfRespondents > 100 then 'Yes' else 'No' end
) and 2) can change over time are not "facts" and should not be stored.