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:
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.
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.
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.
Again, BI Semantic Model developers and client tools can choose between MDX and DAX based on application needs, skill set, user experience, etc.
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.
The following flavors of analysis are present with today’s latest releases of SQL Server and Microsoft Office:
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.