Vidas Matelis Analysis Services Blog

Years ago I was using Windows Scripting Host (WSH) for majority of my scripting tasks. It is installed by default on all Windows PCs, VBScript language is quite easy to learn/use and you can do a lot with just a few lines of code. But since DTS was introduced, I started to use DTS more and more and now SSIS is default environment where I do all my scripting. This is because SSIS has very good error loging system and is so much more powerful. But I still use a few WSH scripts that gives me quick status of my SSAS databases. To test them in your environment just copy script to text file, name it with extension *.vbs and then run it with command “CScript YourScript.vbs” with required parameters.

I have no problems running these scripts on any of my workstation PCs. But when I tested these cscripts on my server machines I was getting error message “Microsoft VBScript runtime error: ActiveX component can’t create object ‘Microsoft.AnalysisServices.Server'”. Maybe I am getting this error message because my servers are 64bit, or I might require to manually register some dll(s). If anyone knows how to make it work on server, I would appreciate if you’ll leave your comments here.
Updated: Few hours after I posted this blog I got solution from Andreau to this problem. On 64 bit environment I have to execute script using command: “c:\windows\syswow64\cscript.exe YourScript.vbs”. Thanks Andreau!

Here are 2 scripts that you might find useful: Read the rest of this entry »

While writing SSIS package to load one of my data warehouse tables, I encountered SSIS problem that took me some time to figure out. I noticed that in the dataflow task one of the OLE DB source components was not returning any data even I know that records were there. So I did some investigation and found what was causing this issue.

I found that when following conditions are met, OLE DB Source component will not return any data:

• “OLE DB Source” component is based on the SQL Query
• SQL Query contains parameters
• SQL Query contains line that starts with comments symbol “–” 

 Example, this query (database “Adventure Works DW”) works:

SELECT CurrencyKey
  FROM dbo.DimCurrency
WHERE CurrencyKey BETWEEN ? AND ?

But this query always returns empty result set:

SELECT CurrencyKey
FROM dbo.DimCurrency
— my comments
WHERE CurrencyKey BETWEEN ? AND ?

Read the rest of this entry »

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 »