MDX behavior in SQL Server 2008 R2 & Earlier Versions

I have been working on Sql Server Analysis Services & MDX for quite a long period. And interestingly on day to day basis I get to understand new concepts, bugs, and some interesting features. 

Few days back, I encountered with a MDX query which behaved differently in SQL Server 2008 R2 and its earlier versions. After posting my query to MSDN forum, and raising this behavior as bug @ http://connect.microsoft.com/, I got an explanation as this behavior was a design issue in SQL Server 2008 and earlier versions and got fixed in SQL Server 2008 R2.

Below is a sample MDX which behaves differently in SQL Server 2008 R2 and earlier versions: Behavior can be seen in "Adventure Works" cube.

MDX 1:

        select [Measures].[Internet Sales Amount] on 0,
        non empty [Date].[Date].[Date] on 1
        from [Adventure Works]
        where ([Customer].[Country].&[United States],[Customer].[State-Province].&[NY]&[US])

MDX 2:

        select [Measures].[Internet Sales Amount] on 0,
        non empty [Date].[Date].[Date] on 1
        from [Adventure Works]
        where ([Customer].[Country].&[United States],{[Customer].[State-Province].&[NY]&[US]})

When I ran both queries on SQL Server 2008 R2 (Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) ), both queries returned same result. i.e.

                                Internet Sales Amount
November 23, 2003      $1,735.98
May 7, 2004               $2,337.27
June 19, 2004             $50.94

Then ran these queries on SQL Server 2005/SQL Server 2008 (Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)   Mar 29 2009 10:27:29   Copyright (c) 1988-2008 Microsoft Corporation  Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3) ). They returned different result sets. i.e.

MDX 1 Output:

                                Internet Sales Amount
November 23, 2003     $1,735.98
May 7, 2004                $2,337.27
June 19, 2004             $50.94

MDX 2 Output:

MDX 2 Output:

                               Internet Sales Amount
November 23, 2003     $14,271.38
May 7, 2004                $17,992.14
June 19, 2004             $22,902.75

Reason: Microsoft Product Team described this behavior as bug in SQL Server 2005 & 2008, which has gotten fixed in SQL Server 2008 R2. As per Microsoft Team:

"The behavior in SQL Server 2005 and 2008 was a bug. Adding curly braces around a single tuple should not impact the query results in any way. A set that contains a single tuple is equivalent to the single tuple. In SQL Server 2008 R2, we fixed the bug, and produce correct results which are identical with and without the curly braces.

The nature of the bug was to ignore the tuple inside the braces because a related attribute was present in the WHERE clause of the query."

MSDN Forum: http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/3cc1da03-d29a-4bc5-994e-b95d0ebcff05

Microsoft Feedback : https://connect.microsoft.com/SQLServer/feedback/details/667987/mdx-behaving-differently-in-sql-server-2008-r2-analysis-services-and-its-earlier-versions

Readers: Kindly share your comments & suggestions. 

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. 

MDX Extraction VB Tool

I had already posted one blog about extracting MDX from Excel pivot table by installing Excel add-in. This post is again about extracting MDX. It is a very small executable file, which can be downloaded from below link and can be used to extract mdx.

Download: MDX Extraction Tool

Steps:

1. Run ExtractMDX.exe file and browse Excel Workbook, from which you want to extract MDX.. 

 

2. Select Worksheet, which has Pivot table created on Analysis Services cube.

3. If selected sheet has Pivot Table, Then it will get listed in "Select Pivot Table" drop down list. Select appropriate Pivot table.

4. Now click on "Extract MDX" button to extract mdx of the selected Pivot table.It may ask for below option:

 

5. Click 'OK'. MDX will get displayed in "Pivot Table MDX" text box.

6. This MDX can be saved/copied for future reference. "Refresh" button can be used to reset all selected settings.

Readers: Kindly share your comments & suggestions.