Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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:
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.
