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 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! 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 Creating simple measures for our pivot table 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.

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!

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
Creating simple measures for our pivot table
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

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:

Choose this menu option to use an existing database.

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

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

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

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:

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:

Here we've only chosen one table to add.

The new table appears in the data model:

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:

Memo to Microsoft: where is the Properties menu?

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

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:

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:

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:

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.

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 Creating simple measures for our pivot table Making changes to a model (adding tables and columns) (this blog)

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

Making changes to a model (adding tables and columns)

Adding a table to a model

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

Head office

London

Manchester