Archive for the ‘Uncategorized’ Category.

Using PowerShell to Manipulate SQL Server Analysis Services Traces

I recently started using SSAS Server Traces a lot with SQL Server Analysis Services.  This type of trace is basically the same trace you can create with SQL Server Profiler, but it runs without Profiler, uses less resources, and can be persisted across reboots.  They’re a really handy tool.

I started using these when I built some AS monitoring tools based on the “Solution for Collecting Analysis Services Performance Data for Performance Analysis”  sample on CodePlex.  Seriously, totally revolutionized my life (at least the part related to administering complex AS installations and projects).  After installing, adapting, and enhancing the functionality there I found I wanted more and easier ways to control AS traces, so I built some PowerShell functions to help manage them.  These functions basically just wrap XMLA commands to make them easier to use.

Here are some sample files I’ll be talking about in this post:  Download Sample Files

Don’t worry about copying the sample code out of the post… it’s all included as part of the sample.

Creating SSAS Traces

You use a XMLA command to create a trace.  A trace looks something like this:

1: <Batch xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine” xmlns:soap=”http://schemas.xmlsoap.org/soap/envelope/”>

   2:   <Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

   3:     <ObjectDefinition>

   4:       <Trace>

   5:         <ID>My Trace</ID>

   6:         <Name>My Trace</Name>

   7:         <Events>

   8:           <Event>

   9:             <EventID>15</EventID>

  10:             <Columns>

  11:               <ColumnID>28</ColumnID>

  12:               <!-- ... More Columns ... -->

  13:               <ColumnID>3</ColumnID>

  14:             </Columns>

  15:           </Event>

  16:           <Event>

  17:             <EventID>16</EventID>

  18:             <Columns>

  19:               <ColumnID>24</ColumnID>

  20:               <!-- ... More Columns ... -->

  21:               <ColumnID>36</ColumnID>

  22:             </Columns>

  23:           </Event>

  24:           <!-- ... More events ... -->

  25:         </Events>

  26:         <Filter>

  27:           <NotLike>

  28:             <ColumnID>37</ColumnID>

  29:             <Value>Application I don't care about events from</Value>

  30:           </NotLike>

  31:         </Filter>

  32:       </Trace>

  33:     </ObjectDefinition>

  34:   </Create>

  35: </Batch>

Not the most fun to create by hand, but you could make it happen.  However, there is an easier way to come up with the CREATE statement for your trace.  Just do the following:

  1. Start up a SQL Server Profiler session and monitor the AS instance you’re working on.  You only need to capture the Command Begin event.
  2. Start up a 2nd instance of SQL Server profiler.  Use the GUI to create the trace you’re actually interested in, with all the events, columns, and filters.  Then start the trace.
  3. Snag the CREATE XMLA from the 1st Profiler section and save it off.

Now you have XMLA you can use as the base for the trace you want.  You’ll want to add a few more elements to the XMLA to make the server trace work though.  It will look something like this:

1: <Batch xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine” xmlns:soap=”http://schemas.xmlsoap.org/soap/envelope/”>

   2:   <Create mlns="http://schemas.microsoft.com/analysisservices/2003/engine">

   3:     <ObjectDefinition>

   4:       <Trace>

   5:         <ID>My Trace</ID>

   6:         <Name>My Trace</Name>

   7:         <LogFileName>\\MyServer\TraceFiles\MyTrace.trc</LogFileName>

   8:         <LogFileAppend>0</LogFileAppend>

   9:         <AutoRestart>1</AutoRestart>

  10:         <LogFileSize>100</LogFileSize>

  11:         <LogFileRollover>1</LogFileRollover>

  12:         <Events>

  13:           <!-- ... The rest of the Create statement you just generated ... -->

There are just a few extra fields there.  Here’s what they’re used for:

LogFileName Name of the log file.  Must end in .trc.  The AS Service Account must have permission to write to the directory.
LogFileAppend 0 for Overwrite, 1 for Append.
AutoRestart 0 for No, 1 to restart when the server restarts.
LogFileSize Size in MB.  The log will roll over when it reaches this size.
LogFileRollover 1 means create a new log file (it appends 1, 2, 3, etc. for each new log) when you reach the LogFileSize.

Deleting SSAS Traces

So, we’ve created a trace that auto restarts.  How do you get rid of it?

1: <Batch xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine” xmlns:soap=”http://schemas.xmlsoap.org/soap/envelope/”>

   2:   <Delete xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">

   3:     <Object>

   4:       <TraceID>My Trace</TraceID>

   5:     </Object>

   6:   </Delete>

   7: </Batch>

SSAS Trace PowerShell Library

I found that I wanted a convenient way to see what traces were running on a server, create them, delete them, and flush them (i.e., close out the current trace and create a new one, so you can process or otherwise work with events that were just logged).  I have included two versions of my library in this sample.  This first (SsasTraceLibrary.ps1) runs with PowerShell V1.  The second (SsasTraceV2Library.ps1) is basically identical, but uses function header and parameter functionality from PowerShell V2 CTP3.  I keep the V1 version around to deploy to servers (more on this later), but load the V2 in my environment to take advantage of the examples, help, and all of the other V2 goodness.  I would encourage you to go with the V2 version, as it includes easy to use descriptions, examples, and better parameter help.

I created the following functions as part of this library:

Function Description
Get-SsasTrace Get details of a specific trace
Get-SsasTraceExists Check if a specific trace exists
Get-SsasTraces Get all traces running on a server
Start-SsasTrace Start a new trace based on a stored template
Delete-SsasTrace Delete an existing trace
Flush-SsasTrace Stop/Restart an existing trace

A Sample Function

Most of the functions in this library require the SSAS assemblies

1: # Load Required SSAS Assemblies

   2: $asm = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

   3: $asm = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.Xmla")

You typically follow the pattern of connecting to a server, executing XMLA command, outputting the results, and disconnecting from the server.

1: # Connect to the server

   2: $xmlaClient = new-object Microsoft.AnalysisServices.Xmla.XmlaClient

   3: $xmlaClient.Connect($serverName)

   4:

   5: $xmlStringResult = "" # Initialize the variable so that it can be passed by [ref]

   6:

   7: # Fire off the discover command to return all traces

   8: $xmlaClient.Discover("DISCOVER_TRACES", "", "", [ref] $xmlStringResult, 0, 1, 1)

   9:

  10: # Convert the result to XML to make it easier to deal with

  11: $xmlResult = $xmlStringResult

  12:

  13: return $xmlResult.return.Root.row

  14:

  15: # Disconnect the session

  16: $xmlaClient.Disconnect()

Occasionally we want to work with the XML result set a little bit to verify the results, but usually nothing major.

   1: # Create the trace

   2:  $xmlaClient.Execute($createTraceXmla, "", [ref] $xmlStringResult, 0, 1)

   3:

   4:  # Convert the result to XML to make it easier to deal with

   5:  $xmlResult = $xmlStringResult

   6:

   7:  $xmlResultException = $xmlResult.return.results.root | ? {$_.Exception -ne $null}

   8:

   9:  if ($xmlResultException -ne $null)

  10:  {

  11:    throw $xmlResultException.Messages.Error.Description

  12:  }

The PowerShell is really just a wrapper around XMLA commands… it just makes it easier to use.

Using the SsasTraceLibrary.ps1 in the Dev Environment

I’ve found I use these functions a decent bit as part of my day to day operations.  I have the following command in my Profile.ps1 to load all the script files ending with “Library.ps1” in a given directory… I store command libraries like SsasTraceLibrary.ps1 in this folder, so they’re automatically loaded when PowerShell starts.

1: $powerShellScriptsDirectory = “c:\PowerShellScripts\”

   2: if (!$powerShellScriptsDirectory.EndsWith("\")) { $powerShellScriptsDirectory += "\" }

   3:

   4: Write-Host Welcome $Env:Username

   5:

   6: foreach($filename in Get-ChildItem $powerShellScriptsDirectory* -Include "*Library.ps1")

   7: {

   8:     & $filename

   9: }

Now, you just have to start PowerShell and run a command like

1: Get-SsasTraces LocalHost

to return all the traces running on your local machine.

Using the SsasTraceLibrary.ps1 in the Server Environment

I mentioned earlier that I also deploy this script to my various AS instances.  I do this because various people need to work on the machine, and I want an easy (read: single click) way to do things like start/stop/flush the trace on the machine.  This also makes it easy to automate these actions as part of an ETL or job.

I use a batch file with the following commands:

1: ECHO Setting System Variables

   2: SET DATA_COLLECTION_PATH=[INSTALLDIR]

   3: SET SSAS_TRACE_UNC=\\[OLAPSERVER]\[TRACE_FILE_SHARE_NAME]\[OLAPSERVER]_SsasPerformanceErrorMonitoringTrace.trc

   4: SET SSAS_SERVER=[OLAPSERVER]

   5: SET SSAS_TRACE_FILE_SIZE_MB=100

   6:

   7: ECHO Running Commands

   8: REM: Create the Data Collection Trace File

   9: PowerShell -Command "& {&'%DATA_COLLECTION_PATH%\DataCollection\SsasTraceLibrary.ps1'; Start-SsasTrace -ServerName '%SSAS_SERVER%' -TraceID 'Analysis Services Performance and Error Trace' -TraceName 'Analysis Services Performance and Error Trace' -UncFileName '%SSAS_TRACE_UNC%' -FileSizeInMB '%SSAS_TRACE_FILE_SIZE_MB%'}"

  10: ECHO Script Complete!

  11: pause

The parameters encased in ‘[‘ and ‘]’ are replaced whenever the scripts are deployed to a server with variables specific to their environment.  Someone can now just run one of the batch files to Start, Stop, or Flush a trace on the server.  I also typically call the file to Flush the trace file as part of my Processing job, so I can immediately load the results into a database for analysis.

Performance

So a question that will always come up when running traces like this is the amount of overhead they require.  And of course they require some, both in terms of CPU to log the events and Disk to write them.  I’ve typically seen this to be in the single digits of CPU, and I always write to a location where there isn’t disk contention.  You’ll of course want to test in your environment, but I haven’t seen a performance hit that makes the ROI of running these traces not worth it.  If you’re concerned, you could consider turning them on/off as part of a scheduled job, or just running them on an as needed basis.  Personally, I’ve seen a huge benefit from running them 24/7 as I capture detailed processing information (how long each step takes), query information (who is doing what, how bad, and how often) and error information (some errors that aren’t caught in any other logs are captured via traces).

Next Steps

Takes these libraries and modify to your heart’s content.  I use a template in the scripts that is my standard, but you can replace it, add more, or whatever you want to do.  You could also add a little bit better error handling if desired.

Conclusion

So, included here are some functions that will help you with some basic functionality around SSAS traces.  Feel free to post back if you have any ideas for improvements or things that would be cool to do.

Cheers,

David

New Features for Visual Studio Team System 2008 Database Edition

Today I was creating a new project in Studio Team System 2008 Database Edition, and needed to import a SQL Server 2008 DB.  I went looking for a SQL Server 2008 template for Visual Studio… instead, I found the Microsoft® Visual Studio Team System 2008 Database Edition GDR that was release a few weeks ago.  With this General Distribution Release (GDR), you get support for SQL Server 2008 as well as a number of cool features.  If you’re using VSTS Database Edition, definitely check it out…

How to implement cascading parameters in a SQL Services Reporting Services MDX Report

Background

Last week, I needed to prototype a report against an Analysis Services 2005 cube for a client.  The report wasn’t too complicated… I just had the requirement to allow a user to drill down a hierarchy by selecting a member at one level, then displaying all the children for that member, etc.  This is really easy to do in SSRS… but I wouldn’t go so far as to call it super intuitive.  It had been awhile since I wrote a MDX report, and it took me a few minutes to remember just how to do it… so I thought I’d document the steps, for my own benefit if no one else’s.

The sample requires the Adventure Works AS DB.  You can download the sample here.

Scenario

So, I want to create a report that returns the Order Count by Product.  The users need to select a particular Category, and then Subcategory for the products they want to see.  The report also needs to return summary information for both the Category and Subcategory level, and it needs to contain the Category and Subcategory on the report.  This can be a pretty standard business case for reports where the potential data set is to large to report on.

Underlying Data

In order for this to work, I need a hierarchy to drill down.  Here, I’m going to use the Product Categories hierarchy of the Product dimension in the Adventure Works DW.

Step1_thumb

The Report

Next, I’m going to create a data set to use in my report.  I’m going to go ahead and switch to the MDX view for the data set.  Now, I’m going to go ahead and create a few parameters.

Step2_thumb2

Now, I’m going to go to the Report Layout tab,  right-click off the report, and choose to view my parameters.  Yup, they’re there.  Now, I’m going to go back to the Data tab, and look at my list of available data sets…

Step3_thumb

Cool!  BIDS just created two new data sets for me, based on the parameters I created.  Note that you have to view your Report Parameters on the Layout tab for these data sets to be created (you just have to open the dialog, you don’t have to do anything).  This seems to be the part that I always forget.

Let’s look at the ParameterCategory data set first.  The MDX that BIDS created looks like this:

   1: WITH MEMBER [Measures].[ParameterCaption] AS '[Product].[Product Categories].CURRENTMEMBER.MEMBER_CAPTION' 
   2: MEMBER [Measures].[ParameterValue] AS '[Product].[Product Categories].CURRENTMEMBER.UNIQUENAME' 
   3: MEMBER [Measures].[ParameterLevel] AS '[Product].[Product Categories].CURRENTMEMBER.LEVEL.ORDINAL' 
   4: SELECT 
   5:     {
   6:         [Measures].[ParameterCaption]
   7:         , [Measures].[ParameterValue]
   8:         , [Measures].[ParameterLevel]
   9:     } ON COLUMNS , 
  10:     [Product].[Product Categories].ALLMEMBERS ON ROWS 
  11: FROM 
  12:     [Adventure Works]

But when we run that, it returns everything in the hierarchy… including that nasty ‘All Products’, which we don’t want our users selecting.  So, we’ll make a slight modification, and change ‘[Product].[Product Categories].ALLMEMBERS’ to ‘[Product].[Product Categories].Children’.  Now, we get just the Categories that we want our users to select.

Now, we want to look at the ParameterSubCategory dataset.  The MDX looks like this:

   1: WITH MEMBER [Measures].[ParameterCaption] AS '[Product].[Product Categories].CURRENTMEMBER.MEMBER_CAPTION' 
   2: MEMBER [Measures].[ParameterValue] AS '[Product].[Product Categories].CURRENTMEMBER.UNIQUENAME' 
   3: MEMBER [Measures].[ParameterLevel] AS '[Product].[Product Categories].CURRENTMEMBER.LEVEL.ORDINAL' 
   4: SELECT 
   5:     {
   6:         [Measures].[ParameterCaption]
   7:         , [Measures].[ParameterValue]
   8:         , [Measures].[ParameterLevel]
   9:     } ON COLUMNS , 
  10:     [Product].[Product Categories].ALLMEMBERS ON ROWS 
  11: FROM 
  12:     [Adventure Works]

Again, not precisely what we want… plus, we want this one to be parameter driven.

First, we need to add the ParameterCategory (re-use the original Parameter name to keep an extra data set from being created later) to the report:

Step4_thumb3

Then, we change ‘[Product].[Product Categories].ALLMEMBERS’ to ‘STRTOMEMBER(@ParameterCategory).Children’.  Now, our MDX looks like this:

   1: WITH MEMBER [Measures].[ParameterCaption] AS '[Product].[Product Categories].CURRENTMEMBER.MEMBER_CAPTION' 
   2: MEMBER [Measures].[ParameterValue] AS '[Product].[Product Categories].CURRENTMEMBER.UNIQUENAME' 
   3: MEMBER [Measures].[ParameterLevel] AS '[Product].[Product Categories].CURRENTMEMBER.LEVEL.ORDINAL' 
   4: SELECT 
   5:     {
   6:         [Measures].[ParameterCaption]
   7:         , [Measures].[ParameterValue]
   8:         , [Measures].[ParameterLevel]
   9:     } ON COLUMNS , 
  10:     STRTOMEMBER(@ParameterCategory).Children ON ROWS 
  11: FROM 
  12:     [Adventure Works]

and it will only return the Children of the selected category.  You could also create your MDX using an expression, and directly use the the ‘Parameters!ParameterCategory.Value’ embedded in a quoted string.  There is a performance hit for using anything like STRTOMEMBER, but in this case it isn’t worth the trouble to me to do it that way.

Almost done.  Except that we haven’t gotten around to writing the actual query that drives the report…  so we go back to our main data set.

Now, when we create our query, we might be tempted to return the children of the Subcategory… but this won’t get us summary information at the Category and Subcategory levels.  So we’re going to write some MDX using the Hierarchize function to make sure our data set contains the selection at the Category level, at the Subcategory level, and all of the Products underneath the selected subcategory:

   1: SELECT
   2:     [Measures].[Order Count] ON 0,
   3:     HIERARCHIZE(
   4:         {
   5:             STRTOMEMBER(@ParameterCategory),
   6:             STRTOMEMBER(@ParameterSubCategory),
   7:             STRTOMEMBER(@ParameterSubCategory).Children
   8:         }
   9:     ) ON 1
  10: FROM
  11:     [Adventure Works]  

Now I’m going to go back to the Layout tab, right-click off the report, pull up my Report Parameters, and change the Default value to Null (so the user will always have to make a selection… though you can make this part as clever as you’d like).

Step5_thumb4

In your report, you may need to use some expressions, filters or other logic to format your results, suppress rows (i.e., the Category will have a Null for both Subcategory and Product… how you want to handle this is up to you).  For this sample, I just wrote a little VB

   1: Dim _Indent As String = "  "
   2:  
   3: Public Function FormatProduct(ByVal Category As String, ByVal SubCategory As String, ByVal Product As String) As String
   4:     Dim ret As String = String.Empty
   5:  
   6:     If Not String.IsNullOrEmpty(Product) Then
   7:         ret = _Indent & _Indent & Product
   8:     Else If String.IsNullOrEmpty(Product) And Not String.IsNullOrEmpty(SubCategory) Then
   9:         ret = _Indent & SubCategory
  10:     Else
  11:         ret = Category
  12:     End If
  13:     
  14:     Return ret
  15: End Function

And used an expression to format my Product column in the report.

Summary

That’s about it.  We now have a simple report with some cascading parameters.

MDX Filters in PPS Monitor

I had a question on how to implement MDX Filters in PerformancePoint Monitor awhile back.  Nick Barclay  provided an answer here.

Some samples Nick provided were:

   1: [Time].[Fiscal Year].Children

and

   1: DESCENDANTS(
   2:   [Geography].[Geography].[All Geographies]
   3:  ,[Geography].[Geography].[Country]
   4:  ,SELF_AND_BEFORE) 

Depending on how you have implemented your cube, you can also do some other clever things.  On a recent project, I had a Date dimension with a hierarchy that would present the current month as ‘Current Month’, so MDX such as the following :

   1: {[Dim Date].[Calendar].[Month Name].&[Current Month].Lag(6):[Dim Date].[Calendar].[Month Name].&[Current Month]}

could be used to provide the last 6 months contained in the cube as a drop down list:

   1: Nov 2007
   2: Dec 2007
   3: Jan 2008
   4: Feb 2008
   5: Mar 2008
   6: Current Month

Another useful possibility is to provide a filter based on a hierarchy, but an individual member (in this case the ‘Unknown’ member) should not be displayed in the pick list:

   1: FILTER(
   2:     DESCENDANTS(
   3:          [Entity].[Entity]
   4:         ,[Entity].[Entity]
   5:         ,SELF_AND_BEFORE
   6:     )
   7:     ,NOT [Entity].[Entity].CURRENTMEMBER IS [Entity].[Entity].&[Unknown]
   8: )