The goal is similar as it was last time to create a mini data mart from operative SAP ERP data to Tableau in a few minutes only – without ABAP coding or IT staff bothered. This time we load Warehouse Stock Levels and some Material Master data to Tableau – but will do the joins and data preparation still on SAP side with Field Catalog Extensions feature of VirtDB Data Unfolder.
Execute Warehouse Stock Details report (transaction code MB52) with your own selections (on material, plant, storage location, etc.). You will see that this report is in the legacy format of SAP (not in ALV grid) so first we have to turn the report into ALV format to be used as a source.
For this go to System menu, VirtDB Add-ons, Program settings.
It will open a new window – you have to hit the green plus sign on the top left to start VirtDB’s data source definition screen.
There are many options – one of the most important is marked with red on this picture – if set to Tableau, VirtDB will set up this report as a datasource for generating Tableau Data Extracts (TDE). Working with other target systems (like MS SQL, Azure, Amazon S3, Hive, Oracle, etc.) will be explained in later posts. Once data source setup is done, you have to go back to System / VirtDB Add-ons menu, but now select Data Unfolder option.
And now you have the Warehouse Stock report in ALV grid, only the plain, raw data is presented (so SAP’s summary rows will not disturb Tableau aggregations) and with all VirtDB Data Unfolder functions already set up and enabled.
To enrich the original ABAP report’s data by adding calculated fields, or to make text description lookups, or do the currency exchange or add data from other tables you should use VirtDB’s Field Catalog Extension features. Last time we have done the data extension / preparation part in Tableau, but in some situations doing these still on SAP side can be a good idea. For example if we want to use the currency exchange rates/functions of SAP or we want to create and publish a single TDE from all the SAP sources – enriching and merging the data in SAP just makes sense.
Select the Field Catalog Extension from the VirtDB menu (under the lightbulb button) and VirtDB will show you the list of all fields available in the report.
VirtDB’s simplest method to enrich data sets (ABAP reports, tables, queries, views, etc) is adding descriptions to code values. If you suspect your data consumers will not understand what Material Group ‘00107’ is, or have no idea on what Industry Sector ‘M’ means – you will like this feature a lot. For fields containing code values VirtDB identifies the corresponding description texts in SAP database and the VirtDB field catalog will show these potential text sources (see the columns marked with red arrow in the field catalog). You only have to check the Read Text checkbox of such fields in field catalog grid and VirtDB will automatically add a new column to the dataset (next to the code field) with the description text included. In the background VirtDB outer joins the description text data sources to the original report’s dataset based on the foreign key relations it has discovered – so if a description exists in SAP for the particular code value – you will see it right next to the code, but if there was no description, still your original dataset remains intact.
Another feature is adding currency conversion using SAP’s built-in currency exchange functionality.
Select the Currency conversion option from the Add new field menu on the top left.
It will open up a window where you can select from what amount field, from which currency field you want to convert to what currency (it is EUR in our case). You also have to select a date (either a date field from the report or the system date) to specify the exact exchange rate, or you can use an exchange rate field that is already in the report. By setting up currency conversion, VirtDB will add a new field to the report (under the field name / description entered) containing the converted amounts in the specified target currency.
In cases where you need to add data to an existing report from an SAP table (and don’t want to code ABAP), you can use the Add new field / Fields from DB function.
You will have to enter the SAP table to read from. In our case we have selected the Material Master (MARA) table to add some material info to the Warehouse Stock Level report.
After specifying the table VirtDB will bring up the list of fields of that table. Select the fields you want to include in the report.
To add this new data to the existing report, you have to map the key fields of the selected table and the existing report. VirtDB will outer join the new data to the existing report’s dataset based on the key fields specified.
In our case the Material Number (MATNR) will be the key to build the outer join.
After you set it up, the new fields from the selected table will appear in the Field Catalog. You can add description texts (by checking the Add text checkbox) or use them in calculations, etc. as any other fields of the original report.
After you hit the green OK button in the right bottom of Field Catalog, VirtDB will refresh the report and add those changes you have specified. The new fields, descriptions, converted amounts, etc. will appear in the grid.
And now the Tableau magic begins – send the data to Tableau Desktop or Server and start building your viz, gain insights right away.
VirtDB will generate a TDE file with all the data from the extended SAP report, of course data types properly converted. It gets automatically downloaded to your desktop by the SAP client itself and opened by your Tableau Desktop.
If you are interested in Data Unfolder