Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Some Time Intelligence Wizard Calculations *still* don’t work

with 4 comments

You may remember a post from late last year where I talked about how some calculations generated by the Time Intelligence Wizard in BIDs didn’t work:
Well, the other week I checked to see if this had been fixed in SP1 and indeed it had, using the solution that Mosha outlines in his comment on that post.
 
BUT, to my horror, I found that some of the other calculations now don’t work (and indeed don’t even deploy) in some circumstances. Everything seems to work ok on Adventure Works, but on one of my cubes I got the following MDX for a 12 Month Moving Average:

Create Member

  CurrentCube.[Period].[Year-Month Period Calculations].[Twelve Month Moving Average]

  As "NA" ;

 

 

Scope(

       {

         [Measures].[Sales]

       }

) ;

 

// Twelve Month Moving Average 

  (

     [Period].[Year-Month Period Calculations].[Twelve Month Moving Average],

    [Period].[Month].[Month].Members,

     [Period].[Month].Members

  ) =

   

  Avg(

       {

         ParallelPeriod(

                         [Period].[Year-Month].[Month],

                         11,

                         [Period].[Year-Month].CurrentMember

         ) : [Period].[Year-Month].CurrentMember

       },

      [Period].[Year-Month Period Calculations].DefaultMember

  ) ;

 

 

End Scope ;

I’ve highlighted the problem in bold/red: the [Period].[Month] attribute is mentioned twice in the subcube definition; apparently it happens when the target level of the calculation is also the key attribute of the dimension. The fix is fairly simple, though, and is to remove the second mention of the attribute ie in this case the reference to [Period].[Month].Members. This has been confirmed as a bug and will, I’m promised, be fixed in SP2.

Written by Chris Webb

May 31, 2006 at 2:44 pm

Posted in MDX

4 Responses

Subscribe to comments with RSS.

  1. Talking of the Time Intelligence Wzard, will these calculations work as expected in Excel 2007? A couple of recent posts raised some doubts:
     
    http://groups.google.com/group/microsoft.public.sqlserver.olap/msg/54f2ecee7e59d848?hl=en&
    >>

    Hi NG,
    I tested these days Excel 12 with ASY. I realized that the Pivot Table uses sub cubes to filter. This has some bad side effects with some calculated members that aggregate using LASTPERIODS (12) over a time period. When you put the time dim in the filter and you use a calculated measure with LASTPERIODS (12) the 12 time members are filtered out by the sub cube and you just get the member in the filter. In Excel XP the calculated measures work fine because Excel XP uses the WHERE clause to filter and LASTPERIODS (12) returns 12 members. Has anybody an idea if this is a Excel bug? Whats wrong here?
    Andreas >>
     
    http://sqljunkies.com/WebLog/reckless/archive/2006/03/08/18601.aspx
    >>.. What this really means is that our subselect does not set the current coordinate in the cube (in Moshas words it "merely does top level Exists with axis and applies visual totals"). This is really important to remember if you ever want to use the EXISTING operator for some fancy MDX calculations, because if your client tools use subselects (which, for example, Excel 12 does extensively I am told), you might run into some unexpected results.
    >>

    Deepak

    June 1, 2006 at 1:02 am

  2. I agree with Deepak on this one… This really is a big issue. It really means that the type of time calculations that the BI wizard creates cannot be used with Excel 2007 (and at least one other BI tool that I know of). In my opinion, the best solution would be to make the subcube-statements set the current coordinate of the cube, but I am not sure this is a technically viable solution.

    Michael

    June 8, 2006 at 8:14 am

  3. Soooo….with all the headaches in the Time Intelligence portion of SSAS 2005 – could someone provide me with a "good" example of implementing a MTD / YTD calculation that can be used across all measures?  I would like to see a code example of using AdventureWorks give me a universal calculated member that can be used across any measure / measure group.
     
    1.         Is this possible?
    2.        Ideas
    3.        Anyone have the lowdown on Time Intelligence in SSAS 2005 and why it is such a pain?!
     
    Deepak, Chris, and Mosha I look to you!!
     
    Thanks
     
    Dave Harper

    Unknown

    July 5, 2006 at 8:26 pm

  4. It should be possible to write,  but it all depends on what behaviour you\’re expecting to see (cf the debate on the older post about whether YTD calcs should work with a \’Month of Year\’ attribute). The latest version of the code generated by the wizard is probably the best candidate for a general calc though, so long as you watch out for the bug mentioned in this post…

    Chris

    July 10, 2006 at 9:31 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

Follow

Get every new post delivered to your Inbox.

Join 2,867 other followers

%d bloggers like this: