I work at a rental company with multiple shop fronts (branches) in different areas.
Each individual thing that is rented out is one product and there is a product hierarchy. Let's say category, subcategory, product.
We also have a geographical hierarchy. Let's say division, area, branch.
Now assume we want to have our sales (rentals) fact table showing invoice revenue at the granularity of product, branch, and financial period. Then the dimension keys (and the natural key for the table) will be product_key, branch_key, period_key. We have one measure: revenue_amount.
The key point here is that a single product can appear numerous times in this table, because it's a rental company. Products come back and are rented out over and over again. Furthermore, they can be returned and then rented out from a different branch. So in any one period, a product might have revenue across multiple branches.
Now, each product has an associated cost_amount, being the cost of initially buying the product and adding it to the rental fleet.
We then want to create a new measure: "financial utilization". This is defined as revenue_amount / cost_amount. And we want to be able to roll up this FU measure by product and area over a selected period or range of periods.
How does one define such a measure?
It can't be added to the fact table as the simple expression revenue_amount / cost_amount, because if we do this then the cost_amount will be duplicated when summing over periods or when rolling up geography, and the total FU value will be wrong. The cost amount is at the granularity of the product, but the revenue is at the granularity of product, branch and period! The required expression to work at any level is, in plain english, is sum of revenue over the sum cost of all distinct products in the aggregation.
I assume this requires some kind of tricky MDX/DAX, and maybe splitting the product_cost off into a different fact table with just product_key and cost_amount, but I still have no idea how to define the measure in terms of actual code in a way that rolls up properly for all dimensions.
Sample source DDL (assume name columns are unique within their domains to simplify the model):
create table dim_product(product_key int, product_name varchar(50), subcategory_name varchar(50), category_name varchar(50))
create table dim_period(period_key int, period_number char(6), financial_year smallint) -- eg: 1, '201101', '2011'
create table dim_geography(geo_key int, branch_name varchar(50), area_name varchar(50), division_name varchar(50))
create table fact_revenue(product_key int, period_key int, geo_key int, revenue_amount decimal(10, 2), product cost decimal(10, 2))
Blog on sqlservercentral