Konstantin Gorakine's BI Lingual Blog

Just another WordPress.com site

Weighted Average: from requirements to implementation

leave a comment »

Just a few days ago, I was given a very typical request to calculate common business measure: Weighted Average. Usually, business folks give their verbal explanation of the problem along with their way of calculation and provide us developers with at least a formula or an Excel spreadsheet that explains in detail what is required and how it’s solved by example. Since, almost all people who are new to MDX script struggle to tackle such requests, and because I was asked several times on how I did it, I decided to provide a step-by-step explained development process of converting Excel logic to a MDX implementation and to be able to show how a typical problem can be solved and even optimized to a certain extent.

 

As a business scenario, lets say that potential customers inquire (call, browse web, email, etc) for some service and eventually some percent of those people subscribe for it. In our cube we have 2 regular measures: Inquiry and Subscription. We record our data at the Month level and need to calculate Conversion (# of Subscriptions divided by # of Inquiries)  Weighted Average at all levels above the Month level (Quarter, Year and All Dates). It’s better to grasp by looking at the Excel spreadsheet

 

              

There are several formulas presented here. The first one is simple and it’s getting applied at the intersection of the Month level and [Conversion Ratio we need]

 

 

We simply divide the number of Inquiries by the number of Subscriptions and we get our Conversion Ratio at the Month level. So, typical MDX expression to calculate this would look like this:

 

create member currentcube.Measures.[Conversion Ratio] as

iif([Inquiry Count] = 0, null,

[Measures].[Subscription Count] / [Inquiry Count])

, format_string = "#.00%", visible = 0;

 

Second calculation will be involved in overall calculation of our Conversion Weighted Average and provide us with the ratio of Number Subscriptions at the current to a Number of Subscriptions at the Calendar parent level 

 

 

This calculation works like the following:

 

create member currentcube.Measures.[Subscription % to Parent] as

iif(([Date].[Calendar].CurrentMember.Parent, Measures.[Subscription Count]) = 0, null,

([Date].[Calendar].CurrentMember, Measures.[Subscription Count])

 /

[Date].[Calendar].CurrentMember.Parent, Measures.[Subscription Count]))

, format_string = "#.00%", visible = 0;

 

Here we use the Parent reference of the current member to get Subscription Count at the higher level and check whether this calculation is not equial to 0 since it used as a denominator and we want to avoid 1/0 error.Now comes the most interesting calculation: the final Conversion Ratio at the levels higher than Month level:

 

 

Looks like the Weighted Average at the higher level is not as simple as we thought (sum of children divided by their count): first it requires us to calculate Conversion Ratio than the mentioned above [Subscription % to Parent], calculate their product and only then – sum results of all children. And it should work at all levels above the Month level…

 

How do we approach this problem? We know that eventually we are going to use SUM() function, but how to implement earlier calculations inside it? SUM function expects mandatory set and additional <Numeric_Expression> parameter. If that parameter is there, inside of that numeric expression, we iteratively can get access to any member from the set (provided as a first parameter), meaning that we have flexibility to calculate any expression for a current set’ member in question. This and some other functions create their own calculation context, which allows us to provide complex calculations with ease. So, my first pass was like that – I wanted to calculate something, for example, at the Quarter and Month levels. First, I created empty calculated member

 

create member currentcube.Measures.[Conversion Ratio we need] as "NA";

 

then I oppened a new scope and provided calculations for the Month level. This will work as a simple assignment of already existing calculated member:

  

scope(Measures.[Conversion Ratio we need]);

  this = Measures.[Conversion Ratio];

  format_string(this) = "#.00%";

 

end scope;

 

then, inside of that scope, another scope is added to narrow down my first calculations to the Quarter level

 

scope(Measures.[Conversion Ratio we need]);

  this = Measures.[Conversion Ratio];

  format_string(this) = "#.00%";

 

  scope([Date].[Quarter].Members);

  this = sum(descendants([Date].[Calendar].CurrentMember,

                         [Date].[Calendar].[Month]),

             –<numeric expression> parameter

            ([Measures].[Conversion Ratio] *

             [Measures].[Subscription % to Parent]));

  format_string(this) = "#.00%";

end scope;

 

end scope;

 

here, inside <numeric expression> we accessing every Quarter’s Month and calculate our product of [Conversion Ratio] and [Subscription % to Parent]. Since our scope is working at the Quarter level and providing additional scopes for Year and All Dates levels would be redundant, I expanded calculation scope to all levels above Month level. Scope definition looks like this now:

 

scope([Date].[Calendar].[All Dates] * [Date].[Year].Members * [Date].[Quarter].Members);

 

Another problem was to provide a proper set to a SUM function. Turned out that Weighted Average did not calculate properly at the Year and All Dates levels if always set of Months was provided to the SUM function. For calculations to work for each level, I modified calcs to provide a set of children of the current member only, so for example for a Year level, Year’s Quarters were passed, for All Dates – Years and so on. Therefore, instead of using descendants, I used

 

[Date].[Calendar].CurrentMember.Children

 

Finally, my calculation started to look very simple now. Here is the complete list:

 

create member currentcube.Measures.[Conversion Ratio] as

iif([Inquiry Count] = 0, null,

[Measures].[Subscription Count] / [Inquiry Count])

, visible = 0;

 

 

create member currentcube.Measures.[Subscription % to Parent] as

iif(([Date].[Calendar].CurrentMember.Parent, Measures.[Subscription Count]) = 0, null,

([Date].[Calendar].CurrentMember, Measures.[Subscription Count])

 /

[Date].[Calendar].CurrentMember.Parent, Measures.[Subscription Count]))

, visible = 0;

 

 

create member currentcube.Measures.[Conversion Ratio we need] as "NA";

 

 

scope(Measures.[Conversion Ratio we need]);

  –Month level only – (Quarter, Year, All Dates) will be overwritten by the second scope

  this = Measures.[Conversion Ratio];

  format_string(this) = "#.00%";

 

  –all other levels (Quarter, Year, All Dates)

  scope([Date].[Calendar].[All Dates] *

        [Date].[Year].Members *

        [Date].[Quarter].Members);

                               –will provide set of children for any

                               –level

  this = sum([Date].[Calendar].CurrentMember.Children,

             –will calculate product of ratios for each Calendar

             –member

            ([Measures].[Conversion Ratio] * [Subscription % to Parent])

             );

  format_string(this) = "#.00%";

end scope;

 

end scope;

 

I can now run my query and get my Conversion Weighted Average as follows:

 

 

The purpose of this blog is to provide the development path, starting by analyzing requirements and finishing with working solution. Please comment if this solution can be optimized even further.

Advertisements

Written by Konstantin Gorakine

September 18, 2009 at 3:58 pm

Posted in MDX

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: