Sunday, December 9, 2012

What's the future of SQL Server Analysis Services

There are now two main technologies to choose from when creating cubes in Microsoft's SQL Server 2012, the traditional multidimensional and the new tabular technology.  You really have to choose between these technologies as there is no easy way to switch between them and each one has its own server process.

I have written previously on how to choose between these (see ).  However, it is becoming more apparent to  me that Tabular is strategic and multidimensional is becoming legacy.  Not to say that Microsoft has whispered a single squeak on not supporting multidimensional in the future, but there are a few clues for us to see that Microsoft is not putting effort into this technology.    The multidimensional cubes have not changed much since SQL Server 2005.  This is through 3 SQL Server releases (2008, R2 and 2012).  Another big clue that has come to my attention is the survey where the SSAS team are asking for us to rate enhancements for the next version of SQL Server.  See http://www.instant.ly/s/Wqdj4mEAIAA.   This survey only mentions Tabular by name.  There are no questions that pertain to multidimensional.  My conclusion from this survey (and other hints) is that Microsoft are not planning on enhancing multidimensional.

What does this mean?  Does it mean that new enterprise developments should be in Tabular?   I feel that we should definitely consider Tabular for all new developments.  If it supports the business requirements, as well as multidimensional, then use it.  Multidimensional has a wider feature set, so it will not be uncommon that you will decide to stay with multidimensional.  Two features come to forefront

  1. Tabular will only work if there is sufficient memory for the entire cube in memory.  Else you won't be able to open the database.  
  2. Several features in multidimensional are not supported in Tabular.  For example Tabular does not support composite primary keys.
It will be very interesting to see if the missing features in Tabular are worked on the the next version of SQL Server.

By the way, I would love to be wrong in this assessment, and will be happy to blog a retraction when proved wrong.  There is a massive base of multidimensional cubes supporting enterprise applications today.


2 comments:

MattK said...

Others believe that MOLAP is being replaced too: http://www.dbms2.com/2012/11/05/do-you-need-an-analytic-rdbms/

Anonymous said...

Another much desired feature will to add mining models into the Tabular.