MdxScriptUpdater

MdxScriptUpdater is a simple C# class that simplifies updating MDX Scripts into a cube in a production environment. MdxScriptUpdater is provided in form of a sample source code as is.

There are a lot of scenarios where nightly batches would update parts of the MDX Script of a cube. For example, I had a customer with a calculated member for each year with data. We can define the calculated member by hand, but we would need to remember to create a new one each year. Another case is the customer that wants to consolidate their own calculated members, without requiring a new cube deployment.

AMO (Analysis Services Management Objects) is the API that allows you to do these kinds of modifications on a running cube. The problem arises when you want to preserve existing MDX scripts, adding and removing only your own. We tried to solve this problem in a generic way.

Analysis Services object model

The object model offers you access to the MDX Script of a cube through a not-so-intuitive approach.

One Server has a collection of Databases, each Database a collection of Cubes.

Each Cube has an MdxScripts collection: this is strange, because you are used to designing a single MdxScript containing all statements for a cube. However, internal structures of the cube allow the definition of multiple MdxScript objects, but only one is enabled and it is identified by the DefaultScript property value set to true.

Each MdxScript has a collection of Command objects. Each one of those is an item with a Text property containing one or more MDX statements. Command objects do not have ID or Tag or Name properties to identify them specifically. We should rely on an MDX parser to understand the contents of a Command, but we do not have a publicly documented MDX parser – though a class of Visual Studio Designer (Microsoft.AnalysisServices.Design.Scripts) already does this, but it is not supported by Microsoft (see http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/thread/a4a05a16-1a99-49bf-9d0f-bdb163c6cf8f/ for more information).

How can we automatically manage our own MDX statements without affecting the original ones? We decided to add Command objects to the Commands collection of the default MdxScript object of a cube. To be able to detect our own Commands, we add a “comment marker” that we look for when we want to remove our previously-created custom commands.

MdxScriptUpdater implementation

MdxScriptUpdater is a class that provides a simple way to handle custom MDX commands into an existing cube.

The following code shows its use against the AdventureWorks cube. 

MdxScriptUpdater updater = new MdxScriptUpdater( "localhost" );
updater.MdxCommands.Add(
"CREATE MEMBER [Adventure Works].Measures.Sample42 As 42, VISIBLE = 1 ;" );
updater.MdxCommands.Add(
"CREATE MEMBER [Adventure Works].Measures.Sample33 As 33, VISIBLE = 1 ;" );
updater.Update( "Adventure Works DW", "Adventure Works dw" );

You have to create an instance of MdxScriptUpdater specifying the instance server name. Then you add to the MdxCommands list, the strings you want to add. These strings will be prefixed by a predefined marker comment (actually it is /* AUTO-GENERATED */) that you can customize through the Marker property. In this example we add two different commands with a single statement for each one. However, you can always create a single command with many MDX statements, simply putting them into the same string.

The final step is to call the Update method specifying the database ID and cube ID: please note that these names are not always the same as the one displayed. For example, the second parameter is the cube ID (“Adventure Works dw”), which is different from the displayed cube name (“Adventure Works”).

When you call Update, two actions are executed. First, the existing commands containing the marker comment are removed from the activeMdxScript of the cube. Second, the commands defined in the MdxCommands collections are added to the active MdxScript of the cube.

If you need to maintain two separate sets of custom MDX commands, you can use several instances of MdxScriptUpdater only changing the Marker comment string.