560 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 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.
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!
|Parts of this blog|
25 Aytoun Street