Should you use tabular or multi-dimensional SSAS mode? Here's the answer!
Part two of a seven-part series of blogs

If you're building a data warehouse, you'll want to know whether you should be creating cubes using the legacy multi-dimensional Analysis Services server mode, or creating data models in the new tabular mode. This blog gives Wise Owl's take on the subject.

  1. Analysis Services - Tabular or Multi-Dimensional Model?
  2. What the two SSAS Server modes have in common (this blog)
  3. Creating a tabular model
  4. Creating and using a multi-dimensional model cube
  5. Comparing tabular model DAX with multi-dimensional MDX
  6. Other differences between multi-dimensional and tabular
  7. Conclusions and a recommendation

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Posted by Andy Brown on 07 November 2015

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

What the two SSAS Server modes have in common

Before looking at the differences between multi-dimensional and tabular data modes, let's look first at how Analysis Services works,

Loading a transactional database into a data warehouse

The idea is that you start with a number of data tables, typically in a relational database:

Tables in database

For the examples on this page, we assume that you have a simple relational database consisting of 4 tables only.

What happens next is that you build a data warehouse, and load data into this from your relational database. 

A company's source relational  database is often called transactional, since it contains the day-to-day transactions taking place in a company (the buzzword is OLTP, or On-Lline Transactional Processing). A data warehouse by contrast is often called On-Line Analytical Processing, or OLAP.

The data warehouse you construct takes the form of either a data model (for tabular) or cube (for multi-dimensional): let's look at each in turn.

The Tabular Data Model

For the underlying relational database above, here's an example of a data model that you could build:

Tabular data model

Note that some tables and fields have been renamed, and some removed altogether.

The Multi-Dimensional Data Model

For the multi-dimensional mode, you also need to create a data model, although this is called a data source view:

Multi-dimensional data source view

A data source view in Analysis Services (Multi-Dimensional)

However, you then need to create one or more dimensions.  Here's what a Brand dimension might look like for our example:

Brand dimension

This dimension links each product to the animal (and hence species) that it belongs to.


Finally, you need to create a cube:

A cube

A cube, which (as the diagram shows) bears little resemblance to its 3-dimensional namesake.  The yellow table contains the measures, or calculated fields.

Creating a Pivot Table in Excel (tabular or multi-dimensional)

When you have created your data model or cube, you can use it as a basis for a pivot table.  Here's what this might look like in Excel for a tabular model:

Tabular pivot table

A pivot table based on the tabular data model.


And here's the same pivot table in Excel for a multi-dimensional model:

Multi-dimensional pivot table

A pivot table based on a cube (multi-dimensional model).

This shows that the end result is similar whichever server mode you choose to use.

This all begs the question: if the end result is the same, why does it matter which server mode you choose?  Let's look in more detail now at each of the two software applications to answer that question.


This blog has 0 threads Add post