WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 519 reviews for our classroom and online training
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 1 thread Add post
20 Nov 21 at 15:04

Do you guys know if there is anyway of hiding from client tools programtically with VBA?  Whilst using the data model is not new to me as an early adopter of PQ & PP,  I am working on my 1st project in which i am generating all of the queries, relationships and measures programatically with VBA which is working amazingly, however not being able to hide from client tools has become a sticking point!



PS: have you looked at the Lamda function yet, it may make a good video, especially when you get into recursive Lamda!

22 Nov 21 at 09:39

Full disclosure: I didn't know you COULD generate the data model using VBA!  Do you create a reference in VBA to another library, and if so which?  As you will have gathered from this, I don't know how to hide things from the data model in VBA.  It may well be that this is part of the SSAS client interface, and so included in a different object model.  Maybe!

As to lambda functions, as far as I know these are not included in DAX, although they are now possible in Excel.


22 Nov 21 at 12:27

Hi Andy

You can use the macro VBA macro recorder to record the steps in PowerQuery and it will basically generate the text string for the PQ advanced editor. This then allows you to use VBA to close and load to data model. Once all the tables are loaded you can use VBA to write the DAX measures and relationships. 

Must confess I would not of got this far without the help of video 51.5 in your pivot tables playlist!

You do not need to add any additional reference libraries. 

In terms of LAMBDA I was referring to the Excel function.