Konstantin Gorakine's BI Lingual Blog

Just another WordPress.com site

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?

 

Advertisements

Written by Konstantin Gorakine

November 5, 2009 at 1:19 pm

Posted in MDX

4 Responses

Subscribe to comments with RSS.

  1. Hi Konstantin,I tried your original query against a cube I am using at my current client, and didn\’t receive any error. Did you try this against AdventureWorks to see if you can recreate the issue?-Kory

    Kory

    November 8, 2009 at 11:40 am

  2. Sorry, I should have added that I tried using SQL Server 2008. The error you received was one of the issues that 2005 SP2 (CU5) resolved. Have you applied CU5 yet?

    Kory

    November 8, 2009 at 11:45 am

  3. Thanks, Kory – but SP2 is applied

    Konstantin

    November 9, 2009 at 5:05 pm

  4. You must install Cumulative Update 5 or greater, which is post-SP2

    Kory

    November 10, 2009 at 9:19 am


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: