Presenting ‘Enterprise Class Analysis Services Development’ at the Vancouver PASS Chapter

Hey Guys!

I’m going to be giving a presentation on Enterprise Class Analysis Services Development at the Vancouver PASS Chapter Friday, March 12th. 

http://vancouverbi.sqlpass.org/Home/tabid/1551/Default.aspx

I’ll be talking about some of the topics I’ve blogged about here, including working with multiple developers, using a custom MSBuild task to build SSAS Databases, and reviewing a monitoring/reporting solution for SSAS.  If you don’t happen to be in Vancouver, you can always attend virtually.  :)

Title: Enterprise Class Analysis Services Development

SSAS is one of the most popular tools for OLAP, but many organizations experience challenges when attempting to use their standard development best practices with the tool. This session will cover many topics around enterprise development practices for SSAS, including how to effectively use source control with multiple developers, enable robust automated build/deployment strategies, implement usage monitoring and tracking solutions, and support unit testing for SSAS solutions.

Objective 1: Demonstrate how to use source control with multiple developers.

Objective 2: Show techniques to automate builds and enable robust deployment strategies.

Objective 3: Review strategies for robust monitoring of multiple SSAS deployments for development, administrative, and business purposes.

Using MSBuild with SQL Server Analysis Services Projects

I’ve written several blogs and community samples on working with SSAS Projects directly using AMO (instead of SSAS Databases on an Analysis Services server).  I was travelling this weekend, and got a chance to create a sample MSBuild task that will generate a .ASDatabase file directly from a Visual Studio project, without requiring Visual Studio itself.  This means that multiple developers can work on a project, check-in files via source control, and can schedule an automated build, build on a dedicated “clean” machine (without VS), or any of a number of other scenarios.

I added the custom MSBuild task to the Analysis Services Community Samples project on CodePlex under the SsasHelper sample.

The Build task code itself is trivial.  I won’t go into a lot of detail on that, has creating/debugging custom tasks is well documented (you can start the library with MSBuild.exe as the external program and the project file as the argument).  I used it as a wrapper for previously developed ProjectHelper code that does all the heavy lifting.  All we do is inherit from Microsoft.Build.Utilities.Task and implement the Execute method.  All I do in the task is de-serialize the project (based on the Visual Studio project passed in), validate the project (based on the target version of SSAS), and write out the .ASDatabase file.  This could of course be modified to use another method to deploy the database, but I’ve been using the .ASDatabase method for awhile with no issues.

Here’s the main code for the method:

   1: try

   2: {

   3:     Database database = ProjectHelper.DeserializeProject(SsasProjectFile);

   4:  

   5:     // ... Verify our project doesn't have any errors ...

   6:     ValidationResultCollection results;

   7:  

   8:     bool isValidated = ProjectHelper.ValidateDatabase(database, SsasServerEdition, out results);

   9:  

  10:     // If the database doesn't validate (i.e., a build error)

  11:     // log the errors and return failure.

  12:     foreach (ValidationResult result in results)

  13:     {

  14:         Log.LogError(result.Description);

  15:     }

  16:  

  17:     if (!isValidated)

  18:     {

  19:         return false;

  20:     }

  21:  

  22:     // Build the .ASDatabase file

  23:     ProjectHelper.GenerateASDatabaseFile(database, SsasTargetFile);

  24: }

  25: catch (Exception ex)

  26: {

  27:     Log.LogErrorFromException(ex);

  28:     return false;

  29: }

  30:  

  31: return true;

So… how do we actually use this?  I included a sample project file in the SsasBuilder project.  The basic idea is you have a project file (a file that ends in “.[optional prefix]proj”, such as “.proj”, “.csproj”, etc.).  You can call this via MSBuild.  Note that the standard SSAS project file DOES NOT work with MSBuild.  The schemas required for that project conflict with the MSBuild schema, so you’ll have to create another project file, or build the build step into somewhere else.  Here’s an example project file:

   1: <Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">

   2:     <UsingTask TaskName="SsasBuilder.SsasBuildASDatabaseFileTask" 

   3:         AssemblyFile="C:\TFS\SsasHelper\SsasBuilder\bin\debug\SsasBuilder.dll"/>

   4:     <Target Name="BuildASDatabaseFile">

   5:         <SsasBuildASDatabaseFileTask SsasProjectFile = "C:\Test\enterprise_Gold\Adventure Works DW 2008.dwproj"

   6:             SsasTargetFile = "C:\Test\SsasBuildTest\AdventureWorks.ASDtabase"

   7:             SsasServerEdition = "Enterprise" />

   8:     </Target>

   9: </Project>

Here I’m using absolute paths, but you can use either properties or relative paths as required for your particular project.  You just use a UsingTask tag to point to the assembly containing the build task, then use the task in a target.  For this sample I’m I’m taking the SSAS project file and target filename, along with the server edition, as parameters.  If there are no errors in the project file, the .ASDatabase file will be generated in the specified location.

Now, all you have to do is call “MSBuild.exe <Whateveryounamedyourproject>”, and you’ll get a .ASDatabase file out of it…

Cheers,

David

Validating SSAS Projects Programmatically

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:

   1: bool doesBuild = false;

   2: results = new ValidationResultCollection();

   3:  

   4: // We have to provide a ServerEdition for this method to work.  There are 

   5: // overloads that look like the will work without them, but they can't be used

   6: // in this scenario.

   7: // The ServerEdition might need to be changed for your situation.

   8: // This can be modified to return warnings and messages as well.

   9: doesBuild = database.Validate(results, ValidationOptions.None, ServerEdition.Developer);

  10:  

  11: return doesBuild;

You can use the method I created like so:

   1: Database database;

   2: bool hasErrors = false;

   3:  

   4: // Load a SSAS database object based on a BIDS project

   5: database = ProjectHelper.DeserializeProject(ssasProjectFile);

   6:  

   7: // ... Verify our project doesn't have any errors ...

   8: ValidationResultCollection results;

   9: hasErrors = ProjectHelper.ValidateDatabase(database, out results);

  10:  

  11: foreach (ValidationResult result in results)

  12: {

  13:     Console.WriteLine(string.Format("{0}", result.Description));

  14: }

  15:  

  16: Console.WriteLine(string.Format("Project is Error Free?  {0}", hasErrors));

  17:  

  18: Console.WriteLine("Project validated!");

This will take a Database object and validate it to see if there are any errors.  You can modify it to return Warnings and Messages as well by changing the Validation Options.

One thing to note is that the Server Edition is a required parameter.  There are a few overloads of the Validate method that don’t require this parameter, but what they try and do is walk up the object tree to get the Server object (associated with the Database) and retrieve the edition.  Since I’m de-serializing a project into a Database object, this property isn’t available (and can’t be set).  It is important to use this method with the correct Server Edition.  The validation process will throw errors if you use some (but I don’t think *all*) of the features for a different edition.  For example, if your project includes Translations (an Enterprise-only feature), validation will succeed if you use ServerEdition.Developer or ServerEdition.Enterprise, but will fail if you use ServerEdition.Standard.

Cheers,

David

SQL Server Analysis Services ‘Project Helper’

A little while back I spent an afternoon prototyping some functionality for Analysis Services.  I’ve been working a bit on improving the collaborative development story around SSAS, and I was after Building a SQL Server Analysis Services .ASDatabase file from a Visual Studio SSAS Project. I did come up with some nifty functionality in the process that I thought I’d share.

SsasHelper / ProjectHelper

I added the SsasHelper sample to the Microsoft SQL Server Community Samples:  Analysis Services site on CodePlex. 

The SsasHelper sample currently only contains the ProjectHelper library, which contains some functions to help work with a Visual Studio SSAS project. I also built a command line interface to demonstrate how to use the library to do a few tasks. There are a bunch of things you can use the library for, but the sample should at least get you started. The sample has the following functionality:

1. De-serialize a Visual Studio SSAS Project into an AMO Database, then re-serialize all the components (.cube, .dim, .dsv, etc.) back out to files. You could use this functionality to let you programmatically work with a AS DB.
2. Build a .ASDatabase file based on the Visual Studio SSAS Project. You could use this functionality as part of a robust build process.
3. "Clean" a Visual Studio SSAS Project for comparison/source code merges. This is basically the same functionality I wrote about here, just ported into this library.
4. Re-order a SSAS Project File. This is basically the "Smart Diff" functionality from BIDS Helper… I included it because De-serializing/serializing a project re-orders the .partition files, making them a pain to validate. I used this function to sort the .partition files to make them easy to diff.

Serialize/De-Serializing Visual Studio SSAS Projects

This is something I’d be wanting to figure out for awhile.  Most of this is really straight forward, with just one or two little tricks to make it work.  We start by reading the SSAS Project file… we just load up the XML Document and parse each set of objects. It looks like this:

   1: // Load the SSAS Project File

   2: XmlReader reader = new XmlTextReader(ssasProjectFile);

   3: XmlDocument doc = new XmlDocument();

   4: doc.Load(reader);

   5:  

   6: // Load the Database

   7: nodeList = doc.SelectNodes("//Database/FullPath");

   8: fullPath = nodeList[0].InnerText;

   9: innerReader = new XmlTextReader(ssasProjectDirectory + fullPath);

  10: Utils.Deserialize(innerReader, (MajorObject)database);

  11:  

  12: // Load all the Datasources

  13: nodeList = doc.SelectNodes("//DataSources/ProjectItem/FullPath");

  14: DataSource dataSource = null;

  15: foreach (XmlNode node in nodeList)

  16: {

  17:     fullPath = node.InnerText;

  18:     innerReader = new XmlTextReader(ssasProjectDirectory + fullPath);

  19:     dataSource = new RelationalDataSource();

  20:     Utils.Deserialize(innerReader, (MajorObject)dataSource);

  21:     database.DataSources.Add(dataSource);

  22: }

Loading each object type looks pretty much the same… the SSAS team provided that handy Utils class that helps to serialize and de-serialize objects.  There is, of course, one little wrinkle… the .cube and .partition files.  They don’t play by the same rules as the other objects.  Luckily, they’re close enough.  A few little hacks get us there.

The De-Serialize for cubes looks like this:

   1: // Load all the Cubes

   2: nodeList = doc.SelectNodes("//Cubes/ProjectItem/FullPath");

   3: Cube cube = null;

   4: foreach (XmlNode node in nodeList)

   5: {

   6:     fullPath = node.InnerText;

   7:     innerReader = new XmlTextReader(ssasProjectDirectory + fullPath);

   8:     cube = new Cube();

   9:     Utils.Deserialize(innerReader, (MajorObject)cube);

  10:     database.Cubes.Add(cube);

  11:  

  12:     // Process cube dependencies (i.e. partitions

  13:     // Little known fact:  The Serialize/Deserialize methods DO handle partitions... just not when 

  14:     // paired with anything else in the cube.  We have to do this part ourselves

  15:     dependencyNodeList = node.ParentNode.SelectNodes("Dependencies/ProjectItem/FullPath");

  16:     foreach (XmlNode dependencyNode in dependencyNodeList)

  17:     {

  18:         fullPath = dependencyNode.InnerText;

  19:         innerReader = ProjectHelper.FixPartitionsFileForDeserialize( ssasProjectDirectory + fullPath, cube);

  20:         Cube partitionsCube = new Cube();

  21:         Utils.Deserialize(innerReader, (MajorObject)partitionsCube);

  22:         MergePartitionCube(cube, partitionsCube);

  23:     }

  24: }

De-serializing the .cube files is easy… but I figured out you have to muck with the .partitions file a bit before it will de-serialize.  We basically just have to add a Name node to the XML for the de-serialize to work… we don’t use it for anything, but it is required.

   1: private static XmlReader FixPartitionsFileForDeserialize(string partitionFilename,Cube sourceCube)

   2: {

   3:     // Validate inputs

   4:     if (sourceCube == null)

   5:     {

   6:         throw new ArgumentException("Provide a Cube object that matches the partitions file");

   7:     }

   8:  

   9:     if (string.IsNullOrEmpty(partitionFilename))

  10:     {

  11:         throw new ArgumentException("Provide a partitions file");

  12:     }

  13:     // I am NOT validating the extention to provide some extra flexibility here

  14:  

  15:     XmlDocument document = new XmlDocument();

  16:     document.Load(partitionFilename);

  17:  

  18:     // Setup for XPath queries

  19:     XmlNamespaceManager xmlnsManager = LoadSsasNamespaces(document);

  20:     string defaultNamespaceURI = "http://schemas.microsoft.com/analysisservices/2003/engine";

  21:      

  22:     // Get all the MeasureGroup IDs

  23:     XmlNodeList nodeList = document.SelectNodes("/AS:Cube/AS:MeasureGroups/AS:MeasureGroup/AS:ID", xmlnsManager);

  24:     XmlNode newNode = null;

  25:     

  26:     // Add a Name node underneath the ID node if one doesn't exist, using the MeasureGroup's real name

  27:     foreach (XmlNode node in nodeList)

  28:     {

  29:         // Verify the node doesn't exist

  30:         if (XmlHelper.NodeExists(node.ParentNode, "Name"))

  31:             continue;

  32:  

  33:         newNode = document.CreateNode(XmlNodeType.Element, "Name", defaultNamespaceURI);

  34:         // Lookup the MG name from the cube based on the ID in the file

  35:         newNode.InnerText = sourceCube.MeasureGroups.Find(node.InnerText).Name;

  36:         node.ParentNode.InsertAfter (newNode, node);

  37:     }

  38:  

  39:     // Return this as an XmlReader, so it can be manipulated

  40:     return new XmlTextReader(new StringReader(document.OuterXml));

  41: }

Next, the MergeParition method will just loop thru the “partitions only” cube we de-serialized, and add each partition and aggregation design to the “main” cube.  We have to make sure a create a copy of each object we add, because a Partition and AggregationDesign can only be in a single cube… using a Add on an existing object removes it from the initial collection.

   1: private static void MergePartitionCube(Cube baseCube, Cube partitionCube)

   2: {

   3:     MeasureGroup baseMG = null;

   4:  

   5:     foreach (MeasureGroup mg in partitionCube.MeasureGroups)

   6:     {

   7:         baseMG = baseCube.MeasureGroups.Find(mg.ID);

   8:  

   9:         // Heisenberg principle in action with these objects; use 'for' instead of 'foreach'

  10:         if (mg.Partitions.Count > 0)

  11:         {

  12:             for (int i = 0; i < mg.Partitions.Count; ++i)

  13:             {

  14:                 Partition partitionCopy = mg.Partitions[i].Clone();

  15:                 baseMG.Partitions.Add(partitionCopy);

  16:             }

  17:         }

  18:  

  19:         // Heisenberg principle in action with these objects; use 'for' instead of 'foreach'

  20:         if (mg.AggregationDesigns.Count > 0)

  21:         {

  22:             for (int i = 0; i < mg.AggregationDesigns.Count; ++i)

  23:             {

  24:                 AggregationDesign aggDesignCopy = mg.AggregationDesigns[i].Clone();

  25:                 baseMG.AggregationDesigns.Add(aggDesignCopy);

  26:             }

  27:         }

  28:     }

  29: }

We now have a AMO Database from our Visual Studio Project!  Serializing it back out is pretty similar… we just loop through the Database, and serialize everything out:

   1: XmlTextWriter writer = null;

   2:  

   3: // Iterate through all objects in the database and serialize them

   4: foreach (DataSource dataSource in database.DataSources)

   5: {

   6:     writer = new XmlTextWriter(targetDirectory + dataSource.Name + ".ds", Encoding.UTF8);

   7:     writer.Formatting = Formatting.Indented;

   8:     Utils.Serialize(writer, (MajorObject)dataSource, false);

   9:     writer.Close();

  10: }

Again, the one wrinkle is the .cube and .partitions file… the Utils methods don’t 100% work with those guys.  Luckily, they work about 90%, so we have a work around.

   1: // Special case:  The cube serialization won't work for partitions when Partion/AggregationDesign

   2: // objects are mixed in with other objects.  Serialize most of the cube, then split out

   3: // Partion/AggregationDesign objects into their own cube to serialize, then clean up

   4: // a few tags

   5: foreach (Cube cube in database.Cubes)

   6: {

   7:     writer = new XmlTextWriter(targetDirectory + cube.Name + ".cube", Encoding.UTF8);

   8:     writer.Formatting = Formatting.Indented;

   9:     Utils.Serialize(writer, (MajorObject)cube, false);

  10:     writer.Close();

  11:  

  12:     // Partitions and AggregationDesigns may be written to the Cube file, and we want

  13:     // to keep them all in the Partitions file; strip them from the cube file

  14:     FixSerializedCubeFile(targetDirectory + cube.Name + ".cube");

  15:  

  16:     Cube partitionCube = SplitPartitionCube(cube);

  17:     writer = new XmlTextWriter(targetDirectory + cube.Name + ".partitions", Encoding.UTF8);

  18:     writer.Formatting = Formatting.Indented;

  19:     Utils.Serialize(writer, (MajorObject)partitionCube, false);

  20:     writer.Close();

  21:  

  22:     // The partitions file gets serialized with a few extra nodes... remove them

  23:     FixSerializedPartitionsFile(targetDirectory + cube.Name + ".partitions");

  24: }

We first serialize the whole cube (for each cube, of course) and then manually fix up the file.  Some of the Partition and Measuregroup info gets serialized out to the .cube file at this point, and we want to keep all of that information in the .partition file where it belongs.

   1: private static void FixSerializedCubeFile(string cubeFilename)

   2: {

   3:     // Validate inputs

   4:     if (string.IsNullOrEmpty(cubeFilename))

   5:     {

   6:         throw new ArgumentException("Provide a cube file");

   7:     }

   8:     // I am NOT validating the extention to provide some extra flexibility here

   9:  

  10:     XmlDocument document = new XmlDocument();

  11:     document.Load(cubeFilename);

  12:  

  13:     XmlNamespaceManager xmlnsManager = LoadSsasNamespaces(document);

  14:  

  15:     XmlNodeList nodeList = null;

  16:  

  17:     // Remove the MeasureGroup Names

  18:     nodeList = document.SelectNodes("/AS:Cube/AS:MeasureGroups/AS:MeasureGroup/AS:Partitions", xmlnsManager);

  19:     XmlHelper.RemoveNodes(nodeList);

  20:  

  21:     // Remove the StorageModes

  22:     nodeList = document.SelectNodes("/AS:Cube/AS:MeasureGroups/AS:MeasureGroup/AS:AggregationDesigns", xmlnsManager);

  23:     XmlHelper.RemoveNodes(nodeList);

  24:  

  25:     document.Save(cubeFilename);

  26: }

We then create a temporary cube with just the Partition and AggregationDesign objects in it, and serialize it.  Again, we have to clean up the file just a bit.

   1: private static void FixSerializedPartitionsFile(string partitionFilename)

   2: {

   3:     // Validate inputs

   4:     if (string.IsNullOrEmpty(partitionFilename))

   5:     {

   6:         throw new ArgumentException("Provide a partitions file");

   7:     }

   8:     // I am NOT validating the extention to provide some extra flexibility here

   9:  

  10:     XmlDocument document = new XmlDocument();

  11:     document.Load(partitionFilename);

  12:  

  13:     XmlNamespaceManager xmlnsManager = LoadSsasNamespaces(document);

  14:  

  15:     XmlNodeList nodeList = null;

  16:     

  17:     // Remove the MeasureGroup Names

  18:     nodeList = document.SelectNodes("/AS:Cube/AS:MeasureGroups/AS:MeasureGroup/AS:Name", xmlnsManager);

  19:     XmlHelper.RemoveNodes(nodeList);

  20:  

  21:     // Remove the StorageModes

  22:     nodeList = document.SelectNodes("/AS:Cube/AS:MeasureGroups/AS:MeasureGroup/AS:StorageMode", xmlnsManager);

  23:     XmlHelper.RemoveNodes(nodeList);

  24:  

  25:     // Remove the ProcessingModes

  26:     nodeList = document.SelectNodes("/AS:Cube/AS:MeasureGroups/AS:MeasureGroup/AS:ProcessingMode", xmlnsManager);

  27:     XmlHelper.RemoveNodes(nodeList);

  28:  

  29:     document.Save(partitionFilename);

  30: }

…and there we go!  We can start with a Visual Studio SSAS Project, create the Database in memory, do whatever we want with it, then write it back out to files!

Build .ASDatabase File

I talk more about the what and why in <<this blog>>, but the actual mechanics of this functionality is really easy (once we’ve de-serialized the project, that is).  This could basically be condensed to a single line of code:

   1: Database database;

   2:  

   3: database = ProjectHelper.DeserializeProject(ssasProjectFile);

   4:  

   5: ProjectHelper.GenerateASDatabaseFile(database, targetFilename);

Clean Visual Studio SSAS Project

I won’t go into a lot of detail on this here… I posted a blog on SQL Server Analysis Services Projects with Multiple Developers a little while back.  I originally wrote this functionality in PowerShell, but just ported it into this library because I thought it would be useful.  This method just goes through each unlocked file and removes fields maintained by Visual Studio that aren’t required by the AS DB per se, but that make merging code from multiple developers substantially more difficult.  If you’re testing out the serialize/de-serialize functionality, you might also want to use this against your projects to make them easier to compare.

Sort SSAS Project File

One annoying side affect of serializing the .partition file is that all the elements get re-arranged.  Everything still works, of course, but if you’re like me you’ll want to verify that everything is working.  The good people over at BIDS Helper let me use a version of their “Smart Diff” XSLT that, which will sort a SSAS file.  I modified it slightly and customized it to help validate the .partition files.  I would be very careful using it for anything else… its only been tested for the intended scenario.

Known Issues

I’ve tested this with Visual Studio 2008 SP1, SQL Server Analysis Services SP1 CU3, and the Adventure Works 2008 AS DB with no issues.  Definitely make a backup before you try this out, as your mileage may vary.

1. The sample I wrote just serializes objects into files based on their names.  It would be trivial to maintain a list based on the Visual Studio project file to make sure the objects are written out with their correct names.
2. Partitions are reordered when De-Serialized/Serialized. Makes it a pain to validate, but I've seen no ill effects. Use SortSssasFile functionality to make a copy of the files for easier comparison.
3. Some fields maintained for Visual Studio (State (Processed, Unprocessed), CreatedTimestamp, LastSchemaUpdate, LastProcessed, dwd:design-time-name, CurrentStorageMode) are lost when a project is De-Serialized/Serialized.

Next Steps

I’ve thought of some nifty things to do with this functionality, including…

1. Generate a new Visual Studio project based on metadata.
2. Maintain a project with external metadata… you can maintain a separate data dictionary with descriptions of each attribute/measure/cube, and just programmatically update your source when needed.
3. Automate repetitive tasks and maintenance of the project (like adding a bunch of partitions to your measure groups, or replacing every occurrence of a certain word in the project.
4. Build a more robust development and deployment process.
5. …and many more things.

Cheers,

David

Filtering Objects in PowerShell based on a List of Accepted Values

I was writing a script the other day where I want to return a collection of Services based on the name.  It took me a few minutes to figure out how to do this, so I thought I’d jot it down.  Nothing revolutionary, but I’ve definitely found this pattern to be handy.

Let’s start with a comma separated list of objects we want to filter by.  In this case I only want to return objects that start with “a”, “c”, “e”, or “g”.  I’ll take that list, and split it into an array.

# List we want to filter against
# Split it into an array
$filterList = 'a,c,e,g'.Split(",")

Next, I’ll generate a collection of objects to try and filter.  I’ll just use a function to make life easy:

# Function to generate a collection of objects to test on
function Create-TestCollection {
    # Turn this into an object to filter
    $objectList = 'a,b,c,d,e,f,g'.Split(",")
    
    foreach($object in $objectList)
    {
        $output = new-object PSObject
        $output | add-member noteproperty Name $object
        
        Write-Output $output 
    }
}

# Populate the variable to test with our collection of objects
$collection = Create-TestCollection

Now $collection contains a list of objects with a single Name property containing “a”, “b”, “c”, “d”, etc.

Now list filter $collection using a Where:

Write-Host "Objects from the Collection where the Name property exists in an Array"
# Filter the collection of objects based on a CSV list
$collection | Where {$_.Name -and $filterList -eq $_.Name }

And it will return only the objects from our initial comma separated list:

Name                                                                                     
----                                                                                     
a                                                                                        
c                                                                                        
e                                                                                        
g    

This also works with other strings:

# An array we want to filter
$collection2 = 'a,d,g,j,m'.Split(",")

Write-Host "Strings in an Array where that exist in an Array"
# Filter the collection of objects based on a CSV list
$collection2 | Where {$_ -and $filterList -eq $_ }

Strings in an Array where that exist in an Array
a
g

Enjoy…

David

Building a SQL Server Analysis Services .ASDatabase file from a Visual Studio SSAS Project

There are several methods available for deploying Analysis Services databases once you’ve build your solution, including direct connections, generating XMLA to deploy, and using the Deployment Wizard with answer files.  Still, building and deploying AS Databases can sometimes be a challenge in enterprise development scenarios.  One common scenario is when you have multiple developers working on a single solution where all the files are under source control.  I wrote a blog about about SQL Server Analysis Services Projects with Multiple Developers recently, and this is one of the issues that you tend to run into.  I created a sample you can use to help with this problem.  I’m not going to go over how to deploy AS solutions (it’s documented plenty of other places)… this is just a tool to address this particular scenario.

I create the SsasHelper sample demonstrate the functionality, and posted it to the Microsoft SQL Server Community Samples:  Analysis Services site on CodePlex.  I posted about this functionality in my blog SQL Server Analsysis Services ‘Project Helper’ if you want details on the mechanics.

Background

So you’ve built your solution, tested it, and everything works great.  All of your developers are hard at work constantly improving your OLAP capabilities, and they’re storing all of their changes under source control.  Now you need to do routine deployments to multiple environments.  One of the problems you might have run into is that you have to use Visual Studio to actually do a deployment.  Even if you’re deploying via the Deployment Wizard (using answer files), you still need to generate the .ASDatabase by building the solution.  This problem becomes a little bigger if you want to use a Build Server, that doesn’t have Visual Studio, where you compile your solution, test, deploy it, etc.  Currently, the only option you really have for this scenario is to make sure every developer Builds the solution after all changes and checks in a new .ASDatabase file.  This is a bit of a pain, and tends to lead to (at least occasional) issues with deploying updated versions.

SSAS Helper

I created a class library to work with Analysis Services projects.  This library has a couple of neat pieces of functionality, including being able to de-serialize a SSAS project to an AMO database, write an AMO database back out to component files, clean a AS project (removing volatile, non-essential fields… I went over it in SQL Server Analysis Services Projects with Multiple Developers), and creating a .ASDatabase file based on a project.  The library is written in C# 3.5, and is designed/tested with SSAS 2008 (though it should work with 2005… test it though).

Using SSAS Helper

If you’re running into this problem, you probably already have a good build solution, so I won’t go over that here.  What you’ll want to do is create a new build task (using whatever framework you’re using) using this component.  As an example, you can create a custom MSBuild using this library that takes the SSAS Project as an input, and delivers a .ASDatabase file as an output.  The task would then be added to the build file, so you will no longer have a dependency on Visual Studio to compile all your .cube, .dim, .role, etc. files into something that can be deployed.  I’ll try and post a sample in the next few weeks, but it shouldn’t be a major task.

Caveats

I built this tool and tested on my current project, and tested with Adventure WOrks… I haven’t seen any problems with it, but your mileage may vary.  Make sure you test with your project.

Here are some known issues for this functionality:

1. Partitions are reordered when De-Serialized/Serialized. Makes it a pain to validate, but I've seen no ill effects. Use the SortSssasFile functionality to make a copy of the files for easier comparison.
2. Some fields maintained for Visual Studio (State (Processed, Unprocessed), CreatedTimestamp, LastSchemaUpdate, LastProcessed, dwd:design-time-name, CurrentStorageMode) are lost when a project is De-Serialized/Serialized.

When I use this tool on a project, I work on a copy, and then do a file diff to check the output the first time.  I used Beyond Compare (http://www.scootersoftware.com/) to compare the entire directory, and each file side by side, just to make sure there are no unintended side affects.  I would recommend you do the same to make sure… this works fine on the projects I’ve used it on, but you need to make sure there’s nothing special about yours so you don’t accidently destroy something.

How it Works

This project works by de-serializing all the files referenced by the .dwproj file into AMO objects then serializing the entire database.  There is more detail on de-serializing/serializing the objects in my post SQL Server Analsysis Services ‘Project Helper’ .  The code is fairly well commented (well… I think it is :) and should be fairly straight forward.

Next Steps

To use this, you’ll need create a build task in your framework of choice, and just plug it in to your solution.  If this proves difficult for people, I’ll try and provide a sample, but it should be fairly straight forward to do.  If it doesn’t work right out of the box, it should work with minimal modification.  Just make sure all of your project files (.dwproj, .cube, .dim, .role, .dsv, .ds, .dmm, etc.) are published and you have a way to push the .ASDatabase file to your target.

Conclusion

That’s about it… I hope this helps some folks out.  Let me know if you have any problems, and we’ll see what we can get working.

Cheers,

David

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:

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:

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: [xml]$xmlResult = $xmlStringResult

  12:  

  13: return $xmlResult.return.Root.row

  14:  

  15: # Disconnect the session

  16: $xmlaClient.Disconnect()

   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:  [xml]$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

SQL Server Analysis Services Projects with Multiple Developers

A topic that often comes up when discussing enterprise level development with SSAS is how to have multiple developers work on the same project at the same time.  This issue doesn’t come up for many installations… a lot of teams get away with just having a single person working on their OLAP capabilities.  However, for a decent sized implementation, you’re going to want to have more than one person working on the solution at the same time.  I’ll be discussing some of the issues, workarounds, and tools you can use to make concurrent SSAS development easier.

Here’s a link to the source code from later in the post if that’s all you’re looking for.

Background

Analysis Services objects (cubes, dimensions, roles, etc.) are manipulated either programmatically or in Visual Studio (Visual Studio is the normal method). These objects are persisted by serializing them to XML. When you deploy an Analysis Services database, you either connect directly to an instance of Analysis Services, or you save off XMLA scripts/files that can be used to create the database on a remote server.

If you have a single person working on an AS project, you don’t have a problem.  If you’re using source control with exclusive locks (i.e., only one person can edit a file at a given time) you can have multiple people working on the same solution, but not on the same object at the same time.  This is somewhat complicated by the fact that modifying one object (such as a dimension) may require a change in associated objects (such as a cube where it is included).  You’re still fairly limited in the amount of work you can do concurrently.

The way to have multiple developers working concurrently is to use source control with non-exclusive check-outs, so multiple people can work on each file at the same time.  The down side is that you eventually have to merge the copies each person is working on back together.  Since the SSAS files are large, complicated XML documents this isn’t necessarily an easy task.  Most source control systems will attempt to automatically merge non-conflicting changes, but this usually doesn’t work very well with SSAS files (for reasons I’ll go into in just a minute).  There are, however, some things we can do to make the task a bit easier.

Challenges with Merging SSAS Files

When SSAS objects are persisted to XML, they contain structural information about the objects (which is required) as well as environmental and formatting metadata (which can be helpful in Visual Studio, but is not required for the solution to work correctly when deployed). The environment and formatting metadata elements tend to be extremely volatile, and vary for each developer. Stripping the volatile fields from the XML files will make the merge process easier without affecting the cubes and dimensions that are deployed.

Ex. A developer checks out “Adventure Works.cube” , fixes an error in a Description field, then deploys the cube to test. When he checks the file in, he will have to merge large XML file. He has only changed one line, but large sections of the file will be different from copies checked out to other developers due to metadata capturing the state of Visual Studio and the local AS server. By stripping this metadata, the developer can focus on merging the one change that matters without having to verify every other change in the file.

SSAS Elements that can be Removed

The following represent the environment and formatting metadata elements that are persisted in Analysis Services files. These fields can all be safely stripped from Analysis Services files prior to merging to remove the large number of unimportant conflicts that normally occur.

Programmatically Removing SSAS Elements

I’ve create a PowerShell function 'Clean-SsasProject’ that will iterate over all writable SSAS objects in a directory and remove the volatile elements by manipulating the XML.  The function will make a copy of every file it modifies.  It is written using using PowerShell v2 CTP3, but should be easy to back port if you need to.  I’ve included a commented out section that will process the .ASDatabase file as well… this is used for a particular scenario on our team, just including it in case it is handy for anybody.  Use the $WhatIf and $Debug flags to know what the function will do before you do it for real.  This code is geared to the project I’m working on currently, and you may want to modify it to meet your precise needs. 

I would recommend creating a backup of your solution before you try this script, just in case.  I’ve been using this for awhile with no ill effects, but you could have a scenario I never dreamed about, so…

***DO THIS AT YOUR OWN RISK.  IT WORKS ON MY MACHINE.  ***

Consider comparing the cleaned XML side by side with the original to make sure this process works for you… it’s worked fine for every project I’ve used it on, but better safe than sorry.

You can download the source here.

Using Clean-SsasProject (for an individual)

I have my environment configured to load all files with the pattern ‘*Library.ps1’ when PowerShell loads via the following script in my ‘Profile.ps1’ file:

   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: }

I store the .ps1 file with Clean-SsasProject and the other functions it depends on in my PowerShell scripts directory, so it’s loaded every time the PowerShell environment loads.  You can then just run ‘Clean-SsasProject’ from the PowerShell prompt.  I also have a .Cmd file in my path to automatically clean my normal SSAS project.  It just uses the following commands:

   1: SET SSAS_PROJECT_LOCATION=C:\Source\MyProject

   2:  

   3: PowerShell -Command "Clean-SsasProject %SSAS_PROJECT_LOCATION%"

Running that command file will strip the volatile fields out of any file in the directory that is writable (i.e., checked-out of my source control system).

Using Clean-SsasProject (for a team)

This tool is designed to work when every team member does the following:

1. Check-out all files required for a change.  Remember that modifying one object may require that another object be updated, so make sure and check out all objects that can possibly be affected).
2. Make the change.
3. Clean the files.
4. Merge/Resolve conflicts.
5. Build project output (if required for your solution… I’ll be posting on how to easy project builds/deployments in a few days)
6. Check-in all files required for a change.

General Best Practices

There are some other general things you can do to make concurrent development a little bit easier (most of these go for software development in general, not just Analysis Services).  If you’ve attempted to have multiple developers work on a project, you’re probably doing all these things already.  Remember that it is always faster and easier not to have to merge when you don’t have to.

Do Separate AS Cubes and Databases by Subject Area

Including only related objects in a cube/database is a standard best practice. This approach avoids potential performance issues, increases manageability and maintainability, and improves the presentation and understandability for the end user. This design pattern also lessens the chance that multiple developers will need to be working on the same object at the same time.

Don’t Combine Unrelated Attributes in a Single Dimension

Including unrelated attributes in a single dimension causes problems with performance, maintainability, and general use of the solution. Including unrelated attributes also promotes conflicts by increasing the chance that developers working on unrelated areas will need to work on the same file.

Do Communicate and Schedule Work for Minimum Conflicts

Make sure to communicate with other developers to avoid working on the same objects when possible. If you need to work on the same object, ensure the design changes are compatible and that there is no way to optimize the work.

Major changes that will dramatically affect source merging should be performed with an exclusive lock on the file.

Ex. A developer wants to re-order the 200 calculated members in the calculate script. The developer should wait until everyone else has submitted their changes, then make the change and submit it.

Do Check-out late and Check-in Early

Minimize the time you keep AS files checked out. While it may take some time to develop new functionality for AS (modifying the source database, creating an ETL to load the database from a source system, etc.) the work in AS is typically fairly quick to do if properly designed and prepared for. Complete the design and other development before checking out the Analysis Services files.

Do Use Tools to Help Merge

Use a side-by-side differencing tool to compare and merge different versions of Analysis Services files. A good diffing tool will have features to make this operation significantly easier. Consider using a tool such as Beyond Compare for this task.  You can use this process to verify that Clean-SsasProject works for your solution the first time you it.

Next Steps

Modify the provided source/process to meet your needs and environment.  There is no 100% “right way" to handle development like this… everyone’s situation will be just a little bit different, and require a little bit of customization.  I’m just trying to give you the tools to make it a little bit easier.

Conclusion

That’s all there is.  If you use the tools, techniques, and approach above it should make developing Analysis Services solutions with multiple developers a bit easier for for you.  You’ll still have some of the headaches normally associated with this type of work, but hopefully you’ll have an easier time of it.

Cheers,

David

SQL Database Tuning Advisor Output Renamer

I’ve uploaded the ‘Database Tuning Advisor Output Renamer’ at http://DtaOutputRenamer.codeplex.com/.

OK… so Friday marked the first day I’ve ever gotten sunburned while coding.  I had a little bit of free time while at an outdoor event, and whipped up a a little utility to help apply standards to to DTA recommendations.

I use the SQL Database Tuning Advisor (DTA) a lot to generate basic recommendations for indexes and statistics based on a workload.  In my team, we store all index and statistics creation scripts in .SQL files, which are then run as part of our deployments.  We use a standard naming convention for each of the objects to enhance the maintainability.

Last week I ran the DTA against a workload I generated based on running reports on some new schema… not surprisingly, quite a few recommendations were generated.  It occurred to me my time could be better spent doing something besides renaming 50 database objects based on their definitions.  I decided to write a small application to help change the default names (such as ‘_dta_index_SsasProcessingRunArchive_c_7_1677965054__K10’) to something a little more user friendly (like ‘IX_dbo_SsasProcessingRunArchive_ObjectType_EventClass_SessionID_I_StartTime’).  You’ll want to modify the application to match your local coding standards, but it should be pretty straight forward to do. 

This application only handles a few cases, but does cover Clustered/Non-Clustered Indexes (with and without INCLUDE columns) and Statistics.  It should be easy to extend it if you need to.  This app is just something I whipped up in an hour or two, so it isn’t the most robust thing ever created.

I created the following regex (remove the line breaks… I just used those for presentation) to capture the index name, table name, and column/include lists for the indexes:

   1: CREATE\s(?<NonClustered>NON)?CLUSTERED\sINDEX\s\[(?<IndexName>.*?)\].*?ON\s\

   2: [(?<Schema>.*?)\]\.\[(?<Table>.*?)\].*?\((?<ColumnList>.*?)\)\s*?(?:INCLUDE\s

   3: \((?<IncludeList>.*?)\))??\s*?(?:WITH\s*?\(.*?\)\s*?ON\s\[.*?\])

I created the following regex (remove the line breaks… I just used those for presentation) to capture the statistics name, table name, and column list for the statistics:

   1: CREATE\sSTATISTICS\s\[(?<StatisticsName>.*?)\].*?ON\s\[(?<Schema>.*?)\]

   2: \.\[(?<Table>.*?)\].*?\((?<ColumnList>.*?)\

I then just update the object names with a new name created based column lists and such.  I also through in the functionality to strip ‘go’ statements from input.

Enjoy…

PowerShell Script to reset the local instance of SQL Server

I use virtual machines a lot for development and testing.  I typically start with a sysprepped base image that I then initialize every time I need a new machine.  One issue is that SQL Server doesn’t know it has been sysprepped… if you execute

   1: SELECT @@SERVERNAME

You will get the name of the machine from when you installed SQL Server.

I use the following PowerShell script to reset the name of the local instance to the current name of the machine:

# Load Assemblies we need to access SMO
$asm = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
$asm = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$asm = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
$asm = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlEnum")
$asm = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.WmiEnum")
$asm = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")

##############################################################################
# Description:
# Change the name SQL Server instance name (stored inside SQL Server) to the name 
# of the machine.  When a machine is unboxed after being sysprepped, it will still
# use the original SQL Server name as the instance name for SQL Server
# 
# Input:
#
# Output:
# 
# Author: DDarden
# Date  : 200904030748
# 
# Change History
# Date        Author          Description
# --------    --------------  -------------------------------------------------
# 
###############################################################################
function global:Set-SqlServerInstanceName{
    Write "Renaming SQL Server Instance"
    $smo = 'Microsoft.SqlServer.Management.Smo.'
    
    $server = new-object ($smo + 'server') .
    $database = $server.Databases["master"]
    $mc = new-object ($smo + 'WMI.ManagedComputer') .
    
    $newServerName = $mc.Name
    
    $database.ExecuteNonQuery("EXEC sp_dropserver @@SERVERNAME")
    $database.ExecuteNonQuery("EXEC sp_addserver '$newServerName', 'local'")
    
    Write-Host "Renamed server to '$newServerName'`n"
}

# Set the SQL Server instance name to the current machine name
# MSSQLSERVER service needs to be restarted after this change

Set-SqlServerInstanceName

Analysis Services Error: The attribute key cannot be found when processing a dimension

I was processing a SSAS database to test some aggregations today, and I noticed some errors and came across some unexpected behavior. 

When I was processing the User dimension (among others), I got an error similar to the following:

Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'dbo_DimUser', Column: 'Country', Value: 'US'; Table: 'dbo_DimUser', Column: 'Region', Value: 'NY'; Table: 'dbo_DimUser', Column: 'City', Value: 'Albany '. The attribute is 'City'. Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute User of Dimension: User from Database: OfficeLive, Record: 1234.

Now, if I was processing a cube and saw this, I would immediately think a referential integrity issue.  Not so in this case.  So I looked at the missing key value, in this case ‘Albany ‘.  The string had some trailing spaces… but that shouldn’t be a big deal, since the Key property in BIDS was set to perform right trimming. 

But with a little sleuthing, I found something interesting:  the error message was <gasp> a lie!  When I looked at the database, it wasn’t a trailing space (which is what appeared in the error message), it was a trailing tab.  The character were automagically converted in the error message.  So it wasn’t getting trimmed and matching the already existing ‘Albany’ key.  The weird thing was that it was just failing, because of the tab character in the key.   I actually would have expected to see two entries for ‘Albany’ (one with some whitespace, if you looked hard enough) in the dimension.  I actually prefer this, but it did take me a little by surprise…

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…

SQL Load Generator Tool

A few weeks ago I spoke at PASS on using the Resource Governor in SQL Server 2008.  For my demo, I created a little application that could fire off multiple queries against SQL Server to simulate different users/applications.  This tool… gasp… creates a load on SQL Server.  People seemed interested in the application, so I’ve decided to release it on CodePlex.  You can find the project here.

For anyone that saw that demo, this tool has gotten a a bit of a face lift… I sort of got bored over the holidays, and added a few features that I wished I had when I was using it to demo, as well as just playing around.  It’s nothing fancy… I just put it together one Saturday for my presentation, then gave into some feeping creaturism a couple of nights.

I originally created this to test settings for the SQL Server 2008 Resource Governor.   You can also use it as a (very) light weight load testing tool, but no promises on that.  I’m not planning on adding any features to it, or supporting it much, but I thought I’d put it out there.

Here’s what it looks like:

Here’s a summary of the features:

• Runs multiple queries against SQL Server.  You can add as many as you like.
• Each query can be either a SQL User or Domain User.
• You can specify an Application name for the connection.
• You can specify the new of concurrent threads to use for each query.
• You can start all queries, stop all queries, remove all queries.
• There is logging (you can toggle on and off… it isn’t precisely thread safe, and can cause crashes when there are lots of failures on multiple threads) for failed queries.
• You can set all the defaults on a per user basis, and persist them.
• Each query has a # of Runs and a # of Fails counter.  You can use the ‘Reset Counters’ feature to reset the total counts (not the per query counts).

You can save your settings via the ‘Options’ menu.  You can add default items to the different dropdowns, provide default query settings, change the log locations, etc.  You can also modify the stock connection string… though keep in mind some of the settings (particularly ‘pooling=false’ will affect the way the application works… namely, the connections to SQL Server won’t be closed). 

That’s about it… enjoy!

David

Troubleshooting Writeable Regions in PerformancePoint Plan

Only certain cells are considered “writeable” in a PPS Plan Excel form. A writeable region is a region where the user can enter data. Here is a checklist of reasons why a region that you expect to be writeable is not.

I put this list together based on some correspondence with the PPS Team, as well as adding in everything I found on the forums... this isn't my work.  I just wanted to post it somewhere that I wouldn't always have to go looking for it...

1. Verify your form.

1. Verify that you have authored a form in Excel that uses every dimension/hierarchy that's in your model (as a column, row, or filter).

2. Verify that the ‘Allow Data Entry’ property is set to true for the matrix.

3. Note that you can only write to cells that match the model's member set view for time so the form should be authored with the same time hierarchy as the model's member set view.

4. Verify that the matrix is using the correct model for the cycle.

5. Verify that the matrix is using the correct scenario for the cycle.

6. Check that the cell style "Data Entry Cell - PerformancePoint" has distinct formatting from the cell style "Data Cell - PerformancePoint", and type directly into a cell expected to be writeable to confirm that the add-in is preventing data entry. (Use the "Reset Default Cell Style" button in the PerformancePoint options dialog to restore default settings for the styles)

7. If you use Custom MDX:

1. Verify you have crossjoined [Measures].[Value] to the columns.
2. If you want annotations, you have crossjoined [Measures]. [Model_MeasureGroup_AnnotationCount].
3. No level that you're entering data into is aliased with the With Member statement.

2. Verify your cycles.

1. Verify you've published the form as a Form Template from Excel.

2. Verify that your form shows up under "Forms" in the "Forms and Reports" section.

3. Verify that the cycle and assignment status is started.

4. Check the Model | Summary page and note down the Current Period; this may affect the time range for the form.

5. Verify the cycle has the correct start and end dates (and that the current date/time is between them).

6. Verify the cycle has the correct scenario.

7. Verify that you've assigned the correct form to the Cycle and assigned the correct people as Contributors/Reviewers/Approvers.

8. Verify in Process Management | Cycle Instance | Forms Assignment that the status is started for the user.

3. Verify your security.

1. Verify that user permissions for the roles used in the assignment are set properly. This includes being able to Write data to all leaf nodes, as well as ensuring that the role is turned on for the model.

4. Verify you’re running the form correctly.

1. Verify that you've opened the form as a Contributor (i.e. with appropriate credentials), and have opened the Assignment and not just gone to Reports>Open.

2. Verify that you have put all filters to a leaf level.

3. Verify that you are entering in at the correct scenario at a leaf level of all dimensions (unless you have enabled input at all levels, in which case, only the filters must be at leaf levels).

4. Verify that the matrix is showing data at a leaf level for all dimensions in the measure group. Alternately, turn spreading on to see where non-leaf data entry is possible – but note spreading does not work across time.

5. If the writeable region appears for a contributor but not for an approver, check that the option "Allow the approver to edit submissions" was set on the assignment definition. Also be sure that the approver is using the individual assignment (from the review/approve dialog) rather than the grouped assignment.

Repeated Dimension Names in PerformancePoint Server Monitor & Analyze

A colleague came to me recently with a problem.  She was implementing a Dashboard in PPS M&A, and she was seeing some unexpected dimension names.  The behavior was specifically affecting her when she was creating a report using an Analytic Grid, but it was consistent inside of PPS M&A everywhere I looked.  The problem seemed to be present with several other hierarchies.

Basically, her hierarchy/dimension names were not being presented to the user as expected.  For example, in her SSAS DB she had a dimension named 'Revenue Indicator' and a user hierarchy named 'Revenue Indicators'.  In the Analytic grid, it looked like the word 'Indicator' was repeating... the hierarchy was being displayed as 'Revenue Indicator Indicators'.  This naming was a bit confusing, and a little unexpected, so I dug into it a little bit.

Evaluate the Problem

My first step was to check the Analytic Grid in question, as well as the dimension in SSAS.  Not that I thought my coworker was lying to me, but I wanted to make sure that what was being displayed to the user was NOT what I expected to see.  Nope, I was not being lied to and deceived... my faith in humanity can continue unabated.  Some transformation was definitely going on between SSAS and PPS.

Determine how PPS is getting the Hierarchy name

Next, I wanted to see how PPS retrieves the names of all the hierarchies and attributes to display to the user.  I fired up Profiler, and captured a trace when I loaded up the Analytic Grid.  I saw PPS using several Discover commands... so I fired off a Discover command based on what I saw in the trace.  It looked something like this:

  1: <Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
  2:   <RequestType>MDSCHEMA_HIERARCHIES</RequestType>
  3:   <Restrictions />
  4:   <Properties>
  5:     <PropertyList>
  6:       <Catalog>MyCube</Catalog>
  7:       <Content>SchemaData</Content>
  8:       <Format>Tabular</Format>
  9:     </PropertyList>
 10:   </Properties>
 11: </Discover>

That discover command gave me back the schema for the Cube.  I took a look at the XML... here is the relevant section:

  1: ...
  2: <row>
  3:   <CATALOG_NAME>MyCube</CATALOG_NAME>
  4:   <CUBE_NAME>My Cube</CUBE_NAME>
  5:   <DIMENSION_UNIQUE_NAME>[Revenue Indicator]</DIMENSION_UNIQUE_NAME>
  6:   <HIERARCHY_NAME>Revenue Indicators</HIERARCHY_NAME>
  7:   <HIERARCHY_UNIQUE_NAME>[Revenue Indicator].[Revenue Indicators]</HIERARCHY_UNIQUE_NAME>
  8:   <HIERARCHY_CAPTION>Revenue Indicators</HIERARCHY_CAPTION>
  9:   <DIMENSION_TYPE>3</DIMENSION_TYPE>
 10:   <HIERARCHY_CARDINALITY>26</HIERARCHY_CARDINALITY>
 11:   <DEFAULT_MEMBER>[Revenue Indicator].[Revenue Indicators].[All]</DEFAULT_MEMBER>
 12:   <ALL_MEMBER>[Revenue Indicator].[Revenue Indicators].[All]</ALL_MEMBER>
 13:   <DESCRIPTION />
 14:   <STRUCTURE>1</STRUCTURE>
 15:   <IS_VIRTUAL>false</IS_VIRTUAL>
 16:   <IS_READWRITE>false</IS_READWRITE>
 17:   <DIMENSION_UNIQUE_SETTINGS>1</DIMENSION_UNIQUE_SETTINGS>
 18:   <DIMENSION_IS_VISIBLE>true</DIMENSION_IS_VISIBLE>
 19:   <HIERARCHY_ORDINAL>1</HIERARCHY_ORDINAL>
 20:   <DIMENSION_IS_SHARED>true</DIMENSION_IS_SHARED>
 21:   <HIERARCHY_IS_VISIBLE>true</HIERARCHY_IS_VISIBLE>
 22:   <HIERARCHY_ORIGIN>1</HIERARCHY_ORIGIN>
 23:   <HIERARCHY_DISPLAY_FOLDER />
 24:   <GROUPING_BEHAVIOR>1</GROUPING_BEHAVIOR>
 25: </row>
 26: ...

So, precisely what I would expect... but not what is coming out of PPS. 

Determining how PPS is transforming the Hierarchy name

I then tried a little experiment... I added a few more hierarchies, to see how PPS would present them:

After deploying the changes, I saw the following hierarchies in my Analytic Grid:

Result

PPS is retrieving the Dimension name and Hierarchy name, then concatenating them together while removing any duplicated words from the Hierarchy name.  I checked this behavior out in several places inside of PPS (not just in the Analytic Chart)... it appears to be common functionality.

This is actually pretty helpful.  Most of the time when we create SSAS DB's, they're accessible using a variety of tools... PPS M&A for Dashboards, via Excel for power users, etc.  The dimensions and hierarchies are rendered in different ways for each tool.  Some will concatenate the Dimension name with the Hierarchy name, and some won't.  Some will give you a nice hierarchy, some you just get an ugly list.  Naming your dimension appropriately will give you the most user friendly names in each of the tools... in this case, using 'Revenue Indicator Types' gives you a user friendly name in both Excel and in PPS, which is what we needed.

This is some pretty handy functionality, but I didn't find it with a quick search of the 'net and BOL (which doesn't mean it's not there).  If you're surfacing your SSAS DB through PPS, I would definitely take this into account when determining your naming conventions.

Cheers,

David