Should you use tabular or multi-dimensional SSAS mode? Here's the answer!
Part four 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
  3. Creating a tabular model
  4. Creating and using a multi-dimensional model cube (this blog)
  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.

Creating and using a multi-dimensional model cube

Here's how to create a cube in the legacy version of Analysis Services.  Again, this isn't intended to be a user guide; instead, the idea is to give you an impression of what using SSAS in multi-dimensional mode is like.

Wise Owl have no axe to grind - we train in both versions of SSAS - but I hope that it will become apparent as you read down this blog just how much harder multi-dimensional SSAS mode is to use.

Creating a Data Source View

The first thing to do in a new multi-dimensional project is to create a data source (not shown here), and then create a data source view:

Creating a data source view

Creating a data source view is easy enough.

You can then choose which tables you want to include:

Choosing tables

You can choose which tables your data source view should include.

You can then (as in tabular models) rename tables and fields to get the information looking as you want it:

Renamed tables/fields

Here we've given the tables friendlier names.

Creating dimensions

The next step is to create dimensions for your cube:

Creating a dimension

Creating a new dimension.

The wizard, like all wizards in SSAS multi-dimensional, is a bit confusing to use:

Choosing a field for a dimension

We'll use the products table for a dimension, although it's not obvious what the key and name columns are at this stage.


You can now say how the dimension hierarchy is built up:

The dimension hierarchy

Products belong to animals, which belong to species.


The penultimate stage of the wizard sums up SSAS multi-dimensional for me:

Dimension attributes dialog box

Select Enable Browsing to surface dimension attributes as hierarchies is not a completely intuitive instruction, I think it's fair to say!

You now have a dimension you can browse:

The product dimension, browsed

It's a lot of work to go to just to say that the animals, products and species are related to each other hierarchically, particularly when the software should be able to infer this from the data.

Creating a cube

Having created a dimension, it's time to create a cube:

Creating a new cube

Confusingly to this pedantic mathematician, a cube doesn't have to have 3 dimensions.


You can now say which are the statistics you want to measure:

A cube's measure groups

Here we're going to calculate the total quantity of goods sold for each possible combination of product, animal and species.


You can now say what statistics you want to calculate:

Measures to calculate

Here we'll just calculate total sales.


You can now say against which dimensions you'll calculate each measure chosen:

Choosing a dimension

Here we only have one dimension, so it's easy.


Finally, you have a cube!

The cube

The final cube.

Deploying your cube

You can't browse your cube, or use it as the basis for a pivot table, until you've deployed it:

Project properties

You need to set the properties of your project to say to which server you are deploying your cube.

You can then deploy the cube (provided, of course, that you've solved any security issues with it):

Deploying your project

You can right-click on a project to deploy it.

Finally, you can browse your cube:

Browsing your cube

The cube shows the data you request.


If you're going to choose multi-dimensional over tabular model, you need a really, really good reason! 


This blog has 0 threads Add post