Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
581 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andy Brown
In this tutorial
Up until the release of SQL Server 2012, SQL Server Analysis Services was straightforward: there was only one installation mode. But then along came tabular ...
The choice you have to make when installing Analysis Services in SQL Server 2012 or later. You don't get a second bite at this cherry!
This blog accordingly tries to answer the question: which server mode should you choose?
For those who want a quick answer, my recommendation is that you should use tabular if you possibly can (although if you have legacy cubes to support you may not have the luxury of choice). However, you should read through this blog carefully to see if you agree with its arguments!
Before looking at the differences between multi-dimensional and tabular data modes, let's look first at how Analysis Services works,
The idea is that you start with a number of data tables, typically in a relational 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.
For the underlying relational database above, here's an example of a data model that you could build:
Note that some tables and fields have been renamed, and some removed altogether.
For the multi-dimensional mode, you also need to create a data model, although this is called a 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:
This dimension links each product to the animal (and hence species) that it belongs to.
Finally, you need to create 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.
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:
A pivot table based on the tabular data model.
And here's the same pivot table in Excel for a multi-dimensional model:
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.
Let's start with how to create a tabular model. This isn't meant to be a user guide; instead I'm just trying to give an impression of how easy (or otherwise) the software is to use. You start by importing data:
You can import data from almost any relational database, but also from Excel, CSV files and even from the clipboard.
You can then choose which tables you want to import, and give them friendly names:
Here we've chosen to import 4 tables.
Analysis Services gives a friendly message showing that you've imported (or processed) the tables:
We've successfully loaded data into our model.
You can view your data model in either Grid view:
In grid view, you see one table's data at a time.
Or Diagram view:
In diagram view, you can see how tables are related, and even create new relationships.
You then need to create measures (stating which statistics you want to calculate in your pivot table):
The simplest way to create a measure: this will sum the quantity of sales by product, animal or species.
Finally, you can create a pivot table based on this model:
You can analyse your model in Excel using this menu.
By default you'll get a slightly messy pivot table field list, but it's easy enough to tidy this up:
What you get by default | After a bit of tidying |
What this shows is that SSAS tabular is full of useful wizards and is built to be easy to use, particularly for anyone who has a background in relational databases.
Bearing the hint above in mind, let's now have a look at the equivalent process for creating a cube in multi-dimensional mode for Analysis Services ...
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.
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 is easy enough.
You can then choose which tables you want to include:
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:
Here we've given the tables friendlier names.
The next step is to create dimensions for your cube:
Creating a new dimension.
The wizard, like all wizards in SSAS multi-dimensional, is a bit confusing to use:
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:
Products belong to animals, which belong to species.
The penultimate stage of the wizard sums up SSAS multi-dimensional for me:
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:
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.
Having created a dimension, it's time to create a 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:
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:
Here we'll just calculate total sales.
You can now say against which dimensions you'll calculate each measure chosen:
Here we only have one dimension, so it's easy.
Finally, you have a cube!
The final cube.
You can't browse your cube, or use it as the basis for a pivot table, until you've deployed it:
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):
You can right-click on a project to deploy it.
Finally, you can browse 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!
Not only is the architecture different for the two approaches to using Analysis Services; so too is the underlying language.
Suppose that we want to show the total value of transactions in a pivot table (that is, we want to sum Price * Quantity):
This pivot table shows the total quantity sold, and also the total value of all transactions.
Here's a DAX formula which would give the total value measure:
A typical DAX expression, giving total price * quanttiy.
Suppose we want to calculate the transaction value as in the above example. Here's an MDX calculation which would create the necessary measure:
A measure in MDX to create the expression for summing.
As this shows, neither DAX or MDX look remotely like SQL, although DAX has a lot in common with the Excel formula language.
I don't claim to have built huge models containing terabytes of data, but reading through other blogs, it seems reasonable to state two truths:
However, I think the DAX versus MDX question is a red herring. You wouldn't choose Oracle over SQL Server because you preferred Oracle SQL to T-SQL, and you shouldn't choose an Analysis Services server model based on the language that you'll have to learn.
This blog has argued strongly that you should choose the tabular server mode for SSAS, as it is so much easier to understand and use (and is integrated so much more closely with Excel and PowerPivot).
Above all, tabular models build on relational database theory, whereas to use multi-dimensional models you'll have to get your head round a completely new way of working.
However, in the interests of full disclosure, you should be aware of the following limitations of tabular models:
This isn't a full list, but in general the extra functionality provided by multi-dimensional models is either obscure and not particularly useful, can be modelled around using tabular or is likely to be included in future tabular releases.
If you already have cubes deployed within your organisation, it's unlikely to be worth re-creating all of their functionality using tabular models, so you're almost certainly best off staying with legacy multi-dimensional models.
For everyone else, you should choose tabular!
It's worth repeating that we have no axe to grind: we train on both SSAS multi-dimensional and SSAS tabular, and have no connection with Microsoft beyond training on their software.
Here are the reasons why:
Reason | Notes |
---|---|
Familiarity | Tabular models work like relational databases. If you're used to one-to-many relationships and primary keys, you'll find creating a tabular model intuitive. The same is definitely not true for creating multi-dimensional models, which involves difficult and unfamiliar concepts. |
Ease of use | Tabular is easier to use not just because the concepts are easier, but because it was written so much more recently. The dialog boxes in tabular are, by and large, easy to use; the ones in multi-dimensional give the impression that they were built by geeks who rarely interacted with human beings. |
Integration with Excel | Tabular models are tightly integrated with Excel and PowerPivot (you can develop a model in PowerPivot and import it into Analysis Services). For the 99% of users of Analysis Services who want to report on data using pivot tables, this tight integration makes life easier. |
Future-proofing | This is an informed guess on my part, but when you've trained on and worked with Microsoft software for over 20 years you get a feeling for the way things are going. Although Microsoft have publicly said they will support both server modes for the foreseeable future, I'm pretty confident that multi-dimensional mode will be frozen, while tabular models will be improved with each new version of SSAS. |
I hope this has helped you decide on the way forward for your organisation!
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.