Konstantin Gorakine's BI Lingual Blog

Just another WordPress.com site

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  

Advertisements

Written by Konstantin Gorakine

June 5, 2009 at 12:14 pm

Posted in MDX

3 Responses

Subscribe to comments with RSS.

  1. Hi Konstantin,Unfortunately, in SSAS2008 using a set alias in this way is going to be bad for performance. It might simplify the logic but it prevents the use of block computation, as this page in BOL mentions:http://msdn.microsoft.com/en-us/library/bb934106.aspxThe original calculation will probably perform just fine; if you wanted to simplify it and make better use of caching, instead of persisting your tuple in a named set you could put it in its own calculated measure, as follows:withmember measures.prevper as ([Date].[Marketing Calendar].CurrentMember.PrevMember, [Measures].[Actuals]) member [Actuals Growth PP %]asiif(measures.prevper <> 0, ( ([Measures].[Actuals] – measures.prevper) / measures.prevper ), null), format_string="Percent"

    Chris

    June 19, 2009 at 6:07 am

  2. Thanks, ChrisI\’ve used SSAS 2005 and forgot to mention the version of SSAS, the example applied to. Thanks for your example as well

    Konstantin

    June 22, 2009 at 11:02 am

  3. Konstantin, Chris\’s comments also apply to SSAS 2005 SP2. As much as they simplify your code, aliases apparently create issues for the query optimizer and often result in less efficient query performance.

    Darren

    July 2, 2009 at 7:02 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: