563 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls 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
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.
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.
|Parts of this blog|
25 Aytoun Street