Finding out MDX calculation scripts is a common task
A co-worker recently asked me what the calculation for a calculated measure is in our reporting cube.
If you have the Analysis Services project in Visual Studio locally, it is easy to find what the calculation script is from the Calculations tab in the cube designer.
But what if you don’t have the VS project handy, and you have access to the cube from SQL Server Management Studio?
EXPRESSION field in $SYSTEM.MDSCHEMA_MEASURES
Here is a simple script you can run to quickly get the calculation script.
This script queries the SSAS Dynamic Management View $SYSTEM.MDSCHEMA_MEASURES. The EXPRESSION field will return the actual calculation script. You will need to run the DMV queries in the MDX query editor, not the SQL query editor.
Here is the result.
Here is the query in text.
SELECT CUBE_NAME
, MEASURE_UNIQUE_NAME
, EXPRESSION
, MEASUREGROUP_NAME
from $SYSTEM.MDSCHEMA_MEASURES
where MEASURE_UNIQUE_NAME = ‘[Measures].[Ratio to Parent Product]’
Use $SYSTEM.DBSCHEMA_COLUMNS to find all the columns in a DMV
You might ask how do I know what are all the columns in this view. Here is a DMV view, $SYSTEM.DBSCHEMA_COLUMNS, you can query to find out all the columns in a DMV view.
Here is the result.
Here is the query in text.
SELECT *
FROM $SYSTEM.DBSCHEMA_COLUMNS
WHERE TABLE_SCHEMA = ‘$SYSTEM’
AND TABLE_NAME = ‘MDSCHEMA_MEASURES’
$SYSTEM.DISCOVER_SCHEMA_ROWSETS is the only DMV name you need to remember
You might also ask how would I know to use the view $SYSTEM.MDSCHEMA_MEASURES to find out the calculation script.
The answer is to just remember one view, $SYSTEM.DISCOVER_SCHEMA_ROWSETS.
SELECT * FROM $SYSTEM.DISCOVER_SCHEMA_ROWSETS
The $SYSTEM.DISCOVER_SCHEMA_ROWSETS view will show you all the DMV views that you can use to get the metadata about your dimensions and cubes.
DMVs can be very useful for documenting SSAS databases, monitoring usage and activity. To know more about how to use these DMVs check out our book “MDX with SSAS 2012 Cookbook”.
