0

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?

Improve this question
8
  • Things that 1) can be calculated (case when NumberOfRespondents > 100 then 'Yes' else 'No' end) and 2) can change over time are not "facts" and should not be stored.
    – mustaccio
    May 14, 2022 at 11:31
  • Hi mustaccio. That is true for transaction databases. But one of the purposes of an analytical data warehouse is to increase query speed for end users. Queries will be faster if calculation are performed in the ETL process.
    – Mads
    May 14, 2022 at 11:58
  • It's often counterproductive to engage in premature optimization.
    – mustaccio
    May 14, 2022 at 12:22
  • Good point! However, in this case I know the end user will want this information, and the flag will make it easier for them, as they don't need to calculate it by themselves.
    – Mads
    May 14, 2022 at 12:34
  • Have you considered views for presentation?
    – mustaccio
    May 14, 2022 at 13:22

1 Answer 1

Reset to default
0

It's not really an answer, but in lack of good ideas, I decided to put the flag in a dimension table after all.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.