September 16, 2012

Order by – MDX v.s SQL

One of the most used SQL syntax in reporting is Order by, so it is normal to expect a similar functionality in MDX with a simple keyword.

How to order the results based on single column in SQL?

Let us first execute a simple query against AdventureworksDB

 SELECT  geo.City
,sum(SalesAmount) InternetsalesAmount
FROM dbo.FactInternetSales fis
join DimCustomer cus on fis.CustomerKey = cus.CustomerKey
join dbo.DimGeography geo on cus.GeographyKey = geo.GeographyKey
join dbo.DimDate dt on fis.OrderDateKey = dt.DateKey
where dt.CalendarYear = 2005
group by geo.City
order by City asc
The above query filter the records for 2005 and order the results based on city in ascending order

image


How to get the similar results in MDX?


In MDX we need to



  • Remove the empty (null) values using non empty function
  • order the values by

    • using the Order function
    • with non hierarchy ascending order (basc)
    • based on the city names derived using membervalue(used to convert the values to string) function.
  SELECT
{ [Measures].[Internet Sales Amount] } ON COLUMNS,
order(nonempty( [Customer].[Customer Geography].[City]
, [Measures].[Internet Sales Amount]
)
,[Customer].[Customer Geography].currentmember.membervalue
,basc
)

ON ROWS
FROM
[Adventure Works]
WHERE
( [Date].[Calendar].[Calendar Year].&[2005] ) ;
image



How to order the results based on multiple columns in SQL?

SELECT  geo.City,sum(SalesAmount) InternetsalesAmount
FROM dbo.FactInternetSales fis
join DimCustomer cus on fis.CustomerKey = cus.CustomerKey
join dbo.DimGeography geo on cus.GeographyKey = geo.GeographyKey
join dbo.DimDate dt on fis.OrderDateKey = dt.DateKey
where dt.CalendarYear = 2005
group by geo.City
order by 2 asc, City asc
The above query first sort the results by internetsales amount and if we have the same total amount for multiple cities then they get sorted in ascending order. 
Note: check the sort order in the order by class, it is internet sales amount followed by city



image


How to order the results based on multiple columns in MDX?


In MDX we need to



  • Remove the empty (null) values using non empty function
  • order the values by

    • using the Order function
    • in non hierarchy ascending order (basc)
    • based on the city names derived using the membervalue function.

  • Order the values by

    • using the Order function
    • with non hierarchy ascending order (basc)
    • based on internet sales amount.

  SELECT
{ [Measures].[Internet Sales Amount] } ON COLUMNS, 
order (order(nonempty( [Customer].[Customer Geography].[City], [Measures].[Internet Sales Amount])


             ,[Customer].[Customer Geography].currentmember.membervalue
             ,basc
       )
       ,
       [Measures].[Internet Sales Amount]
       ,basc
       )
ON ROWS
FROM
[Adventure Works]
WHERE
( [Date].[Calendar].[Calendar Year].&[2005] ) ;


Note: check the sort order, it is city followed by internet sales amount


Why should we eliminate the Null values in the MDX query? Won’t that degrade query performance?


SSAS cubes never store the Null values so it is merely a representation. So by including the Nonempty function you are actually increasing the performance of the query


Why we are reversing the order of columns in MDX?


In SQL we are using a single order by keyword which accepts multiple columns. It orders the results by first column and if the values are same then it further order the rows based on second column. So in our example the results are ordered by internet salesamount and when the values are same (e.g 3374.99) it has further been ordered by CIty


In MDX we are using two order by function, so we are ordering the results twice. The query works only because of the stable sort algorithm which is used in MDX order function


E.g. let us use Bubble sort to prove this theory


In the below example I have already sorted the results by City and using the bubble sort to sort the results based on Amount.


Bubble sort is a stable sort and as you can see, whenever amount are equal between multiple rows, it remember and retain the position of pre-Sorted CITY order.


wsrd3tzw

August 12, 2012

MDX Wildcard search in parameters dropdown -- SSRS

 

Recently I got a request from my insurance client to filter the SSRS MDX report based on the Policy holder, one choice is to give them the complete list but as we have more than 5,000 policy holders(that is only commercial insurance), it is really impossible for them to choose even with alphabetical ordered list

As the Google search has become the norm, we want to implement wildcard dropdown boxes where user can type in the partial name of the policy holder and then choose one/multiple items. Unfortunately SSRS dropdown parameter list doesn't support the wild card search. So we implemented it via cascaded parameters.

To mimic the problem let us search the Employee dimension in the Adventureworks cube

let us first implement the wild card search in MDX.

There is no direct equivalent of SQL LIKE operator is available in MDX but we can use one of the below methods to achieve wildcard search

  • Using Custom library .
  • Other easy method is to use VBA instr function to search the member value and filter the dimension list

So if i want to filter all the Employees whose first/middle/last name is David then the query would like below

  SELECT  { } ON COLUMNS
, FILTER(
[Employee].[Employees].members,
vbamdx!INSTR([Employee].[Employees].CURRENTMEMBER.Name,'David',1 >= 1 )
) on rows
FROM [Adventure Works]

The above query search for David using the Instr function. if the instr function found the match then will return the position of David string which will be either 1 or greater than 1 . If there is no match found then it will return Zero. We then can use this as the logical expression in the filter function to get the subset of interested employees.


In order to use this query in SSRS we need to include member properties as shown below. Also replace static name ‘David’ with suitable parameter name

 SELECT  { } ON COLUMNS
, FILTER(
[Employee].[Employees].members,
vbamdx!INSTR([Employee].[Employees].CURRENTMEMBER.Name,@EmplyeeName,1 >= 1 )
) DIMENSION PROPERTIES
MEMBER_CAPTION,
MEMBER_UNIQUE_NAME,
PARENT_UNIQUE_NAME,
LEVEL_NUMBER ON ROWS
FROM [Adventure Works] CELL PROPERTIES VALUE



Final solution will look like blow.


image


As you can see, using the cascaded parameters and MDX wildcard search, we can limit the list of employees. If you want to allow the user to select multiple employees then select the “Allow Multiple Values” in the parameter properties.



Happy Reading Smile

July 22, 2012

MDX - Use formatting to change the Null values into other Characters

 

Have you ever used the If statement to convert the null into some special character mainly for Excel based reports? If then there is no need for it, you can easily achieve this via formatting

Let us first do this in a wrong way

In the below code I am checking for null values and if it is null then replace it with “–” . It works perfectly fine and it can be directly linked with excel reports and handed over to users

with member measures.[Reseller Sales Amount Formatted] as
iif( isempty([Measures].[Reseller Sales Amount])  ,"-",[Measures].[Reseller Sales Amount])
Select {[Measures].[Reseller Sales Amount], measures.[Reseller Sales Amount Formatted] }on 0
     ,[Date].[Calendar].[Date].members 
      * 
      [Geography].[Geography].[State-Province].members on 1
from [Adventure Works]

This has clearly produces the required result  as shown below,


image


What is the best way then?


Though this is correct in terms of results there is a simpler way of doing this with format_String as shown below



with member measures.[Reseller Sales Amount Formatted] as
[Measures].[Reseller Sales Amount], FORMAT_STRING = "$#,##.00;($#,##.00);$#,##.00;-"
Select {[Measures].[Reseller Sales Amount], measures.[Reseller Sales Amount Formatted] }on 0
     ,[Date].[Calendar].[Date].members 
      * 
      [Geography].[Geography].[State-Province].members on 1
from [Adventure Works]

image


So, Are we only talking about Aesthetic reasons here?


Actually no. Using the format option gives you lot of performance advantage in complex queries especially in Crosstab report.


E.g. Test the below query it takes around 30 seconds



with member measures.[Reseller Sales Amount Formatted] as
iif( isempty([Measures].[Reseller Sales Amount])  ,"-",[Measures].[Reseller Sales Amount])
Select [Geography].[Geography].[State-Province].members on 0
     ,[Date].[Calendar].[Date].members      on 1
from [Adventure Works]
where  measures.[Reseller Sales Amount Formatted

Just change it with format option, it will get executed under 5 seconds and that should convince you.


Happy Reading Smile