Overcoming Performance limitations in ROLAP and PDW


Overview:

The customer uses SSAS ROLAP partition model connect to PDW as data source. The customer wants to do a drill through action, and the ROLAP query generated by the drill through contains a few hundred GROUP BY clauses. The ROLAP query is requesting more data than the customer is asking for in the drill through action. So many GROUP BY clauses is causing PDW at least 5 minutes to compile the query plan and execute the query.

Problem statement:

In general, PDW compilation time is not substantially larger than SQL compilation time. Typically, execution time dominates the overall query runtime and not compilation. PDW has to spend addition time producing the distributed query execution plan (which SQL SMP does not have to) and it computes some statistics that are not needed in SMP compilation. That is one of the main reasons for slow PDW query performance. So with the drill through, there are some performance issue due to a complex query generated by SSAS that takes a long time to get compiled on the PDW side.

Solution:

This is an example of how to address ROLAP performance issues. We will use Adventure Works as an example on how to address this problem.

When a user uses a Drill Through action in Excel a “DRILLTHROUGH” query is generated and sent to the SSAS server. Here is an example of a simple query:

DRILLTHROUGH  Select  ([Measures].[Internet Sales Amount],[Date].[Calendar].[Date].&[20070201])  on 0 From [Adventure Works] RETURN [Internet Sales].[Internet Sales Amount],[Internet Sales].[Internet Unit Price],[$Product].[Product],[$Product].[Color]

Since our server is configured to run in ROLAP mode, we need to get real-time data from the PDW data source through a T-SQL statement. The complexity of this T-SQL statement varies depending on the dimension and fact data design. For designs with huge number of measures and attributes, the query can be quite complicated and will take significant processing time on the PDW data source. Actually, with large number of “GROUP BY”, “JOINS”, and “UNION ALL” PDW will take more time to compile the query even though the amount of data might not be large in some situations.

In order to identify where the bottleneck is and verify that the delay is on the data source, use the profiler to find the time it takes the data source to return the data. Compare it to overall query time and then you can know where the bottleneck.

If you identify a situation where the delay is on the data source side and the query is quite complicated, while data requested by user is much less than requested by SSAS to the data source, then we will show you one way to resolve this problem:

First we add an assembly that helps identify our current member context in Excel. This assembly is then compiled into a dynamic linked library (dll).

The code is given at the end of the document:

 

        [SafeToPrepare(true)]

        public static string ReturnCurrentTuple()

        {

            string output = “(“;

            Dimension d;

            Hierarchy h = Context.CurrentCube.Dimensions[0].AttributeHierarchies[0];

            for (int i = 0; i < Context.CurrentCube.Dimensions.Count; i++)

            {

                d = Context.CurrentCube.Dimensions[i];

                for (int j = 0; j < d.AttributeHierarchies.Count; j++)

                {

                    h = d.AttributeHierarchies[j];

                    //remove measures dimesnion

 

                    if (!h.CurrentMember.UniqueName.StartsWith(“[Measures].”, StringComparison.CurrentCultureIgnoreCase) &&

                        !h.CurrentMember.UniqueName.Equals(h.DefaultMember,StringComparison.CurrentCultureIgnoreCase))

                    {

                            output += h.CurrentMember.UniqueName + “,”;

                    }

                }  

            }

            if (output.EndsWith(“,”,StringComparison.CurrentCultureIgnoreCase))

                {

                   output= output.Remove(output.Length-1)+“)”;

                }

                if (output.Equals(“(“))

                {

                    return “ThisActionIsNotAllowedAtHighestLevel”;

                }

            return output;

        }

 

To register the assembly, use SSMS to add the assembly:

Locate your assembly on your system, add the assembly and any needed information in the dialog box:

Then click OK.

Let’s look at the “Internet Details” Drill Through Action in Adventure Works and create a generic Action that does the same job.

From “Internet Details” Drill Through Action, we identify the columns that need to be returned and create an MDX query that returns the same result set. Below is the “Internet Details” Drill Through action.

 

Next, we define a generic action with target type cells. This is the action that we will invoke in our client like Excel, to generate a ROWSET with a result equivalent to the Drill Through Action.

The condition expression is below to focus on one measure group, which is “Internet Sales”:

not intersect ( MEASUREGROUPMEASURES(“Internet Sales”),[Measures].currentmember ).count=0

The axes we used in the MDX query are the columns that the “Internet Details” Drill Through returns. Notice that this query will generate a simpler ROLAP query and get us the data from the data source but in much less time.

 

Select MEASUREGROUPMEASURES(”Internet Sales) on 0,

NON EMPTY

[Customer].[Customer].[Customer].members

* [Date].[Date].[Date].members

* [Source Currency].[Source Currency Code].[Source Currency Code].members

* [Product].[Product].[Product].members

* [Sales Reason].[Sales Reason].[Sales Reason].members

* [Ship Date].[Date].[Date].members

* [Sales Territory].[Sales Territory Region].[Sales Territory Region].members

* [Sales Territory].[Sales Territory Country].[Sales Territory Country].members

* [Delivery Date].[Date].[Date].members

* [Internet Sales Order Details].[Internet Sales Order].[Internet Sales Order].members

* [Internet Sales Order Details].[Sales Order Number].[Sales Order Number].members

* [Internet Sales Order Details].[Sales Order Line].[Sales Order Line].members

on 1

from (select + ASSP.ReturnCurrentTuple()+ on 0 from [Adventure Works])

 

From SSDT-BI we create the action as shown below:

Now to test this, we use Excel and create a pivot table as shown below:

 

I click on the cell I would like to do a Drill Through and choose the new Action I created.

The output will look like this:

 

This workaround has the following limitations:

  1. MDX query result can be altered by cube calculation script. If you have assignment/scope statement in the cube that alters cube space measure value in the cube calculation script, the MDX query result is affected by the calculation script. Drillthrough is not altered by the calculation script.
  2. In order to get down to the lowest level in the cube, I have to use the key attributes from each dimension. . In a drillthrough action, you do not have to show the key attribute, but in MDX, I have to show the key attribute. Since the key attribute means nothing to the end user, this is not a big problem.
  3. Drillthrough query and MDX query returns different column names. In the Adventure Works sample database, drillthrough returns column names such as [Internet Sales].[$Date.Date], and MDX query returns column names such as [Date].[Date].[Date].[MEMBER_CAPTION]. This is a minor limitation though.

     

Here is the code for the ASSP assembly we used:

/*============================================================================

File: FindCurrentMember.cs

 

Summary: Implements functions to help determine the currentmember on dimensions and hierarchies

 

Date: August 2, 2006

 

—————————————————————————-

This file is part of the Analysis Services Stored Procedure Project.

http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures

 

THIS CODE AND INFORMATION ARE PROVIDED “AS IS” WITHOUT WARRANTY OF ANY

KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE

IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A

PARTICULAR PURPOSE.

============================================================================*/

 

using System;

using System.Collections.Generic;

using System.Text;

using Microsoft.AnalysisServices.AdomdServer;

 

namespace ASStoredProcs

{

public class FindCurrentMembers

{

//ReturnCurrentTuple returns a comma-delimited list of the uniquenames of the currentmember on every attribute hierarchy

//on every dimension, enclosed by parenthesis

[SafeToPrepare(true)]

public static string ReturnCurrentTuple()

{

string output = “(“;

Dimension d;

Hierarchy h = Context.CurrentCube.Dimensions[0].AttributeHierarchies[0];

for (int i = 0; i < Context.CurrentCube.Dimensions.Count; i++)

{

d = Context.CurrentCube.Dimensions[i];

for (int j = 0; j < d.AttributeHierarchies.Count; j++)

{

h = d.AttributeHierarchies[j];

//remove measures dimesnion

 

if (!h.CurrentMember.UniqueName.StartsWith(“[Measures].”, StringComparison.CurrentCultureIgnoreCase) &&

!h.CurrentMember.UniqueName.Equals(h.DefaultMember,StringComparison.CurrentCultureIgnoreCase))

{

output += h.CurrentMember.UniqueName + “,”;

}

 

}

}

if (output.EndsWith(“,”,StringComparison.CurrentCultureIgnoreCase))

{

output= output.Remove(output.Length-1)+“)”;

}

if (output.Equals(“(“))

{

return “ThisActionIsNotAllowedAtHighestLevel”;

}

return output;

}

}

}

 

 

Comments (2)

  1. #12 says:

    Great Post

  2. Greg Galloway says:

    Thanks for posting this. I proposed an enhancement to SSAS which would eliminate the GROUP BYs in the ROLAP drillthrough SQL query here:

    connect.microsoft.com/…/1184785

Skip to main content