SCOPE and calculated members

This post on the MSDN forum:

…reminded me of something interesting I found out a few months ago. It seems that whereas you can’t mix regular and calculated measures in a set used in the SCOPE statement, you can rewrite the assignment to avoid using SCOPE and do a direct assignment instead. So, for example, if you were trying to assign to a regular measure and a calculated measure using a SCOPE statement like this:


You would get the following error:

A set has been encountered that cannot contain calculated members.
MdxScript(Cube1) (line, col) A set has been encountered that cannot contain calculated members.
The END SCOPE statement does not match the opening SCOPE statement.
MdxScript(Cube1) (line, col) The END SCOPE statement does not match the opening SCOPE statement.

You could rewrite the assignment as follows using two SCOPEs:



…but this is clearly a pain, as you’re duplicating your assignment logic. What you can do instead is this:


Not as easy to read as using SCOPE, I know, especially if you’re doing something complex, but at least it works! Now I wonder why SCOPE has this problem? Probably something worth opening an issue on Connect about…

White paper on resolving query performance problems

Another great and very detailed white paper from the SQLCat team entitled "Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services". You can download it here:
There’s loads of information here I’ve not seen before, for example on Perfmon counters. A must read! 

AS2008 webcast now available

If, like me you missed the recent AS2008 new features webcast then you can now download the recording here:
It’s a very good summary of what’s coming, although the content of the presentation contains no major new revelations. The question/answer session is a bit more revealing: we’ll be getting what sounds like very basic execution plans (possibly visual), there’s no LINQ integration as yet, and there are no changes to SSRS/SSAS integration (WHEN are the people in Redmond going to wake up and do something about this???). 

Post-holiday round-up

The worst thing about taking a holiday is coming back home and, despite having taken my laptop with me and checking my mails a few times, finding there are a gazillion emails waiting for me to follow-up. Hohum. Anyway, here are a couple of things that need mentioning…

First of all, registration is open for the next BI evening in London on September 26th:

There’s also a whole load of new stuff up at the SQLBits site, including some sessions (though not mine yet) and speakers:

You can register for the conference here:

I see Vidas Matelis has picked up some more useful snippets of new features in AS2008 from a recent webcast:

I missed the main webcast on the wider changes (originally billed as just being about the time series algorithm but it seems to have had its scope widened) in AS2008 but it looks like the recording should be available soon:

PerformancePoint CTP4 has been released and can be downloaded from Connect. David Francis has a feature list:

I also received a copy of Marco Russo’s new book "Introducing Microsoft LINQ". I won’t be reviewing here because I’m not really qualified to do so, but from the point of view of someone whose dev skills are pretty basic it looks like a good introduction to the subject.

SQLBits and the next BI Evening

I’ve been meaning to blog about these two events for a while but somehow not got round to doing so…
Anyway, first date for your diary is the evening of September 26th when the next BI evening will be held in London, at the offices of IM Group. Registration isn’t open yet (I’ll update this post when it is) but there will be two speakers:
Suranjan Som of IM Group:
Data Mining in practice – the Microsoft Way
Data Mining has come a long way from being a black art to a contemporary tool that allows businesses to unearth the hidden potential in their data. Come and join us for a whirlwind that looks how one can leverage data mining to address business problems using the new features in SQL 2005/2008.
Mark Hill of Edenbrook:
What’s new in the SQL2008 relational engine for BI?
How does Katmai change how we do MS BI projects, Mark Hill takes a look at the planned improvements such as CDC and UPSERT and what impact they will have followed by couple of demos on how these new features can be used.
Even more exciting, on Saturday October 6th is the first SQLBits community day at Microsoft in Reading. It’s a free one-day event concentrating on all things SQL Server, kind of a SQL version of Developer Day, and you can find out more here:
Again, registration isn’t open yet but we are looking for speakers and sponsors so please get in touch through the site if you’re interested in doing either. For my sins I’m helping organise the BI/DW track, so hopefully I’ll see you there!

MDX Editor for SQLMS

Ashton Hobbs has just posted a very early version (ie it’s a bit buggy and Ashton is looking for feedback) of an MDX editor he’s working on that integrates with SQL Management Studio and adds some of the more obvious functionality that SQLMS is missing. You can read more about it here as well as download it:

Here are a few more details:

The Editor provides Intellisense, Snippets, and the grid supports any number of specified Axis.  I’ve only included one snippet initially for parallelperiod, but people can create their own (they’re stored in My Documents\My SSMS Snippets).  With ParallelPeriod snippet, I’ve added a custom snippet token tag called DateLevel which will pop down all levels from Date dimension for the cube.  You can invoke the parallelperiod snippet using its shortcut “pp” or by pressing Ctrl+Shift+P to invoke snippet menu (although seems to work in some and not other SSMSs).  Ctrl+Tab will invoke the Intellisense drop down.  F5 executes a query.  The treeview also has tooltips on each of the nodes that provide more detailed info than standard SSMS.

There are several other items I want to add like additional snippets (if anyone wants to make any and share that would be great J), function intellisense, export options, and any other MDX thing that might be useful.

If anyone does try it out then I would be interested to hear your thoughts and whether it’s something people would use (if so, I’ll continue working on it), also if there any found bugs.

What’s new in AS2008?

There are a whole bunch of new white papers detailing new features in SQL2008 (you can see the complete list here) but I thought I’d highlight the BI paper:

It’s the first time I’ve seen a fairly detailed discussion in public of what’s going to be new in SQL2008. Here’s a section on what’s new in the AS engine:

The premise of Online Analytical Processing (OLAP) is that instant access to accurate information enbles end users to answer even the most complex questions at the speed of thought. Thus, the aim to continuously excel in providing even faster query times and data refresh rates is a priority during the development process of any Microsoft SQL Server Analysis Services release, an aim that naturally also has been driving the release of SQL Server 2008 Analysis Services.

SQL Server 2008 includes Analysis Services that enable you to drive broader analysis with enhanced capabilities, including complex computations and aggregations. Analysis Service provides enterprise-scale performance through:

· A flexible caching model. With Analysis Services, you can control how data and aggregations are cached to optimize query performance while maintaining an acceptable level of latency between the cache and its underlying data store.

· Declarative attribute relationships. In an Analysis Services dimension, you can explicitly declare relationships between attributes in a hierarchy. This enables Analysis Services to pre-generate aggregations when a cube or dimension is processed, which improves runtime query performance.

· Block computation. Block computation eliminates unnecessary aggregation calculations (for example, when the values to be aggregated are NULL) and provides a significant improvement in analysis cube performance, which enables users to increase the depth of their hierarchies and complexity of computations.

· Writeback to MOLAP. Analysis Services 2008 removes the requirement to query ROLAP partitions when performing writebacks, which results in huge performance gains.

· Scale-out Analysis Services. A single read-only copy of an Analysis Services database can be shared between many Analysis Servers through a virtual IP address. This creates a highly scalable deployment option for an Analysis Services solution.

 The two points here are the biggies. Block computation should significantly increase the performance of many calculations that don’t perform well on SQL2005; writeback to MOLAP should finally solve the biggest weakness of AS for financial applications, namely that writeback performance at the moment is rubbish (maybe PerformancePoint will consider it good enough to use now!).

SQL2008 July CTP Released

You can get it from Connect, as always:

I’m not going to install this CTP because a) I’m about to go on holiday and b) there isn’t a VPC available yet, although one is coming in a few weeks.

What’s new in AS? Well, according to the docs nothing much – just an improvement to the time series algorithm in data mining. Is that it, I hear you cry? Well at the moment there are two possibilities as far as I can see: either the AS team have done their usual bad job of telling anyone what they’ve implemented, or given the new policy of only releasing finished code in the CTPs they’ve not released anything because it’s not finished. I was expecting to see some new features in this release (along the lines of what Vidas Matelis blogs about here: but I’ll find out what’s happened.

Some news on the RS front though:

Reporting Services – New Server and Designer

Improvements represent the two major infrastructure changes for Reporting Services. Reporting Services enhances the processing engine and rendering extensions to enable new functionality, such as Tablix support, and scalability as well as remove the dependency on IIS.  Additionally, new report designer and configuration tool are provided that improve usability and workflow for RS customers.

Oh, and if you’re one of the few people who’ve been using Notification Services – bad luck, it’s dead:


5.1 SQL Server Notification Services Removed from SQL Server 2008

SQL Server Notification Services will not be included as a component of SQL Server 2008, but will continue to be supported as part of the SQL Server 2005 product support life-cycle. Moving forward, support for key notification scenarios will be incorporated into SQL Server Reporting Services. Existing Reporting Services functionality, such as data driven subscriptions, addresses some of the notification requirements. Features to support additional notification scenarios may be expected in future releases.

%d bloggers like this: