Google specialized search for Analysis Services and MDX web resources integrated in my blog

I have been tracking web pages related to Analysis Services and MDX since 2005. Over these few years, I have collected numerous links, articles, blogs on Analysis Services and Multi-Dimensional Expressions.

Normally, I tried to store the key articles on my computer disk but there are many resources which I wished I could just search on. Finally, this Sunday afternoon, I extracted all the URLs and created my own Google custom search engine which tracks the web sites indexed my me. It turned out to be very cool and most of the time gave very accurate results which otherwise was not possible to get in generic Google search engine.

E.g., if you search for a very generic term called "Bucket", here is what you would get:

Well, try the same search in the customized search option in my blog and here is the result:

Here you would get a bunch of links which deals with the "Bucket Analysis" using Multi-dimensional expression (MDX) queries. Well, the result also listed lots of sites from one "Gary Low" whose blog on Analysis Services had a name "Bucket" so, I needed to filter out the "Gary Low" in my search keyword.

There has been a lot of effort on my part gone into setting up this custom search specifically for Analysis Services and MDX and hope that you too can take advantage of it.

Happy searching!!

NextAnalytics and MDX : Part 1 - Swap Cells with Row Labels

NextAnalytics blog on "Can a business intelligence product be used to answer analytic questions?"  raised some valid questions on the complexity, the business users face while analyzing the business data. It did generate quite of few good responses on how MDX can provide the similar solution. I think both the approaches address different level of needs and both can co-exist.

I just started to look at their online demo site. Being in CPM (Corporate Performance Management) industry for about eight years, I can understand the difficulty the functional users face while venturing outside the realm of predefined reports and designing on their own.

I plan to write a multi-part articles on how NextAnalytics solutions can be replicated using MDX. I  would be using "Adventure Works DW" OLAP database to replicate the similar business case.

I saw a very interesting case in NextAnalytics where the result grid cells distinct values can be swapped with either rows or columns as labels. The row or column members appear in against the swapped cell's. This is a very important and useful feature for performing the basket analysis or outliers in a report.

The goal is not to prove who is superior or complex to use. Just the sheer pleasure of validating that MDX is equally capable of fulfilling similar requirement.

Let me illustrate as what is happening in NextAnalytics' "Swap Cells with Row Labels" Feature:

1. This is the start point. The sales data is presented in the cross tab format. (a limited portion of data is displayed, hence in screens, they might not match)

2. Now the variation of Sales data for each day and sales person (column-wise) is calculated and displayed. I guess, the formula would be somewhat at the below line:

Variation = (Sales Amount - Average) / (Standard Deviation for the day)

3. Using "Swap cells to Row Labels", the unique values of variation is shifted to rows and the row members are loaded in the corresponding standard deviation cells.

I would say that the above feature is simply awesome. I can visually say who are the "Outstanding" sales reps (Standard Deviation of more than 3) and who are the laggards (in above screen, standard deviation of -1) and whether they are  consistent in their performance.

Challenge to replicate the same requirement using MDX:

1. Lets get some sample cross tab data

WITH
SET Emp AS
{
[Employee].[Employee].&[290]
,[Employee].[Employee].&[289]
,[Employee].[Employee].&[284]
,[Employee].[Employee].&[291]
,[Employee].[Employee].&[283]
,[Employee].[Employee].&[288]
,[Employee].[Employee].&[282]
,[Employee].[Employee].&[296]
,[Employee].[Employee].&[281]
,[Employee].[Employee].&[286]
,[Employee].[Employee].&[295]
,[Employee].[Employee].&[292]
,[Employee].[Employee].&[287]
,[Employee].[Employee].&[272]
,[Employee].[Employee].&[294]
,[Employee].[Employee].&[293]
,[Employee].[Employee].&[285]
}
SELECT
NON EMPTY
(EXISTING
{
[Date].[Date].&[915]
,[Date].[Date].&[946]
,[Date].[Date].&[975]
,[Date].[Date].&[1006]
,[Date].[Date].&[1036]
,[Date].[Date].&[1067]
}
*
[Reseller Sales Amount]
) ON COLUMNS
,NonEmpty(emp) ON ROWS
FROM [Adventure Works]
WHERE
[Date].[Calendar Year].&[2004]

2. Now, lets calculate the variation. I would convert the variation amounts to the range basket so that we would have few but distinct variation baskets in the cell.

WITH
SET Emp AS
{
[Employee].[Employee].&[290]
,[Employee].[Employee].&[289]
,[Employee].[Employee].&[284]
,[Employee].[Employee].&[291]
,[Employee].[Employee].&[283]
,[Employee].[Employee].&[288]
,[Employee].[Employee].&[282]
,[Employee].[Employee].&[296]
,[Employee].[Employee].&[281]
,[Employee].[Employee].&[286]
,[Employee].[Employee].&[295]
,[Employee].[Employee].&[292]
,[Employee].[Employee].&[287]
,[Employee].[Employee].&[272]
,[Employee].[Employee].&[294]
,[Employee].[Employee].&[293]
,[Employee].[Employee].&[285]
}
MEMBER sales_avg AS
Avg
(
[Date].[Date].CurrentMember * [Emp]
,[Reseller Sales Amount]
)
MEMBER sales_StDev AS
StDev
(
[Date].[Date].CurrentMember * [Emp]
,[Reseller Sales Amount]
)
MEMBER Sales_Variation AS
([Reseller Sales Amount] - sales_avg) / sales_StDev
,format_string = "currency"
MEMBER Sales_Variation_Basket AS
CASE
WHEN
Sales_Variation > 3
THEN
3
WHEN
Sales_Variation > 2
THEN
2
WHEN
Sales_Variation > 1
THEN
1
WHEN
Sales_Variation > 0
THEN 0
WHEN
Sales_Variation > -1
THEN -1
WHEN
Sales_Variation > -2
THEN -2
WHEN
Sales_Variation > -3
THEN -3
END
SELECT
NonEmpty
(
(EXISTING
{
[Date].[Date].&[915]
,[Date].[Date].&[946]
,[Date].[Date].&[975]
,[Date].[Date].&[1006]
,[Date].[Date].&[1036]
,[Date].[Date].&[1067]
}
*
Sales_Variation_Basket
)
,NonEmpty(emp)
) ON COLUMNS
,NonEmpty(emp) ON ROWS
FROM [Adventure Works]
WHERE
[Date].[Calendar Year].&[2004]

3. Now, we need to do "Cells to Row Labels" transformation on the above MDX. What i mean is to retain the dats on the column, put 3,2,1,0,-1,-2,-3 on rows and fill in the corresponding employees in the cells.

The result should be somewhat as below grid ( I have filled couple of cells manually for illustration).

The MDX in item #1 and item#2 is for illustration of concept and not necessary the accuracy of calculation. The trick needed is to use the item#2 MDX and generate the last item#3 output.

Is anybody up for the challenge to write the third part of the MDX query?

Mosha provided the first cut of the query as below:

WITH
  SET Emp AS
    {
      [Employee].[Employee].&[290]
     ,[Employee].[Employee].&[289]
     ,[Employee].[Employee].&[284]
     ,[Employee].[Employee].&[291]
     ,[Employee].[Employee].&[283]
     ,[Employee].[Employee].&[288]
     ,[Employee].[Employee].&[282]
     ,[Employee].[Employee].&[296]
     ,[Employee].[Employee].&[281]
     ,[Employee].[Employee].&[286]
     ,[Employee].[Employee].&[295]
     ,[Employee].[Employee].&[292]
     ,[Employee].[Employee].&[287]
     ,[Employee].[Employee].&[272]
     ,[Employee].[Employee].&[294]
     ,[Employee].[Employee].&[293]
     ,[Employee].[Employee].&[285]
    }
  MEMBER sales_avg AS
    Avg
    (
      [Date].[Date].CurrentMember * [Emp]
     ,[Reseller Sales Amount]
    )
  MEMBER sales_StDev AS
    StDev
    (
      [Date].[Date].CurrentMember * [Emp]
     ,[Reseller Sales Amount]
    )
  MEMBER Sales_Variation AS
    ([Reseller Sales Amount] - sales_avg) / sales_StDev
   ,format_string = "currency"
  MEMBER Sales_Variation_Basket AS
    CASE
      WHEN
        Sales_Variation > 3
      THEN 3
      WHEN
        Sales_Variation > 2
      THEN 2
      WHEN
        Sales_Variation > 1
      THEN 1
      WHEN
        Sales_Variation > 0
      THEN 0
      WHEN
        Sales_Variation > -1
      THEN
        -1
      WHEN
        Sales_Variation > -2
      THEN
        -2
      WHEN
        Sales_Variation > -3
      THEN
        -3
    END
  MEMBER Measures.[-2] AS
    Generate
    (
      Filter
      (
        Emp
       ,
        Sales_Variation_Basket = -2
      )
     ,
      Employee.Employee.CurrentMember.Name + ","
    )
  MEMBER Measures.[-1] AS
    Generate
    (
      Filter
      (
        Emp
       ,
        Sales_Variation_Basket = -1
      )
     ,
      Employee.Employee.CurrentMember.Name + ","
    )
  MEMBER Measures.[0] AS
    Generate
    (
      Filter
      (
        Emp
       ,
        Sales_Variation_Basket = 0
      )
     ,
      Employee.Employee.CurrentMember.Name + ","
    )
  MEMBER Measures.[1] AS
    Generate
    (
      Filter
      (
        Emp
       ,
        Sales_Variation_Basket = 1
      )
     ,
      Employee.Employee.CurrentMember.Name + ","
    )
  MEMBER Measures.[2] AS
    Generate
    (
      Filter
      (
        Emp
       ,
        Sales_Variation_Basket = 2
      )
     ,
      Employee.Employee.CurrentMember.Name + ","
    )
SELECT
  {
    [Date].[Date].&[915]
   ,[Date].[Date].&[946]
   ,[Date].[Date].&[975]
   ,[Date].[Date].&[1006]
   ,[Date].[Date].&[1036]
   ,[Date].[Date].&[1067]
  } ON COLUMNS
,{
    Measures.[-2]
   ,Measures.[-1]
   ,Measures.[0]
   ,Measures.[1]
   ,Measures.[2]
  } ON ROWS
FROM [Adventure Works]

And the output is as desired (partial screenshot):

While the above meets the requirement, there is one significant limitation that we need to overcome.

In the solution, the distinct values of "Sales_Variation_Basket" are manually defined as Measures.[3].....Measures.[-3].

We need to automate it. I mean, using MDX, to read the set of "Sales_Variation_Basket" values, read the distinct values out of it and sort it, and then put the distinct values of "Sales_Variation_Basket" on rows and then put the employees in the result cells.

I am sure we would have the solution soon.

Art of reading MDX articles

I learnt MDX much more by reading articles by knowledgeable people in this industry, Mosha for example. These extremely well written articles are sprayed with numerous MDX queries each explaining the nuances of MDX. Earlier, I used to open two windows, one for reading article and other for executing MDX queries. Many a times, continuous copy and paste distracted the subject context.

Of late, I have devised a new technique. I just copy and paste the complete article in SS Management Studio's MDX query editor and just read in that. Whenever, I need to execute the query, I just select the query text portion and execute it. Now, I don't have to switch between windows or lost my focus, of course at the cost of text formatting. I wish I could do the same thing in Mosha's MDX Studio but it does not support text wrapping. (I have devised another technique where I copy and paste the article in my Notepad++ text editor and then re-wrap the text to the desired width and then paste).

It works great for me to read text based articles with lots of MDX in it without getting distracted.

MDX Expression Builder : Need for a tool making it easier for functional users to write MDX expressions, queries.

MDX is great for business problem analysis. Moving averages, % of parent, YoY growth etc are business needs and surely MDX can handle it. There is only one hitch though. Most of the business users can not write it. There are so many tools including SS Management studio which allows me to write complex SQL queries many a times just using my mouse, but hardly anything to write MDX expressions as flexibly.

To make a start, at least a tool can be provided with the below functionality:

1. The interface would display the dimension hierarchies and and an area to create MDX expression. The area would have two panes, one which shows the MDX expression in AST hierarchy and other pane shows the real MDX script text.

2. The user can write MDX expression directly in the MDX script text pane and wheh toggles to AST pane, the expression would be shows as a expression tree.

3. The user can drag and drop the functions, operators or dimension members onto the AST pane and when switch it back to MDX script text pane, the MDX query text would be regenerated.

4. Of course, some wizards, templates can be pre-stored for common business expressions which gives the functional user some starting point to use it as is or further customize it.

In MDX studio by Mosha, I can paste a MDX query ant it generates a Syntax tree which i find extremely useful to analyse complext MDX statements. The problem is it is only one way, i.e. MDX query text to tree and not the other way around. Long back, I had seen an evaluation version of a tool named as Hungry Dog (if i remember correctly) which used to do it and I can not find them anymore.

E.g. in below screen, the left side provides a tree representation of a MDX expression and right a text representation. The tree one is easier to understand. Over time, the it could be made much more user friendly.

Unless we have something like above, we can not service those super intelligent functional users who understand business need in depth but can not analyze it themselves fully due to lack of a good tool to define MDX expressions.

If anybody knows a good tool which works on Analysis Services, kindly let me know.