Mark Davis – Senior Business Intelligence Consultant, Superior Consulting Services, LLC
I attended the Twin Cities’ Microsoft BI User Group recently and was in a conversation with a gentleman who asked me the question: “The Tabular BI Semantic model. Hmm. What is it useful for? It sounds small-scale, like it would only be used for smaller projects – you probably wouldn’t ever roll it out into production in an enterprise, right?
Many people have this perception. The Tabular BI Semantic Model – the technology describing a database on a tabular instance of Microsoft Analysis Services that utilizes the xVelocity in-memory analytics engine (formerly VertiPaq) – has an image problem. It has also seen a bit of resistance among professionals who have built their career on multi-dimensional data in Analysis Services cubes. Why would Microsoft provide another technology for analytical reporting? Isn’t the multi-dimensional semantic model (the OLAP cube) good enough?
This posting will take a look at some rationale for using the tabular BI semantic model in two areas: (1) what were some of Microsoft’s stated intentions that led to the formation of the tabular BI Semantic Model, and (2) what are some business cases for which it is well suited? Along these lines, we’ll compare the tabular model side-by-side with the multi-dimensional model, evaluating the advantages and disadvantages of both.
Does the World Need a New Data Model
The world has accepted, even loves, Microsoft Analysis Services. It has seen adoption by some huge customers around the world who wish to slice-and-dice quickly through data, particularly in a fast and interactive way, in a multi-dimensional database, often referred to as a cube.
But that didn’t happen overnight. It has taken years for Microsoft to perfect Analysis Services’ multi-dimensional data modeling, and for developers to develop their skills in schema-tizing that data, and for users to query that data with the MDX query language. And that didn’t happen until after their enterprise decided to invest time and money in developing solutions of which the cube is an integral part. It had always been seen as a high-price-point leap into luxury, a long-term prospect, until the release of SQL Server 2005. And today – particularly with enhancements added in SQL Server 2008, and 2008 R2, it is well within reach of small and medium-size organizations and has gained much greater acceptance.
With the Tabular BI Semantic Model, Microsoft has provided an alternative for those who have not yet put all their eggs into Analysis Services cubes. Now there are two Analysis Services server modes – Tabular, and Multi-Dimensional. If you install Analysis Services in Tabular mode, you use it to serve up Tabular BI semantic models. If you install Analysis Services in multi-dimensional mode, you use it to serve up cubes (now called a “multi-dimensional BI semantic model”). It is hoped and expected that the Tabular model will make life much easier for the business intelligence developer, user, and the enterprise at large, to adopt Analysis Services.
Multi-Dimensional or Tabular?
Now faced with the choice between modeling your data as a cube, and modeling it in a tabular manner, what are some factors that affect that decision? Can you convert a cube into a tabular model, and vice-versa? What are some real-world characteristics of the tabular BI semantic model that make it the best choice for my immediate need? What features does one type of model offer that the other does not?
Because tabular is the newer solution, you might think that migrating an existing multidimensional solution to a tabular format is the correct course of action, but this is usually not the case. Tabular does not supersede multidimensional, and the two formats are not interchangeable. Unless you have a specific reason to do so, do not rebuild an existing multidimensional solution if it is already meeting the needs of your organization. For new projects, consider the tabular approach. It will be faster to design, test, and deploy; and it will work better with the latest self-service BI applications from Microsoft.
The following table will attempt to break down some of the key benefits of analysis services, and compare if/how each type of model provides that benefit.
|Feature||Multi-Dimensional (traditional cubes) Model||Tabular (new) Model|
|Speed||One of its main benefits is that a cube affords a subset of data that contains pre-aggregated amounts and subset data caching. So, it is architected to exceed the performance of SQL queries against a relational engine for complex and flexible reporting. Many design factors affect this speed, including how well the dimensions and measures are organized, whether the data is physically stored in the cube, aggregation designs, and many others. Knowledgeable and methodical writing of queries also has a significant impact on how fast the cube provides answers; that is due to how two query engines (the storage engine, and the formula engine) work together.||The tabular model is built on an efficient storage and indexing technology, and it completely resides in memory. So it is fast by nature, and how fast is affected merely by how large the model is and how efficient the query is. There isn’t a storage engine to potentially cause a delay in query response time.|
|Size||Cubes exist that are several Terabytes in size. There are limits to the sizes of strings in cells, and other such limits, but the entire cube can be enlarged as far as disk storage is available.||Tabular model data and high-performance structures operate in memory, so RAM is the main factor limiting size.|
|Client Tool Compatibility||The multi-dimensional model is compatible with numerous client tools, including Excel, Reporting Services (Report Designer and ReportBuilder). Power View is unable to access data in a multi-dimensional model.||A Tabular BI Semantic model can be utilized with Power View, Reporting Services, and most other tools that can access an Analysis Services database, including PerformancePoint dashboards.|
|Security||You can provide role-based authorization down to any dimension member or cell of a cube. Both types of models employ Windows authentication as well as database-level access.||Tabular models provide role-based authorization at the row level.|
|Ease of Development||The more you’re willing to put in, the more you’ll get out of the model. For more complex cubes it can entail rigorous and complex work. Many files have interdependencies. Definition files are separate from the data files.||Generally a much faster, and less complex, process than multi-dimensional development. Only one file contains the definition and the data.|
|Processing||Interdependencies between objects require more methodical processing. Takes generally longer than tabular. Dimensions, measure groups, and partitions can be processed individually, but dimensions must generally be processed first.||Individual tables can be processed autonomously, and there are no processing interdependencies. Processing is generally faster and more efficient.|
|Ease of Use||MDX query language requires training and is relatively complex. Easy to browse dimensions and measures using a compatible graphical interface. Must understand measure/dimension relationships.||DAX query language requires training. It can be complex, but resembles Excel functions, and so it has a basis in familiar usage. Easy to browse tables; must understand table relationships.|
|Feature Comparisons||Multi-dimension supports the following features which tabular does not: Actions, aggregations, custom assemblies, custom roll-ups, linked objects, many-to-many relationships, translations, write-back.||Two features not supported by tabular natively, but with DAX language extensions, include: distinct count, and parent-child hierarchies. Supports Power View, whereas multi-dimensional does not.|
|Data Sources||Multi-dimensional models can access data from numerous OleDb and .Net providers. ODBC data sources are not supported.||A tabular model can import data from virtually all of the same providers as a multi-dimensional model; in addition, ODBC data sources are supported as well as those afforded by the Office data connectivity components (or the ACE provider), and the ASOLEDB provider which enable import of PowerPivot and Analysis Services cube data.|
|SQL Editions||Standard Edition of SQL Server||Requires Enterprise or Business Intelligence edition.|
A Note about PowerPivot
PowerPivot allows a semantic model to be constructed and used within a Microsoft Excel workbook, and it can also leverage Excel Services and SharePoint, required for shared server usage. By and large, the characteristics described for a tabular BI semantic model also apply to a model created with PowerPivot; however, PowerPivot has these unique characteristics relative to the tabular BI model in standalone Analysis Services:
– There is a 2 Gb artificial size limitation caused by the fact that the xVelocity engine isn’t a standalone server in this instance; rather, it exists as an internal component of a PowerPIvot for SharePoint installation and is therefore subject to limits for uploading files to SharePoint.
– Certain data sources aren’t compatible with PowerPivot use on a SharePoint server. On a desktop, you have access to data provided by Microsoft Office data connectivity components.
– PowerPivot workbooks are secured at the file level, using SharePoint permissions
A very good general overview providing more detail can be found here:
A detailed whitepaper providing usage and feature comparisons can be found here:
Superior Consulting Services (SCS) has been a Microsoft Partner since 1998 and received Microsoft Gold Certified Partner status in 2006. In 2012, with evolution of the Microsoft Partner program, SCS is now recognized for its expertise in the following competencies:
- Gold – Business Intelligence
- Gold – Web Development
- Silver – Data Platform
- Silver — Software Development
- Silver – Midmarket Solution Provider
Our gold competency is evidence of a deep, consistent commitment to the Business Intelligence and Web Development solution offerings and is a distinction given to just one percent of Microsoft partners worldwide. Our silver competencies also demonstrate a strong dedication to Data Platform, Software Development and Midmarket Solutions and is awarded to the top 5 percent of Microsoft’s worldwide partners. These designations, together with our close working relationship with Microsoft, give us:
- Fast access to a variety of resources and support
- Real-time knowledge of the latest Microsoft applications
- The ability to deliver leading-edge business intelligence, reports, applications, web and midmarket solutions for our clients’ solutions using Microsoft tools