Professional Microsoft PowerPivot for Excel and SharePoint

Sivakumar Harinath, Ron Pihlgren, Denny Guang-Yeu Lee

• Publisher: Wrox
• Year: 2010
• Print ISBN: 9780470587379
• eBook ISBN: 9780470913444
• Edition: 1
• Topic: Computer Science
• Subtopic: Data Warehousing
• Index: Computer Science

The next wave of business intelligence, Self-Service BI, seeks to meet the demands of powerful hardware and shorter decision time frames. This book introduces PowerPivot for Excel and SharePoint, showing you how to use this innovative software for self-service analytics and reporting. Authored by key members of the Microsoft team that built the product, this book will guide you through the use of in-memory BI server technology, data analytics eXpressions, and report gallery. The book also discusses how to deploy and manage sandbox servers, and a companion website provides sample reports and applications.

Part I: Introduction

• Chapter 1: Self-Service Business Intelligence and Microsoft PowerPivot
• Chapter 2: A First Look at PowerPivot

Chapter 1: Self-Service Business Intelligence and Microsoft PowerPivot

What’s In This Chapter?

• Reviewing SQL Server 2008 R2
• Understanding Self-Service Business Intelligence
• Getting to know PowerPivot
• Taking a look at PowerPivot applications
• Taking a look at PowerPivot for Excel
• Taking a look at PowerPivot for SharePoint
• Taking a look at the VertiPaq engine

PowerPivot is Microsoft’s entry into the self-service business intelligence (BI) arena. PowerPivot was built with specific goals in mind, and this chapter will explain some of those goals. PowerPivot was also specifically designed not to address certain goals, and this chapter will also discuss those decisions as well. Some dependencies PowerPivot had on other groups and technologies (specifically, Microsoft Office, especially Excel and SharePoint) led to how it was designed and built. This chapter will explore those goals, dependencies, and decisions.

By the end of this chapter you will have a clear idea of the “what and why” of PowerPivot. Subsequent chapters will go into much greater detail on how to work with PowerPivot, and describe its features with the goal of helping you become a professional PowerPivot user who can get the most out of this innovative product.

SQL Server 2008 R2

PowerPivot is included in the R2 release of SQL Server 2008. The “R2” in the name might give you the impression that this release of SQL Server is a minor update to SQL Server 2008. If you thought that, you would be wrong. The 2008 R2 release includes major new functionality, including the following:

• Application and Multi-server Management capabilities, which provide the ability to manage a data environment that includes many servers
• Stream Insight, which supports building applications that do high-volume, complex event processing
• Master Data Services, which helps organizations manage and standardize their enterprise data across applications and systems.
• PowerPivot, Microsoft’s self-service BI offering, which is the subject of this book.

SQL Server 2008 R2 was designed to be a BI-centric release of SQL Server, with a particular focus on self-service BI.

Self-Service Business Intelligence

If you ask different people to define self-service business intelligence (or self-service BI), chances are you will get different answers, depending on who you ask. That’s because self-service BI is a new BI paradigm that is still being defined and created. It has not been around long enough to be standardized in the way that other paradigms like relational databases or even traditional BI has. And yet, it is also not an approach that is starting completely from scratch.

Many of the concepts in self-service BI sprang from earlier BI principles and practices. This book refers to those earlier BI paradigms as “corporate BI.” Self-service BI, then, is something new, but it’s also based on some things that came before. To help explain the relationship between corporate BI and self-service BI, consider another technological advancement that is familiar to many people — the move from command-line interfaces (CLIs) to graphical user interfaces (GUIs) in computer operating systems.

Earlier personal computer operating systems (such as MS-DOS) provided a very simple interface to users: the command line. This interface was text-based, as opposed to graphics-based, and allowed you to type in one line at a time. The response you got back was a single line of text, and perhaps a change in the state of the system that wasn’t visible to you (the command-line user).

There were, of course, ways to get more friendly and capable applications on those operating systems, but in order to do it, you had to build everything yourself. The operating system didn’t provide standardized components like graphical controls, easy-to-use interfaces to the file system, or a common way to talk to devices like printers. In that world, mere mortals (those without detailed computer knowledge and low-level programming skills) would need custom applications built for them in order to work with computers.

Back then, as you can imagine, the majority of people did not see the computer as an integral part of the way they did their jobs. People who could build the custom applications needed to make computers a part of the way people did their work were few and far between. Since you had to build all the functionality your application needed, applications took a long time to build. Even if you had an idea of how a computer application could help you do your job, unless you had the money to hire someone to build it, or had that highly specialized knowledge of how to implement it yourself, you wouldn’t be able to realize your idea of a computer application that could help you do your job.

With the emergence of GUIs, operating systems provided a much richer set of common functionality that applications could make use of without having to implement all the low-level details themselves. For example, instead of having to write your own printer drivers for every printer you wanted to support in your application, you could simply rely on the printer drivers that were provided by the operating system. When coupled with new application-creation tools (such as Visual Basic), that allowed more people with less detailed knowledge and skill to build the applications that people wanted and needed in order to get their jobs done. Then you had the ingredients necessary to make computers an integral part of more and more people’s daily lives. Many more people than before could realize the ideas they had about how computers could help them do their work.

GUIs, and the operating systems that supported them, were a completely new paradigm of how people interacted with computers. And yet, underneath were many of the same components that were there before GUIs came on the scene. They extended, augmented, and standardized what came before and allowed much greater capability for a larger variety of people than their predecessor, the command-line-based operating system.

Self-service BI aims to effect the same sort of paradigm shift in the BI world that modern operating systems did for general computer users. Here is what the state of BI looked like before the self-service concept:

• In order to build BI applications, you had to be a BI developer with highly specialized skills, or have enough money (or clout) to hire one. BI applications were generally custom-built.
• Once your BI application was built and deployed, it could be difficult to change in response to a change in the business situation or customer requirements.
• If you worked in the data center and were responsible for BI applications, chances were that you had to maintain every application in a separate way. Each one may have had its own special requirements for deployment, maintenance, backup, and so on.
• If you were an analyst needing to use data to make your business decisions but were not able to build the BI application you needed, you might have cobbled together data from various sources in an ad-hoc way in order to do your analysis. You probably used spreadsheets to do this. Once you did, your application and its data moved out of the realm of the corporate BI systems and into the wild and wooly world of desktop and laptop systems.
• As a result, your analytical data might have become disconnected from its source and, as a result, outdated as the source data changed. Refreshing your data, when it was possible, could be difficult and time-consuming.
• Since your data now lived in a spreadsheet file, when you shared it, you lost explicit control over it. If all or part of the data was confidential, you couldn’t prevent those you shared it with from sharing it with unauthorized people.

Contrast that situation with Microsoft’s vision of self-service BI, which it calls “manag...

Table of contents

• Cover
• Title Page
• Copyright
• Dedication
• About the Authors
• About the Technical Editor
• Credits
• Acknowledgments
• Introduction
• Part I: Introduction
• Part II: Creating Self-Service BI Applications Using PowerPivot
• Part III: IT Professional
• Part IV: Appendix
• Index