Traditional web-based reporting with SAS BI tools

Non-additive measure in OLAP cube / MDX query is slow

Reply
Established User
Posts: 1

Non-additive measure in OLAP cube / MDX query is slow

[ Edited ]

My cube has 3 dimensions: location (geo), products and time; a calculated measure (a ratio: dividing measures achieved / goal) and a weight that reffers to the importance of that product in our sales goals. The weight is constant for every product and if I sum it up the total is 100%. I am trying to calculate a new measure that calculates the total percentage of achievment - an weighted average. For example:

 

Group | Branch |  Product    | Budget achieved | Weight |     Weighted budget achieved |

G1      | Br1       | Product 1  |       50%              |    20%  |          10%                                |

G1      | Br1       | Product 2  |       50%              |    80%  |          40%                                |

G1      | Br1 subtotal                                                     |          50%                                |

G1      | Br2       | Product 1  |       50%              |    20%  |          10%                                |

G1      | Br2       | Product 2  |       100%            |    80%  |          80%                                |

G1      | Br2 subtotal                                                     |          90%                                |

 

I'm using SOLVEORDER in order to correctly summarize the subtotals.

 

The problem is that if I want to have summarize at the group level, the Weight column is also summarized so the weighted average is  incorrect:

 

Group | Product     | Budget achieved | Weight |     Weighted budget achieved |

G1      | Product 1  |       50%              |    40%  |            20%                              |

G1      | Product 2  |       75%              |  160%  |          120%                              |

 

G1 subtotal                                                    |          140%                               |

 

The right way would be like this:

 

Group | Product     | Budget achieved | Weight |     Weighted budget achieved |

G1      | Product 1  |       50%              |    20%  |          10%                                |

G1      | Product 2  |       75%              |    80%  |          60%                                |

 

G1 subtotal                                                    |          70%                                |

 

I have managed to this by creating a new calculated measure "weight1":

 

1) [Measures].[weightSUM] / Count(NonEmpty((DESCENDANTS([Location].CurrentMember,,LEAVES))))

 

or

 

2) Max(Descendants(Location.currentmember, [Location].[Location].Levels(5)), measures.[weightSUM])

 

The problem is that it's very slow - it takes a few minutes to exapand the Location hierarchy. Probably because of the "descendants" function. The basetable has ~30.000 records, the location dimension has 5 levels and ~5000 leaves/sales rep, and the product hierarchy has 2 levels and 20 products and the Time dimesnion has year and month levels.

 

 

I tried to use the weight as a property of the Product dimension, but I can't get its value in the MDX statement:

 

StrToValue([Product].CurrentMember.Properties("weight")) doesn't work.

 

Is there any better way to this?

Ask a Question
Discussion stats
  • 0 replies
  • 238 views
  • 0 likes
  • 1 in conversation