Microsoft OLAP by Mosha Pasumansky

Take advantage of FE caching to optimize MDX performance

I have decided to move my blog from the sqljunkies site to sqlblog site. The transition started about a year ago. During this year I was posting every blog on both sites. This transition period has ended now, and from now on all the posts will be on sqlblog only. I apologize for all the inconvenience that it causes, but sqljunkies had too many problems to deal with, and the current owners didn't seem interested in fixing them. The sqlblog owners, on the other side, were were responsive and supportive, making sqlblog much more attractive solution for the blog publishers. The latest post is Take advantage of FE caching to optimize MDX performance.

MDX and partitioning

The up-to-date version of this post with comments enabled is at sqlblog Partitions have been a built-in feature of Analysis Services ever since the first version. Naturally, all the engine subsystems, including the MDX query optimizer, work well with partitions. There are rare cases, however, when over-aggressive prefetching can generate query plan which is not optimal with respect to the partitioning scheme. Today we will review one such scenario and see how MDX could be rewritten to suit the partitioning...(read more)

Optimizing Count(Filter(...)) expressions in MDX

As the readers of my blog know, bulk evaluation mode (called "block computation mode" in Katmai) delivers much better performance in MDX than the cell-by-cell evaluation mode. Therefore the most important optimization technique with MDX in Analysis Services is to rewrite MDX in such a way that makes block computations possible. Easy to say, but not always easy to do. With the release of Katmai's CTP5, Microsoft published the BOL article outlining conditions when block computations are and are not possible. Chris Webb picked on this article and mentioned in his blog, that "the list of set functions is a bit limited (where is Filter?)". In reply I said that I didn't think that Filter function was that common inside the MDX calculations, but Greg Galloway immediatelly came up with a good example, one that involves Count(Filter(...)). This is indeed a common calculation - every time we want to know how many entities are there which satisfy certain condition. (Greg's example was to find out how many physicians performed 10 or more cases during certain time period.)

Let's build an example using Adventure Works sample cube and see how we can optimize it. In Adventure Works terms, our task would be to find the number of products which had more than 5 orders placed over the Internet. Such calculation can be written as

Count(Filter([Product].[Product].[Product], [Measures].[Internet Order Quantity] > 5))

Indeed, neither in AS2005 nor in AS2008 the Filter function is optimized to work in the block computation mode, therefore the query involving this calculation will execute in the cell by cell mode:

Note: Here and below, change [Date ] to remove trailing space after Date in order to run the query. I had to insert this space due to a bug with the blog hosting software (Community Server)

WITH MEMBER [Measures].[High Volume Products Count] AS 
 Count(Filter([Product].[Product].[Product], [Measures].[Internet Order Quantity] > 5))
SELECT [Customer].[Customer Geography].[Country] ON 0
, [Date ].[Calendar].[Date ].MEMBERS ON 1
FROM [Adventure Works]
WHERE [Measures].[High Volume Products Count]

If we execute this query in MDX Studio, we will get the following stats:

Time             : 30 sec 781 ms
Calc covers      : 4
Cells calculated : 4217436
Sonar subcubes   : 2
SE queries       : 1
Cache hits       : 1
Cache misses     : 1
Cache inserts    : 1
Cache lookups    : 2
Memory Usage KB  : 4160

The best hint here that this query indeed executed in the cell-by-cell mode (beyong the slow execution time) is value of "Cells calculated" perfmon counter. Now, the way most people approach optimization for such MDX is trying to reduce the number of cells to iterate. One way to do it is to eliminate manually all the empty cells from Filter. I.e., if value of [Internet Order Quantity] is NULL for certain product, it is definitely less than 5. With this in mind, one possible rewrite for the query would be

WITH MEMBER [Measures].[High Volume Products Count] AS 
 Count(Filter(
  Exists([Product].[Product].[Product],,"Internet Sales")
  ,[Measures].[Internet Order Quantity] > 5))
SELECT [Customer].[Customer Geography].[Country] ON 0
, [Date ].[Calendar].[Date ].MEMBERS ON 1
FROM [Adventure Works]
WHERE [Measures].[High Volume Products Count]

Here Exists inside Filter eliminates the empty space. Let's run this again in MDX Studio and look at the new stats

Time             : 12 sec 46 ms
Calc covers      : 954
Cells calculated : 50036
Sonar subcubes   : 7900
SE queries       : 7899
Cache hits       : 7899
Cache misses     : 1
Cache inserts    : 1
Cache lookups    : 7900
Memory Usage KB  : 43936

The time indeed decreased, from 30 seconds to 12 seconds. This is good, but something doesn't add up. The number of cells went down from 4 million to only 50 thousands, i.e. about 85 times, yet the execution time decreased by only 2 times. The explanation is that putting Exists (or NonEmpty) inside MDX calculation is usually a bad idea - since now for every cell, there is an SE query being sent (it can be seen through "Query Subcube" event in trace too). In our case there were 7900 such queries sent. Because the set inside Exists was always the same and the context was fixed, only 1 such query actually had to go to disk, and other 7899 hit the cache, yet the overhead of issuing SE query is non-trivial, even when it is answered from the cache.

Conclusion is that even though we get some performance gain from this approach, it doesn't play well with the rest of the system, and only drives us further from the goal of switching to the superior block computation mode. We need to rewrite the calculation in such a way that we eliminate Filter. Fortunately, it is possible to do. Let's recall that Count function returns number of tuples in the set, and Filter return set of tuples which satisfy certain condition. I.e. we are counting how many tuples satisfy a condition. If we convert count to sum, and sum up 1's every time when the condition is met and 0's every time when the condition is not met, we will get the same result. I.e.

Count(Filter(set, condition)) = Sum(set, Iif(condition, 1, 0))

Using this formula we can rewrite our calculation as following:

WITH MEMBER [Measures].[High Volume Products Count] AS 
 Sum(
   [Product].[Product].[Product], 
   Iif([Measures].[Internet Order Quantity] > 5,1,0))
SELECT [Customer].[Customer Geography].[Country] ON 0
, [Date ].[Calendar].[Date ].MEMBERS ON 1
FROM [Adventure Works]
WHERE [Measures].[High Volume Products Count]

Executing this query yields the following stats:

Time             : 6 sec 375 ms
Calc covers      : 4
Cells calculated : 6948
Sonar subcubes   : 1
SE queries       : 1
Cache hits       : 1
Cache misses     : 1
Cache inserts    : 1
Cache lookups    : 2
Memory Usage KB  : 0

This is definitely much better. The time is down to 6 seconds, and we can tell that within every cell the Sum works very efficiently, because we only see 6948 cell calculated (this is the exact number of cell in the resulting cellset). Yet, we are not in true block computation mode yet. We are still running Sum for every cell, instead of computing the entire query in one operation. What prevents us from doing it now ? Now the problem is with Iif function inside the Sum. I have written about Iif function and its interaction with block mode in the past. If we reread that article, we will see that we are in the scenario where condition inside Iif looks at the cell values and not at the attribute coordinates, and the only thing we can do here is to have one of the branches to return NULL. And it probably makes sense to define our calculated member to return NULL instead of 0 when there are no products which satisfy our condition. After this rewrite we get

WITH MEMBER [Measures].[High Volume Products Count] AS 
 Sum(
   [Product].[Product].[Product], 
   Iif([Measures].[Internet Order Quantity] > 5,1,NULL))
SELECT [Customer].[Customer Geography].[Country] ON 0
, [Date ].[Calendar].[Date ].MEMBERS ON 1
FROM [Adventure Works]
WHERE [Measures].[High Volume Products Count]

This gets us down to 4 seconds, which is yet another improvement, but still doesn't yet reach the best execution plan. Now it is time to use the performance optimization hints. Looking at the expression Iif([Measures].[Internet Order Quantity] > 5,1,NULL) we observe that it is guaranteed to be NULL when [Measures].[Internet Order Quantity] is NULL. Therefore we can define the MDX script flavor of NON_EMPTY_BEHAVIOR for it. Since NEB cannot be defined on subexpressions, we will separate it into special calculated measure. Let's write the following fragment inside MDX Script:

CREATE HIDDEN Summator;
[Measures].[Summator] = Iif([Measures].[Internet Order Quantity] > 5,1,NULL);
NON_EMPTY_BEHAVIOR([Measures].[Summator]) = [Measures].[Internet Order Quantity];

And then use it inside our calculation:

WITH 
MEMBER [Measures].[High Volume Products Count] AS 
 Sum([Product].[Product].[Product], [Measures].[Summator])
SELECT [Customer].[Customer Geography].[Country] ON 0
, [Date ].[Calendar].[Date ].MEMBERS ON 1
FROM [Adventure Works]
WHERE [Measures].[High Volume Products Count]

Finally, when we execute this query, it finishes in about 0.2 second (218 milliseconds). This is the performance we expect to observe from the block computation mode. From 32 seconds to 0.2 seconds - improvement of over 160 times !

Now the even better news are, that starting with Katmai November CTP5 (which was released couple of days ago, so I finally can publicly speak about it), the trick with explicitly defining NON_EMPTY_BEHAVIOR is not required, the engine recognizes it itself. Therefore, even our previous query (the one which took 4 seconds in AS2005) returns in 0.2 seconds in AS2008. (This improvement in query optimizer in Katmai is just a tiny little tip of the iceberg around improvements in block computation query plans, and I intend to cover this subject in more depth in the upcoming blogs, if I will have enough time for that).

How to detect subselect inside MDX calculations (aka Multiselect in Excel 2007)

The subject of multiselect friendly calculations is a popular one in forums. I have written about it before here, here and here. In February 2008 I will write a blog which will give a definite answer on how AS2008 will deal with it (which is follow up on this post). But in the meantime, questions about AS2005 keep coming. By now, most people realize that it is possible to detect presence of set in WHERE clause by using EXISTING operator, which takes care of multiselect queries generated by Excel 2003 and other tools. However, Excel 2007 for multiselect uses subselects which are stealthier, their presence cannot be detected neither by EXISTING nor by any other MDX function. The standard answer for detecting subselects always was to use query scoped named sets, because they are subject of implicit autoexist with subselects. However, this approach isn't applicable to Excel 2007, since it is up to Excel 2007 to generate MDX queries, and the user can only control the expression for the calculation inside MDX Script.

So the question that gets asked is "Is it possible in AS2005 to detect and obtain subselect restriction from the MDX calculation?". Up until now the answer to this question was "No". I have thought about it, and I found a way how to make the answer to this question "Yes". I must warn, that the solution is neither elegant nor efficient, and probably is mostly of the theoretical interest. I wouldn't recommend using it in the real implementation. Still, it is interesting one in my opinion.

Let's start with the assumption, that the lowest attribute on which multiselect is possible has only few members. More precisely it should have 63 or less members. We will see how we can lift this limitation later, but for now let's go with it. For our example, we will choose [Ship Date].[Calendar Year] attribute, which only has 4 members. For the calculation itself, let's pick example of calculating the maximum yearly sales. The classic formula for this is below:

Max(Descendants([Ship Date].[Calendar].CurrentMember, [Ship Date].[Calendar].[Calendar Year]), [Measures].[Internet Sales Amount])

Indeed, if we run the following query

WITH 
 MEMBER Measures.[Max Yearly Sales] AS Max(Descendants([Ship Date].[Calendar].CurrentMember, [Ship Date].[Calendar].[Calendar Year]), [Measures].[Internet Sales Amount])
SELECT
{Measures.[Max Yearly Sales]} ON 0
FROM (
 SELECT  { [Ship Date].[Calendar].[Calendar Year].&[2001], [Ship Date].[Calendar].[Calendar Year].&[2003] } ON 0
 FROM [Adventure Works])

When we run this query, the result is $10,158,562.38. Quick check query:

SELECT {[Measures].[Internet Sales Amount]} ON 0
, [Ship Date].[Calendar].[Calendar Year] ON 1
FROM [Adventure Works]

it shows that the number we got was actually for year 2004. But it wasn't even included by our subselect restriction which covered 2001 and 2003 only. So how does the solution works. First, we create another small table (or named query in DSV) which looks like following:

Year	Encoding
2001	1
2002	2
2003	4
2004	8

In general case we want to list every member in the lowest attribute by which the multiselect is expected, and assign values which are powers of 2. The idea here is that each member then gets assigned a bit in the 64-bit representation of the integer. Adding Encoding values for different members will be equivalent for OR'ing their representative bits.

Next we build a small measure group off this table, by linking Year column to the [Calendar Year] attribute in the [Ship Date] dimension, and Encoding is used as a measure with Aggregation Funciton Sum. But due to a special nature of Encoding values, the Aggregation Function here really behaves as OR.

This is the root of the idea. When there is a subselect restriction, all physical measures get automatic Visual Totals applied to them if the coordinate [Calendar Year] hasn't been overwritten by formula. Applying Visual Totals to the Encoding measure will mean that it will be equal to a number, which has bits set to 1 only for those members which participated in subselect ! Now all we need to do is to decode back from the bitmask to the MDX set. It may seem that the easiest way to do it is to run Filter over [Calendar Year] and do filtering by AND'ing bitmasks. However, this won't work, since using Filter over [Calendar Year] will cause [Calendar Year] attribute to be overwritten, and Visual Totals won't get applied - the trick won't work. Therefore, we need to do something a little more sophisticated - write a special purpose stored procedure which will behave exactly like Filter, only without coordinate overwrite. Such stored procedure is simple to write, below is the full source code for it:

        public Set DecodeSet(Set set, System.UInt64 Encoding)
        {
            SetBuilder sb = new SetBuilder();
            System.UInt64 bit = 1;
            foreach (Tuple t in set.Tuples)
            {
                if ((bit & Encoding) != 0)
                    sb.Add(t);

                bit *= 2;
            }

            return sb.ToSet();
        }

Now, the expression for the calculated member will look like

Max(ASSP.ASStoredProcs.Util.DecodeSet([Ship Date].[Calendar].[Calendar Year], [Measures].[Encoding]), [Measures].[Internet Sales Amount])

And we can verify that it works correctly by running the following query:

WITH 
 MEMBER Measures.[Max Yearly Sales] AS Max(ASSP.ASStoredProcs.Util.DecodeSet([Ship Date].[Calendar].[Calendar Year], [Measures].[Encoding]), [Measures].[Internet Sales Amount])
SELECT
{Measures.[Max Yearly Sales]} ON 0
FROM (
 SELECT  { [Ship Date].[Calendar].[Calendar Year].&[2001], [Ship Date].[Calendar].[Calendar Year].&[2003] } ON 0
 FROM [Adventure Works])

This is nice, now back to the case when we have more than 63 members for the attribute. For example, we want to go over days, not over years, and in Adventure Works we have 4 years worth of data, which translates to 1158 days. 1158 > 63. Well, the only solution in this case is to break the attribute into groups of 63 members and assign dedicated measure to each group. In our case this makes 19 new measures. It is not pretty, but at least it can be automated. The sproc will have to change to accept set as a second parameter, where the formula will pass MeasureGroupMeasures("ShipDateMultiselect"), and sproc will have to make nested loops in order to move to the right member for the right measure. The exact code is left as exercise to the reader, if the reader is still interested.

MDX Studio forum

Two dot releases of MDX Studio generated unusually high level of feedback both in email and in the comments in both mirrors of my blog (at sqljunkies and at sqlblog). In order to keep these and future discussions more structured and not to pollute my blog with lots of noise about every little feature and bug in MDX Studio, I would like to move these discussion to the proper forum. Vidas Matelis was kind enough to offer his site to host this forum. From now on, please post suggestions, bug reports and all other comments about MDX Studio in the MDX Studio forum at http://www.ssas-info.com/forum/MdxStudio. You don't have to register in order to post (although you may if you want to). I will monitor this forum closely, and will post all the future updates there. I will still announce major version releases of MDX Studio in this blog for those who don't want to suffer through the beta testing and want to get the finished and polished product.

MDX Studio v0.1.1 update

Yesterday announcement of MDX Studio has generated lots of feedback, and I would like to thank everybody for taking your time to try this early version. Below I address most of the comments I received:

1. The error that got reported most is "Category not found" during execution of MDX query. I believe this error is due to inability to query perfmon counters. I will appreciate if people who run into this problem will check whether they get AS perfmon counters in the perfmon application. Especially the ones under "MSAS 2005:MDX", "MSAS 2005:Cache" and "MSAS 2005:Memory" categories.

In the meantime I have implemented a workaround, if some of the counters are not found, the query will still execute. The updated version is v0.1.1 and can be downloaded from http://cid-74f04d1ea28ece4e.skydrive.live.com/browse.aspx/MDXStudio/v0.1.1

This should unblock MDX query execution piece. However, MDX query execution is the least interesting aspect of MDX Studio. There are dozens of tools which allow MDX query execution, and SSMS does this just fine. The real power of MDX Studio is under the Parse button and inside MDX expression debugger. Both these features are not affected by the perfmon issue, and I am very interested to get feedback on them.

2. Skydrive is not available in all countries, so MDX Studio cannot be downloaded. If your country is excluded from Skydrive beta, you can download MDX Studio from www.mosha.com/msolap/MDXStudio/v0.1.1/MDXStudio.exe

3. Color coding doesn't work. How did I get pretty colors in the screenshots ?

Yes, color coding is not implemented yet. I wrote the queries in SSMS and copy pasted them into MDX Studio. Since MDX Studio still lacks many features (like functional drag and drop, color coding, open/save), I find it easier to write queries in SSMS and paste them back.

4. Drag and drop from metadata tree drops object caption instead of object unique name.

Thanks - this will be fixed in the future release.

5. Perfmon tab to show which aggregates were hit.

Cannot get this info through perfmon, but can get it from elsewhere. This will probably take a while until it is implemented.

6. Problem on Vista with error: Access to the registry key 'Global' is denied.

It worked on at least one another Vista installation, so I am not sure what the problem here. MDX Studio doesn't try to access registry directly

7. Not everything works with AS2000.

I need to do more testing with AS2000.

Thanks again.

Announcing MDX Studio CTP1 (v0.1 Alpha)

Redmond, WA September 18, 2007 - Mosha Pasumansky today announced the immediate availability of MDX Studio Community Technology Preview (CTP) 1, version 0.1 Alpha.

MDX Studio is tool which helps users of Microsoft Analysis Services to analyze complex MDX expressions, monitor performance characteristics of MDX queries and get insight into how MDX interacts with other UDM features such as attribute relationships. MDX Studio provides unique visualization representation of MDX expression and allows the user to interact with different stages of MDX execution. MDX Studio is aimed to be compatible with all the versions of Microsoft OLAP servers - OLAP Services 7.0, Analysis Services 2000, SQL Server Analysis Services 2005 and SQL Server Analysis Services 2008 (codename Katmai).

The licensing terms were not disclosed, however Mosha has explicitly noted, that everybody who was registered for the 'Deep Dive to MDX' preconference presentation at PASS 2007 summit will have lifetime eligibility for free copy for all future versions of MDX Studio.

The MDX Studio CTP1 v0.1 Alpha can be downloaded from http://cid-74f04d1ea28ece4e.skydrive.live.com/browse.aspx/MDXStudio/v0.1 (if this SkyDrive thing actually works). Mosha Pasumansky welcomes all the feedback, but especially critique and bug reports. The current 0.1 Alpha version is pretty much work in progress, with many features not yet implemented (for example query execution is synchronous and Cancel doesn't work, drag and drop is almost non-existing etc).

The brief explanation of how this thing works follows:

MDX Studio is very similar to SSMS, but with deeper focus on MDX. It includes connection string builder which allows user to specify different connection string properties categorized in groups - Security, MDX Semantics, Performance Hints etc.

Below is a screenshot of query execution in MDX Studio. Note, that on the left hand side, some of the useful Perfmon counters are automatically computed as deltas for just this query. Counters can be configured through Tools -> Options -> Perfmon menu.

Perhaps most important feature of MDX Studio is behind the "Parse" button. In SSMS, it simply validates that the MDX query is syntactically correct. MDX Studio doesn't support yet parsing of MDX SELECT queries or other MDX commands, but it supports parsing of MDX expressions. Below is sample screenshot of the parse tree for the expression of simple calculated member which computes Reseller Sales Amount for all products containing string "Front" in their names.

But what is more interesting, the user can now interact with this tree and see how each subexpression evaluates. Let's position the cursor on CurrentMember node under Descendants. We see in the Results pane, that this current member evaluates to Components member. Note the 'Current Context' window on the right hand side, where user can change the coordinates of the current context, thus affecting results of the MDX expression evaluation.

Next, let's move cursor on Descendants, and see what it evaluates to. The Results pane now displays the set which is result of applying Descendants to the Components member.

Let's make things even more interesting. We are running Filter over this Descendants set which checks boolean expression. Let's see what it evaluates to. MDX Studio understands that the boolean condition needs to be evaluated in the context of iteration over Descendants set, therefore the result looks like below:

Let's dig in deeper. Why some of the comparisons are true and other false. Now we can look inside boolean expression, and position cursor over VBA!Instr function. Again, MDX Studio is intelligent enough to show how Instr evaluates over the set inside Filter:

We could go on and on with this. Once we computed Filter, we now can see how Sum was calculated:

Hopefully this gives an idea what MDX Expression debugger is capable of doing.

Lastly, there is a handy "Coordinates" button, which allows the user to see effect of applying strong hierarchies. Here is a screenshot which shows how August 2003 decodes other attributes:

Of course, it is possible to use any MDX expression here as well. So if we will add FirstChild, we will get to the August 1st 2003:

There are plenty of plans for new features (like support for MDX Scripts) and finishing existing features, but at this point I believe there is enough functionality implemented to start sharing it with the community and gathering the feedback. Please let me know what do you think, what do you like and again, especially what you don't like so I can fix it.

PASS Presentation canceled :(

I am very upset about it, but my tomorrow's PASS preconference presentation isn't going to happen. My flight to Denver today was canceled, after having spent few hours in Seattle airport I realized that there was absolutely no way for me to get to Denver before tomorrow morning. I went through all airlines, asking for any flight, any connection, and every other flight was full and sold out. I apologize to everybody who registered for this presentation. I hope PASS will be able to accommodate you for another session. I am very upset, for I have spent lots of time during last month preparing for this presentation. I believe that I gathered plenty of unique material not available anywhere else, knowledge that would have benefited MDX practitioners. I even developed a new tool (code name 'MDX Studio'), especially for this presentation, which features MDX expression debugger, MDX query execution performance monitor and attribute overwrite viewer - this was supposed to be a surprise announcement during my presentation. If only PASS was in Seattle, just like last year, and year before that.

Anyway, I apologize again, and once I calmed down, I will start thinking about other ways to share the content of the presentation. It will take me months to write all of it down, so I hope another opportunity for speaking will come soon again, this time in Seattle or somewhere close, like Vancouver or Portland - where I could just drive...

Outline of the 'Deep dive to MDX' PASS presentation

My PASS presentation is quickly approaching. Currently there are about twice as many people registered for it than I thought there will be. I am getting a little nervous about it. After all, 7 hours is a lot of time, and I am not sure what people expect from this session. Since there is still time to make changes, I decided to publish the tentative outline of the presentation here, in the hope to get more feedback. The outline is not final, the order of subjects will change for sure, but this should give a good idea what to expect from the session. If this isn't what you had in mind - there is still time to cancel and go for another session. (For example, I think about half of the presentation is based on former blog entries (although somewhat expanded), so if you read all my blogs, then you already know at least half the content).

Introduction

Architecture

SE and FE

Subcube

Granularity
Single grain/mixed grain
Slice
DataIDs
OR slice
Slice below granularity
Arbitrary shape

Architecture
Sonar
- Cache Ratio

SE Measure group Cache

Cache registry
Aggregate & Filter from cache
Prefetching
Cache sharing
- Slice below granularity
- Visual totals
- Subselects
- Arbitrary shapes
Spliting measure group
- MDX stiching
CREATE CACHE

SE query plan

Aggregations
Scan vs. indexes
Bitmap indexes
Segment compression
Arbitrary shapes
- Normalize mixed grain with Descendants
Partitions
- Slice
- Autoslice
- OR slice

SE dimensions

Natural vs. unnatural hierarchies
Dimension cache

Architecture

FE Cache

Global scope
- Security
  - Dynamic
- Username
- Dynamic functions
- Sprocs
  - Non deterministic functions
- Outline calcs
Session/query scope
- Visual totals/subselects
  - Hidden members
- Calculations
Registry vs flat MCache
Subselect visual totals & overwrite history

MDX Caching techniques

Value caching
- Subexpressions
- KPI/cell security/calc properties
Member caching
- Today
- Tuple caching
- Dynamic
Set caching
- Static/dynamic/alias

FE execution

Sonar
Calc covers
- Calculate

FE execution plans

Cell by cell
Bulk evaluation (aka block computation mode)
- Space transformations
- Space partitioning

Forcing block mode

Optimized MDX functions
- Static literal references
Dense vs. sparse subcubes
- Multiplication
- ValidMeasure vs. IgnoreUnrelatedDimensions
- Writing over NULLs
Conditional execution
- IIF vs. IF vs. SCOPE
Imprecise Sonar subcubes
- Cache Ratio
- Hidden members
NON_EMPTY_BEHAVIOR
“Cache Policy”

Aggregate function

Implicit aggregations
- Calcs on leaves
- No cascading
Single tuple set
Arbitrary shapes
- No NonEmpty
Space partitioning

Scenarios

Ratios
Comparison to previous period
Running sum
Inventory
Moving average

Non Empty

Sonar
SE data
Calculated members
Calculated measures
- NON_EMPTY_BEHAVIOR
Calculations

Exists and Autoexists

Autoexist
- cell
Single dimension
Multi-dimension
- Position in crossjoin
Arbitrary shapes

MDX

Sequence

Bootstrapping
MDX Script
- Static vs. Dynamic statements
- Calculation properties
Personalization extensions

Sets

Iterator pipelining
Containers
Rankers
Enumerated set and Materialization
Crossjoin
- cannot operate on a set with more than 4,294,967,296 tuples
Not materializable sets

Strong hierarchies

Attribute relationships
Diamond shape
Decoding attributes
Cube space
Non-idempotence of CurrentMember

MDX in UDM

Existing vs. Descendants
Strong hierarchies vs. User hierarchies

Calculation precedence

History
SOLVE_ORDER
Pass
Implicit aggregations
MDX Script order
- CREATE MEMBER vs. CREATE
Outline calcs
Aggregate over calc measures
WHERE
Visual totals
SCOPE_ISOLATION

Calculated members

Non-aggregatable
- Multiselect on assignment
Inside subcubes
- Excel 2007

WHERE vs. subselects

What WHERE does
What subselect does
Implicit Exists
Coordinate overwrites
- Overwrite history

Multiselect

WHERE
- Existing
Classic
Subselects

Semiadditive measures

LastNonEmpty
- RunningSum + LastChild
- Manual with member caching
AverageOfChildren

Security

Dimension security
- Orthogonal design
Cell security
Dynamic security

Dimension query

How to query dimension
- Dimension cube
Exists
Filter
- Enumeration
- NonEmpty
- Extract
- Non SE materializable
Search
Internal VBA
LIKE

Stored procedures

Best practices
ASSP project

Moving averages in MDX

Moving averages is an important statistical technique for analyzing time series data. It is often used to do forecasting or trend estimations, and it is especially popular for analyzing financial data. There are many different types of moving averages, from simple to weighted to exponential etc. "Moving average" article in Wikipedia is a good starting point to learn about them. In this article we will discuss the implementation of moving averages in MDX.

Simple Moving Average

The classic way to compute moving average is explained in my "Fast Track to MDX" book, chapter 8. Translated to Adventure Works example, the moving average over last 12 months for the [Internet Sales Amount] can be written as following:

Avg(
  [Ship Date].[Month Name].CurrentMember.Lag(11):[Ship Date].[Month Name],
  [Measures].[Internet Sales Amount]
)

Here we use Lag function to go 11 months back, then put a range from that 11 months ago month to the current one, and compute average over it. To get a sense how moving average behaves when we look at last 12, 6 or 3 months, we can run the following query:

WITH 
 MEMBER [Measures].[SMA12] AS Avg(
  [Ship Date].[Month Name].CurrentMember.Lag(11):[Ship Date].[Month Name],
  [Measures].[Internet Sales Amount]
 )
 MEMBER [Measures].[SMA6] AS Avg(
  [Ship Date].[Month Name].CurrentMember.Lag(5):[Ship Date].[Month Name],
  [Measures].[Internet Sales Amount]
 )
 MEMBER [Measures].[SMA3] AS Avg(
  [Ship Date].[Month Name].CurrentMember.Lag(2):[Ship Date].[Month Name],
  [Measures].[Internet Sales Amount]
 )
SELECT 
 {[Measures].[Internet Sales Amount], [Measures].[SMA12], [Measures].[SMA6], [Measures].[SMA3]} ON 0
 ,[Ship Date].[Calendar].[Month] ON 1
FROM [Adventure Works]

And it's result can be seen in the graph below :

The above formulas for moving average are straightforward. In fact, the BIDS in the Calculation tab, at the left side of the screen has a Templates tab, which contains templates for common calculations. Moving average is among them, and the MDX code in the template is the same as above:

//
/*Returns the average value of a member over a specified time interval.*/
CREATE MEMBER CURRENTCUBE.[MEASURES].[Moving Average]
AS Avg
(
   [>].[>].CurrentMember.Lag(>) : 
   [>].[>].CurrentMember, 
   [Measures].[>]
) 

// This calculation returns the average value of a member over the specified time interval.
            ,
FORMAT_STRING = "Standard";

The use of MDX function Avg here is justified, because we are talking about moving average. However, we should note how the Avg function treats cells with NULL value. For the compatibility with Excel, when the cell contains no value, i.e. it contains NULL, Avg treats it as if the cell didn't exist. I.e. it doesn't count this cell in the total number of cells to be used in the denominator. If such behavior is not desirable, instead of Avg(set, exp) one could write Sum(set, exp)/Count(set). Our example then will become

Sum(
  [Ship Date].[Month Name].CurrentMember.Lag(11):[Ship Date].[Month Name],
  [Measures].[Internet Sales Amount]
) 
/
Count([Ship Date].[Month Name].CurrentMember.Lag(11):[Ship Date].[Month Name])

Or, if it is OK to ignore the values near the beginning of the Time dimension, then instead of Count() we can write the constant 12 in the denominator

Sum(
  [Ship Date].[Month Name].CurrentMember.Lag(11):[Ship Date].[Month Name],
  [Measures].[Internet Sales Amount]
) / 12

If the difference in semantics w.r.t. NULL treatment between Avg and Sum is not important (for example, it is known that NULLs won't happen anyway), then Sum is always preferable in Analysis Services 2005, since it can have better performance.

Speaking of performance, let's see how well this expression behaves. Teo Lachev have provided the query below as an Adventure Works compatible example showing problematic performance:

WITH MEMBER [Measures].[SMA12] AS
 Sum
 (
        [Ship Date].[Calendar].CurrentMember.Lag(11):[Ship Date].[Calendar],
        [Measures].[Internet Sales Amount]
 )
  /
 Count([Ship Date].[Calendar].CurrentMember.Lag(11):[Ship Date].[Calendar])
SELECT [Measures].[SMA12] ON 0,
[Customer].[Customer].[Customer].Members on 1
FROM [Adventure Works]
WHERE ([Ship Date].[Calendar].[Month].&[2004]&[8])

This query executes for about 13 seconds on my laptop. This is definitely too long - there are about only 18,484 customers in Adventure Works, and since we are getting data for 12 months - it would still be about 17,000 cells per second, which is suspiciously low. What is even more interesting, is if we compare this to the calculation for running sum from my blog entry, we will get the following queries to compare:

WITH MEMBER [Measures].[Sum 12] AS
Sum
(
        [Ship Date].[Calendar].CurrentMember.Lag(11):[Ship Date].[Calendar],
        [Measures].[Internet Sales Amount]
)
SELECT [Measures].[Sum 12] ON 0,
[Customer].[Customer].[Customer].Members on 1
FROM [Adventure Works]
WHERE ([Ship Date].[Calendar].[Month].&[2004]&[8])

WITH MEMBER [Measures].[RSum] AS
Sum
(
        [Ship Date].[Calendar].[Month].Item(0):[Ship Date].[Calendar],
        [Measures].[Internet Sales Amount]
)
SELECT [Measures].[RSum] ON 0,
[Customer].[Customer].[Customer].Members on 1
FROM [Adventure Works]
WHERE ([Ship Date].[Calendar].[Month].&[2004]&[8])

It is immediately clear from here that the only difference between the two queries is .Item(0) instead of Lag(11), i.e. running sum query sums up all the months from the beginning of the time, while the first query only looks at most recent 12 months. The common sense would tell us that the first query should execute faster, because it looks at less data (12 months vs 38 months). Yet, the first query takes 13 seconds, and the second one takes only 1.3 seconds - completely against the common sense.

To understand what's going on here, let's take a look at the performance counters. The interesting counter here to watch is MSAS 2005: MDX\Total calculation covers. When we execute the first query, it goes to 18487. But for the second query it stays low at 4. The high number of calculation covers is a clear indication that something went wrong with the execution plan. As we know, AS2005 query optimizer has two major modes in which it evaluates subspaces - the one I used to call "bulk evaluation mode" (it Katmai marketing materials this mode received an official name - "block computation") and cell by cell mode. (If you are interested in detailed explanations on these modes and other internals of the execution plans it might be worthwhile attending my preconference session "Deep Dive into MDX" on PASS 2007 summit in two weeks). But even in the cell by cell mode, the number of calculation covers should have been smaller. Number 18487 is suspiciously close to the number 18484, which is number of customers, which leads us to assumption that there was calculation cover created for every customer. Running query trace with "Query Subcube Verbose" enabled confirms this assumption - we see SE subcube query generated for every single customer. The conclusion is that use aggregation function such as Aggregate or Sum over a range set which uses Lag function produces horrible execution plan. Lag by itself is not a bad function, but in this combination, AS2005 misses good query plan. This particular scenario, as well as many others is fixed in Katmai, but what can we do in the meantime ? We could work around use of Lag function and use another MDX function which will position us on the desired member. The most obvious function to do that is ParallelPeriod.

The query rewritten to use ParallelPeriod will look the following:

WITH MEMBER [Measures].[SMA12] AS
 Sum
 (
        ParallelPeriod([Ship Date].[Calendar].[Month],11):[Ship Date].[Calendar],
        [Measures].[Internet Sales Amount]
 )
 /
 Count(ParallelPeriod([Ship Date].[Calendar].[Month],11):[Ship Date].[Calendar])
SELECT [Measures].[SMA12] ON 0
,[Customer].[Customer].[Customer].Members on 1
FROM [Adventure Works]
WHERE ([Ship Date].[Calendar].[Month].&[2004]&[8])

And it executes in about 1 second.

Weighted Moving Average

Weighted moving average (WMA) is moving average where the values for the previous time periods are multiplied by weight before being summed up. Usually, the more recent time periods get bigger weight. One very common distribution of weights is when they decrease arithmetically, i.e. WMA is computed by the following formula:

The denominator is a simple arithmetic progression which can be calculated using the following formula:

How can we implement this formula in MDX ? There are several possible solutions. The most straightforward one is during iteration over the set to check the position inside the set and multiply by the appropriate weight. AS2005 added a new MDX function to get the index of tuple during iteration over the set - CurrentOrdinal. For the 6 month WMA the MDX will look like following:

WITH MEMBER Measures.WMA6 AS
  Sum(
    ([Ship Date].[Calendar].Lag(6):[Ship Date].[Calendar].PrevMember) as Iter,
    Iter.CurrentOrdinal * [Measures].[Internet Sales Amount]
  ) / (6*7/2)
  , FORMAT_STRING = 'Currency'
SELECT {[Measures].[Internet Sales Amount], Measures.WMA6} ON 0
, [Ship Date].[Calendar].[Month].MEMBERS on 1
FROM [Adventure Works]

Here n=6, therefore n*(n+1)/2 = 21. However, this gives inaccurate results at the beginning of Time, for the first 5 months, since the set actually contains less then 6 members in it. If this is important, the formula can be rewritten to be more precise:

WITH 
 MEMBER Measures.WMA6 AS
  Sum(
    ([Ship Date].[Calendar].Lag(6):[Ship Date].[Calendar].PrevMember) as Iter,
    Iter.CurrentOrdinal * [Measures].[Internet Sales Amount]
  ) 
  /
  (  Count([Ship Date].[Calendar].Lag(6):[Ship Date].[Calendar].PrevMember)
   *(Count([Ship Date].[Calendar].Lag(6):[Ship Date].[Calendar].PrevMember)+1)
   /2
  )
  , FORMAT_STRING = 'Currency'
SELECT {[Measures].[Internet Sales Amount], Measures.WMA6} ON 0
, [Ship Date].[Calendar].[Month].MEMBERS on 1
FROM [Adventure Works]

We can improve this a little by explicitly caching the common subexpression so it will be calculated only once (more on caching techniques during "Deep dive to MDX" PASS preconference session):

WITH 
 MEMBER Measures.IterCount AS Count([Ship Date].[Calendar].Lag(6):[Ship Date].[Calendar].PrevMember), VISIBLE = false
 MEMBER Measures.WMA6 AS
  Sum(
    ([Ship Date].[Calendar].Lag(6):[Ship Date].[Calendar].PrevMember) as Iter,
    Iter.CurrentOrdinal * [Measures].[Internet Sales Amount]
  ) 
  /
  (IterCount*(IterCount+1)/2)
  , FORMAT_STRING = 'Currency'
SELECT {[Measures].[Internet Sales Amount], Measures.WMA6} ON 0
, [Ship Date].[Calendar].[Month].MEMBERS on 1
FROM [Adventure Works]

Now, let's play a closer attention to the results of this query. If we look at the numbers, we will notice, that they seem to be way too big. In fact they are exactly 10,000 times bigger than what they are supposed to be ! This is a known bug in AS2005 with respect to the mathematical operations with Currency data type. Until this bug is fixed, we will have to compensate for it by dividing the result by 10,000:

WITH MEMBER Measures.WMA6 AS
  Sum(
    ([Ship Date].[Calendar].Lag(6):[Ship Date].[Calendar].PrevMember) as Iter,
    Iter.CurrentOrdinal * [Measures].[Internet Sales Amount]
  ) / 21 / 10000
  , FORMAT_STRING = 'Currency'
SELECT {[Measures].[Internet Sales Amount], Measures.WMA6} ON 0
, [Ship Date].[Calendar].[Month].MEMBERS on 1
FROM [Adventure Works]

Exponential Moving Average

Exponential moving average (EMA) is a special case of WMA, where the weights decrease exponentially. There are few variations on the exact formula, below is one common definition:

The parameter "alpha" is called smoothing factor, and it relates to the number of periods to go back in the moving average computation as

Since the above definition is a recursive one, we can write this in MDX using recursion as well:

WITH 
 MEMBER Measures.Alpha AS 2/(6+1)
 MEMBER Measures.EMA6 AS 
  IIF( [Ship Date].[Calendar].CurrentMember IS [Ship Date].[Calendar].CurrentMember.Level.Members(0)
       ,[Measures].[Internet Sales Amount]
       ,[Measures].[Internet Sales Amount]*Alpha + (Measures.[EMA6], [Ship Date].[Calendar].PrevMember)*(1-Alpha) )
  , FORMAT_STRING = 'Currency'
SELECT {[Measures].[Internet Sales Amount], Measures.EMA6} ON 0
, [Ship Date].[Calendar].[Month].MEMBERS on 1
FROM [Adventure Works]

Recursive calculations are not efficient in AS2005, since query optimizer doesn't know how to optimize them. In order to improve performance, the EMA can be rewritten using non-recursive approach. The math works out to the following formula:

I leave the translation of this formula into MDX as an exercise for the reader.

The chart below shows comparison between SMA, WMA and EMA in a graphical way.

What is new in OLAP engine in Katmai CTP

After writing my previous blog post about dynamic named sets, I received feedback from multiple people saying the same thing: "How come nobody knew about this great new feature for the last 3 months". Now, one of the constant complains about AS2005 was inadequate documentation. Almost two years after AS2005 release, the documentation improved greatly with several BOL Web releases. In the upcoming AS2008 release, all the new features are properly documented in CTP BOL. However, the problem seems to be with the "What's new" documents. They are written at pretty high level and don't list every single small feature. And since there is no easy way to get a diff between BOLs for different releases. Additionally, after July CTP was released, the "What's new in June CTP" document disappeared, or at least I wasn't able to locate it, there is only "What's new in July CTP" document. Therefore there is simply no way for somebody to get list of all changes from AS2005 to Katmai. Therefore, I compiled the list of changes specifically in the OLAP engine only, i.e. I omit both Data Mining and Tools (UI/AMO). Of course, my list is likely to be incomplete, but until there is a real one - better something than nothing.

Analysis Services Personalization Extensions

This feature includes:

- Plug-in extensions to the AS stored procedures

- Events such as SessionOpened, SessionClosing, CubeOpened, CubeClosing

- Additions to the server ADOMD.NET object model to enable altering of session state (such as adding calculated members, named sets, KPIs etc)

BOL: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10md_3devsamp/html/a3e9bc0d-d56d-42a1-9c09-aa93e4f49bb2.htm

CodePlex: http://www.codeplex.com/MSFTASProdSamples/Wiki/View.aspx?title=SS2008%21Readme%20for%20Analysis%20Services%20Personalization%20Extensions%20Sample&referringTitle=Home

MDX: Dynamic sets

BOL for CREATE SET: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10md_6mdxassl/html/eff51eeb-5e7e-4706-b861-c57b6f3f89f0.htm

BOL for MDSCHEMA_SETS: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10md_9trfmisc/html/abb00dc0-2b83-48d6-b2ba-6615c1488d06.htm

Mosha's blog: http://sqlblog.com/blogs/mosha/archive/2007/08/25/mdx-in-katmai-dynamic-named-sets.aspx

MDX: CREATE/DROP KPIs

BOL for CREATE KPI: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10md_6mdxassl/html/87618fef-95e5-4dd0-a650-aeb60ccbddcb.htm

BOL for DROP KPI: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10md_6mdxassl/html/d19c6809-b8a6-459d-8554-b41854f7cc45.htm

MDX: UPDATE MEMBER statement

This statement gives ability to modify calculated members on the fly without changing their solve order/pass/calculation precedence. It enables some interesting scenarios.

BOL: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10md_6mdxassl/html/07ab708d-d165-4fb1-a9f9-fb8197ff0dab.htm

MDX: CAPTION, DISPLAY_FOLDER, ASSOCIATED_MEASURE_GROUP calculation properties

Previously it was possible to specify these only through AMO or through XMLA ASSL. Now it is possible to use these inside CREATE MEMBER statement.

BOL: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10md_6mdxassl/html/49379217-be2c-4139-a206-1168078b9b76.htm

Backup changes

In Katmai Backup changed dramatically to feature new storage implementation, improved performance, better scalability and removing limitations around the size of the database and individual files inside database.

BOL: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10md_0evalplan/html/aa69c299-b8f4-4969-86d8-b3292fe13f08.htm

Vidas's blog: http://www.ssas-info.com/VidasMatelisBlog/28_ssas-2008-katmai-upcoming-changes-in-the-next-ctp

MDX: CREATE CACHE statement

Technically this is not a new thing in Katmai, because support for CREATE CACHE/WITH CACHE was introduced in SP2 (previously even though the syntax was accepted, these commands did nothing). However, when I looked at the list of changes in AS2005 SP2, I noticed that this feature was missing there.

SQLCAT blog: http://blogs.msdn.com/sqlcat/archive/2007/04/26/how-to-warm-up-the-analysis-services-data-cache-using-create-cache-statement.aspx

P.S. I have a feeling that I forgot something, if I will remember, I will update this entry.

MDX in Katmai: Dynamic named sets

The September CTP of Katmai (speculated to be released during PASS) is expected to be the first Katmai CTP to contain significant improvements in the OLAP engine. The most important one (which is, in my opinion, is also the most important AS Katmai feature) is changes to the query optimizer, which are nicknamed "Block computation mode" in the whitepapers and webcasts. I can't wait for it to be released to start blogging about new execution plans. But while we all wait for this CTP, perhaps it is a good time to cover other changes which were already released in the previous CTPs. June CTP featured four relatively small additions to MDX, and I will cover today the most interesting one - dynamic named sets. (The other three are - CREATE KPI statement, ability to specify additional properties such as display folder, associated measure group etc inside CREATE MEMBER statement, and ability to update calculated member "on the fly").

Semantic of dynamic named sets

Named sets have been part of MDX from the very first version. Named sets can be defined for the query (using the WITH SET clause), for the session (using CREATE SESSION SET in the session) or globally for the entire cube (using CREATE SET statement inside MDX Script of the cube). Named sets were always static, in a sense that they were calculated only once, in the context where they were defined. It wasn't a problem for query named sets, since their lifetime was lifetime of the query anyway, and it also usually wasn't a problem for session sets, since they were typically created by the client application, for example OWC creates session named sets to be used as axes in the future queries. However, the named sets defined in the cubes had a problem. The main reason to define named sets in the cube was to expose them to the end users. Most modern client tools such as Excel 2007 have UI to let the user use the cube named sets in the query. However, since these sets are computed only once, when the MDX Script is evaluated, their applicability was severely limited. Let's take an example to illustrate why. Suppose we want to find all the products which have negative gross profit margin. Normally, in MDX we could write the following statement:

SELECT 
 [Measures].[Gross Profit Margin] ON 0 
 , NON EMPTY [Product].[Subcategory].[Subcategory] HAVING [Measures].[Gross Profit Margin] < 0 ON 1 
FROM [Adventure Works]

This brings back 3 products - Caps, Jerseys and Touring Frames. Now let's slice by year 2004 and see how profit margins look there:

SELECT 
 [Measures].[Gross Profit Margin] ON 0
 , NON EMPTY [Product].[Subcategory].[Subcategory] HAVING [Measures].[Gross Profit Margin] < 0
ON 1 
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2004]

The new result is only Jerseys and Road Frames. Apparently, Caps and Touring Frames made recovery in 2004.

However, the end users are not expected to master writing MDX queries, even the simple ones above. The end users want to work in UI environment such as Excel. Therefore the cube designer can put the named set defining products with negative profit margin inside the MDX Script of the cube:

CREATE SET [Adventure Works].NegativeMarginProducts AS 
  Filter([Product].[Subcategory].[Subcategory], [Measures].[Gross Profit Margin] < 0)

Now, this set will appear in Excel 2007 under the Product dimension, and can be dropped into rows with single mouse click. Excel will generate the query like the following:

SELECT [Measures].[Gross Profit Margin] ON 0
, NON EMPTY NegativeMarginProducts ON 1 
FROM [Adventure Works]

This will work fine, the results are good. However, when the user slices by year 2004, the following query is generated

SELECT [Measures].[Gross Profit Margin] ON 0
, NON EMPTY NegativeMarginProducts ON 1 
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2004]

And the result is no longer good. It still shows Caps, Jerseys and Touring Frames, even though Caps and Touring Frames have positive margins in 2004. The reason is simple - the named set NegativeMarginProducts was evaluated only once and in the context of [Date].[All Periods] - therefore the content of this named set remains the same regardless of how the current query is sliced.

And this is exactly the problem that Katmai's dynamic sets are designed to solve. Dynamic sets are not calculated once. They are calculated before each query, and, very important, in the context of that's query WHERE clause and subselects. In other words, using the MDX notation, we can say that using the following statement in the MDX Script

CREATE DYNAMIC SET .DynSet AS

and issuing queries

SELECT Function1(DynSet) FROM 
SELECT Function2(DynSet) FROM

is functionally equivalent to issuing the following queries

WITH SET DynSet SELECT Functions1(DynSet) FROM 
WITH SET DynSet SELECT Functions2(DynSet) FROM

However, dynamic sets are smarter than regular query named sets. Dynamic sets won't be evaluated at all if the query doesn't reference them directly or indirectly. Therefore, the performance of queries which don't need dynamic sets won't be compromised.

So in our scenario, we can write the following in the MDX Script.

CREATE DYNAMIC SET [Adventure Works].NegativeMarginProducts AS 
  Filter([Product].[Subcategory].[Subcategory], [Measures].[Gross Profit Margin] < 0)

And all the queries will now work as expected !

Using dynamic sets for performance optimizations

While the dynamic sets were designed to address the functionality limitations of the static sets, they also can be used as very efficient performance optimization technique. Let's use as an example the problem of doing efficient ranking by value in MDX. I have covered this subject in a dedicated blog post: http://sqljunkies.com/WebLog/mosha/archive/2006/03/14/mdx_ranking.aspx. The conclusion was, that the most efficient way was to define query named set and then use RANK over it. Below is the best MDX query:

WITH 
SET OrderedEmployees AS ORDER([Employee].[Employee].[Employee].members, [Measures].[Reseller Sales Amount], BDESC)
MEMBER [Measures].[Employee Rank] AS RANK([Employee].[Employee].CurrentMember, OrderedEmployees)
SELECT [Measures].[Employee Rank] ON 0 
,[Employee].[Employee].[Employee].MEMBERS ON 1
from [Adventure Works]

However, this technique does not always works. It can only be used when the user can write his own MDX query. One of the comments (from Ed) wonders exactly about this:

"Hi, Defining the set first definitely speeds this up. However, we are finding that doesn't work when delivering results via Excel and using filters. Because of the way Excel represents filters in MDX, the filter context is not applied to the set and thus the rank is always based on the unfiltered set. Any ideas on how to speed up RANK in this context?"

Well, I didn't have a good answer for this question back then, but thanks to Katmai's dynamic sets, there is a good answer now. Remember, that from the functionality point of view, defining dynamic set is like having a query named set for every query. Therefore, we could simply define OrderedEmployees as dynamic set

CREATE DYNAMIC SET [Adventure Works].OrderedEmployees AS 
  ORDER([Employee].[Employee].[Employee].members, [Measures].[Reseller Sales Amount], BDESC)

And we can use it inside calculated members

WITH 
MEMBER [Measures].[Employee Rank] AS RANK([Employee].[Employee].CurrentMember, OrderedEmployees)
SELECT [Measures].[Employee Rank] ON 0 
,[Employee].[Employee].[Employee].MEMBERS ON 1
FROM [Adventure Works]

If there is a slice on a query now, it will be respected - note how the ranks change.

WITH 
MEMBER [Measures].[Employee Rank] AS RANK([Employee].[Employee].CurrentMember, OrderedEmployees)
SELECT [Measures].[Employee Rank] ON 0 
,[Employee].[Employee].[Employee].MEMBERS ON 1
FROM [Adventure Works]
WHERE [Ship Date].[Calendar].[Month].&[2002]&[1]

Using dynamic sets to detect subselects

I am a bit reluctant to write this section, mostly because the functionality of subselects may change in later Katmai CTPs. However, it didn't change yet in the current CTPs. If it will change, this section may become obsolete. But the current functionality is that unlike WHERE clause, subselects do not change the current coordinate. Therefore calculated members which rely on the current coordinate but not at the cell value in the coordinate, fail to work correctly in the presence of subselects. This problem is well known to the ReportBuilder users, since it uses subselects. Consequently, unlike WHERE clause, subselects do not affect the result of EXISTING operator. Therefore calculations designed to work correctly with multiselect by means of using EXISTING operator, do not work when multiselect is implemented through subselects. This is a problem for Excel 2007 users. (Now I have to make myself clear, that I don't consider the solution below a clean solution to multiselect. A while ago I said that I was looking into clean and elegant solution for multiselect, and I had in mind something completely different - but I still cannot really talk about it.). As an example, I will use the simple problem of counting days. Again this problem has much better solution, but for simplicity I will use the MDX approach.

The classic approach in MDX for counting days is the following expression:

Count(Descendants([Ship Date].[Calendar].CurrentMember,[Ship Date].[Calendar].[Date],SELF))

It is simple enough, and it has few problems - it doesn't work with subselects and it doesn't work with multiselect in WHERE. The multiselect in WHERE can be solved by using

Count(EXISTING [Ship Date].[Calendar].[Date])

But subselects still won't work. The following query returns 1158 instead of 31:

WITH MEMBER Measures.NumberOfDays AS Count(Descendants([Ship Date].[Calendar].CurrentMember,[Ship Date].[Calendar].[Date],SELF))
SELECT NumberOfDays ON 0
FROM 
(SELECT [Ship Date].[Calendar].[Month].&[2002]&[1]  ON 0
FROM
[Adventure Works])

The problem is that subselects are almost stealth. They cannot be detected with CurrentMember or EXISTING. However, they are not completely stealth. Subselects perform autoexist with query axes and query named sets. Remember how dynamic named sets are like adding query named set to every query. Therefore, we can define the following dynamic named set in the MDX Script:

CREATE DYNAMIC SET [Adventure Works].Days AS [Ship Date].[Calendar].[Date]

Now we are guarantee, that this set will be autoexisted with whatever subselects and WHERE we might have for the query. The following query returns correct result of 31:

WITH MEMBER Measures.NumberOfDays AS Count(Days)
SELECT NumberOfDays ON 0
FROM 
(SELECT [Ship Date].[Calendar].[Month].&[2002]&[1] ON 0
FROM
[Adventure Works])

And, of course, multiselect is no longer a problem even under subselects:

WITH MEMBER Measures.NumberOfDays AS Count(Days)
SELECT NumberOfDays ON 0
FROM 
(SELECT {[Ship Date].[Calendar].[Month].&[2002]&[1],[Ship Date].[Calendar].[Month].&[2002]&[2]} ON 0
FROM
[Adventure Works])

Conclusion

While they can be considered to be a small addition to MDX, dynamic named sets are very useful feature, which can be applied beyond the things it was originally designed for. It can also be used for improving performance and for solving some of the MDX semantic issues.

References

The official documentation on dynamic sets is part of CTP BOL under MDX Language Reference -> MDX Statement Reference -> MDX Data Definition Statements -> CREATE SET Statement (MDX). If you have it installed follow this path: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10md_6mdxassl/html/eff51eeb-5e7e-4706-b861-c57b6f3f89f0.htm

For convenience, I copied the content of BOL inline below:

Syntax


CREATE [SESSION] [ STATIC \| DYNAMIC ] [HIDDEN] SET CURRENTCUBE \| Cube_Name .Set_Name AS 'Set_Expression' [,Property_Name = Property_Value, ...n]

Arguments

Cube_Name

A valid string expression that provides the name of the cube.

Set_Name

A valid string expression that provides the name for the named set being created.

Set_Expression

A valid Multidimensional Expressions (MDX) expression that returns a set.

Property_Name

A valid string that provides the name of a set property.

Property_Value

A valid scalar expression that defines the set property's value.

Remarks

A named set is a set of dimension members (or an expression that defines a set) that you create to use again. For example, a named set makes it possible to define a set of dimension members that consists of the set of the top ten stores by sales. This set can be defined statically, or by means of a function like TopCount. This named set can then be used wherever the set of the top 10 stores is needed.

The CREATE SET statement creates a named set that remains available throughout the session, and therefore, can be used in multiple queries in a session. For more information, see Creating Session-Scoped Calculated Members (MDX).

You can also define a named set for use by a single query. To define such a set, you use the WITH clause in the SELECT statement. For more information about the WITH clause, see Creating Query-Scoped Named Sets (MDX).

The Set_Expression clause can contain any function that supports MDX syntax. Sets created with the CREATE SET statement that do not specify the SESSION clause have session scope. Use the WITH clause to create a set with query scope.

Specifying a cube other than the cube that is currently connected causes an error. Therefore, you should use CURRENTCUBE in place of a cube name to denote the current cube.

Scope

A user-defined set can occur within one of the scopes listed in the following table.

Query scope

The visibility and lifetime of the set is limited to the query. The set is defined in an individual query. Query scope overrides session scope. For more information, see Creating Query-Scoped Named Sets (MDX).

Session scope

The visibility and lifetime of the set is limited to the session in which it is created. (The lifetime is less than the session duration if a DROP SET statement is issued on the set.) The CREATE SET statement creates a set with session scope. Use the WITH clause to create a set with query scope.

Example

The following example creates a set called Core Products. The SELECT query then demonstrates calling the newly created set. The CREATE SET statement must be executed before the SELECT query can be executed - they cannot be executed in the same batch.

	Copy Code
CREATE SET [Adventure Works].[Core Products] AS '{[Product].[Category].[Bikes]}' SELECT [Core Products] ON 0 FROM [Adventure Works]

Set Evaluation

Set evaluation can be defined to occur differently; it can be defined to occur only once at set creation or can be defined to occur every time the set is used.

STATIC

Indicates that the set is evaluated only once at the time the CREATE SET statement is evaluated.

DYNAMIC

Indicates that the set is to be evaluated every time it is used in a query.

Set Visibility

The set can be either visible or not to other users who query the cube.

HIDDEN

Specifies that the set is not visible to users who query the cube.

Standard Properties

Each set has a set of default properties. When a client application is connected to Microsoft Analysis Services, the default properties are either supported, or available to be supported, as the administrator chooses.

Property identifier	Meaning
CAPTION	A string that the client application uses as the caption for the set.
DISPLAY_FOLDER	A string that identifies the path of the display folder that the client application uses to show the set. The folder level separator is defined by the client application. For the tools and clients supplied by Analysis Services, the backslash (\) is the level separator. To provide multiple display folders for a defined set, use a semicolon (;) to separate the folders.

AS Query Performance Hands-On-Lab during PASS

More information about upcoming SQL PASS summit. We just got confirmation that we will have 3 spots for the "SQL Server Analysis Services Query Performance Comparison Lab". Richard Tkachuk (of www.sqlserveranalysisservices.com fame) is hosting these labs, and I will join him during Wednesday session. The idea behind it is the following. We will have computers in the lab running the yet-not-released CTP build of Analysis Services 2008 side by side with Analysis Services 2005. People will bring their own cubes and their MDX queries and will run them side by side on both versions. More in Richard's own words below:

Richard Tkachuk, a Program Manager on the Analysis Services team, is hosting a lab to compare Analysis Services calculation performance on SQL Server 2005 vs SQL Server 2008. Calculation performance is one of the themes of Analysis Services in SQL Server 2008 and Richard will have an early build of the release in the lab. This is a great opportunity for you to bring your own cubes, queries and MDX questions into the lab and see how things compare. Join him in room ___ in one of the three scheduled sessions on Wednesday or Thursday. Prizes will be awarded for best performance improvement (and worst if we find any!), most complex calculation scenario and other categories.

PASS Presentation"Deep Dive into MDX"

SQL PASS organization holds the Summit 2007 this year in Denver, CO from Sep 17 to Sep 21. I always liked attending PASS summits, but this year I have been honored by the PASS committee who offered me to present at pre-conference seminar on the subject of my choosing. Pre-conference seminars don't have the glory of the conference presentation, because they are, well, pre-conference, so the attendance is light, as most people arrive only to the conference itself. However, the pre-conference seminar runs for whole 7 hours, as opposed to 1 or 1.5 hours of the normal PASS presentation. I always wanted to do a deep presentation on the MDX internals, but it is difficult to do in 1.5 hours only and in the full room with people. But it is so different in a setting where there are only few dedicated people, who are really into MDX (otherwise why would one choose to pay money to suffer through 7 hours of MDX talk!). I must admit that I am somewhat nervous about it, because I never presented 7 hours straight before, and it is a lot of work to make such a presentation. If you are one of the people registered for the PASS and you are interested in attending my session, I would really like to know what would you like to hear - there is still plenty of time to make changes to the presentation. And even if you won't attend the session, I would still like to hear the suggestions. You can either leave your suggestions in the blog comments or email them to me. Thanks in advance !

Here is the official blurb

This class is for seasoned MDX practitioners who have mastered “Fast Track to MDX” and who feel very comfortable with “MDX Solutions Second Edition”. We will talk about MDX semantics such as calculation precedence, multiselects, coordinate overwrites, visual totals flavors, arbitrary shaped subcubes and how all that can be leveraged to get MDX calculations to return desired results. We will also discuss important internals such as caching subsystems, query optimizer, execution plans and more. This isn’t a pure theoretical knowledge, we will see how it can be directly applied to optimize performance of MDX calculations. Differences between AS2005 and Katmai (AS2008) will be discussed where appropriate.

MDX is like chess - Solution to the puzzle

It is time to publish the solution to the MDX puzzle from the "MDX is like chess" blog. I wrote it before going to my summer vacation. Since then I received several answers. While nobody answered correctly all 3 questions, there are still winners. In a spooky coincidence, the 3 winners are from 3 countries that I visited during my vacation, and I was actually been able to meet with 2 of them ! And the winners are

1. Oz Shal-Bar. Oz is the Panorama employee whose blog entry triggered my blog replay and the puzzle. Oz solved correctly first and third questions. When I visited Panorama office in Israel, I met with Oz and gave him the first price - the chess set with my autograph.

2. Francesco De Chirico. Francesco solved first question only, but he gave the most complete answer for it. Unfortunately, I found about his answer only upon my return from Italy, so I couldn't arrange a meeting.

3. Marius Dumitru. Marius is the only person who was able to solve the second question.

And now for the solutions themselves. You should reread the original blog for the problem setting.

1. Find yet another solution to the riddle which doesn't require neither IIF nor Filter functions.

This turned out to be the simplest question with variety of different answers. Popular answer was to use CASE operator instead, and while technically this is correct, I felt that this solution was a little bit of cheating and going against the spirit of the riddle. But several people also found the more elegant solution. This solution relies on the implicit conversion from boolean data type to integer data type. The idea is to modify the code at the end of the post and replace IIF over Condition for computing length of Head, to simply multiply the length of the set by either 0 or 1. Here is Francesco's code:

{ Head([TRUE SET], ([TRUE SET].Count * ABS(Condition))), Head([FALSE SET], ([FALSE SET].Count * ABS(NOT(Condition])))) }

The reason why Francesco used ABS function is because in AS2005 true maps to -1 and false maps to 0, but in AS2000 true maps to 1 and false still to 0, so use of ABS is the only way to keep solution working in both versions.

2. There is a flaw in the solution with the Head function which isn't there in the solution with Generate over Filter. Can you identify this flaw ?

This turned out to be the most tricky question. There were multiple attempts to solve it, but only Marius gave correct answer. The problem with Union of Heads happens when True Set and False Set have different dimensionalities. Even though one of the Heads will end up empty set, the MDX keeps track of dimensionality of empty sets too. And then Union will detect two sets with different dimensionalities and will raise an error.

3. In both solutions (with Head and with Generate over Filter) the Condition is evaluated twice. Is there a solution under given constraints which requires only single evaluation of solution ?

In my opinion, this was the most difficult question, and Oz solved it absolutely correctly. I copy below his answer verbatim - there is nothing I could add to it !

Let's call [Larger True] this condition: '[True Set].Count > [False Set].Count'
Now, the solution would be:
SubSet( 
{ 
  Head([True Set], Iif([Larger True], [True Set].Count, 0)), 
  [False Set], 
  Head([True Set], Iif([Larger True], 0, [True Set].Count)) 
}, 
Iif(Condition, Iif([Larger True], 0, [False Set].Count), Iif([Larger True], [True Set].Count, 0)), 
Iif([Larger True], [True Set].Count, [False Set].Count) 
) 
First, I make a union of the true/false sets, but make sure the larger set is first. Then I use subset, from the beginning of the first set, or the beginning on the second set, depending on the condition (the only place I use the condition), and depending on which set was first. The count of the subset is the count of the larger set – that's why I had to have the larger set first