SCOPE_ISOLATION

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:

Multiple Time Utility Dimensions

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.

Post SP1 cumulative hotfix package now available

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:

UK SQL Server BI User Group – June 22nd

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!

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

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:
 
 

OLAP Survey 6 Now Live

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:

Reporting Services and SAP BW

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:
 

The future of Panorama

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…

Microsoft, BI, Search and Advertising

A few months ago I posted the following entry on Microsoft, BI and Search:
Most of it was guesswork. However, while checking out the following SEO blog:
I did a double-take at the following entry:
Now, surely that is the infamous Proclarity decomposition tree control? Take a look at it in action here:
In fact, a lot of the MS Ad Center links in the blog above seem to suggest that AS2005 is being used quite heavily…

MDX Scripts article

I didn’t realise this was freely available until just now, but here’s an article I wrote for the April edition of ‘SQL Server Professional’ on MDX Scripts:
It’s adapted from the chapter I wrote on the same subject for ‘MDX Solutions’, although the big difference is that for this article I used Adventure Works for my demos whereas in the book I used a cube I built specifically for the purpose.