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 one 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 (this blog) 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 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 one 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 (this blog)
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 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

Creating a data model in Analysis Services tabular

The data model is central to everything in SSAS tabular. A data model takes information from a cube, a database or any other data source, and uses it as the basis for (usually) a pivot table in Excel:

A data model feeds off the data in a relational database, and provides the data for a pivot table in Excel.

The model we'll create in this blog will feed off the main tables in the Make-a-Mammal database (which you can view and download here):

The main tables in the underlying relational database.

This would by default give rise to a pivot table which was almost impossible to use:

This pivot table field list includes everything and the kitchen sink, so it's difficult if not impossible to pick out the fields of interest.

What we'll do is:

give our tables and fields friendly names;
include in our data model only the fields needed; and
hide from our pivot table fields which would otherwise clutter it up.

The result will be a model looking more like this:

This data model is much cleaner and easier to understand.

This gives a pivot table field list which is much easier to use:

Pivot tables like this are the holy grail of tabular models; there are 9 tables included from the underlying database, but only 3 tables visible in the pivot table field list.

Later on in this tutorial we'll discover that you can use the RELATED function to combine these fields into (apparently) a single table.

To create a data model, this tutorial shows how to:

Open a connection to a database or other data source;
Import tables, choosing which rows and columns you want;
Tidy up the resulting data model in grid/diagram view, including renaming tables and columns and creating/editing relationships;
Hide tables and columns from your client, so they don't clutter up your data model;
Create simple measures; and
Make changes to tables that you've included in your model.

We'll begin with creating a connection to your data source.

Parts of this blog
Creating a data model in Analysis Services tabular (this blog) 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)

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

Creating a data model in Analysis Services tabular

Head office

London

Manchester