Integrate SAP data into Microsoft PowerPivot with Xtract PPV

Introduction:

Xtract for PowerPivot (Xtract PPV) is a middleware component to extract data from SAP systems and load it into PowerPivot. Xtract PPV is based on the SAP certified product ERPConnect. It has two components: Xtract Server and Xtract Designer.

The Xtract Server is responsible for the extraction. PowerPivot communicates with the Xtract Server, und this in turn with SAP. Since the Xtract Server has no GUI, you use the Xtract Designer for the design and configuration of the extractions. The Designer directly communicates also with SAP, not to transport data, but just to query metadata during the design process.

Xtract PowerPivot is a set of eight extraction types which make it possible to cover the entire range of demands placed on SAP data extraction:


Xtract Table accesses Tables and Views.
Xtract Query accesses Queries.
Xtract BAPI accesses BAPIs and RFC function modules.
Xtract BW Cube extracts data from SAP BW InfoCubes and QueryCubes (also known as BEX Queries).
Xtract Hierarchy extracts Hierarchies from an SAP BW / BI system.
Xtract Open Hub Service extracts InfoSpokes and OHS destinations.
Xtract DeltaQ extracts Data Sources (OLTP).
Xtract Report executes ABAP Reports.

First Steps: Define an SAP Table Extraction with Xtract for PowerPivot

After the launch of Xtract Designer, the designer must connect to the Xtract Server and the extraction repository. The address is in case of a local Xtract Server the localhost (as in the screenshot), or a different network address, depending on where the Xtract Server is running. The default port 8082 may vary depending on the configuration. If the Xtract Server is running on another machine than the Xtract Designer be sure that the port is not disabled by your firewall. Click Connect to establish a connection with the server.

After successfully connecting the server, the buttons to create and configure extractions are enabled. The list shows all the extractions, which are already created on the server.

Click New to define a new extraction and select the extraction type. In our case we want a table extraction.

Enter a unique name and click OK.

The next form shows the connection manager, with which you define the connection details. The form must be filled in as shown below. Please fill in the text boxes with the logon data (user name, client, language, password). If you need to log on to a single application host, fill in the fields “Host” and “System Number”. If you want log on by load-balancing the message server field, the logon group and SID must be filled. If you don’t know what these parameters in your SAP system environment are, please ask your SAP Basis Components department for help.

If everything is filled out correctly, try to establish a connection to SAP by clicking the Test Connection button.

Now the Xtract Table window opens. Click the binocular button to open a dialog for selecting a table.

Enter the name of the table (wildcards like * are supported) and click the binocular button again to invoke the search.

The editor is now filled in with all the columns from the table. The columns must be marked in the checkbox before they can be extracted.

To define a WHERE statement, please fill in the multi-line text box below the grid. The statement must be conform to the OpenSQL Standard.

Click OK to save the extraction to the repository of the Xtract Server.

Test an Extraction with an Internet Browser

To execute the created extraction, select the corresponding line in the main form and then click Run.

In the following window you can specify details of the execution.
The actual request of the data occurs via URL (see screenshot). To run an extraction it’s sufficient to specify its name. However, it is possible to override some extraction’s behaviors with the help of parameters. If you want to change the maximum number of rows from 0 (unlimited) to 100, change the Operation from Default to Override und set the desired parameter in the value column.

The other parameters format, bg und packagesize are described in section Settings in the product documentation. To run the extraction in the Browser, click Run in Browser. Your default browser opens and displays the data in accordance with the criteria and format.

Please note that passing parameters (rows in our example) is not necessary. It is only necessary to set them if we want to override the default value.

Load your SAP Table Extraction in PowerPivot

The next step is to load the extraction in PowerPivot. Start Excel and go to PowerPivot Tab.

• Click on the button PowerPivot to run PowerPivot.
• Click in Home tab on Data Feeds -> From Other Feeds.

• Type the Xtract Repository URL in the Data Feed Url box. Click on Next.

• The Site Select Tables and View lists all your extractions from the Xtract Repository. Select the extractions that you want and click on Finish.

• The Importing Site shows the number of the transferred rows. Click on Close to finish the import process.

• PowerPivot shows now the loaded data.

Create powerful analysis:

If you want, you can deply your PowerPivot Workbooks to Sharepoint, see http://www.powerpivot.com


Here a link to get more Information about Xtract for PowerPivot http://www.theobald-software.com/en/products.htm and to download a demo version http://www.theobald-software.com/en/download/evaluation.htm.