Konstantin Gorakine's BI Lingual Blog

Just another WordPress.com site

SSAS 2008 Reporting Actions: MDX vs SQL

with one comment

Just a few days before the BI Summit at Microsoft (04/07/2009, Minneapolis), I was asked to explore some less known features of the Analysis Services 2008 to dig out some interesting stuff to present about. The choice was made to play with Actions, since it is still remains a grey area for many people including BI developers and Business Analysts. The idea was to write two similar Reporting Services reports utilizing MDX and T-SQL respectfully and call them as SSAS Actions from any front-end, provided that front-end supports this integration feature.

 

Well known that BA’s are slicing and dicing their cubes following any path of their wish. But more often than less, those smart people have they own favorite views of data that they like to visit the most. They usually consist of several hierarchies with predefined number of levels and some critical to a current analysis measures. People tend to store those views into Excel PivotTables, ProClarity analytical views, or just build them on the fly by using OWC provided that they have SQL Server Management Studio installed or have access to the OWC online. When views are accessed they can provide majority of the useful information almost instantaneously. I am going to use OLAP Adventure Works DW database to illustrate my point.

 

 

 

This view shows us some Internet Sales broken by Product Categories and Date Calendar hierarchies. Very often, and in this case too, just limited information is presented, whether only numbers or numbers with existing member properties, and naturally, analyst wants to dig deeper on the point of interest, or just get access to the supportive information which is not exposed via properties. Usual course of action in that situation, and I saw it many times, would be to write down the location of the interesting cell – members of the levels which intersect the cell and try to write the ad-hoc query to pull more information about the fact or dimensional members involved. Needless to say that such a deviation from the analysis process brings unnecessary interruptions into the work day of the Business Analyst, making him half SQL expert, half data dictionary explorer. SSAS Actions can help overcome this limitation as well as optimize Analysis services or SQL Server resource management and be quickly deployed once the pattern explored. Let’s look at the picture.

 

 

In our example, there would be eight dimensional levels that present interest. Three come from Product Categories hierarchy and the rest five from Date Calendar. When users select a cell, they ultimately rely on the system to provide current coordinate information regardless any level they are currently at. And this is right. If you at the Subcategory and Month levels, for example, then show me all totals or all orders contributing to a cell, plus emails and age of the customers. So, how do we make a view to be self aware of the current position selected?   

 

Let’s start with the SQL path first. SSAS Report Actions can be built of two types: Report Actions and URL Actions. Let’s work with URL Action. To make SQL SSRS 2008 report self-aware we need to provide 8! Report Parameters, design 8 Datasets for each parameter, link them, so sub-selects work between them, set Default Values to be All Values from before mentioned Datasets, do some tricks on the Analysis Services side to properly pass parameter for each Level not currently selected and set Target Type to Cells and Target Object to All Cells (only option) for the Action in the Analysis Services.

. Here is a part of our initial Main Dataset (not even including Date part):

 

select

 pc.EnglishProductCategoryName

,ps.EnglishProductSubcategoryName

,p.EnglishProductName

,c.FirstName + ‘, ‘ + LastName CustomerName

,c.EmailAddress

,fis.SalesOrderNumber

,d.FullDateAlternateKey

,fis.SalesAmount

,fis.TaxAmt

 

from FactInternetSales fis

inner join Dim_Date_GNet d on fis.OrderDateKey = d.DateKey

inner join DimCustomer c on fis.CustomerKey = c.CustomerKey

inner join DimProduct p on fis.ProductKey = p.ProductKey

inner join DimProductSubcategory ps on p.ProductSubcategoryKey = ps.ProductSubcategoryKey

inner join DimProductCategory pc on ps.ProductCategoryKey = pc.ProductCategoryKey

 

where

    pc.EnglishProductCategoryName in (@Category)

and ps.EnglishProductSubcategoryName in (@SubCategory)

and p.EnglishProductName in (@Product)    

 

Multi-select parameters have to be placed into IN statement and SSRS 2008 will do single-quote wrapping for string values automatically. Nice feature. Here we have only three parameters which are linked in between on the SSRS side to make them cascading.  The trick on the Analysis Services Action side would the following MDX snippet that allows not passing level parameter from the Action completely if selected cell is not at this level.

"http://gnetapps/ReportServer_SQL2K8/Pages/ReportViewer.aspx?/SQL2K8 Reports/Orders Details" + iif([Product].[Category].currentmember.Name = "All Products", "", "&Category=" + UrlEscapeFragment([Product].[Category].currentmember.Name)) + iif([Product].[Subcategory].currentmember.Name = "All Products", "", "&SubCategory=" + UrlEscapeFragment([Product].[Subcategory].currentmember.Name)) + iif([Product].[Product].currentmember.Name = "All Products", "", "&Product=" + UrlEscapeFragment([Product].[Product].currentmember.Name))

This code is not complete: it shows all Product Category levels only, so Date Calendar is not covered. To make this working, we need to know the [All] Member name and wrap all dynamics by UrlEscapeFragment function. Essentially it reads: if your selected cell is not at this level of hierarchy, meaning [All] Member is provided, don’t pass parameter at all, so SSRS can figure the missing and provide Default value, which in our case is All Values from corresponding to this parameter Dataset.  By all means, it looks very cumbersome to me, but can still be achieved with SQL programming. The final report looks like this with all 8 Parameters shown.

 

Let’s turn our attention to MDX. Here is a close-enough report that I wrote to test the concept:

 

with

set Dates as Descendants(StrToSet(@CurrDate), 

                         [Date].[Calendar].[Date])

 

set CustomersOrders as

NonEmpty(CrossJoin([Internet Sales Order Details].[Sales Order Number].Children,

                   [Customer].[Customer].Children

                   ) ,

         (StrToSet(@CurrDate), [Measures].[Internet Sales Amount])

)

 

set Final as

NonEmpty(CrossJoin(CrossJoin(CustomersOrders, Dates),

                  Except([Product].[Product].Members, [Product].[Product].[All Products])), [Measures].[Internet Sales Amount])

 

member [Measures].Email as iif([Customer].[Customer].CurrentMember.Level Is [Customer].[Customer].[Customer],

[Customer].[Customer].CurrentMember.Properties("Email Address"), null), solve_order = 10

 

member [Measures].Category as

[Product].[Product].CurrentMember.Properties("Subcategory"), solve_order = 10

 

select

{

[Measures].Email,

[Measures].Category,

[Measures].[Internet Sales Amount],

[Measures].[Internet Tax Amount]

}

on 0,

Final on 1

from [Adventure Works]

where (StrToSet(@Product))

Here we have only two parameters to work with, since MDX doesn’t care at what level of hierarchy the current cell is selected at and properly passes and calculates values to show on report. As a nice bonus, we don’t need to create additional Datasets for each parameter and make them cascading. With MDX we have only one Dataset and much less parameters to care about. Parameters have to be wrapped by StrToSet Function, since they are passed as strings. Another feature of SSRS 2008 worth of mentioning is that when we run report, the whole hierarchy for each parameter is nicely presented in the parameter dropdown box. I remember myself writing separate MDX queries in SSRS 2000 with Space() function just to provide proper indentation for level of parameter values. Here, once you indicate the parameter placeholder as @Parameter, SSRS creates hidden Dataset, totally by itself, which reduces development time considerably. You can see it on the picture.

And then you can explore self-generated MDX for each parameter and even modify it

Once it’s all done, I created another SSAS Action, this time of Reporting type. Again Target type is Cells and Target Object is All Cells. This will enable the stereo effect of the user navigation: Action will be valid at any Dimension, Hierarchy or Level.

 

Call SSAS Action from any front-ends that support them. Here is a ProClarity screen shot

The final report looks like this with only 2 neat Parameters shown.

Actions provide excellent point of integration between Analysis Services and Reporting Services, can be secured by SSAS Perspectives, be highly customized and it is just a matter of time when they will be widely used throughout the entire organization at analytical and operational levels

 

Advertisements

Written by Konstantin Gorakine

April 14, 2009 at 12:49 pm

Posted in SSAS

One Response

Subscribe to comments with RSS.

  1. Here are this and some other articles on SSAS Actions: http://ssas-wiki.com/w/Articles#Actions

    Sam Kane

    February 24, 2011 at 10:01 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: