Konstantin Gorakine's BI Lingual Blog

Just another WordPress.com site

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.

Advertisements

Written by Konstantin Gorakine

June 3, 2009 at 12:24 am

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: