Archive

Posts Tagged ‘DMV’

MDX #43–Find a MDX Calculation Script

November 12, 2014 Leave a comment

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.

pic1

 

 

 

 

 

Here is the result.

pic2

 

 

 

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.

pic3

 

 

 

Here is the result.

 

pic4

 

 

 

 

 

 

 

 

 

 

 

 

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

pic5

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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”.

Categories: MDX Challenges, SSAS Tags: , ,
%d bloggers like this: