557 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
How to create a data model in SSAS tabular
Part four of a seven-part series of blogs
The first - and most important - part of using Analysis Services (tabular mode) is to create a data model, incuding only those tables and columns you need. This lengthy blog shows you how to go about creating the perfect data model!
When you first see your data model, it will be in diagram view.
At the extreme bottom right of your model are two tiny icons!
|Use this to go to Grid view||Use this to go to Diagram view|
You can also switch between the two views using this menu:
Use this menu to switch between the two views.
In grid view you can see a table of data at a time:
Click on a table to see its rows.
Diagram view is described below!
In diagram view, you can tell SSAS Tabular to fit all of the tables into your window:
Click on this tool at the top right of the diagram to fit all of the tables into one screen.
Sometimes the tables can then be hard to see!
Here SSAS tabular has had to zoom in to 15% to fit everything in the window.
You can click and drag on the slider to change this zoom percentage:
Click and drag to zoom in/out.
SSAS Tabular will only link two tables if there is a relationship between them in the underlying data source; otherwise, you'll have to create a relationship yourself.
Here I've shown deleting a relationship created automatically, just so that I can show recreating it!
To create a relationship between two tables, click on the field which is part of the relationship in one table and drag it onto the corresponding field in the other table:
Here we're re-linking the Store and Centre tables by the CentreId column.
Unnervingly, it doesn't matter which way round you drag (either from the parent Centre table to the child Store table, or vice versa). SSAS tabular will work out from the data in each table which is the parent and which is the child, and display the relationship accordingly, as shown below.
Here's the relationship the above procedure creates:
In SSAS Tabular, relationships point from the child to the parent table.
If you neglected to give your tables friendly names when choosing data to import into your model, you have a second chance now - either in diagram view:
To rename a table in diagram view, right-click on it and choose the option shown.
Or in grid view:
In grid view you can rename a table in much the same way (just right-click on the table's tab and choose Rename).
You can also, more usefully, rename columns. Again this can be in grid view:
In grid view you can right-click on a column to rename it.
Or in diagram view:
You can right-click on a column to rename it using this menu in diagram view.
In the diagram shown below, we've removed the Name suffix from every field (Product is easier to read than ProductName).
The final diagram could look something like this:
The tables look reasonably tidy, and both the tables and their columns have sensible names.
The problem remaining is: how do we stop all of the above clutter appearing in our pivot table field list:
Most of the columns shown here are of no interest to users of our model - we need to hide them.
To see how to hide tables and columns, read on!
|Parts of this blog|
25 Aytoun Street