xvi Practical PowerPivot & DAX Formulas for Excel 2010
we realized that there was no way users would ever drop Excel. Even if we gave them
something else, they would keep using Excel behind the scene as a shadow application.
All users ask is that whatever tool you give them has one and only one feature: Export
to Excel. That feature is the only common denominator between every single data or
BI software tools on the planet today! So that is when we had our “Ah-ha” moment:
Let’s not fight Excel; let’s embrace it! The way to build the BI sandbox is to provide an
Excel-based environment for users to do what they need, better than the way they do
it with raw Excel. But—and here was the critical moment—let’s make sure that when
users use this environment, it is in an IT-managed workspace or infrastructure.
And here the concept of Managed Self-Service BI was born. Self-Service BI has
been there forever—whether called by that name or not, it has been there. But before
PowerPivot, Self-Service equated to tools enabling users to build tons of disconnected,
uncontrolled, unmanaged islands of data. We wanted the BI Sandbox environment
to be attractive to business users: We wanted to make it super easy to load data from
enterprise and non-enterprise data sources or from structured and non-structured
data sources, to build models using Excel language and formulae, and to build reports
using Excel Pivottable and Pivotchart or any other BI tools (Reporting Services,
PerformancePoint, or any third-party tool that knows how to talk MDX). But we
also wanted to make sure that when this happened, the business users’ work in this
environment would be properly saved and backed up, and the data would be refreshed
regularly from the data sources so that the data didn’t become stale. We also wanted
to make it easy for people to collaborate with this work without duplicating these
workbooks all over the place in the file or email systems; and we also wanted to be able
to provide usage pattern statistics. Now this would make it both valuable to business
users as well as IT. What if IT were able to have knowledge of the existence of these
“special workbooks,” know what data sources they use, and when they are refreshed?
And more importantly, what if IT knew when the refresh failed and why, who is using
the data sources, how big they are, and how many people are using them, so that they
can ensure that these models live on the appropriately provisioned machines, but can
do all of this without necessarily even having access to the content of the workbooks
themselves? Because, after all, these workbooks are the users’ property and none of
IT’s business. Now that is “Managed Self-service BI.” And because of the duality of
this value proposition both to the IT and business users, we needed a new name. Twin
values to the two big population categories in an enterprise: The IT folks and the
business user folks. And that is how the Gemini code name was born: The Twins.
So now you know all about the history and value proposition of this project, which
grew from within a few folks’ minds in 2007 (including Olivier Matrat, who was the
first program manager on the project and who also tech edited this book) to what it
became when we released it with SQL Server 2008 R2 in 2010. This book focuses