Konstantin Gorakine's BI Lingual Blog

Just another WordPress.com site

Archive for the ‘MDX’ Category

ParallelPeriod + PeriodsToDate = Unexpected exception

with 4 comments

Apparently, I have no luck with ParallelPeriod() function again, but now, on how it works together with PeriodsToDate() function. This example is run on SSAS 2005 SP2 and I didn’t have chance to run it on SSAS 2008 yet, so very possible, it’s not an issue anymore.

 

A very harmless, at first sight MDX combination, left me in limbo and I wish I knew more about internals of query parsing and resolving process. Let’s say, I am collecting my data at the Week level and I want to calculate rolling measure by using PeriodsToDate function. My first calculation member looks like this:

 

member firstQTD as

aggregate(PeriodsToDate([Date].[Marketing Calendar].[Market Quarter])

         , [Measures].[MyMeasure]) 

 

Now, I want to calculate firstQTD for the previous year. So, here is the second member:

 

member QTD_LY as

(firstQTD,  ParallelPeriod([Date].[Marketing Calendar].[Market Year]))

 

or even:

 

member QTD_LY as

(firstQTD, 

ParallelPeriod([Date].[Marketing Calendar].[Market Year], 1,

               [Date].[Marketing Calendar].CurrentMember))

 

Very basic. But when I run my query:

 

with

member firstQTD as

aggregate(PeriodsToDate([Date].[Marketing Calendar].[Market Quarter])

         , [Measures].[MyMeasure])

 

member QTD_LY as

(firstQTD,  ParallelPeriod([Date].[Marketing Calendar].[Market Year]))

 

select

{{

[Date].[Marketing Calendar].[Market Week].&[200821],

[Date].[Marketing Calendar].[Market Week].&[200921]} *

{

firstQTD, QTD_LY

}}

on 0,

non empty [MyDimension].Members on 1

from [Cube]

 

I get something totally unexpected to me and to SSAS internals. Literally:

 

Executing the query …

Internal error: An unexpected exception occured

Execution complete

 

 

By rewriting my query, I’ve got some explanation of the internal info about the error:

 

Executing the query …

Internal error: An unexpected error occurred (file ‘mdevaluator.cpp’, line 2721, function ‘MDDisjointUnionEvaluator::AddSplit’).

Execution complete

 

The new query is:

 

with

member firstQTD as

aggregate(PeriodsToDate([Date].[Marketing Calendar].[Market Quarter],

                        [Date].[Marketing Calendar].CurrentMember)

         , [Measures].[MyMeasure])

        

member QTD_LY as

(firstQTD,  ParallelPeriod([Date].[Marketing Calendar].[Market Year], 1,

                           [Date].[Marketing Calendar].CurrentMember))                

                  

select {QTD_LY} on 0,

{

[Date].[Marketing Calendar].[Market Quarter].&[2009]&[1].Children,

[Date].[Marketing Calendar].[Market Quarter].&[2009]&[2].Children

}

on 1

from [Cube]

 

By rewriting the firstQTD and making it inelegant and equal to:

 

member secondQTD as

aggregate({ancestor([Date].[Marketing Calendar].CurrentMember,

            [Date].[Marketing Calendar].[Market Quarter]).FirstChild :

                    [Date].[Marketing Calendar].CurrentMember}

                    , [Measures].[MyMeasure])

 

, I’ve got my both queries running, and myself puzzled:

 

 

This left me thinking about ability (tool) for a regular developer to look inside of the code parsing process and be able to at least follow the code flow or something….

 

Any ideas on what happened to my query?

 

Written by Konstantin Gorakine

November 5, 2009 at 1:19 pm

Posted in MDX

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.

Written by Konstantin Gorakine

September 18, 2009 at 3:58 pm

Posted in MDX

SUBCUBE vs. Slice for YTD()

with one comment

When cube is built and developer wants to browse it quickly, often the first choice is SSMS or BIDS Browse view. By right clicking on the cube in SSMS Object Explorer and selecting the “Browse” option, a Browse view opens with Cube Metadata viewer, OWC and additional Filter pane, which altogether make cube navigation experience fast and simple as it can be. The Filter pane gives you ability to navigate the cube based on conditional operator (=, in, no in, etc) as well as with MDX expression and range selection. This is very flexible way of defining your filter conditions and definitely adds up value to overall user experience comparing it with OWC Filter selector which in turn allows only specific member selections. But what about MDX which is generated by both filters and sent over to the server? Is it the same when we use different filters for the same constrain?

 

Recently, I was testing my YTD and QTD calculated members created on the cube side along with real measures and unintentionally dropped week member on the Filter pane (not OWC Filter selector). Just to remind: for YTD, QTD, MTD to work properly, the Date CurrentMember needs to be selected at the level lower or equal to the function level(Year, Quarter or Month). Here is MDX for YTD and QTD calculated members:

 

CREATE MEMBER CURRENTCUBE.[Measures].[Actuals YTD]

 AS AGGREGATE(YTD([Date].[Marketing Calendar].CurrentMember)

    ,[Measures].[Actuals]),

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Actuals QTD]

 AS AGGREGATE(PERIODSTODATE([Date].[Marketing Calendar].[Market Quarter],

                      [Date].[Marketing Calendar].CurrentMember)

    ,[Measures].[Actuals]),

VISIBLE = 1;

 

When I looked on the Results view, nothing was shown for YTD and QTD members. Real measure was showing some data.

 

 

Profiler is the first choice to see what the final MDX is that gets submitted to the server.

 

 

Looks like a SUBCUBE is created first to reduce the querying space and run subsequent queries in its context for a current Session. Name of the SUBCUBE is the same as name of the original Cube. After refreshing the new subcube and creating a temporary set of members to place on rows, a final query is submitted:

 

 

So, what is wrong with the query? Why YTD and QTD don’t work? Sure, SUBCUBE reduced querying space and I can run the following query to prove it.

 

create subcube [Enrollment] as

select ({ [Date].[Marketing Calendar].[Market Week].&[200924] })

on columns from [Enrollment];

 

select {} on 0,

[Date].[Marketing Calendar].Members on 1

from [Enrollment];

 

drop subcube [Enrollment];

 

Here is the results of the query:

 

 

The reason for YTD and QTD not returning any data is that although SUBCUBE has reduced the querying space, it is NOT resetting the DefaultMember to the member of choice (Week 24, 2009 in our case), nor providing any slices for CurrentMember as well. If I run:

 

with

member [Default Market Week] as [Date].[Market Week].DefaultMember.Unique_name

member [Default Marketing Calendar] as [Date].[Marketing Calendar].DefaultMember.Unique_name

member [Current Market Week] as [Date].[Market Week].CurrentMember.Unique_name

member [Current Marketing Calendar] as [Date].[Marketing Calendar].CurrentMember.Unique_name

select {[Default Market Week],

        [Default Marketing Calendar],

        [Current Market Week],

        [Current Marketing Calendar]} on 0

from [Enrollment]

 

The defaults are still

 

 

Looking back to the final query that is submitted to the server, we can see that no slice for Date dimension is provided, leaving server to use Default Members implicitly. Since YTD and QTD are not working at (All) level, no data is returned for those calcs.

 

Results look different when Date member is provided for OWC Filter

 

 

Differently looks the final query as well:

 

 

Here is an explicit slice provided for the Date dimension, so YTD’s are working fine.

 

Filters are not the same and some calculated members can show different results depending on the type of the Filter used.

 

Written by Konstantin Gorakine

August 27, 2009 at 11:54 am

Posted in MDX

List all cubes’ Dimensions, Hierarchies and Current Members

leave a comment »

I needed to list all dimensions and their current members to see the overall picture of my cube context and autoexists behavior. I used Chris Webb’s query, but strangely enough, it was running longer than expected. So, here is my version of it that shows me all info that I need.

with

 

member [Dim Rank] as rank([Customer].[Customer].CurrentMember,

                          [Customer].[Customer].Members) – 1

member [Dimension Name] as dimensions([Dim Rank]).Dimension_Unique_Name

member [Hierarchy Name] as dimensions([Dim Rank]).UniqueName

member [Current Member Name] as dimensions([Dim Rank]).CurrentMember.UniqueName

 

select {[Dim Rank], [Dimension Name], [Hierarchy Name], [Current Member Name]} on 0,

head([Customer].[Customer].Members, dimensions.count) on 1

from [Adventure Works]

 

where [Date].[Fiscal Year].&[2002]

 

Here is the result of the query

 

Written by Konstantin Gorakine

June 17, 2009 at 12:10 pm

Posted in MDX

Set alias in IIF statement

with 3 comments

When you write IIF statement, whether as a part of the calculated member or select statement, often one tuple or set needs to be evaluated more than one time. For example, in the following query

 

with

member [Actuals Growth PP %]

as

iif(([Date].[Marketing Calendar].CurrentMember.PrevMember[Measures].[Actuals]) <> 0,

   (

    ([Measures].[Actuals] –

    ([Date].[Marketing Calendar].CurrentMember.PrevMember, [Measures].[Actuals])) /

    ([Date].[Marketing Calendar].CurrentMember.PrevMember, [Measures].[Actuals])

   ), null)

    ,format_string="Percent"

 

select

[Actuals Growth PP %] on 0,

[Date].[Marketing Calendar].[Market Year].&[2009].Children on 1

from [Inquiry]

 

The ([Date].[Marketing Calendar].CurrentMember.PrevMember, [Measures].[Actuals]) tuple will be evaluated 3! times for each Market Calendar current member. Set aliases can help to evaluate a set only once and be referenced in remaining parts of the statement. In the modified version of the query, our tuple is silently converted to the set, set alias is assigned to it and finally extracted tuple (by Item(0)) is used in all remaining calculations.

 

with

member [Actuals Growth PP %]

as

iif(([Date].[Marketing Calendar].CurrentMember.PrevMember[Measures].[Actuals])

     as alias.Item(0) <> 0,

   (

    ([Measures].[Actuals] – alias.Item(0)) alias.Item(0)

    ), null),

   format_string="Percent"

 

select

[Actuals Growth PP %] on 0,

[Date].[Marketing Calendar].[Market Week].Members on 1

from [Inquiry] 

 

additional bonus: calculation logic itself is much clearer now  

Written by Konstantin Gorakine

June 5, 2009 at 12:14 pm

Posted in MDX

Problem with ParallelPeriod() function

leave a comment »

One of the clients had very weird Date dimension. They simply mapped weeks to months with no pattern in mind. For example, [May 2008] in that dimension has 5 weeks, whereas [May 2009] – 4. Still, they want to use ParallelPeriod() to compare same members between different years and expect to see meaningful data. Looks like ParallelPeriod doesn’t work that way. Whenever there is a user-defined hierarchy and same child in different years belongs to the different parents (Months), ParallelPeriod will make a shift following the hierarchy path and return a child under the same parent of the parallel period. For example, if you have [Week 22, 2009] member and want to select parallel member from year 2008, the [Week 23 2008] will be returned and the reason: [Week 22, 2009] member is the first child of [June 2009], so ParallelPeriod will follow Year-Quarter-Month-Week hierarchy and select the first child of [June 2008] which is [Week 23 2008].

When you run

with

member [ParallelP] as

(ParallelPeriod([Accounting Period].[Year], 1, [Accounting Period]), [Net Wt]).Item(0).Name

 

select { [ParallelP]} on 0,

 

{[Dim Accounting Period].[Accounting Period].[Month].&[May 2009].Children

,[Dim Accounting Period].[Accounting Period].[Month].&[June 2009].Children}

 on 1

from [Sales Invoice]
 

you get

 

See the shift. Not good

To be able to avoid this, Week level has to be ranked by the natural order, and sibling at the same position of the previous year has to be referenced:

with

 

member [Net Wt LY] as

iif([Dim Accounting Period].CurrentMember.Level is [Dim Accounting Period].[Week],

    –at the week level use ranking instead of ParallelPeriod()

    (descendants(

                 ancestor([Dim Accounting Period].CurrentMember,

                          [Accounting Period].[Year]

                          ) as alias.PrevMember

                 ,[Dim Accounting Period].[Week]

                 ).Item(

                        rank([Dim Accounting Period].CurrentMember,

                              descendants(alias, [Dim Accounting Period].[Week])

                             ) – 1

                        ), [Net Wt]

      )

    –at any other level use ParallelPeriod safely                       

   ,(ParallelPeriod([Accounting Period].[Year], 1, [Accounting Period]), [Net Wt])

   )

 

select {[Net Wt], [Net Wt LY]} on 0,

{

 [Dim Accounting Period].[Accounting Period].[Month].&[May 2008].Children

,[Dim Accounting Period].[Accounting Period].[Month].&[June 2008].Children

,[Dim Accounting Period].[Accounting Period].[Month].&[May 2009].Children

,[Dim Accounting Period].[Accounting Period].[Month].&[June 2009].Children

,[Dim Accounting Period].[Accounting Period].[Month].&[October 2008]

}

on 1

 

from [Sales Invoice]

You can see, that data for the same members (Weeks) under different years is now matching.

Written by Konstantin Gorakine

June 3, 2009 at 12:24 am

Posted in MDX