Tag / MSPFE

by Arvind Shyamsundar

Implementing MDX Drillthrough in SSRS

Let’s say for some reason your users want to implement MDX DRILLTHROUGH statement (equivalent to the cube action provided by most OLAP browsers) in SSRS. Here’s a step-by-step way to implement this.

For the purposes of this walkthrough, we are using the standard AdventureWorks Analysis Services database. You can obtain this and the related DW database from http://msftdbprodsamples.codeplex.com/. Do note that you have to manually deploy and process the database after opening the Adventure Works.sln file from its default location of C:Program FilesMicrosoft SQL Server100ToolsSamplesAdventureWorks Analysis Services Projectenterprise

Our objective is to implement two reports:

1. The parent report will list the top 10 products along with their sales amount totals

2. The child (drillthrough) report will display a raw DRILLTHROUGH for the first 500 rows and get back the product quantity of the individual order, along with the order number and date the order was placed

Step 1. Verify the AS database is deployed and processed

Step 2. Here is our (pretty basic) MDX query for the Top 10 listing for current products:

WITH MEMBER PKey AS
[Product].[Product].CurrentMember.Properties(“Key0”)
select {[Measures].[Reseller Sales Amount], PKey } on 0,
(TopCount([Product].[Product Categories].[Product], 10, [Measures].[Reseller Sales Amount])) on 1
from [Adventure Works]
where [Product].[Status].[Current]

Why the calculated member PKey? You will see later.

Step 3. Capture the DRILLTHROUGH query in Profiler

Since writing the DRILLTHROUGH statement by hand can be a bit of a challenge, here it is captured from SQL Profiler:

DRILLTHROUGH  Select  ([Measures].[Reseller Sales Amount],[Product].[Product].&[358])  on 0 From [Adventure Works] RETURN [Reseller Sales].[Reseller Order Quantity],[$Employee].[Employee],[$Delivery Date].[Date],[$Sales Territory].[Sales Territory Region],[$Reseller Sales Order Details].[Sales Order Number]

You can see the product key is highlighted. This is actually the key column syntax as can be seen from the ampersand prefix. So now you know why we selected the PKey calculated member in the previous query.

Step 4. Create the parent report

The first step is to create a shared data source to the SSAS database:

image

Next, we actually create the report. We select the New Report option and use the above shared data source. In the Query Designer, we switch to Query Mode and type in the MDX query we developed earlier:

image

Once this is done, select Tabular report, and move all the columns into the Details section of the report. Title the report as MainReport.

image

In the generated report, delete the PKey column.

Step 5. Create the drillthrough report

This is the tricky part. Normally, the MDX query designer does not support DRILLTHROUGH SELECT syntax. Hence we need to use a workaround. Please note that this workaround may not be officially supported by Microsoft.

We start the same way, by creating a new report and referencing the shared data source. But when you launch the query designer please switch the query type to DMX instead of MDX.

image

After this is switched, also switch to query mode as done before. In the space at the bottom, type in the ‘DMX’ query which is actually our MDX DRILLTHROUGH SELECT query. However now we do need to parameterize the query to accommodate the PKey parameter:

DRILLTHROUGH MAXROWS 50 Select  ([Measures].[Reseller Sales Amount], StrToMember(‘[Product].[Product].&[‘ + @PKey + ‘]’))  on 0 From [Adventure Works] RETURN [Reseller Sales].[Reseller Order Quantity],[$Employee].[Employee],[$Delivery Date].[Date],[$Sales Territory].[Sales Territory Region],[$Reseller Sales Order Details].[Sales Order Number]

Before you can click on OK though, you need to setup the parameter as well. You do that by clicking on the Query Parameters button:

image

We provide a dummy value of 394 so that the fields can be retrieved. The actual value, of course, will be provided by the drillthrough action later.

image

Select the tabular layout and move all the fields to the Details section. Name the report DrillThroughReport.rdl. Finally, set the report parameter PKey type as Hidden:

image

Step 6. Create the drillthrough action

Back to MainReport.rdl, right click on the Product text box and select Text Box properties. In the Action tab, you need to set up the Drillthrough (SSRS this time… don’t get confused by the similar term Smile)

image

We also change the hyperlink look and feel for good measure:

image

And that does it for our reports:

image image

Final Notes

How ‘legitimate’ is it to masquerade a MDX DRILLTHROUGH as a DMX query? As I said before, this is not officially supported. You can refer the to the MS Connect posting at http://connect.microsoft.com/SQLServer/feedback/details/126175/reporting-services-drillthrough-mdx-queries for the ‘won’t fix’ response.

The main reason for my documenting the approach above is that in the specific case I was interested in, the MAXROWS clause is very important. Of course one can argue that using MDX the same can be achieved, but then it is a matter of preference in the end.

BTW, Chris Webb also shares his perspectives on this at http://cwebbbi.wordpress.com/2009/06/16/implementing-analysis-services-drillthrough-in-reporting-services/

If you liked this posting, please rate it! In any case please do take a minute and leave comments, questions etc.

Advertisement
by Arvind Shyamsundar

T-SQL Anti-pattern of the day: ‘all-in-one’ queries

Scenario

A common requirement for enquiry queries on an OLTP database is to have search criteria which are very specific (‘get me details for for OrderID = NNNN’) and also the occasional reports which ask for all the orders (‘get me all the orders, no questions asked’.) Here is a sample from AdventureWorks which illustrates the problem:

CREATE PROCEDURE RptOrder(@OrderID int)
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader
    WHERE (SalesOrderID = @OrderID OR @OrderID IS NULL)
END

What is the meaning of the underlined predicate in the above WHERE clause? It is actually a ‘special case’ where the developer intends to get back all the rows, regardless of the OrderID. This ‘special case’ is triggered by passing in a value of NULL for the @OrderID parameter.

Problem

So while this construct looks good in theory, it lends itself to very poor performance. Take a look at the 2 cases where this procedure is executed.

Case A: with specific OrderID

EXEC RptOrder 43672

Case B: asking for all records

EXEC RptOrder NULL

The plan, it turns out, is the same for both cases and a scan is used! This is despite a seekable index being present on SalesOrderID column for the SalesOrderHeader table:

image

The reason the optimizer chooses to scan the SalesOrderHeader (in this case it chooses a non-clustered index scan) is because it has no way to determine at compile and optimization time, as to what the specific value of @OrderID would be. Hence it has no way to ‘fold’ the (@OrderID IS NULL) expression and therefore has no option but to look at all the records.

Workarounds

‘IF-ELSE’ Workaround: The straightforward workaround in simple cases like the one above is to separate out the 2 cases into an IF-ELSE block:

ALTER PROCEDURE RptOrder(@OrderID int)
AS
BEGIN
    IF (@OrderID IS NOT NULL)
    BEGIN
        SELECT *
        FROM Sales.SalesOrderHeader
        WHERE (SalesOrderID = @OrderID)
    END
    ELSE
    BEGIN
        SELECT *
        FROM Sales.SalesOrderHeader
    END
END

Now, the 2 test cases work as expected. Here are the execution plans:

EXEC RptOrder 43672

image

EXEC RptOrder NULL

image

Dynamic SQL Workaround: However, as the number of predicates in the WHERE clause increase, and if all those predicates (or most of them) have such ‘catch-all’ handling then the IF – ELSE construct becomes unviable. In those cases, a dynamic SQL construct should be considered. Of course, when dealing with dynamic SQL, we must consider security first, including the possibility of SQL Injection and also the Execution Context of the dynamic SQL statement. But that is a topic for another post. Right now, here is how we could handle something like that:

— NOTE: This code is highly simplified and does not provide for any screening

— or protection against SQL injection!!! Provided as-is, confers no warranties.

ALTER PROCEDURE RptOrder(@OrderID int)
AS
BEGIN
    DECLARE @sDynamicSQL nvarchar(4000)
    SELECT @sDynamicSQL = ‘SELECT * FROM Sales.SalesOrderHeader ‘

    IF (@OrderID IS NOT NULL)
    BEGIN
        SELECT @sDynamicSQL = @sDynamicSQL + ‘ WHERE (SalesOrderID = @OrderID)’
    END

    EXEC sp_executesql @sDynamicSQL, N’@OrderID int’, @OrderID = @OrderID
END

Different Code Paths: The cleanest way of course is to consider having separate procedures for each kind of query. For example we can have a procedure called RptSpecificOrder for the case where we are searching by specific OrderID, and another one called RptAllOrders for the ‘get-me-everything’ case. This does have the advantage of clean isolation, but it does not scale easily when the number of predicates are larger. But is does also have the advantage that if we are querying for specific orders 99% of the time, that code path is simplified and optimized accordingly.

Conclusion

Beware of this T-SQL anti-pattern as it is one of the most common ones we see and it does have a huge (negative) impact on query performance. As you can see, if they are not done with these patterns in mind, application design and reporting requirements can have a detrimental effect on OLTP query execution. Separating reporting and OLTP workloads could be the key to solving these kinds of issues. But if separation is not possible, then clever use of separate code paths and stored procedures could help ensure that the most efficient execution plan is selected for each case. For complex queries, dynamic SQL may offer the simplest way out, but due care has to be taken to ensure that permissions and SQL injection issues are kept in mind when dealing with dynamic SQL statements.