Quick SAP Data mart in Tableau 1.0
For certain analytics you may need operational data from SAP ERP systems. Sometimes you require freedom to select data from SAP in self-service or have to re-design BI data structures to catch up to business process changes / new analytical demands. Big, centralized (and most of the time rigid) data warehouses won’t work well for these scenarios – but small, business managed operational data marts can do the job.
We will make the below example by pairing VirtDB and Tableau Desktop – but you can do this with Tableau Server, other BI tools or database platforms or cloud services as well.
Let’s load frequently used SAP FI (financial) data to Tableau, extend it with Customer master data, make some calculations, a bit of data prep, and build our first viz in a few minutes only.
One of the most frequently used SAP FI datasets is the Account / Vendor / Customer balance report (S_ALR_87012332 – Customer – Vendor – GL Account).
Let’s show the Customer Balances added up to country level on a map (you may notice that country level information is not part of the standard report). And for currency conversions we want to use a special rate, not the one in SAP. How can it be done?
Option 1: You reverse engineer the standard SAP ABAP report – will need ABAP expertise – identify all the source tables (potentially a few dozen), extract all these tables (to Tableau or a database platform), rebuild the ABAP report logic outside of SAP (all the joins, filters, calculations), look-up country (from extracted SAP customer master – KNA1), add your special conversion rate, create the viz and you are done. It will cost you a lot.
Option 2: You customize the standard S_ALR_87012332 ABAP report by adding the above mentioned KNA1 country info – you will need an ABAP developer for that, plus extensive testing and life-cycle management on SAP side to put your customizations into an operating production ERP. It will take ages.
Option 3: Use VirtDB. You can also combine the required info (the standard Balance report and the KNA1 customer master) still on SAP side using the Field Catalog Extension feature of VirtDB (will be detailed in another use case), but for now we will extract the balance and customer datasets separately.
Since the Customer Balance report is in a legacy format of SAP (optimized for ancient printers and monitors, with aggregations, separator lines, etc. included – not usable as datasource for analytic scenarios), we have to turn it into a more usable ALV grid format first. It can be done by choosing “Data Unfolder” in the System menu:
Once the report is in ALV format, we can set it up as a datasource and use the standard VirtDB button with all extraction functions available.
Let’s transfer the data to Tableau Desktop by clicking the “Display in Tableau Desktop” option. By pressing it, VirtDB will generate a Tableau Data Extract (TDE) file from the SAP report content (using SAP metadata to make the proper conversions from SAP to Tableau data types), download it to our desktop (where the SAP client runs) and will open the file in Tableau Desktop.
We can transfer Customer master data (in KNA1 table) to Tableau by using the SE16N table viewer transaction of SAP and the regular VirtDB functions. After opening SAP data in Tableau Desktop, we can save TDE files to our Tableau Repository folder (easier than using from the SAP download folder).
In our example, the custom currency conversion rates come from an MS Excel file – we will save it to the Tableau Repository for simplicity.
We can add the TDEs from SAP and the Excel file one-by-one as Data Sources (new Data Source -> To a File -> More… -> locate the files) of our Tableau report.
Then we have to join the Data Sources to make a unified data set – depending on our needs we may use inner or outer joins.
The Custom Currency Rate dataset can be joined to the Customer Balance dataset using the Currency field and the Customer Balances dataset can be joined to Customers based on Customer field (KUNNR – the Customer Number).
Since the Country information is part of the Customer master data – now we can set up a Country -> City hierarchy to make our map more useful.
In our case the Country field contains ISO standard abbreviations but the City is a free text field in SAP – some of the city names were not properly recognized by Tableau. In such cases, you can go to Map menu -> Edit locations and match values from your data to locations of Tableau’s map.
We have created a custom calculation for our currency rate conversion.
We also used the Industry information from the Customer Master to further detail our analysis of country level customer balances. Here is the result for Europe:
And we can drill down to City level
Conclusion: business users can build a mini data mart in less than an hour using standard or custom SAP reports like Customer Balances, enriching it with dimensions / master data from SAP or some external (Excel/CSV file) information – and build easy to use visualizations on the top.
And the best thing is that you don’t have to stop at personal level, VirtDB can also share this data by publishing to servers like Tableau Server / Online – or to database like MS SQL, MySQL or cloud services on Azure / Amazon.
Happy analyzing in 2017!