Introduction
In newly-released version of SQL Server, SQL Server 2012, a whole new approach to multi-dimensional analysis-like Business Intelligence is introduced. This is just one of the many new features. For a complete overview of new BI related functionality, please read the Insight "What’s new in SQL Server 2012? Highlights of new Business Intelligence functionality in the latest release."
This insight will explain the two "models" that now exist for OLAP-like analysis, the Tabular and Multidimensional model, and their advantages and disadvantages.
Introducing the Tabular and Multidimensional model
Microsoft has been a market leader in OLAP technology for many years. Microsoft’s technology is very mature and scalable as proven by the Yahoo’s 12 TB Analysis Services cube. However, in the last five years, new players have entered the BI market with the promise to finally deliver "BI for the masses”. These new players have provided BI tools with highly interactive and graphical user interfaces built on in-memory architectures and associative paradigms in terms of selecting data in the analysis. They address business users' unmet ease-of-use and rapid deployment needs. These tools have become very popular and have started to become important competitors for Microsoft especially in the midsized business segment.
- The Tabular model (In-Memory Cube). Tabular models are in-memory databases in Analysis Services. Using state-of-the-art compression algorithms and multi-threaded query processing, the Xvelocity™ engine delivers fast access to tabular model objects and data through reporting client applications such as Microsoft Excel and Microsoft Power View.
- The Multidimensional model (traditional OLAP Cube). This model is the OLAP cube that already exists for more than ten years. OLAP technology organizes summary data into multidimensional structures. Aggregations are stored in the multidimensional structure in cells at coordinates specified by the dimensions.
Figure 1: FTP access via Internet ExplorerHigh Level Architecture of SQL Server Analysis Services 2012
Data Source Layer
Figure 2: Connect to a Data Source with Tabular Model
Data Access Layer
- In Cached mode, all the data is loaded in memory and all queries are answered from there.
- In DirectQuery mode, you bypass the in-memory model, allowing client applications to query data directly on the database source (only supported for SQL-Server relational engine).
Figure 3: Different Data Access modes in the Tabular Model
- MOLAP. This is the default and most frequently used storage mode. In this mode, when you process the cube, the source data is pulled from the relational store, the required aggregation is then performed within Analysis Services and finally the data is stored in the Analysis Services server in a compressed and optimized multidimensional format.
- ROLAP does not pull data from the underlying relational database source to the OLAP server but rather both the cube detail data and the aggregated data stay in the relational database source. In order to store the calculated aggregation the database server creates additional database objects (indexed views).
- Only DAX is supported which means that it can only be used from Power View (i.e. you cannot browse it from an Excel Pivot Table).
- Time calculations are not supported.
- Calculated columns are not supported.
Performance
- By default, the Tabular engine will give great performance without any special tuning.
- This does not mean that the Tabular model will be faster than the Multidimensional model. In fact, we could argue that for any existing aggregation the Multidimensional model is likely to give better performance especially if the aggregated data is already in cache. Contrary to the Tabular model, the Multidimensional model keeps the query results in cache and as such the more the cube is used, the better query performance you will get. The results of DAX queries are never saved within the cache which means that DAX queries will always take the same time to execute.
- The relational concept of the Tabular model will also give good performance results when querying data at the lowest granularity level. This is much more of an issue for the Multi-Dimensional model especially when one needs to cross-join some very big dimensions.
Scenarios |
Multidimensional MOLAP |
Tabular In-Memory |
More Performant |
Report on Low granularity data |
Read atomic data from disk. |
Read columnar data from RAM. |
Tabular In –Memory. |
Report on aggregated data with no predefined aggregation |
Read atomic data from disk. Aggregate data in Memory |
Read columnar data from RAM. Aggregate data in Memory |
Tabular In –Memory. |
Report on aggregated data with predefined aggregations on Cold Cache |
Read aggregated data from disk. |
Read columnar data from RAM. Aggregate data in Memory |
Comparable. |
Report on aggregated data with predefined aggregations on Warm Cache |
Read aggregated data from RAM. |
Read columnar data from RAM. Aggregate data in Memory |
Multidimensional. |
Table 1 : Performance comparison between Tabular and Multidimensional Model
In most cases, the Tabular model outperforms the Multidimensional model.
Data Language Layer
Figure 4: Editing a DAX formulas in a Tabular Model
The Data Model Layer
- will be difficult to maintain,
- will impact your performance in a negative way and
- will require a lot of space (high cardinality character fields are not good candidates for high compression with the column store).
- You cannot define role-playing dimensions (this was especially useful for the Date dimension)
- You cannot define Many-to-Many Relationships (there are some workarounds to get the same results but you will have to use very complex DAX formulas)
- You cannot define a Parent-Child hierarchy
- Custom Rollups are not supported (especially useful when aggregating data following a chart of accounts)
- You cannot define Actions (Drill trough, Reporting, etc.)
- Write-back is not supported
Reporting Layer
Figure 5: Creating a report with Power View
Management Layer
Conclusion
|
Tabular Model |
Multidimensional Model |
Pros |
|
|
Cons |
|
|
Table 2: Pros and Cons Tabular Model vs. Multidimensional Model
- Do not migrate your existing multi-Dimensional models if your end-users are happy with it. There is a high probability that you will not be able to reproduce some existing functionalities which might be frustrating for your end-users. The only consideration that could lead to a rewrite in a Tabular model would be to be able to use Power View.
- For every new project, choose the Tabular model unless (1) you have a very high volume of data or (2) you have very complex business requirements (typically advanced financial reporting).