SSAS Multidimensional vs Tabular - Part 1

Have you been or are you currently working on a project where you are trying to determine which flavor of Analysis Services (Tabular or Multidimensional) you should have used or should use? Recently, I was doing a presentation on SSAS Multidimensional best practices and following the presentation one of the attendees asked a very good question, “When it comes to SSAS Tabular versus SSAS Multidimensional how do I know which one to choose?” We discussed some of the various aspects of each one, but during the conversation I realized I needed to put a presentation and ultimately a blog post together on this topic.

In this series of posts, we'll be covering 5 main considerations. Obviously there could be more considerations you should take when making this decision for your organization, but those could differ depending on your organization, team, and other factors. But the 5 big ones are:

1. Scalability
2. Performance
3. Time to Develop
4. Complex Business Problems
5. Learning Curve

Some of the tangibles that also play a role are the BI Semantic Model (BISM) and the “different flavors of analysis” that were introduced originally with SQL Server 2012.

BI Semantic Modeling (BISM) Primer

Before diving into the detailed differences between multidimensional and tabular modeling, let’s begin with a brief primer on each of the BISM modeling experiences provided by SQL Server 2012 Analysis Services.

The Business Intelligence Semantic Model (BISM) is a single unified BI platform which has both multidimensional as well as tabular data modeling capabilities to offer best of both worlds and choice for the developer. As we all agree, the relational data model is relatively easier to understand and used by a larger group of developers than multidimensional models, hence it makes sense to embrace the relational/tabular data modeling for broader adoption and to ensure utilization of customers’ existing investments and skill available with them.

The model based on BISM can integrate data from heterogeneous data sources including traditional data sources like relational databases, LOB applications or non-traditional sources like data feeds, text files, Excel, cloud services, etc. and users can work with the data stored in the model in all of these ways regardless of how the model (whether it’s multidimensional or tabular) was developed. Having said that, the BI Semantic Model is the one model that powers all end-user experiences and can be accessed in an intuitive way using Reporting Services, Power View, PowerPivot, Excel, SharePoint, etc.  Any model based on the BISM can be conceptually divided into three layers (1) Data Model (2) Business logic and queries and (3) Data Access.

Data Model

BI Semantic Model developers can choose between multidimensional or tabular projects based on the needs of their application, their skill set and the client tool can consume the model using a multidimensional or a tabular interface.  From a multidimensional perspective, the BI Semantic Model allows traditional ways of creating a multidimensional model; it allows creating a model with a cube and dimensions normally based on dimensional data model/star-snowflake schemas of a relational data warehouse.  On the other hand, from a tabular perspective the BI Semantic Model also allows creating a model based on relational data sources and makes the development much easier as it is easier to understand.

Business Logic and Queries

Again, BI Semantic Model developers and client tools can choose between MDX and DAX based on application needs, skill set, user experience, etc.

• MDX (Multi Dimensional Expression) was introduced with Analysis Services long back and now has become the de-facto BI industry standard for multi-dimensional business logic, calculations and queries for OLAP.
• DAX (Data Analysis Expression) is an expression language based on Excel formulas that was introduced initially with PowerPivot and is built on tabular concepts (tables, columns, and relationships). DAX can be used to support model development (like creating calculated columns, measures, and KPIs for tabular models and PowerPivot models), relationship navigation, context modification and time intelligence.

Data Access

The Data Access layer integrates data from heterogeneous data sources; there are basically two modes for data retrieval and management. Though there is a tradeoff between the two modes and the BI Semantic Model developer needs to make the choice based on application needs, local data storage requirements and data latency.

• Cached - Cached data mode retrieves data from all the sources and stores it in a compressed data structure that is optimized for high-speed data access. MOLAP is the storage format that has been used in Analysis Services cube (for multidimensional model) for many releases of Analysis Services. It is optimized for OLAP and uses techniques such as pre-built aggregates, bitmap indexes, and high degree of compression to deliver great performance and scale. Whereas VertiPaq is an in-memory column store engine (for tabular model) that combines state-of-art data compression and scanning algorithms to deliver blazing fast performance with no need for indexes, pre-calculated aggregates or tuning.
• Pass-through – Pass-through mode pushes query processing and business logic down to the underlying data sources, thereby exploiting the capabilities of the source system and avoiding the need to copy the data as part of the model. ROLAP is complementary to MOLAP of multidimensional model whereas DirectQuery is complementary to VertiPaq of tabular data model. Both attempt to push query evaluation, as much as possible, down into the underlying data sources. ROLAP is optimized for large fact tables and relatively small dimension tables (star schema), while DirectQuery is mostly neutral towards the backend database structure though in SQL Server 2012, DirectQuery is supported only for models that are based on a single SQL Server relational data source.

Different Flavors of Analysis

The following flavors of analysis are present with today’s latest releases of SQL Server and Microsoft Office:

• PowerPivot – With PowerPivot, you can create your own data model from various data sources, modeled and structured precisely to fit your needs.
• PowerPivot for SharePoint  – Microsoft SQL Server PowerPivot for SharePoint 2010/2013 and Excel Services add server-side processing, collaboration, and document management support for PowerPivot workbooks that you publish to SharePoint in the PowerPivot Gallery.
• Analysis Services Tabular – Tabular models are in-memory databases in Analysis Services.  Using compression algorithms and multi-threaded query processing, the xVelocity in-memory analytics engine delivers fast access to tabular model objects and data by client applications such as Excel and Power View.
• Analysis Services Multidimensional – An Analysis Services multidimensional solution uses cubes for analyzing data across multiple dimensions.  It includes a query and calculation engine for OLAP data with MOLAP, ROLAP, and HOLAP storage modes.

Analysis Services Deployment Modes

Now with SQL Server 2012, an installation of Analysis Services can be done either in multidimensional, tabular or PowerPivot for SharePoint mode. Each of these deployment modes uses a different engine (Analysis Services engine for multidimensional whereas VertiPaq engine for tabular or PowerPivot for SharePoint) and works differently by using different storage structure and memory architecture. Each Analysis deployment mode supports a different set of data sources, development/management tools, query language, etc.

During the installation of Analysis Services, on the Setup Role page of the SQL Server Setup, you need to select the SQL Server Feature Installation option for Multi-dimensional and Data Mining or Tabular mode, or PowerPivot For SharePoint option. Once you are done with the installation of Analysis Services, you cannot change the deployment mode of an existing Analysis Services instance (from multidimensional to tabular or vice-versa). Though multiple instances of Analysis Services can co-exist on the same server, each running a different deployment mode.  However, just because you can doesn’t mean  that you should.  In other words, you may be able to get away with this in a development scenario, but if you are moving to production it is highly recommended that you install the SSAS tabular and multidimensional on two different machines because both are “memory hogs”!  For a walk through of installing SSAS in tabular mode, see the previous post Installing SQL Server 2012 SSAS Tabular Mode.

Determining whether multidimensional or tabular is the optimum solution for your organization is a key step when working with SQL Server Analysis Services 2012/2014. In subsequent posts related to this topic, we will begin to explore the aspects of scalability, performance, time to develop, complex business problems, and the learning curve associated with both SSAS modes.