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 six 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 (this blog) 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 six 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 (this blog)
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 simple measures for our pivot table

You can take the data model that you've created and analyse it in Excel:

Click on this tool to create a pivot table based on your data model.

The problem is that there's nothing to analyse!:

We can aggregate data by region and/or species, but there are no statistics to show!

To get round this, you need to create measures using the DAX expression language.

Much of this tutorial is given over to explaining how to write DAX expressions - for the moment, though, we'll just use a wizard to create basic statistics.

Complete the following steps to create a measure showing total quantity:

Click at the bottom of the column you want to analyse (step 1), then choose the statistic you want to create (step 2).

SSAS tabular will create a measure, and give it a default name:

This measure gives the total quantity - you can rename it by changing the text before the colon (:).

Here we've renamed the measure to TotalQuantity:

When you press the enter key, SSAS tabular will change the measure name.

You could follow a similar procedure to create a measure showing the average price for any combination of region/species:

Here we're changing the measure name to AveragePrice.

You can now choose to create a pivot table based on your data model in Excel:

Click on this tool to create a pivot table based on your model.

You can now choose to display the measures you've created in the pivot table field list:

Here we're showing the total quantity of transactions for each region and species.

I've now shown how to create and use a full data model. For the final part of this tutorial, I'll show how to make changes to your model if you realise you've done something wrong or omitted something.

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 (this blog) 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 simple measures for our pivot table

Head office

London

Manchester