THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

The state of Multidimensional and Tabular adoption

SQL Server Analysis Services is a product that does not have its own product code, being sold as part of SQL Server. I think that also Microsoft does not know (and cannot know) the exact number of users. For this reason, it’s also more difficult to analyze the effective usage of the two different model types: Multidimensional and Tabular. Thus, only indirect observations are possible: books sold, web page reads, web searches, support calls, consulting engagements, and so on.

Accessing to public data is very hard. For example, it’s hard to discriminate between MDX and DAX web searches (Google Trends is an interesting tool for this kind of things). In fact, MDX is also the name of a car mode, and DAX is the main German Stock Index. As a book and blog author, I have some data that I can share.

I co-authored books that cover SSAS Multidimensional and we recently released a refresh of the Expert Cube Development with SSAS Multidimensional Models. The main change we had to do compared to the first edition published in 2009 was changing the screenshot. The average “life” of a book covering a software product is 2-3 years. Well, this book kept the same level of sales for 3 years, it decreased sales after the release of Analysis Services Tabular, and it increased sales with the release of the new edition.

expertcubesales

This can be a predictable behavior, but what is interesting is that the lifespan of this book is much longer than average (of other software product books) and the technology we talk about is not going to disappear in the market.

So what about SSAS Tabular? The book Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model has a shortest sales history, but it has a regular trend, with a slow decrease over time.

ssastabular-sales

The spike you see in the first quarter is because of the different distribution of the two publishers, so just consider the trend in the following quarters.

A common question I receive also in comments to my blog posts is about the future of Multidimensional and MDX. Well, I’m pretty much convinced that MDX will be like C++. Nowadays, many people use C# (myself included, and I have been using C++ for many years). But for certain applications, C++ is the only option. Moreover, MDX is used by other products (Pentaho, SAP HANA, and others), whereas by now DAX is used only by Microsoft products (Power Pivot and SSAS Tabular).

I think that we will not see a single language/technology replacing everything we are using now. There is an interesting movement towards “R”, but I see it as an additional tool for certain users and usages, and not as something that can replace existing technologies. It is interesting to consider the effects of integration of these technologies, at least from a Microsoft point of view. The recent article on SQLBI about AutoExist and Normalization is just an example of the work we have to do in order to understand how to get the best from this integration, hoping that Microsoft will continue investments on the server side.

I do not expect major changes in MDX, but I would prioritize a better integration between MDX and DAX in both Multidimensional and Tabular. For example, I would love being able to write a DAX expression for an MDX measure in a Multidimensional model. Imagine the power of writing a leaf-level calculation in DAX, propagating its effects in attribute hierarchies defined in a cube. Very powerful, not easy to master, but really interesting for certain conditions (that I’m not inventing – I’ve actually seen customers that would have used it if it was possible!).

What about DAX? It’s an interesting language, much easier to learn than MDX for people with a SQL language background. I think it will be improved, we’re still working with a version 1 (remember MDX evolved between 2009 and 2005, adding SCOPE and other features), the only concern is that it is a Microsoft language, whereas MDX has been adopted by a number of companies (and we have many dialects – so don’t expect to see all MDX formulas working the same in all MDX products…).

So, when I say that I don’t see the need of migrating Multidimensional models to Tabular or other technologies, I have the support of the market adoption, which means there will be tools, support and knowledge. Can Microsoft do more and better for keeping this technology alive? Yes, definitely. Is the Multidimensional world lacking features that would be useful? Yes, but not in the measure new features are required by Tabular now. The next major release of Analysis Services will be important to answer to these questions.

Published Thursday, October 23, 2014 9:24 AM by Marco Russo (SQLBI)
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Greg Low said:

Hi Marco,

One aspect that isn't discussed here, yet I think is critical, is the licensing. It's pointless having Microsoft pushing tabular (a direction that I fully support), yet only multidimensional is available in standard edition. Their licensing policy is not supporting the desired direction.

Regards,

Greg

October 25, 2014 5:47 AM
 

Marco Russo (SQLBI) said:

Greg, I agree that this is a nonsense to me, too.

By including Multidimensional and not Tabular in the Standard version, Microsoft is actually incentivating new projects on Multidimensional for small and medium companies, or for small projects. Who are those that, ironically, would benefit more by the adoption of Tabular in terms of development effort.

I understand the logic behind this decision: increasing revenues. And I actually know companies who moved to Business Intelligence instead of Standard because they wanted to use Tabular. But how many companies would have adopted Tabular instead of Multidimensional if the Standard edition would have had a Tabular version with a limit in database size?

Considering Power Pivot is free for an Excel user, I think that a database limit between 5GB and 10GB in Tabular for Standard edition (or just no partitioning, just as Multidimensional in Standard) would increase the adoption of Tabular, and would incentivate an upgrade to Business Intelligence or Enterprise as the database grows up.

Losing 1 USD in one year might correspond to selling 10 USD more in 3-4 years.

Thanks for the feedback, it's a consideration it worths to share.

October 25, 2014 6:37 AM
 

Robert Greig said:

In my opinion using DAX is quite a painful experience which reflects the lack of focus (and investment) from Microsoft. It really is a v1.0 product and I have no idea when it will improve - has Microsoft shared the road map anywhere? Here are the reasons why I find DAX frustrating:

* Developer experience is appalling for writing and debugging DAX

* Documentation is pretty poor - your book is absolutely essential reading!

* Tuning is messy - no nice query plans that are easy to understand

* I disagree that DAX is easy to learn - the learning curve quicky ramps up for things like semi-additive measures and many to many relationships. These are things that are easy in tools like Qlikview.

It seems that Microsoft is more interested in investing in things that enable nice conference demos such as PowerMap than addressing the basic issues in the core platform.

October 26, 2014 12:07 PM
 

Marco Russo (SQLBI) said:

Robert, Microsoft is investing but the speed of new releases is not what we would like to have!

For developer experience... next release of DAX Studio should give you some improvements.

Documentation: in reality I don't think MDX is much better - which doesn't mean the one about DAX is good, I know.

Easiness - I Always compare DAX to MDX and, in this regard, I still think DAX is easier than MDX. Semi-Additive measures are not that bad, whereas many-to-many might be a little bit harder (but the pattern at the end of this article is very easy to use: http://www.sqlbi.com/articles/optimize-many-to-many-calculation-in-dax-with-summarize-and-cross-table-filtering/ - but I agree that M2M is completely absent from MS documentation, it's only described in blogs, books and whitepapers)

I hope MS will make some announcements at PASS Summit in November...

October 26, 2014 12:42 PM
 

Chris said:

The problem I'm having with MSBI right now is that the products being developed are just a long way away from being able to support a large majority of customer needs, while the products that are very robust (MD+RS) haven't been improved in quite some time.

I can pretty much produce anything a customer wants with MD & RS, but I'm very limited when using Tab & PowerView... I'd love to see MS get some new features into MD and give RS a face lift (design trends have changed just a bit in the last 10 years).

October 27, 2014 1:47 AM
 

Marco Russo (SQLBI) said:

Chris, I agree that MD and RS would need some love by MS!

October 27, 2014 2:30 AM
 

chiburi said:

It's slightly ironic that one of the reason I heard cited many times for DAX was the MDX was too difficult to learn. Know there are just 2 languages that equally as complex.

October 28, 2014 9:10 AM
 

Marco Russo (SQLBI) said:

Chiburi,

I know MDX and DAX equally well, and I think DAX is one order of magnitude easier than MDX. This does not mean it is simple like Excel formula Language, it still has an higher level of abstraction that is important especially for performance.

From this point of view, DAX has some more abstractions than SQL, but SQL can be considered as difficult as DAX when you combine correlated subqueries and/or CTE.

Just my 2 cents.

October 28, 2014 10:06 AM
 

Suhas said:

Hi Marco,

Thanks for excellent blog, about state of Multidimensional and Tabular adoption. I like to write MDX Query than DAX.

Let's hope from MS, some good news features about Multidimensional and Tabular.

Thanks,

Suhas

October 30, 2014 4:11 AM
 

Marco Russo (SQLBI) said:

Thanks for the feedback, Suhas!

October 30, 2014 4:18 AM
 

Mike Honey said:

I really agree with Chris on MD + RS, but given there were no significant enhancements to either since SQL 2008 R2, I'm not holding my breath.

On the topic of languages: I've written a pile of MDX (for MD cube calc scripts) and learned the hard way to avoid it as much as possible. As a language I think it's inconsistent, half-finished and very poorly documented. I suspect only one guy at Microsoft ever understood it and he left years ago.

DAX so far has been much easier and faster to learn, read, write, debug and reuse.

So where does that leave me? Prototype / PoC in Power Pivot, then rebuild the serious solution in MD+RS, I guess ...

October 30, 2014 9:27 PM
 

Marco Russo (SQLBI) said:

Hi Mike, thanks for the feedback. Some MS investement in Corporate BI stuff (SSRS, support for team development in SSAS - both MD and Tab - and other lifting here and there) would be important. I hope comments like the ones in this page will be helpful to support our requests at next MVP and PASS Summit.

October 30, 2014 9:35 PM
 

Alejandro Leguizamo said:

Well, the discussion about overall Microsoft strategy is a discussion worth a thousand beers.

I know for a fact, many consultants (and less openly, Many Microsoft Consultants) , avoid Tabular like the plague: After all, Power Pivot users don´t pay 100K in licenses as companies would do, if Microsoft were to invest in the BI Corporate space.

Many colleagues working in corporate life, wouldn´t even want to hear about the Tabular world: And the reason is simply: There is absolutely no feature parity with Multidimensional.

Indeed , as a previous commenter said before , Microsoft is focused in Bi-Demo-Building instead of delivering enterprise BI Solutions.

November 4, 2014 5:11 PM
 

Marco Russo (SQLBI) said:

Hi Alejandro, good to hear from you!

Well, Tabular and Power pivot are two different things from a licensing point of view. Multidimensional is available on Standard version, whereas Tabular requires a Business Intelligence edition, at least.

That said, I know very well features missing from Tabular, making it not suitable for certain requirements. At the same time Tabular provides much better performance and lower TCO than Multidimensional to keep a constant level of performance.

In my opinion, a big obstacle to Tabular adotion is not the lack of feature to end users, but the lack of support in many areas of development Tools.

However, good to hear different voices. Thanks!

November 4, 2014 5:28 PM
 

Sanya said:

Hi Marco

I am getting this error while processing the cube.

please advice..

The attribute key can not be found when processing table 'table name' column 'columnname1' value '35' the attribute 'columnname2'

The attribute key was converted to unknown member because the attribute key was not found

Thanks for your help.

April 17, 2015 1:12 AM
 

Marco Russo (SQLBI) said:

Sanya, as the error says - the fact table has a key to a dimension that does not exists in the dimension table.

May 3, 2015 8:27 PM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Privacy Statement