Book Review “Microsoft SQL Server 2012 Analysis Services – The BISM Tabular Model”

I’ve recently had the pleasure to read the book “Microsoft SQL Server 2012 Analysis Services – The BISM Tabular Model” by Marco Russo, Alberto Ferrari, and Chris Webb. The authors don’t need an introduction and their names should be familiar to any BI practitioner. They are all well-known experts and fellow SQL Server MVPs who got together again to write another bestseller after their previous work “Expert Cube Development with Microsoft SQL Server 2008 Analysis Services”. The latest book was published about five months after my book “Applied Microsoft SQL Server 2012 Analysis Services: Tabular Modeling”. Although both books are on the same topic, we didn’t exchange notes when starting on the book projects. In fact, I was well into writing mine when I learned on the SSAS insider’s discussion list about the trio’s new project. Naturally, you might think that the books compete with each other but after reading Microsoft SQL Server 2012 Analysis Services – The BISM Tabular Model” I agree with Marco and Chris that the books actually complement each other pretty well.

A central theme of my book is the continuum of Self-service, Team, and Organizational BI. I felt that it is very important to show how Tabular addresses the needs of both business users and BI pros. Indeed, the Tabular journey can start very unassuming, perhaps with a business user creating a simple personal model, gains popularity and evolves to a deployed model shared by teammates, and finally to a corporate model that is provisioned and sanctioned by IT. Because of this, the first part of the book covers PowerPivot for Excel, the second covers PowerPivot for SharePoint, and the third part covers Analysis Services Tabular. Since my book naturally targets different reader audiences (business users, power BI users, and BI pros), I felt that it was imperative to lower the learning curve as much as possible, such as providing step-by-step instructions for the exercises and video tutorials. Writing a book that targets such a broad base is not easy. To make sure that the book will be well accepted, I had readers who represented each of these groups review the manuscript and provide feedback.

On the other hand, Microsoft SQL Server 2012 Analysis Services – The BISM Tabular Model focuses on the professional side of Analysis Services Tabular and targets mainly BI pros. More than half of the book is devoted on DAX and you’ll be hard pressed to find a better coverage on this topic (a note to myself that DAX deserves more attention if I ever write a revision). Besides DAX, Microsoft SQL Server 2012 Analysis Services – The BISM Tabular Model covers equally well other aspects of Tabular and the author’s real life experience shows through. My favorite chapters are Chapter 11 “Data Modeling in Tabular” and Chapter 12 “Using Advanced Tabular Relationships”.

All in all, any serious BI pro willing to learn Tabular should have this book on the shelf… I hope next to mine.

Announcing My New Book

021112_1455_AnnouncingM1My new book, Applied Microsoft SQL Server 2012 Analysis Services (Tabular Modeling), will start shipping in a week with all popular resellers, such as Amazon, B&N, etc. I’ve been working on it for a few months and I’m excited to have it done. While waiting to buy the book, check the following resources to get you started with PowerPivot and Tabular:

– A sample chapter – “Introducing Business Intelligence Semantic Model”

Video demos – I recorded over three hours of video content for selected exercises in the book

An insightful tour that provides an authoritative yet independent view of this exciting technology, this guide introduces the Tabular side of the innovative Business Intelligence Semantic Model (BISM) that promotes rapid professional and self-service BI application development. Business analysts and power users will learn how to integrate data from multiple data sources, implement self-service BI applications with Excel, and deploy them to SharePoint. Business intelligence professionals and database administrators will discover how to build corporate solutions powered by BISM Tabular, delivering supreme performance with large data volumes, and how to implement a wide range of solutions for the entire spectrum of personal-team-organizational BI needs.

Chapter 1: Introducing Business Intelligence Semantic Model

Chapter 2: Personal BI Basics
Chapter 3: Importing Data
Chapter 4: Refining the Model
Chapter 5: Analyzing Data
Chapter 6: Implementing Calculations

Chapter 7: Team BI Basics
Chapter 8: SharePoint Insights
Chapter 9: Managing PowerPivot for SharePoint

Chapter 10: Organizational BI Basics
Chapter 11: Designing Storage and Security
Chapter 12: Managing Tabular Models


Book Review: MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook

8053.MDX%20cookbook%20200%20x%20247.png-200x0MDX is the query language for multidimensional cubes. Many BI practitioners perceive MDX to have a high learning curve probably not that much for the language itself but for the multidimensional concepts you need to master before you can get something out of it. When interacting with the community, I am frequently asked to recommend a MDX book. So far, my recommendations have been Microsoft SQL Server 2008 MDX Step by Step by Brian Smith at el for beginners and MDX Solutions by George Spofford at el for more advanced users.

I will add MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook by Tomislav Piasevoli (MVP – SQL Server) to my recommendation list. This book takes an interesting approach that will be particularly appealing to readers who have already mastered the basics. As its name suggests, the book is a collection of recipes that you can quickly refer to when you tackle a specific MDX requirement. For example, suppose you need to obtain the last date with data in the cube. You flip to Chapter 2: Working with Time and you find a section Getting Values on The Last Date With Data. In it, the author provides the necessary background to explain the concept and provides a 9-step recipe for implementing it. Besides its practical and concise style, another thing that I liked about this book is that it targets the Adventure Works cube so you don’t have to spend time installing and learning other databases. You can hit the ground running by just copying and executing the query.

All in all, this is great book to add to your repertoire of Analysis Services books. The author is an industry-recognized expert who has many years of experience in developing BI solutions with Analysis Services and this becomes evident quickly. Get the recipes!

Book Review – Microsoft PowerPivot for Excel 2010

0020.pp.png-550x0I dare to predict that in a few years after SQL 11 ships, there will be two kinds of BI professionals – those who know the Business Intelligence Semantic Model and those who will learn it soon. By the way, the same applies to SharePoint. What can you do to start on the path and prepare while waiting for BISM? Learn PowerPivot, of course, which is one of the three technologies that are powered by VertiPaq – the new column-oriented in-memory store. This is where the book PowerPivot for Excel 2010 can help. It’s written by Marco Russo and Alberto Ferrari, whose names should be familiar for those of you who have been following Microsoft BI for a while. Both authors are respected experts who have contributed a lot to the community. Stationed in Italy, they run the SQLBI website and share their knowledge via their blog and publications.

This is the second book that I’ve read about PowerPivot – after Professional Microsoft PowerPivot for Excel and SharePoint, which I reviewed in this blog. What I liked about this book is its deep coverage of Data Analysis Expressions (DAX). I specifically enjoyed the following chapters:

Chapter 6: Evaluation Context and CALCULATE – Provides a deep coverage of how DAX measures work. Although DAX is meant to be simpler than MDX, expressions can get complex and this chapter will help you understand how DAX works behind the hood.

Chapter 7: Date Calculations in DAX – Time calculations, such as YTD, QTD, are an important requirement for most BI projects. This chapter goes into details to explain how to implement them and provide workarounds for PowerPivot limitations.

Chapter 9: PowerPivot DAX Patterns – If you wonder whether PowerPivot can do this and that, read this chapter. It demonstrates advanced concepts ranging from ratio, percent of total, standard deviation, ranking over measures, Pareto computations, and more.

Chapter 10: PowerPivot Data Model Patterns – Another gem for addressing popular BI needs, such as banding, courier simulation, and many-to-many relationships.

Although not big in size (370 pages), you will find this book rich in patterns and solutions. What impressed me is that the authors put a great effort to cover not only the PowerPivot basics but to leave no stone unturned when the tool lacks in features. The authors discuss a requirement, approach it from different angles, and provide several implementation approaches. Thus, this book will benefit both beginners and advanced users. An indispensible resource for learning PowerPivot and giving a head start on BISM!

Book Review – Microsoft SQL Server Reporting Services Recipes

This is a review of the latest addition to the long repertoire of Reporting Services books – Microsoft SQL Server Reporting Services Recipes (ISBN: 978-0-470-56311-3). When you learn a new technology you suggest you pick up several books covering this product because every author writes from his own experience. Thus, even books covering the same technology are not competing by completing each other. Some books target novice users, others are more advanced; some have a strong practical connotation while others are designed to be used more as a reference.

In my opinion, this book will benefit mainly readers who have worked with Reporting Services and have already some report authoring experience under their belt. If you fit this profile, you probably find yourself occasionally wanting a report sample that addresses a specific requirement, a tip for better implementation, or a trick to get around a given limitation. Continuing this line of thought, another title for this book might have been “Reporting Services Tips and Tricks”.

Two author names appear on the book cover – Paul Turley and Robert Bruckner – and both names should be familiar to you. Both authors have been heavily involved with Reporting Services since its inception. Paul is a SQL Server MVP and BI architect for Hitachi Consulting. Robert is a technical lead with the Reporting Services team and when he speaks I take a note. Both authors have helped the community tremendously by answering questions on the Reporting Services discussion list, writing and blogging about Reporting Services, and speaking at industry events. Some of the book material has been contributed by other Hitachi employees and SQL Server experts. The foreword is written by Thierry D’hers – Group Program Manager with the Reporting Services team.

The good thing about this book is that it’s not tied to a particular release of Reporting Services. What I particularly liked is that in some cases the authors have provided examples that work with previous editions of Reporting Services, coupled with versions that benefit from the latest features found in 2008 or R2. The book is organized in the following parts:

  • Introduction (80 pages) – Provides the necessary foundation for report authoring.
  • Part 1: Columnar and Grouped Reports (50 pages) – This part covers features that every report author should know, including alternative row colors, dynamic groups, conditional column visibility, and resetting page numbers.
  • Part 2 : BI Dashboards and Elements (30 pages) – This is where you would learn to work with indicators, sparklines, charts, and other elements to author dashboard pages that are becoming increasingly popular. You will also learn interesting tips to integrate your reports with Analysis Services.
  • Part 3: Chart and Gauge Reports (60 pages) – Think of this part as how to get the most out of the Reporting Services data visualization features. It walks you through the cornucopia of chart types that SSRS 2008 made possible, such as histogram, Peretto, bullet graphs, and gauges. It teaches you how to enhance these charts with custom color palettes and exception highlighting.
  • Part 4: Interactive Reporting (40 pages) – Reporting Services has supported interactive features since the beginning, such as drillthrough, toggled visibility, links, bookmarks, etc. This part takes the interactive features to the next level by showing you how to implement conditional linking, breadcrumbs, dynamic pivoting and document maps.
  • Part 5: Integrated Reporting Applications (35 pages) – This part shows you how to build report solutions that go beyond a single report, such as conditional subreports, changing the data behind the report, and embedding reports in .NET applications.
  • Part 6: Enhanced Report Content (100 pages) – This part covers advanced report authoring techniques, including mailing labels, barcodes, custom aggregation, dynamic page breaks, external images, checkbox list and mapping (a new feature of R2).
  • Part 7: Filtering and Parameterization (70 pages) – Report parameters is one area that takes a lot of criticism from the community and it’s one of the weakest links of Reporting Services. The authors present several workarounds for common requirements and limitations, such as advanced filtering, handing multi-valued parameters, top ranking, and custom sorting.
  • Part 8: Custom and Dynamic Data Sources (30 pages) – This section shows advanced techniques for data retrieval, such as obtaining data from a web service, SharePoint lists (2008 and R2), and Dynamics AX.
  • Part 9: Games (15 pages) – Now that you’ve learned all these cool tricks, it’s time to chill out. If Xbox is not cool anymore, Reporting Services is here for you to play and delight your users with games such as hangman, and sea battle. Hallo is coming soon… just kidding.

In a nutshell, pick up this book. You’ll find yourself reaching for it on a regular basis.