How to create a data model in SSAS tabular
Part seven 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)
  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)

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.

Making changes to a model (adding tables and columns)

This page shows how you can add a table or a table column to your model.

Deleting a table or model is easier: you just right-click on it and choose Delete.

Adding a table to a model

Suppose that you decide you want to include the centre type table in your model.  To add a table it doesn't make any sense to go through the rigmarole of creating a new connection to your database; instead, you can use your existing connection:

Existing connections

Choose this menu option to use an existing database.


Note that you can also use this tool to do the same thing:

Existing connections tool

Click on this tool to choose to use an existing database connection.


You can then double-click on the connection you want to use:

Choosing existing connection

It's just as well we gave the connection a friendly name of MAM when we created it; you can now double-click on the connection to open it.

After (possibly) entering your password, you can now choose to add a table to your model:

Adding CentreType table

Tick the table(s) you want to add, and give them friendly names as shown here.


When you finish the wizard, SSAS tabular will import all of the rows in these tables:

Transferring rows

Here we've only chosen one table to add.

The new table appears in the data model:

New table appears

The problem now is that we need to add the CentreTypeId column to the Centre table!

Adding a column to a table, or changing row/column choices

To change a table, you should be able to right-click on it and choose its properties:

No properties option

Memo to Microsoft: where is the Properties menu?


However, since you can't do this, choose this menu option instead:

Table properties menu

Make sure you've selected the table you want to amend in some way, then choose this menu option.

You can now make any changes you like:

Changing table properties

Here you should tick the CentreTypeId column to include this in the model.


SSAS tabular adds the extra column to the model, which you can then drag onto the corresponding field in the CentreType table to create a relationship:

Creating relationship

Linking each shopping centre to its type.


If you import two tables together, SSAS tabular will also import any relationship between then in the underlying database.  If, however, you import the tables in two separate chunks (as we've done above), you'll have to create the relationship manually.

You could now include the CentreTypeName column in your pivot table, to show total quantity sold by type of centre:

Shopping centre aggregate

Total quantity sold summed by centre type and species.


And with that, I'll end this long blog on creating a data model in Analysis Services (tabular model)!  Note that in the UK we also run classroom courses in SSAS.

This blog has 0 threads Add post