Analysis Services and XMLA


Introduction

Last week was a week in "BI Boot camp”, learning how to work with Microsoft SQL Server Integration, Analysis and Reporting Services. The week was very worthwhile. The three of us from the migrate to Microsoft project really did get us all “up to speed” with all of the tools which we have at our disposal. There should be a big thankyou to Bill Chestnut for presenting a very good weeks worth of training.

Having just completed the training in SQL Server BI tools, this week has been full steam into continuing the migration project. The area of interest I started exploring is the whole deployment side of the project. We will have cubes in out development environment which will need to be migrated to a User Acceptance, Test (if we run with the environments) and Production environments. Apart from the “getting the space” issue, which will resolve itself as new SAN (storage area network) hardware is installed. The issue of developing a deployment methodology has been consuming my time for the last couple of days.

The Strategy

  • Use XMLA to transfer the definition of the cubes from Development into another environment.
  • Use Powershell to script the deployment process.

The Glitches

  1. Learning Powershell. This will be a learning curve for the next couple of weeks. I’ve found some many resources on the net on Powershell. Nothing I’d strongly recommend at this point. There is plenty on the MSDN web site on the product.
  2. Generating XMLA for a Cube, programmatically. The way I’ve found to do this is using the Scripter Class, and the WriteCreate static method. I’ll post some sample code (in C# and Poweshell) which demonstrates this (later, need to get the code together). The method does generate XMLA as advertised, and once you know where it is is not too hard to use.
    • There is a wrinkle to using this class you need to have a reference to the “Assembly: Microsoft.AnalysisServices (in microsoft.analysisservices.dll)”. A search for the assembly was required to find it on the machine.
  3. Applying XMLA. This one had me going for a while. There are two options I found:
  4. Generated XMLA for a Cube. The XMLA generated for a Cube, has references to a DataSource and a DataSourceView. The absence of these in the target database (would appear) to cause the XMLA execution to fail. I’m in the process of building the process so that it creates these, if necessary (sample code to come later).
  5. Powershell integration. I’ve a concern that what’s developing is something which is a “bit to .Net programming” for Powershell to comfortably accommodate.  A subsequent step in this journey, may be the creation of some (or maybe one) Powershell cmdlet . More on this as the project progresses.
Digg This
Advertisements

, , , ,

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: