Using SQL Server PowerPivot for Excel to Analyze SAP Data

SQL Server Technical Article

Authors: Scott Cameron, 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

Summary: This paper provides instructions and guidance on how to implement six scenarios where PowerPivot for Excel is used to extract data from SAP or SAP BW.

Copyright
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication. This white paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED, OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in, or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation. Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property. Unless otherwise noted, the example companies, organizations, products, domain names, email addresses, logos, people, places, and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, e-mail address, logo, person, place, or event is intended or should be inferred.

2010 Microsoft Corporation. All rights reserved.

Microsoft, Active Directory, Excel, Internet Explorer, PerformancePoint, PowerPivot, SharePoint, SQL Server, Windows, and Windows Server are trademarks of the Microsoft group of companies.

All other trademarks are property of their respective owners.

Contents
Introduction.................................................................................................................................................. 4 Importing data into PowerPivot via an Excel PivotTable connected to SAP BW ............................. 5 Importing data into PowerPivot via SQL Server Reporting Services connected to SAP BW ....... 11 Importing data into PowerPivot from a text file generated using SAP BW Open Hub Service .... 16 Importing data into PowerPivot from an Analysis Services cube connected to SAP BW created using ERP-Link iNet.BI ............................................................................................................................ 19 Importing data into PowerPivot using Theobald Xtract PPV connected to SAP BW ..................... 24 Importing data into PowerPivot using Simplement Data Liberator connected to SAP ERP ......... 28 Conclusion ................................................................................................................................................. 30 Appendix .................................................................................................................................................... 32

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 departments 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 inmemory 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 ITsupported 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.

Importing data into PowerPivot via an Excel PivotTable connected to SAP BW

Many organizations that have SAP use Excel PivotTables to analyze data stored in SAP BW. In this scenario, PowerPivot is used to extract data from these Excel PivotTables. This section explains how to connect an Excel PivotTable to SAP BW and then how to connect PowerPivot to an Excel PivotTable as shown in Figure 1.

Figure 1: Extracting SAP BW data into PowerPivot via Excel PivotTable Excel connects to SAP BW using the SAP BW OLE DB Provider that can be installed when SAP Business Explorer (a component of the SAP Front End GUI) is installed. In order to connect to SAP BW, the SAP BW OLE DB Provider uses information that has been entered in the SAP Logon GUI. Therefore, in order to connect an Excel PivotTable to SAP BW, an entry for the SAP BW system to which you want to connect must exist in the SAP Logon GUI. For more information about connecting Excel PivotTables to SAP BW, including how to connect using 5

Secure Network Communications (SNC), see Connecting to SAP NetWeaver BI with Microsoft Excel 2007 PivotTables and ODBO. After you have configured a system entry in the SAP Logon GUI, you can follow these steps to create an Excel PivotTable using SAP BW data: 1. In Excel, click the Data tab, and then in the Get External Data group, click From Other Sources and From Data Connection Wizard. 2. On the Welcome to the Data Connection Wizard page, click Other/Advanced and then click Next. 3. In the Data Link Properties dialog box, on the Provider tab, click SAP BW OLE DB Provider, and then click Next. 4. In the Data Link Properties dialog box, on the Connection tab: a. In the Data Source box enter the name of SAP BW system that you want to access. The name that you enter here must match one of the values in the Description column of the list on the Systems tab in the SAP Logon application. b. Ensure that the Use a Specific User Name and Password check box is selected. c. Clear the Blank Password check box, and ensure that the Allow Saving Password check box is selected. d. Enter the user name and password that you use to connect to SAP BW. e. Click the All tab. Click Extended Properties, and then click Edit Value. In the Property Value box enter SFC_CLIENT=<SAP Client>, replacing <SAP Client> with the 3-digit SAP client number that you use to connect to SAP BW as shown in Figure 2. Click OK.

Figure 2: Data Link extended properties f. Click the Connection tab and then from the Enter the Initial Catalog to Use list, select $INFOCUBE. The Data Link Properties dialog box should look similar to Figure 3. Click OK.

Figure 3: Data Link Properties dialog box configured to connect to SAP BW 7

5. In the Data Connection Wizard, on the Select Database and Table page, select an InfoCube, and then click Next. 6. On the Save Data Connection File and Finish page: a. Enter values in the File Name and Friendly Name boxes. b. Click Save password in file. A warning will appear because the password will be stored unencrypted in an ODC (Office Data Connection) file. Click Yes. Saving the password in the ODC file will prevent you from having to repeatedly enter the password as you manipulate a PivotTable. You can avoid this issue by connecting to SAP BW using Secure Network Communications (SNC). c. Click Always Attempt to Use this File to Refresh Data, and then click Finish. 7. In the Import Data dialog box, click OK. Note: These steps result in the creation of an ODC file that you can now use to connect to SAP BW. If you want to create another PivotTable, instead of following the previous steps, on the Data tab in the Get External Data group, click Existing Connections. In the Existing Connections dialog box, click the connection that you just created, click Open, and then in the Import Data dialog box click OK. You now have an Excel PivotTable in which SAP BW key figures and characteristics have become PivotTable fields. Because you are going to extract data from this PivotTable into PowerPivot, it is best to add characteristics as row labels and key figures as column values to the PivotTable. After you have added the characteristics and key figures, you will need to further modify your PivotTable prior to extracting the data into PowerPivot. By default, the PivotTable displays totals and subtotals, indents nested row labels in a single column, and doesnt repeat row labels, as shown in Figure 4.

Figure 4: Default PivotTable PowerPivot extracts the data from the PivotTable exactly as it appears. Before you extract the data from the PivotTable into PowerPivot, you will need to flatten the data, so it can be imported by PowerPivot like any other tabular data form, by following these steps: 1. In Excel, on the PivotTable Tools Design tab in the Layout group from the Subtotals list, click Do Not Show Subtotals. 8

2. In the Layout group, from the Grand Totals list, click Off for Rows and Columns. 3. In the Layout group, from the Report Layout list, click Show in Tabular Form and then click Repeat All Item Labels. You will now have a flattened PivotTable as shown in Figure 5.

Figure 5: Flattened PivotTable You can now use these steps to extract data from an Excel PivotTable into PowerPivot: 1. Save and close the Excel file that contains the PivotTable using SAP BW data. 2. Open a new Excel file and on the PowerPivot tab click PowerPivot Window. 3. In the PowerPivot window, on the Home tab, in the Get External Data group, click From Other Sources. 4. In the Table Import Wizard, click Excel File (see Figure 6) and then click Next.

Figure 6: Importing data from an Excel file 5. On the Connect to a Microsoft Excel File page, click Browse, navigate to and select the Excel file with a PivotTable containing SAP BW data, and then click Open. 6. Click Use first row as column headers, and then click Next. 9

7. On the Select Tables and Views page, select the worksheet that contains the PivotTable, and then click Finish. The SAP BW data contained in the PivotTable is extracted into a PowerPivot table.

Comments
In this scenario you should be aware of the following: It is important to understand that getting SAP BW data into PowerPivot is a two-step process. First, data from SAP BW is read into an Excel PivotTable. Second, data is extracted from the Excel PivotTable into PowerPivot. In order to refresh the SAP BW data in PowerPivot, you must refresh both the Excel PivotTable and PowerPivot. At the time of this white papers publication, the SAP BW OLE DB Provider is a 32-bit data provider. When you install the SAP Front End GUI on a computer with a 64-bit version of Windows, the option to install the SAP BW OLE DB Provider will not be available. So you can only create and refresh an Excel PivotTable based on SAP BW data on a computer that is running a 32-bit version of Windows and a 32-bit version of Excel 2010. This means that you will not be able to use Microsoft SharePoint 2010, which only comes in a 64-bit version, to schedule data refreshes of the PivotTable that contains SAP BW data. There is a limit on the number of records that you can load into PowerPivot in this scenario because an Excel worksheet cannot contain more than 1,048,576 rows. You cannot extract data from SAP BW directly into PowerPivot. PowerPivot expects data to be presented as rows and columns and the SAP BW OLE DB Provider is an OLE DB for OLAP provider that doesnt present data in this format. PowerPivot provides two methods for reading data from Excel: o In the PowerPivot Window you can use the Table Import Wizard to import data from an Excel file. o In Excel, on the PowerPivot tab in the Excel Data group, you can use the Create Linked Table button to link Excel data directly to a PowerPivot table. However, this method will not work in this scenario because you cannot link a PowerPivot table to an Excel PivotTable. Unless you install the Desktop Experience feature in Windows Server 2008 or Windows Server 2008 R2, PowerPivot for SharePoint is unable to refresh its data from an Excel file (or a text file) stored in a SharePoint library because there is a dependency on the WebClient service, which is installed as part of the Desktop Experience feature. Many server administrators may be reluctant to install the Desktop Experience because it increases the attack surface of the server and increases the number of software updates that will have to be applied to the server.

10

Importing data into PowerPivot via SQL Server Reporting Services connected to SAP BW
Many organizations have taken advantage of the ability of SQL Server Reporting Services to connect to SAP BW to create a Reporting Services enterprise reporting solution that includes reports based on SAP BW data. Business analysts who wanted to enhance or further analyze the SAP BW data contained in a report have had to export the Reporting Services report to Excel or manually copy the data. Now, users are able to extract data from SQL Server 2008 R2 Reporting Services reports directly into PowerPivot. This section shows how to create a Reporting Services report that consumes SAP BW data and then how to extract data from a Reporting Services report into PowerPivot as shown in Figure 7.

Figure 7: Extracting SAP BW data into PowerPivot via Reporting Services report SQL Server Reporting Services connects to SAP BW using the Microsoft .NET Data Provider 1.1 for SAP NetWeaver Business Intelligence, which is installed when you install SQL Server 2008 R2 server or SQL Server 2008 R2 client tools. The Microsoft .NET Data Provider 1.1 for SAP NetWeaver Business Intelligence is certified with SAP. By default you will be able to access data contained in SAP BW InfoCubes, but if you want to build a report using data from a QueryCube, you will first need to use the SAP Business Explorer Query Designer to allow external access. For more information about how to enable external access to QueryCubes and additional information about how to build Reporting Services reports using SAP BW data, see the MSDN article Using SQL Server 2008 Reporting Services with SAP NetWeaver Business Intelligence. After you have created a Reporting Services report containing SAP BW data, you can extract the data from that report into PowerPivot. A new feature of SQL Server 2008 R2 Reporting Services is that reports are ATOM data feed providers. Because PowerPivot can consume data feeds, it can extract data from a Reporting Services report. There are three basic procedures you must follow to create a Reporting Services report using SAP BW data. The first procedure is to create a report and configure a data source. The second

11

procedure is to create a dataset. The third procedure is to design and publish the report. You can create and configure a data source by following these steps: 1. In Business Intelligence Development Studio (BIDS), create a Report Server project. 2. In BIDS Solution Explorer, right-click the Reports folder, click Add, click New Item, and then add a new report to the project. 3. In the Report Data pane, right-click the Data Sources folder and then click Add Data Source as shown in Figure 8. a. If you do not see the Report Data pane, click the report design pane, and then on the View menu, click Report Data.

Figure 8: Adding a data source to a report 4. In the Data Source Properties dialog box, click Embedded Connection, and then from the Type list, click SAP Netweaver BI as shown in Figure 9. Next, click Edit.

Figure 9: Selecting the Microsoft .NET Data Provider 1.1 for SAP NetWeaver Business Intelligence data provider 5. In the Connection Properties dialog box: a. Enter an XMLA Service URL of the form http://[domain:port]/sap/bw/xml/soap/xmla. b. Use Windows integrated authentication or click Use Basic Authentication, enter a user name and password, and then click Save My Password. c. Enter a SAP Client number and language. d. From the Select or Enter a Catalog Name list, select a specific QueryCube or select $INFOCUBE, and then in the query designer, select a specific InfoCube. Figure 10 shows an example of the Reporting Services connection string editor, 12

which has been configured to connect to SAP BW. It also shows an example of how to add SAP property values to the XMLA Service URL.

Figure 10: SSRS Connection string editor configured to connect to SAP BW in the Connection Properties dialog box 6. Click OK. Now that you have created a Reporting Services data source that connects to SAP BW, you can create a data set, design the report, and publish the report. Whether you are creating a report using SAP BW data, Analysis Services data, or another source that uses XMLA, the procedure will be identical. This is an outline of the steps you need to follow: 1. Create a dataset using the data source you just created. In the query designer select a QueryCube or InfoCube and then drag key figures and characteristics onto the data pane as shown in Figure 11. Click OK twice to close the dataset designer.

13

Figure 11: Adding key figures and characteristics to a dataset query 2. Design the report by dragging a table (or other report item) from the toolbox to the design pane and then drag fields from the dataset onto the table. 3. Publish your report to Report Manager or SharePoint. To publish a report to Report Manager, follow the steps in How to: Set Deployment Properties (Reporting Services) and. To publish a report to SharePoint, follow the steps in How to: Publish a Report to a SharePoint Library. After your report has been published, you are ready to extract the SAP BW data from the Reporting Services report into PowerPivot. In PowerPivot you can do this by entering the URL of the report or by specifying the path to a service document (.atomsvc) that references the reports ATOM data feed. These options are explained in Import Data from a Reporting Services Report. You can also start in Reporting Services and export data to PowerPivot as explained in these steps: 1. In Microsoft Internet Explorer, browse to the Report Manager or SharePoint folder that contains your Reporting Services report and select the report. 2. On the report toolbar, click the Export to data feed button, shown in Figure 12.

Figure 12: Export to Data Feed button 3. In the File Download dialog box, click Open. Excel starts and the PowerPivot Table Import Wizard opens. As shown in Figure 13, the Data Feed Url property is populated with a reference to the Reporting Services report data feed.

14

Figure 13: PowerPivot Table Import Wizard referencing a Reporting Services data feed 4. Click Next to preview the data and apply filters. Click Finish. PowerPivot will import the SAP BW data that was contained in the Reporting Services report.

Comments
In this scenario you should be aware of the following: PowerPivot extracts the dataset used to create each report item, not the rendering of the data. For example, if you choose to export a bar chart, the dataset used to create the bar chart is exported, not the image of the bar chart. Each dataset is extracted into a separate PowerPivot table. If a report has multiple report items, you will be able to choose to extract the data from all items or a subset of the report items. The data from each item is extracted into a separate table in PowerPivot. When you extract data from a parameterized Reporting Services report, you can select parameter values as part of the export process. By creating a report with multiple parameters, you can enable a very flexible way to export data from SAP BW. The ability to export data from a report as an ATOM data feed was introduced in SQL Server 2008 R2 Reporting Services. You can export data from reports created using Business Intelligence Development Studio or Report Builder 2.0 or 3.0, but you cannot export data from a report model directly into PowerPivot. Preliminary performance testing indicates this method does not scale well. Because Reporting Services and ATOM feeds are not meant for unloading large amounts of data, we do not recommend using this method to extract more than 500,000 transactional records, which may take more than 1 hour to complete.

15

Importing data into PowerPivot from a text file generated using SAP BW Open Hub Service
SAP BW OLAP service has a built-in limit and will not return a dataset that contains more than one million rows. You can overcome this limitation by using SAP BW Open Hub Service, which enables the export of large data volumes from SAP BW into text files, relational databases, and third-party ETL tools. For example, in our testing we exported a file containing over 1.2 million rows and 59 fields containing over 43.7 million data values using SAP Open Hub. This section assumes that an SAP administrator has already exported data from SAP BW into a text file using SAP BW Open Hub Service. It shows how to import that text file into PowerPivot. 1. In Excel, on the PowerPivot tab, click PowerPivot Window. 2. In the PowerPivot Window, on the Home tab in the Get External Data group, click From Text. 3. In the Table Import Wizard: a. In the Column separator list select the character in your file that is used to delimit columns. b. Select Advanced, select the appropriate values in Encoding and Locale as shown in Figure 14, and then click OK. i. Setting the column separator and encoding before setting the file path improves the likelihood that the data in your file will appear correctly in the preview pane.

16

Figure 14: Connect to Flat File Advanced Settings c. Click Browse, navigate to the location where you saved the text file you exported using SAP Open Hub, select the text file, and then click Open. i. If your file does not appear in the Open file dialog box, select the files extension from the list in the lower right corner. d. If your file contains columns that you do not want to extract into PowerPivot, clear the check box in the column header. e. Click Finish. After the data has been successfully imported, click Close. 4. The data now appears in the PowerPivot window. You can rename columns by rightclicking a column header and selecting Rename. On the Home tab, in the Formatting group you can set values in Data Type and Format.

Comments
Because SAP BW data is staged in a text file in this scenario, all of the limitations common to importing data from a text file apply: When the SAP Open Hub exports SAP BW data into a CSV file, the metadata (or schema) is saved in a separate file (with file name starting with S_) instead of being the header in the CSV. After you import the data into PowerPivot, you will need to manually rename the columns, or extract the field name information from the metadata file to form a header line and then merge it into the main CSV file. PowerPivot assigns data types based on the values in the first several rows of the file, but it might not always assign the

17

correct data type. For example, dates with the format YYYYMMDD or text fields that contain only digits are imported as integers. You can modify column data types in the PowerPivot window. You can provide metadata for a text file by placing a schema.ini file in the same folder. In a schema.ini file you can specify the column delimiter (by default PowerPivot only recognizes tab stops, semicolons, spaces, colons, and vertical bars [|] as column delimiters), datetime format, each columns name and data type, and other metadata. The appendix contains an example of a schema.ini file. For more information about the use of these files, see the MSDN article Schema.ini File (Text File Driver). Text fields in a text file may contain characters that make it difficult to import the file. In our testing there were fields in the text file that contained the null character, irregular characters, carriage return/line feeds, the character specified as the column delimiter, and quotation marks ("). A single record that contained a carriage return/line feed is imported as two records each with fewer than the correct number of columns. A record with text fields that contained the character specified as the column delimiter, in our case the vertical bar (|), is imported with more than the correct number of columns. In our test data, every recognized column delimiter (tab, semicolon, space, colon, and vertical bar) appeared somewhere in a text field. In this case we could not switch to a different column delimiter. Instead, we had to enclose text fields in quotation marks ("). However, we also had text fields that contained quotation marks, so we first had to replace single instances of a quotation mark (") with two quotation marks ("") and then enclose each text field in quote marks. We also found that enclosing text fields in quotation marks enabled PowerPivot to more successfully import irregular characters. PowerPivot only imports files with .txt, .csv, or .tab extensions. If your file has a different extension, you will have to rename it. If you do not use a schema.ini file to specify a date field that only contains digits of the form YYYYMMDD, you cannot change the data type to date in PowerPivot. Instead, you can use a Data Analysis Expressions (DAX) expression similar to this to create a column with a date data type: =Date(Left('TableName'[ColumnName],4),Mid('TableName'[ColumnNa me],5,2),Right('TableName'[ColumnName],2))

Depending on each individual IT departments SAP BW Open Hub table access policy, you may be able to consider using Open Hub to extract data into Open Hub tables; then you can use PowerPivot to access SAP BW s underlying DB layer and import the data from Open Hub tables.

18

Importing data into PowerPivot from an Analysis Services cube connected to SAP BW created using ERP-Link iNet.BI
ERP-Link (www.erp-link.com) is a third-party provider of SAP-Microsoft interoperability solutions. One component of their iNet Business Optimization Platform is iNet.BI, which provides a .NET data provider, three SQL Server Integration Services (SSIS) data flow sources, and an application that creates SQL Server Analysis Services (SSAS) cubes. Figure 15 illustrates this architecture. In this scenario we used the iNet.BI iNet Data Source View Creator to create an Analysis Services relational OLAP (ROLAP) cube and then connected PowerPivot to this cube. Note that in this scenario you must have access to an Analysis Services server and sufficient permissions to deploy or modify objects on that server.

Figure 15: ERP-Link iNet.BI architecture (MOSS/WSS is Microsoft SharePoint Server/Windows SharePoint Server and BAPI is SAP Business Application Programming Interface.) After you install the iNet.BI software, you will need to configure a connection pool that will contain the information the iNet.BI data provider will use to connect to SAP or SAP BW. You will only need to go through these steps once for each SAP or SAP BW data source. 1. On your Windows desktop, click Start, point to All Programs, point to the iNetServer program group, and then click Administration. 2. From the Connection Pool list select an unconfigured connection pool. 3. In Description, type a short description to help you identify this connection pool. 4. In the Destination String box enter ASHOST=<ApplicationServer> SYSNR=<SystemNumber> where <ApplicationServer> is replaced by the SAP application server name or IP address and <SystemNumber> is replaced by the SAP system number. 19

5. Under Connection mode, click the mode you want the data provider to use to connect to SAP or SAP BW. In our testing we used Shared & anonymous. 6. Under SAP Credentials, enter the 3-digit SAP client number, user name, password, and two-character language code. 7. Click Access Ctrl. In the Access Control dialog box, add any users or groups that will access this connection pool. Click OK. 8. Click Validate. If you are able to successfully connect to SAP, you will see a Validation succeeded message at the bottom of the window. Otherwise, correct any errors and then click Validate again. 9. In the upper left of the window, click Activate. The Connection Server Console should look similar to Figure 16.

Figure 16: Connection Server Console 10. Click Save. 11. Under Remote connection service management, click Restart. After the connection service restarts, click Exit. Now that you have configured a connection string to your SAP BW server, you can create an Analysis Services ROLAP cube using the iNet Data Source View Creator. First, connect to SAP BW: 1. On the Windows desktop, double-click the iNet Data Source View Creator shortcut. 2. In iNet Data Source View Creator, click the Connection Pool String ellipsis button (). 20

3. In the Establish iNet Connector Session to SAP System dialog box connector list, click Use Local Connector if the iNet.Connector service is running on the local computer, or click Use Remote Connector if the iNet.Connector service is running on a different computer. If you selected a remote computer, enter the remote machine name (or IP address). Click Connect. 4. From the Select Pool list, select the connection pool that you created. Click OK. 5. Click the Connect button, which appears just to the right of the connection pool string as shown in Figure 17.

Figure 17: Connect Data Source View Creator to SAP BW iNet Data Source View Creator connects to SAP BW, retrieves the metadata, and then displays a list of SAP BW objects in the Reports, Queries, and Cubes box. If you have a large number of objects in SAP BW, it may take a few moments for the list of objects to appear. The next step is to specify the Analysis Services server where the ROLAP cube will be deployed: 1. In the DB Server Name box, enter the name or IP address of your Analysis Services server, and then click Connect. 2. In the DB Database, DB Data Source, and DB Data Source View lists enter the names of a new database, data source, and data source view. a. iNet Data Source View Creator creates a new Analysis Services database containing a data source configured to connect to SAP BW using the values specified in Connection Pool String and a data source view that contains no tables. 3. In the Reports, Queries, and Cubes box, select the SAP BW object that contains the data you want to import into PowerPivot, and then click Apply. 4. Under the Reports, Queries, and Cubes to Import box, click Import. a. iNet Data Source View Creator creates tables and relationships in the data source view and, and then it creates a ROLAP cube and dimensions. Figure 18 shows the list of tables that are added to a data source view when the $0FIAP_C03 InfoCube is imported into Analysis Services.

21

Figure 18: Importing an InfoCube into Analysis Services Before you query the cube you will need to process it: 1. Start SQL Server Management Studio, connect to the Analysis Services server, expand the Databases folder, right-click the new database, and then click Process. 2. In the Process Database dialog box, click OK. When processing is complete, click Close. You can now query the cube. Because the dimensions and cube use ROLAP storage, all queries are passed through to SAP BW where a result set is generated and passed back through Analysis Services to the application that submitted the query to Analysis Services. Now that you have used iNet Data Source View Creator to create an Analysis Services cube that connects to SAP BW, you can use the standard procedure for extracting data from an Analysis Services cube into PowerPivot: 1. In Excel, on the PowerPivot tab, click PowerPivot Window. 2. In the PowerPivot window, on the Home tab, in the Get External Data group, click From Database, and then click From Analysis Services or PowerPivot. 3. In the Table Import Wizard, enter the name of your Analysis Services server and select the name of the database you created using iNet Data Source View Creator. Click Next.

22

4. On the Specify a MDX Query page, click Design. In the Query Designer add attributes and measures to the design pane and then click OK. a. In the Query Designer you may want to clear the Auto-Execute check box so that a query is not executed every time you add a measure or attribute to the design pane. 5. Click Finish. PowerPivot submits an MDX query to Analysis Services. Analysis Services then queries SAP BW and returns the data to PowerPivot, where it is loaded into a PowerPivot table.

Comments
In this scenario you should be aware of the following: You must have sufficient permissions to deploy objects to an Analysis Services server. In the iNet Data Source View Creator you can specify a new or existing database. If you specify a new database, iNet Data Source View Creator deploys a new database to the server and so you must be a member of the Analysis Services Server Administrator role. If you specify an existing database, iNet Data Source View Creator deploys a new Analysis Services data source, data source view, and cube objects, so you must be a member of a database role that has Full control (Administrator) permission. Installing iNet.BI involves some manual steps, including: o Creating a folder in the file system and copying files into the folder. o Creating a desktop shortcut to the iNet Data Source View Creator executable. o Running a utility to install files into the Global Assembly Cache (GAC). o Manually adding configuration information about the ERP-Link SAP data provider to the DbProviderFactories section of the .NET framework machine.config file. o Modifying the iNet Data Source View Creator and Administrator Console shortcuts or executables so that they execute with administrator privileges. In the PowerPivot window, you may want to rename the columns. When PowerPivot imports data from Analysis Services it names each column by concatenating the dimension, hierarchy, and level name for dimension data, and it adds Measures to the beginning of each measure name. All of this can result in long, unwieldy column names. PowerPivot imports Analysis Services measures as text. You will need to change each column containing measure data to a numeric data type. iNet Data Source View Creator creates a cube and dimensions that use ROLAP storage mode. In this mode, data is not loaded into the Analysis Services database and all queries to the Analysis Services cube are passed through to SAP ERP or SAP BW. You should be aware that when users refresh PowerPivot data their queries may impact SAP performance. You can modify the Analysis Services database so that the cube and dimensions use multidimensional OLAP (MOLAP) storage mode. In this mode you will need to process the database, at which time data is extracted from SAP ERP or SAP

23

BW and stored in the Analysis Services database. Then when users refresh PowerPivot, Analysis Services responds to the query using data in its own storage and does not pass the query through to SAP ERP or SAP BW. The cube that you create with iNet Data Source View Creator can be queried by any application capable of querying an Analysis Services cube, such as Reporting Services, PerformancePoint Services, and Excel PivotTables. In this scenario we used iNet.BI to connect to a SAP BW InfoCube. This scenario also supports connections to SAP ECC query and report objects, BAPIs and RFCs, table views, and SAP BW ODS queries and QueryCubes. We did not test any of these other extraction methods. You can increase the scalability of your solution by using revolving connection pools and interval loading of dimensions. After you configure revolving connection pools, the ERPLink SAP data provider will be able to leverage multiple SAP sessions. Interval dimension loading enables Analysis Services to execute a sequence of queries in which each query retrieves a range of data from SAP. You can learn how to implement revolving connection pools here and interval loading here. Please note that we did not have an opportunity to test either of these scalability methods recommended by ERPLink.

Importing data into PowerPivot using Theobald Xtract PPV connected to SAP BW
Theobald Software (www.theobald-software.com) is a third-party vendor of SAP integration software. Their products include Xtract PPV, software that extracts data from SAP ERP and SAP BW into PowerPivot. Xtract PPV has two components: Xtract PPV Server, which extracts data from SAP ERP and SAP BW and provides it as a data feed to PowerPivot, and Xtract PPV Designer, which is used to configure connections to SAP ERP and SAP BW and to design data extracts. Figure 19 shows the Xtract PPV architecture. In this scenario we used Xtract PPV to extract data from a SAP BW InfoCube and load it into PowerPivot.

24

Figure 19: Xtract PPV architecture After you have installed Xtract PPV you can create a Shared Connection that contains the information Xtract PPV needs to connect to SAP BW. 1. 2. 3. 4. Start Xtract PPV Designer, and then on the Main tab click Connect. On the Server tab in the Administration group, click Shared Connections. In the Shared Connections dialog box, click Add. In the Connections Details dialog box enter: a. A name for the connection. b. A 3-digit SAP client number. c. A username and password. d. A host name or IP address for your SAP BW server. e. An SAP system number. The Connection Details dialog box appears, displaying values similar to Figure 20:

25

Figure 20: Connection Details dialog box 5. Click OK. Now that you have created a shared connection, you can create a SAP BW cube extraction that defines the data feed that is consumed when you load PowerPivot. 1. In Xtract PPV Designer, on the Main tab, in the Design group, click New and then click BW Cube / Query. 2. In the Add Extraction dialog box, enter a name and then click OK. 3. In the Xtract Connection Manager dialog box, make sure that the shared connection you just created appears in the Shared Connection list, and then click OK. 4. In the Xtract BW Cube dialog box, click the binocular button (it has an icon that looks like a pair of binoculars). In the Look Up Cube dialog box, click the binocular button. Xtract PPV retrieves a list of all the InfoCubes from SAP BW. 5. Select an InfoCube, and then click OK. 6. In the Xtract BW Cube dialog box, right-click the key figures and characteristics that you want to add to the data extract, and then click Select For Output. The Xtract BW Cube dialog box should look similar to Figure 21. Click OK.

26

Figure 21: Xtract BW Cube dialog box You are now ready to extract data from SAP BW into PowerPivot: 1. In Xtract PPV designer select the extract that you just created, and then on the Main tab in the Execute group, click Run. 2. In the Run Extraction dialog box, click Run in PowerPivot. Excel starts, the PowerPivot Window opens, and the Table Import Wizard starts, automatically displaying the location of an ATOM service document in the Data Feed Url box, as shown in Figure 22.

Figure 22: Table Import Wizard with Data feed URL 3. Click Next, and then click Finish. 4. After the import operation succeeds, click Close.

Comments
In this scenario you should be aware of the following:

27

If you install Xtract for PowerPivot on a 64-bit version of Windows, you will need to download a 64-bit version of librfc32.dll and copy it to the Windows\System32 folder. The 64-bit version of librfc32.dll is available from the SAP Service Marketplace. Xtract for PowerPivot can also extract data from SAP queries, BAPIs and RFCs, SAP BW hierarchies, Open Hub Service, DeltaQ, and ABAP reports. Dates are imported as text, so you will want to change them to date data type in the PowerPivot window. In the PowerPivot window you may also want to modify the columns so that they have friendly names. You can load data into PowerPivot from multiple extracts in a single step. Create multiple extracts and then enter the Xtract PPV repository URL (for example, http://localhost:8088) in your Internet browser. After PowerPivot starts, each extract appears as a separate table on the Select Tables and Views page of the Table Import Wizard. You can find the port for the Xtract PPV repository in the ListenerPort box in the Server Settings dialog box, which is accessible from the Server tab in Xtract PPV Designer. When a shared connection is used in the data feed, all users importing the feed connect to SAP using the SAP credentials defined in the connection. You can define a different connection for each feed. Feed security can also be configured to pass the credentials of the user to SAP instead of using a central connection; in this case, SAP treats the data the same way as it treats data that the user works with through the SAP GUI. If you choose this option, you have to supply the SAP credentials to the feed URL. Feeds can be protected by assigning Active Directory or built in Xtract for PowerPivot users or groups to a specific feed.

Importing data into PowerPivot using Simplement Data Liberator connected to SAP ERP
Simplement (www.simplement.us) is a third-party provider of a near real-time business intelligence solution that makes SAP ERP data available in SQL Server 2008. Their solution enables replication of a subset of the tables in the SAP ERP database and then replicates that data into a datamart where the data is transformed and exposed in a set of views that provide a user-friendly format. After the data is in SQL Server 2008, it can be easily extracted into PowerPivot or accessed by other Microsoft BI components. Figure 23 shows the Simplement Data Liberator architecture.

28

Figure 23: Simplement Data Liberator architecture Simplement implements their solution by providing a virtual machine or appliance that has all of the Data Liberator components preinstalled. Then they execute scripts to enable replication on the SAP ERP database. After the software is installed and enabled, SAP ERP data is replicated into a SQL Server database. So in this scenario we just needed to extract data from SQL Server into PowerPivot by following these steps: 1. In Excel, in the PowerPivot window, in the Get External Data group, click From Database and then click from SQL Server. 2. In the Table Import Wizard, enter the server name or IP address and the database name of the SQL Server database that contains the replicated and transformed SAP data, and then click Next. 3. Select the option to import data from a list of table and views and then click Next. 4. Select the views that you want to extract data from, preview and apply row or column filters, and then click Finish to begin extracting data into PowerPivot. When the data has been successfully imported, click Close. Now that your SAP BW data has been extracted from SQL Server and loaded into PowerPivot, you can join it to data in other PowerPivot tables and analyze it in PivotTables and PivotCharts.

Comments
In this scenario you should be aware of the following: 29

Simplement can enable replication of SAP ERP data stored in Oracle, IBM DB2, and SQL Server. Simplement replicates both SAP ERP data and security. Any changes made to SAP security will be automatically applied to the SQL Server datamart. Simplement replicates data from the SAP ERP database. It does not provide (nor does it require) access to data stored in SAP BW ODS tables or InfoCubes. The Simplement solution does not access SAP ERP data using the standard SAP APIs. It requires that replication be enabled on the SAP transaction database. Consult your SAP administrators in advance for approval. The Simplement solution is a pure BI solution. They do not offer document management or business process components.

Conclusion
PowerPivot enables 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. By leveraging a familiar Excel environment, nonspecialists can now, independent of the IT departments 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.

This paper demonstrates six technical scenarios where SAP data is extracted into PowerPivot for Excel. Using tools provided by Microsoft and SAP, analysts can load SAP data from Excel PivotTables, Reporting Services reports, or from text files exported from SAP BW Open Hub Services into PowerPivot. They can also take advantage of solutions provided by third-party vendors like Theobald Software, Simplement, and ERP-Link to connect PowerPivot to SAP data. Using these methods, business analysts finally have the power at their fingertips to quickly find the answers to the unique questions that can provide critical differentiation and competitive advantage. For more information: http://www.microsoft.com/sqlserver/: SQL Server Web site http://technet.microsoft.com/en-us/sqlserver/: SQL Server TechCenter http://msdn.microsoft.com/en-us/sqlserver/: SQL Server DevCenter

30

Did this paper help you? Please give us your feedback. Tell us on a scale of 1 (poor) to 5 (excellent), how would you rate this paper and why have you given it this rating? For example: Are you rating it high due to having good examples, excellent screen shots, clear writing, or another reason? Are you rating it low due to poor examples, fuzzy screen shots, or unclear writing?

This feedback will help us improve the quality of white papers we release. Send feedback.

31

Appendix
Schema.ini File
You can provide meta-data for a text file by placing a Schema.ini file in the same folder. An example of two text files and the Schema.ini file that contains their metadata follows: TestData.txt 1~2.50~20101130~Test 1 3~4.25~20101231~Test 2 TestData2.txt 1~20101130~TestData2 A 3~20101231~TestData2 B Schema.ini [TestData.txt] Format=Delimited(~) ColNameHeader=False DateTimeFormat=yyyymmdd Col1="Integer 1" Integer Col2="Currency 2" Currency Col3="Date 3" Date Col4="Text 4" Text [TestData2.txt] Format=Delimited(~) ColNameHeader=False DateTimeFormat=yyyymmdd Col1="Integer 1" Integer Col2="Date 2" Date Col3="Text 3" Text

32