Microsoft SQL Server Analysis Services 2005/2008 articles - MDX, performance, management

DMVs in Analysis Service

webadmin@ssas-info.com (Amish Manubhai Shah) — Wed, 11 Nov 2009 02:59:47 +0000

As we all know that AS 2008 has come with many new features. I am using SQL Server since last 8 years and DMVs are great improvement in SQL Server 2005. Now exciting thing is that this DMVs are also in Analysis Service. Till now its not possible to view metadata in Analysis Service , but after 2008 its possible. We will explore this DMVs in coming articles.

Optimizing Basket Analysis

webadmin@ssas-info.com (Richard Lees) — Sun, 08 Nov 2009 20:18:41 +0000

Basket Analysis is the analysis of sales by basket (or transaction). It can be useful in understanding questions such as

What is the total value of sales for transactions that include products x (and y, and z...)?
What is the average transaction value for transactions that include products x (and y, and z...)?
What proportion of transactions include products x (or y or z)? And what proportion of transactions that include products a (and b, and c...) also include products x (and y, and z...)?
How did the attach rate of product x change during the sales promotion series? And was there a lasting effect.
etc. etc.

Traditionally basket analysis has been achieved by running SQL queries on very large, highly scalable, databases that dynamically summarize by transaction id.

ParallelPeriod + PeriodsToDate = Unexpected exception

webadmin@ssas-info.com (Konstantin Gorakine) — Sat, 07 Nov 2009 03:40:23 +0000

Apparently, I have no luck with ParallelPeriod() function again, but now, on how it works together with PeriodsToDate() function. This example is run on SSAS 2005 SP2 and I didn’t have chance to run it on SSAS 2008 yet, so very possible, it’s not an issue anymore.

A very harmless, at first sight MDX combination, left me in limbo and I wish I knew more about internals of query parsing and resolving process. Let’s say, I am collecting my data at the Week level and I want to calculate rolling measure by using PeriodsToDate function. My first calculation member looks like this:

Validating SSAS Projects Programmatically

webadmin@ssas-info.com (David Darden ) — Sat, 07 Nov 2009 03:37:42 +0000

Earlier this week I got a feature request from someone someone that was looking in to my SsasHelper sample on the Microsoft SQL Server Community Samples: Analysis Services site on CodePlex. She was interested in Building a SQL Server Analysis Services .ASDatabase file from a Visual Studio SSAS Project, but pointed out that I didn’t actually *validate* a project before creating the .ASDatabase file, so if someone had checked in a project with errors, the whole process could blow up (or we might deploy a database with some issues). I looked into doing this, and it turns out it’s really easy to accomplish. I updated the code in SsasHelper sample on CodePlex to show how to do this.

The actual code is really simple:

SQL 2005 (SQL 2008) Analysis Services Server Side Tracing

webadmin@ssas-info.com (Karan Gulati) — Sat, 07 Nov 2009 03:33:09 +0000

With Analysis Services 2005 (2008) you can collect traces without using gui this feature is known as Server Side Tracing.

In this article we’ll talk about how to achieve Server Side Tracing.

How to create a Server Side Trace

Open SQL Server profiler connect to Analysis Services and in an Event Selection define the SSAS Events.

Improving Cube Performance with Precalculated Aggregations

webadmin@ssas-info.com (Johan Åhlen) — Sat, 07 Nov 2009 03:25:45 +0000

Introduction

Having seen a number of Microsoft Business Intelligence implementations, I have noticed that surprisingly few use precalculated closing balances and semi-additive measures in SQL Server. I think this may be partly because of a lack of good examples available, and therefore decided to write about this issue.

This article is mainly divided in the following sections:

Background describing a simple example of closing balances
Description of our example data - a Balance Sheet
Step by step how to precalculate closing balances and build a cube for our Balance Sheet
Test results and conclusion

Live Blogging @PASS - SSAS Consolidation and Virtualisation

webadmin@ssas-info.com (Chris Webb) — Sat, 07 Nov 2009 03:19:29 +0000

Here are some notes from the SQLCat team’s session on SSAS consolidation and virtualisation; they’re a bit fragmentary since I’m too busy paying attention to what’s being said! I get asked about these issues by my customers all the time.

Use Windows System Resource Manager to control how many resources SSAS can use. For more on WSRM see http://technet.microsoft.com/en-us/library/cc755056.aspx
If SSAS and SQL are on the same server, use the Shared Memory protocol to improve processing performance
Also since resource usage requirements for SSAS and SQL will be different when processing and when querying, if they’re on the same box you can use WSRM to dynamically change resource allocations at different times.

Building Your First Cube

webadmin@ssas-info.com (Stacia Misner) — Sat, 07 Nov 2009 03:11:42 +0000

I like to introduce people to Microsoft SQL Server Analysis Services by creating a simple cube that contains a sampling of their own data. I have to be careful when I do this, though,
because I have been pushed out of my chair on more than one occasion when new users got excited about what they were seeing and wanted to dive right in to explore the data in ways they never before thought possible. Whether you have data of your own you want to explore more easily or a data-hungry user community to support, you'll find SQL Server Analysis Services (SSAS) provides the tools.

This article is the third installment of a series; the first two articles appeared in the August 2009 issue and are available at technet.microsoft.com/magazine/ee263919.aspx. This series will help you develop a better understanding of business intelligence (BI) by using the components available in Microsoft SQL Server 2008 as you build a simple BI solution. Because I'll be referring to concepts and terminology introduced in the previous two articles, I recommend you take the time to review those articles first if you haven't read them already.

In "Planning Your First BI Solution," I explained the advantages of building a data mart compared with retrieving data directly from a corporate database. I also provided instructions for downloading the sample databases you'll need in order to follow the steps described in these articles. Then Derek Comingore, in "Building a Data Foundation for a BI Solution," explained how to use Integration Services to populate a data mart with data from your corporate database. In this article, I'll show you how to build a cube based on a similar data mart design.

Simple Banding function for KPI Status or KPI Trend

webadmin@ssas-info.com (Hilmar Buchta) — Sat, 07 Nov 2009 02:58:56 +0000

Although the way of defining the KPI status and trend is very flexible it is also somehow circumstantial if you simply want to rate a KPI by its target value. In this case you would have to calculate the status of the KPI as being between –1 (worse) and +1 (best).

In many cases we find simple case statements here:

case
when KPIVALUE("TestKPI") >= 0.95 * KPIGOAL("TestKPI") then 1
when KPIVALUE("TestKPI") < 0.7 * KPIGOAL("TestKPI") then -1
else 0
end

Partition management using AMO

webadmin@ssas-info.com (Frank Kearney) — Thu, 05 Nov 2009 00:40:02 +0000

Partition management using AMO

When designing how I was going to process my SSAS database there were a few things that I wanted to accomplish:

1) Use AMO

2) Use SSIS

3) Create logs for troubleshooting

4) Make it as automated as possible

Since I was relatively new to AMO I started scouring the web. I happened upon a posting on www.ssas-info.com by Vidas Matelis that gave a pretty good baseline for an AMO project to process partitions. I took that baseline code and with a little bit of help from Vidas himself, was able to setup what I feel is a very nice process and something that is very portable.

One note, the environment I was creating this for has one large measures group that was partitioned by Year and Fiscal week and 5 other measures groups that were smaller and had only one partition.

Setting up the database

To begin with I wanted to automate the processing. This meant not only automating the run time (which is easy with SQL Service Agent) but also automating when a new partition was created and what partitions were processed daily. To do this, I set up a partition metadata table. This table contains all of the information about each partition in the cube

CREATE TABLE [SSAS_PARTITION_META]

( [Partition] [varchar](100) NOT NULL,

[CubeName] [varchar](100) NOT NULL,

[MeasureGroup] [varchar](100) NOT NULL,

[StorageLocation] [nchar](20) NULL,

[SQL] [varchar](4000) NOT NULL,

[NeedsReProcessing] [bit] NOT NULL,

[NeedsCreating] [bit] NOT NULL,

[CreateDate] [datetime] NULL,

[ProcessBeginDate] [datetime] NULL,

[ProcessEndDate] [datetime] NULL)

ON [PRIMARY]

Partition – The name of the partition

Cube Name – The name of the cube

Measure Group – the name of the measure group for the partition

Storage Location – the name of the drive\folder that I want the partition stored on

SQL – The SQL query used in the query binding

NeedsReProcessing – 0 if no, 1 if yes

NeedsCreating – 0 if no, 1 if yes

CreateDate – Date the partition was created

ProcessBeginDate – datetime the partition was last processed (begin)

ProcessEndDate – datetime the partition was last processed (end)

I have also created a stored procedure which runs each day to insert a new record into the partition metadata table if a new partition needs to be created. Note that values in < > need to be modified to fit your environment

CREATE procedure [dbo].[sp_insert_new_cube_partitions]

/*declare your variables*/

declare @week varchar(2)

declare @year varchar(4)

declare @StorageLocation varchar(20)

declare @partition as nvarchar(50)

declare @SQL as nvarchar(4000)

set @week = (select case when LEN(FSCL_WK_YR_NBR) < 2 then '0'+FSCL_WK_YR_NBR ELSE FSCL_WK_YR_NBR

end as FSCL_WK_YR_NBR from D_DT where DT = convert(varchar(10),GETDATE(),101))

set @year = (select FSCL_YR from D_DT where DT = convert(varchar(10),GETDATE(),101))

set @partition = '<Partition root name> ' + @year + ' ' + @week

/*CASE statement to determine partition location on disk. All partitions within a year will reside on the same disk */

set @StorageLocation = Case

When SUBSTRING(@partition,13,4) = '2009' then 'H:\'

when SUBSTRING(@partition,13,4) = '2010' then 'G:\'

Else 'G:\'

End

set @SQL = ' < INSERT QUERY FOR PARTITION HERE> '

IF NOT EXISTS (Select partition from SSAS_PARTITION_META where Partition = @partition)

BEGIN

insert into SSAS_PARTITION_META (Partition,CubeName,MeasureGroup,StorageLocation,SQL,NeedsCreating,NeedsReProcessing)

Values

(@partition,

'<CUBE NAME>',

'<Partition root name>',

@StorageLocation,

@SQL,

END

This stored procedure runs each day as part of the Create Partitions package to make it simple.

The Create Partition package

The create partition SSIS package is pretty simple. It consists of 3 variables and 5 steps

Variables

DaystoKeep – This variable is an Int32 and simply holds the value for the number of days you want to keep the log files

Log Location – Holds the location of the log files

PartitionListInDW – This variable is a datatype of Object. It holds the partition list that is passed into the AMO task

Tasks

Cleanup Old log files – This step executes some fairly simple VB.NET code to that goes out to the log directory defined in the Log location variable and deletes and .TXT files that are older than the value in DaystoKeep variable

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.IO

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _

<System.CLSCompliantAttribute(False)> _

Partial Public Class ScriptMain

Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

Enum ScriptResults

Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success

Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

End Enum

Public Sub Main()

Dim LogLocation As String

Dim DaysToKeep As Int32

LogLocation = CStr(Dts.Variables("LogLocation").Value)

DaysToKeep = Dts.Variables("DaysToKeep").Value

Dim folder As New DirectoryInfo(LogLocation)

Dim thresholdDate As Date = Date.Today.AddDays(-(DaysToKeep))

For Each file As FileInfo In folder.GetFiles("*.txt")

If file.CreationTime < thresholdDate Then

file.Delete()

End If

Dts.TaskResult = ScriptResults.Success

End Sub

End Class

Add Missing Partitions – this step executes the sp_insert_new_partitions stored procedure

Find partitions that need to be created – this step issues a sql query against the ssas_partition_meta table to get a list of rows where the NeedsCreating is set to 1. This value was updated when the sp_insert_new_partitions stored procedure was executed. IMPORTANT: This step uses an ADO.NET connection to the database since the results will be stored as an object that will be passed on to the Create partitions task

Create Partitions - This step executes AMO code to take the list of partitions that was found in the previous step and create the partitions on the SSAS server. It accepts the ReadOnlyVariable PartitionListInDW. See the code below

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.AnalysisServices

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _

<System.CLSCompliantAttribute(False)> _

Partial Public Class ScriptMain

Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

Enum ScriptResults

Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success

Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

End Enum

Public Sub Main()

Dim ExecutionSuccessfull As Boolean = True ' If true, package executed without errors

Dim dataBytes(0) As Byte

' Following objects are used to capture execution results

Dim oResults As Microsoft.AnalysisServices.XmlaResultCollection

Dim oResult As Microsoft.AnalysisServices.XmlaResult

Dim oMessage As Microsoft.AnalysisServices.XmlaMessage

Dim oPartitionList As Variable = Dts.Variables("PartitionListInDW") ' From variable

Dim oDS As DataSet = CType(oPartitionList.Value, DataSet)

Dim oTbl As DataTable = oDS.Tables(0)

Dim oRow As DataRow

Dim oConnection As ConnectionManager

oConnection = Dts.Connections("IBC_AS")

Dim sServer As String = CStr(oConnection.Properties("ServerName").GetValue(oConnection))

Dim sDatabase As String = CStr(oConnection.Properties("InitialCatalog").GetValue(oConnection))

Dim oServer As New Microsoft.AnalysisServices.Server

oServer.Connect(sServer) ' connect to the server and start scanning down the object hierarchy

Dim oDB As Microsoft.AnalysisServices.Database = oServer.Databases.FindByName(sDatabase)

If oDB Is Nothing Then

Dts.Log("Did not find expected database: " & sDatabase, 0, dataBytes)

ExecutionSuccessfull = False

GoTo Done

End If

Dim oCube As Microsoft.AnalysisServices.Cube

Dim oMeasureGroup As Microsoft.AnalysisServices.MeasureGroup

Dim oPartition As Microsoft.AnalysisServices.Partition

oServer.CaptureXml() = True

For Each oRow In oTbl.Rows

oCube = oDB.Cubes.FindByName(oRow("CubeName").ToString)

If oCube Is Nothing Then

Dts.Log("Did not find cube: " & oRow("CubeName").ToString, 0, dataBytes)

ExecutionSuccessfull = False

GoTo Done

Continue For

End If

oMeasureGroup = oCube.MeasureGroups.FindByName(oRow("MeasureGroup").ToString)

If oMeasureGroup Is Nothing Then

Dts.Log("Did not find measure group: " & oRow("MeasureGroup").ToString, 0, dataBytes)

ExecutionSuccessfull = False

GoTo Done

Continue For

End If

oPartition = oMeasureGroup.Partitions.FindByName(oRow("Partition").ToString)

If (oPartition Is Nothing) Then

Dts.Log("Need to create partition: " & oRow("Partition").ToString, 0, dataBytes)

oPartition = oMeasureGroup.Partitions.Add(oRow("Partition").ToString)

oPartition.StorageMode = StorageMode.Molap

oPartition.StorageLocation = oRow("StorageLocation").ToString()

oPartition.Source = New QueryBinding(oDB.DataSources(0).ID, oRow("SQL").ToString)

If oMeasureGroup.AggregationDesigns.Count > 0 Then '

oPartition.AggregationDesignID = oMeasureGroup.AggregationDesigns(0).ID ' Taking first available aggregation design!

End If

oPartition.Update(UpdateOptions.ExpandFull)

End If

oServer.CaptureXml() = False

' Executing log that contains all XMLA commands

oResults = oServer.ExecuteCaptureLog(True, False)

'Log the errors and warnings

For Each oResult In oResults

For Each oMessage In oResult.Messages

If oMessage.GetType.Name = "XmlaError" Then

'The processing failed

Dts.Log(oMessage.Description, 0, dataBytes)

ExecutionSuccessfull = False

Else

'Its just a warning.

ExecutionSuccessfull = True ' if you want to fail on warning, change this to False

End If

Next oMessage

Next oResult

' Finished code that creates new partitions

' ————————————————————————————————————————-

Done:

oServer.Disconnect()

If ExecutionSuccessfull Then

Dts.TaskResult = ScriptResults.Success

Else

Dts.TaskResult = ScriptResults.Failure

End If

End Sub

End Class

As Vidas explained in his blog, this code basically takes in the values from the partition_meta table and creates an XML script to create the partitions. That XML script is then executed against the SSAS server to create your partitions. For a more in-depth explanation, refer to Vidas’s blog at http://www.ssas-info.com/VidasMatelisBlog/11_ssis-package-that-process-all-partitionsmeasure-groupscubes-in-one-database

Update NeedsCreating flag – This step updates the partition_meta table and sets the NeedsCreating flag to 0 for the partitions that were just created.

Using this package, I am able to create over 100 partitions in less than 60 seconds.

Logging

I created custom logging which is written to a text file to capture the events during the execution of this package. I capture the following logging events

· OnError

· OnInformation

· OnPostExecute

· OnPreExecute

· OnProgress

· OnTaskFailed

· ScriptTaskLogEntry

This may be a little overkill, but it provides me with the information I need in the event of an error.

About Author:
Frank Kearney is business intelligence developer with over 10 years experience working with SQL Server and 8 years experience working with Analysis Services. You can find his blog at http://fkearney.spaces.live.com/