With the release of SQL Server 2000, Analysis Services has been the Microsoft product to deliver data models, simply referred to as cubes. As the cornerstone of a BI platform, cubes deliver an intuitive interface that provide accelerated access to potential large data volumes and encapsulated business logic in the form of calculations. Importantly, cubes help implement a “single version of the truth.” With the release of SQL Server 2012, and a new data model development approach, Analysis Services cubes are now more correctly termed multidimensional BI semantic models. This is to contrast them with the new tabular BI semantic model.
The tabular BI semantic model was first made available as a SQL Server 2008 R2 add-in to Excel 2012 named PowerPivot. PowerPivot revolutionized developing data models – both in terms of the design approach and the target audience. Targeted at business analysts, the tabular data model is developed by defining well-understood concepts including tables, consisting of columns, and relationships between tables. Tables can be extended with calculated columns and measures; measures are defined to aggregate (sum, count, etc.) the data stored in the tables. The result is a data model that can be queried by existing cube browser tools (PivotTables, Reporting Services, PerformancePoint Services, etc.). To the untrained eye, there is no user experience different between browsing a multidimensional or tabular data model.
When developing a data model with SQL Server 2012, the developer now has choice. To produce a multidimensional data model, or to produce a tabular data model. It is important to stress that careful consideration and evaluation should be made from the outset, as once developed, a data model design cannot be migrated to a different type.
The purpose of this article, then, is to describe at a high-level when to consider developing multidimensional or tabular data models.
The first consideration should be to understand data model requirements, in terms of capabilities and calculations. Understanding supported data model features will be necessary, and there is a good MSDN article available here that provides a detailed feature matrix by data model type. For example, if cube write-back is a requirement, then there is no choice – you must develop a multidimensional data model.
Understanding the business logic requirements may also result in eliminating choice, particularly when sophisticated business logic needs to be implemented. Cubes implement business logic by using Multidimensional Expressions (MDX), while tabular data models use Data Analysis Expressions (DAX). MDX is a mature and rich language designed to query cubes and navigate and evaluate expressions in multidimensional space. It also includes advanced capabilities like named sets, calculated members (on non-measure dimensions) and scoped assignments that are not supported in DAX. The business logic requirements alone may dictate that a cube is the only choice.
If the data model requirements can be met by either design approach, then the next considerations will likely be the data volumes that need to be stored. The cached mode for cubes is named MOLAP and it typically achieves a 3x compression ratio for storage. In contrast, the cached mode for tabular data models is named In-Memory, and it typically achieves a 10x compression ratio, although it is not uncommon to encounter 100x and above. Tabular data models are more efficient in terms of storage, although they come with a requirement that they entire In-Memory cache must reside in physical memory to maintain high performance query responses.
Additional considerations should concern the hardware available, the skills of the data model developers, the project timeframe and budget, and the administrative effort to maintain the data model. Generally speaking, tabular data models are easier and faster to develop, and may require little or no administrative effort to maintain.
In summary, Analysis Services data models deliver choice, flexibility, richness and scalability. The multidimensional and tabular design approaches each have their strengths and weaknesses. Developers should consider carefully the appropriate design approach. To make an informed decision, the developer will typically require knowledge of, and expertise in, both design approaches. And of course, if in doubt, produce a Proof of Concept.
Analysis Services data model development is fully covered in the Microsoft BI Academy Analysis Services training program, written and presented by the author of this article.
The author has also written a Microsoft technical article titled “Introducing the BI Semantic Model in Microsoft SQL Server 2012”. The technical article is available for download from MSDN here.