August 29, 2012 at 5:24 am
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))
August 29, 2012 at 6:26 am
allmhuran (8/29/2012)
...Now assume we want to have our sales (rentals) fact table showing invoice revenue at the granularity of product, branch, and financial period.
...
I think you need to have your granularity at a lower level, such as rental transaction and add a proper date dimension. Once you have your facts at the lower grain then they should roll-up nicely within your hierarchies.
August 29, 2012 at 9:48 am
Charles Hearn (8/29/2012)
allmhuran (8/29/2012)
I think you need to have your granularity at a lower level, such as rental transaction and add a proper date dimension. Once you have your facts at the lower grain then they should roll-up nicely within your hierarchies.
It wouldn't matter in this case.
The problem is that the product cost is less granular than the revenue. So making revenue even more granular won't help.
Tangentially, the granularity of product, period, branch was selected for the data because it meets all of the usage requirements and results in much smaller storage size and faster loading times. For the record, I've built this entire thing, from source queries, data warehouse, data cleansing, dimension and fact loads, relationships, a powerpivot file, hierarchies, and a published tabular cube all since last thursday because we needed some figures quickly... I am le tired... :doze:
August 29, 2012 at 1:44 pm
Is there one cost_amount per product key?
I see two options.
1. Add cost_amount to the dim_product table. Create a calculated measure SUM([Measures].[Revenue Amount]/[Dim Product].[Cost Amount].MemberValue)
This only calculates FU per product. So if you do this, you can only see FU at the product key granularity. You can roll up based on the geography and period dimensions, but you will always have to see FU broken down to each individual product. There may be a better MDX calculation but this is the best I can think of right now.
2. Create a new fact table to fact_product (product_key, cost_amount). Create a new measure group with [Cost Amount] measure and make sure [Dim Product] is mapped to both [Fact Product] and [Fact Revenue]. Create a calculated measure SUM([Measures].[Revenue Amount])/SUM([Measures].[Cost Amount]). This should allow you to slice and dice FU any way you want.
August 29, 2012 at 7:37 pm
Cheers, I will try the second option and see if it does what I expect. I am suspicous... that seems like too simple a solution lol.
August 30, 2012 at 9:36 pm
This solution apparently does not quite work.
When sum against the fact_product purchase cost is executed, it is executed against the entire product cost table. In other words, the result is always the total cost of all products.
This is not correct, the cost that should be returned would be the cost of products that appear in the revenue set, which is a subset of all products, because not all products generate revenue in every period.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply