In a data warehouse, should a measure be based on a fact or a dimension

data-warehousedimensional-modelingolap

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:

Best Answer

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.

Related Solutions

Does surrogate key assignment for a fact table require that the source data has natural keys

I'm not sure that you need "natural" keys, but you probably do need to maintain a key mapping of sorts. So you need to understand what relationships map between your source and target systems, identify the keys for those relationships and build your key mappings from there.

I had a previous question on this called "What is the best practice for mapping from natural keys to integer-based keys? (ETL)".

EDIT: So far I am seeing at least three, if not four mappings.
CustomersToDim_Customers (customer_id, dim_customer_id)
ProductsToDim_Products (product_id, dim_product_id)
OrderDatesToDim_Date (order_date, date_id) or (map_id,order_date,date_id) if you want to use a key to map.
And lastly, I see the order_id as your key to the fact table. So I would go
OrdersToFactOrders (order_id,dim_date_id,dim_customer_id,dim_product_id)
In my case I renamed the fields for the mart with dim_field_id because I didn't want name collision within my tables or confusion as to which Id they pointed to. Your ETL would have to know that CustomersToDim_Customers.dim_customer_id really maps to Dim_Customers.customer_id and that CustomersToDim_Customers.customer_id really maps to Customers.customer_id.

I would also be half-inclined to include the order_number in the OrdersToFactOrders mapping table, but that is because I like to have tracking data for audit purposes. Makes my life easier. But, based on what you told me, order_number and order_id are one-to-one so the inclusion of order_number would be redundant then and only necessary if you have a perfectionist paranoia to make sure your data is correct on both sides (I really like to make sure that A on side A and B on side B are really correct after the ETL is done.).

Sql-server – Dealing with measure groups of different granularity

If you want to see measures from two different fact tables side by side but different granularity, it's possible but not perfect. For example, the measure for purchase amount in FactPurchase has no monthly equivalent.

To do this, it helps to have as many related dimensions between both fact tables. So if you have a dimension such as property type that is related to FactPurchase, I would make that a dimension on FactMonthlyStatus as well. This can be done by adding these to the actual FactMonthlyStatus table and figuring them out in your ETL process or by creating a view that sits on top of FactMonthlyStatus that does a join to lookup these dimensions as they are loaded into your cube. However, you are still going to have dimensions such as month that are at a lower level of granularity than FactPurchase.

The main thing to figure out is how your measures in both fact tables relate to each other. Are you attempting to keep a running total for rent paid and see how it relates to what you paid for the property? If that's the case, it might make sense to create a cube based off a single view that combined the FactPurchase with an aggregate of FactMonthlyStatus to see how they compare.

Best Answer

Related Solutions

Does surrogate key assignment for a fact table require that the source data has natural keys

Sql-server – Dealing with measure groups of different granularity

Related Question