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.
- Use XMLA to transfer the definition of the cubes from Development into another environment.
- Use Powershell to script the deployment process.
- 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.
- 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.
- The Microsoft.AnalaysisServices, Server Class, Execute Method, which although taking a string as input appears (I’ve yet to have it succeed). I’ve problems with the generated XMLA and the process I’m trying to implement, more on those problems below.
- The ADOMD.NET Microsoft.AnalysisServices.AdomdClient Namespace. The AdomdConnection Class has the CreateCommand method, the AdomdCommand has a CommandText which accepts XMLA (I’ve had this work – but again not fully I’ve issues with the XML at present).