Calculated Member & Named Set Definition

Analysis Services provides flexibility to add business logic by creating Calculated Member/Named Set on Measures or Dimensions in existing Cube. These are custom MDX expressions defined and saved as part of cube definition. There are multiple options to create these calculations depending on scope of use.

1. Create Members in Calculations tab of Cube designer.
2. Create Calculated Members at run time using client tools. For Example: SQL Server Management Studio, ProClarity...

Generally, developers know about logic behind calculated members. Sometimes, as a user, we want to know what expression has been used for these calculated members. And there is no option available to get the MDX logic behind these members except using calculation tab of cube designer.

We can use DMVs (i.e. Dynamic Management Views) to get mdx expressions of Calculated Members. DMVs are only available for SQL Server Analysis Services 2008 and not for earlier versions.

Below are stored procedures to get Calculated Members expression: In order to run these procedures, create a linked server on Analysis Server. Here in these examples, linked_server name is 'CUBE'.

1. Get Calculated Measure Expression:
Parameters detail:

1. @linked_server: Name of Linked Server created on Analysis Server.
2. @cube_name: Cube Name
3. @measure_name: Calculated Measure Name

/*
EXEC GET_MEASURE_EXPRESSION @linked_server = 'CUBE'
                        , @cube_name = 'Adventure Works'
                        , @measure_name = 'Internet Gross Profit'
*/

CREATE PROCEDURE GET_MEASURE_EXPRESSION
    @linked_server VARCHAR(100)
    ,@cube_name VARCHAR(100)
    ,@measure_name VARCHAR(100)   
AS
BEGIN
    SET NOCOUNT ON
   
    DECLARE @cube_script VARCHAR(max)
    DECLARE @script VARCHAR(max)
    SET @cube_script = 'SELECT * FROM $system.mdschema_measures
                        WHERE CUBE_NAME = ''''' + @cube_name + '''''
                        AND MEASURE_NAME = ''''' + @measure_name + ''''''

    SET @script = 'SELECT
                    "MEASURE_NAME" AS MEASURE_NAME
                    ,"MEASUREGROUP_NAME" AS DIMENSIONS
                    ,"EXPRESSION" AS EXPRESSION
                    ,"DEFAULT_FORMAT_STRING" AS FORMAT_STRING
                    FROM OPENQUERY('+@linked_server+', '''
                    +@cube_script+ ''')'
    --PRINT @script
    EXEC (@script)
   
    SET NOCOUNT OFF
END

2. Get Named Set Expression:
Parameters detail:

1. @linked_server: Name of Linked Server created on Analysis Server.
2. @cube_name: Cube Name
3. @set_name: Set Name

/*
EXEC GET_SET_EXPRESSION @linked_server = 'CUBE'
                        , @cube_name = 'Adventure Works'
                        , @set_name = 'Core Product Group'
*/

CREATE PROCEDURE GET_SET_EXPRESSION
    @linked_server VARCHAR(100)
    ,@cube_name VARCHAR(100)
    ,@set_name VARCHAR(100)   
AS
BEGIN
    SET NOCOUNT ON
   
    DECLARE @cube_script VARCHAR(max)
    DECLARE @script VARCHAR(max)
    SET @cube_script = 'SELECT * FROM $system.mdschema_sets
                    WHERE CUBE_NAME = ''''' + @cube_name + '''''
                    AND SET_NAME = ''''' + @set_name + ''''''
   
    SET @script = 'SELECT
                    "SET_NAME" AS SET_NAME
                    ,"DIMENSIONS" AS DIMENSIONS
                    ,"EXPRESSION" AS EXPRESSION
                    FROM OPENQUERY('+@linked_server+', '''
                    +@cube_script+ ''')'
    --PRINT @script
    EXEC (@script)
   
    SET NOCOUNT OFF
END

These procedures can be modified as per requirement to get more detail about Set/Member expressions.
More on DMV queries: SSAS DMV(Dynamic Management View)


Readers: Kindly share your comments & suggestions.