A full tutorial blog on how to create Analysis Services Tabular models
From SQL Server 2012 onwards there is an alternative way of working with Analysis Services. Say goodbye to difficult-to-create cubes and dimensions, and embrace the new tabular model server mode instead! When finished this tutorial will cover everything you need to know, from creating your first data model to creating measures using DAX expressions.

Posted by Andy Brown on 07 November 2015

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

SQL Server Analysis Services Tabular Model - Online Tutorial

There are two server modes when you use Analysis Services (SSAS):

This blog shows how to use Analysis Services tabular (here's why you should choose this mode).

Getting Started

The first thing you'll need to do is to choose your server mode, database and project:

Tutorial Learn how to ...
Choosing SSAS server mode When you install Analysis Services, you can choose between multi-dimensional and tabular models.  This part of the tutorial explains which you should plump for!
The tutorial database Set up and understand the Make-a-Mammal tutorial database used throughout this tutorial.
Creating a project in Visual Studio Use Visual Studio (SQL Server Data Tools) to create a new Analysis Services tabular model project, and set up Visual Studio windows to work the way you want.

Creating Data Models

The key to using SSAS tabular is to spend time creating a simple, easy-to-use data model:

Tutorial Learn how to ...
Creating a data model Import tables (choosing rows, columns and friendly names) and hide some from client view to create the perfect data model.
Import data from other sources Import tables from Excel, Access, text files, SSRS, SSAS and other data sources.

Viewing Model Data in Excel

It's not strictly part of SSAS Tabular, but this part of the tutorial explains how to create and use pivot tables in Excel, and also Power View:

Tutorial Learn how to ...
Pivot tables Create, sort, filter and format pivot tables in Excel, use slicers and timelines and also features like drill-down and quick explore.
Power View Install and use Power View to create tabular reports, matrices and charts.

Calculated Columns

This part of the tutorial shows how to create calculated columns in SSAS Tabular (similar to formulae in Excel):

Tutorial Learn how to ...
Basic calculated columns Create new calculated columns in SSAS tabular, including using the IF and SWITCH functions.
Showing related columns Use the RELATED function to bring in fields from other tables, to simplify your pivot table field list, and test for missing values with ISBLANK.

Measures and DAX

The all-important topic of using DAX to create your own measures in SSAS tabular:

Tutorial Learn how to ...
Measures in DAX Create simple measures in DAX, including an explanation of query context, the DAX aggregation functions and using X-suffix functions to aggregate expressions.
DAX Editors Review different options for creating DAX formulae, including DAX Editor and DAX Studio.
The CALCULATE function Learn the syntax of this vital DAX function, and learn how to use it to remove, replace or change the query context.
The FILTER function Learn about another way to change query context, using the FILTER function.
The RANKX function Learn how to use the RANKX function to rank data.
The EARLIER function Learn how to use this complicated (but powerful) function to create running totals and to rank, group and band data (strictly speaking this creates calculated columns, not measures).

Calendars and Dates

Your data model is likely to contain dates, and you need to learn how to work with them!

Tutorial Learn how to ...
Creating a calendar Create and integrate a calendar in Excel or SQL Server (and understand why this is necessary).
Modelling dates Solve various typical DAX date problems, including how to pick out bank holidays in calendars, how to cope with financial year-ends and how to cope with multiple date tables.
Date functions Learn about impossibly useful functions like SAMEPERIODLASTYEAR and YTD, and about exotica such as semi-additive functions.
Creating a periods table A clever way to allow users of pivot tables to add a set of date-based calculations with a single click of the mouse.

Writing DAX Queries

As well as creating measures using DAX, you can extract data from tabular models by writing DAX queries:

Tutorial Learn how to ...
Writing DAX queries Display, sort, filter and group data using DAX syntax.
Where DAX can be used Understand when you can use DAX queries, and the various ways in which you can fiddle Microsoft software applications to accept them.

KPIs and Hierarchies

Create key performance indicators and establish hierarchies to improve your models:

Tutorial Learn how to ...
KPIs Create key performance indicators, both absolute and relative.
Hierarchies Create hierarchies, and use them to make pivot tables easier to create.


PowerPivot is SSAS tabular's little brother/sister.  Learn how to prototype models in PowerPivot, and how to import them into SSAS tabular:

Tutorial Learn how to ...
Create and upload PowerPivot models Create data models using PowerPivot, understanding the differences between this and SSAS, and then import these into a tabular data model.

Processing and partitions

You can partition tables to make them load more quickly when you process them:

Tutorial Learn how to ...
Processing Understand how SSAS tabular loads data from tables, and learn some techniques for how to speed up processing.
Partitions Partition large tables into discrete chunks, allowing you to fine-tune table processing.

Controlling who sees what

You can control who can see which bits of your model either by setting perspectives onto a model, or by managing security roles:

Tutorial Learn how to ...
Perspectives Create perspectives onto your model, so that different users see different things in Excel.
Security and roles Limit which users of your data model can see which bits by creating and applying roles.
Administrator access As well as setting database security, SSAS Tabular also has provision for controlling who has administrator access to your model, as this blog explains.

Other topics

Finally, miscellaneous topics which didn't fit in to any other category:

Tutorial Learn how to ...
DirectQuery Understand how DirectQuery bypasses the tabular engine, and find out whether you should learn more about this technique.


Finally, if you're the sort of person who prefers to learn in a classroom environment (ie a human) , you might also be interested in our SSAS Tabular or PowerPivot training courses (sadly, in the UK only - unless you're somewhere sunny and want to fly us out?).

This blog has 0 threads Add post