Map data flow from Microsoft PowerPivot to Excel, SharePoint
Robert Sheldon, Contributor
Microsoft
PowerPivot provides an end-to-end system for creating and collaborating on Excel workbooks to
deliver enterprisewide business
intelligence (BI). To take full advantage of these capabilities, users must use PowerPivot in
conjunction with Excel and Microsoft
SharePoint Server, which means installing PowerPivot for Excel on client computers and
PowerPivot for SharePoint on
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
Dig Deeper
-
People who read this also read...
-
This was first published in February 2012
SharePoint
farms.
Though PowerPivot for Excel and PowerPivot for SharePoint are interrelated, they are separate
products and, as such, can be examined individually; this will help to conceptualize how each one
works -- how the primary PowerPivot components fit into the Excel and SharePoint environments and
how data flows within and between those environments. Keep in mind, however, that they’re meant to
be used together for a single BI system.
PowerPivot for Excel
PowerPivot for Excel is a SQL
Server 2008 R2 add-in developed specifically for Excel 2010. The add-in lets you import
millions of rows of data into an Excel workbook from one or more data sources, such as SQL Server,
other relational database management systems, Microsoft Access, text files, SQL
Server Analysis Services (SSAS), SQL
Server Reporting Services (SSRS), Excel workbook files and data feeds.
PowerPivot
for Excel adds an extra window to the Excel workspace that’s used exclusively to manage the
imported data and establish relationships between that data. You can then use the familiar Excel
tools as well as additional PowerPivot tools to manipulate and analyze the information. Figure 1
provides an overview of how the PowerPivot components fit into the Excel environment. The
PowerPivot add-in lets you create PowerPivot workbooks, which are enhanced Excel workbooks that
support the PowerPivot features and host the imported data.
Figure
1. PowerPivot for Excel imports data into the Excel workbook and enhances Excel capabilities.
PowerPivot for Excel includes the PowerPivot add-in assembly and the VertiPaq
engine. The assembly loads PowerPivot into Excel and facilitates communication between
PowerPivot and Excel’s COM Interop, a Component Object Model (COM) assembly that allows COM objects
and .NET objects to interact. As a result, PowerPivot can call into the Excel object model and
respond to events and callbacks raised by Excel.
The VertiPaq engine uses the SSAS object model to process PivotTable and PivotChart queries as
well as Data
Analysis Expressions (DAX) statements. It is the VertiPaq engine that lets PowerPivot import,
filter and sort millions of rows of data within a single workbook.
The VertiPaq engine supports two additional components:
- VertiPaq database module, which uses embedded extensible markup language (XML)
data, referred to as a custom XML part, to maintain the PowerPivot database. The XML part
and data are embedded in the PowerPivot workbook.
- Data cache, which stores temporary files during save operations. This is the only
PowerPivot data that resides outside the PowerPivot workbook.
All the pieces that make up a PowerPivot workbook -- the PowerPivot data and Excel features,
such as PivotTables and PivotCharts -- are embedded in a single XLSX file. After you create the
file, you can post it to a SharePoint server configured with PowerPivot for SharePoint for
collaboration with other users.
PowerPivot for SharePoint
Not surprisingly, PowerPivot’s implementation in SharePoint is a bit more complex than in
Excel. PowerPivot works with Excel Services to let users view, manipulate and modify PowerPivot
workbooks. Figure 2 shows the components that make up a PowerPivot for SharePoint
implementation.
Not surprisingly, PowerPivot’s implementation in SharePoint is a bit more complex than in Excel.
PowerPivot works with Excel Services to let users view, manipulate and modify PowerPivot workbooks.
Figure 2 shows the components that make up a PowerPivot for SharePoint implementation.
Figure
2. PowerPivot for SharePoint components integrate with the Excel Calculations Services in
SharePoint Server.
PowerPivot workbooks are stored within the SharePoint content databases. The Analysis Services
service, which drives SSAS, manages the data within the workbooks and makes it available to other
components. The service also updates the data as part of the data refresh operations, which
are data updates scheduled in the SharePoint Timer service, a Windows service that manages
scheduled SharePoint-related tasks. By scheduling regular data refreshes, business users can ensure
that the workbook data is kept up to date with data from the original data sources.
The Analysis Services service also interfaces with the PowerPivot System service, which
processes requests for workbook data, monitors server health, coordinates load balancing
requests and collects usage data. The PowerPivot System service interfaces with the PowerPivot Web
service to make PowerPivot workbooks available to external applications such as Excel and SSRS.
Excel
Calculation Services retrieves the PowerPivot data through the PowerPivot System service but
accesses other workbook components directly from the SharePoint database. Excel Calculation
Services interfaces with the following services to present the read-only workbooks to clients other
than PowerPivot for Excel and SSRS:
- PowerPivot Gallery, which provides users with a custom SharePoint content library for
PowerPivot workbooks.
- Excel Web Access, which renders PowerPivot workbooks on a Web page and lets users view
and manipulate the data.
- Excel Web Services, which facilitates programmatic access to the PowerPivot
workbooks.
The Excel and PowerPivot components work together to let readers view and manipulate data in a
PowerPivot workbook. However, the only way to create and modify PowerPivot data is by using an
Excel client configured with PowerPivot for Excel. You can use Excel to create a workbook and then
post it to SharePoint or you can retrieve a workbook from the SharePoint site, modify it on your
workstation in Excel and then check the file back in. All other methods of accessing PowerPivot
data through SharePoint are limited to read-only access. Users can still manipulate the data that
they’re viewing, but their changes will not be propagated back to the SharePoint content library.
For that, you need Excel.
Together, Microsoft PowerPivot for Excel and PowerPivot
for SharePoint provide a BI system that lets business users analyze and share data. The
implementations of these two products in their respective environments are, of course, complex, but
you now have an overview of how the primary pieces fit together. From there, you can dig into the
components in-depth or test the products to better understand the full power and benefits of
PowerPivot.
ABOUT THE AUTHOR
Robert Sheldon is a technical consultant and the author of numerous books, articles and training
materials related to Microsoft Windows, various relational database management systems, and
business intelligence design and implementation. Check out his blog, Slipstream.
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation