Introduction
The first edition of this book was published in 2012, when Microsoft released the first version of SQL Server Analysis Services (SSAS) working in Tabular mode. Previously, SSAS ran a different engine, now called Multidimensional mode; since 2012, users are given the option to choose which one to install. In 2016, Microsoft issued the second major release of Analysis Services Tabular, introducing many new features and important improvements. For this reason, we decided to write the second edition of our SSAS Tabular book, which is what you are reading now.
Notice that we omitted the Analysis Services version number from the book title. This is because things are moving faster and faster. At the time of this writing, we are using the 2016 version of SSAS, but a technical preview of the next version is already available. Does that mean this book is already out-of-date? No. We took on this challenge, and we included notes related to features that could change soon. These are exceptions, however. You will probably see new features added to the product, but not many changes to the existing ones.
If you already read the previous edition of this book, is it worth reading this new one? Yes. There is a lot of new content and updates. Indeed, you should read almost all the chapters again, because we updated the entire book using the new version of Analysis Services. Moreover, with this second edition, we decided to focus on SSAS only. We removed all the advanced chapters about the DAX language, adding several new chapters and extending the existing ones to cover new features and to provide more insights into the SSAS engine. We also leveraged the experience we gained in the intervening years helping many customers around the world to deploy solutions based on Analysis Services Tabular. In case you are missing the DAX part, we wrote a comprehensive book about DAX only, The Definitive Guide to DAX, where you can find everything you need to master this beautiful language—much more than what was available in the previous edition of this book.
Finally, if you are a new developer, why should you invest on learning Analysis Services Tabular? These days, Power BI looks like a good alternative for smaller models, it is easier to use, and it is free. But it may be that one day, your Power BI–based solution will need to scale up, serve multiple users, handle more information, and grow in size and complexity. When that happens, the natural move will be to migrate to a full Tabular solution. The engine in Power BI and Power Pivot is the very same as in SSAS Tabular, so the more you know about it, the better.
We hope this book will be useful to you, and that you will enjoy reading it.
Who should read this book
This book is aimed at professional business intelligence (BI) developers: consultants or members of in-house BI development teams who are about to embark on a project using the tabular model.
We are going to start with the basics of Tabular, so in a sense, this is an introductory book. However, we assume that you already know certain core BI concepts such as dimensional modeling and data warehouse design. Some previous knowledge of relational databases, especially SQL Server, will be important when it comes to understanding how Tabular is structured and how to load data into it, and for topics such as DirectQuery. Previous experience with Analysis Services Multidimensional is not necessary, but because we know most readers of this book have some, we occasionally refer to its features and compare them with equivalent features in Tabular.
Who should not read this book
No book is suitable for every possible audience, and this book is no exception. Those with no BI experience will find themselves out of their depth very quickly, as will managers who do not have a technical background.
Organization of this book
This book is organized as follows:
Chapter 1, “Introducing the tabular model,” introduces the tabular model—what it is and when it should (and should not) be used.
Chapter 2, “Getting started with the tabular model,” and Chapter 3, “Loading data inside Tabular,” cover the basics of building a tabular model.
Chapter 4, “Introducing calculations in DAX,” introduces the DAX language to enable you to create simple calculations.
Chapter 5, “Building hierarchies,” Chapter 6, “Data modeling in Tabular,” Chapter 8, “The tabular presentation layer,” Chapter 10, “Security,” and Chapter 15, “Optimizing tabular models” deal with numerous Tabular design topics, such as hierarchies, relationships, many-to-many, and security.
Chapter 7, “Tabular Model Scripting Language (TMSL)” discusses TMSL, while Chapter 13, “Interfacing with Tabular,” provides insight into yet more methods for accessing tabular models programmatically.
Chapter 9, “Using DirectQuery,” and Chapter 12, “Inside VertiPaq,” provide several insights into the DirectQuery and VertiPaq engines.
Chapter 11, “Processing and partitioning tabular models,” Chapter 14, “Monitoring and tuning a Tabular service,” and Chapter 16, “Choosing hardware and virtualization,” deal with operational issues such as hardware sizing and configuration, processing, partitioning, and monitoring.
Conventions and features in this book
This book presents information using conventions designed to make information readable and easy to follow:
Boxed elements with labels such as “Note” provide additional information or alternative methods for completing a step successfully.
Text that you type (apart from code blocks) appears in bold.
A plus sign (+) between two key names means you must press those keys at the same time. For example, “Press Alt+Tab” means that you hold down the Alt key while you press the Tab key.
The definition of measures, calculated columns, and calculated tables uses the following standard, which does not correspond to the syntax used in Visual Studio, but it is a simpler convention for the book (more details about that in Chapter 4, “Introducing calculations in DAX”):
Table[MeasureName] := <expression>
Table[CalculatedColumnName] = <expression>
CalculatedTable = <expression>
System requirements
You will need the following hardware and software to install the code samples and sample database used in this book:
Windows 7, Windows Server 2008 SP2, or greater. Either 32-bit or 64-bit editions will be suitable.
At least 6 GB of free space on disk.
At least 4 GB of RAM.
A 2.0GHz x86 or x64 processor or better.
An instance of SQL Server Analysis Services 2016 Tabular plus client components.
Full instructions on how to install this are given in Chapter 2, “Getting started with the tabular model.”
Code samples
The databases used for examples in this book are based on Microsoft’s Adventure Works 2012 DW and on ContosoDW sample databases. All sample projects and the sample databases can be downloaded from the following page:
https://aka.ms/tabular/downloads
Follow these steps to install the code samples on your computer so that you can follow the examples in this book:
1. Unzip the samples file onto your hard drive.
2. Restore the two SQL Server databases from the .bak files that can be found in the Databases directory. Full instructions on how to do this can be found here: http://msdn.microsoft.com/en-us/library/ms177429.aspx.
3. Each chapter has its own subdirectory containing code samples within the Models directory. In many cases this takes the form of a project, which must be opened in SQL Server Data Tools. Full instructions on how to install SQL Server Data Tools are given in Chapter 2, “Getting started with the tabular model.”
4. Scripts in PowerShell and TMSL are included in the directories Script PowerShell and Script TMSL, respectively.
Acknowledgments
We would like to thank the following people for their help and advice: Bret Grinslade, Christian Wade, Cristian Petculescu, Darren Gosbell, Jeffrey Wang, Kasper de Jonge, Marius Dumitru, Kay Unkroth, and TK Anand.
A special mention to Akshai Mirchandani for the incredible job he did answering all our questions, completing accurate technical reviews, and providing us the foreword for the book.
Finally, we want to thank Ed Price and Kate Shoup, who worked as technical reviewer and editor. You will find fewer mistakes thanks to their work. The remaining ones (hopefully very few) are on us.
Errata and book support
We have made every effort to ensure the accuracy of this book and its companion content. Any errors that have been reported since this book was published are listed on our Microsoft Press site at:
If you find an error that is not already listed, you can report it to us through the same page.
If you need additional support, email Microsoft Press Book Support at [email protected].
Please note that product support for Microsoft software is not offered through the addresses above.
We want to hear from you
At Microsoft Press, your satisfaction is our top priority and your feedback our most valuable asset. Please tell us what you think of this book at:
The survey is short, and we read every one of your comments and ideas. Thanks in advance for your input!
Stay in touch
Let’s keep the conversation going! We’re on Twitter: @MicrosoftPress.