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!

  1. Creating a data model in Analysis Services tabular
  2. Creating a connection to your data source
  3. Choosing tables, columns and rows to import
  4. Tidying your data model (grid/diagram view, joins, etc) (this blog)
  5. Hiding tables and columns from client view
  6. Creating simple measures for our pivot table
  7. Making changes to a model (adding tables and columns)

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 16 November 2015

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Tidying your data model (grid/diagram view, joins, etc)

When you first see your data model, it will be in diagram view. 

Switching between Grid and Diagram View

At the extreme bottom right of your model are two tiny icons!

Grid view Diagram view
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:

Diagram and grid view

Use this menu to switch between the two views.

In grid view you can see a table of data at a time:

Grid view tabs

Click on a table to see its rows.

Diagram view is described below!

Customising diagram view

In diagram view, you can tell SSAS Tabular to fit all of the tables into your window:

Fit to screen

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!

Slider view

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:

Drag to Zoom

Click and drag to zoom in/out.


Creating relationships

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.

Deleting a relationship

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:

Creating a relationship

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:

The relationship created

In SSAS Tabular, relationships point from the child to the parent table.

Renaming tables

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:

Renaming a table in diagram view

To rename a table in diagram view, right-click on it and choose the option shown.


Or in grid view:

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


Renaming columns

You can also, more usefully, rename columns.  Again this can be in grid view:

Renaming a column in grid view

In grid view you can right-click on a column to rename it.


Or in diagram view:

Renaming a column 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 tidied diagram

The final diagram could look something like this:

Final diagram

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:

Pivot table field list cluttered

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! 

This blog has 0 threads Add post