Vidas Matelis Analysis Services Blog

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 »