Using SQL Server PowerPivot for Excel to Analyze SAP Data

  • Article

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

Introduction:

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:

  • Integrate data from multiple sources.
  • Analyze large datasets and get fast query response.
  • Perform data transformations and complex calculations.
  • Create and share insightful and compelling reports and data visualizations.

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:

  • Importing data into PowerPivot via an Excel PivotTable connected to SAP BW
  • Importing data into PowerPivot via SQL Server Reporting Services connected to SAP BW
  • Importing data into PowerPivot from a text file generated using SAP BW Open Hub Service
  • Importing data into PowerPivot from a SQL Server Analysis Services cube connected to SAP BW created using ERP-Link iNet.BI
  • Importing data into PowerPivot using Xtract PPV connected to SAP BW
  • Importing data into PowerPivot using Simplement Data Liberator connected to SAP ERP

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.