Udostępnij za pośrednictwem

Analysis Services Tabular Mode vs Multidimensional Mode

  • Artykuł

A new feature in SQL Server 2012 is the tabular models in Analysis Services, and after facing some issues when I was preparing a demo for a recent event I decided to finish some tests to compare the performance of Tabular Models vs multidimensional models.

First, what it is Analysis Services Tabular Mode. SQL Server includes not only the relation engine but also the analytical engine. Prior to SQL Server 2012, there was only one kind of analytical database in SQL Server (multidimensional database or cubes), now in SQL Server 2012 we have a new kind of analytical database engine, Analysis Services in tabular mode, the objective is similar to the multidimensional database, be able to answer questions about the data as fast as possible, however, the internal architecture and the language used (DAX instead of MDX) are different. Analysis Services in tabular mode holds all the information in memory in columnar storage (instead of the classic row based storage), this significantly improves query performance without requiring indexes or aggregations. For more details about tabular mode you can read the related ppt that you can found in https://blogs.technet.com/b/sql_pfe_latam/archive/2012/06/27/1-176-simposio-latinoamericano-de-sql-server.aspx        

In these scenarios, the data used was random and I run this test on a 16GB of RAM Laptop (not a high end Server), however it is useful to illustrate that we need to be careful before choosing one modelo r the other doing the appropriate proof of concepts.

Tabular mode has some advantages over multidimensional but it also has disadvantages.

Some of the advantages of tabular models are:

-          They are easier and faster to develop.

-          If you already have PowerPivot models in production is very easy to evolve these models to tabular models.

-          You can use PowerView on tabular models.

Some disadvantages can be seen in the results of my tests. The scenario consists of a 16.4 GB relational database with data compression containing 100 million rows with less than 10 columns. Here is some data about the size and behavior on both tabular models and multidimensional models on this database.

Característica

Tabular

Multidimensional

Processing time

More than 9 hours

20 minutes

Memory consumption

11 GB

1 GB

Database Size

4.5 GB

8.4 GB

Query 1

88 ms

94 ms

Query 2

334 ms

62 ms

Query 3

5033 ms

920 ms

The previous results don’t mean that you shouldn’t use tabular mode, but should evaluate if it is your best option. Depending on the data, the amount of dimensions, the complexity of measures, etc., the results can change.

Here are the print screens of the previous tests.

The relational database size (no data compression)

Tabular Mode Database size

Multidimensional Database Size

Queries response time in Tabular mode

Queries response time in multidimensional mode

 

“The opinions and views expressed in this blog are those of the author and do not necessarily state or reflect those of Microsoft”

Comments

  • Anonymous
    January 01, 2003
    You are Welcome, We are very excited to help.

  • Anonymous
    March 19, 2013
    Thanks a lot I really appreciate your post. i have been evaluating the choice between tabular and cube. i tried the tabular model and it just zaps my memory. Now i know i should just stick with my cube. thanks a lot

  • Anonymous
    April 11, 2013
    I think the demo is incomplete. Did you have distinct count measures ? This scenario is a good reason to use Tabular Modes.