Our Blog

where we write about the things we love



SQL Server 2012 Analysis Services Enhancements

The use of OLAP capabilities by Microsoft customers is among the highest when compared to other vendors, largely due to the success and adoption of Microsoft SQL Server Analysis Services, bundled with Microsoft SQL Server. With the release of SQL Server 2012, Microsoft is adding even more benefits for OLAP users.

In addition to the Multidimensional model that has been available in previous releases of SQL Server, SQL Server 2012 provides the ability to develop solutions using the Tabular model, a relational model which enables in-memory analytics.

With the addition of the Tabular model, the Unified Dimensional Model that was introduced in SQL Server 2005 is now replaced by the Business Intelligence Semantic Model (BISM), which had been labelled as “one model for all user experiences – reporting, analytics, scorecards, dashboards and custom applications.”

Users now have the choice of developing solutions in either the multidimensional model, which is great for analysing data, but can be complicated to set up initially; or the tabular model, which is easier to understand because of its relational model. 


An overview of the Tabular model

The concepts behind the tabular model were originally introduced in PowerPivot, a free add-in for Microsoft Excel 2010.

With the release of SQL Server 2012, the model has been further enhanced, and users can now develop tabular models using SQL Server Data Tools (which replaces the Business Intelligence Development Studio previous versions of SQL Server) for consumption by OLAP client tools such as Excel and PowerView, another SQL Server 2012 addition that can only report off tabular models at this stage.

Tabular models use the xVelocity engine, previously known as VertiPaq, which is an in-memory column store engine which can store billions of rows of data in memory and produce rapid response times and high data compression rates.

Tabular models also bridge the gap between business users and developers. Business users can develop a model in PowerPivot which can then become the basis of an Analysis Services tabular model application – they don’t need to resort to software development, which may have been the case in the past. Users that are familiar with relational database structures will also find developing with the tabular models much easier than the multidimensional model

Although it is still strongly recommended to have a data warehouse as a source for tabular model cubes, the tabular model can use a variety of data sources including tables, Microsoft Excel spread sheets, SQL Azure data and even multidimensional cubes. This makes it ideal for fast prototyping exercises but also enables users to enrich data in the data warehouse using data from external sources. There is also no need to first perform ETL (Extract, Transform and Load) procedures on these sources before using them, which can often be a lengthy and time consuming process. 


Multidimensional model evolution

In SQL Server 2012, there aren’t any major changes or additions to functionality in the multidimensional model; the improvements that have been introduced are mainly focused on improving performance, reliability and scalability. Development of projects is now performed using the SQL Server Data Tools, which are based on Visual Studio 2010 and replace the Business Intelligence Development Studio, provided in earlier versions of SQL Server.

Comparing the two models, the tabular model should not be seen as a replacement of the multidimensional model, but rather as an addition to an already powerful toolset. Each model has its own advantages and it will be up to the business to decide which model suits their needs – the best solution might even be a combination of both models.

By simplifying the way businesses are able to access, model and analyse data in SQL Server 2012 it brings users closer to a self service BI model in which they are able to get the right information at the right time. With the increased focus on data within organisations of all sizes, these tools empower users – and their organisations – to better understand the data with which they work, and derive even more value from their software investments.

Posted by: Dylan Venter, Senior Consultant, Enterprise Applications | 14 June 2012

Tags: Business Intelligence, SQL Server, SQL Server 2012

Blog archive

Stay up to date with all insights from the Intergen blog