Context Aware And Customised Drillthrough

#SQLCoop this month has decided to post on a “something new you found really useful”. So, here is my bit from the Analysis Services world.

Customising Drillthrough

Recently, I have just come across a requirement to make a drillthrough to return better column names. This drillthrough would be generated from an area in the Pivot Table that is connected to SSAS Multidimensional Model. Then, the extension to this requirement is to create a separate drillthrough that filters the dataset to be context aware. As an example, if a user accesses the drillthrough on “Quality Reason” measure for Internet Sales, it should provide all the internet sale transactions that are associated to “Quality Reason” sales.

Luckily the two requirements above are easily solvable if you have Analysis Services Stored Procedure (ASSP) installed. You can download ASSP from Codeplex and also follow the thorough Installation Instructions.

Big thank you to the people involved in the ASSP project: Chris Webb (t | b), Greg Galloway (b), Darren Gosbell (t | b) and team.

Introduction To The Data

In this article, we use Adventure Works dataset (AdventureWorksDW2012 that you can download from CodePlex). We will create a new view that retrieve the Internet Sales Reason and perform counts on the three main reasons for the Sales, such as Quality, Marketing and Promotion. This is a fairly typical request from business.

The view looks like this:

USE AdventureWorksDW2012
GO
CREATE VIEW vInternetSalesReason AS
WITH SalesReason AS (
	SELECT	isr.[SalesOrderNumber],
			MAX(IIF(sr_o.[SalesReasonKey] IS NOT NULL, 1, 0)) SalesReasonQuality,
			MAX(IIF(sr_p.[SalesReasonKey] IS NOT NULL, 1, 0)) SalesReasonPromotion,
			MAX(IIF(sr_m.[SalesReasonKey] IS NOT NULL, 1, 0)) SalesReasonMarketing
	FROM dbo.[FactInternetSalesReason] isr
		LEFT OUTER JOIN dbo.[DimSalesReason] sr_o
			ON sr_o.[SalesReasonKey] = isr.[SalesReasonKey]
			AND sr_o.[SalesReasonReasonType] = 'Other'
			AND sr_o.[SalesReasonName] = 'Quality'
		LEFT OUTER JOIN dbo.[DimSalesReason] sr_p
			ON sr_p.[SalesReasonKey] = isr.[SalesReasonKey]
			AND sr_p.[SalesReasonReasonType] = 'Promotion'
		LEFT OUTER JOIN dbo.[DimSalesReason] sr_m
			ON sr_p.[SalesReasonKey] = isr.[SalesReasonKey]
			AND sr_p.[SalesReasonReasonType] = 'Marketing'
	GROUP BY 
		isr.[SalesOrderNumber])
SELECT
	fis.[SalesOrderNumber],
	fis.[OrderDateKey],
	SUM(fis.[SalesAmount]) AS SalesAmount,
	SUM(fis.[OrderQuantity]) AS OrderQuantity,
	ISNULL(m.[SalesReasonQuality], 0) AS [SalesReasonQuality],
	ISNULL(m.[SalesReasonPromotion], 0) AS [SalesReasonPromotion],
	ISNULL(m.[SalesReasonMarketing], 0) AS [SalesReasonMarketing]
FROM dbo.[FactInternetSales] fis 
	LEFT OUTER JOIN SalesReason m
	ON m.[SalesOrderNumber] = fis.[SalesOrderNumber]
GROUP BY 
	fis.[SalesOrderNumber], 
	fis.[OrderDateKey],
	ISNULL(m.[SalesReasonQuality], 0),
	ISNULL(m.[SalesReasonPromotion], 0),
	ISNULL(m.[SalesReasonMarketing], 0)

You can use an existing Multidimensional Analysis Services solution on Adventure Works and add the new view to it; or you could create a fairly simple SSAS solution just like what I have here: SSAS Solution for Sales Reason (zip file).

Nice Headers In Drillthrough

There is a list of fields that we want to retrieve, such as Order Date, Sales Order Number, Sales Amount, Order Quantity, Quality Reason, Promotion Reason and Marketing Reason.

The objective is to create a drillthrough with the set of fields above, as illustrated below:

The steps are very easy and are also mentioned on the ASSP CodePlex website. To summarise,

1. Create a New Action. Provide a name (in this instance it is “Drillthrough Internet Sales”

2. Action Target is set to “Cells” with Target Object of “All Cells”.

3. Optionally you can provide Condition where the drillthrough can be accessed from. In this instance, we do not need to provide anything since we want to make the drillthrough accessible from all cells in the Pivot Table.

4. In the Action Content section, choose “Rowset” for the Type.

5. In the Action Expression, use the ASSP.GetCustomDrillthroughMDX function and provide the attributes or measures to be displayed with their new names. Please note that [Sales Amount] measure in the cube has Currency format. As far as I know the currency formatting is not being exposed to the DRILLTHROUGH command (which is what ASSP is generating). For further discussion on formatting, please see here.

ASSP.GetCustomDrillthroughMDX("
   NAME([$Date].[Date]) as [Order Date]
  ,NAME([$Sales Reasons].[Sales Order Number]) as [Sales Order Number]
  ,[Internet Sales Reason].[Sales Amount] as [Sales Amount]
  ,[Internet Sales Reason].[Order Quantity] as [Sales Order Quantity]
  ,[Internet Sales Reason].[Quality Reason] as [Quality Reason (1=Y, 0=N)]
  ,[Internet Sales Reason].[Promotion Reason] as [Promotion Reason (1=Y, 0=N)]
  ,[Internet Sales Reason].[Marketing Reason] as [Marketing Reason (1=Y, 0=N)] 
 ")

6. In the Pivot Table, you should be able to access “Show Internet Sales” now, which will display the data.

Context Aware Drillthrough

Another good use case for ASSP is where the drillthrough needs to be context aware. As an example, if a user right clicks on one of the cells under “Quality Reason” for Year 2002, the drillthrough should only return Sales information relating to those that are associated with Quality Reason in 2002.

The steps are similar to the above, except for the Action Expression section, where the ASSP.ExecuteDrillthroughAndFixcolumns function is used.

The Action Expression looks like this:

'call ASSP.ExecuteDrillthroughAndFixcolumns("' + 
 ASSP.GetDefaultDrillthroughMDX(
 (
   IIF ([Measures].CurrentMember.Name = 'Quality Reason',
     ([Sales Reasons].[Sales Reason Quality].&[1], [Measures].[Quality Reason]),
   IIF ([Measures].CurrentMember.Name = 'Promotion Reason',
     ([Sales Reasons].[Sales Reason Promotion].&[1], [Measures].[Promotion Reason]),
   IIF ([Measures].CurrentMember.Name = 'Marketing Reason',
    ([Sales Reasons].[Sales Reason Marketing].&[1], [Measures].[Marketing Reason]), 
    ([Sales Reasons].[Sales Reason Quality].&[0], [Measures].[Quality Reason])
    )))
  )
)
+ '")'

Once the above Action is setup, the Drillthrough menu is available from the Additional Action menu. If the user moves the right click on a “Marketing Reason” cell, the caption will change to “Sales made based on Marketing Reason”.

The expected output for the “Sales made based on Quality Reason” looks something like below:

Wrap Up

Analysis Services Stored Procedure (ASSP) is one of the most useful extension for Multidimension project. One of its incredibly useful features is enabling customisation on Drillthrough. In this article, we solve customisation on the columns to be brought by a drillthrough, and context aware drillthrough.

What other interesting learnings do the rest of #SQLCoop peeps have to share?

• Alerts – Who Knew? by Chris Yates
• Change data through CTE’s by Jeffrey Verheul
• Data Models, SQL Server, SQLite, and PowerShell by Mickey Stuewe

What is #SQLCoop? Check out how we start here.

Further Reading

Analysis Services Stored Procedure (ASSP) on CodePlex

Preserve Measure formatting on drill through action from Excel on TechNet Forum