Paul Stovell says…

Yesterday I posted a short walkthrough that demonstrated how to make changes to the TFS cube. Today, I want to show how the same change could have been applied using SQL Server Analysis Management Objects.

According to MSDN, when we want to deploy changes to an Analysis Services database, there are five ways in which we can do it.

• Backup/restore. This is usually used when moving between environments, such as from staging to production.
• Deployment Wizard. This is done by giving the system administrator an XMLA script (see below) which they run manually with the help of a wizard.
• Synchronize Wizard. This not only copies the structure of the data, but the data itself. It assumes you already have an analysis services database that you want to use as the source of the synchronization.
• XMLA scripts. Using SQL Server Management Studio, you can right-click any dimension, measure or cube and tell it to script the object. This generates XML for Analysis (XMLA) scripts, which are an XML format for representing an analysis services database.
• Analysis Management Objects (AMO). This is a .NET managed API that you can use to manually explore and apply changes to an analysis services database.

In my scenario, I want to make a simple console application which will add the Full File Path detail (see my previous entry) to the Filename dimension. That rules out Backup/Restore and the Synchronize wizard, as both of these require an existing database. XMLA scripts are a possibility, but their format is pretty limited; you can use them to create a database or dimension from scratch, but they can’t be used to make iterative updates. Since the user may have already made modifications to their dimensions, we need something that will simply add the attribute without overwriting the entire dimension. That leaves AMO.

Analysis Management Objects are a managed .NET API that will be installed into your GAC when you install the Workstation components of SQL Server 2005. At first I assumed they would be painful to work with, but I have to say, AMO is probably one of the cleanest .NET API’s around.

To get started, I created a new C# Console project and added a reference to Microsoft.AnalysisServices, which is actually listed in the “Add Reference” dialog as “Analysis Management Objects” (I wonder how they managed to do that?):

Then, after much trial and error (I couldn’t find many tutorials online) I came up with the following block of code:

Server server = new Server();
server.Connect(
    "Data Source=server;Database=TFSWarehouse;Integrated Security=SSPI;");

foreach (Database database in server.Databases) {
    if (database.Name == “TFSWarehouse”) {
        Dimension dimension = database.Dimensions.FindByName(”File”);
        if (!dimension.Attributes.ContainsName(”Full File Path”)) {
            DimensionAttribute attribute = dimension.Attributes.Add(
                “Full File Path”);
            attribute.ID = “Full File Path”;
            attribute.Name = “Full File Path”;
            attribute.KeyColumns.Add(new DataItem(”File”, “File Path”));
            attribute.NameColumn = new DataItem(”File”, “File Path”);
            attribute.Translations.Add(
                new AttributeTranslation(1033, “Full File Path”));
            attribute.OrderBy = OrderBy.Key;

            database.Update(UpdateOptions.ExpandFull);
            database.Process(ProcessType.ProcessFull);
        }
    }
}

This script re-creates our Full File Path detail exactly as if we would have using Visual Studio, without making any other modifications to the database or overwriting the existing dimension. There are two things to watch out for:

• The call to database.Update(UpdateOptions.ExpandFull). This will send all of the object definitions to the server (including all other attributes and measures, even ones you didn’t touch), but so long as no one else is modifying the cube at the same time this shouldn’t be a problem. The reason for this is that modifying the schema affects the entire database, so the whole thing has to be updated. I tried using dimension.Update(...), but it threw some exceptions saying that other objects had been invalidated.
• The call to database.Process(ProcessType.ProcessFull). You can leave this out and your changes will be picked up the next time the cube is processed (in the case of the TFS cube, this is usually every hour) but it since you’ve made modifications to the cube structure it appears to “invalidate” the cube until it is processed. This means that if you try to connect to the cube using Excel, the cube won’t be shown. This might annoy your users (or bosses!), so forcing the database to be re-processed seems to avoid this problem.

Creating custom dimensions and measures is just as easy. Perhaps it’s not as quick as a T-SQL ALTER statement, but as a .NET API I think AMO is very well designed. I think I’ll consider using SQL Server Management Objects (SMO) when I get around to making changes to the TFSWarehouse database.

Filed under: TFS