SSAS: Difference bewteen Multidimensional and Tabular Models

This article is copied from http://blog.aditi.com/data/choosing-between-analysis-services-multidimensional-and-tabular-models-part-1/

SQL Server 2012 introduced a new concept called Business Intelligence Semantic Model (BISM). In this 3 part series, we will take a look at this new concept, pros and cons of traditional multidimensional and new tabular models, considerations, recommendations, best practices, and few tips and tricks.

In this first part, we will take a look at what BISM is all about and get an initial understanding of this whole new concept.

Prior to SQL Server 2012, we had the concept of Unified Dimensional Model (UDM) for creating the multidimensional structures (OLAP Cubes) and data mining models. Microsoft introduced a new in-memory technology, with SQL Server 2008 R2, called “xVelocity” (Formerly “VertiPaq”). This technology was initially implemented with PowerPivot. With SQL Server 2012, Microsoft introduced BISM, which brings the capabilities of traditional UDM and the new xVelocity under one umbrella. BISM is implemented in the following formats:

• Traditional Multidimensional & Data Mining Models (Popularly known as SSAS Cubes)
• Analysis Services Tabular Models
• PowerPivot for Excel/SharePoint

Below is the architecture of BISM:

Source: SSAS & PowerPivot Team Blog

As we can see from the above architecture, BISM caters to the following types of BI requirements:

• Personal BI (PowerPivot for Excel)
• Team BI (PowerPivot for SharePoint)
• Corporate BI (SSAS Multidimensional/Tabular Models)

Here are the highlights of each of the three implementations of BISM:

SSAS Multidimensional and Data Mining Models:

• Uses Multidimensional (Dimensional) Modeling approach with Facts and Dimensions.
• Uses different storage modes – MOLAP, ROLAP, and HOLAP.
• Data is typically persisted on disk.
• Developed in SQL Server Data Tools (Visual Studio).

SSAS Tabular Models:

• Uses Relational Modeling approach with Tables and Relationships.
• Uses xVelocity (VertiPaq) engine for data storage and calculations.
• Data is typically persisted in memory (default). DirectQuery mode allows querying the data directly from underlying data source (More Information).
• This is basically a server-side version of PowerPivot.
• Developed in SQL Server Data Tools (Visual Studio).

PowerPivot:

• Uses xVelocity (VertiPaq) engine within Excel and/or SharePoint.
• PowerPivot workbooks on SharePoint require Excel Services.
• Data is typically persisted in memory.
• Developed in Excel.

In the next part of this series, we will take a look at the similarities and differences between the above implementations of BISM.

=================================================================

In the previous part of this series, we saw what Business Intelligence Semantic Model (BISM) is and what are its different implementations and the highlights of each of the implementations. In this part of the series, we will compare the three implementations of BISM based on various parameters.

Below is a comparison of the three implementations along on some of the most important and common areas:

The comparison matrix above can be really helpful for deciding which implementation to choose from, be it multidimensional, tabular, or PowerPivot.

In the next and last part of this series, I will look at the considerations, recommendations, best practices and a few tips and tricks.

=================================================================

In the previous part of this series, we saw the comparison between the three implementations of the Business Intelligence Semantic Model (BISM). In this 3^rd and last part of this series, we will look at the considerations, recommendations, best practices, and a few tips and tricks.

 

Considerations:

Here are few considerations while choosing between Multidimensional and Tabular Models. Use these as guidelines while choosing between the different implementations of BISM apart from the comparison matrix in the previous article.

• SSAS Tabular Model feature is available only in Business Intelligence and Enterprise editions of SQL Server 2012.
• An SSAS instance can be either in Multidimensional or Tabular modes but not both at the same time. If you have both Multidimensional and Tabular models in your organization, then you need to install two different instances of SSAS – One in Multidimensional (Traditional OLAP) mode and another one in Tabular mode.
• Both Multidimensional and Tabular Models are different and once the development has been started, one cannot switch from one model to another model.
• There is no straight forward way to transfer/sync data between Multidimensional and Tabular Models.
• PowerPivot has a limitation of 2GB on the workbook primarily due to the limitation enforced by SharePoint file upload size when the PowerPivot for Excel workbooks are uploaded to SharePoint.
• Tabular models use DAX for Calculations and Querying, which is very easy to learn and implement compared to MDX.

 

Recommendations:

Here are few recommendations on choosing between Multidimensional and Tabular models.

SSAS Multidimensional Model: Use Analysis Services Multidimensional and Data Mining Models if any of the following, but not limited to, criteria are met:

• You have large amount of data with complex requirements.
• You need the data mining capabilities.
• You need features like Actions, Translations etc.
• You need security at the most granular level possible (Cell level security).

SSAS Tabular Model: Use Analysis Services Tabular Models if any of the following, but not limited to, criteria are met:

• You have a short development cycle. Want to ship something really quick and get a feedback from the field (end users).
• Your data model is relatively simple. Also, the tables do not necessarily need to be Facts and Dimensions.
• End users are querying large amounts of detailed data and query performance is a critical factor (Though this can be achieved to a certain extent using ROLAP storage mode in multidimensional models but since Tabular Models work in-memory, better speed and performance can be achieved using Tabular Models).
• If the data is huge and cannot fit into memory, consider using the DirectQuery Mode, which is equivalent to ROLAP storage mode in traditional multidimensional models. With DirectQuery mode, data is queried directly from underlying relational database every time it is accessed. However, DirectQuery mode has various limitations like underlying source can be either SQL Server or SQL Server PDW (Parallel Data Warehouse) etc. More Information on the limitations of DirectQuery mode.

PowerPivot: Use PowerPivot if any of the following, but not limited to, criteria are met:

• The size of data that you need to work with is less than 2GB. If you have started building a PowerPivot model and after a period of time, your data grows beyond 2GB then you can build a SSAS Tabular Model by importing PowerPivot into SSAS Tabular (Refer this MSDN KB Article for more information: http://msdn.microsoft.com/en-us/library/gg492155.aspx).
• You do not have SQL Server license but still want to be able to build tabular models with smaller datasets. In this scenario, you can go with PowerPivot for excel which is a free add-in for excel. However, if you want collaboration, like in SharePoint, then you need to have SharePoint license (with additional components like Excel Services).

 

Best Practices:

Following are some of the best practices while working with Tabular Models.

• Hide the Surrogate Key columns from client tools (Set the “Hide from Client Tools” in Tabular & PowerPivot models).
• Hide columns which are part of user-defined hierarchies.
• Do not bring the metadata columns like Creation date of the record, Modification date of the record etc. unless there is any very specific reporting requirement, which needs those columns.
• Hide columns used in calculation of Measures like Reseller Sales Amount is derived from Sum of Sales Amount, and hence Sales Amount can be hidden form client tools.
• While developing Tabular Model in SSAS, try to play with it in Excel at regular intervals. This will give a feel of how the solution, that is being built, will look like for the end users.
• Mark the Date Dimension (Table) as Date Type. This will help in date related calculations like YTD, MTD etc.

 

Tips and Tricks:

While working with Tabular Models in SQL Server Data Tools (SSDT), whenever any change is made in the model, it takes a while for the change to be applied to the workspace tabular database and the screen gets locked during this interval without allowing the user to perform any other action. This is somewhat a limitation and consumes more time in building the model. Though we cannot do anything much about this as this is by design, here are few tips and tricks to do things smartly and save time to a certain extent.

• While hiding columns from the client tools, if you need to hide multiple columns from client tools, select multiple columns by holding “Ctrl” key and then hide them all in one go (This can be done in Diagram View).
• While creating a hierarchy, select all the columns which need to be part of the hierarchy and then select “Create Hierarchy”. Later these columns can be re-arranged in the hierarchy if required.
• Before adding calculated columns, set the “Calculation Options” to “Manual Calculation” (SQL Server Data Tools –> Model –> Calculation Options –> Manual Calculation). After the calculated columns are added, click on “Calculate Now” (SQL Server Data Tools –> Model –> Calculate Now)

This concludes the 3 part series on BISM. Hope this gives you a fair idea about what the different implementations of BISM are and which one to choose based on various parameters.

=================================================================

Other Reference:

DirectQuery Mode (SSAS Tabular): http://msdn.microsoft.com/en-us/library/hh230898.aspx

Determine the Server Mode of an Analysis Services Instance: http://msdn.microsoft.com/en-us/library/gg471594.aspx

Comparing Tabular and Multidimensional Solutions: http://msdn.microsoft.com/en-us/library/hh212940.aspx