Professional Microsoft PowerPivot for Excel and SharePoint E-Book

Sivakumar Harinath

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 538

Veröffentlichungsjahr: 2010

Beschreibung

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.

Leseprobe

Table of Contents

Cover

Title Page

Copyright

Dedication

About the Authors

About the Technical Editor

Credits

Acknowledgments

Introduction

Who This Book Is For

What This Book Covers

How This Book Is Structured

What You Need to Use This Book

Conventions

Source Code

Errata

p2p.wrox.com

Part I: Introduction

Chapter 1: Self-Service Business Intelligence and Microsoft PowerPivot

SQL Server 2008 R2

Self-Service Business Intelligence

Power Pivot: Microsoft’s Implementation of Self-Service BI

Summary

Chapter 2: A First Look at PowerPivot

PowerPivot for Excel

Setting the Stage

PowerPivot for SharePoint

Summary

Part II: Creating Self-Service BI Applications Using PowerPivot

Chapter 3: Assembling Data

Importing Data

Other Ways to Bring Data into PowerPivot

The Healthcare Audit Application

Assembling Data for the Healthcare Audit Application

Summary

Chapter 4: Enriching Data

Exploring the PowerPivot Window

Enriching Data for the Healthcare Audit Application

Summary

Chapter 5: Self-Service Analysis

PivotTables and PivotCharts

The PowerPivot Field List

Slicers

DAX Measures

PowerPivot and Other Excel Features

Analysis in the Healthcare Audit Application

Summary

Chapter 6: Self-Service Reporting

Publishing PowerPivot Workbooks

PowerPivot for SharePoint

Adding Reporting to the SDR Healthcare Application

Summary

Part III: IT Professional

Chapter 7: Preparing for SharePoint 2010

SharePoint 2010

Why Not SharePoint “Lite” BI Edition?

Excel Services

Key Servers in PowerPivot for SharePoint

Key Services in PowerPivot for SharePoint

Services Architecture Workflow Scenarios

Summary

Chapter 8: PowerPivot for SharePoint Setup and Configuration

Required Hardware and Software

Setup and Configuration

Multi-Server Farm Setup

Verify the PowerPivot for SharePoint Setup

Optional Setup Steps

Summary

Chapter 9: Troubleshooting, Monitoring, and Securing PowerPivot Services

Troubleshooting Tools

Troubleshooting Issues

Monitoring PowerPivot Services

Security

Summary

Chapter 10: Diving into the PowerPivot Architecture

PowerPivot for Excel Architecture

PowerPivot for SharePoint Architecture

Summary

Chapter 11: Enterprise Considerations

Capacity Planning

SharePoint WFEs

SharePoint App Servers

SharePoint Databases

Upgrade and Patching Considerations

Upload Considerations

Summary

Part IV: Appendix

Appendix A: Setting Up the SDR Healthcare Application

Setting Up the SQL Server Audit Database

Setting Up the Database Group Name SharePoint List

Setting Up the Client Address to State Report

Index

Professional Microsoft® PowerPivot for Excel® and SharePoint®

Published by Wiley Publishing, Inc. 10475 Crosspoint Boulevard Indianapolis, IN 46256www.wiley.com

Copyright © 2010 by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

ISBN: 978-0-470-58737-9

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA, 01923, [phone], fax [phone]. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, [phone], fax [phone], or online at http://www.wiley.com/go/permissions.

Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Web site is referred to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or Web site may provide or recommendations it may make. Further, readers should be aware that Internet Web sites listed in this work may have changed or disappeared between when this work was written and when it is read.

For general information on our other products and services please contact our Customer Care Department within the United States at [phone], outside the United States at [phone] or fax [phone].

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books.

Library of Congress Control Number: 2010928461

Trademarks: Wiley, the Wiley logo, Wrox, the Wrox logo, Wrox Programmer to Programmer, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used without written permission. Microsoft is a registered trademark of Microsoft Corporation in the United States and/or other countries. All other trademarks are the property of their respective owners. Wiley Publishing, Inc. is not associated with any product or vendor mentioned in this book.

To Shreepriya Sundaravathanen, Praveen Sivakumar, and Divya Sivakumar

— Sivakumar Harinath

To my wife, Nujsaran. Your support is why I can do what I do.

— Ron Pihlgren

To Isabella and Hua-Ping. I can climb because you are there when I fall.

— Denny Lee

About the Authors

Sivakumar Harinath was born in Chennai, India. He has a Ph.D. in Computer Science from the University of Illinois at Chicago. His thesis title was “Data Management Support for Distributed Data Mining of Large Datasets over High-Speed Wide Area Networks.” Harinath has worked for Newgen Software Technologies (P) Ltd; IBM Toronto Labs, Canada; National Center for Data Mining; and the University of Illinois at Chicago. He started as a Software Design Engineer in Test (SDET) at Microsoft in 2002 for the Analysis Services Performance Team, and is currently a Senior Test Manager in the SQL Server Analysis Services team. Harinath has co-authored Professional Microsoft SQL Server Analysis Services 2005 with MDX (Indianapolis: Wiley, 2006), MDX Solutions: With Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase(Indianapolis: Wiley, 2006), and Professional Microsoft SQL Server Analysis Services 2008 with MDX (Indianapolis: Wiley, 2009). His other interests include high-performance computing, distributed systems, and high-speed networking. He is married to Shreepriya and has twins, Praveen and Divya. His personal interests include travel, games/sports (in particular, carrom, chess, racquetball and board games). You can reach him at [email protected].

Ronald Pihlgren is a native of Chicago, Illinois. He has a Bachelor of Science degree in Computer Science from DePaul University. A 15-year veteran at Microsoft, he is currently a Senior Test Manager on the SQL Server Analysis Services team, and has been a part of the project that became PowerPivot since it was in incubation. He was one of the principal technical reviewers for the book Professional Microsoft SQL Server Analysis Services 2008 with MDX (Indianapolis: Wiley, 2009). He has a blog at http://blogs.msdn.com/ronpih. This is his first book as an author.

Denny Lee is a Senior Program Manager with Microsoft based out of Redmond, Washington, in the SQL Customer Advisory Team (SQLCAT) DW/BI Group. He has more than 13 years of experience as a developer and consultant implementing software solutions to complex online transaction processing (OLTP) and data warehousing problems. His industry experience includes accounting, human resources, automotive, retail, Web analytics, telecommunications, and healthcare. He helped create the first online analytical processing (OLAP) services reporting application in production at Microsoft. He co-authored Professional Microsoft SQL Server Analysis Services 2008 with MDX (Indianapolis: Wiley, 2009), Professional Microsoft SQL Server 2000 Data Warehousing with Analysis Services (Indianapolis: Wiley, 2001), and Transforming Healthcare through Information (New York: Springer, 2004). In addition to contributing to the SQLCAT Blog, SQL Server Best Practices, and SQLCAT.com, you can also review Lee’s blog at http://dennyglee.com. Lee specializes in developing solutions for Enterprise Data Warehousing, Analysis Services, and Data Mining. He also has focuses in the areas of Privacy and Healthcare.

About the Technical Editor

John Sirmon is a Senior Escalation Engineer with the SQL Server Analysis Services Support team at Microsoft, based in the Microsoft Regional Support Center in Charlotte, North Carolina. He has worked for Microsoft since March, 2001, and began working with Microsoft SQL Server more than 10 years ago when he began his professional career as a consultant at PricewaterhouseCoopers. He has extensive development experience with Microsoft Visual Studio and all the components of the Microsoft BI Stack. His specialties include Analysis Services performance tuning, Reporting Services, SharePoint integration, and troubleshooting Kerberos authentication. Sirmon has presented on topics ranging from Reporting Services SharePoint Integration to Analysis Services at SQL Server PASS Summits and Microsoft TechReady conferences. He holds a Bachelor of Science degree in Business Administration from the Citadel. Sirmon holds Microsoft Certified Solution Developer (MCSD) and Microsoft Certified Database Administrator (MCDBA) certifications.

Credits

Executive Editor

Robert Elliott

Project Editor

Kevin Shafer

Technical Editor

John Sirmon

Production Editor

Kathleen Wisor

Copy Editor

Christopher Jones

Editorial Director

Robyn B. Siesky

Editorial Manager

Mary Beth Wakefield

Marketing Manager

Ashley Zurcher

Production Manager

Tim Tate

Vice President and Executive Group Publisher

Richard Swadley

Vice President and Executive Publisher

Barry Pruett

Associate Publisher

Jim Minatel

Project Coordinator, Cover

Lynsey Stanford

Compositor

Jeffrey Lytle, Happenstance Type-O-Rama

Proofreaders

Jen Larsen, Word One

Michael Shaw, Word One

Indexer

Johnna VanHoose Dinse

Cover Designer

Michael E. Trent

Cover Image

© Ben Blankenburg/istockphoto

Acknowledgments

The authors thank SQLCAT, the SQL Server Analysis Services team, Excel team, and Excel Services team for their help and contribution to this book. The authors would like to specially thank Rob Collie, Howie Dickerman, Deva Kaladipet Muthukumarasamy, Amir Netz, Sergey Volegov, Dave Wickert, Lee Graber, John Hancock, Marius Dumitru, Jeffrey Wang, Karen Aleksanyan, Ashvini Sharma, Kathy MacDonald, Marcelo Blinder, Bogdan Crivat, Leon Cyril, and Thierry D’Hers for their key contributions and reviews for the book. The authors thank John Sirmon for technical review of the book, as well as Kevin Shafer for helping with all the logistics of technical editing and publishing, and keeping us on track for all the timelines. The authors finally thank Robert Elliott for all his support of this book, from initial proposal to final completion.

Introduction

Self-service business intelligence (BI) is hot! Companies are scrambling to provide easier-to-use tools to bring the benefits of BI to analysts and business decision makers at all levels of organizations. PowerPivot is Microsoft’s entry into this fast-growing market.

Built on top of Microsoft’s popular Office suite, PowerPivot extends Excel and SharePoint to create a self-service BI system that allows creation of applications inside Excel 2010, a server-side component that enhances SharePoint 2010 with the capability to share those applications across the organization, update them with the latest data, and monitor how people are using them.

This book describes all aspects of PowerPivot and shows you how to use each of its major features. It also provides insight into the design and development of this innovative product. By the time you are finished with this book, you will be well on your way to becoming a PowerPivot expert.

Who This Book Is For

This book is for people who want to learn about PowerPivot end to end. You should have some rudimentary knowledge of databases and data analysis. Familiarity with Microsoft Excel and Microsoft SharePoint is helpful, since PowerPivot builds on those two products.

Part I of the book is for those who want an introduction to PowerPivot. It provides background on self-service BI and how PowerPivot fits into the picture. It also includes a quick end-to-end walkthrough of the major features in PowerPivot for those who want to get their feet wet.

Part II is for those who want to understand the client half of PowerPivot — PowerPivot for Excel. This includes Excel power users who work with PivotTables day in and day out, and are curious about the additional capabilities PowerPivot can provide to them, as well as BI professionals who want to understand the details of what PowerPivot is and what you can do with it. If you are a business analyst, this section will be particularly relevant for you.

Part III of the book is for those who want to learn about the server side of PowerPivot. This includes IT professionals who want to learn about how to plan for, deploy, and maintain PowerPivot’s server infrastructure. Since PowerPivot builds on SharePoint, SharePoint administrators who are responsible for adding PowerPivot to their SharePoint farm will find a wealth of information in this section of the book.

What This Book Covers

This book covers the first version of PowerPivot, which ships with SQL Server 2008 R2 and enhances Microsoft Office 2010. It provides an overview of PowerPivot and a detailed look at its two components: PowerPivot for Excel and PowerPivot for SharePoint. It explains the technologies that make up these two components, and gives some insight into why these components were implemented the way they were. Through an extended example, it shows how to build a PowerPivot application from end to end.

How This Book Is Structured

After discussing self-service BI and the motivation for creating PowerPivot, we present a quick, end-to-end tutorial showing how to create and publish a simple PowerPivot application. We then drill into the features of PowerPivot for Excel in detail and, in the process, build a more complex PowerPivot application based on a real-world case study. Finally, we discuss the server side of PowerPivot (PowerPivot for SharePoint) and provide detailed information about its installation and maintenance.

Chapter 1, “Self-Service Business Intelligence and Microsoft PowerPivot,” begins Part I of the book. This chapter describes self-service BI and introduces PowerPivot, Microsoft’s first self-service BI tool. It provides a high-level look at the two components that make up PowerPivot — PowerPivot for Excel and PowerPivot for SharePoint.

Chapter 2, “A First Look at PowerPivot,” walks you through a simple example of creating a PowerPivot application from end to end. In the process, it shows how to set up the two components of PowerPivot (PowerPivot for Excel and PowerPivot for SharePoint), and describes the normal workflow of creating a simple PowerPivot application.

Chapter 3, “Assembling Data,” starts off Part II of the book, and explains how to bring data into PowerPivot from various external data sources. It also introduces the extended example that you will build in this and subsequent chapters.

Chapter 4, “Enriching Data,” shows how to enhance the data you brought into your application by creating relationships and using PowerPivot’s expression language, Data Analysis eXpressions (DAX).

Chapter 5, “Self-Service Analysis,” describes how to use your PowerPivot data with various Excel features, such as PivotTables, PivotCharts, and slicers to do analysis. Chapter 5 also delves further into DAX, showing how to create and use DAX measures.

Chapter 6, “Self-Service Reporting,” shows how to publish your PowerPivot workbook to the server side of PowerPivot (PowerPivot for SharePoint), and make use of its features to view and update PowerPivot reports. It also shows how to use the data in a PowerPivot workbook as a data source for reports created in other tools such as Report Builder 3.0 and Excel.

Chapter 7, “Preparing for SharePoint 2010,” is the first chapter in Part III of the book. It describes the components of SharePoint 2010 that are relevant for PowerPivot, and looks at how PowerPivot for SharePoint interacts with those components.

Chapter 8, “PowerPivot for SharePoint Setup and Configuration,” provides instructions on how to set up and configure a multi-machine SharePoint farm that contains PowerPivot for SharePoint.

Chapter 9, “Troubleshooting, Monitoring, and Securing PowerPivot Services,” gives tips on how to troubleshoot PowerPivot for SharePoint issues. It also shows how to monitor the health of your PowerPivot for SharePoint environment, and discusses relevant security issues.

Chapter 10, “Diving into the PowerPivot Architecture,” describes at a deeper level the architecture of PowerPivot, both for client and server. It also explains the Windows Identity Foundation and discusses the use of Kerberos in the context of PowerPivot for SharePoint.

Chapter 11, “Enterprise Considerations,” talks about common PowerPivot for SharePoint enterprise considerations: capacity planning, optimizing the environment, upgrade considerations, and uploading performance.

Appendix A provides instructions for setting up the data sources that are used to build the SDR Healthcare extended example in Chapters 3 through 6.

Additionally, one “bonus” element is available online at the book’s companion Web site (see the later section, “Source Code”):

Appendix B is a comprehensive DAX reference that describes all the DAX functions and provides code snippets that show how to use them. The content published in Appendix B has been provided by Microsoft.

What You Need to Use This Book

To work through the examples in this book, you will need Microsoft Office Excel 2010 and Microsoft Office SharePoint 2010. You will also need PowerPivot for Excel (which is available as a free download) and PowerPivot for SharePoint (which is included in the Enterprise edition of Microsoft SQL Server 2008 R2). The Contoso BI Demo Database, available from the Microsoft Download Center, is needed for the Chapter 2 tutorial. Data that is needed for the extended BI Healthcare example is available on this book’s www.wrox.com download site. Instructions for installing the data needed for the example are included in Appendix A.

Conventions

To help you get the most from the text and keep track of what’s happening, we’ve used a number of conventions throughout the book.

Boxes with a warning icon like this one hold important, not-to-be-forgotten information that is directly relevant to the surrounding text.

The pencil icon indicates notes, tips, hints, tricks, or asides to the current discussion.

As for styles in the text:

We highlight new terms and important words when we introduce them.We show keyboard strokes like this: Ctrl+A.We show filenames, URLs, and code within the text like so: persistence.properties. We present code in two different ways:

We use a monofont type with no highlighting for most code examples.

We use bold to emphasize code that is particularly important in the present 

     context or to show changes from a previous code snippet.

Source Code

As you work through the examples in this book, you may choose either to type in all the code manually, or to use the source code files that accompany the book. All the source code used in this book is available for download at http://www.wrox.com. When at the site, simply locate the book’s title (use the Search box or one of the title lists) and click the Download Code link on the book’s detail page to obtain all the source code for the book. Code that is included on the Web site is highlighted by the following icon:

Listings include the filename in the title. If it is just a code snippet, you’ll find the filename in a CodeNote such as this:

code snippet filename

Because many books have similar titles, you may find it easiest to search by ISBN; this book’s ISBN is 978-0-470-58737-9.

Once you download the code, just decompress it with your favorite compression tool. Alternately, you can go to the main Wrox code download page at http://www.wrox.com/dynamic/books/download.aspx to see the code available for this book and all other Wrox books.

Errata

We make every effort to ensure that there are no errors in the text or in the code. However, no one is perfect, and mistakes do occur. If you find an error in one of our books, like a spelling mistake or faulty piece of code, we would be very grateful for your feedback. By sending in errata, you may save another reader hours of frustration, and at the same time, you will be helping us provide even higher quality information.

To find the errata page for this book, go to http://www.wrox.com and locate the title using the Search box or one of the title lists. Then, on the book details page, click the Book Errata link. On this page, you can view all errata that has been submitted for this book and posted by Wrox editors. A complete book list, including links to each book’s errata, is also available at www.wrox.com/misc-pages/booklist.shtml.

If you don’t spot “your” error on the Book Errata page, go to www.wrox.com/contact/techsupport.shtml and complete the form there to send us the error you have found. We’ll check the information and, if appropriate, post a message to the book’s errata page and fix the problem in subsequent editions of the book.

p2p.wrox.com

For author and peer discussion, join the P2P forums at p2p.wrox.com. The forums are a Web-based system for you to post messages relating to Wrox books and related technologies, and interact with other readers and technology users. The forums offer a subscription feature to email you topics of interest of your choosing when new posts are made to the forums. Wrox authors, editors, other industry experts, and your fellow readers are present on these forums.

At http://p2p.wrox.com, you will find a number of different forums that will help you, not only as you read this book, but also as you develop your own applications. To join the forums, just follow these steps:

1. Go to p2p.wrox.com and click the Register link.

2. Read the terms of use and click Agree.

3. Complete the required information to join, as well as any optional information you wish to provide, and click Submit.

4. You will receive an email with information describing how to verify your account and complete the joining process.

You can read messages in the forums without joining P2P, but in order to post your own messages, you must join.

Once you join, you can post new messages and respond to messages other users post. You can read messages at any time on the Web. If you would like to have new messages from a particular forum emailed to you, click the “Subscribe to this Forum” icon by the forum name in the forum listing.

For more information about how to use the Wrox P2P, be sure to read the P2P FAQs for answers to questions about how the forum software works, as well as many common questions specific to P2P and Wrox books. To read the FAQs, click the FAQ link on any P2P page.

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 R2Understanding Self-Service Business IntelligenceGetting to know PowerPivotTaking a look at PowerPivot applicationsTaking a look at PowerPivot for ExcelTaking a look at PowerPivot for SharePointTaking 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 serversStream Insight, which supports building applications that do high-volume, complex event processingMaster 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.