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 »

Today I participated in the Microsoft webcast “TechNet Webcast: Data Warehousing Enhancements in Microsoft SQL Server 2008” (Event Code: 1032344497) by Torsten Grabs. Some information in this webcast was about upcoming changes in SSAS 2008.

In SSAS 2008 we should expect MDX query performance improvement as now Analysis Services will be able to deal better with cube sparsity. Cube space is generally “sparse” – values only exists for small number of dimension intersections. SSAS 2005 evaluates expressions on complete space. With SP2 SSAS 2005 does some subspace computation, but in SSAS 2008 this is improved a lot. SSAS 2008 divides the space to separate calculated members, regular members and empty space. Then it can better evaluate cells that needs to be included in calculations.

 There was interesting information presented about SSAS backups. I did not know that that for SSAS 2005 backup time grows exponentially for databases over 20GB in size. This is fixed in SSAS 2008 and backup time growth now is linear. Also in SSAS 2008 Analysis Services backup storage subsystem will be replaced and all limitations on backup size will be removed.

Presenter also listed other upcoming SSAS improvements, but because of time shortage did not elaborate more:

• Query Tracing
• Writeback performance
• Read-only scalable database
• Resource monitoring 

 I understood that these changes will be included in the next CTP (CTP 4) that is expected in 2-3 weeks. Microsoft is planning to to CTP releases every 2-3 months.

Recently I was asked for a generic script that does backup of all Analysis Services 2005 databases on one server.

 I quickly wrote SSIS package that does just that. You can download this package from here.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
    Public Sub Main()
        Dim sSSASServerName As String = CStr(Dts.Variables(“SSASServerName”).Value)
        Dim sBackupLocation As String = CStr(Dts.Variables(“BackupLocation”).Value)
        If Right(sBackupLocation, 1) <> “\” Then sBackupLocation = sBackupLocation + “\”

        Dim Locations() As Microsoft.AnalysisServices.BackupLocation
        Dim oServer As New Microsoft.AnalysisServices.Server
        oServer.Connect(sSSASServerName) ‘ connect to the server and start scanning down the object hierarchy
        Dim oDB As Microsoft.AnalysisServices.Database
        For Each oDB In oServer.Databases
            ‘If oDB.Name = “AdventureWords” Then ‘ here you can include or excluded databases
            oDB.Backup(sBackupLocation & oDB.Name & “_” & Now().ToString(“yyyyMMdd_hhmmss”) & “.abf”, True, False, Locations, True)
            ‘End If
        Next
        Dts.TaskResult = Dts.Results.Success
    End Sub
 
End Class

If you wish you can use CaptureXML technique (described in my previous blogs) for doing backups in parallel, but I usually like to leave some server power to users so they can query data while backups are executed. Running multiple database backups in parallel could have huge performance impact for end user querying. 

You can execute this package from command line and pass parameters that specify server name and location:

dtexec /FILE “C:\DTSX_Scripts\DWBackupOLAPDBs.dtsx” /Set \package.variables[BackupLocation].Value;c:\NewBackupLocation\ /Set \package.variables[SSASServerName].Value;NewSSASServerName

When I have to write a script to backup one or a few databases with a known name, then I usualy use XMLA command:

<Backup xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”>
  <Object>
    <DatabaseID>MyDBName</DatabaseID>
  </Object>
  <File>c:\BackupLocation\MyDBName.abf</File>
</Backup>

 This XMLA script could be executed using one of these methods:

• SSIS package using control flow item “Analysis Services Execute DDL task”
• SQL Server Job create step with type “SQL Server Analysis Services command”
• using ascmd.exe utility.

SQL Server 2005 Best Practices Analyzer (BPA) was available as CTP already for some time, but just a few days ago Microsoft finally released it. I tested how this tool works with Microsoft SQL Server Analysis Services 2005.

Download is quite small – just about 2MB. Installation was trivial – just a few questions.  After installation when you start BPA, you can configure what do you want to scan and then run this scan right away or schedule it to run at certain time(s). For my test I choose Analysis Services computer and then SSAS database. I started scanning process that was pretty quick – less than 1 min.  So I started to investigate what this tool was checking.

There were 2 types of scans done: service level scan and database level scan. Read the rest of this entry »

I posted this in SSAS 2008 forum, but decided also to post it here.

I did some testing with Analysis Services 2008 June CTP and found following:

• No problems restoring databases that were archived in SSAS 2005.
• I like new dimension wizard and can see myself using it more, as I have more control on how attributes are created.
• June CTP can be used to test how my database is holding to “best design practices”, as I can see all the warnings after database is restored.
• Adventure works database that is shipped with Katmai June CTP has a lot of warnings. I know that this is just a warning and not error, but when I open Adventure Works DW cube, I keep getting distracted by all these blue lines in almost every dimension.

Read the rest of this entry »

Just participated in chat session on Katmai release and found some new information on SSAS 2008. Full transcript of this chat will be published shortly here.

Some interesting Q/A below:

• Q: What are improvements to Analysis Services 2008? I saw new feature visual attribute relationship in Dimension Editor, and also I saw “Design warning” interface where you can enable/disable design warnings. Anything else we should check in SSAS 2008?
  Rob Zare [MSFT] (Expert):
  A: This CTP includes a redesigned dimension wizard, a new attribute relationship designer, and real-time design/best practice warnings. The warnings are implemented in AMO, exposed in the UI via blue squiggly lines, and can be dismissed individually (a single occurrence) or turned off all together.

Read the rest of this entry »