Professional Microsoft PowerPivot for Excel and SharePoint
560
Professional Microsoft PowerPivot for Excel and SharePoint
560eBook
Available on Compatible NOOK devices, the free NOOK App and in My Digital Library.
Related collections and offers
Overview
Product Details
| ISBN-13: | 9780470913444 |
|---|---|
| Publisher: | Wiley |
| Publication date: | 05/25/2010 |
| Sold by: | JOHN WILEY & SONS |
| Format: | eBook |
| Pages: | 560 |
| File size: | 17 MB |
| Note: | This product may take a few minutes to download. |
About the Author
Sivakumar Harinath is a Senior Test Manager on the SQL Server Analysis Services team and a coauthor of Professional Microsoft SQL Server Analysis Services 2008 with MDX.
Ron Pihlgren is a 15-year Microsoft veteran and member of the SQL Server Analysis Services team as a senior test manager.
Denny Guang-Yeu Lee is a member of the SQL Customer Advisory team and a coauthor of Professional Microsoft SQL Server Analysis Services 2008 with MDX.
Wrox Professional guides are planned and written by working programmers to meet the real-world needs of programmers, developers, and IT professionals. Focused and relevant, they address the issues technology professionals face every day. They provide examples, practical solutions, and expert education in new technologies, all designed to help programmers do a better job.
Read an Excerpt
Professional Microsoft PowerPivot for Excel and SharePoint
By Sivakumar Harinath Ron Pihlgren Denny Guang-Yeu Lee
John Wiley & Sons
Copyright © 2010 John Wiley & Sons, LtdAll right reserved.
ISBN: 978-0-470-58737-9
Chapter One
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 "managed self-service business intelligence," as implemented in PowerPivot:
* Anyone can easily build his or her own self-service BI applications using tools that they already know, starting with Microsoft Office Excel.
* Self-service BI applications are easy to update and modify. This can be done by the person who built them, even if that person isn't a BI application developer.
* If you work in the data center and are responsible for deploying and managing self-service BI applications, you can manage all your published applications in a common way. You have the tools you need to track usage, administer security, and deploy new hardware in response to the needs of the system.
* Your analytical data remains connected to its source. Refreshing your application from source data is easy, and can even be done automatically by the system.
* You can easily share data, but in a controlled way. Customers of your application can access it over the Web (internal or external) without needing anything other than a Web browser.
Microsoft's previous tag line for its BI products was "business intelligence for the masses." With self-service BI, that tag line can now be expanded to "business intelligence for the masses, by the masses." The Microsoft product that aims to make this possible is PowerPivot.
POWER PIVOT: MICROSOFT'S IMPLEMENTATION OF SELF-SERVICE BI
PowerPivot is made up of two separate components that work together:
* PowerPivot for Excel - PowerPivot for Excel is an Excel add-in that enhances the capabilities of Excel, enabling business analysts and Excel power users to create and edit PowerPivot applications.
* PowerPivot for SharePoint - PowerPivot for SharePoint extends Microsoft Office SharePoint Server to include the capabilities to share and manage the PowerPivot applications that are created with PowerPivot for Excel.
The next section describes what a PowerPivot application looks like.
PowerPivot Applications
At first glance, PowerPivot applications look just like Excel workbooks. And that they are, but they also include something more - PowerPivot data and metadata embedded in the workbook itself. This allows a PowerPivot-enhanced Excel workbook to contain much more functionality than can be contained in a regular Excel workbook that doesn't connect to external data sources. For example, PowerPivot workbooks can contain tables that are much bigger than Excel tables. Excel tables (in Office 2007 and beyond) can contain 1 million rows of data. PowerPivot tables inside an Excel workbook can contain tens or even hundreds of millions of rows of data, as shown at the bottom of Figure 1-1.
The PowerPivot tables in the workbook make up the PowerPivot data mentioned previously. These tables can be joined together and then used as the source data for Excel PivotTables and PivotCharts, which can then be used for analysis and reporting.
PowerPivot applications can be shared among stakeholders, co-workers, management - anyone who wants to view or interact with them. To do this, you publish your PowerPivot workbook to Microsoft Office SharePoint Server. People can then browse and/or interact with your application using either the Excel client or a Web browser. You can also set up PowerPivot to automatically refresh your application from the source data either once or on a regular schedule.
To summarize, you can think of a PowerPivot application as an Excel workbook "on steroids." It gives you all the power of Excel, plus the greater analytical capability necessary to deliver true self-service BI.
Now, let's take a look at the two components that make PowerPivot applications possible.
PowerPivot for Excel
PowerPivot for Excel is the tool you use to create and edit PowerPivot applications. It supports integrating data from various external data sources, enriching that data with custom calculations and adding relationships between tables, as well as using that data to do analysis in Excel using features such as PivotTables and PivotCharts. PowerPivot for Excel is implemented as a managed Excel add-in that provides the user interface for working with PowerPivot data. Figure 1-2 shows the architecture of PowerPivot for Excel.
PowerPivot for Excel also includes the VertiPaq engine, a local, in-process version of the Analysis Services engine in VertiPaq mode (which is discussed later in this chapter). The PowerPivot for Excel add-in communicates with the VertiPaq engine via the traditional Analysis Services interfaces Analysis Management Objects (AMO) and ActiveX Data Objects Multi-Dimensional (ADOMD.NET). The add-in communicates with Excel via its object model using the Visual Studio Tools for Office (VSTO) managed interface. Excel communicates with the in-process VertiPaq engine via the Analysis Services OLEDB provider.
When you are working with PowerPivot for Excel, the PowerPivot data will reside in memory. But when you save your workbook, PowerPivot will store its data and metadata inside the Excel file, as shown in Figure 1-2. The in-memory database will be stored in a section of the file called the Custom Data Part (CDP). The writing of the CDP is done through a public interface that first appeared in Excel 2010. It allows applications to write and retrieve their own data inside an Excel file.
PowerPivot for Excel will also store metadata and workbook settings in XML streams inside the Excel file. This saved metadata allows PowerPivot to attempt to reconstruct a workbook's data model if the CDP data becomes corrupted. If the structure is successfully recovered, you may be able to refresh the workbook's external data, and recover the contents of the workbook.
PivotTables and PivotCharts are the main analytical tools in Excel. The PowerPivot add-in and VertiPaq engine work with Excel to provide you with the capability to use these tools to do your self-service BI analysis.
Microsoft wants to make it as easy as possible to get started with PowerPivot, so it is making this part of PowerPivot available as a free download on the Web. PowerPivot for Excel has the following prerequisites:
* .NET 3.5 SP1 - Installation of this component is not needed on later operating systems like Windows 7, which includes it as part of the operating system itself. If you are installing on an older operating system such as Windows XP or Windows Vista, you will need to install .NET 3.5 SP1. Install this before installing Office 2010.
* Excel 2010 + Office Shared Features - PowerPivot for Excel requires Excel 2010. It will not install on earlier versions of Excel. Also, the architecture of PowerPivot for Excel must match the architecture of Excel itself. If you have 32-bit Excel installed, you must install the 32-bit version of PowerPivot for Excel. If you have 64-bit Excel installed, you must install the 64-bit version of PowerPivot for Excel.
* Platform Update for Windows Vista/Windows Server 2008 - PowerPivot for Excel requires this component if you are running on the Windows Vista or Windows Server 2008 operating systems. You can find more information about this prerequisite at http://support .microsoft.com/kb/971644.
* Drivers for connecting to non-Microsoft data sources - If you want to import data from data sources other than Microsoft data sources that are included with PowerPivot (such as Oracle, Teradata, or DB2), you must acquire and install those drivers and any related client components yourself. They are not included with PowerPivot for Excel. Importing data and more details on the data sources that are supported by PowerPivot will be covered in Chapter 3.
Note that there are also operating system requirements, as shown in Table 1-1.
After you install the necessary prerequisites and PowerPivot for Excel, launch Excel. The first time you launch Excel after installing PowerPivot for Excel, you will see a dialog asking for confirmation that you want to install the add-in. Accept this dialog, and PowerPivot for Excel will load. You will notice that the Excel toolbar now has one new tab called PowerPivot, as shown in Figure 1-3. This tab is your entry point into PowerPivot for Excel.
If you click the PowerPivot Window button on the left side of the ribbon, the PowerPivot Window appears, as shown in Figure 1-4.
(Continues...)
Excerpted from Professional Microsoft PowerPivot for Excel and SharePoint by Sivakumar Harinath Ron Pihlgren Denny Guang-Yeu Lee Copyright © 2010 by John Wiley & Sons, Ltd. Excerpted by permission.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.
Table of Contents
Introduction xxi
Part I: Introduction
Chapter 1: Self-Service Business Intelligence and Microsoft PowerPivot 3
SQL Server 2008 R 2 4
Self-Service Business Intelligence 4
Power Pivot: Microsoft’s Implementation of Self-Service BI 6
PowerPivot Applications 6
PowerPivot for Excel 7
PowerPivot for SharePoint 13
The Analysis Services Engine in VertiPaq Mode 18
Summary 21
Chapter 2: A First Look at PowerPivot 23
PowerPivot for Excel 24
Setting the Stage 24
Setup and Installation 25
Importing Data 27
Analyzing and Enriching Data 32
Creating a PivotTable/PivotChart 39
Sharing Your Data 43
PowerPivot for SharePoint 44
Single-Machine New Farm Install 44
Publishing Your Workbook 51
Viewing PowerPivot Workbooks 51
Viewing the PowerPivot Gallery 52
Summary 53
Part II: Creating Self-Service BI Applications Using PowerPivot
Chapter 3: Assembling Data 57
Importing Data 58
Relational Databases 58
Multi-Dimensional Data Sources 64
Data Feeds 68
Text Files 70
Importing the data 74
Other Ways to Bring Data into PowerPivot 77
Pasting From the Clipboard 77
Linked Excel Tables 78
The Healthcare Audit Application 80
Assembling Data for the Healthcare Audit Application 80
Importing the Main Data Table 80
Importing the Related Tables 82
Adding Data from Other Sources 83
Summary 86
Chapter 4: Enriching Data 87
Exploring the PowerPivot Window 87
Data Refresh 88
Formatting Data 89
Column Operations 91
Relationships 95
Data Analysis Expressions (DAX) 97
Managing Connections 106
Update Import Definition 107
Enriching Data for the Healthcare Audit Application 107
Establishing Relationships 108
Defining DAX Calculations 110
Summary 111
Chapter 5: Self-Service Analysis 113
PivotTables and PivotCharts 113
Single PivotTable 115
Single PivotChart 116
Flattened PivotTable 117
The PowerPivot Field List 118
The New PowerPivot Data Model 119
Automatic Relationship Detection 120
Metadata Refresh 122
Slicers 122
Excel Slicers 123
PowerPivot-Enhanced Slicers 125
DAX Measures 127
Looking at Some Examples 129
PowerPivot and Other Excel Features 131
Cube Formulas 132
Named Sets 132
Analysis in the Healthcare Audit Application 132
The Server Group PivotTable Report 132
The Dashboard Page 137
The “Top 5” Report 144
Summary 146
Chapter 6: Self-Service Reporting 147
Publishing PowerPivot Workbooks 147
PowerPivot for SharePoint 150
PowerPivot Gallery 151
Architecture of PowerPivot for SharePoint 156
PowerPivot Data Refresh 157
Building Ad Hoc Reports 162
Adding Reporting to the SDR Healthcare Application 166
Summary 176
Part III: IT PROFESSIONAL
Chapter 7: Preparing for SharePoint 2010 179
SharePoint 2010 179
Why Not SharePoint “Lite” BI Edition? 180
Excel Services 181
Comparing Excel and Excel Services 182
Excel Services and PowerPivot 182
Key Servers in PowerPivot for SharePoint 184
SharePoint Web Front End (WFE) 184
SharePoint Application Servers (App Servers) 185
SharePoint Databases 186
Key Services in PowerPivot for SharePoint 186
The Analysis Services Service in PowerPivot 187
PowerPivot System Service 189
Services Architecture Workflow Scenarios 191
Excel Client Upload to SharePoint 191
Excel Services Rendering 192
Excel Services Server Action 193
Summary 194
Chapter 8: PowerPivot for SharePoint Setup and Configuration 195
Required Hardware and Software 196
Single-Server Hardware Requirements 196
Multi-Server Hardware Requirements 197
Software Requirements 197
Setup and Configuration 198
Multi-Server Farm Setup 199
Install SQL Server on the SharePoint Database Server 200
Install SharePoint 2010 on the SharePoint WFE 201
Configuring the SharePoint WFE 204
Initial Farm Configuration Wizard on the WFE 207
Install SharePoint 2010 on the SharePoint App Server 209
Configuring the SharePoint App Server 210
Confirm SharePoint Farm Setup 211
Install SQL Server 2008 R2 Analysis Services on the SharePoint App Server 212
Deploy, Configure, and Activate PowerPivot for SharePoint 213
Configuration of the PowerPivotUnattendedAccount 216
Enabling PowerPivot Management Dashboard Data Collection 219
Turn off Excel Calculation Services on the SharePoint WFE 220
Final Configuration Steps 220
Verify the PowerPivot for SharePoint Setup 222
Publishing Your Excel Workbooks 222
Viewing Workbooks in PowerPivot Gallery 224
Viewing Workbooks in Excel Services 224
Additional Verification Steps (Optional) 225
Optional Setup Steps 225
Configuring File Size Limits 225
Turning off the External Data Warning on Data Refresh 226
Integrating Reporting Services 227
Add More Servers to your PowerPivot for SharePoint Farm 230
Summary 232
Chapter 9: Troubleshooting, Monitoring, and Securing PowerPivot Services 233
Troubleshooting Tools 234
Tracing Tool in PowerPivot for Excel 234
SQL Server Profiler 235
ULS Logs 241
Troubleshooting Issues 245
Installation 246
Usage 248
Connectivity 252
Configuration Issues 255
Monitoring PowerPivot Services 261
Monitoring Infrastructure (Server Health) 262
Workbook Activity 264
Data Refresh 265
Reports 267
Dashboard Settings 269
Security 269
Security Services 269
Site Access 270
Infrastructure Access 273
Summary 275
Chapter 10: Diving into the PowerPivot Architecture 277
PowerPivot for Excel Architecture 278
PowerPivot for SharePoint Architecture 282
PowerPivot for SharePoint Services Architecture 282
Diving into Excel Services 284
Diving into PowerPivot Services 286
“Time” to Take a Break 293
Diving into Windows Identity Foundation 294
Summary 299
Chapter 11: Enterprise Considerations 301
Capacity Planning 302
Resources 302
Recommended Hardware Requirements 303
Provisioning 306
SharePoint Topologies 308
SharePoint WFEs 313
SharePoint App Servers 314
Excel Calculation Services 315
PowerPivot System Service 316
SSAS Engine Service 322
SharePoint Databases 323
Sizing 323
Scaling Out 323
Maintenance 324
Remote Blob Store 324
Upgrade and Patching Considerations 324
Upgrading from SharePoint 2007 to 2010 325
Upgrade and Patch Management 326
Upload Considerations 327
Save As Versus Upload 327
LargeChunkFileSize Configuration 330
SharePoint Upload Versus File Copy 330
Impact of Online Edit 332
Summary 335
Part IV: Appendix
Appendix A: Setting Up the SDR Healthcare Application 339
Setting Up the SQL Server Audit Database 339
Setting Up the Database Group Name SharePoint List 340
Setting Up the Client Address to State Report 342
Appendix B: DAX Reference online Only
Index 345