Professional Microsoft PowerPivot for Excel and SharePoint
560
Professional Microsoft PowerPivot for Excel and SharePoint
560Overview
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
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.
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.Part I: Introduction.
Chapter 1 Self-Service Business Intelligence and Microsoft PowerPivot.
Chapter 2 A First Look at PowerPivot.
Part I: Creating Self-Service Bi Applications Using PowerPivot.
Chapter 3 Assembling Data.
Chapter 4 Enriching Data.
Chapter 5 Self-Service Analysis.
Chapter 6 Self-Service Reporting.
Part II: IT Professional.
Chapter 7 Preparing for SharePoint 2010.
Chapter 8 PowerPivot for SharePoint Setup and Configuration.
Chapter 9 Troubleshooting, Monitoring, and Securing PowerPivot Services.
chaPter 10 Diving into the PowerPivot Architecture.
Chapter 11 Enterprise Considerations.
Part IV: Appendix.
Appendix A: Setting Up the SDR Healthcare Application.
Appendix B: DAX Reference.
Index.