Let's say there is a data warehouse created from a shop data. A fact is a single purchase of a product. There is a dimension that describes a customer.

There is a need to create a measure that stores a number of distinct customers. Can this measure be created based on customer identifier in the dimension table, or it needs to be fact table? In which cases one or the other solution is better?

Below I post a visualization based on an AdventureWorks2016 database:

Data warehouse example visualization based on a AdventureWorks2016 database.

  • If all you care about is number of customers regardless of what/when/where they bought something, its much cheaper to do it in the dimension table. Commented Apr 26, 2020 at 19:19

1 Answer 1


The problem with contrived examples like the one you provide is that whatever answer you find, it won't be helpful in a real life situation. "A number of distinct customers" is useless as a measure, because it doesn't represent anything a business would care about.

Now, there could be more useful measures, such as "a number of distinct active customers per month", or "a number of distinct customers per geo", or "monthly customer churn", and from their very definitions you immediately see what information is required to compute them.

Your Answer

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

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