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. |
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. |
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 training courses (sadly the classroom version of this course is in the UK only - unless you are somewhere sunny and want to fly us out? - but for the online version you could be anywhere in the world).
