All Tools   \  

MdxScriptUpdater


Skip to download »

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 his 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 to access the MDX Script of a cube through a not so intuitive way.

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 design 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 is an item with a Text property containing one or more MDX statements. Each Command does not have any ID or Tag or Name property to identify a specific Command. We should rely on an MDX parser to understand the content of a Command, but we do not have a publicly documented one, even if a class of Visual Studio Designer (Microsoft.AnalysisServices.Design.Scripts) already do 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 informations).

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.

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 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 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, but you can always create a single command with many MDX statements, simply putting them into the same string.

The final part 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 that are 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 to the activeMdxScript of the cube. Second, the commands defined into 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 changing only the Marker comment string.

Download File (ZIP)







 
Want to read more?