If you found this blog useful and you’d like to say thanks you can click here to make a contribution. Thanks for looking at our blogs!

BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

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

The newer tabular mode; and
The legacy multi-dimensional mode (think cubes and dimensions)

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

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.