560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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. |
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).
The first thing you'll need to do is to choose your server mode, database and project:
Tutorial | Learn how to ... |
---|---|
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! | |
Set up and understand the Make-a-Mammal tutorial database used throughout this tutorial. | |
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. |
The key to using SSAS tabular is to spend time creating a simple, easy-to-use data model:
Tutorial | Learn how to ... |
---|---|
Import tables (choosing rows, columns and friendly names) and hide some from client view to create the perfect data model. | |
Import tables from Excel, Access, text files, SSRS, SSAS and other data sources. |
This part of the tutorial shows how to create calculated columns in SSAS Tabular (similar to formulae in Excel):
Tutorial | Learn how to ... |
---|---|
Create new calculated columns in SSAS tabular, including using the IF and SWITCH functions. | |
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. |
The all-important topic of using DAX to create your own measures in SSAS tabular:
Tutorial | Learn how to ... |
---|---|
Create simple measures in DAX, including an explanation of query context, the DAX aggregation functions and using X-suffix functions to aggregate expressions. | |
Review different options for creating DAX formulae, including DAX Editor and DAX Studio. | |
Learn the syntax of this vital DAX function, and learn how to use it to remove, replace or change the query context. | |
Learn about another way to change query context, using the FILTER function. | |
Learn how to use the RANKX function to rank data. | |
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). |
Your data model is likely to contain dates, and you need to learn how to work with them!
Tutorial | Learn how to ... |
---|---|
Create and integrate a calendar in Excel or SQL Server (and understand why this is necessary). | |
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. | |
Learn about impossibly useful functions like SAMEPERIODLASTYEAR and YTD, and about exotica such as semi-additive functions. | |
A clever way to allow users of pivot tables to add a set of date-based calculations with a single click of the mouse. |
As well as creating measures using DAX, you can extract data from tabular models by writing DAX queries:
Tutorial | Learn how to ... |
---|---|
Display, sort, filter and group data using DAX syntax. | |
Understand when you can use DAX queries, and the various ways in which you can fiddle Microsoft software applications to accept them. |
Create key performance indicators and establish hierarchies to improve your models:
Tutorial | Learn how to ... |
---|---|
Create key performance indicators, both absolute and relative. | |
Create hierarchies, and use them to make pivot tables easier to create. |
You can partition tables to make them load more quickly when you process them:
Tutorial | Learn how to ... |
---|---|
Understand how SSAS tabular loads data from tables, and learn some techniques for how to speed up processing. | |
Partition large tables into discrete chunks, allowing you to fine-tune table processing. |
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 ... |
---|---|
Create perspectives onto your model, so that different users see different things in Excel. | |
Limit which users of your data model can see which bits by creating and applying roles. | |
As well as setting database security, SSAS Tabular also has provision for controlling who has administrator access to your model, as this blog explains. |
Finally, miscellaneous topics which didn't fit in to any other category:
Tutorial | Learn how to ... |
---|---|
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).
Some other pages relevant to the above blog include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.