Remote Query (Linked Server) Connection Time-Out Issue & Solution

Today, I faced an issue while running a long MDX query in terms of execution time. I went through so many posts on this issue, but didn't get any answer. Below are the details:

Error Message: 

"OLE DB provider "MSOLAP" for linked server "LINKED_SERVER_SSAS" returned message "XML for Analysis parser: The XML for Analysis request timed out before it was completed.".

Description: My SQL Server DB has a Linked Server connection (i.e. named "LINKED_SERVER_SSAS") to connect to SQL Server Analysis Services 2008 OLAP database. This connection is being used in Stored Procs & T-SQL queries to fetch stats from cube. I executed a MDX using this linked server connection, query ran for 10 mins and threw this error message.

Then I tried to ran MDX directly on Analysis Services which ran successfully & took around 18 mins. I checked multiple options to resolve the issue such as

1. Linked Server Connection: Query Timeout property under Server Options
2. SQL Server Analysis Services: Server Properties (including advance server properties)
3. SQL Sevrer Management Studio: Query Execution Property

None of them were successful attempt in my case. 

Solution:

1. Open SQL Server Database Instance Property where linked server is created.
2. Go to Connections.
3. Check "Remote query timeout" value under "Allow remote connections to this server" property. Update this value as per Query/Business requirement. (In my case this value was set to 600 secs.) 

As this value was 10 mins, my remote MDX query faced timeout issue. After updating value to 1800 secs remote MDX query executed successfully. 

Readers: Kindly share your comments & suggestions.

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.