Wednesday, August 17, 2011

MDX to get the parent level summation for a Measure with LastNonEmpty as aggregation

I faced an situation recently related to Inventory Calculations. In that we keep average inventory value in the Item level. It's parent level is category level. Aggregate function for average inventory measure is LastNonEmpty. In the report we have to show the summation of average inventory measure group by category level.
But the issue is if we take only the category dimension hierarchy and the average inventory measure, the value is incorrect. Because the aggregate function of the average inventory measure is LastNonEmpty, not the Sum.
We can resolve that issue by just adding the Item dimension hierarchy to the report data set and show the summation of average inventory measure value in the report using expressions.
But this will increase the report execution time if there are large number of items exist in the cube. Therefore in such situation report will take more time to load.

The best solution is add a calculated member to the cube which stores the summed value of items relevant to the category.
Just add a calculated member and add the Expression as

Sum(([Item—Category].currentmember.children), [Measures].[AverageInventory])

In this [Item—Category] is the hierarchy in the cube.

This resolves the issue with high report performance.

No comments:

Post a Comment