3

We have a fact table with the weight trasported by truck/driver/day.

And a dimension with the max weight per truck.

We would like to have the % of weight transported, so we started with

create MEMBER CURRENTCUBE.[Measures].[% WT]
 AS [Measures].[Weight]/[Dim Truck].[Max_weight].currentmember.properties("key"), 
FORMAT_STRING = "Percent";

This only works for individual rows where one truck has only one leaf for the specific day and driver.

It doesn't work if a truck does two trips in a day, instead of sum(weight)/sum(max_weight) or if you prefer sum(weight)/(n*max_weight) what we get is sum(weight)/max_weight

Exactly the same occurs at any aggregate level. For example, month or year or total. Or simply at truck level.

We have been playing with all sorts of formula and scopes without success.

Any idea how to define it so the aggregates work as expected?

5

Your main problem is that you are storing a measure in your dimension. You will keep running into issues with multiselect and or aggregation unless you store the measure where it belongs, namely in the fact table.

Either change your ETL to copy the max_weight in your fact table, or use a named query in your DMV joining your fact table to your dimension table so you can create a real measure.

Depending on your situation you could also add a new measure group.

  • I see your point, and in fact our solution using a present 1 constant value on the fact table, is conceptually what you are suggesting. But also consider that this is a measure related only to truck definition, not to the fact table of trucks delivering parts. So to be purist we would need a second fact table with just truck_id and max_weight ... and this seems exactly what a dimension holds, data related to the truck itself. Theory seems a little bit weird in this situation :) – DeepButi Sep 5 '17 at 13:49
  • 1
    @DeepButi If your purist you add your measures to your measure groups not your dimensions :). Datawarehouses aren't always normalized for a good reason – Tom V Sep 5 '17 at 13:51
2

Probably some MDX COUNT function would do the trick, but as the real fact table is somehow more complex and there is already a constant value of 1 identifying this specific rows among other related info, we simply modified the formula to

create MEMBER CURRENTCUBE.[Measures].[% WT]
 AS [Measures].[Weight]/[Dim Truck].[Max_weight].currentmember.properties("key")/[Measures].[N_Deliveries], 
FORMAT_STRING = "Percent";

N_Deliveries is always 1 (or 0 for other rows with other related info).

This works ... although Excel doesn't know how to calculate some totals, but we can survive that :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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