This page has 0 threads | Add post

Blogs

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

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! 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) (this blog) Hiding tables and columns from client view 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 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!

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) (this blog)
Hiding tables and columns from client view
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

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!


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:

Use this menu to switch between the two views.

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

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:

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!

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:

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.

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:

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:

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:

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

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

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

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

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:

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!

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) (this blog) Hiding tables and columns from client view 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 0 threads

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

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

BLOGS BY AUTHOR