This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. Analysis Services - Tabular or Multi-Dimensional Model? What the two SSAS Server modes have in common (this blog) Creating a tabular model Creating and using a multi-dimensional model cube Comparing tabular model DAX with multi-dimensional MDX Other differences between multi-dimensional and tabular Conclusions and a recommendation This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

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.

Analysis Services - Tabular or Multi-Dimensional Model?
What the two SSAS Server modes have in common (this blog)
Creating a tabular model
Creating and using a multi-dimensional model cube
Comparing tabular model DAX with multi-dimensional MDX
Other differences between multi-dimensional and tabular
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 July 2023

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:

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:

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:

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.

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:

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
Analysis Services - Tabular or Multi-Dimensional Model? What the two SSAS Server modes have in common (this blog) Creating a tabular model Creating and using a multi-dimensional model cube Comparing tabular model DAX with multi-dimensional MDX Other differences between multi-dimensional and tabular Conclusions and a recommendation

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR