Writers: Scott Cameron (Hitachi Consulting), Chunhui Zhu, Denny Lee
Technical Reviewers: John Hancock, Lindsey Allen, Hermann Daeubler, Juergen Thomas, Kasper de Jonge
Vendors: The authors wish to thank the vendors who assisted us with this project: Simplement, ERP-Link, Theobald, and Simba.
Published: January 2011
Applies to: SQL Server PowerPivot for Excel
SAP is one of the most widely deployed and capable ERP systems. Because SAP is the central nervous system for many organizations, accessing the data that is captured and lives in SAP is essential to effectively managing the business. As with many ERP systems, it can sometimes be challenging to get data out of SAP applications because those are optimized for transacting and capturing data. SAP has a number of BI tools, but very few of the tools allow end users to work directly with the data in a familiar environment like Microsoft Excel and easily integrate other non-SAP data sources.
PowerPivot for Excel 2010 and PowerPivot for SharePoint enable SAP business users to engage in managed self-service business intelligence. This includes empowering more information workers to integrate and analyze SAP and non-SAP data so that the cycle of analysis can be accelerated. Managed self-service BI means that business analysts can now, independent of the IT department’s help:
With PowerPivot, analysts can extract data from SAP or SAP BW and integrate and enhance it with data from text files, Excel files, Microsoft SQL Server Reporting Services reports, ATOM data feeds, or data from many other OLE DB and ODBC sources. Because PowerPivot enables analysis within Excel of large datasets (tens to hundreds of millions of rows) by using an in-memory BI engine that takes advantage of column-based compression, analysts can extract large amounts of data from SAP or other sources. Even with such large datasets, analysts will still get fast response times as they manipulate and query data. PowerPivot includes Data Analysis Expressions (DAX), which enables users to use Excel formula-like expressions to transform data and create complex calculations. The data modeling capabilities of PowerPivot enable users to join data from multiple sources. Because PowerPivot is an Excel add-in, information workers can use Excel PivotTables, PivotCharts, and other data analysis and visualization tools in Excel to perform even more sophisticated analysis on very large datasets. You can learn more about the capabilities of PowerPivot at the PowerPivot website (www.powerpivot.com).
This paper reviews six scenarios where PowerPivot is used to analyze SAP ERP or SAP BW data:
The goal of this paper is not to advocate for one scenario over another, but rather to provide guidance on how to implement the scenario and list some of the possible pitfalls to help you avoid them. The first two scenarios, importing data into PowerPivot from a Reporting Services report and from an Excel PivotTable, are not the most efficient way to move data from SAP ERP/BW to PowerPivot. These scenarios are examples of self-service BI where a business user combines data from already existing reports. The other scenarios represent a mix of IT-supported BI and self-service BI. In these scenarios IT creates infrastructure that provides access to SAP ERP or SAP BW data and then business users load, transform, integrate, and analyze the data.
We do not discuss aspects of licensing, certification or support with or by SAP of the different approaches shown in this paper. The paper focuses only on the technical aspects of different solutions that SAP, Microsoft and some third-party applications offer.
To continue reading, please download the whitepaper Using SQL Server PowerPivot for Excel to Analyze SAP Data.
Pingback from Twitter Trackbacks for Using SQL Server PowerPivot for Excel to Analyze SAP Data - Whitepapers [sqlcat.com] on Topsy.com
Pingback from Using SQL Server PowerPivot for Excel to Analyze SAP Data « Denny Lee
Pingback from SAP Integration – Part 1: Microsoft BI on top of SAP BW « Data Warehouse Junkie
Hey Denny - great info here. Any updates to this blog (small or large)?
Thanks Mark - right now, that's the latest info, eh?! i.e. not yet ;)