Microsoft Fabric, Power BI, Analysis Services, DAX, M, MDX, Power Query, Power Pivot and Excel
This post on the MSDN forum:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2064643&SiteID=1
…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:
SCOPE({[Measures].[RegularMeasure],[Measures].[CalculatedMeasure]});
this=1;
END SCOPE;
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:
SCOPE([Measures].[RegularMeasure]);
this=1;
END SCOPE;
SCOPE([Measures].[CalculatedMeasure]);
this=1;
END SCOPE;
…but this is clearly a pain, as you’re duplicating your assignment logic. What you can do instead is this:
({[Measures].[RegularMeasure],[Measures].[CalculatedMeasure]})=1;
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…
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:
http://www.sqlserverfaq.com/
There’s also a whole load of new stuff up at the SQLBits site, including some sessions (though not mine yet) and speakers:
http://www.sqlbits.com/
You can register for the conference here:
http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032349295&Culture=en-GB
I see Vidas Matelis has picked up some more useful snippets of new features in AS2008 from a recent webcast:
http://www.ssas-info.com/VidasMatelisBlog/29_ssas-2008-katmai-info-from-august-9th-webcast#more-29
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:
https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=6194
PerformancePoint CTP4 has been released and can be downloaded from Connect. David Francis has a feature list:
http://blogs.conchango.com/davidfrancis/archive/2007/08/16/PerformancePoint-CTP4-now-available.aspx
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.
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:
http://withnolock.com/communityserver/blogs/dirtyread/archive/2007/08/06/new-mdx-editor.aspx
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.
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:
http://www.microsoft.com/sql/techinfo/whitepapers/hosting_sql_2008_bi.mspx
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!).
You can get it from Connect, as always:
https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5395
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: http://www.ssas-info.com/VidasMatelisBlog/28_ssas-2008-katmai-upcoming-changes-in-the-next-ctp) 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:
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.