Part II: Visualizing Customer Sales Data in Power Map
Power Map is a great tool for visualizing business data on a map, using the familiar Excel application. With a few steps, the Excel user can create interactive scenes, showing multiple layers of data and dynamically changing data over time on a 3D map. Power Map works with data that has been loaded into a spreadsheet or into a Power Pivot data model. Creating a Power Map on top of your data is pretty straight forward – all you need is location data and values for it. In this blog, we will look at how Theobald Software’s Xtract PPV greatly simplifies the task of bringing SAP business data into Excel and using Power Map to visualize the data. 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 in Power Map. In this sample scenario, we want to show on a map where our customers are located. In addition, we want to show a representation of sales revenue for our customers.
Getting to the source. For our sample scenario, we will need customer data from SAP, including location information. In addition, we will need sales revenue data for those customers. As with other products from Theobald Software, Xtract PPV allows us to access a range of SAP source objects, including SAP Table, RFC Function Module / BAPI, BW Cube, or the more advanced DeltaQ. For this scenario, we will use SAP Table as the source for customer and sales revenue data.
Our first extract is based on SAP table KNA1 which stores customer information. We will only include the fields from table KNA1 that we need for the visualization – Customer Number and City.
Our second extract will be based on SAP table VBAK which stores sales document information. As before, we will only include the fields from table VBAK that we need for the visualization – Sales Document and Net Value.
There is no direct relationship between tables KNA1 and VBAK, so we need create one more extract based on SAP table VBPA which contains partner data for sales documents and will allow us to later create the relationships that we need between the three tables. From table VBPA we will include the fields Document Number, Partner Function and Customer Number. Using a Where Clause in our extract allows us to limit the records returned from table VBPA to customers (or Sold-To Party) only.
With our three extractions defined in Xtract PPV we can now preview the data in the browser, using the Run command. From here we will also copy the URLs for the extraction, in this example: http://localhost:8088/OData.svc/ECCSales,
or simply http://localhost:8080/OData.svc.
Consuming SAP data in Excel. The server component of Xtract PPV will provide the SAP data extractions as an OData feed which can be directly loaded into 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 imports for the three SAP tables with customer, sales revenue and partner data. The data is now available in a Power Pivot data model. We can rename the columns to make them more descriptive.
In the Diagram View of our Power Pivot model, we need to create the relationships between the three tables. We create a relationship between the sales and partner table on field DocumentNumber and another relationship between the partner and customer table on field CustomerNumber.
Creating a Power Map. Now that we have the SAP data that we need in a Power Pivot model, creating the Power Map is simple. We launch Power Map in the workbook containing the Power Pivot model with our SAP data. As a first step in Power Map, we select the City field from the customer table as geography.
Next, we add the NetValue (Sum) and CustomerName (Count – Not Blank) as clustered columns to the map.
The remaining steps are customizing the Power Map to your preference for example by changing the colors or theme.
Data Refresh. Using the Refresh Data button in Power Map, you can update the SAP data on your 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: