Vidas Matelis Analysis Services Blog

Microsoft just released new best practice paper “Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services” written by Carl Rabeler and Eric Jacobsen. I just started to read it, but I am very impressed. It describes how to use SQL Server Profiler, System Monitor, Windows Task Manager, Kernrate, Process Monitor and MDX Script Performance Analyzer to analyze MDX queries. For SQL Server Profiler this document lists what events to track and what values actually mean. For System Monitor it describes counters that could be useful for MDX query analysis. Document also describes how to establish if bottlenecks are in storage or formula engine.

Then document shows examples on how actually perform analysis on sample queries, explaining all results and provides recommendations on how to improve MDX query performance.

Amongst other examples there are suggestions on how to do Ranking over sets and demonstrates performance difference between CASE and IIF statements.

I just started to read this document, but already saw a lot of very useful information. You can find this paper here.

Mosha Pasumansky just posted blog entry about dynamic named sets in SSAS 2008. In his post Mosha listed 4 SSAS changes that were included in June CTP release:

• Dynamic named sets
• CREATE MEMBER statement extension to allow specify display folder and associated measure group
• New CREATE KPI statement
• Ability to change calculated member “on the fly”

As Mosha focused on changes with Dynamic named sets, I decided to do a quick test on other 3 changes. To be honest, just from Mosha’s blog entry I realised that these MDX changes are already in CTP. I knew that some of these changes were planned, but from webcasts and chat sessions I was under impression that these changes will be available just in the future releases. Is it possible that these changes were included just in July CTP?

So here are my tests on other changes. 

Read the rest of this entry »

In my last post I listed changes that are expected in SSAS 2008 next CTP 4. But it looks like none of the changes made into July CTP. They probably were postponed last minute. Even new Microsoft documentation (read Chris Web  blog) lists some of the features as present. For example block computation is listed in documentation, but just today in webcast I saw block computation example where in CTP4 it took query to execute 1min, and in new SSAS build the same query come back in a few seconds. So I hope that all changes listed in my previous post will be in the next CTP5, that is targeted for September 18th release (during PASS conference).

Today there was another good webcast presentation “The value of Business Intelligence with SQL Server 2008” by Julie Strauss (Product Manager) & Donald Farmer (Principal Program Manager). From this presentation I got more info on 2 new enhancements (not listed in my previous posts) that we should expect in the next CTP.

Read the rest of this entry »