Search code examples
data-warehousedimensionsfact-tabledatamart

Should the "count" measure be stored in the fact table?

I have a fact table that includes "wait times in hours" for certain services. I have a lot of dimensions that could describe the wait-times based on different slices; however, I am also interested in knowing how many people (counts) came for services through the filters of the same dimensions.

Given the dimensions for both the wait-times in hours and the number of people who got services are exactly the same, I think it's best practice to keep it in the same fact table. My question is:

  1. Should there be a different fact table for the count measure mentioned?
  2. How would I include this measure? Do I just put 1 in every single row? Because regardless of the wait-time, they've gotten the service only once (you cannot go above/below 1 in my scenario).
Solution

  • 1) Think about the grain of your existing fact table. It sounds like it's probably "an occasion on which a person received a service." If that's the same thing you're trying to count, then yes - the waiting time and the count are the same grain.

    However, while they may well be the same grain, there might be no need to add anything to the table. Read point 2 for an explanation.

    2) You could put a 1 in a column on every row, but I'm not sure what you'd gain from it. You've not said what tools will be consuming this data, but you should be able to do a count/distinct count of some kind.

    Working on the basis that you've tagged SSIS so are likely using Microsoft's BI stack:

    • TSQL has count(), and you can do count(distinct [column]).
    • SSAS has both counts and distinct counts as aggregation types.
    • MDX offers several different types of count.
    • SSRS has Count, CountDistinct, and CountRows.

    Whether you do a normal count or a distinct count will depend on whether you're trying to ask "How many people used this service?" or "How many different people used this service?"