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 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. Analysis Services - Tabular or Multi-Dimensional Model? What the two SSAS Server modes have in common Creating a tabular model Creating and using a multi-dimensional model cube (this blog) 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 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.

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

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 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.

Creating dimensions

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.

Creating a cube

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.

Deploying your 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.

Conclusion

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

Parts of this blog
Analysis Services - Tabular or Multi-Dimensional Model? What the two SSAS Server modes have in common Creating a tabular model Creating and using a multi-dimensional model cube (this blog) 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