MDX

Using TraceEvent for MDX Debugging

Using TraceEvent for MDX Debugging

January 23, 2012

Do you know how it feels like to hear an old song and have a whole lot of memories come flooding back to you? Well, something sort of similar happened to me today. I was doing my weekly reading and going through a brilliant post by Microsoft MVP – Greg Galloway (blog). That was when I noticed that he had released a new functionality to the Analysis Services Stored Procedures (ASSP) Project on Codeplex. I decided to test this new functionality and was thinking of a scenario where I could use it, when I remembered about an old post in the Analysis Services forum that I intended to blog about but never did. New-Tool-in-the-ASSP-Toolkit_thumb1 Now the new addition to the ASSP toolkit is the TraceEvent class. Quoting from the ASSP project page – “The function in the TraceEvent class is FireTraceEventAndReturnValue. It can be used to detect when Analysis Services is evaluating an expression since the function merely fires a trace event that appears in Profiler as a User Defined event, then returns the integer value you pass in. This function can be used in a scenario such as determining when the cell security expression is evaluated.

Here is an example MDX query displaying the usage of this function:

with member [Measures].[test] as ASSP.FireTraceEventAndReturnValue(99)
select [Measures].[test] on 0
from [Adventure Works]

The signature of the function is:
FireTraceEventAndReturnValue(int)”
Having a base in programming, it was natural for me to expect a sort of “watch window” when I started learning MDX. I wanted to view the values and the change in them while the query was being debugged, but sadly there was none. The TraceEvent class will help alleviate that pain to some extent and I will be showing it in the next few minutes. For the purpose of demonstration, I would be taking the two queries from the AS forum post I mentioned before –
I) PROBLEM
Query 1

with
member [Product].[Product Categories].[Agg] as Aggregate(TopCount([Product].[Product Categories].[Product].members, 3, [Measures].[Internet Sales Amount]))
select {[Measures].[Internet Sales Amount]} on 0,
[Date].[Calendar].[Month].members on 1
from [Adventure Works]
where ([Product].[Product Categories].[Agg])

Query 2

select {[Measures].[Internet Sales Amount]} on 0,
[Date].[Calendar].[Month].members on 1
from [Adventure Works]
where (TopCount([Product].[Product Categories].[Product].members, 3, [Measures].[Internet Sales Amount]))

Results
1-MDX-Query-results_thumb
For a MDX beginner, you would expect the results to be same for both queries, as you are just moving the formula of the calculated member in Query 1 to the WHERE clause in Query 2. The reason for the difference is because calculations overwrite current coordinates while axes don’t overwrite the current context. So when the first query is being executed, the current coordinates are overwritten and executed in the context of the query axis which means it will check the top 3 products for each of the month and display the result. However, in the second case, you don’t have any calculations defined and hence the current coordinates would be the one in the WHERE clause, which is the Top count of products across all months. (You would get a better idea by reading the original post in the forum)
Enough of theory and let us see whether we can debug and understand the working of the mdx queries using the TraceEvent class. For the purpose of seeing the results, we will modify the queries as shown below and see the results in the Profiler.
II) DEBUGGING THE PROBLEM
Query 1 for July 2005

with
member [Product].[Product Categories].[Agg] as Aggregate(TopCount([Product].[Product Categories].[Product].members, 3, ASSP.FireTraceEventAndReturnValue([Measures].[Internet Sales Amount])))
select {[Measures].[Internet Sales Amount]} on 0,
[Date].[Calendar].[Month].&[2005]&[7] on 1
from [Adventure Works]
where ([Product].[Product Categories].[Agg])


Profiler Results for Query 1
2-Query-1-Profiler-results_thumb1
Even without knowing the theory part, you can find out that the measure gets evaluated at 4, which is after the axes get evaluated (1, 2 and 3 in the image above) and hence, the top 3 products would be calculated for the current context of July 2005. I have not pasted the entire trace here but if you run it, you can see that the measure values for each product for the month of July 2005 is returned in the Integer Data column. I have marked the top 3 values in red and you can see that this corresponds to $261,231 which is what we saw in the results for the first query.
Query 2 for July 2005

select {[Measures].[Internet Sales Amount]} on 0,
[Date].[Calendar].[Month].&[2005]&[7] on 1
from [Adventure Works]
where (TopCount([Product].[Product Categories].[Product].members, 3, ASSP.FireTraceEventAndReturnValue([Measures].[Internet Sales Amount])))

Profiler Results for Query 2
3-Query-2-Profiler-results_thumb1
Here, you can see that the measure gets evaluated at 1, which is before the axes get evaluated (3 and 4 in the image above). I have also marked the top three values for the products, and on close examination, it can be found to be the top 3 values for products across all months.
4-Top-3-products-for-all-months_thum
So once the top products are found, the axes are evaluated and then the results are serialized in 5, which gives us the value of $221,852 which is what we saw in the results for the second query.
III) SUMMARY
Even without knowing the theory, we were able to debug the MDX queries and find out why there was a difference with the help of the TraceEvent function. This technique can be elaborated and used across variable scenarios for query debugging. In short, the TraceEvent functionality is a great function to have when you don’t know why you are getting some particular results in your queries. And yeah, don’t forget to thank Greg for this!Smile

Posted by SQLJason, 2 comments
Performance problems with Dynamic Named Sets

Performance problems with Dynamic Named Sets

April 19, 2011

The last couple of days, I have been fiddling around with an interesting mdx query passed on to me by Hrvoje Piasevoli (blog | twitter | MSDN). Though I wouldn’t be explaining about the query as Hrvoje himself would blog about it soon (once he gets off the SSAS forums and twitter, which could be never Winking smile), I was trying to recreate the scenario in the cube and was involving a lot of dynamic sets. That is when I hit upon the problem – queries which were running in split seconds started to drag. And I am not even speaking of the measures which involved those dynamic named sets. If you are a beginner to named sets, I would suggest having a quick glance through the post – Static Named Sets v/s Dynamic Named Sets before reading further. Dynamic Set Performance The steps to demonstrate the issues are given below. Problem 1) Before illustrating the problem, let us take the average times of a simple query in cold cache. For the demo, the query shown below is used SELECT [Measures].[Internet Sales Amount] ON 0
FROM [Adventure Works]
WHERE {[Date].[Calendar Year].&[2002],[Date].[Calendar Year].&[2001]} The average time in cold cache was around 63ms in my laptop. 1 Query before set creation 2) Now a simple dynamic set is created in the cube calculated member script as shown below CREATE DYNAMIC SET CURRENTCUBE.[ExSet]
AS (EXISTING [Date].[Calendar].[Calendar Year]); The average time of the previous query is again checked in cold cache and is found out to be around 63ms still. All well and good so far. 3) Now create a measure which will refer the dynamic set, like shown below CREATE MEMBER CURRENTCUBE.[Measures].SetString
AS SetToStr(ExSet),
VISIBLE = 1  ; The average time of the previous query is again checked and suddenly, it has come up to 1.1s which is an increase of 75%. – Issue 1 2 Query after set creation 4) Let us also note down the time taken to display the SetString measure which was created. 3 Query for SetString measure Even this takes around 1.1s on cold cache which is a lot for such a simple operation. – Issue 2 Reason On closely checking the traces from Profiler, it is found that when the SetString measure referring the dynamic set is not created, only the above query (in step 1) gets executed (after the calculated member script is loaded into memory). However, after the SetString measure is created, in addition to the above events, a series of Query dimension events and Calculate Non Empty Current events are generated which indicates that the dynamic set is being evaluated, even though it is not being used in the query. 4 Reason for performance degradation Workaround A workaround for this issue is to trick the engine into thinking that the dynamic set is not being referred. This can be done by replacing the set with StrToSet. Eg – the dynamic set ExSet will be replaced by StrToSet(‘ExSet’). Hence, the calculated member definition in the script will be modified as shown below CREATE MEMBER CURRENTCUBE.[Measures].SetString
AS SetToStr(StrToSet(‘ExSet’)), VISIBLE = 1 ; This will solve the issue and bring the performance back to normal. 5 Solution Note This issue had been raised in Connect almost 20 months ago and Microsoft had responded saying that they will investigate this in the future release. As of now (SQL Server 2008 R2 – 10.50.1600.1), there has been no improvements and part of it could be because of the very low number of votes. Considering that the performance could get a lot more worse in the case of complex dynamic sets, it is a serious issue for me and needs to be fixed. If you also feel the same, please vote for this Connect issue – Calculated member with a reference to dynamic named set Kills the cube’s performance.

Posted by SQLJason, 1 comment
MDX Basics

MDX Basics

April 5, 2011
One of my colleagues in MindTree Ltd, Bragdishwaran U, had recently taken a session on MDX Basics. I found the session well structured and specially asked his permission to publish the slides from his deck for the benefit of my readers who are completely new to MDX.
 
Download MDX Basics ppt by clicking here

 
Feel free to comment and let me know if there are any additional areas you would like to see being included here.
Posted by SQLJason, 0 comments
Using TYPED for member properties in SSRS MDX queries

Using TYPED for member properties in SSRS MDX queries

April 3, 2011

Recently I got a very interesting query from one of my colleagues. He was trying to sort a table in a SSRS report based on an integer member property, but the sort results were coming wrong according to him. He said that the report was treating the property field as a character and claimed that this was a problem reported by many people all over the MSDN forums. That is when I thought of putting forth this post.

Sort by member property report

To illustrate the problem as well as the solution, I will be using the Adventure Works cube.

1) The requirement is to sort the Sub Categories based on the key values in the report. For this, a new dataset is made with the following MDX query

WITH
MEMBER [Measures].[Mkey] AS
[Product].[SubCategory].CurrentMember.Properties(“key”)
SELECT
NON EMPTY
{[Measures].[Mkey]} ON COLUMNS
,NON EMPTY
{
[Product].[SubCategory].Children
} ON ROWS
FROM [Adventure Works];

MDX Query original

2) Drag and drop the SubCategory and MKey fields into a table.

SSRS report table with SubCategory and Mkey

3) Right click on the Subcategory row group for the properties and then sort it by Mkey field.

Table sort by member property SSRS report

4) Now preview the report.

Incorrect sort order

We can see that the key has been considered as a string for sorting, because of which 10 comes after 1. For the proper numerical sorting, we will have to follow the steps below.

Solution

1) Modify the MDX query to include the TYPED keyword.

WITH
MEMBER [Measures].[Mkey] AS
[Product].[SubCategory].CurrentMember.Properties(“key”,TYPED)
SELECT
NON EMPTY
{[Measures].[Mkey]} ON COLUMNS
,NON EMPTY
{
[Product].[SubCategory].Children
} ON ROWS
FROM [Adventure Works];

To quote MSDN – “The Properties function returns the value of the specified member for the specified member property. By default, the value is coerced to be a string. If TYPED is specified, the return value is strongly typed. If the property type is intrinsic, the function returns the original type of the member. If the property type is user defined, the type of the return value is the same as the type of the return value of the MemberValue function.”

2) Now preview the report and the sorting would be done in the expected way.

Correct sorting order

Posted by SQLJason, 0 comments
Where to store calculated measures – SSAS cube or SSRS reports?

Where to store calculated measures – SSAS cube or SSRS reports?

March 15, 2011

As I mentioned in one of my previous posts, nowadays I am busy preparing the design document of my new project and sending it around for reviews. If there is one question I could pick from the list that keeps coming back to me frequently, that would be this – why are you recommending to store all the report level calculations in the cube rather than in the reports? Usually I get away by just answering it is better for query performance and the reports would come out faster. Of course, I didn’t see any value explaining the technical behind-the-scenes part on why the reports would come out faster to my reviewers (ok, I admit, I am just lazy). But this time, one of my over-zealous peers was adamant on knowing the reason and I decided to demonstrate it to him finally. Store calculated measure in cube demo Before I illustrate the actual demonstration, it is necessary to understand the importance of caching in SSAS. Designing a system which will optimally utilize the caching feature is very critical for query performance and user experience. To quote SSAS 2008 Performance Guide “ During the execution of an MDX query, the query processor stores calculation results in the query processor cache. The primary benefits of the cache are to optimize the evaluation of calculations and to support reuse of calculation results across users. To understand how the query processor uses caching during query execution, consider the following example. You have a calculated member called Profit Margin. When an MDX query requests Profit Margin by Sales Territory, the query processor caches the non null Profit Margin values for each Sales Territory. To manage the reuse of the cached results across users, the query processor distinguishes different contexts in the cache: • Query Context—contains the result of any calculations created by using the WITH keyword within a query. The query context is created on demand and terminates when the query is over. Therefore, the cache of the query context is not shared across queries in a session.
Session Context —contains the result of any calculations created by using the CREATE statement within a given session. The cache of the session context is reused from request to request in the same session, but it is not shared across sessions.
Global Context —contains the result of any calculations that are shared among users. The cache of the global context can be shared across sessions if the sessions share the same security roles. The contexts are tiered in terms of their level of reusage. At the top, the query context is can be reused only within the query. At the bottom, the global context has the greatest potential for reusage across multiple sessions and users. “ So, in short, calculations defined in Query Context can be reused only within the query while calculations defined in Global context can be reused across sessions. This means that measures which are defined within the global scope will come out faster after the first time it has been run (since it is already there in the cache) compared to the query scope measures. Now let us see what happens when we execute an MDX query with the same calculation defined within a report and within a cube. For the demonstration, I would be using the Adventure Works cube and the following calculation – ([Measures].[Internet Sales Amount] / 2) +1 Option 1 – Make calculated member within SSRS Report 1) Make a simple report dataset with the following query WITH MEMBER [measures].[non cache] as
[Measures].[Internet Sales Amount]/2 +1
SELECT NON EMPTY { [measures].[non cache] } ON COLUMNS,
NON EMPTY { ([Customer].[Customer Geography].[State-Province].ALLMEMBERS ) }  ON ROWS
FROM [Adventure Works] non cache is the name of the new calculated measure. 2) Use the fields from the query to make a simple table in the layout. Report Layout 3) Before previewing the report, clear the cache of the database cache by running the following XMLA query in Management Studio. <ClearCache xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine“>
  <Object>
    <DatabaseID>Adventure Works DW</DatabaseID>
  </Object>
</ClearCache> 4) Keep SQL Server profiler ready and preview the report now. You would be getting the below activity in the trace 1 query scope measure - after cache clearing We can see some Progress Report Begin and End events which indicates that some I/O is happening to fetch the data. The EventSubClass column of Query Subcube event clearly says that the data being fetched is non-cache data, as expected. 5) Now refresh the report once again and check out the profiler activity. 2 query scope measure - using cache We can see that there is a Get Data from Cache event. But on further observation, we can see that this data is from the measure group cache, which means that this is from the cache of the storage engine. Data would need to be passed back to the formula engine to execute any possible calculations, as is shown by the following AS querying architecture diagram. MDX Query execution architecture The formula Engine processes MDX Queries, figures out what data is needed to cover them, and forwards this information to the storage engine for retrieval. It then performs all calculations needed for the query. The Storage Engine handles all the reading and writing of data. It also retrieves and aggregates the data the formula engine requested at query run time. Now let us see what happens when we define the calculation inside the cube and use it in our reports. Option 2 – Make calculated member within SSAS Cube 1) Make the calculation in the calculated member script of the cube as shown below Create measure in cube cached is the name of the new calculated measure. 2) Edit the dataset query in the report to use the new measure SELECT NON EMPTY { [measures].[cached] } ON COLUMNS,
NON EMPTY { ([Customer].[Customer Geography].[State-Province].ALLMEMBERS ) } ON ROWS
FROM [Adventure Works] 3) Make the appropriate changes in the table also to include cached instead of non cache measure. Report Layout for cached measure 4) Before previewing the report, clear the cache of the database cache by running the following XMLA query in Management Studio. <ClearCache xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine“>
<Object>
<DatabaseID>Adventure Works DW</DatabaseID>
</Object>
</ClearCache> 5) Keep SQL Server profiler ready and preview the report now. You would be getting the below activity in the trace 3 global scope measure - after cache clearing We can see that the activities and time are pretty much the same compared to the first approach when the cache was cleared. 6) Now refresh the report one more time and have a look at the profiler activity. 4 global scope measure - using cache Voila! Now we can see that there is a Get Data from Cache event and the Text Data clearly says that it is of Global Scope. The description in the EventSubClass column says that the data is from the calculation cache which means it is from the cache of the formula engine. Note : For the more inquisitive minds, the three main EventSubClass column values of the Get Data from Cache event are

  • 1 – Get data from measure group cache :- storage engine cache, so data from here would need to be passed back to the formula engine to have any calculations executed. This could mean that something in your query is not allowing the calculations to be cached in the formula engine. Some
  • 2 – Get data from flat cache :- formula engine cache, but this is used to store individual cell values and is only used when a query is executing in cell-by-cell mode. So if you see this it is an indication that your query is not executing in block mode
  • 3 – Get data from calculation cache :- formula engine cache and is probably the best type of cache because it is used in block mode and stores the final results of any calculation.

Conclusion Calculated measures defined within the report use the query context for caching while those defined within the cube uses the global context. Hence, from a query performance angle, it is always better to define measures within the cube than in the reports as the global context would be much faster after the first time the report has been run and also because the cache can be re-used across sessions. References This is an advanced topic and at least some of you would have found it a bit confusing (now you know why I don’t explain such stuff! Winking smile). You might want to take your time and go through the following references to know more about Storage Engine & Formula Engine or MDX performance tuning in general. 1) Microsoft SQL Server 2008 Analysis Services Unleashed (Book)
2) SSAS 2008 Performance Guide (Whitepaper)
3) Identifying and Resolving MDX Bottlenecks (Whitepaper)
4) Blogs by Mosha Pasumansky particularly
      a) Inside OLAP Engine: SE cache sharing
      b) Inside OLAP Engine: Cache Prefetching
      c) Take advantage of FE caching to optimize MDX performance
5) Blogs by Chris Webb particularly
      a) Formula Caching and Query Scope
      b) Reporting Services-generated MDX, Subselects and Formula Caching
      c) Missing Members and the Formula Engine Cache
      d) Now() and the Formula Cache
      e) Arbitrary-shaped sets and the Storage Engine cache

Posted by SQLJason, 1 comment
Static Named Sets v/s Dynamic Named Sets

Static Named Sets v/s Dynamic Named Sets

May 24, 2010

It is no big secret that most of the questions that are asked in interviews today are repeated. One of those questions which interviewers just love to ask again and again is the difference between  a static named set and a dynamic named set. The popularity of this question is further aided by the fact that this particular feature of dynamic named sets was newly introduced in SQL Server 2008. So here I continue my interview question series on this topic. Many times, it can happen that we end up writing very complex MDX expressions revolving sets. To simplify such expressions or to improve the performance of some queries, we can extract the definitions of some sets into a separate named set expression which are called named sets. A named set can be created either using a CREATE SET statement or a WITH SET statement (For syntax, refer Building Named Sets in MDX). In SQL Server 2005, there was only the Static named sets feature but in SQL Server 2008, both Dynamic and Static named sets are present. Static Named Sets The value of a static named set is evaluated either when the CREATE SET statement is executed (if it is defined inside the cube) or right after the WHERE clause is resolved (if the set is defined within a WITH clause). Hence, when a query references a static named set, the set is not resolved again in the context of the current coordinates which explains why it is known as a static named set. To explain this better, let us consider a scenario where we want to count the number of years for a particular country where the order is greater than 4000. WITH 
  MEMBER [measures].[country count] AS 
    Count
    (
      Filter
      (
        [Date].[Calendar Year].[Calendar Year].ALLMEMBERS
       ,
          (
            [Customer].[Customer Geography].CurrentMember
           ,[Measures].[Internet Order Quantity]
          )
        > 4000
      )
    ) 
SELECT 
  {
    [Measures].[Internet Order Quantity]
   ,[measures].[country count]
  } ON 0
,{
      [Customer].[Customer Geography].[Country].ALLMEMBERS
    * 
      [Date].[Calendar Year].Children
  } ON 1
FROM [Adventure Works]; This will give us the following output result 1  Now let us simulate creating a static named set in the Adventure Works cube and see the result by running the following query in SQL Server Management Studio (SSMS). CREATE 
  STATIC SET [Adventure Works].[Filtered Year] AS 
    Filter
    (
      [Date].[Calendar Year].[Calendar Year].ALLMEMBERS
     ,
        (
          [Customer].[Customer Geography].CurrentMember
         ,[Measures].[Internet Order Quantity]
        )
      > 4000
    ) ; GO
WITH 
  MEMBER [measures].[country count] AS 
    Count([Filtered Year]) 
SELECT 
  {
    [Measures].[Internet Order Quantity]
   ,[measures].[country count]
  } ON 0
,{
      [Customer].[Customer Geography].[Country].ALLMEMBERS
    * 
      [Date].[Calendar Year].Children
  } ON 1
FROM [Adventure Works]
WHERE 
  [Customer].[Country].&[Canada]; Note that we are using GO statement in between, else you will get an error in SSMS saying that multiple statements are not allowed. As per the explanation above, the set would be evaluated when the CREATE SET statement is executed and the coordinates at that context for Customer Geography is ALL. So now when the set is referenced in the query below, it will the count for the ALL member even if Canada is selected. Static set result To verify this, I queried the cube to just return the count of countries when no country is selected and the country count matches with the above result. Total result for all countries Now what do we do if I just wanted to consider the coordinates in the where condition and return the result based on it (in this eg, consider the filter condition on Canada and return just the periods having order greater than 4000 for Canada which is 1)? That is where Dynamic sets come to our aid. Dynamic Named Sets With the help of dynamic named sets, we can create a named set which would be revaluated in context of each query that references it. It is evaluated in the context of WHERE clause and SubSelect of every query but are not evaluated in the context of every cell. The most visible difference would be in the case of dynamic named sets created in the cube. Let us use the previous example and see what happens when we use a dynamic set instead of a static set. CREATE 
  DYNAMIC SET [Adventure Works].[Filtered Year] AS 
    Filter
    (
      [Date].[Calendar Year].[Calendar Year].ALLMEMBERS
     ,
        (
          [Customer].[Customer Geography].CurrentMember
         ,[Measures].[Internet Order Quantity]
        )
      > 4000
    ) ; GO
WITH 
  MEMBER [measures].[country count] AS 
    Count([Filtered Year]) 
SELECT 
  {
    [Measures].[Internet Order Quantity]
   ,[measures].[country count]
  } ON 0
,{
      [Customer].[Customer Geography].[Country].ALLMEMBERS
    * 
      [Date].[Calendar Year].Children
  } ON 1
FROM [Adventure Works]
WHERE 
  [Customer].[Country].&[Canada]; The output is shown below Dynamic Set result Finally we get the result we want taking the filter conditions in the WHERE clause (for Canada). We can also test what happens when we remove Canada from the WHERE clause and introduce it on the rows. CREATE 
  DYNAMIC SET [Adventure Works].[Filtered Year] AS 
    Filter
    (
      [Date].[Calendar Year].[Calendar Year].ALLMEMBERS
     ,
        (
          [Customer].[Customer Geography].CurrentMember
         ,[Measures].[Internet Order Quantity]
        )
      > 4000
    ) ; GO
WITH 
  MEMBER [measures].[country count] AS 
    Count([Filtered Year]) 
SELECT 
  {
    [Measures].[Internet Order Quantity]
   ,[measures].[country count]
  } ON 0
,{
      [Customer].[Customer Geography].[Country].&[Canada]    * 
      [Date].[Calendar Year].Children
  } ON 1
FROM [Adventure Works] As mentioned, the set will be evaluated using the current coordinates which would be ALL for Country as there is nothing on the WHERE clause. The output is shown below Dynamic set result with Canada on rows  Now that you have understood that the difference between a static named set and dynamic named set is not only the difference in their syntax, you might want to go to the following blogs by Mosha (who is called the father of MDX) and understand how a named set improves performance

 

Posted by SQLJason, 6 comments
OR operation in MDX

OR operation in MDX

May 10, 2010

A seemingly harmless question which makes you ignore it thinking you know the answer, but the moment you give it a second look, you start scratching your head. This was my first reaction when I was asked to implement an OR operation in MDX and I really had to think to come up with an answer, quite contrary to the no-brainer I thought this question was. Ever since, this has been in my list of interview questions and this blog would be the second part of the series where I unveil some of my favourite questions. Problem Lets face a scenario where you have a relation – Month<—Date & Week<—Date. Now the requirement is such that the Date field should be displayed such that it should either belong to the Month selected or the Week selected. Answer Follow the steps below to get the answer 1) The query below will display the Date member captions for all dates WITH 
  MEMBER [measures].[nam] AS 
    [Date].[Date].CurrentMember.Member_Caption 
SELECT 
  [measures].[nam] ON 0
,[Date].[Date].Children ON 1
FROM [Adventure Works]; 2) Suppose we need to display all the Dates such that it either belongs to the month March,2007 or Week 27 CY 2005. P.S. : Just giving a where clause with a set like the query given below will not work as it will perform an AND operation rather than an OR operation. WITH 
  MEMBER [measures].[nam] AS 
    [Date].[Date].CurrentMember.Member_Caption 
SELECT 
  [measures].[nam] ON 0
,[Date].[Date].Children ON 1
FROM [Adventure Works]
WHERE 
  (
    {[Date].[Calendar Weeks].[Calendar Week].&[27]&[2005]}
   ,{[Date].[Calendar].[Month].&[2007]&[3]}
  ); 3) The correct way is to bring both the conditions inside a set but as of now, they are of different dimensionality and can’t be used together as the members of the same set. Hence we create a tuple of the calendar week and month such that the valid condition of one attribute would be used with the default member of the other dimension (which is usually the All member). WITH 
  MEMBER [measures].[nam] AS 
    [Date].[Date].CurrentMember.Member_Caption 
SELECT 
  [measures].[nam] ON 0
,[Date].[Date].Children ON 1
FROM [Adventure Works]
WHERE 
  {
    (
      [Date].[Calendar].DefaultMember
     ,[Date].[Calendar Weeks].[Calendar Week].&[27]&[2005]
    )
   ,(
      [Date].[Calendar].[Month].&[2007]&[3]
     ,[Date].[Calendar Weeks].DefaultMember
    )
  }; This query would give the correct output as shown below OR operation in MDX This query makes use of the fact that ([Date].[Calendar].DefaultMember,[Date].[Calendar Weeks].[Calendar Week].&[27]&[2005]) = (Date].[Calendar Weeks].[Calendar Week].&[27]&[2005]) We use this technique to make the dimensionality of both the week and month conditions the same. Once this concept is understood, implementing OR conditions in MDX would be a piece of cake. 

Posted by SQLJason, 0 comments
MDX : Non Empty v/s NonEmpty

MDX : Non Empty v/s NonEmpty

May 6, 2010

Now that the recession pains have allayed, most of my colleagues are having a very busy time either taking interviews or giving interviews. As for myself, I thought of sharing some thoughts on my interview questions through my blogs and here goes my first attempt where I have tried to differentiate between Non Empty and NonEmpty. Find the link to my blog here – MDX : Non Empty v/s NonEmpty Non Empty vs NonEmpty Title MDX

Posted by SQLJason, 1 comment
Searching substrings in MDX

Searching substrings in MDX

April 11, 2010

A quick tip for the beginners. Most of you would be familiar with substring searches in SQL. Today, we will see how to replicate the same scenario in MDX.

1) Suppose we have to find all the employee names having ‘David’ as a substring, we write the following SQL query

SELECT EmployeeName, [Internet Sales Amount] FROM Employee WHERE EmployeeName LIKE ‘%David%’

This same query can be replicated in MDX as below

SELECT [Measures].[Internet Sales Amount] on COLUMNS, filter([Employee].[Employees].allmembers, instr([Employee].[Employees].currentmember.member_caption,’David’)>0) on ROWS from [Adventure Works]

Query result

2) To find all the employee names not having ‘David’ as a substring, we write the following SQL query

SELECT EmployeeName, [Internet Sales Amount] FROM Employee WHERE EmployeeName NOT LIKE ‘%David%’

This same query can be replicated in MDX as below

SELECT [Measures].[Internet Sales Amount] on columns, filter([Employee].[Employees].allmembers, instr([Employee].[Employees].currentmember.member_caption,’David’)=0) on ROWS from [Adventure Works]

3) You can write multiple conditions also. For e.g., to find all the employee names having ‘David’ as a substring but not having ‘am’ as a substring, we write the following SQL query

SELECT EmployeeName, [Internet Sales Amount] FROM Employee WHERE EmployeeName LIKE ‘%David% AND ’EmployeeName NOT LIKE ‘%am%’ 

This same query can be replicated in MDX as below

SELECT [Measures].[Internet Sales Amount] on columns, filter([Employee].[Employees].allmembers, instr([Employee].[Employees].currentmember.member_caption,’David’)>0 and instr([Employee].[Employees].currentmember.member_caption,’am’)=0) on ROWS from [Adventure Works]

Update (25/08/2010)

You can also do the same using Stored Procedures. You can create your own Stored Procedures to achieve this or you can also download Analysis Services Stored Procedures project from:
http://www.codeplex.com/wikipage?ProjectName=ASStoredProcedures

After that, you can use the code like this:

SELECT 
{} ON 0
,ASSP.Like([Employee].[Employees].Members
,”%RA%”
,[Employee].[Employees].CurrentMember.Name)
ON 1
FROM [Adventure Works];

Update Courtesy – Charles Wang (MSDN Moderator)

Title MDX

Posted by SQLJason, 1 comment
Different ways of referencing objects in MDX

Different ways of referencing objects in MDX

April 8, 2010

Back to the basics today. Often, I have been asked by colleagues what are the different ways of referencing objects like Dimension, Hierarchies, Levels and Member names in MDX. Most of the times, I have seen people following the way they learnt from their seniors or simply the first way they found to be working without understanding the intricacies. This post is meant to shed some light on the basics. There are 3 main ways of referencing objects in MDX, namely

  1. By Name :- This is the most easiest way of referencing an object, by just specifying the name. For ex., if you have a member Australia in the level Country, you can just refer to it by specifying Australia.
    • Comments :- If you have multiple objects with the same name, for ex., having Australia in 2 dimensions, then the result would be ambiguous. Also, it hits the performance badly as all the dimensions and hierarchies have to be iterated to resolve the member name. Moral of the story – you might not want to use this approach unless you want to get fired by your boss. This is the worst way to reference an object in MDX.
  2. By Qualified Name :- For a dimension, the qualified name is equal to the name of the dimension in square brackets. Ex. [Time] for time dimension. For a hierarchy, it is the the dimension name followed by the hierarchy name in the following format – [Time].[Calendar Hierarchy]. For the level, it is the qualified name of the hierarchy followed by the level name in the format – [Time].[Calendar Hierarchy].[Year]. For a member, the qualified name is the qualified name of the level or hierarchy followed by the names of all parents of the current member and the name of the current member -  [Time].[Calendar Hierarchy].[2009].[Q2].[May].
    • Comments :- This method is faster when compared to the previous method and works well in most cases. The only issue with this is that if the qualified name for the member is created by concatenating all the parent levels, then the qualified name becomes immobile. It will get outdated the moment a child changes it parent and it can happen especially in cases like where a customer changes his city.
  3. By Unique Name :- Analysis Services assigns a unique name to every object and this can be retrieved by using a schema rowset or from the results of another MDX request. Usually, the unique name is generated by using the member key (rather than the name like in the previous 2 methods). For ex., if the key for 2009 was 1412, then it would have been referenced as [Time].[Calendar Hierarchy].&[1412].
    • Comments :- This is the most correct way of referencing a MDX object. However care should be taken that the unique name should never be generated by the MDX programmer, and should always be retrieved from the server. The reason is that the generation of unique names is a complex task and the providers that support MDX may have different algorithms for generating unique names. Also, the rules might change from version to version and hence, to make sure that the application we build is compatible with the next version, never generate unique names on your own.

Title MDX

Posted by SQLJason, 2 comments