Microsoft OLAP by Mosha Pasumansky : mdx

AS2008 MDX: subselects and CREATE SUBCUBE in non-visual mode

mosha — Tue, 04 Nov 2008 07:22:16 GMT

There were not very many changes to MDX syntax in AS2008, and Vidas Matelis described most of them in his blog here (for his list of all changes in AS2008 check this blog entry). I just noticed that there is at least one more change which Vidas didn’t include, but which is somewhat important: ability to define CREATE SUBCUBE and subselects in non visual mode.

To understand this, we need to understand what CREATE SUBCUBE does. It really does two things:

1. Implicit Exists between the set in CREATE SUBCUBE and query axis, query named sets including top level set functions (for more details see blog "Slicer and axis interaction in MDX Part 2 - Implicit Exists" – even though it talks about WHERE clause, everything from that entry equally applies to subselects and CREATE SUBCUBE as well)

2. Applies visual totals to the values of physical measures even within expressions if there are no coordinate overwrites.

Let’s look into this deeper. Consider the following example:

CREATE SUBCUBE [Adventure Works] AS 
 (SELECT 
    {
       [Customer].[Customer Geography].[City].&[Redmond]&[WA]
      ,[Customer].[Customer Geography].[City].&[Seattle]&[WA]
    } ON 0 
  FROM [Adventure Works])

We are creating filter to include only two cities – Redmond and Seattle. Now if we send the following query:

WITH 
 MEMBER [Measures].[Gross Margin] AS [Measures].[Internet Gross Profit]/[Measures].[Internet Sales Amount]
   ,FORMAT_STRING = 'Percent' 
SELECT {[Measures].[Internet Sales Amount], [Measures].[Internet Gross Profit],[Measures].[Gross Margin]} ON 0
, [Customer].[City].MEMBERS ON 1
FROM [Adventure Works]

	Internet Sales Amount	Internet Gross Profit	Gross Margin
All Customers	$153,989.23	$64,075.99	41.61%
Redmond	$78,824.37	$33,150.36	42.06%
Seattle	$75,164.86	$30,925.63	41.14%

We will observe two things. First, we only get two cities on row axis – that’s the implicit exists part. Second, the values for physical measure Internet Sales Amount, cube defined calculated measure Internet Gross Profit and query defined calculated measure Gross Margin – are all visual totals, i.e. they all are computed using data for only two cities defined by the CREATE SUBCUBE command.

AS2008 adds capability to only keep implicit exists behavior, and to return real totals as opposed to visual totals. The syntax is described in MSDN documentation and in our example it will be (note that NON VISUAL keywords)

CREATE SUBCUBE [Adventure Works] AS NON VISUAL
 (SELECT 
    {
       [Customer].[Customer Geography].[City].&[Redmond]&[WA]
      ,[Customer].[Customer Geography].[City].&[Seattle]&[WA]
    } ON 0 
  FROM [Adventure Works])

Now if we execute the same query, the result is different:

	Internet Sales Amount	Internet Gross Profit	Gross Margin
All Customers	$29,358,677.22	$12,080,883.65	41.15%
Redmond	$78,824.37	$33,150.36	42.06%
Seattle	$75,164.86	$30,925.63	41.14%

The implicit exists still happens, we see only two cities, but there is no visual totals anymore. Both physical and calculated measures are computed now as if there were no CREATE SUBCUBE. Same functionality applies also to subselects (documented on MSDN here).

Why is this important ? Remember, that Excel 2007 uses subselects to implement multiselect functionality. However, since subselects only supported visual totals mode before AS2008, Excel team was forced to remove ability to show non-visual totals in PivotTables. This caused users dissatisfaction, since users wanted to have control over numbers in PivotTables, just like with previous versions of Excel and Analysis Services. This problem is well documented, for example in "Common Questions Around Excel 2007 OLAP PivotTables" blog entry by Allan Folting from Excel team. So implementing NON VISUAL mode in AS2008 is a key enabling feature to Excel to be able to restore this PivotTables functionality in the future releases.

Custom filters with MDX subcubes

mosha — Tue, 04 Nov 2008 06:30:43 GMT

Omniture Discover is not general purpose OLAP client tool. Rather it is specialized application that lets users to explore data from the Omniture’s Web Analytics product. But it does look and feel like an OLAP browser – which is not surprising, since “A” in OLAP stands for “Analysis”, and the product is all about analyzing web site performance. Omniture model feels like typical cube too - there are dimensions (called segments), and there are measures (called metrics), and there are drilldowns, pivots, aggregations etc. However, there is something not typical about this client tool – the way it handles slicers. Usually, OLAP client provides a UI element called slicer or filter or page field or something similar, where user can choose how the current view should be sliced. UI for slicer does differ between applications, from classic drilldown list to calendar control for Time dimension to something else, but there is always something to choose the current slice. Omniture Discover doesn’t have this. Instead, it uses different paradigm. User is expected to define “custom segments”, which really are dimension filters, but they are given names and list of these segments is stored in special folder. Then user can apply these predefined filters to any report view. The definitions of the segments are very flexible, they really can be arbitrary expressions – something from “Last 3 days” (filter on Time) to “Users referred by www.live.com through paid search campaign who bought product in specific category” (filter on Referer, Action and Product dimensions) etc. Dimensions used in the custom segment definition can be included in the report, and can be omitted from it – the filter is applied in either case. The only limitation is that only one such segment can be applied to the report at a time – if the user want to combine two custom segments, he needs to create a new one. First I was taken aback by how unusual this approach was, but then I saw that it could come in handy sometimes.

So this is an interesting approach, how could we add this capability to existing Analysis Services client tools ? Well, the only way to extend existing client UI functionality is through actions, so it is clear we will need to use actions here, but how can we implement custom segments in MDX ? Actually, MDX has perfect solution for that – and it is CREATE SUBCUBE statement – in fact – this is exactly what CREATE SUBCUBE was invented for in the first place – to be able to apply arbitrary custom filters on any MDX query. CREATE SUBCUBE sets this filter in the session state, and all MDX queries executed after it are affected by it.

Let’s take few examples from Adventure Works 2008 cube to demonstrate this approach in action:

1. Filtering on named set.

In this example, we want to limit analysis to the top 50 customers. Fortunately, Adventure Works cube already has named set defined inside MDX Script which defines these customers. So, our CREATE SUBCUBE will be fairly simple:

CREATE SUBCUBE [Adventure Works] AS (SELECT [Top 50 Customers] ON 0 FROM [Adventure Works])

The only tricky thing here to remember is how actions work in Analysis Services. The action expression is an MDX expression, which, in case of STATEMENT action type, should return string representing MDX statement to be executed by the client tool. Therefore, in the action expression field – we need to put a string with statement inside, not the statement itself. This will become more clear in example 3.

Here is how action definition will look like:

Name          : Top 50 Customers
Target type   : Cells
Target object : All cells
Type          : Statement
Expression    : "CREATE SUBCUBE [Adventure Works] AS (SELECT [Top 50 Customers] ON 0 FROM [Adventure Works])"
Invocation    : Interactive
Caption       : Top 50 Customers
Caption is MDX: false

This action now works fine inside cube browser – once executed, the current view needs to be refreshed (OWC doesn’t do it automatically after action execution) to reflect the currently set filter. The action will work in any client tool which properly supports statement actions. Unfortunately, it seems like Excel 2007 is not such a tool – I wasn’t able to get statement actions to work in it.

2. Filtering on multiple dimensions.

Of course the custom filters don’t have to be on single attribute, hierarchy or dimension – they can employ any combination of those. In this example, we want to filter on transactions performed by customers with low income, but on high priced products, perhaps this segment is susceptible for fraud.

We have Yearly Income attribute in the Customer dimension, and we will pick “10000-30000” bucket as “low income” indicator. The Product dimension has List Price attribute, and we will take all the members with value above $1700 as high priced products – in MDX this is best done with range set operator [Product].[List Price].&[1700.99] : NULL

CREATE SUBCUBE [Adventure Works] AS
(
SELECT 
   [Customer].[Yearly Income].&[0] * {[Product].[List Price].&[1700.99] : NULL}  ON 0
   FROM [Adventure Works]
)

The action definition would be

Name          : Potential Fraud
Target type   : Cells
Target object : All cells
Type          : Statement
Expression    : "CREATE SUBCUBE [Adventure Works] AS 
  (SELECT [Customer].[Yearly Income].&[0] * {[Product].[List Price].&[1700.99] : NULL} ON 0 
  FROM [Adventure Works])"
Invocation    : Interactive
Caption       : Potential Fraud
Caption is MDX: false

3. Dynamic (context dependant) filters

So far we only explored static filters – i.e. they always defined the same subspace, regardless of the position in the cube. This is how all Omniture custom segments work. However, MDX is, of course more powerful than that ! Action expression is called expression for a reason – it doesn’t have to return the constant string all the time. Let’s say we want to build a filter which will return last 3 months starting with the current month. In MDX we can do this easily using range set operator over Lag. The action target this time will be members of the Month attribute – because it only makes sense to count 3 months back if we are at granularity of Month (or if we are below it – so we could also define similar action over Days as well). The client tool will send the current Month coordinate, and it can be used inside MDX by referring to CurrentMember in the Month hierarchy. With some simple string concatenations (which unfortunately are always required to make dynamic action) we will get the following for action expression:

"CREATE SUBCUBE [Adventure Works] AS (SELECT " + 
[Date].[Month Name].CurrentMember.UniqueName + 
".Lag(2):" + [Date].[Month Name].CurrentMember.UniqueName + 
" ON 0 FROM [Adventure Works])"

The rest of parameters for the action are

Name          : Last 3 Months
Target type   : Attribute Members
Target object : Date.Month Name
Type          : Statement
Expression    : see above
Invocation    : Interactive
Caption       : Last 3 Months
Caption is MDX: false

4. Arbitrary complex filters

So far every filter we have discussed – was theoretically possible to define using standard slicer UI in client tools. That’s because every condition we put was either on single hierarchy or when it was on multiple hierarchies, they were combined using AND logic, i.e. using CrossJoin operator. But custom filters can be arbitrary complex – they can include any condition logic, combination of AND, OR, NOT, XOR and other logical operators. The good news are that even though it is usually impossible to set such complex filters in standard UI, it is totally possible with custom CREATE SUBCUBE statement. Let’s take the following example: We want to define a segment of customers with family. Definition of family means that either customer is married, or he has children. Customer dimension has the Marital Status attribute – so this will give us married or not information, and Customer dimension also has Number of Children attribute – everything which is not 0 – counts as having children – in MDX this can be easily coded with set unary operator "-". Now we need to figure out how to implement OR logic condition with sets – the answer is to use Union operator. We will union married customers no matter how many children they have, and customers with children no matter what their marital status is. Of course, there will be big overlap between these two sets, but CREATE SUBCUBE is going to do the right thing – it guarantees that there will be no double counting even if the set used to define it has duplicates.

CREATE SUBCUBE [Adventure Works] AS
(SELECT 
   {([Customer].[Marital Status].&[M], [Customer].[Total Children].[All Customers])
    ,([Customer].[Marital Status].[All Customers],-{[Customer].[Total Children].&[0]})}
ON 0 FROM [Adventure Works])

The resulting union set has interesting shape – it cannot be represented as pure crossjoin of attribute hierarchies. Such sets are nicknamed “arbitrary shaped sets” in Analysis Services, because they represent shapes of arbitrary complexity. Analysis Services in general doesn’t like arbitrary shaped sets, and there are often performance issues when using them, but if the business requirement calls for them – they can be used and will work correctly, although not most efficiently.

5. Clearing up

And, of course, we also need to provide a way for the user to remove whatever filter he had set. This is done with DROP SUBCUBE command, so we need to provision one more action to clear things up.

Name          : Clear Segments
Target type   : Cells
Target object : All cells
Type          : Statement
Expression    : "DROP SUBCUBE [Adventure Works]"
Invocation    : Interactive
Caption       : Clear Segments
Caption is MDX: false

Gross margin - dense vs. sparse block evaluation mode in MDX

mosha — Sat, 01 Nov 2008 22:23:35 GMT

Gross margin (also known as Gross profit margin or Gross profit rate) is defined as (Revenue – Cost of Sales)/Revenue. In terms of Adventure Works sample database we can write this in MDX as

[Gross Margin] = ([Measures].[Internet Sales Amount] - [Measures].[Internet Total Product Cost]) / [Measures].[Internet Sales Amount];

While this expression is simple enough, it might be tempting to try to optimize it. If we look at the evaluation tree for this expression – we will see 5 nodes in it: Fetching values for Internet Sales Amount twice – because it appears twice in the expression, fetching values for Total Cost, one minus operation and one division operation. Accessing Internet Sales Amount twice is not really a problem, because second access is going to come from cache anyway, but one might think that removing this extra operation would still improve thing, if only a little.

So, perhaps we can rewrite the formula using simple math equivalence: (a-b)/a = 1 – b/a

The first problem with this rewrite is that it is not always correct. In math it relies on the fact that a/a=1, but is is only true when a <> 0. The result of a/a is undefined when a=0. In MDX, 0/0 will also result in undefined number, usually formatted as “-1.#IND”. But more interesting question is what would happen in MDX when a is empty, i.e. has value of NULL. In MDX NULL/NULL = NULL, therefore when both Sales and Cost are empty, i.e. there is no record in the fact table, the two formulas are going to give different results.

(a-b)/a = NULL when a = NULL and b = NULL
1 – b/a = 1    when a = NULL and b = NULL

So at very least someone first need to set exact definition of Gross margin at the coordinates where no sales occurred. Is Gross margin 100% there or is it empty as well ?

But I want to direct your attention to the performance aspect of this change. Remember, it seemed that 1-b/a would perform a little bit better – is it really so ? Let’s compare both approaches side by side in AS2005 using MDX Studio.

// Gross margin using 1-a/b
WITH 
  MEMBER [Gross Margin] AS 
       1
    - 
        [Measures].[Internet Total Product Cost]
      / 
        [Measures].[Internet Sales Amount] 
    , FORMAT_STRING = 'Percent'
  MEMBER [Max Gross Margin] AS 
    Max
    (
      (
        [Customer].[Customer Geography].[Customer]
       ,[Product].[Product Categories].[Subcategory]
       ,[Date].[Calendar].[Calendar Year]
      )
     ,[Measures].[Gross Margin]
    ) 
SELECT 
  [Measures].[Max Gross Margin] ON 0
FROM [Adventure Works];

Time              : 6 sec 640 ms
Calc covers       : 6
Cells calculated  : 1
Sonar subcubes    : 2
NON EMPTYs        : 0
Autoexists        : 1
EXISTINGs         : 0
SE queries        : 2
Cache hits        : 3
Cache misses      : 1
Cache inserts     : 1
Cache lookups     : 4
Memory Usage KB   : 88840

WITH 
  MEMBER [Gross Margin] AS 
      (
        [Measures].[Internet Sales Amount]
      - 
        [Measures].[Internet Total Product Cost]
      )
    / 
      [Measures].[Internet Sales Amount] 
    , FORMAT_STRING = 'Percent'
  MEMBER [Max Gross Margin] AS 
    Max
    (
      (
        [Customer].[Customer Geography].[Customer]
       ,[Product].[Product Categories].[Subcategory]
       ,[Date].[Calendar].[Calendar Year]
      )
     ,[Measures].[Gross Margin]
    ) 
SELECT 
  [Measures].[Max Gross Margin] ON 0
FROM [Adventure Works];

Time              : 234 ms
Calc covers       : 7
Cells calculated  : 1
Sonar subcubes    : 1
NON EMPTYs        : 0
Autoexists        : 0
EXISTINGs         : 0
SE queries        : 3
Cache hits        : 3
Cache misses      : 1
Cache inserts     : 1
Cache lookups     : 4
Memory Usage KB   : 0

The results come shockingly different. Almost 7 seconds for the “optimized” approach, and mere 234 ms for the original approach. Let’s try to understand why. First let’s compare the number of SE queries. It is 3 in the (a-b)/a approach, and 2 in the 1-b/a – so our optimization to reduce number of SE queries to fetch Internet Sales Amount indeed worked (and as we can see from hierarchical profiling), on cold cache run, only first SE query went for partitions, the other two came from cache).

Usually, when we get such a big difference in the results, we suspect that slow query executes in the cell-by-cell mode, and fast query executes in the block mode. However, there is no reason to believe that the slow query went through cell-by-cell mode. Both expressions use exactly same operands: minus and divide, and both of these are optimized for block mode. Perfmon counters from the run also don’t give evidence for the cell-by-cell mode. But then, if both queries indeed went through block mode, why one query is so much significantly slower than the other one ?

The answer lies in the density and sparsity characteristics of the space. Let’s take closer look at both evaluation subtrees:

Operator "/"
 |
 +-- Operator "-"
 |    |
 |    +-- SE Data (Internet Sales Amount, Customer, Subcategory, Year)
 |    |
 |    +-- SE Data (Internet Total Product Cost, Customer, Subcategory, Year)
 |
 +-- SE Data (Internet Sales Amount, Customer, Subcategory, Year)

In this tree every leaf element is SE Data, and it is sparse – i.e. even though the query subcubes cover big space, the SE Data node only fetches records that exist in fact table, i.e. non empty data. Both “/” and “-” operators operate efficiently on sparse data, because they can iterate only over non-empty cells (the algorithm for operator “*” was explained here, the algorithms for “/” and “-” are slightly more complicated, but similar).

Now the evaluation subtree for the slow query:

Operator "-"
 |
 +-- Constant (1) (Customer, Subcategory, Year)
 |
 +-- Operator "/"
      |
      +-- SE Data (Internet Total Product Cost, Customer, Subcategory, Year)
      |
      +-- SE Data (Internet Sales Amount, Customer, Subcategory, Year)

Here we have two leaf nodes of SE Data – no problem with them, but another leaf node is constant 1. Since it is constant, it will evaluate the same value over all the cells in its subcube – and this value, 1, is not NULL. Therefore, this node evaluates to non empty result in every single cell. So while the query plan is still using block mode, it doesn’t get much benefit out of it, because the “block” is dense, there is no single empty cell in it, and while algorithm says to iterate over non empty cells, it ends up iterating over every cell, which makes it as slow as if it was in cell by cell mode.

Optimizing order of sets in MDX crossjoins

mosha — Fri, 24 Oct 2008 07:00:05 GMT

For scalar values a*b is the same as b*a. (although we saw that performance of these two could be different in MDX in “Performance of multiplication in MDX” article). But if a and b are sets, then obviously results are different – since the order of tuples in the resulting set will be different. But does it matter if we were to perform some other operation on top of result, which wouldn’t depend on order of the tuples ? Semantically – the answer is no, but there could be a big difference in performance. Let’s take the following example:

WITH MEMBER [Measures].x AS
 Count(
   (
     [Customer].[City].[City]
    ,[Customer].[Gender].[Gender]
    ,[Customer].[Education].[Education]
    ,[Product].[Subcategory].[Subcategory]
    ,[Product].[Color].[Color]
    ,[Product].[Size].[Size]
   )
 )
SELECT x ON 0
FROM [Adventure Works]

This query runs in 78 ms – no problem. But if we do innocently looking change – move one set to another position -

WITH MEMBER [Measures].x AS
 Count(
   (
     [Customer].[City].[City]
    ,[Product].[Subcategory].[Subcategory]
    ,[Customer].[Gender].[Gender]
    ,[Customer].[Education].[Education]
    ,[Product].[Color].[Color]
    ,[Product].[Size].[Size]
   )
 )
SELECT x ON 0
FROM [Adventure Works]

Now this query takes 13 seconds, and also grabs significant memory. So what happened ? To understand this better, let’s go back to the first query and pay closer attention to what MDX Studio told us about the execution. We notice, that in PerfMon tab, it reported that there were 2 autoexists, and also hierarchical profiler shows 2 NON EMPTY events (each one corresponding to autoexist operation). Why does it report 2 ? The query has just single crossjoin, so there should’ve been only 1 autoexist. What happens here is that crossjoin detects that the set that it needs to apply autoexists to, can be nicely split into two parts – first part related to Customer dimension, and second part to Product dimension. Then it can apply the following formula

JOIN( c1, c2, c3, p1, p2, p3 ) = CROSSJOIN( INNERJOIN(c1,c2,c3), INNERJOIN(p1,p2,p3) )

I.e. it can run inner join against each dimension table separately, and then do full cross join between results. Inner join against single dimension table is very efficient since even in worse case, we don’t need to go deeper than the dimension key to which all other attributes relate – so this isn’t going to take additional memory or much time.

However, in second case, the equation doesn’t hold anymore, since the order of dimensions inside crossjoin is mixed. So some other, much less efficient algorithm will be needed, probably loop join, with crossjoin now being pushed to the earlier stages. This is very inefficient and also going to take lots of additional memory.

Conclusion: Inside crossjoin, cluster all the sets from the same dimension together – this will result in great benefit to performance.

Optimizing MDX aggregation functions

mosha — Wed, 22 Oct 2008 19:32:01 GMT

One of the most significant changes in Analysis Services 2008 was improving performance of MDX queries and calculations. In particular, query optimizer can choose block (a.k.a. subspace) computation mode in query plan more often than in AS2005, and usually using block/subspace computation mode brings performance orders of magnitude better than without it. However, even in AS2008, query optimizer is not always capable of using block mode. The “Performance Improvements for MDX in SQL Server 2008 Analysis Services” whitepaper documents which MDX constructs are optimized and which not, and MDX Studio automates process of analyzing MDX and figuring out which fragments are not optimized, and offers advice how to optimize them. In this article we will cover techniques related to the optimization of aggregation functions in MDX, i.e. Sum, Min, Max, Aggregate and (in AS2008) Avg. All examples were done on AS2008 version of Adventure Works (some of these techniques will work with AS2005, but others won’t).

1. Aggregating over Filter

Scenario: We want to compute average sales for the products which increased its sales since same date last month. The normal approach is to use Filter function to determine which products grew their sales, and then apply Avg to the resulting set. Now applying this to every day in the Year 2003, we will get

WITH 
  MEMBER [Measures].AvgGrowingProducts AS 
    Avg
    (
      Filter
      (
        [Product].[Product].[Product].MEMBERS
       ,[Measures].[Sales Amount] > ([Measures].[Sales Amount],ParallelPeriod([Date].[Calendar].[Month]))
      )
     ,[Measures].[Sales Amount]
    ) 
SELECT 
  [Measures].AvgGrowingProducts ON 0
 ,Descendants
  (
    [Date].[Calendar].[Calendar Year].&[2003]
   ,[Date].[Calendar].[Date]
  ) ON 1
FROM [Adventure Works];

Running this query took more than 7 seconds on my laptop, and the culprit is clear from the perfmon counters – there were 295011 cells calculated – this is a sign of cell-by-cell iterations as opposed to working in the block mode. And if we run “Analyze” function of MDX Studio, it will tell us why this is happening: Function ‘Filter’ was used inside aggregation function – this disables block mode. How do we get rid of Filter ? I actually has written about it before – in the “Optimizing Count(Filter(...)) expressions in MDX” with respect to the Count function, but same technique can be applied to any aggregation function, including Avg (in AS2008). The idea is simple – even though Filter reduces the set of products, in order to compute the Filter in the first place, we really have to check sales of every single product, so we may as well just run Avg over all products, but with smart expression which will return NULL in case product should not be included – and since Avg ignores NULLs, this will work correctly. Here is how rewritten MDX will look like:

WITH 
  MEMBER [Measures].Growth AS 
    IIF
    (
      [Measures].[Sales Amount] > ([Measures].[Sales Amount] ,ParallelPeriod([Date].[Calendar].[Month]))
     ,[Measures].[Sales Amount]
     ,NULL
    ) 
   ,FORMAT_STRING = 'Currency' 
  MEMBER [Measures].AvgGrowingProducts AS 
    Avg
    (
      [Product].[Product].[Product].MEMBERS
     ,[Measures].Growth
    ) 
SELECT 
  [Measures].AvgGrowingProducts ON 0
 ,Descendants
  (
    [Date].[Calendar].[Calendar Year].&[2003]
   ,[Date].[Calendar].[Date]
  ) ON 1
FROM [Adventure Works];

Now it executes in mere 200 ms, and number of cells calculated is 365 – exactly the same number as number of cells in the result – which is the best theoretical value we can get !

2. Aggregating over NonEmpty

Scenario: Compute average sales for all products. The solution is straightforward:

WITH 
  MEMBER [Measures].AvgProductSales AS 
    Avg
    (
      [Product].[Product].[Product].MEMBERS
     ,[Measures].[Sales Amount]
    ) 
SELECT 
  [Measures].AvgProductSales ON 0
 ,[Date].[Date].[Date].MEMBERS ON 1
FROM [Adventure Works];

It is very efficient – only 100 ms and single SE query. But what if someone wants to optimize it even more ? Is this possible ? Poking with MDX Studio expression debugger, we can notice, that the sets over which Avg is run are pretty sparse. For example, on August 7, 2001 – there were only 3 products which had sales, the rest was empty. Year later, on August 7, 2002 there were only 5 products which sold. So, it might be tempting to add NonEmpty function over the products, in a hope to reduce the size of the set before it is fed to Avg:

WITH 
  MEMBER [Measures].AvgProductSales AS 
    Avg
    (
      NonEmpty
      (
        [Product].[Product].[Product].MEMBERS
       ,[Measures].[Sales Amount]
      )
     ,[Measures].[Sales Amount]
    ) 
SELECT 
  [Measures].AvgProductSales ON 0
 ,[Date].[Date].[Date].MEMBERS ON 1
FROM [Adventure Works];

This turns out to be a big mistake. The execution time jumps 18-fold to 1 sec 796 ms, and the all the perfmon counters jump as well, with number of SE queries going to 1189 from 1. I have written about this before, but it is worthwhile to reiterate. Unfortunately, this kind of wrong advice “reduce the space with NonEmpty before applying computation” is a popular myth. I see it mentioned in various presentations, and it even shows up in otherwise good “MDX Performance Hints” document. The irony is that the tip which is supposed to improve performance, actually makes it worse. The reason here is twofold:

1) Since NonEmpty is used inside calculated member, it will be computed every time this calculated member is invoked. No block mode. Each call to NonEmpty triggers at least one SE query. This is why we see so many SE queries in perfmon – one for every cell which uses AvgProductSales calculated member.

2) The result of NonEmpty could be a set of any shape and form. It is very difficult for Avg to work with such unpredictable sets. In our example we got lucky, that since we only had single hierarchy inside NonEmpty – we didn’t end up with arbitrary shape set – otherwise performance would’ve been even worse.

So the guidance is to never use NonEmpty inside calculations, but it is OK to use NonEmpty while building axes or named sets – because then it will be only called once and not for every cell.

3. Aggregating over Union

Scenario: Running sum by day. Typical and very sensible approach to this problem is to call Sum over all the preceding days. Assuming that our calculation will only ever be called on Date attribute (this is easy to ensure with SCOPE assignment, but for simplicity I will put it here as regular calculated member):

WITH 
  MEMBER [Measures].RunSales AS 
    Sum
    (
      NULL : [Date].[Date].CurrentMember
     ,[Measures].[Sales Amount]
    ) 
SELECT 
  [Measures].RunSales ON 0
 ,[Date].[Date].[Date].MEMBERS ON 1
FROM [Adventure Works];

This works OK – about 2 seconds execution time, and in block mode. We still want to optimize it. The common optimization technique for running sum is to do summation at higher grains whenever possible. I.e. instead of running over days, we would like to run over months until the current month, and only from there over the remaining days in this month. This approach makes a lot of sense, but the implementation should be done carefully. Let’s see what will happen if we modify the set to be union of months and days:

WITH 
  MEMBER [Measures].RunSales AS 
    Sum
    (
      Union
      (
        NULL : [Date].[Calendar].Parent.PrevMember
       ,
        [Date].[Calendar].FirstSibling : [Date].[Calendar].CurrentMember
      )
     ,[Measures].[Sales Amount]
    ) 
SELECT 
  [Measures].RunSales ON 0
 ,[Date].[Date].[Date].MEMBERS ON 1
FROM [Adventure Works];

The results are disastrous ! The execution time rose above 3 seconds, but more alarmingly, both number of cells calculated and SE queries jumped to thousands. Why is that ? After all, if we read the official documentation, it says that Sum over Union is in fact optimized for block mode, but the results we see here clearly show the opposite. The root cause is a little bit deeper. It is true that Sum(Union()) is optimized, but one of the other conditions for aggregation functions to work optimally, is to have the input set in a good shape, i.e. not arbitrary shaped set. Our Union combines sets from different granularities, so we end up with the mixed grain set, which is classified as arbitrary shape. How can we fix it ? One way is to break single Sum into two Sum’s, each one over the set of uniform single grain, i.e.

WITH 
  MEMBER [Measures].RunSales AS 
      Sum
      (
        NULL : [Date].[Calendar].Parent.PrevMember
       ,[Measures].[Sales Amount]
      )
    + 
      Sum
      (
        [Date].[Calendar].FirstSibling : [Date].[Calendar].CurrentMember
       ,[Measures].[Sales Amount]
      ) 
SELECT 
  [Measures].RunSales ON 0
 ,[Date].[Date].[Date].MEMBERS ON 1
FROM [Adventure Works];

Now the results are great – the runtime is about 400 ms, and other stats look good too. It is possible to optimize it even further using techniques described in the “Take advantage of FE caching to optimize MDX performance” article. We notice that the first component of plus is the same for all days in the month, so we can take advantage of it by caching it at the first day and reusing for all other days as following:

WITH 
  MEMBER [Measures].RunMonthSales AS
      Sum
      (
        NULL : [Date].[Calendar].CurrentMember
       ,[Measures].[Sales Amount]
      )
  MEMBER [Measures].RunSales AS 
      ([Measures].RunMonthSales, [Date].[Calendar].Parent.PrevMember)
    + 
      Sum
      (
        [Date].[Calendar].FirstSibling : [Date].[Calendar].CurrentMember
       ,[Measures].[Sales Amount]
      ) 
SELECT 
  [Measures].RunSales ON 0
 ,[Date].[Date].[Date].MEMBERS ON 1
FROM [Adventure Works];

This gives better runtime of 280 ms, on bigger cubes the difference will be more significant.

4. Aggregating over Exists or CrossJoin ?

To be written when I get more time.

5. Aggregting over Descendants or EXISTING ?

To be written when I get more time.

Get most out of partition slices

mosha — Tue, 14 Oct 2008 06:36:33 GMT

Setting partition slice has always been an important optimization technique in Analysis Services. Every presentation talked about it and every whitepaper mentioned it, for example the Microsoft SQL Server 2000 Analysis Services Performance Guide contains a chapter appropriately named “Define the Data Slice for Each Partition”, here is a quote from it:

“If the data slice value for a partition is set properly, Analysis Services can quickly eliminate irrelevant partitions from the query processing and significantly reduce the amount of physical I/O and processor time needed for many queries issued against MOLAP and HOLAP partitions <skip>

Caution: Creating a partition without setting the data slice is not a good practice, and can result in considerable overhead being added to the system (artificially increasing response times). Without the data slice, Analysis Services cannot limit a query to the appropriate partitions and must scan each partition even if zero cells will be returned.

The data slice enables Analysis Services to determine which partitions contain data relevant to the query”

However, after AS2005 was released, there was a new tip making it into presentations - “For MOLAP partitions no need to specify slice property, it is detected automatically”. Even though I briefly debunked it before here, this rumor just kept popping up, and it even made it as far as official “OLAP Design Best Practices for Analysis Services 2005” document – here is the quote:

“For MOLAP partitions, you do not have to specify the slice because the server will be able to figure out the relevant partitions after the data has been processed”

Even worse, the official documentation on data slices goes as far as stating:

“Data slices are applicable only to objects that use the ROLAP storage mode”

But this is completely not true, and many people already discovered it. This Jesse Orosz’s blog entry has great description of 4 reasons why setting partition slice in MOLAP is important. In my blog I want to focus on #3 from his list – automatic slice detection is not perfect.

Here is how automatic slice detection works – during building indexes for the partition, for every attribute it determines the minimum and maximum data id values in this attribute (for more detailed explanation see section “Partition Slice” in the Microsoft SQL Server 2005 Analysis Services book, page 355). It is possible to discover what values exactly Analysis Services detected, either doing it hard way as described here, or doing it easy way as described here. In either case, it is clear, that since slice autodetection operates with ranges of data ids, it is totally possible that partition which contains only two attribute members, but one with the lowest possible data id (2), and another one with highest possible data id, and as a result the range will cover all possible values, i.e. will be useless.

On the other hand, if for certain attribute the data in partition contains only single value of member’s data id, then the range will be very effective – since such a range will be equivalent to slice on a single member.

So this must be the root of all the confusion in documentation and in whitepapers. Prior to AS2005, partition slice could have only been a single member. And specifying partition slice of a single member is really not necessary in MOLAP, since it will always be automatically discovered by autoslice (well, except for other 3 reasons that Jesse lists in his blog). But starting with AS2005, it is possible to specify MDX sets for partition slice, and that’s exactly the case where setting partition slice manually makes sense.

Analysis Services Stored Procedures Project on Codeplex features PartitionHealthCheck function, which shows the overlaps in data id ranges between partitions, but it is no clear what action the user can take when he discovers that the autodetected ranges are not optimal. User doesn’t have direct control over assignments of data ids to dimension members. What this blog entry is going to explore is how to avoid relying on autoslice altogether, and put the precise control over partition slices into the hands of the user.

Almost related attributes

First scenario to consider is what I call “almost related attributes”. I.e. we have attributes, which are not truly related to each other, but, on the other hand are not completely unrelated. Examples of such attributes are Zip code to State – it is almost many to one, but sometimes there are Zip codes which cross State boundaries. Another example is Week to Year. Year mostly contains 52 weeks, but the weeks at the beginning and end of year can cross to previous or next year.

Let’s use Adventure Works 2008 as our sample database. We have ‘Calendar Year’ and ‘Calendar Week’ attributes. Let’s first query for specific Calendar Year:

SELECT 
  [Measures].[Reseller Sales Amount] ON 0
FROM [Adventure Works]
WHERE 
  [Date].[Calendar Year].[Calendar Year].[CY 2004];

Running this query under Hierarchical Profiler of MDX Studio shows that only one partition – Reseller_Sales_2004 – was read for that query. This is because that partition contains data just for year 2004, and therefore the automatic slice detection worked just fine. But let’s query for a specific week in 2004:

SELECT 
  [Measures].[Reseller Sales Amount] ON 0
FROM [Adventure Works]
WHERE 
  [Date].[Calendar Week].&[17]&[2004];

Of course, we would like again to read just 2004 partition, but this time profiler shows that also partitions Reseller_Sales_2002 and Reseller_Sales_2003 were touched. This is pretty bad, this week 17 of 2004 is well inside year 2004 with no hope of crossing into 2003, let alone 2002 !

So why did automatic slice detection did such a poor job here. To answer this question, let’s look at data id values for the Calendar Week attribute:

WITH MEMBER [Measures].CalendarWeekDataID AS
  DataId([Date].[Calendar Week].CurrentMember)
 ,CAPTION = 'Data ID of Calendar Week'
SELECT [Measures].[CalendarWeekDataID] ON 0
, [Date].[Calendar Week].[Calendar Week] ON 1
FROM [Adventure Works];

This query shows the data id for every calendar week, and the problem is now obvious. The calendar weeks don’t have nice ordering by the years, instead first we get Week 1 of years 2002, 2003 and 2004 (this week didn’t exist in 2001), then Week 2 for these years etc. No wonder the range of data ids for calendar week, included week 17 of 2004 also in 2003 and 2002. We could fix the Calendar Week attribute, but we would still be at the mercy of Analysis Services about how it decides to assign data ids. Instead, we can explicitly set the slice on every partition in the ‘Reseller Sales’ measure group.

And what is better way to build this slice if not by using another MDX query !

WITH 
  SET WeeksIn2004 AS 
    Exists(
       [Date].[Calendar Week].[Calendar Week].MEMBERS
      ,[Date].[Calendar Year].[Calendar Year].[CY 2004])
  MEMBER [Measures].PartitionSlice AS
   "{" +
   Generate(
     WeeksIn2004 AS itr, 
         [Date].[Calendar Week].CurrentMember.UniqueName 
       + IIF(itr.CurrentOrdinal = WeeksIn2004.Count, "", ","))
   + "}"
SELECT [Measures].PartitionSlice ON 0
FROM [Adventure Works]

This returns long string representing MDX set with all the weeks in 2004. We just need to make sure that the resulting string is XML’ized, i.e. ‘&’ is replaced with ‘&’ if it is to be pasted directly into XMLA script for ALTER partition, and we are good to go.

When we apply this procedure to every partition, rerunning the query

SELECT 
  [Measures].[Reseller Sales Amount] ON 0
FROM [Adventure Works]
WHERE 
  [Date].[Calendar Week].&[17]&[2004];

Now only touches 2004 partition.

Related attributes

So far we considered “almost related attributes”, as for truly related attributes one may think there is no problem. But in fact, in Adventure Works 2008, Calendar Week 2004 is defined as related to Calendar Year. So why when we sliced on [Date].[Calendar Week].&[17]&[2004] it didn’t decode the year ? If we run coordinate decoding in MDX Studio, it does properly show ‘CY 2004’ being decoded. The problem here lies in the fact that FE does coordinate decoding lazily, only if it really needs to. This makes sense – otherwise there will be a lot of time spent decoding attributes that nobody needs. So in our query, FE noticed that there were no calculations looking at Calendar Year’s CurrentMember, and it left it non decoded, as showed by verbose data in profiler in MDX Studio:

Dimension 9 [Date] (0 0 0 0 0 0 0 0 0 0 52 0 0 0 0 0 0 0 0 0 0)  

[Calendar Week]:[Week 17 CY 2004]  
[Calendar Year]:0

But in this case, it looks like SE could benefit from attribute decoding on query subcube – this could be useful performance optimization.

What about multiple attributes

We now successfully dealt with Calendar Week attribute, but Date dimension contains plenty of other attributes. If their Data IDs are as messed up as with Calendar Week – we need to do something about them. Unfortunately here we run into unintentional, yet very unfortunate limitation of Analysis Services. It is impossible to set partition slice by multiple attributes if at least one of them is a set and not a single member (which is the only useful case for us !). This limitation stems from unfortunate combination of few different factors:

1. Sets used in partition slice have to comply with limitations of SetToStr(…, CONSTRAINED) (see documentation). There is a good reason for that – partition slices are computed when cube is not really ready for querying, so putting just any MDX set could trigger FE wanting to get data from the cube, which is not yet ready with unpredictable results.

2. SetToStr(…, CONSTRAINED) doesn’t allow crossjoin of sets using neither CrossJoin function, nor * operator, no tuple-like form – i.e. it is not possible to specify crossjoin

3. While it is possible to specify set of tuples for SetToStr(…, CONSTRAINED), it still doesn’t help, since FE is not capable of building a subcube out of set of tuples (instead it immediatelly resorts to dreaded arbitrary shaped form).

It would be useful if any one of these limitations was relaxed – because it would enable true great control over partition slices in non-trivial cubes.

Displaying duration values – MDX expressions in FORMAT_STRING

mosha — Sat, 27 Sep 2008 03:01:37 GMT

Suppose you have a calculation (or even a real measure) which returns elapsed time or duration. We want to present it to the user as such, i.e. break into hours, minutes and seconds. This sounds easy enough, we would just use appropriate FORMAT_STRING. If we read documentation, we quickly find that by using ‘hh’ for hours, ‘mm’ for minutes and ‘ss’ for seconds, we can have ‘hh:mm:ss’ formatting, i.e.

with member measures.delta as 0.14159
 ,format_string = 'hh:mm:ss'
select {measures.delta} on 0
from [Adventure Works]

will produce ‘03:23:53’ result. This works great, but if duration is greater than one day, we need to include number of days elapsed as well. So it may look like the format string ‘d “days” hh:mm:ss’ would work, but there are few problems with using ‘d’ inside format string. It is supposed to give the day of the month, therefore it can not work for more than 31 days, and it has other undesired effects on durations – like returning 31 for values between 0 and 1, and 30 for values between 1 and 2. So we need better solution. And the solution is to use real MDX expression to build FORMAT_STRING calculation property.

I discovered that even some of the advanced MDX users were surprised that MDX expressions can be used with FORMAT_STRING. Certainly, everybody knows that it is possible to use MDX expressions for other calculations properties, like BACK_COLOR – there is whole chapter dedicated to it in my “Fast Track to MDX” book, or even more exotic ones, like LANGUAGE – see this blog for example. But there are no examples of using MDX expressions in the most popular and widely used calculation property – FORMAT_STRING. (Perhaps the reason for that in AS2000 it was not possible, but it certainly is possible according to OLEDB for OLAP spec, and both AS2005 and AS2008 fully support it).

So in our case one way to write such expression would be

with member measures.delta as 3.14159
 ,format_string 
   = 
    IIF(
      measures.delta < 1
     ,'d "days" hh:mm:ss'
     ,'"' + cstr(int(measures.delta)) + ' days" hh:mm:ss')
select {measures.delta} on 0
from [Adventure Works]

And we get expected result of ‘3 days 03:23:53’ as a result.

Ratio to “Parent on Rows” in MDX

mosha — Fri, 12 Sep 2008 05:15:16 GMT

Every now and then the issue of computing a generic “ratio to parent” calculation comes up. There is a good overview of the problem in Darren Gosbell’s blog “MDX ratio of current parent issue”. I want to pick up the conversation where Darren left it:

It is technically possible to get "kind of" close doing something like the following using the Axis() function: <MDX fragment skipped> which sort of gives us a "Percent of Row parent" calculation and this is probably the best you can do, but if you crossjoin multiple hierarchies on the row axis we are in trouble again.

I am not big fan of calculation which depends on the content of axis, mostly because when result of the same expression at the same coordinates differ from query to query (and this happens when expression uses Axis function), the whole caching is destroyed, and as a result performance suffers. However, if there is a real business requirement to build calculated member like that – it is certainly possible, and in most generic way, which will work no matter how many hierarchies are in the row axis. What’s more interesting, it is even possible to do it in pure MDX, and this MSDN forum thread provides different approaches. However, none of these approaches is practical one. The practical solution is to use the following stored procedure:

public decimal RatioToParent(Set axis, Expression exp)
{
    Hierarchy h = null;

    // Iterate over all hierarchies in the set
    int cHier = axis.Hierarchies.Count;
    int iHier;
    for (iHier = cHier-1; iHier >= 0; iHier--)
    {
        h = axis.Hierarchies[iHier];
        // and find the hierarchy where the current member is not yet at the highest possible level
        if (h.CurrentMember.ParentLevel.LevelNumber > 0)
            break;
    }

    // If there were no such hierarchy found - report ratio of 100%
    if (h == null || iHier < 0)
        return 1;

    // Since current member in this hierarchy is not yet at the highest level, we can safely call .Parent
    TupleBuilder tb = new TupleBuilder(h.CurrentMember.Parent);
    // and divide value at current cell by the value of its parent
    return (decimal)exp.Calculate(null) / (decimal)exp.Calculate(tb.ToTuple());
}

The way to use this stored procedure in the definition of calculated member is as in the following query – we have three different hierarchies on rows, and all the ratios come back correctly

WITH 
  MEMBER Measures.SalesRatioToParent AS 
    IIF
    (
      IsEmpty([Measures].[Sales Amount])
     ,null
     ,ASSP.ASStoredProcs.SetOperations.RatioToParent
      (
        Axis(1).Item(0)
       ,[Measures].[Sales Amount]
      )
    )
   ,FORMAT_STRING = 'Percent'
   ,NON_EMPTY_BEHAVIOR = [Measures].[Sales Amount]
SELECT 
  {
    [Measures].[Sales Amount]
   ,[Measures].[SalesRatioToParent]
  } ON 0
 ,NON EMPTY 
    (
      [Promotion].[Promotion Category].MEMBERS
     ,[Product].[Category].MEMBERS
     ,[Sales Territory].[Sales Territory].MEMBERS
    ) ON 1
FROM [Adventure Works];

Note the careful combination of IsEmpty([Sales Amount], NULL, …) construct and NON_EMPTY_BEHAVIOR. The condition of IIF ensures that NON_EMPTY_BEHAVOIR is set correctly in this scenario

I added RatioToParent function to my local copy of Analysis Services Stored Procedures project, I hope that it will get approved and become part of the official ASSP release.

Drillthrough on calculated measures

mosha — Tue, 02 Sep 2008 04:40:13 GMT

Drillthrough is a great feature of Analysis Services, but it has a limitation – only cells without calculations covering them can be drilled through. This automatically rules out drillthrough for calculated members in general and calculated measures in particular. This limitation has been a subject to much debate in forums and blogs. It has been logged on connect site as “Enable drillthrough on calculated measures” suggestion, where it gathered strong support. And competition struggles with very similar issues as well.

While there is no universal solution to this problem, in this article we will show how many practical particular cases can be solved in AS2005. Let’s first understand what does it mean to drill through on calculated measure. According to the definition:

“Drillthrough is the operation in which a user specifies a single cell and the Analysis server returns the most detail level data that contributed to that cell.”

For the regular cells it is pretty straightforward to figure out which detailed level data contributed to them, but in case of arbitrary calculation, it can be very tricky. Basically, one needs to reverse engineer the expression of the calculation, find out all the dependencies, and drill through to each one of them. But in case of simple calculations, such dependencies are obvious. Let’s consider the [Internet Gross Profit] calculated measure from the Adventure Works cube. It is defined as

Create Member CurrentCube.[Measures].[Internet Gross Profit]

 AS [Measures].[Internet Sales Amount] - [Measures].[Internet Total Product Cost],
 
Format_String = "Currency";

It is obvious, that [Internet Gross Profit] is computed from two real measures: [Internet Sales Amount] and [Internet Total Product Cost]. But how can we convey this to the engine, since no matter what the expression of the calculated measure is, the DRILLTHROUGH statement is going to fail on it. The trick here is to build a DRILLTHROUGH statement which redirects from calculated measure to the real measures it derived from. Below are the exact steps in order to achieve that:

1. Define new action. Note, that it cannot be “Drillthrough action”, since it won’t even apply to calculated measures. Just a regular action.

2. Set the “Action Target” property to “Cells” – after all drillthrough is done on cells

3. Since the “Target Object” for cells actions is always “All cells”, we will use condition to constrain our action just to the desired calculated measure. In the “Condition” property set the following expression

Measures.CurrentMember IS [Measures].[Internet Gross Profit]

4. “Action Type” needs to be set to “Rowset”, since drillthrough returns rowset.

5. Set the “Caption” to “Drillthrough”, so in UI of the client tool it will show up with this title, and it will be obvious to the user.

6. “Action Expression” is the most important property. Here we need to build the MDX DRILLTHROUGH statement which redirects to two real measures. The MDX DRILLTHROUGH does not allow multiple cells to appear in the SELECT clause. So how will we get two measures ? We will use the fact that both of them belong to the same measure group, and instead of putting them into the SELECT clause, we will send them to RETURN clause. Building custom return clause also has a benefit of having flexibility to choose which attributes will show up in the result of drillthorugh. By default all the granularity attributes of the measure group will be there, but we can include less or more attributes. We also get to decide on the actual content in these attribute columns – whether we want keys, names, unique names etc. Here is how the RETURN clause may look like:

RETURN 
 [Internet Sales].[Internet Sales Amount]
,[Internet Sales].[Internet Total Product Cost]
,NAME([$Date].[Date])
,NAME([$Customer].[Customer])
,NAME([$Product].[Product])
,NAME([$Promotion].[Promotion])
,NAME([$Source Currency].[Source Currency Code])
,NAME([$Sales Reason].[Sales Reason])

In order for drillthrough to address the correct cell, we need to put current cell coordinates into the SELECT clause. One way of doing it would be to write long MDX string concatenation, using <hierarchy>.CurrentMember.UniqueName subexpression for every hierarchy in the cube. However, this is tedious and error-prone approach. There are hundreds of hierarchies, new ones can be added, old ones removed or renamed etc. Instead, we can just call stored procedure which will enumerate all the current coordinates (note that we could’ve done the same with the RETURN clause too).

Analysis Services Stored Procedures Project (ASSP) actually features some sprocs very similar to what we need here, under FindCurrentMembers class. Similar, but not quite what we need. In our particular case, we want to skip measures (because we are hardcoded them), and also for brevity, we need to consider only attribute hierarchies. Below source code achieves just that:

public static string CurrentCellAttributes()
{
    // start with empty string
    string coordinate = String.Empty;
    bool first = true;
    foreach (Dimension d in Context.CurrentCube.Dimensions)
    {
        // skip measures
        if (d.DimensionType == DimensionTypeEnum.Measure)
            continue;

        foreach (Hierarchy h in d.AttributeHierarchies)
        {
            // skip user hierarchies - consider attribute and parent-child hierarchies
            // (parent-child is both user and attribute hierarchy)
            if (h.HierarchyOrigin == HierarchyOrigin.UserHierarchy)
                continue;
            if (!first)
                coordinate += ",";
            first = false;
            coordinate += h.CurrentMember.UniqueName;
        }
    }

    return coordinate;
}

Now, equipped with this powerful stored procedure, we can build the expression for the action:

"DRILLTHROUGH MAXROWS 100 SELECT (" +
ASSP.ASStoredProcs.FindCurrentMembers.CurrentCellAttributes()
+ ") ON 0 FROM [Adventure Works] 
RETURN 
 [Internet Sales].[Internet Sales Amount]
,[Internet Sales].[Internet Total Product Cost]
,NAME([$Date].[Date])
,NAME([$Customer].[Customer])
,NAME([$Product].[Product])
,NAME([$Promotion].[Promotion])
,NAME([$Source Currency].[Source Currency Code])
,NAME([$Sales Reason].[Sales Reason])
"

We are done ! Now if we deploy this action, any client that supports actions (such as Excel 2007 or cube browser), on the right click on cell under [Internet Gross Profit] calculated measure, we will see additional menu item “Drillthrough”, and clicking on it will bring the most detail level data that contributed to that cell.

Product Volatility: Optimizing MDX with MDX Studio

mosha — Wed, 30 Jul 2008 03:38:16 GMT

In this article we will show how to take typical, everyday MDX and optimize it by simply following advices of MDX Studio Analyzer.

As a starting point, I will take “Product Volatility” example as published by Thomas Ivarson in his blog. It is a perfect example, because Thomas took a real business problem, and implemented it using very clean and straightforward MDX, just like I expect many MDX practitioners do. This MDX can be optimized, but some of these optimizations might not be obvious. Running MDX Studio Analyzer and following its advices takes the guesswork out of equation.

Here is the MDX that computes total number of products, number of products with sales increased from last month and number of products with sales decreased from last month:

WITH 
  MEMBER Measures.TotNumberOfProducts AS 
    Count
    (
      Descendants
      (
        [Product].[Product Categories].CurrentMember
       ,[Product].[Product Categories].[Product]
      )
    ) 
  MEMBER Measures.NumberOfSoldProductsIncreasing AS 
    Count
    (
      Filter
      (
        NonEmpty
        (
          Descendants
          (
            [Product].[Product Categories].CurrentMember
           ,[Product].[Product Categories].[Product]
          )
         ,[Measures].[Internet Sales Amount]
        )
       ,
            (
              [Date].[Calendar].CurrentMember
             ,[Measures].[Internet Sales Amount]
            )
          - 
            (
              [Date].[Calendar].PrevMember
             ,[Measures].[Internet Sales Amount]
            )
        > 0
      )
    ) 
  MEMBER Measures.NumberOfSoldProductsDecreasing AS 
    Count
    (
      Filter
      (
        NonEmpty
        (
          Descendants
          (
            [Product].[Product Categories].CurrentMember
           ,[Product].[Product Categories].[Product]
          )
         ,[Measures].[Internet Sales Amount]
        )
       ,
            (
              [Date].[Calendar].CurrentMember
             ,[Measures].[Internet Sales Amount]
            )
          - 
            (
              [Date].[Calendar].PrevMember
             ,[Measures].[Internet Sales Amount]
            )
        < 0
      )
    ) 
SELECT
  {
    [Measures].[TotNumberOfProducts]
   ,[Measures].[NumberOfSoldProductsIncreasing]
   ,[Measures].[NumberOfSoldProductsDecreasing]
  } ON 0
 ,[Date].[Calendar].[Month] ON 1
FROM [Adventure Works]

Executing this query with hot cache in MDX Studio produces the following perfmon counter statistics:

Time             : 359 ms
Calc covers      : 85
Cells calculated : 7672
Sonar subcubes   : 79
SE queries       : 76
Cache hits       : 76
Cache misses     : 3
Cache inserts    : 3
Cache lookups    : 79

The time looks good, but only because both the cube and the query are small. The number of SE queries should raise a red flag here. With only 114 cells in the result, why there were 76 SE queries ? Obviously, on more serious cubes, this will become a major performance issue. So, let’s run MDX Studio Analyzer and see what it has to say about this query. We get back the following list:

Line	Col	Message	Link
3	6	If you are trying to count number of members in current selection - consider introducing special measure group for this dimension	More Info
7	41	MDX function 'CurrentMember' may raise an error or produce non-desired result when user applies multiselect	More Info
12	6	Consider rewriting Count(Filter(set, cond)) construct as Sum(set, summator) with summator IIF(cond, 1, NULL)	More Info
20	45	MDX function 'CurrentMember' may raise an error or produce non-desired result when user applies multiselect	More Info
27	34	MDX function 'CurrentMember' may raise an error or produce non-desired result when user applies multiselect	More Info
32	34	MDX function 'PrevMember' may raise an error or produce non-desired result when user applies multiselect	More Info
39	6	Consider rewriting Count(Filter(set, cond)) construct as Sum(set, summator) with summator IIF(cond, 1, NULL)	More Info
47	45	MDX function 'CurrentMember' may raise an error or produce non-desired result when user applies multiselect	More Info
53	14	Same expression was used before at Line 26 Column 14. Consider eliminating common subexpressions for better performance and to take advantage of cache

Let’s go over this list and fix issue by issue.

Line 3, Col 6: If you are trying to count number of members in current selection - consider introducing special measure group for this dimension

The link points to “Counting days in MDX” blog. While we count here Products and not Days, the same technique applies here. These types of questions are best done not in MDX, but by introducing measure group, which only contains Products dimension, and single measure [Number Of Products] of type Count.

Line 7, Col 41: MDX function 'CurrentMember' may raise an error or produce non-desired result when user applies multiselect

Now, this is no longer a problem because of how we solved the previous warning ! Indeed, if we wanted to see total number of products across Bikes and Accessories categories, we would’ve added the following WHERE clause:

WHERE {[Product].[Category].&[1],[Product].[Category].&[4]}

It fails with the original query, but works fine with Count measure [Number of Products]

Lines 12/39, Col 6: Consider rewriting Count(Filter(set, cond)) construct as Sum(set, summator) with summator IIF(cond, 1, NULL)

This is a common construct, and as shown in the link to the “Optimizing Count(Filter(…)) expressions in MDX”, it performs much better if rewritten to use Sum. But before we do this rewrite, let’s take a look at the next warning:

Line 53, Col 14: Same expression was used before at Line 26 Column 14. Consider eliminating common subexpressions for better performance and to take advantage of cache

If we click on this message, the expression in question is the following:

            (
              [Date].[Calendar].CurrentMember
             ,[Measures].[Internet Sales Amount]
            )
          - 
            (
              [Date].[Calendar].PrevMember
             ,[Measures].[Internet Sales Amount]
            )

Indeed, this expression represents Sales Growth, so we can isolate it into separate calculated member. But before we write the exact expression for the calculated member, let’s look deeper into the original expression. It features Count(Filter(NonEmpty(…))) construct, i.e. only products which had sales in current month are considered for increasing/decreasing calculation. I.e. even if product had sales in the previous month, but not in the current month, it won’t be counted in the number of products with decreasing sales. In order to repeat the same logic, the expression would be

  [Sales Growth] =
    IIF
    (
      IsEmpty([Measures].[Internet Sales Amount])
     ,NULL
     ,
        [Measures].[Internet Sales Amount]
      - 
        (
          [Date].[Calendar].PrevMember
         ,[Measures].[Internet Sales Amount]
        )
    )

For more details why IIF(cond, NULL, exp) is good for performance, read “Performance of IIF function in MDX” blog. Now, putting all of together so far, we will get the following query:

WITH 
  MEMBER [Sales Growth] AS 
    IIF
    (
      IsEmpty([Measures].[Internet Sales Amount])
     ,NULL
     ,
        [Measures].[Internet Sales Amount]
      - 
        (
          [Date].[Calendar].PrevMember
         ,[Measures].[Internet Sales Amount]
        )
    ) 
  MEMBER PositiveGrowth AS 
    IIF
    (
      [Measures].[Sales Growth] > 0
     ,1
     ,NULL
    ) 
  MEMBER Measures.NumberOfSoldProductsIncreasing AS 
    Sum
    (
      Descendants
      (
        [Product].[Product Categories].CurrentMember
       ,[Product].[Product Categories].[Product]
      )
     ,[Measures].[PositiveGrowth]
    ) 
  MEMBER NegativeGrowth AS 
    IIF
    (
      [Measures].[Sales Growth] < 0
     ,1
     ,NULL
    ) 
  MEMBER Measures.NumberOfSoldProductsDecreasing AS 
    Sum
    (
      Descendants
      (
        [Product].[Product Categories].CurrentMember
       ,[Product].[Product Categories].[Product]
      )
     ,[Measures].[NegativeGrowth]
    ) 
SELECT
  {
    [Measures].[Number Of Products]
   ,[Measures].[NumberOfSoldProductsIncreasing]
   ,[Measures].[NumberOfSoldProductsDecreasing]
  } ON 0
 ,[Date].[Calendar].[Month] ON 1
FROM [Adventure Works];

When executed in MDX Studio, we get the following perfmon statistics:

Time             : 171 ms
Calc covers      : 15
Cells calculated : 1993
Sonar subcubes   : 6
SE queries       : 3
Cache hits       : 7
Cache misses     : 5
Cache inserts    : 5
Cache lookups    : 12

This is now much better than before, there are only 3 SE queries. All of the performance warnings have been addressed, but we still have warnings about CurrentMember not working well with multiselect. Indeed, when we put multiselect on product categories on Bikes and Accessories, we get errors for number of increasing and decreasing products. Links to “Writing multiselect friendly MDX calculations” and “Multiselect friendly MDX for calculations looking at current coordinate” blogs are helpful to understand what the problem is. Unfortunately, there is no universal solution, and each case needs to be evaluated differently.

In this scenario, we can use apply to Sum(Descendants(…), exp) very similar technique that we applied to Count(Descendants(…)). The idea is not to use Descendants function, which forces us to use CurrentMember, which in turn fails on multiselect, but to put the expression “exp” directly on the Product attribute. In order to do that, we need to create fake measure in the measure group - “Num Products Increased”. It is bound to NULL expression, so this measure will always be NULL, and therefore won’t take any space in storage. But since this measure will have aggregation function Sum, it will properly aggregate when assigned expressions inside MDX Script.

We will use MDX Studio to emulate MDX Script too (MDX Studio has a feature, where it automatically uses cube selected in the dropdown list to resolve current cube used in script statements).

Here is the MDX:

CREATE 
  [Sales Growth] = 
    IIF
    (
      IsEmpty([Measures].[Internet Sales Amount])
     ,NULL
     ,
        [Measures].[Internet Sales Amount]
      - 
        (
          [Date].[Calendar].PrevMember
         ,[Measures].[Internet Sales Amount]
        )
    );

(
  [Product].[Product Categories].[Product]
 ,[Measures].[Num Products Increased]
) = 
  IIF
  (
    [Measures].[Sales Growth] > 0
   ,1
   ,NULL
  );

SELECT
  {
    [Measures].[Number Of Products]
   ,[Measures].[Num Products Increased]
  } ON 0
 ,[Date].[Calendar].[Month] ON 1
FROM [Adventure Works]
WHERE 
  {
    [Product].[Category].&[1]
   ,[Product].[Category].&[4]
  };

It’s performance characteristics are even slightly better than the query before:

Time             : 31 ms
Calc covers      : 6
Cells calculated : 76
Sonar subcubes   : 1
SE queries       : 1
Cache hits       : 2
Cache misses     : 0
Cache inserts    : 0
Cache lookups    : 2

Not to mention the fact that it works just fine with multiselect on products ! This is a great example, where fixing MDX to work properly in multiselect scenarios, has the side effect of improving performance.

We still have warnings about PrevMember with respect to multiselect on the Time dimension. It is more difficult problem, since the definition of “Sales Growth” when there is multiselect on the Time dimension is ambiguous.

Counting distinct values in MDX

mosha — Sun, 15 Jun 2008 00:13:01 GMT

How many elements in the set have distinct values ? This sounds like a simple question. Apparently, this is well known problem in Excel, and there is a classic solution for it. There are plenty of sites in Internet (for example here and here) which give the following solution:

=SUM(1/COUNTIF(A1:A6,A1:A6)))

(note that this formula needs to be entered with Ctrl-Shift-Enter to indicate that this is array formula, or it won’t give right result – one of the Excel quirks)

When someone showed me this solution, I just stared at it and I couldn’t understand how it worked. Only rewriting it in MDX clarified what was going on. Let’s take the following example based on Adventure Works – we want to count how different letters the product names start from (just wanted to pick something for which Product dimension didn’t have dedicated attribute, because otherwise it would’ve been too simple). The MDX which performs the same algorithm as Excel formula above in these conditions will look like following:

with 
  member measures.CountDistinctLetters as 
   Sum([Product].[Product].[Product].MEMBERS AS AllProducts,
       1/Filter([Product].[Product].[Product].MEMBERS, 
          VBA!Left(AllProducts.Current.Name,1) = VBA!Left([Product].[Product].CurrentMember.Name,1)
         ).Count)

select CountDistinctLetters on 0
from [Adventure Works]

It is more verbose, but probably it is not simpler to understand to normal developer than the Excel’s formula. In the nutshell the idea is following: For every element in the set, we compute how many other elements produce the same value (this is COUNTIF in Excel, and Filter(…).Count in MDX), and then dividing 1 by this number we get fraction of current element. The summing up all these fractions will give the desired number.

(Unfortunately there is no easy way to debug such formulas. The MDX debugger inside MDX Studio is not capable of dealing with such expressions. I actually spent quite a time thinking about how to build decent environment for debugging MDX, and after few failed starts I had what I consider as breakthrough idea. Early experiments were very encouraging, and if this idea works, I will blog soon about it. It radically different from all other MDX debugging attempts.)

It is clear that performance of such solution is horrible. For every element in the set, we go again and again over all elements in the set just to find out how many of them have same value. The algorithmic complexity of this nested loop approach is O(n^2). Indeed the query above executes for 12 seconds. It is possible to slightly improve it, by forcing caching the results of VBA!Left through

with 
  member Prefix as VBA!Left([Product].[Product].CurrentMember.Name,1)
  member measures.CountDistinctLetters as 
   Sum([Product].[Product].[Product].MEMBERS AS AllProducts,
       1/Filter([Product].[Product].[Product].MEMBERS, 
          (AllProducts.Current,Prefix) = Prefix
         ).Count)

select CountDistinctLetters on 0
from [Adventure Works]

and reducing time to 11 seconds, but obviously, different, more sane approach is needed here.

Another idea is instead of doing nested-loop algorithm, sort the elements of the set, and then scan sorted set once counting number of different elements. The only technical difficulty with that would be - while scaning sorted set, we need to compare current element with the previous one. Getting current element is easy, but how to get previous one ? There is no "Previous" function in MDX, there is only PrevMember, but it works on hierarchical ordering of the level, and here we have arbitrary set sorted by arbitrary criteria. The solution is to use CurrentOrdinal function, which returns the index of the tuple being iterated in the set, and then using CurrentOrdinal-1 as a new index, send it to Item function. In MDX this would be recorded as

with
  member Prefix as VBA!Left([Product].[Product].CurrentMember.Name,1)
  set ProductsSortedByPrefix as order([Product].[Product].[Product].MEMBERS, Prefix, BASC)
  member measures.CountDistinctLetters  as Sum(ProductsSortedByPrefix as y, 
    iif(y.CurrentOrdinal = 1 OR Prefix <> (Prefix, y.Item(y.CurrentOrdinal-2)), 1, 0))

select CountDistinctLetters  on 0
from [Adventure Works]

This query executes much faster now, in only 0.234 seconds. The algorithmic complexity is down to O(n*log(n)). However, it is still not perfect solution, since it is possible to solve this in just one scan of the original set, i.e. with O(n) complexity. In order to do that, we will have to write stored procedure though.

public int CountDistinctValues(Set set, Expression exp)
{
    System.Collections.Hashtable ht = new System.Collections.Hashtable();
    foreach (Tuple t in set.Tuples)
    {
        string v = exp.Calculate(t).ToString();
        if ( !ht.ContainsKey(v) )
            ht.Add(v, null);
    }

    return ht.Count;
}

Now, if we use this stored procedure in the query as following

with member CountDistinctLetters as ASSP.ASStoredProcs.Util.CountDistinctValues(
  [Product].[Product].[Product].MEMBERS,
  VBA!Left([Product].[Product].CurrentMember.Name,1))
select CountDistinctLetters on 0
from [Adventure Works]

we get it working in only 0.062 seconds.

MDX answer to NextAnalytics challenge

mosha — Mon, 09 Jun 2008 00:05:00 GMT

NextAnalytics is a young BI company founded by Ward Yaternick of OLAP@Work fame. He has a blog dedicated to his new product, and while I was reading it, I ran across the post named “Can a business intelligence product be used to answer analytic questions?”. The main premises of this post was that real-life non-trivial calculations are very difficult with traditional BI tools using SQL or MDX, but can be done in much simpler fashion using NextAnalytics product. Ward then gives very concrete example:

That’s the crux of the problem: The nature of analytics often means that the need evolves. It’s either because the analytics identified the problem or the analytic led to other questions. By their nature, analytics are iterative and sequential. For example:

I want to see the fastest growing products but it's more complicated than just telling me one measurement.

for each region, I want to see which products were growing faster than the rolling six month average, at least six times out of the last N periods.

next, again for each region, of those products that were just discovered, which ones grew by more than twenty five percent from the start of the period to the end of the period.

I want to know the intersection of those two sets of products across all regions.

By the way, I won’t need these questions answered until next year at this time, although I might change them a bit.

Certainly this looks like something that can be done with basic MDX. Ward, however, stated that

That’s an example of one analytic combined with another, iteratively and sequentially. Neither of which would be possible in MDX or SQL without the creation of a complex underlying data structure. Notice that this isn’t simply an OLAP “drill down” or a “slice and dice”. These are distinct questions, with the answers needing to be merged and intersected. This kind of query would take only a few hours in nextanalytics, or a few months in a BI tool.

I left a comment to the blog saying that I disagreed with Ward’s statement, and unless by “complex underlying data structure” he meant regular OLAP cube, I thought this example can be easily done in MDX. Ward took his time to reply to my comment, going into detail how this would be done with nextanalytics:

Allow me to describe the processing a bit better in a pseudocode style:
// the column axis has time periods, the row axis has regions and products.
for each region
{
// part 1
calculate the rolling six month average
calcualte the growth of that
count each "growth" calc to the row average
keep only rows above average six or more times
// part 2
remove all time periods except the first and last
calculate growth
remove any below 25 %
// part 3
intersect part 1 and part 2
}

And then he threw a gauntlet, challenging me to do the same in MDX as easily as the pseudocode above.

Note that in part 1, the output of the rolling average becomes the input to the growth calculations.
Now that I've been more clear, do you still think this is achievable in MDX? Is it fair of me to qualify your claim by saying "In a single MDX expression?" "someone with a normal amount of MDX education" and "not having to create intermediate data structures in a server or disk"? Because, if it takes any of that, then my blog's point still stands IMO.
To do all that with nextanalytics is about just as many lines as what I just typed above. No external tables, no external cubes. And, the nextanalytics "code" is still readable a year later and easily modified.

Well, I decided to pick up this challenge, and write complete implementation of this problem in MDX using Adventure Works sample database. I also decided to time myself to see how much time it will actually take – to have real answer to the claim that it would take “few months in a BI tool”. This weekend it was mix of rain and clouds in Seattle, so my family didn’t go backpacking, and I thought that I would use 2 hour window between teaching my son to bike on two wheels and going to watch “Chronicles of Narnia: Prince Caspian”, to implement this. This blog presents the results of this experiment.

I used MDX Studio to develop all of the code. One nice thing about it, is that it allows mixing MDX Script type of statement, which I used to define calculated measures with SELECT statements. In order to do it, I just specified “Cube=Adventure Works” in the connection string, and everything worked fine. (Of course, I also could’ve used more traditional syntax of CREATE MEMBER statement, but I am too used to MDX Scripts now).

I went line by line over the Ward’s pseudocode writing MDX for it. I didn’t try to write the super-optimized MDX, and I didn’t do any smart tricks – I just wanted to write natural, most straightforward MDX, that average MDX developer would’ve done in my place.

calculate the rolling six month average

There are several techniques how to approach it, and I wrote both chapter in my “Fast Track to MDX” book and blog entry about it, but let’s take the simplest approach:

// calculate the rolling six month average 
CREATE Rolling6MonthAverage;
(Rolling6MonthAverage, [Date].[Month Name].[Month Name]) 
  = Avg([Date].[Month Name].Lag(5):[Date].[Month Name], [Measures].[Internet Sales Amount]);

This leaves the calculated measure to be NULL at the attributes above (or unrelated to) Month, and computes rolling 6 month average for Month and all attributes to which Month is related (i.e. Day etc).

calcualte the growth of that

That’s the part where Ward made a comment of “Note that in part 1, the output of the rolling average becomes the input to the growth calculations”. I just wanted to note, that this is absolutely routine in MDX

// calcualte the growth of that 
CREATE GrowthOfRolling6MonthAverage = Rolling6MonthAverage - (Rolling6MonthAverage, [Date].[Month Name].PrevMember);

count each "growth" calc to the row average

The way I interpret this is we need to compare sales growth to the growth of rolling average and then count how many times sales growth exceeded growth of rolling average. For simplicity of reading, I broke this into two calculated measures, one computing sales growth, and another one doing counting. Of course, it could’ve been done in one calculated measure too, but this way it is a little more natural to read.

// calculate growth
CREATE GrowthOfSales = [Measures].[Internet Sales Amount] - ([Measures].[Internet Sales Amount], [Date].[Month Name].PrevMember);

In the original posting, Ward said “for each region, I want to see which products were growing faster than the rolling six month average, at least six times out of the last N periods.” So I really had choose what N periods would be in my example – and I choose that we are interested in the months of 2003.

// count each "growth" calc to the row average 
CREATE HowManyTimeGrowthExceededRollingAverage = 
  Count(
    Filter(([Date].[Calendar Year].&[2003],[Date].[Month Name].[Month Name]), GrowthOfSales > GrowthOfRolling6MonthAverage));

keep only rows above average six or more times

This is simple filter, but since we need to find products for every region, we also need to run Generate over regions. For Adventure Works, I choose Country attribute to represent regions:

// keep only rows above average six or more times 
CREATE SET ProductsThatGrewFasterThanRollingAverageMore6OrMoreTimes AS
 Generate([Customer].[Customer Geography].[Country], 
    CrossJoin([Customer].[Customer Geography],
      Filter(
        [Product].[Product].[Product], 
        HowManyTimeGrowthExceededRollingAverage >= 6
      )
    )
  );

My understanding of nextanalytics scripting language is that it computes static sets in certain context, and this is why I used CREATE SET statement in MDX. However, MDX is obviously capable of computing such a set completely dynamically, depending on the current context, slices, subcubes etc.

remove all time periods except the first and last
calculate growth

Aha, here we have an opportunity to replace two lines of nextanalytics script with just one line of MDX. We don’t need to remove anything to calculate growth from the start of period to its end. Assuming again that the period is months in 2003, we would just use OpeningPeriod and ClosingPeriod MDX functions.

// create growth from start to end of period
CREATE GrowthFromStartToEnd =
  ([Measures].[Internet Sales Amount], ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[Calendar Year].&[2002]))
- ([Measures].[Internet Sales Amount], OpeningPeriod([Date].[Calendar].[Month], [Date].[Calendar].[Calendar Year].&[2002]));

remove any below 25 %

One way to do it is to create calculated measure which computes relative growth as percentage, but then we would need to deal with division by zero handling, so it is simpler to code the 25% rule inside the Filter itself.

CREATE SET ProductsThatGrewMoreThan25PctFromStartToEnd AS
  Generate([Customer].[Customer Geography].[Country], 
    CrossJoin([Customer].[Customer Geography],
      Filter([Product].[Product].[Product], 
        GrowthFromStartToEnd > 
           0.25*([Measures].[Internet Sales Amount], 
             OpeningPeriod([Date].[Calendar].[Month], [Date].[Calendar].[Calendar Year].&[2002]))
      )
    )
  );

intersect part 1 and part 2

Just to prove the point, let’s use Intersect function. However, better approach would’ve been to use the Generate(Filter()), and combine the conditions inside Filter using AND. The following query shows final set of products by regions:

SELECT {} ON 0
, Intersect(
    ProductsThatGrewFasterThanRollingAverageMore6OrMoreTimes,
    ProductsThatGrewMoreThan25PctFromStartToEnd ) ON 1
FROM [Adventure Works]

Hopefully this exercise will convince Ward that MDX is pretty powerful, yet simple language which allows for complex analysis. But how long did it take to implement all this logic. I measured myself, and it only took 21 minutes of my time to write all of the MDX statements. However, this also included about 4 minutes to arbitrage dispute between my kids, so net spent time was 17 minutes. Factoring that I am probably faster at writing MDX than average, I estimate that it would take around 1 hour to somebody with “normal amount of MDX education”. Much better than “few months in BI tool” and also somewhat better than “few hours in nextanalytics”.

MDX and partitioning

mosha — Wed, 19 Dec 2007 04:21:00 GMT

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 scheme better.

Let's consider scenario where we need to determine the last date for which there is data in certain measure group. The example will be built around Adventure Works sample database, using Date dimension and Internet Sales Amount as a measure.

One more thing to note: while writing this post I used MDX Studio which simplifies many operations, so when below I say "look at the set", it means inspecting it in the Watch window of MDX Studio, when I say "clear the cache", it means using MDX Studio's cache clear button etc. The only other tool that needs to be run is Profiler, since AS traces are not yet integrated into MDX Studio, but the MDX Studio development team is working on this feature. Also, in order to be able to run MDX Script statements from within MDX Studio, I added "Cube=Adventure Works" in the Connection Properties field of the Connect dialog.

Due to limitations of sqlblog.com, in all examples below remove the space before the square braket in the [Date ] fragments

The most straightforward way to compute the last date is by using the following formula:

CREATE SET CurrentCube.LastDate as Tail(NonEmpty([Date ].[Date ].[Date ], [Measures].[Internet Sales Amount]), 1)

We can see that the result set will have a single member in it - July 31, 2004. But if we execute this statement over the clean cache, we can see the following events in the trace:

Query Begin    0 - MDXQuery    MDX Studio v0.2.6.0        
Progress Report Begin    14 - Query    Started reading data from the 'Internet_Sales_2001' partition.  
Progress Report Begin    14 - Query    Started reading data from the 'Internet_Sales_2002' partition.  
Progress Report Begin    14 - Query    Started reading data from the 'Internet_Sales_2003' partition.  
Progress Report End    14 - Query    Finished reading data from the 'Internet_Sales_2001' partition.   
Progress Report Begin    14 - Query    Started reading data from the 'Internet_Sales_2004' partition.  
Progress Report End    14 - Query    Finished reading data from the 'Internet_Sales_2002' partition.  
Progress Report End    14 - Query    Finished reading data from the 'Internet_Sales_2003' partition.  
Progress Report End    14 - Query    Finished reading data from the 'Internet_Sales_2004' partition.  
Query End    0 - MDXQuery

We see that all partitions got queried, even though the last non empty date is in 2004, and therefore it should've been enough to query just the Internet_Sales_2004 partition. Unfortunately, the query optimizer gets carried away in this example, decides to ignore the outer Tail(..., 1) and executes NonEmpty over all dates literally over all dates. So, we need a different strategy here. The second obvious strategy for finding last non empty date is the recursive approach - start from the last date in the dimension and move back by one date until we run into a date which has data. The formula for that is

CREATE
 MEMBER CurrentCube.LastDateIndex AS
   Iif(
    IsEmpty([Measures].[Internet Sales Amount]), 
    [Date ].[Calendar].PrevMember,
    Rank([Date ].[Calendar].CurrentMember, [Date ].[Calendar].[Date ])
   )
 SET LastDate AS [Date ].[Calendar].[Date ].Item((LastDateIndex, Tail([Date ].[Calendar].[Date ],1).Item(0))-1)

Here we implemented the recursive logic inside calculated member LastDateIndex, and then we position it on the last date (using Tail(1) over level of dates for that). The result is the same as before, but trace looks very different:

Query Begin	0 - MDXQuery	MDX Studio v0.2.6.0		
Query Subcube	2 - Non-cache data August 31, 2004	
Query Subcube	2 - Non-cache data August 30, 2004	
Query Subcube	2 - Non-cache data August 29, 2004	
Query Subcube	2 - Non-cache data August 28, 2004	
Query Subcube	2 - Non-cache data August 27, 2004	
Query Subcube	2 - Non-cache data August 26, 2004	
Query Subcube	2 - Non-cache data August 25, 2004	
Query Subcube	2 - Non-cache data August 24, 2004	
Query Subcube	2 - Non-cache data August 23, 2004	
Query Subcube	2 - Non-cache data August 22, 2004	
Query Subcube	2 - Non-cache data August 21, 2004	
Query Subcube	2 - Non-cache data August 20, 2004	
Query Subcube	2 - Non-cache data August 19, 2004	
Query Subcube	2 - Non-cache data August 18, 2004	
Query Subcube	2 - Non-cache data August 17, 2004	
Query Subcube	2 - Non-cache data August 16, 2004	
Query Subcube	2 - Non-cache data August 15, 2004	
Query Subcube	2 - Non-cache data August 14, 2004	
Query Subcube	2 - Non-cache data August 13, 2004	
Query Subcube	2 - Non-cache data August 12, 2004	
Query Subcube	2 - Non-cache data August 11, 2004	
Query Subcube	2 - Non-cache data August 10, 2004	
Query Subcube	2 - Non-cache data August 9, 2004	
Query Subcube	2 - Non-cache data August 8, 2004	
Query Subcube	2 - Non-cache data August 7, 2004	
Query Subcube	2 - Non-cache data August 6, 2004	
Query Subcube	2 - Non-cache data August 5, 2004	
Query Subcube	2 - Non-cache data August 4, 2004	
Query Subcube	2 - Non-cache data August 3, 2004	
Query Subcube	2 - Non-cache data August 2, 2004	
Query Subcube	2 - Non-cache data August 1, 2004	
Progress Report Begin	14 - Query	Started reading data from the 'Internet_Sales_2004' partition.
Progress Report End	14 - Query	Finished reading data from the 'Internet_Sales_2004' partition.
Query Subcube	2 - Non-cache data July 31, 2004	
Query End	0 - MDXQuery

Now we see lots of non-cached Query Subcube requests for the dates going from August 31, 2004 down to July 31, 2004. Interestingly, only the last one triggers reading of the partition, since for the others, partition autoslice automatically detects that they don't belong to any partition. So in certain sense this is a good result, since we only touched one partition, but in another sense, this query plan with lots of Query Subcube requests doesn't look good. We were lucky, that in Adventure Works there were only 31 days at the end of Date dimension without data, so recursion went 31 step only. But it is not uncommon to have Date dimensions looking several years ahead, in which case recursion could go on for thousands of steps, and overhead of thousands of Query Subcube requests could become substantial, not to mention the overhead of the depth of recursion.

This leads us to another approach. Instead of iterating by days, we can iterate by partitions. Since in Adventure Works partitioning scheme is by years, we will iterate by years, and then find the last non empty date within a year. The MDX that implements this approach is below:

CREATE
 MEMBER CurrentCube.LastYearIndex AS
  Iif(
   IsEmpty([Measures].[Internet Sales Amount]), 
   [Date ].[Calendar Year].PrevMember,
   Rank([Date ].[Calendar Year].CurrentMember, [Date ].[Calendar Year].[Calendar Year])
  )
 SET LastYear AS [Date ].[Calendar Year].[Calendar Year].Item((LastYearIndex, [Date ].[Calendar Year].LastChild)-1)
 SET LastDate AS Tail(NonEmpty([Date ].[Date ].[Date ].MEMBERS, (LastYear, [Measures].[Internet Sales Amount])), 1)

Again, this gives us the desired result, and now the trace looks like following:

Query Begin	0 - MDXQuery	MDX Studio v0.2.6.0
Progress Report Begin	14 - Query	Started reading data from the 'Internet_Sales_2004' partition.
Progress Report End	14 - Query	Finished reading data from the 'Internet_Sales_2004' partition.
Query Subcube	2 - Non-cache data
00000000,000,00000,00,000000000000100000,000000000000000000,000000000000000000,0000000000000000000000,000000000000000000000,00,10
Progress Report Begin	14 - Query	Started reading data from the 'Internet_Sales_2004' partition.
Progress Report End	14 - Query	Finished reading data from the 'Internet_Sales_2004' partition.
Query Subcube	2 - Non-cache data
00000000,000,00000,00,010000000000100000,000000000000000000,000000000000000000,0000000000000000000000,000000000000000000000,00,10
Query End	0 - MDXQuery

We eliminated lots of Query Subcube requests, but we still have two, and now each one triggers query of partition. Why there are two of them ? This is easily answered by inspecting the Query Subcube granularity bitmasks. We see that the difference between them is in the Date dimension. First Query Subcube doesn't have granularity on Date attribute, while the second one has. It is clear now, that the first Query Subcube request is triggered by IsEmpty([Measures].[Internet Sales Amount]) check inside Iif - because it happens at the Year granularity, and the second Query Subcube is due to NonEmpty over dates with the filter on the specific year.

We don't like the fact that we go to the partition twice, essentially for the same data but on different granularity. So we can do a little trick in MDX. Since we will have to go to the Date granularity within a year anyway, let's do it sooner rather than later - and expand granularity already inside Iif check. The resulting MDX will look like following:

CREATE
 MEMBER CurrentCube.LastYearIndex AS
  Iif(
   IsEmpty(Aggregate(Descendants([Date ].[Calendar], [Date ].[Calendar].[Date ]), [Measures].[Internet Sales Amount])),
   [Date ].[Calendar Year].PrevMember,
   Rank([Date ].[Calendar Year].CurrentMember, [Date ].[Calendar Year].[Calendar Year])
  )
 SET LastYear AS [Date ].[Calendar Year].[Calendar Year].Item((LastYearIndex, [Date ].[Calendar Year].LastChild)-1)
 SET LastDate AS Tail(NonEmpty([Date ].[Date ].[Date ].MEMBERS, (LastYear, [Measures].[Internet Sales Amount])), 1)

Let's see what we get in the trace now:

Query Begin	0 - MDXQuery	MDX Studio v0.2.6.0
Progress Report Begin	14 - Query	Started reading data from the 'Internet_Sales_2004' partition.
Progress Report End	14 - Query	Finished reading data from the 'Internet_Sales_2004' partition.
Query Subcube	2 - Non-cache data
00000000,000,00000,00,011010000001100000,000000000000000000,000000000000000000,0000000000000000000000,000000000000000000000,00,10
Query Subcube	1 - Cache data
00000000,000,00000,00,010000000000100000,000000000000000000,000000000000000000,0000000000000000000000,000000000000000000000,00,10
Query End	0 - MDXQuery

This is perfect - exactly what we wanted. The second Query Subcube which used to be Non-cache data, now turned into cached one, and there is only one query to only one partition going on.

This post was written as a result of discussion on sql.ru forum

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

mosha — Thu, 22 Nov 2007 08:23:00 GMT

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:

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)

mosha — Thu, 27 Sep 2007 02:38:24 GMT

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.