MDX Selective Summation for a Normalization Problem

Question

Hello,

I'm new to MDX and have a problem regarding filtering out values from a summation.

We want to perform normalization calculations, specifically a quantity (q) divided by a basis (b) to get an Intensity (I). The formula is fairly simple I=q/b.

OUR CUBE STRUCTURE:

We have fact tables FactQuantity and FactBasis, each of which are tied to our DimOrg and DimTime dimension table. Both dimensions have hierarchies: Months rollup into years in DimTime, and Locations roll up into business units and groups in DimOrg.

The fact tables are not in sync with each other: just because a quantity exists for a particular org_id and time, a basis is not guaranteed to exist for that same org_id and time.

MY ATTEMPT AT A SOLUTION:

The basic form of the calculated member is easy: [Measures].[Quantity]/[Measures].[Basis]

This works fine when we view at the lowest level of both dimensions, but the problems arise when you roll up the dimensions and begin aggregating.

The problem is that Quantity values that don't have a corresponding Basis value at the same time and location are included, thus making the numerator too large and therefore incorrect.

QUICK EXAMPLE:

org_id 001 in Group A has a basis of 100 and a quantity of 1000, so its calculated intensity is 1000/100=10. Good so far. org_id 002 also in Group A has no basis but it does have a quantity of 2000, so its calculated intensity errors out. Fine.

Rolling up to Group A sums the quantity (3000) and the basis (100), leaving a calculated intensity of 30, which is incorrect for the Group. The 2000 should have been excluded because it had no corresponding basis value for the same org_id and time.

Any help you can offer is much appreciated.

Thanks,

Answer 1

Deepak Puri answered this on the MSDN Forums:

Assuming that [DimOrg].[Location] and [DimTime].[Month] are the leaf-level attributes for the 2 dimensions, you could sum the [Measures].[Quantity] numerator over only those leaves with a basis, like:

Sum(NonEmpty(existing [DimOrg].[Location].[Location].Members * [DimTime].[Month].[Month].Members, [Measures].[Basis]), [Measures].[Quantity]) / [Measures].[Basis]