I posted last month about the beta – thanks to everyone that downloaded it and provided feedback. The major new features are:

Duplicate Roles

This feature allows you to copy a role with all of the associated settings and permissions. This is implemented as a new menu item on the right click menu for a role.

Biml Package Generator

The Biml Package Generator provides the ability to create packages from Business Intelligence Markup Language (Biml). You can find some tutorials on using this functionality here, and a short introduction video here.

There were also several issues resolved, and other improvements. The Enable/Disable features dialog has been improved to provide additional information and help links. The new version of the SSIS Expression Editor is also included, and there are improvements to the variable move feature, the Expression list, and the Printer Friendly Dimension Usage feature.

I’m pretty excited about this release, since I’ve been working on the Biml language and technology since joining Varigence. I think it’s great that we’re able to provide some of this functionality for free to the community through BIDS Helper. I’m particularly interested in seeing what interesting uses the community comes up with for it. As I mentioned here, I think it will provide a nice way to package up complete, working samples that someone can just copy and paste from a web page, and use locally. On top of that, it will help automate a lot of rote tasks in SSIS, as I’ll continue to highlight in my series on using Biml in BIDS Helper.

We’ve already gotten some great feedback on the new version, along with some suggestions for continued improvements. If there’s functionality you’d like to see added to BIDS Helper, please suggest it at the Issue Tracker page – you can also use that to track new feature suggestions.

I’ve attached the slides from the presentation. I’ve also attached a few definition files, one for a Performance Monitor data collector set, and two trace templates, one for SSAS and one for SQL Server. Feel free to customize these files as necessary for your own tuning efforts. Also, while these trace templates can be used in Profiler, the best thing to do is to generate the scripts from them to run the trace directly on the server – lower overhead and less impact on performance.

The file is located on my SkyDrive.

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:

try
{
Database database = ProjectHelper.DeserializeProject(SsasProjectFile);

// ... Verify our project doesn't have any errors ...
ValidationResultCollection results;

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

// If the database doesn't validate (i.e., a build error)
// log the errors and return failure.
foreach (ValidationResult result in results)
{
Log.LogError(result.Description);
}

if (!isValidated)
{
return false;
}

// Build the .ASDatabase file
ProjectHelper.GenerateASDatabaseFile(database, SsasTargetFile);
}
catch (Exception ex)
{
Log.LogErrorFromException(ex);
return false;
}

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:TFSSsasHelperSsasBuilderbindebugSsasBuilder.dll"/>

   4:     <Target Name="BuildASDatabaseFile">

   5:         <SsasBuildASDatabaseFileTask SsasProjectFile = "C:Testenterprise_GoldAdventure Works DW 2008.dwproj"

   6:             SsasTargetFile = "C:TestSsasBuildTestAdventureWorks.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

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

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.

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.

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…

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:

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).

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.

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