For over a decade, many Microsoft database developers have used SQL Server Analysis Services (SSAS) OLAP to create database cubes for advanced business analytics. SSAS OLAP offers many powerful features, although some developers view the learning curve of OLAP (and its supporting language, MDX) as very steep.

SQL Server 2012 contains a new BI semantic model that allows developers to create analytic databases either by using OLAP or by using a new Tabular model. The latter contains a somewhat different set of tools than SSAS OLAP and a new supporting language (DAX). Some believe that the new Tabular model is easier to use than SSAS OLAP. But is it?

Some believe that the new Tabular model is easier to use than SSAS OLAP. But is it?

In this article, I'll take several tasks and functionality that database developers often need to implement, and I'll compare the approaches using SSAS OLAP and SSAS Tabular. Depending on how much (or how little) you've used either approach, the results might surprise you.

Product Comparisons

I've been using SSAS for many years now to create OLAP databases for clients. I've seen it grow into a mature, full-blown solution for creating analytic databases. And I'll freely admit to being partial to SSAS OLAP and MDX. Yes, they take time to learn, but can produce excellent results.

When I started working with the new Tabular model in SSAS 2012, I was keenly interested in whether certain tasks in the Tabular model would be more powerful (or less powerful) and easier (or more difficult) than traditional SSAS OLAP. Sometimes a consensus can surface right away, and the initial consensus was that Tabular could produce the same or similar results with less effort?without the developer needing to get down to the “bare metal” that characterizes many SSAS development activities.

Like most developers, I view available functionality as a key context in product comparisons.

To put the Tabular model to the test, I went through some of the common patterns and development tasks that many developers go through when creating SSAS OLAP databases. Like most developers, I view available functionality as a key context in product comparisons. This article covers these tasks and where I think one approach comes out on top. If you are planning to use the Tabular model for a project, hopefully some of the items in this article will save you some research and time.

A Refresher on SSAS OLAP and SSAS Tabular

First, I'm assuming that you have at least basic knowledge of either methodology. If you want a refresher on the basic steps of either SSAS OLAP or SSAS Tabular, I wrote about creating SSAS OLAP cubes in the March/April 2011 issue of this magazine: https://www.codemag.com/article/1103091. I also wrote about creating SSAS Tabular Databases in the January/February 2013 issue: https://www.codemag.com/article/1301071. Throughout this article, I'll reference specific sections from both articles. Note that this article doesn't have any images, and only a few code snippets; you can find the details for SSAS OLAP and SSAS Tabular in the two prior articles.

Additionally, as experts generally recommend shaping the source of analytic databases in star-schema fact/dimension models, I wrote about Dimension Modeling tips in the March/April 2013 issue. (https://www.codemag.com/article/1304071)

Even before I dive into any detail comparisons, there is one topic that must always be mentioned. Remember that SSAS OLAP (also known as multidimensional OLAP) can handle extremely large datasets that exceed server memory. By contrast, SSAS Tabular is an in-memory solution that compresses the data but cannot exceed server memory. Although some corporate BI databases can use the Tabular model, the larger installations will need to use the more scalable SSAS OLAP model.

Although some corporate BI databases can use the Tabular model, the larger installations will need to use the more scalable SSAS OLAP model.

What's on the Menu?

Here are the 13 areas that I'll compare SSAS OLAP to SSAS Tabular:

1. A brief history of the two methodologies
2. Creating starter projects using SQL Server Data Tools
3. Baker's Dozen Spotlight: handling more advanced relationships (such as role-playing relationships and many-to-many relationships)
4. Using the programming languages (MDX for SSAS OLAP and DAX for SSAS Tabular)
5. Creating KPIs and Calculated Members
6. Creating partitions
7. Creating perspectives
8. Virtual analytic database projects that query back to the source (ROLAP for SSAS OLAP and DirectQuery for Tabular)
9. Security roles
10. Deployment models
11. Updating both models incrementally
12. Accessing with Client Tools
13. Overall feature matrix comparison

Item #1: Some History of the SSAS OLAP and Tabular Methodologies

Let's take a step back and look at the history of SSAS and OLAP.

• In 1998, Microsoft released OLAP Services with SQL Server 7, and along with that, released the first MDX programming language specification.
• In 2000, Microsoft released Analysis Services with SQL Server 2000. This version contained better support for business dimensions, but was definitely limited by today's standards. The version only permitted one fact table per cube, but was still an important release in the history of Microsoft Analytic databases.
• In 2005, Microsoft released a GREATLY enhanced version of Analysis Services with SQL Server 2005. This release introduced the UDM (Unified Dimension Model), which is an API, a “bridge” between users/developers and multidimensional sources. The release of SSAS 2005 also included support for more advanced dimension types, and significant support for data mining. In the opinion of many database historians, SSAS 2005 was the first truly powerful version for creating OLAP solutions.
• In August 2008, Microsoft released an enhanced version of Analysis Services in SQL Server 2008. SSAS 2008 contained some data mining enhancements, MDX enhancements, and a better interface for creating attribute relationships and aggregations (stored subtotals) inside a cube. Although SSAS 2008 wasn't as earth-shaking as SSAS 2005, most still viewed it as a strong upgrade.

By 2008, OLAP developers had great tools in SSAS to build multidimensional OLAP cubes. As a personal statement, I view SSAS 2008 (even today) as a very powerful tool.

But rarely is a product perfect, and some developers had the following concerns about developing analytic database applications with SSAS:

• The learning curve for SSAS was very steep, and usually required a large investment of time to shape data into fact/dimension data marts before using SSAS.
• The learning curve of the MDX language was very steep.
• In general, the SSAS OLAP paradigm was dramatically different from relational databases. Even where SSAS OLAP functionality was easier than the relational counterpart (e.g., security roles, partitions), it still meant that developers had to learn two different approaches.
• Also in general, companies had to seek out SSAS OLAP talent to create analytic databases for business users. This left some business users feeling that lack of talent and difficulty in the tools was a roadblock that interfered with their ability to get meaningful results from the company historical data.

On the last point, by 2009-2010, some businesses noticed competing products like QlikView, which permitted power users a certain level of access to summarize and slice-and-dice data to answer key business questions. And the question, albeit a slightly oversimplified one, was why competing products allowed business users a level of independence and autonomy to work with historical data, while the Microsoft tools always seemed to need a good developer handy to get results.

PowerPivot 1.0 represented a very important flashpoint in Microsoft's entrance into the self-service BI paradigm.

In the spring of 2010, Microsoft released a product, a free add-in for Excel, called PowerPivot. Although the first release of PowerPivot (1.0) itself contained some quirks and shortcomings (which Microsoft addressed in PowerPivot 2.0), PowerPivot 1.0 represented a very important flashpoint in Microsoft's entrance into the self-service BI paradigm. It's sometimes difficult to briefly explain what users can do with PowerPivot, but its primary benefit allows power users to do the following inside of Excel:

• Take historical relational data and create a compressed mini-cube in Excel, using an interface that reminded SSAS developers of a stripped-down OLAP cube wizard.
• Create pivot tables and pivot charts in the spreadsheet against the mini-cube, which lives in the basement of the spreadsheet. The official term for this mini-cube was a Vertipaq database, in recognition of the new Microsoft Vertipaq compression technology that often generates a compression ratio of 8:1. For users retrieving a hundred million rows (which PowerPivot could handle), that compression was critical.
• Create custom calculations against this PowerPivot (Vertipaq) data using a new programming language called DAX (a hybrid of MDX and the Excel Macro language)
• (Optionally) Publish this Excel spreadsheet with the data to a SharePoint 2010 location, where users can interact with it. (This required SharePoint 2010 and SQL Server 2008R2, also released in spring 2010).

PowerPivot 1.0 certainly wasn't a full-blown alternative to SSAS OLAP for business users. However, it accomplished two things. First, it gave Excel business users a stripped-down version of the OLAP cube wizard, to create an end product for users to create analytic databases inside a tool that they use all the time. Second, it put Microsoft on the map in the area of self-service Business Intelligence - and while PowerPivot 1.0 still lacked functionality and even some stability, Microsoft had made a clear statement to the world and its competitors.

As of mid-2010, companies could use PowerPivot for certain elements of self-service BI or departmental BI if they didn't have access to the full-blown SSAS OLAP tools. But that still left developers who wanted the power of SSAS OLAP but with somewhat simpler interface tools (where PowerPivot had sketched out some promising strokes) in the same situation as before.

As of mid-2010, companies could use PowerPivot for certain elements of self-service BI or departmental BI if they didn't have access to the full-blown SSAS OLAP tools.

To a considerable degree, that changed in SQL Server 2012, when Microsoft brought (and improved) some of the capabilities of PowerPivot into the Visual Studio shell where developers previously created OLAP applications (Business Intelligence Development Studio, or BIDS). I could take another full page and talk about what Microsoft implemented for database developers in SQL 2012, but suffice it to say that Microsoft created three paths for the creation of analytic databases.

First, Microsoft greatly enhanced the PowerPivot tool in Excel (PowerPivot 2.0), with better interface tools for business users to gather data, create relationships, and create complex formulas (with a greatly enhanced DAX 2.0 language). They also improved the integration with SharePoint, something that caused considerable difficulties for PowerPivot 1.0 users. I am personally quite impressed with PowerPivot 2.0. Having said that, PowerPivot will not work effectively (or at all) with databases larger than, say, 100 million rows; which continues to keep the product in the self-service BI and departmental BI realm.

• Second, Microsoft allows database developers to create analytic database projects in a new way, with visual tools similar to PowerPivot, using the same visual Studio shell that SSAS OLAP developers used for years. (Note: Microsoft now refers to BIDS as SSDT, which stands for SQL Server Data Tools). It is still essentially “BIDS”, but with enhancements for database access that lessens the need to open up SQL Management Studio to query data. This new methodology is known as the Tabular model. The Tabular model uses the same Vertipaq engine (which is now called xVelocity instead of Vertipaq) found in PowerPivot, but can be used against much larger database sources. Developers can create custom calculations and KPIs using the DAX language, and can build partitions, perspectives, and security roles in a similar fashion to what SSAS OLAP developers have done for years. They can then deploy the tabular database to an SSAS tabular server, and users can interact with the tabular database using the same output client tools (Excel, SSRS, Report Builder, SharePoint/PerformancePoint Services, etc.) that they've used for years against OLAP cubes
• Third, Microsoft continues to provide the same SSAS OLAP capabilities that are found in SQL Server 2008. The SSAS OLAP methodology is largely unchanged in SSAS 2012 OLAP. Although no one outside of Microsoft can predict what we will see in the next release of SQL Server, most individuals do not expect Microsoft to make significant enhancements to SSAS OLAP.

The good news is that PowerPivot 2.0 is much better than before. It's also good news that developers can now either create analytic databases using Visual Studio (SSDT) and PowerPivot-like visual tools to create large tabular databases that can be consumed by end-users in much the same way as users have consumed OLAP cubes for years.

The good news is that PowerPivot 2.0 is much better than before.

But, as the old saying goes, “the devil is in the details.” And the specifics about the new tabular model, combined with the general belief that Microsoft isn't likely to enhance SSAS OLAP in the future, raise some important questions:

• Is SSAS OLAP dead? Should database developers avoid OLAP for new projects?
• Is the new SSAS Tabular (essentially a 1.0 release) as powerful and robust as SSAS OLAP? Or is it more like the proverbial, “it will do 80% of what SSAS OLAP does?”
• If the answer to the first question is “no”, then when should database developers stick with the traditional SSAS OLAP method, and when is the new Tabular model the better choice?
• Are there any instances where PowerPivot 2.0 should be considered in the same breath with the Tabular and OLAP models, or is PowerPivot truly for end users and Tabular/OLAP for developers?

The next 12 topics will cover the first three bullet points. At the end, I'll cover the final bullet point question on PowerPivot.

Item #2: Creating Starter Projects Using SQL Server Data Tools

For both SSAS OLAP and SSAS Tabular, the steps are rather similar. You can use SQL Server Data Tools (formerly BIDS) to create projects. In both instances, you create a new project (either as an SSAS MultiDimensional OLAP project or an SSAS Tabular project), define a data source, and select tables from the source. In both prior CODE articles I've written, I cover how to create new projects.

Note: As of this writing (early May 2013), Microsoft had just released SQL Server Data Tools - Business Intelligence for Visual Studio 2012. In a nutshell, this is still “BIDS” (Business Intelligence Development Studio), but with an updated Visual Studio interface. You can find information for SSDT for BI 2012 at the following links: http://msdn.microsoft.com/en-us/library/jj856966.aspx and http://www.microsoft.com/en-us/download/details.aspx?id=36843. You can use SSDT for BI 2012 for both SSAS OLAP and SSAS Tabular projects.

Whether you are building a basic analytic database or a much more full-blown application, I strongly recommend shaping the source data in fact/dimension star schemas (or snowflake schemas for complicated scenarios).

Whether you are building a basic analytic database or a much more full-blown application, I strongly recommend shaping the source data in fact/dimension star schemas (or snowflake schemas for complicated scenarios). Success in achieving the end game of “slicing and dicing” metrics by related dimensions is largely proportional to how closely you adhere to the methodologies of shaping data into facts and dimensions through ETL processes.

Despite what advocates of self-service BI applications have been saying in the last few years, the best (a.k.a. “tried and true”) practice for analytic databases is ALWAYS to shape data into fact/dimension models. Yes, self-service BI tools (and even the tabular model) are great for building prototypes against less structured data-the process can even reveal missing relationships - but the final “prime time” data application should work off a shaped model.

Advantage: A tie.

Item #3: Spotlight: Handling More Advanced Fact/Dimension Relationships, and Configuring Dimensions

I often identify one topic in my Baker's Dozen articles as the “Baker's Dozen spotlight.” Although all 13 topics are important in some way, one topic is usually so immensely critical that I give it “spotlight” status. This will be one of the deepest “spotlight” topics I've ever written, so grab a chair and get comfortable.

The secret sauce of analytic databases is that users can aggregate or slice and dice measures by related business dimensions without needing to write code.

The secret sauce of analytic databases is that users can aggregate or slice and dice measures by related business dimensions without needing to write code. In both of my prior CODE Magazine articles on SSAS OLAP and SSAS Tabular, I showed examples where users can create pivot tables in Excel against analytic databases. You'd never know whether the underlying analytic database was created using SSAS OLAP or SSAS Tabular.

Some analytic databases are sourced from fact/dimension models with flat, de-normalized, pure star-schema databases. In that situation, both sets of model designers in SSAS OLAP and SSAS Tabular provide similar functionality when the source data contains simple star-schema relationships.

However, there are certainly many projects out there with fact/dimension relationships that go beyond this. I'm going to take a look at three examples that SSAS OLAP handles natively, but SSAS Tabular does not. One (role-playing relationships) is rather common - perhaps not initially for a project, but eventually so. Another (many-to-many relationships), while not as common, still represents a situation that can occur where we need to know what SSAS Tabular workarounds are required. A third (self-join relationships) is the least common of the three, primarily used for irregular organizational hierarchies.

For instance, suppose you have a fact table of orders, where you want to summarize order dollars by the original order date (when the liability was booked), by the due date, and by the delivery date. The fact order table might contain three date foreign keys, related to one date dimension. SSAS OLAP allows us to build an association between the date dimension and the three date keys (known as date “roles”), so that the end user can summarize order dollars by any of the three dates. If you establish a hierarchy in the date dimension, SSAS OLAP will surface three views of the date dimension, with the hierarchy available for each one.

As a general rule, the Tabular mode will not support role-playing dimensions.

Unfortunately, SSAS Tabular doesn't provide this. As a general rule, the Tabular mode will not support role-playing dimensions. Even though the relationship editor will appear to allow developers to drag a single primary key into multiple foreign keys in the same fact table, any aggregations will only work against one of the three relationships, rather than all three. The bottom line is that SSAS Tabular only supports one active relationship, so there is no direct support for role-playing dimensions.

The workaround in the Tabular model for role-playing dimensions requires some manual DAX calculations in the tabular model to reflect each of the three relationships. So instead of being able to aggregate a single measure against three different roles/views of the date dimension, you must create three separate calculation measures for each of the roles, using each of the three relationships, as follows:

Sum of OrderSalesAmount Reseller :=
    CALCULATE( sum( [SalesAmount]), userelationship( 'Date'[DateKey], 
       ResellerSales[OrderDateKey]))

Sum of OrderDeliveryAmount Reseller :=
    CALCULATE( sum( [SalesAmount]), userelationship( 'Date'[DateKey], 
       ResellerSales[DeliveryDateKey]))

You might say, “OK, six of one and a half dozen of the other.” But consider this: Last year, I had a client implementation with six different date roles: order date, ship date, WIP date, deliver date, etc. The users needed to summarize up to four existing OLAP cube measures from the related fact table. This means that there are 24 calculation measures, as opposed to viewing four measures according to six different date views. Given what SSAS OLAP provides, I find this somewhat absurd. It's workable, but hardly optimal. SSAS OLAP is a clear winner here.

Next, imagine the following scenario. You're creating an analytic database for a book publisher. You have a fact table of sales for books by date, by book, by customer, etc. You can easily build a scenario for aggregating sales by book or book category. But your database also contains a dimension for authors, with the rule that a book might be written by multiple authors, and a single author can write multiple books (either alone, or with different authors). The database contains a table (a cross-reference, many-to-many bridge table) with each book/author combination, along with the author's contribution percentage toward the book. In my situation, I've written two books, one where I contributed about 80% of the content, and a second where I contributed about 10% of the content. Those percentage figures are needed if you want to roll up sales and then multiply by the percentage to determine the base dollars from which royalty earnings are derived.

Other examples where analytic databases contain bridge tables might include currency conversions from one currency to another, based on exchange rates that vary by day, or unit of measure conversations from one volumetric (pounds) to another (cases), based on conversion rates that vary by product category. These don't occur in every analytic database, but they do occur.

How do SSAS OLAP and SSAS Tabular handle these?

SSAS OLAP has several features to handle many-to-many relationships. There is a built-in relationship type (in my original example) to aggregate sales by author, multiplied by the author share, by “bouncing off” the bridge table, where the bridge table contains both the author key and a second key that relates directly to the sales table (the book key). The key factor here is that SSAS OLAP provides built-in logic to handle bridge tables. (In my original SSAS OLAP article, I also cover the AdventureWorks many-to-many example using currency rates.)

The key factor here is that SSAS OLAP provides built-in logic to handle bridge tables.

SSAS Tabular does not handle bridge table relationships directly?there is no built-in logic the way that SSAS OLAP handles it. You need to write DAX code to aggregate any measure against the bridge table, by multiplying the core measure (sales) by the ratio (from the bridge table), expressed in terms of the dimension that sits in between sales and the bridge table (the book dimension).

AuthorDollars :=
    SUMX('DimBooks', CALCULATE( SUM( 'FactBookSales'[SalesDollars])
    
* SUM('BooksXAuthors'[AuthorShare])/100 ))

Finally, SSAS OLAP provides built-in support for self-join relationships. The SSAS OLAP AdventureWorks project contains a basic but functional example in the Employee dimension of a self-referencing hierarchy for an employee organizational hierarchy list. This feature in SSAS OLAP can be helpful for ragged or unbalanced relationships. By contrast, SSAS Tabular requires DAX code, potentially for each intersection point.

In all three examples of intermediate/advanced relationship types, you must write DAX code in the Tabular model to achieve comparable functionality.

Before I move on, I want to talk about one other topic, and that is ability in SSAS OLAP to build hierarchies, attribute relationships, and set dimension properties. I covered these in my previous Code Magazine article on SSAS OLAP. These features are critical for OLAP developers who need to build and optimize hierarchies that reflect business entity parent/child relationships, and to configure dimension attributes for such areas as:

• Defining default attribute member values and defining if SSAS OLAP can aggregate the attribute (helpful in many-to-many relationships)
• Building ragged hierarchies
• Discretization (helpful for business modeling)
• Optimizing an attribute (helpful to set to false for attributes with many infrequently used member values )
• Dimension attribute display folders

SSAS Tabular supports very few of the dimension attribute properties that are found in SSAS OLAP.

On the SSAS tabular side - SSAS tabular contains a hierarchy editor as well, although it is so closely embedded in the data model view that a developer doesn't get the same kind of “screen real estate” that an SSAS OLAP developer will get. Additionally, SSAS Tabular does not support attribute relationships, as SSAS Tabular does not support aggregations by dimension attribute levels. Finally, on the list of dimension attribute properties I referenced in the bullet point list above (default member, discretization, etc.), SSAS Tabular supports very few of the dimension attribute properties that are found in SSAS OLAP. This can be very disappointing, especially since an analytic database might not contain billions of rows but might contain dimensions with dozens of attributes (e.g., client demographics) that need to be organized by display folders.

Honorable mention: I give SSAS Tabular credit for a dimension-editor feature that is truly easier than SSAS OLAP. That feature sorts an attribute column by another attribute. For scenarios where you want to sort a month description (such as March 2013) by an underlying sort key (such as 2013-03), there is a drop-down in SSAS Tabular for making this selection. SSAS OLAP requires you to set at least one (and sometimes more than one) property in the dimension property sheet. Additionally, SSAS Tabular's dimension editor interface allows you to easily select which attributes should not appear to end users, whereas SSAS OLAP requires you to set the dimension attribute property in the property sheet.

Because the SSAS OLAP dimension property sheet contains many properties that require some understanding, the fact that SSAS Tabular surfaces these two capabilities in the core interface is an example where SSAS Tabular indeed is a little easier. Hopefully the next version of SSAS Tabular can build on this to cover other capabilities that currently only SSAS OLAP provides.

Advantage: SSAS OLAP is a clear winner. The fact that SSAS tabular might require so many calculations for advanced business relationships to extend the model makes SSAS OLAP a far more elegant solution in this area. Even supplemental DAX code won't cover all the situations where SSAS OLAP properties exist. Although the next major release of SSAS Tabular will surely provide new functionality to bridge the gap, this is currently an area where SSAS Tabular falls seriously short of SSAS OLAP.

Item #4: Using the Programming Languages (MDX for SSAS OLAP and DAX for SSAS Tabular)

In the last few years, I've written articles on both SSAS MDX and DAX, and have used both languages in production. Both languages rely heavily on context and (not surprisingly) both require a strong understanding of the underlying data and metadata model.

OLAP developers use MDX for calculated members, KPIs, named sets, and for queries in tools like Reporting Services and PerformancePoint Services. It is not an easy language to learn, which is partly why so many companies are excited when they find a developer who is strong in MDX.

Tabular developers use DAX for calculated members and KPIs. DAX does not currently support named sets, which OLAP developers looking to transition to the Tabular model will find is a serious shortcoming.

DAX does not currently support named sets, which OLAP developers looking to transition to the Tabular model will find is a serious shortcoming.

When SSAS Tabular was released, there was a notion that DAX was “easier” than MDX. Most developers who have worked with DAX know that is often not the case. Although DAX contains a rich set of functions for ranking, period-handling, and other categories that OLAP developers need, the syntax and overall rules (and gotchas) with DAX are nearly as complicated as MDX.

Let's look at two examples for calculating a rank and calculating a % of parent. Here are the DAX examples:

CityResellerSalesRank:=
    if(countrows(values(Geography[City]))=1, 
      IF(NOT(ISBLANK(VALUES(Geography[City]))), 
      RANKX(all(Geography[City]), 
      ResellerSales[SumofSalesAmountReseller]), BLANK()),BLANK())
    
CityResellerPctofTotal:=
    [SumofSalesAmountReseller]/ CALCULATE([SumofSalesAmountReseller], 
      RELATEDTABLE((Date), ALL(Geography[City])))

Now let's look at the MDX equivalents:

WITH SET [OrderedCities] as ORDER([Geography].[City].Children, 
  [Reseller Sales Amount], desc)

MEMBER [CityResellerRank] as RANK([Geography].[City].CurrentMember, 
  [OrderedCities])
WITH MEMBER [Geo Reseller Pct of total] as 
  [Measures].[Reseller Sales Amount] / ( 
  [Measures].[reseller Sales Amount], [geography].[Hierarchy].Parent) )

It's subjective, but I don't see the level of difficulty to be any different between the two. And when I look at other calculated expressions that use functions like ParallelPeriod and the DAX expression SamePeriodLastYear, I also don't see major differences. If anything, the MDX capability to rank against named sets (something that is missing in SSAS Tabular) makes MDX more optimal in certain situations.

Advantage: SSAS OLAP gets the edge, because of the support for named sets. DAX's reported “ease of use” diminishes once you start to write more complicated expressions.

Item #5: Creating KPIs and Calculated Members

At the beginning of this article, I referenced two previous CODE Magazine articles where I covered SSAS OLAP and SSAS Tabular in details. Both articles covered (among other things) how to create KPIs (Key Performance Indicators).

In comparing the two, I would say that the processes are similar. I give SSAS Tabular a slight edge on the user interface (which you can see from the prior CODE article in tip #8).

However, I would give SSAS OLAP an advantage on functionality, as you can define more advanced KPIs (using five-status gauges) with optional trend-based components. The issue with SSAS Tabular is that KPIs do not have a trend-based element, which means the developer must create separate calculations for the trend-based portions of a KPI. Although this is certainly not a deal breaker, it does make the process seem initially a bit more involved than the more self-contained SSAS OLAP KPI module.

Slight advantage to SSAS OLAP for functionality; slight advantage to SSAS Tabular for better UI.

Item #6: Creating Partitions

Both SSAS OLAP and SSAS Tabular allow developers to build partitions. In many instances, developers define partitions based on a date range (i.e., partitions by year, quarter, month, etc.). In both instances, developers must write T-SQL code, which ultimately runs against the source of the OLAP Cube/Tabular database. Both tools provide an interface for defining partitions, with the SSAS OLAP interface quite powerful (though not always intuitive) and the SSAS Tabular interface a bit more streamlined.

In both models, a developer can ultimately use XMLA processing commands to process new data into a partition.

In both models, a developer can ultimately use XMLA processing commands to process new data into a partition. This benefits environments that want to focus on full or incremental rebuilds of data for a recent time period (e.g., last week, last month, etc.).

But from this point on, SSAS OLAP is far more powerful. The Analysis Services engine can process and query OLAP partitions in parallel, leading to potentially better performance and scalability. By contrast, SSAS Tabular partitions do not support parallel processing, nor will the Tabular in-memory engine (VertiPaq) consider table partitioning in order to reduce scans during a query. Although partitioning in either model can facilitate incremental processing of the newest rows, SSAS OLAP is the clear winner on scalability.

Advantage: SSAS OLAP

Item #7: Creating Perspectives

In both models, an analytic database might contain so many fact tables and dimension tables that a user might feel intimidated or confused by the sheer number of measures and entities. A user who only needs to see supplier and product sales by quarter might not need to see dozens of other elements. It's not that they shouldn't be allowed to see other elements, they simplify might not need to see it in order to do their jobs. In other words, they only need to see a certain “perspective” of the analytic database.

To simplify the presentation of the analytic database for certain users, both models support a feature known as “perspectives,” where the developer can associate certain measures and dimension definitions with a specific name (the perspective). If you're thinking, “that sounds like a view,” use the term view is generally associated with filtering that is based on row values. Perspectives are more of a structural view based on metadata than a view to expose a subset of rows.

Perspectives are more of a structural view based on metadata than a view to expose a subset of rows.

An analytic database can contain several perspectives, perhaps for company units like purchasing, financial, production, etc. Note that applications cannot force perspectives on end users; perspectives are not a security feature. The user must select the perspective when browsing the analytic database. (Presumably, if developers build perspectives for end users to simplify their viewing experience, the users will be more than happy to select them).

Advantage: A tie

Item #8: Virtual Analytic Database Projects that Query Back to the Source (ROLAP for SSAS OLAP and DirectQuery for Tabular)

In most OLAP database scenarios, the OLAP database is a physical database, processed from the relational source. A conservative estimate is that at least 85% of the time, OLAP databases use what is called the MOLAP storage methodology.

Although MOLAP provides many benefits (such as huge performance benefits through aggregations), one issue with MOLAP is that developers must build ETL modules to populate MOLAP cubes (either as a full process or an incremental process). This means learning XMLA processing commands, and also means that users won't see the newest transactional data in a MOLAP cube until the MOLAP cube is processed. For those scenarios where OLAP users need real-time access to data, SSAS OLAP provides a storage methodology known as ROLAP. In ROLAP, the OLAP cube (or fact table) is nothing but a virtual proxy with no historical data - when users attempt to retrieve data from what they believe is a fully populated OLAP cube, the SSAS engine makes SQL calls back to the original data source. Although this generally won't perform as well as traditional MOLAP, it allows users to interact with an OLAP definition (using the same tools, like Excel) and also to analyze data in real-time. Although the performance issue can be significant, ROLAP otherwise works decently.

SSAS Tabular provides similar functionality through a feature known as DirectQuery. Similar to ROLAP, SSAS Tabular DirectQuery means the tabular database is essentially an empty structure, and the Tabular engine fires SQL queries back to the original source.

Similar to ROLAP, SSAS Tabular DirectQuery means the tabular database is essentially an empty structure, and the Tabular engine fires SQL queries back to the original source.

However, SSAS Tabular DirectQuery contains some serious drawbacks that don't exist in the SSAS ROLAP method. When using DirectQuery, developers cannot define either security roles or calculated columns. Additionally, the project can only define one data source. And if that weren't enough, DirectQuery models can't be consumed by traditional OLAP tools that generate MDX (such as Excel and SSRS). So this makes DirectQuery very impractical for all tools except Microsoft Power View. So most individuals would strongly recommend against DirectQuery, unless these limitations are not significant to the installation.

Advantage: SSAS OLAP

Item #9: Security Roles

One of the benefits of Microsoft analytic databases is the ability to define security roles. For instance, developers could define that certain users (or groups of users) are only allowed to see (for example) sales for the Northeast Region and for certain Product categories. By contrast, relational databases do not provide this capability out of the box - developers must either implement views or table-valued functions or special logic in stored procedures.

Out of the box, SSAS OLAP provides support for more advanced fact/dimension relationships such as role-playing dimensions, many-to-many bridge relationships, and self-join relationships. SSAS Tabular does not support these, which means the developer might need to write several DAX calculations to supplement.

Both SSAS OLAP and SSAS Tabular provide security role capabilities - for many environments, this is a crucial feature. Both models provide for “dynamic security,” where definitions for user access are stored in separate fact or dimension tables.

However, SSAS OLAP provides one feature that SSAS Tabular does not: non-Visual Totals.

For example, in SSAS OLAP, suppose you define a security role for a user to only see two regions out of five total. SSAS OLAP supports both visual totals (where the user's aggregation of sales only sums the two regions), and non-visual totals (where the user's aggregation of sales sums all five regions). Although most would assume that visual totals are more common, some environments might prefer users to know the grand total of sales for the entire country, even though the user is only allowed to see the specifics for two states. So having the option of either visual or non-visual totals is very important.

Unfortunately, SSAS Tabular only supports visual totals. To be fair, non-visual totals are not terribly common, but some developers (including me) have worked for clients where non-visual totals were required.

Slight Advantage: SSAS OLAP (because of the support for both visual and non-visual totals)

Item #10: Deployment Models

Both SSAS OLAP and SSAS Tabular contain project options to define the target server for deployment.

For SSAS OLAP, developers can choose the traditional MOLAP option, which means that SSAS deploys a physical set of cubes to the SSAS server. You can define the SSAS target server in the project properties. MOLAP is the default option for SSAS OLAP projects. Numbers will vary, but often MOLAP cubes are about 25% of the size of the original relational data source.

Numbers will vary, but often MOLAP cubes are about 25% of the size of the original relational data source.

Developers can also choose to implement and deploy the ROLAP methodology, where the cube (or a portion of it) is nothing but a virtual definition, and the SSAS engine queries the relational data on the fly at runtime. You can define ROLAP settings for the cube, the fact table, and/or individual dimensions.

In SSAS Tabular, you can also define whether the deployed database is a physical, compressed, in-memory database (standard Tabular model), or if you want to use the “ROLAP equivalent,” which is DirectQuery. In the project properties, you can define the target SSAS tabular server.

Advantage: Aside from the deficiencies of SSAS Tabular DirectQuery, this area is a tie.

Item #11: Updating Both Models Incrementally

In both SSAS Tabular and SSAS OLAP models, developers can use XMLA processing commands to rebuild an analytic database, either in part (incremental) or in full (full reprocess). XMLA stands for XML for Analysis Services, an XML specification to process analytic databases with respect to their underlying sources. Developers can write XMLA commands manually, or use the SSAS processing commands inside the SSIS control flow to generate XMLA through a wizard-like interface.

In Table 1, I've listed all the SSAS processing commands for SSAS OLAP. The table covers the different range of processing scenarios, from incremental to full process.

For SSAS Tabular, Cathy Dumas has written an excellent blog entry called “Processing tabular models 101,” that covers the XMLA processing commands for the tabular model. They are certainly similar to the XMLA commands for SSAS OLAP, but there are a few differences that Cathy points out. Here is the link: http://blogs.msdn.com/b/cathyk/archive/2011/09/26/processing-tabular-models-101.aspx. Editor's note: This link redirects to Microsoft Developer Blogs. Search for this specific article/author turned up 0 results.

Advantage: I view this as a tie. Although you can process SSAS OLAP Partitions in parallel, the processing commands themselves seem (more or less) to have equal capabilities.

Item #12: Accessing with Client Tools

For years, SSAS OLAP has enjoyed great support from output tools such as Excel, ProClarity, Reporting Services, PerformancePoint Services, SharePoint and any client tool using OLE DB to access OLAP cubes.

I've already mentioned that SSAS Tabular with DirectQuery is very limiting, with tools like Excel and SSRS being unusable. But for SSAS Tabular databases that deploy physical content, all the tools that can read OLAP cubes can also access SSAS Tabular models. In addition, the newest visualization tool (Microsoft Power View) can read Tabular databases. (Currently, there are technology previews for using Power View with OLAP, but no actual production support.)

Advantage: Until Power View (in SharePoint and Excel) can read OLAP cubes in a production version, SSAS Tabular (so long as DirectQuery is not used) has a slight advantage over SSAS OLAP.

Item #13: Overall Feature Matrix comparison

I've provided two general feature matrix tables (Table 2 and Table 3) for SSAS OLAP and SSAS Tabular. Note that SSAS OLAP contains several features that SSAS Tabular does not - and in a few cases, SSAS Tabular requires DAX to implement the same feature that SSAS OLAP natively provides. The latter point definitely challenges the claim that SSAS Tabular is going to be easier.

In Summary

Although there's no question that Microsoft's current and future focus is on the SSAS Tabular model, and the Visual Studio SSAS Tabular model interface contains a slightly more modernized developer interface, the facts also show that today, SSAS OLAP is the general winner in the feature and functionality realm.

In my career, I've read countless feature comparison articles - everything from Clipper versus FoxBase in the late 1980s, to the C compiler wars between Microsoft and Borland in the early 1990s, to SQL Server versus Oracle today. I also recall when Microsoft implemented the great WCF model as a superior replacement for Web services and remoting. I've always had a special level of curiosity for product comparisons - and I place supreme emphasis on functionality and what developers need to solve business problems. Developers are going to have difficulty becoming productive in new tools if they find them to be a step backwards (or multiple steps backwards) from older (but richer) versions.

In my professional view, SSAS OLAP is still the “king of the hill”, and SSAS Tabular is often “not quite there yet.” But the word “yet” is a powerful word in the evolutionary history of software products. None of this should imply that I don't have great respect for what Microsoft has done to produce an in-memory analytic database model (in SSAS Tabular).

However, the limitations and necessary workarounds in the Tabular model only serve to highlight the sheer depth of SSAS OLAP. And although some areas of SSAS Tabular are very close to what analytic developers need, other areas need considerable attention.

As a review (and many of these are listed in Table 2), here are the primary features in SSAS OLAP that either don't exist in SSAS Tabular (or require DAX code workarounds):

• Extremely large cubes that exceed server memory
• Parallel processing for partitions
• MDX named sets and block computation of calculations
• Direct support for more advanced relationship types (role-playing, many-to-many, self-join)
• Data mining (this is a topic that could occupy an entire article)
• Report actions
• Advanced dimension attribute properties and aggregations
• Non-visual totals
• Calculated measures for ROLAP

Yes, SSAS OLAP (and MDX) have steep learning curves, but the rewards are often worth it. SSAS Tabular might initially seem to have a shorter learning curve, but once put through the paces of an actual project, a more positively realistic conclusion might be that SSAS Tabular isn't better or easier, nor worse or tougher. It's just different.

Yes, SSAS OLAP (and MDX) have steep learning curves, but the rewards are often worth it.

In closing, I hope you have gained a better understanding of the differences between SSAS Tabular and SSAS OLAP. It has never been my intention to harshly criticize SSAS Tabular. (Believe me, I wish some of the improved UI features in SSAS Tabular would be implemented in SSAS OLAP!). I do strongly think that some speakers and bloggers have been soft on SSAS Tabular, and I think it's important to put both products under the proverbial microscope. A developer shouldn't go into a project without knowing the functionality (or lack) of the product. Identifying the differences and shortcomings is a step towards improving (in this case) SSAS Tabular.

Additionally, none of this means I wouldn't recommend using the SSAS Tabular model for new projects. If the database can fit in server memory (because SSAS Tabular is in-memory), doesn't require more advanced fact-dimension relationships and parallel processing, and you don't mind implementing DAX calculations to extend any relationships, then I'd say give SSAS Tabular a try. Additionally, companies might use SSAS Tabular (or even PowerPivot) for prototyping or proof-of-concept discovery activities - and I'm not suggesting a context of “relegation,” as the prototyping and discovery processes can be EXTREMELY valuable. But for most other projects, I recommend staying with SSAS OLAP - but keep a close eye on SSAS Tabular, as one day I'm sure it will win the feature war.