BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
Learn how to create, format and explore pivot tables within Excel based upon SSAS tabular models. This tutorial also shows how to create slicers, and combine them to create dashboards.
- Excel pivot tables in Analysis Services (Tabular Model) (this blog)
- Setting up pivot tables in Excel
- Refreshing a pivot table
- Formatting numbers in a pivot table
- Formatting a pivot table and choosing display options
- Drill-down and Quick Explore
- Slicers
- Things you can't do in tabular model pivot tables
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 24 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.
Excel pivot tables in Analysis Services (Tabular Model)
All of SQL Server Analysis Services talks about "client tools":

A typical example - but what exactly does "client tools" mean?
In practice, when SSAS mentions client tools, to all intents and purposes it's talking about pivot tables in Excel. This blog therefore shows how to create and use pivot tables based on tabular models (also covered on our advanced Excel course).
Using pivot tables based on SSAS tabular models is nearly identical to using pivot tables in vanilla Excel, although there are a few small differences to catch out the unwary!
Our example model
All of the examples in this blog use the data model created in the previous part of this tutorial:

The data model used by this blog (and many of the other blogs in this tutorial).
Starting a pivot table
You can choose to analyse any model's data in an Excel pivot table using this tool:

Click on this tool to analyse the data in a tabular model in a pivot table in Excel.
Alternatively, you can also use this menu option:

You can use this menu to do the same thing.
In either case, you may then need to confirm in what role you want to create a pivot table:

For most of this tutorial I'll assume you're just creating the pivot table in your role as current Windows user.
You should now see the pivot table field list in Excel:

You can choose which fields you want to display in your pivot table.
You can now choose fields to include in the various parts of the pivot table:

The parts of a pivot table.
All this, and much, much more, is explained in the next and subsequent parts of this blog!
- Excel pivot tables in Analysis Services (Tabular Model) (this blog)
- Setting up pivot tables in Excel
- Refreshing a pivot table
- Formatting numbers in a pivot table
- Formatting a pivot table and choosing display options
- Drill-down and Quick Explore
- Slicers
- Things you can't do in tabular model pivot tables