New book: Tabular Modeling in Microsoft SQL Server Analysis Services, 2nd Edition

We’re pleased to announce the availability of Tabular Modeling in Microsoft SQL Server Analysis Services, Second Edition (ISBN 9781509302772), by Marco Russo and Alberto Ferrari.

Purchase from these online retailers:

Microsoft Press Store Amazon Barnes & Noble Independent booksellers – Shop local

Below you’ll find an overview of the book; the Foreword, written by Akshai Mirchandani, Principal Software Engineer at Microsoft; and key sections from the Introduction. Enjoy!

Overview

Build agile and responsive business intelligence solutions

Create a semantic model and analyze data using the tabular model in SQL Server 2016 Analysis Services to create corporate-level business intelligence (BI) solutions. Led by two BI experts, you will learn how to build, deploy, and query a tabular model by following detailed examples and best practices. This hands-on book shows you how to use the tabular model’s in-memory database to perform rapid analytics—whether you are new to Analysis Services or already familiar with its multidimensional model.

Discover how to:

  • Determine when a tabular or multidimensional model is right for your project
  • Build a tabular model using SQL Server Data Tools in Microsoft Visual Studio 2015
  • Integrate data from multiple sources into a single, coherent view of company information
  • Choose a data-modeling technique that meets your organization’s performance and usability requirements
  • Implement security by establishing administrative and data user roles
  • Define and implement partitioning strategies to reduce processing time
  • Use Tabular Model Scripting Language (TMSL) to execute and automate administrative tasks
  • Optimize your data model to reduce the memory footprint for VertiPaq
  • Choose between in-memory (VertiPaq) and pass-through (DirectQuery) engines for tabular models
  • Select the proper hardware and virtualization configurations
  • Deploy and manipulate tabular models from C# and PowerShell using AMO and TOM libraries

About This Book

  • For BI professionals who are new to SQL Server 2016 Analysis Services or already familiar with previous versions of the product, and who want the best reference for creating and maintaining tabular models.
  • Assumes basic familiarity with database design and business analytics concepts.

Foreword

For most people who have already worked with Analysis Services, the names Marco Russo and Alberto Ferrari probably need little introduction. They have worked on some of the most challenging Analysis Services projects, written multiple books about the product, and put together fascinating blog posts on best practices and other technical topics. Besides all of the above, they are frequent presenters at conferences and hold popular training courses on a wide range of topics related to business intelligence and Analysis Services. I’ve met with Alberto and Marco many times over the years and they have a wonderful passion for the BI space and a pure love of learning and teaching.

As a long-term member of the Analysis Services engineering team, I’ve worked on a large spectrum of the SSAS engine as well as parts of Power BI. I’ve truly loved building Analysis Services. The strong and enthusiastic engineering team combined with our amazing partners and customers make it so worthwhile!

Having designed and built features in Analysis Services over so many releases, I sometimes think I know exactly what customers need and want from the product. But my conversations with Marco and Alberto usually remind me how much more they know about BI in the real world. Our discussions are always fascinating and thought-provoking because both of them have a tendency to provide unexpected viewpoints that shatter my preconceived notions. The questions are wild and wide-ranging, the debates often rage on in emails, and the consequences are always positive for the product and our customers.

Every product team is occasionally accused of “living in ivory towers” and ignoring what is important to customers. Having our MVPs and experts act as our sounding board, throw cold water on our bad ideas, and show support for our good ideas is more valuable than even they realize. But I believe that the biggest value they bring to our Analysis Services world is acting as our proxies and translating our documentation and other communications (which can sometimes be too technical or abstract for non-developers), and creating examples and solutions that show people how things should really be done. This book is an excellent example of our expert community leading the way.

As always, Marco and Alberto have put in a massive amount of effort to research the new Analysis Services 2016 release. You can benefit from their expertise and hard work and take advantage of all the lessons that they have learned since they started using the new product.

I’m personally very proud of the Analysis Services 2016 release, which includes the release of so many new features and performance improvements that I can name only a few of my favorites: Tabular metadata, the TOM object model, SuperDAX, Parallel Partition Processing, BiDirectional CrossFiltering, etc. After reviewing many of the chapters of this new book, I’m confident that it will be a truly useful and educational companion to the product, and readers will quickly be able to start taking advantage of the potential of this new version of Analysis Services.

I look forward to more collaboration with Marco and Alberto and wish them all success with this new book!

Akshai Mirchandani
Principal Software Engineer
Microsoft Corporation

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.

About the authors

Marco Russo and Alberto Ferrari are the founders of sqlbi.com, where they regularly publish articles about Microsoft Power Pivot, Power BI, DAX, and SQL Server Analysis Services. Russo and Ferrari have worked with Analysis Services since the first version in 1999. Both Russo and Ferrari provide consultancy and mentoring on business intelligence (BI). They are also frequent speakers at major international conferences, including Microsoft Ignite, PASS Summit, and SQLBits.