Chris Webb's BI Blog

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

Archive for May 2006

Improving Performance of Analysis Services-Sourced Reporting Services Reports

with 3 comments

This is something I picked up on the other week, when I was doing a job tuning some Reporting Services reports which were running off Analysis Services (there were other, more interesting findings but I’ll leave them for a later date). When you create MDX queries using the RS query builder, the reports look something like this:

SELECT NON EMPTY { [Measures].[Internet Sales Amount] } ON COLUMNS, NON EMPTY { ([Date].[Calendar Year].[Calendar Year].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

 
My customer was creating some very large reports that were up to several hundred pages long, and in some cases they took over a minute to render. What I realised was that the above query contains a number of cell properties that aren’t actually needed, such as BACK_COLOR, FORE_COLOR, FORMAT_STRING, FONT_NAME, FONT_SIZE and FONT_FLAGS. They can safely be removed from the query, as follows:

SELECT NON EMPTY { [Measures].[Internet Sales Amount] } ON COLUMNS, NON EMPTY { ([Date].[Calendar Year].[Calendar Year].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, FORMATTED_VALUE

 

There’s no change to the results returned or how most reports will behave (assuming you’re not somehow referencing these properties somewhere), and in my case it made a noticeable difference in the amount of time taken to render the reports – reports that previously took 60 seconds to run now took 50 seconds. I would guess that this is because for very large queries, there’s a significant overhead involved with including all these unnecessary property values in the resultset

Written by Chris Webb

May 31, 2006 at 5:41 pm

Posted in Reporting Services

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

SCOPE_ISOLATION

with one comment

Steve Pontello of Proclarity sent me some interesting information about the new SCOPE_ISOLATION property in MDX, which appeared in the recent post SP1 hotfix rollup. However I see Andrew Sadler (also of Proclarity – or from the looks of things, "Office Business Applications" is what we should say now – that explains which bit of MS has bought Proclarity then) has beaten me to it:
For more background on what this does and why it’s important, see here:

Written by Chris Webb

May 23, 2006 at 6:44 am

Posted in MDX

Multiple Time Utility Dimensions

with 3 comments

Time Utility Dimensions (or, strictly speaking with AS2005 attribute hierarchies) are useful things. I first learnt about them from George Spofford through various newsgroup postings and the first version of ‘MDX Solutions’ and they have become one of the most commonly used design techniques in Analysis Services cubes; indeed, they have been built into the product in the form of the ‘Define Time Intelligence’ functionality of the ‘Add Business Intelligence’ wizard.

 

So what is there that’s new to say about them? Well, sometimes it’s useful to have more than one. Take for example the following bit of MDX Script created by the wizard that creates two calculated members (Year-To-Date and Year-Over-Year Growth) on the [Year-Month Period Calculations] attribute on a simple time dimension:

 

/*

  Begin Time Intelligence script for the [Period].[Year-Month] hierarchy.

*/

 

Create Member

  CurrentCube.[Period].[Year-Month Period Calculations].[Year to Date]

  As "NA" ;

 

Create Member

  CurrentCube.[Period].[Year-Month Period Calculations].[Year Over Year Growth]

  As "NA" ;

 

 

Scope(

       {

         [Measures].[Sales]

       }

) ;

 

// Year to Date

  (

    [Period].[Year-Month Period Calculations].[Year to Date],

    [Period].[Year].[Year].Members,

     [Period].[Month].Members

  ) =

 

  Aggregate(

             { [Period].[Year-Month Period Calculations].DefaultMember } *

             PeriodsToDate(

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

                            [Period].[Year-Month].CurrentMember

             )

  ) ;

 

 

// Year Over Year Growth 

  (

     [Period].[Year-Month Period Calculations].[Year Over Year Growth],

    [Period].[Year].[Year].Members ( 1 ) : Null,

     [Period].[Month].Members

  ) =     

 

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

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

    ParallelPeriod(

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

                    1,

                    [Period].[Year-Month].CurrentMember

    )

  ) ;

 

  (

    [Period].[Year-Month Period Calculations].[Year Over Year Growth],

    [Period].[Year].[Year].Members ( 0 ),

    [Period].[Month].Members

  ) = Null ;

 

 

End Scope ;

 

/*

  End Time Intelligence script for the [Period].[Year-Month] hierarchy.

*/

 

This works fine, but what happens when your users ask to be able to look at Year-Over-Year Growth for the Year-To-Date values? Of course you could create a third calculated member on the hierarchy which implemented this, but you can probably guess where I’m going with this: the more combinations of calculations that you need, the greater the number of calculated members you need to create and maintain.

 

Here’s where the value of multiple time utility attribute hierarchies comes in because they can help you control this explosion of calculated members – you can make them work together instead. To do this, first you need to create a new attribute hierarchy on your time dimension by dragging and dropping the same column as was used to create your original time utility attribute hierarchy; you need to make sure to set its IsAggregatable property to false so that it doesn’t have an All Member. Then you need to decide which calculations should go on which hierarchy, which of course dictates which combinations of calculations you have available. Here’s the MDX Script example from earlier rewritten so that the two calculated members are now on two different attribute hierarchies called [Period].[Year-Month Period Calculations] and [Period].[Year-Month Period Calculations2]:

/*

  Begin Time Intelligence script for the [Period].[Year-Month] hierarchy.

*/

 

Create Member

  CurrentCube.[Period].[Year-Month Period Calculations].[Year to Date]

  As "NA" ;

 

Create Member

  CurrentCube.[Period].[Year-Month Period Calculations 2].[Year Over Year Growth]

  As "NA" ;

 

 

 

Scope(

       {

         [Measures].[Sales]

       }

) ;

 

// Year to Date

  (

    [Period].[Year-Month Period Calculations].[Year to Date],

    [Period].[Year].[Year].Members,

     [Period].[Month].Members

  ) =

 

  Aggregate(

             { [Period].[Year-Month Period Calculations].DefaultMember } *

             PeriodsToDate(

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

                            [Period].[Year-Month].CurrentMember

             )

  ) ;

 

 

// Year Over Year Growth 

  (

     [Period].[Year-Month Period Calculations 2].[Year Over Year Growth],

    [Period].[Year].[Year].Members ( 1 ) : Null,

     [Period].[Month].Members

  ) =     

 

  ( [Period].[Year-Month Period Calculations 2].DefaultMember ) -

  ( [Period].[Year-Month Period Calculations 2].DefaultMember,

    ParallelPeriod(

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

                    1,

                    [Period].[Year-Month].CurrentMember

    )

  ) ;

 

  (

    [Period].[Year-Month Period Calculations 2].[Year Over Year Growth],

    [Period].[Year].[Year].Members ( 0 ),

    [Period].[Month].Members

  ) = Null ;

 

 

End Scope ;

 

/*

  End Time Intelligence script for the [Period].[Year-Month] hierarchy.

*/

 

Now the user can crossjoin the two calculated members in their queries to get the Year-Over-Year Growth for the Year-To-Date, as well as use them separately.

 

In practice it’s actually quite tricky to determine which calculated members should go on each hierarchy and deal with the associated problem of solve order: for example if we added a 12 Month Moving Average calculation to this script, would we want to see the average of the Year-Over-Year growth or the growth of the average, or both or neither? There’s also another downside to this approach which is that if users found it hard to understand the concept of one time utility attribute hierarchy then they are guaranteed to be confused when they have more than one, though this should be balanced against the usability and maintainability problems inherent in having more than about ten combination-style calculated members on one attribute hierarchy. Overall, I’ve used this technique three or four times now (once even using three separate hierarchies) and it’s proved to be very successful when either the users have been very sophisticated or when the cube is only queried via Reporting Services.

Written by Chris Webb

May 22, 2006 at 10:07 pm

Posted in Analysis Services

Post SP1 cumulative hotfix package now available

with 3 comments

See
A lot of fixes relevant to AS in there, including I notice some of the changes to solve order that I’ve blogged about before.
 
UPDATE: Euan Garden has a good explanation of what this release is and what it means here:

Written by Chris Webb

May 18, 2006 at 1:20 pm

Posted in Analysis Services

UK SQL Server BI User Group – June 22nd

leave a comment »

I was talking to Tony Rogerson of the UK SQL Server Community a while back, and mentioned the fact that it would be cool if some kind of Microsoft BI user group could be set up in the UK. Obliging chap that he is he’s now gone ahead and booked TVP for the evening of June 22nd for me to do what I want with. The agenda isn’t confirmed, but I’ll be presenting something Analysis Services-y and there’ll be at least one other person speaking too. You can go ahead and register for the evening here:
(click on the link in the Events box on the right-hand side)
 
UPDATE: the agenda is now confirmed. I’ll be doing a session on "What’s new in AS2005 MDX"; Mark Hill will be talking about his experiences building multi-terabyte cubes; and Simon Sabin (who I’ve not actually met but am looking forward to meeting) will be talking about SSIS SP1. If you’re involved in BI and live anywhere near Reading then make sure you’re there!

Written by Chris Webb

May 16, 2006 at 9:28 am

Posted in Events

Book Review: “Applied Microsoft Analysis Services 2005″ by Teo Lachev

with 3 comments

When I first thought of including book reviews on this site I made a vow not to review any book until I’d read it all the way through. The end result has been that I’ve got several books on my shelf which I’ve had for a while and which I’ve used extensively but which I haven’t reviewed, because strictly speaking I’ve not read them from cover to cover. Teo Lachev’s "Applied Analysis Services 2005" is one such book, but since I’ve now read so much of it (albeit a few pages at a time, when I’ve needed to look something up) I feel like I can bend my own rule and write a review at last.
 
In terms of content the book aims to be a general reference for anyone who is building BI solutions using Analysis Services 2005 and other, related Microsoft tools like Reporting Services, Integration Services and Office. While it’s suitable for the beginner – and I think Teo writes very clearly indeed, explaining the basic concepts very well – it’s much more than that, and goes into enough detail to make it useful for seasoned BI professionals. I’ve struggled to find a topic that it doesn’t cover in some shape or form (the book is 700 pages long so you get a lot of content for your money) and in almost all cases it goes well beyond the basics to offer sensible, practical advice. I can only think of one topic which I didn’t think was covered in enough depth and that was local cubes, but that was the exception rather than the rule and to be honest in that particular case I’m not sure anyone outside the development team knows much about AS2005’s capabilities. Teo also manages to cover advanced functionality such as measure expressions which isn’t officially documented anywhere else to my knowledge, not even in Books Online, which makes the book invaluable to anyone who wasn’t on the TAP program or doesn’t have a direct line to Mosha.
 
Although Teo’s quite open about the sources he’s used while writing the book, and helpfully includes a list of them with urls at the end of the chapter, I never got the impression that he was simply regurgitating information he’d found elsewhere but instead that he’d tested everything out himself and was offering the fruits of his own experience. He’s honest enough to disagree with Microsoft when he feels like he should, for example when he calls pro-active caching the "most oversold" feature of SQL 2005 after CLR stored procedures, and that to me is the sign of an author who knows his subject. And while I disagreed with him in one or two places on similar matters of opinion or style, I’ve not found any errors in the text either which is impressive for a book of this size and scope.
 
"Applied Analysis Services" isn’t going to be a replacement for more in-depth books like "Data Mining with SQL Server 2005" or (excuse the plug) "MDX Solutions", but if you’re only going to buy one book on AS2005 then you won’t go wrong here. There will be other similar books on the market soon but they’re going to have to be very good indeed to beat this one!
 
You can find out more about the book here:
You can see my list of SQL2005 BI-related books here:
 
 

Written by Chris Webb

May 14, 2006 at 1:50 pm

Posted in Books

OLAP Survey 6 Now Live

with 3 comments

I’ve just been told that the link to supply feedback for the OLAP Survey 6 is now live:
(I think there’s a German version somewhere too, but I don’t have the link for that just now. Stay tuned).
 
If you don’t know what the OLAP Survey is, take a look here:
It’s from the same people that bring you the OLAP Report, and I have a very high opinion of Nigel Pendse and co. And there’s a chance to win some Amazon gift vouchers too, so what are you waiting for?
 
UPDATE – here’s the link to the German version:

Written by Chris Webb

May 12, 2006 at 2:43 pm

Posted in On the internet

Reporting Services and SAP BW

with one comment

Brought to my attention by my friends in Germany, the BI Ultras, here’s a new white paper on using Reporting Services 2005 with SAP BW and MDX:
 

Written by Chris Webb

May 6, 2006 at 11:13 pm

Posted in On the internet

The future of Panorama

leave a comment »

Here’s a good article on the TWDI news site (which I’ve always found to be the best BI news site – Stephen Swoyer seems to be able to know the industry pretty well and doesn’t just regurgitate press releases) on what Panorama’s plans for the future are:
I suppose they’ve got to put a brave face on it. There are some choice quotes from Panorama here, along the lines of "We never competed with Proclarity anyway"; and as for what they’ll do to integrate the ex-Proclarity functionality into their own products, well, the mind boggles…

Written by Chris Webb

May 5, 2006 at 1:11 pm

Posted in On the internet

Follow

Get every new post delivered to your Inbox.

Join 3,083 other followers