There may be occasions where it is desirable to extract the MDX that Voyager is executing. Common reasons for doing this are the desire to examine the MDX, the need to execute it in another tool to measure performance or do some other kind of analysis on the MDX and its results.
This article focuses on Microsoft Analysis Services (MSAS) being used as a data source.
There are two main ways to capture the MDX that Voyager is executing. One is by turning on some logging on the Voyager's mid-tier server, the Multidimensional Analysis Services server or MDAS server, and the other is to use the Microsoft tool SQL Server Profiler.
MDAS MDX logging
Turning on the logging on the MDAS server will be the preferable way if you do not have access to and installation of SQL Server Profiler or sufficient rights to attach SQL Server Profiler to the Analysis Server that you want to profile. In order to turn on this logging you will have to add some registry settings:
HKEY_LOCAL_MACHINESOFTWAREBusiness ObjectsSuite 12.0MDALogModulesODBOMODULE]
"MDX Query Log"="c:\odbo_mdx.log"
You are free to name the log file what you want. After this registry setting has been entered you will need to restart the MDAS server. The MDX statement will be logged in the file specified in the "MDX Query Log" registry value.
SQL Server Profiler
Using SQL Server Profiler will be your preferred option if want to avoid restart any services. SQL Server Profiler also gives you the ability to filter the results so is useful if you are testing on a system that isn't isolated and so other users may be using the system at the same time. This Voyager: Capturing MDX timings using SQL Profiler contains information on how to capture MDX using SQL Server Profiler.
Extracting the MDX
Voyager makes use of the MSAS feature of session scoped named sets and calculations. This means that it is very rare that you can execute a single MDX statement from the Voyager logs. Usually an MDX statement will reference objects created in previous MDX statements. In order to execute the statement that you are interested in you will have to collect the other MDX statements that create the objects referenced by the final MDX statement. Sometimes the MDX statements that create objects reference further objects. You will need to go recursively through these chains of objects until you find the MDX statements that don't reference any other objects.
Collecting all the MDX statements
For example, if you want to execute the following MDX statement:
WITH
SET __CRYSTAL_SET_ON_AXIS_0__ AS { DISTINCT( { NS1517158104_84B } ) }
SET __CRYSTAL_SET_ON_AXIS_1__ AS CROSSJOIN( CROSSJOIN( CROSSJOIN( CROSSJOIN( { DISTINCT( { NS1495342416_850 } ) } , { DISTINCT( { NS1519596792_85D } ) } ), { DISTINCT( { NS1518636832_862 } ) } ), { DISTINCT( { NS1564436976_897 } ) } ), { DISTINCT( { NS1621635152_9A0 } ) } )
SELECT
NON EMPTY __CRYSTAL_SET_ON_AXIS_0__ DIMENSION PROPERTIES MEMBER_NAME, MEMBER_TYPE, DESCRIPTION, PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME ON COLUMNS,
NON EMPTY __CRYSTAL_SET_ON_AXIS_1__ DIMENSION PROPERTIES MEMBER_NAME, MEMBER_TYPE, DESCRIPTION, PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME ON ROWS
FROM [A Sample Cube]
WHERE ( [Period].[Calendar Year].[3F5ABE36-DAD2-4FEA-96,4B,FE,0,D4,D4,15,B6], [Measures].defaultmember )
Highlighted in bold are various named sets. You can recognize a named set as it starts with ‘NS', is followed by 10 digits then an underscore and a three digit hexadecimal number. From XI 3.1 Service Pack 2 the named sets will no longer have this form. Their names will be based on GUIDs. Here is an example.
WITH
SET __CRYSTAL_SET_ON_AXIS_0__ AS { DISTINCT( { [3B990DDC-390F-4A33-88,CD,D2,FB,BB,C5,36,8C] } ) }
SET __CRYSTAL_SET_ON_AXIS_1__ AS { DISTINCT( { [C0B16D7A-95EE-416E-81,E7,7D,C9,91,ED,EE,55] } ) }
SELECT
__CRYSTAL_SET_ON_AXIS_0__ DIMENSION PROPERTIES MEMBER_NAME, MEMBER_TYPE, DESCRIPTION, PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME ON COLUMNS ,
__CRYSTAL_SET_ON_AXIS_1__ DIMENSION PROPERTIES MEMBER_NAME, MEMBER_TYPE, DESCRIPTION, PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME ON ROWS
FROM [A Sample Cube]
The named sets will have been created by an MDX statement that begins with CREATE SET. In order to run this MDX you need to have run the MDX that create these sets. If we look at the first set, NS1517158104_84B, it was created by:
CREATE SET [A Sample Cube].NS1517158104_84B AS DISTINCT( HIERARCHIZE( DISTINCT( HIERARCHIZE( UNION( NS1517158104_843, NS1496728568_84A ) ) ) ) )
You can see this MDX statement contains two further sets: NS1517158104_843 and NS1496728568_84A.
The relevant create statements need to be found for these sets, etc. until you find an MDX statement that references no other sets. It might look something like the following:
CREATE SET [A Sample Cube].NS1496728568_844 AS DISTINCT( HIERARCHIZE( { } ) )
Or
CREATE SET [A Sample Cube].NS1495751232_845 AS DISTINCT( HIERARCHIZE( ADDCALCULATEDMEMBERS( [Period].[Period Calendar].[Calendar Month].MEMBERS ) ) )
The final step is to locate any calculated members that referenced in the MDX you need to execute. There is a calculated member highlighted in bold in the above example. You can recognize a calculated member as its name will contain a GUID and a member or hierarchy name. Calculated members may also be referenced by some of the earlier CREATE SET statements. The MDX related to this calculated member is:
CREATE MEMBER [A Sample Cube].[Period].[Calendar Year].[3F5ABE36-DAD2-4FEA-96,4B,FE,0,D4,D4,15,B6] AS AGGREGATE ( { [Period].[Calendar Year].&[2009] , [Period].[Calendar Year].&[2008] } )
Executing the collected MDX statements
Once you have collected all the MDX statements you now need to execute them. If you are using a tool like SQL Server Management Studio you can create a script that executes each statement in turn. In this script, each line ends in a semi-colon and there is the word ‘GO' in between each statement. Look at the following screen shot for an example:
If you need to execute the MDX in a tool which can't handle scripts like this you can consolidate all the statements into one MDX statement. You do this by converting the CREATE SET or CREATE MEMBER statements into WITH SET or WITH MEMBER statements. In general MDX SELECT statements only have on instance of the word ‘WITH' at the top with a number of SET or MEMBER statements afterwards. For example the original MDX example we had had two sets defined:
WITH
SET __CRYSTAL_SET_ON_AXIS_0__ AS { ... }
SET __CRYSTAL_SET_ON_AXIS_1__ AS ...
To start converting the create member statements you no longer need the word CREATE at the beginning and you need to strip the name of the cube out of the set name. So for example:
CREATE SET [A Sample Cube].NS1517158104_84B AS DISTINCT( HIERARCHIZE( DISTINCT( HIERARCHIZE( UNION( NS1517158104_843, NS1496728568_84A ) ) ) ) )
Would become (when added to our original example):
WITH
MEMBER [Period].[Calendar Year].[3F5ABE36-DAD2-4FEA-96,4B,FE,0,D4,D4,15,B6] AS AGGREGATE ( { [Period].[Calendar Year].&[2009] , [Period].[Calendar Year].&[2008] } )
SET NS1517158104_84B AS DISTINCT( HIERARCHIZE( DISTINCT( HIERARCHIZE( UNION( NS1517158104_843, NS1496728568_84A ) ) ) ) )
SET __CRYSTAL_SET_ON_AXIS_0__ AS { ... }
SET __CRYSTAL_SET_ON_AXIS_1__ AS ...
Troubleshooting
If you get errors when executing your MDX script or consolidated MDX look out for error which suggest that you have missed a particular MDX statement that defines a named set. For example:
This message suggests that there is no MDX that created the named set NS712548632_233.
If you have an MDX statement that creates the object that the error message is complaining about, make sure that MDX statement is located before any MDX that references the object that it creates.
