Part I: Analyzing Employee Data in Power View
Power View is a great tool for visualizing and analyzing data, using the familiar Excel application. With a few steps, the Excel user can create interactive presentations of data, for example in form of charts or maps. Power View works with data that has been loaded into a spreadsheet or into a Power Pivot data model. Creating a Power View on top of your data is relatively straight forward – with the right data in the right format. If you have worked with SAP data in the past, you know that getting to the right data and bringing it into Excel in the right format can be a rather challenging task.
In this blog, we will look at how Theobald Software’s Xtract PPV greatly simplifies this task. With Xtract PPV, SAP data becomes accessible from a variety of SAP source objects and can be directly consumed in Excel.
Visualizing the result. Let’s start with the end result in mind and what we want the user to be able to see and analyze in Power View. In this sample scenario, we will create a visual representation of a company’s global work force. Using Power View, a map will show where employees are located, based on one or more job roles selected.
Connected to the map is a bar chart showing the number of employees in each job role. When the user clicks on a location on the map, the bar chart will show the job categories for that location only. Similarly, when the user clicks on a bar, the map will show the locations for employees in that job category only.
Getting to the source. An SAP ERP system offers standard BAPIs for external systems to access business data, organized around business objects. One of the standard BAPIs in SAP for employee data is BAPI_EMPLOYEE_GETDATA. This BAPI is useful to read employee personal information as well as organizational data, including the employee’s job role. Using the Xtract PPV Designer, we can create a new extraction for the BAPI using a connection to an SAP ERP system. As with other products from Theobald Software, Xtract PPV provides you with a range of different source objects, including SAP Table, RFC Function Module / BAPI (used in this example), BW Cube, or the more advanced DeltaQ.
With the BAPI selected, we can specify the required input parameters (Imports) for the BAPI call, such as DATE. In the Tables section we can select which output table to include in the extraction. For this example we will include the table ORG_ASSIGNMENT by changing the Output Type to Pipeline.
Our goal is to show employee data on a map, however the ORG_ASSIGNMENT table of BAPI_EMPLOYEE_GETDATA does not provide us with geographical information. To add geography data, we will create another extraction for SAP table T001. This table contains company code information, including city and country data which we can map. We can later join the table to the ORG_ASSIGNMENT data from the BAPI in the Power Pivot model.
With both extractions defined in Xtract PPV we can preview the data in the browser, using the Run command. From here we will also copy the URL for the extraction, in this example http://localhost:8080/OData.svc/ECCEmployeeOrg and http://localhost:8088/OData.svc/ECCCompanyCode, or simply http://localhost:8080/OData.svc.
Consuming SAP data in Excel. The server component of Xtract PPV will provide the SAP data extraction as an OData feed which can be directly consumed in Excel. Using Power Pivot, we select From OData Data Feed as our external data source.
Simply paste the URL that we previously copied from Xtract PPV into the Table Import Wizard.
We can complete the table import of the SAP employee and company code data. The data is now available in a Power Pivot data model. We can change column names or hide/delete columns that are not relevant.
In the Diagram View of our Power Pivot model, we need to create a relationship between the two tables on field CompanyCode.
Creating a Power View. Now that we have the right SAP data loaded into a Power Pivot model, creating the Power View is simple. From the company code table we can select the Country field and from the employee org table we can select the EmployeeNo field to be shown in the Power View. We can switch the visualization to Map and display EmployeeNo as # Count of EmployeeNo.
The remaining steps are customizing the Power View to your preference or adding other, connected visualizations, for example a bar chart showing the number of employees by job role.
Data Refresh. Using the Refresh button in Power View, you can update the SAP data in your chart or map without leaving Excel.
Give it a try. With just a few steps outlined in this blog, you can bring SAP data into Power BI. It’s easy to get started, both Theobald Software’s Xtract PPV and Microsoft’s Power BI tools are available for free for a trial period. For further info, check out: