Imagine we have received the results of a health survey on daily consumption habits of 3 different items, like the following:
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.
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.