MDX Formatting functionality debuted in MDX Studio 0.2.8, and judging by the amount of feedback I got on it – it became the most popular feature of MDX Studio ever ! Couple of people mentioned that for SQL formatting/beautifying needs, they would go online to sites like www.sqlhere.com, which often do reasonable job for MDX as well. So this got me thinking that I could put MDX formatting functionality online as well. With little refactoring of MDX Studio code I was able to hack my first ever ASP.NET application, which became MDX Studio Online Edition. This was a significant milestone, so I promoted version number to 0.3, and also promoted status from Alpha to Beta.

The MDX Studio Online can be accessed at http://mdx.mosha.com

In addition to “Format MDX” button, it also features “Parse” button, which does exactly the same as desktop version – it shows the MDX parse tree. Just like in desktop version, it can Format and Parse both queries (i.e. SELECT statements) and MDX Scripts (i.e. CREATE MEMBER/SET, SCOPE, assignments etc). The biggest known feature which is lacking from MDX Studio formatting – is support for comments. Currently it just ignores the comments instead of carrying them forward into formatted statements. I still didn’t figured out how to do it, but this issue tops my todo list.

Update: Big thanks to Nick Medveditskov who also has written his own MDX Formatter application – he told me the trick he used to preserve comments in formatted MDX, and this functionality is now implemented in MDX Studio deployed online.

Being an online application – you can use it right away, without any installation, so it can be especially handful for consultants working at customer site, where software installations are not allowed. Another great benefit of it being online application, is that everybody is always on the latest code, so all the hotfixes and version upgrades go live for everybody immediately (well the downside is that if I break something, then everybody is broken right away and there is no rollback, but this would never happen to me, of course, would it ?)

I would like to thanks several people who did smoke testing for this application before it went public and provided lots of very valuable feedback and advice, people from all around the world – Vladimir Stepa (Germany), Vidas Matelis (Canada), Greg Galloway (US), Darren Gosbell (Australia), Ajit Singh (India) and Chris Webb (UK). After all of them gave green light to the current version, I feel OK now to release it to public.

As usual please send your feedback about the tool to the MDX Studio forum at http://www.ssas-info.com/forum/MDXStudio

Darren Gosbell put up a blog post about MDX Formatting, where he describes how he prefers to format MDX to look nice on the screen. So I thought – why to leave to a human something that machine can do. I decided to implement these rules (with some modifications) into MDX Studio. It became MDX Studio 0.2.8 release, available immediately for download at http://cid-74f04d1ea28ece4e.skydrive.live.com/browse.aspx/MDXStudio/v0.2.8

I only had couple of hours to work on it, so it is rough on the edges, and I will appreciate comments in the MDX Studio forum at http://www.ssas-info.com/forum/MDXStudio. But basic staff like calculated members, named sets, subselects etc should work. (One known exception is set aliases).

The functionality is accessible through MDX –> Format menu. Here is example from Darren’s blog

with member measures.ptd as 'sum(periodstodate([Date].[Calendar].[Month],
[Date].[Calendar].currentmember),[Measures].[Sales Amount] )',format_string = "currency"
select   {[Measures].[Measures].[Sales Amount] ,measures.ptd} on 0,
{[Date].[Calendar].[Month].&[2003]&[2].children} on 1 from [Adventure Works] where 
([Product].[Product Categories].[Category].&[1])

And here is how it looks after autoformatting:

WITH
  MEMBER measures.ptd AS 
    sum(
      periodstodate(
        [Date].[Calendar].[Month],
        [Date].[Calendar].currentmember),
      [Measures].[Sales Amount]) 
    
    , format_string = "currency"
SELECT
  {
    [Measures].[Measures].[Sales Amount],
    measures.ptd
  } ON 0,
  [Date].[Calendar].[Month].&[2003]&[2].children ON 1
FROM [Adventure Works]
WHERE 
  [Product].[Product Categories].[Category].&[1]

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.

My post “MDX answer to NextAnalytics challenge” triggered some very active discussion, and I would like to address some of the comments

Chris Webb said:
I've done a fair amount of teaching MDX and the truth is that people do find even the basics very difficult to understand, but my feeling is that the problem people have with MDX is not the language as such but thinking a) multidimensionally and b) in sets. The question is then, does Ward's new language solve either of these problems? Or has he reinvented the wheel?

I agree with the a) part, but not with the b) part. Thinking multidimensional is hard and take some time to get used to. However thinking in sets is not hard at all. SQL language is mostly based on relational algebra and uses sets extensively. Practitioners don’t find sets difficult.

Thomas Ivarsson said:
Can you publish the entire script at the end of this blog post? My conclusion is that we need better clients that can create these analytics sets at run time. End users will never do this and it is impossible to create all sets that the business users would like to have. It will fill up a cube in no time.

I am pessimistic that we will ever see such sophisticated clients. If they were in demand, we would’ve already had them. I am afraid the users will have to code such analytics either using MDX or NextAnalytics script or something else. End users don’t have any problem building such sets in Excel, so maybe Excel is our hope ?

Here is the entire script:

// Nextanalytics challenge

// 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]);
  
// calcualte the growth of that 
CREATE GrowthOfRolling6MonthAverage = Rolling6MonthAverage - (Rolling6MonthAverage, [Date].[Month Name].PrevMember);

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

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

CREATE SET ProductsThatGrewFasterThanRollingAverageMore6OrMoreTimes AS
 Generate([Customer].[Customer Geography].[Country], 
    CrossJoin([Customer].[Customer Geography],
      Filter(
        [Product].[Product].[Product], 
        HowManyTimeGrowthExceededRollingAverage >= 6
      )
    )
  );

SELECT {} ON 0
, ProductsThatGrewFasterThanRollingAverageMore6OrMoreTimes ON 1
FROM [Adventure Works]

// 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]));

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]))
      )
    )
  );

SELECT {} ON 0
, ProductsThatGrewMoreThan25PctFromStartToEnd ON 1
FROM [Adventure Works]

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

Miky Schreiber said:
I agree with Thomas. The question is how difficult it is to the end user. While the regular end user will never write MDX or NextAnalytics script, the advanced user will. Now, the question is what will be more simple for him - MDX or NextAnalytics script. 

Valid point. But I don’t think there is an absolute measure of “what is simpler” – practitioners will tend to use the tool that they are more familiar with. MDX is probably more popular than nextanalytics, but SQL is way more popular than either one of these. And Excel will easily rival SQL in familiarity. On the other hand, developers will want to use “real” programming language. And the question of “what is simpler” will raise there as well. While functional languages like F# are clearly more suited for such tasks, more people are familiar with conventional languages like C#.

Update: Ward published the entire nextanalytics script for this problem, so constrast and compare it yourself: http://www.nextanalytics.com/public/Examples/MDX-Challenge.html

And finally Ward wrote long comment, from which I will cite selectively

Ward Yaternick said: 

I also don’t agree with your implicit assumption that the cube and other infrastructure parts are insignificant.   To put a cube together from scratch and maintain it is a big decision and cost.

…

Therefore, I think you have conclusively proved that (1) if the cube exists and (2) someone can author MDX then MDX does deliver on complex analytics.

I do agree with that. I wasn’t trying to say that setting up a cube was trivial. However, if the cube was set up for some particular purpose, it is very easy to extend it with MDX calculations to get additional value out of it.

nextanalytics can help in these situations because the data can come directly from SQL or CSV files and the incremental IT overhead behind an analytic is very low.  You can feasibly write an analytic like what you did with MDX, run it against a CSV file or a simple SQL query, and then throw it away or keep it (all contained as lines in single text file) in a folder somewhere.

This certainly looks like a more agile way of doing things – quickly throw small script together, run it and move on. I think this approach will be attractive to lots of users – from small IT shops to independent users in bigger organizations. This is at odds, however, with classic methodology of building centralized data warehouse, one version of truth and all that. It doesn’t make it less viable, of course, it is just a tradeoff that someone will have to make – tradeoff between chaos and control, or, in Eric Raymond’s words, between bazaar and cathedral.

Another situation in which a cube isn’t ideal are when ISVs and consultants want to integrate analytics into their applications but they need to minimize the number of other vendor’s licenses and reduce the complexity and cost of the installed products.   This priority is the same when an IT budget is under constraint.

I think this is somewhat weak argument, because it implies that both complexity and pricing of NextAnalytics is below this of other products, like Microsoft Analysis Services. But this is definitely something that practitioners will need to evaluate and compare.

Sometimes, the analytic data processing needs to run on a unix box against something like MySQL or Oracle i.e. where Microsoft OLAP isn’t available 

Just wanted to note, that even though data could be in Unix box, in Oracle or MySQL, Microsoft OLAP doesn’t have problem connecting to it. Oracle is fully supported data source, and many people were successful reading data from MySQL too.

Perhaps they need an Open Source client  to make integration easier with their dashboard or portal.  For this reason, they can’t use any of the popular OLAP Clients 

I didn’t quite get this. There are plenty of Open Source clients which connect just fine to Microsoft Analysis Services (usually through XMLA, but not only). Since you mentioned Pentaho earlier, I wanted to note that Pentaho Analysis Views are based on JPivot, which works fine with Microsoft OLAP. There are many other open source clients for Microsoft OLAP as well.

All this lands us where nextanalytics services it’s niches.  By now I hope you can see our positioning. We’re not competing with MDX especially in the conditions you wrote about.

Yes, I think I see the positioning, and I believe that both your product and your approach have good potential, regardless of what MDX can and cannot do. And I actually think that it does compete, not just with MDX, but with OLAP and with Data Warehousing in general. And this is a good thing. Competition is healthy, it will force everybody to improve their products.

Update: Nextanalytics replied at http://www.nextanalytics.com/public/Examples/MDX-Challenge.html

We usually treat caching system of Analysis Services as a black box, trusting that it will do the "right thing" to optimize the execution. And this is how things should be in the ideal world, caching system should be completely transparent to the end user. However, even though MDX query optimizer and caching system are very sophisticated, they are not perfect. In this article we will see how with very simple MDX rewrites, we can take better advantage of caching, and increase performance of MDX calculations significantly.

(In order to follow examples in this article, you will need to use MDX Studio tool)

As an example we will use very simple statistical analysis of the data, where we would apply "Three sigma rule" to find outliers across combination of attributes. It is based on the fact that for data which conforms normal distribution almost all of the values (99.7% of them) will lay within 3 standard deviations of the mean. So the values which are farther from the mean by more than 3 standard deviations can be considered outliers. (There are of course more advanced methods to find outliers in the data, but for the purpose of this article this is representative enough).

We will start with the analysis of Internet Sales by day inside Adventure Works cube. The MDX to find out "farther than three sigma" days seems straightforward

with 
 member sales_avg   as Avg  ([Date].[Date].[Date], [Internet Sales Amount]), format_string = 'currency'
 member sales_stdev as StDev([Date].[Date].[Date], [Internet Sales Amount]), format_string = 'currency'
select { [Internet Sales Amount] } on 0
, filter([Date].[Date].[Date], abs([Internet Sales Amount]-sales_avg) > 3*sales_stdev) on 1
from [Adventure Works]

If we execute this query we will get the list of 10 outlier dates. But the query takes 7.5 seconds to execute. This is horrible ! 7.5 seconds to go through 1158 members seem completely unreasonable. To get a better idea about what's going on, let's take a look at some execution statistics collected by MDX Studio:

Time             : 7 sec 417 ms
Calc covers      : 1164
Cells calculated : 1344448
Sonar subcubes   : 3
SE queries       : 1159
Cache hits       : 1159
Cache misses     : 2
Cache inserts    : 2
Cache lookups    : 1161

Now we can understand what was going on here. For each one of these 1158 members, the engine recalculated both average and standard deviation, even though they were exactly the same at each point (it is easy to verify through debugger in MDX Studio). The problem is that the engine didn't figure out itself that average and standard deviation were the same for all dates, so we need to help it a little bit. We can redefine sales_avg as a calculation which goes up to All member in Date attribute. This way regardless at which date we are looking right now, the cell coordinate will shift to the All member, and will be computed only once, because all the other times it is referenced it can be answered from FE cache. Calculation rewritten with this trick in mind will look the following:

with 
 member sales_avg_   as Avg  ([Date].[Date].[Date], [Internet Sales Amount]), format_string = 'currency'
 member sales_stdev_ as StDev([Date].[Date].[Date], [Internet Sales Amount]), format_string = 'currency'
 member sales_avg as ([Date].[Date].[All Periods],sales_avg_), format_string = 'currency'
 member sales_stdev as ([Date].[Date].[All Periods],sales_stdev_), format_string = 'currency'
select { [Internet Sales Amount] } on 0
, filter([Date].[Date].[Date], abs([Internet Sales Amount]-sales_avg) > 3*sales_stdev) on 1
from [Adventure Works]

If we execute it now, it is done in mere 0.1 fraction of second. The MDX Studio stats look like following

Time             : 109 ms
Calc covers      : 9
Cells calculated : 4642
Sonar subcubes   : 3
SE queries       : 2
Cache hits       : 4
Cache misses     : 2
Cache inserts    : 2
Cache lookups    : 6

This is much better. Instead of SE query per cell, we got only 2 SE queries (one for axis and one for avg/stdev calculation). There are also significantly less cells calculated, since both average and standard deviation are calculated only once now and served from the cache.

Let's make the problem a little bit complex now. We computed statistics across all 4 years, and discovered that all the outlier dates were in 2004. This is probably attributed to the growth over time. So let's compute statistics differently now, instead of computing them for all dates in 4 years, let's compute them within each month, and find outliers within the month. The straightforward approach to this can be coded in MDX as following.

with 
 member sales_avg   as Avg  ([Date].[Calendar].Parent.children, [Internet Sales Amount]), format_string = 'currency'
 member sales_stdev as StDev([Date].[Calendar].Parent.children, [Internet Sales Amount]), format_string = 'currency'
select { [Internet Sales Amount], sales_avg, sales_stdev } on 0
, filter([Date].[Date].[Date], abs([Internet Sales Amount]-sales_avg) > 3*sales_stdev) on 1
from [Adventure Works]

Executing this query now finds two outliers:

And the MDX Studio stats will be

Time             : 704 ms
Calc covers      : 1206
Cells calculated : 38792
Sonar subcubes   : 41
SE queries       : 1198
Cache hits       : 1198
Cache misses     : 2
Cache inserts    : 2
Cache lookups    : 1200

Again, the stats don't look good. Too many SE queries, too many cells calculated. How can we apply the same technique as before ? Moving coordinate to the All dates will be incorrect now, because average and standard deviations are now different for every month. Therefore we need to move coordinates to the month level. The most natural way of doing it is simple move call to the Parent function from within the Avg/Stdev calls into the tuple coordinate shift transformation. Here is how the resulting query will look like:

with 
 member sales_avg_   as Avg  ([Date].[Calendar].children, [Internet Sales Amount]), format_string = 'currency'
 member sales_stdev_ as StDev([Date].[Calendar].children, [Internet Sales Amount]), format_string = 'currency'
 member sales_avg    as ([Date].[Calendar].Parent,sales_avg_), format_string = 'currency'
 member sales_stdev  as ([Date].[Calendar].Parent,sales_stdev_), format_string = 'currency'
select { [Internet Sales Amount], sales_avg, sales_stdev } on 0
, filter([Date].[Date].[Date], abs([Internet Sales Amount]-sales_avg) > 3*sales_stdev) on 1
from [Adventure Works]

The stats from this rewrite will be

Time             : 203 ms
Calc covers      : 162
Cells calculated : 6954
Sonar subcubes   : 117
SE queries       : 78
Cache hits       : 78
Cache misses     : 2
Cache inserts    : 2
Cache lookups    : 80

Which is much more reasonable result.