This page has 1 thread | Add post

Finding our blogs useful? Please consider helping us to share more!

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! Creating a data model in Analysis Services tabular Creating a connection to your data source Choosing tables, columns and rows to import Tidying your data model (grid/diagram view, joins, etc) Hiding tables and columns from client view (this blog) Creating simple measures for our pivot table 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.

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!

Creating a data model in Analysis Services tabular
Creating a connection to your data source
Choosing tables, columns and rows to import
Tidying your data model (grid/diagram view, joins, etc)
Hiding tables and columns from client view (this blog)
Creating simple measures for our pivot table
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

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:

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:

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):

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

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

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

SSAS Tabular dims the tables you've hidden:

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 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:

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 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:

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:

There's not much left!

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

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.

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

Some other pages relevant to the above blogs include:

This blog has 1 thread

Add a new post

From:	DBlakeMAAT
When:	20 Nov 21 at 15:04

Reply to DBlakeMAAT

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!

Regards

Dave

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

From:	Andy B
When:	22 Nov 21 at 09:39

Reply to Andy B

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.

From:	DBlakeMAAT
When:	22 Nov 21 at 12:27

Reply to DBlakeMAAT

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.

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

Google Sheets

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC