How to create a data model in SSAS tabular
Part five 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 (this blog)
  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.

Hiding tables and columns from client view

Let's suppose that we want to be able to create a pivot table just showing total sales by region and species:

Target pivot table

A simple pivot table, with a very simple data model!

To do this, we need to hide some tables completely, and hide certain columns from others.

Hiding tables from client view

Although you can right-click on tables in grid view to hide them, it's easier to do this in diagram view.  Select the first table you want to hide:

Selecting first table

You don't want to see this table in your pivot table field list, so select it.


Now hold down the CTRL key and select the other tables you want to hide (you don't have to do them all in one go, however):

Selecting multiple tables

Here we've selected all of the tables shown boxed.

Right-click on any one of the tables, and choose this menu option:

Hiding a table

Choose to hide the selected tables from client view (ie from any pivot table).


SSAS Tabular dims the tables you've hidden:

Dimmed tables

The tables which are hidden are dimmed a bit.

Here are the ones we haven't hidden, and why:

Table Why we've kept it
Region Because we want to aggregate by region name.
Species Because we want to aggregate by species name.
Transaction Because we want to create measures summing quantity and averaging price, and show these in our pivot table.

If you were to create a pivot table based on this model now, you'd only see 3 tables:

The 3 tables remaining

The only tables you'd now see in a pivot table field list for this model.


Hiding columns from client view

Having hidden tables, it's time to hide specific columns:

Hiding columns

For each table, select the first column you want to hide then hold down the CTRL key and select subsequent columns.


You can then right-click on one of the selected columns to hide them all:

Right-click to hide

Right-click to hide the two ids, which no user will be interested in.


Note that although we can hide the id numbers, we can't delete them, since then there would be nothing to link the tables together.

For the transactions table, you'd probably hide all of its columns:

Hiding transaction columns

We'll have to create measures later to allow us to show the total quantity sold or average price for any region/species combination, but we should hide these base columns.


The final model and pivot table

Here's what our final model looks like:

Final model

There's not much left!


And here's what a pivot table using it will look like:

The final pivot table fields

There's nothing here to confuse even the simplest of users!


The next step in the process is to create the measures we want to show in the pivot table. 

This blog has 0 threads Add post