Do you use ABAP reports to unload data from SAP to your big data or BI platforms? We have good news for you: you can keep this approach but cut report development time using a combination of two SAP add-ons.
What is this post about?
Here we will explain how you can reduce both SAP development costs and time to deliver data extraction programs at the same time by using innovative SAP add-ons.
Don’t go off the beaten path
Often unloading data from SAP involves developing ABAP reports by the SAP IT department. Usually these reports simply create plain text files on the SAP application server and are not used by the SAP end users.
Technically the development follows the same pattern: you have to declare the types and variables you’ll use, then define the selection screen where you can filter the records. Next, you have to implement the data selection using an OpenSQL query and finally, you have to implement the display functions and saving the results to a file. You may notice that basically every step is the same, except for the data selection. Wouldn’t it be possible to automate them all?
The answer is of course yes! Incorporating an SAP add-on called STA Report Generator from our partner company STA Consulting, you can spare writing hundreds of program code lines. The add-on perfectly complements our Data Unfolder, so using the two products together can supercharge your SAP data extraction processes, without actually changing them.
How does this work?
Let’s see how this works using a simple business example. We will export material stock information from SAP using an SQL query.
We have two videos showing the whole process, and below you can find a long explanation with screenshots.
Create an SQL query
1, First let’s start with a basic SQL query: we’ll select all records from table MARD (Storage Location Data for Material), which contains the unrestricted stock information for materials.
2, Next we filter the fields we want to extract, naturally not all are required. For this we can use the Field Selection Wizard feature:
3, You can invoke a database schema map by selecting a table and pressing a button. This will open a popup window that shows all related tables, foreign keys, cardinalities etc.
4, We will also extract data from table MARA (Material master) because we need analytics with breakdown by Material Group, Material Type and Industry Sector. This can be done very easily using the Table Join Assistant, which offers automatic joins for tables which have foreign keys defined in the SAP data tictionary.
5, We also restrict the selected fields from table MARA.
6, Now we add a fixed WHERE condition manually to extract data from year 2010 and later.
7, We additionally define further selection criteria using Dynamic Value Ranges, a convenient feature to define selections: simply select the table to filter and double-click on the fields to invoke a standard SAP “select-option” with search help. Let’s filter for Material Groups and Material Types.
8, Finally, let’s format the select query using the SAP Pretty Printer. We are finished constructing the data selection we need in our external BI platform.
Invoke VirtDB Data Unfolder
Now comes VirtDB Data Unfolder, which can push any ALV output to practically any target system. Let’s quickly export this to Tableau:
1, We define this SQL command as a data source with a single button click.
2, Now we can use all features of VirtDB Data Unfolder, like exporting to CSV, Excel etc. but first lets tackle some very typical problems first.
You can see that the three fields we want to do breakdowns by are internal codes in SAP. Who knows what is Industry Sector ‘1’ or ‘M’? What is Material Group 001? Unless you work with your SAP system every day, I’m sure you don’t. Adding the texts to internal codes is a dead simple but awesome feature of VirtDB Data Unfolder. By choosing the Field Catalog Extension menu item, you get a list of all fields in the ALV list. The great thing is you can even add new fields to the ALV (standard or custom), without programming! Simply tick a few checkboxes to add text to internal code fields (from the text source visible on the right side).
The second issue is related to the field names. Usually 3rd party tools use the internal SAP technical names – usually 5 character long German abbreviations – which are not too user friendly. More advanced tools allow you to use the textual descriptions – if they are maintained in SAP. VirtDB Data Unfolder also allows you to modify the column headings according to your needs.
3, Once you press OK, the ALV is refreshed and all extra data is added automatically. Great, we did not have to investigate the text source for our three important fields and did not have to join the tables to our main query (which is not even possible if text comes from fixed domain values or from a Function Module).
Push the data to a BI tool
4, Let’s do magic! By pressing a single button, all data we’ve gathered is pushed to Tableau. Moreover, it is pushed to Tableau in a license compliant way! We used an existing licensed SAP user and there was no external (RFC or web service) access to the SAP system.
5, Once we are in Tableau, we can leverage all capabilities of the leading BI tool of the planet. All data we need is there, column headings are fine and all data types are the same as in SAP. No more data preparation, ETL jobs etc., just fun with your data viz.
After the great viz is ready, you want to share it with others and make it refreshed automatically. Here comes another tool borrowed from STA Consulting, called STA Report Generator. Actually it is not a separate product but a simple add-on for the OpenSQL Editor. We will now generate an ABAP report based on our SQL query and schedule a periodic extraction job.
Generate a customizable ABAP report
1, Press the Generate Report button and define generation settings in a popup window. You can enter a name for the report, a title and additional objects to be generated (like a DDIC structure and transaction code).
You can also select any field from any table used in the SQL query (even the ones not retrieved) to include on the selection screen of the report to be generated. You can also select the technology to be used for displaying the results. You can even select additional items to be generated, like skeleton class implementations for event handlers etc.
2, You can preview the code that will be generated using a button.
3, Once everything is fine, simply press Generate. Then the standard SAP development process will kick off, you have to define the settings of the program that will be generated, assign it to a package and to a transport request. This is great because it fits to your existing development processes.
4, After a few seconds, the report is ready. Let’s see how it looks!
The selection screen is exactly how we wanted it to look like and we even have a very handy button that allows you to see the number of matching records without actually retrieving them (the field list is replaced with a COUNT( * ) in the SELECT clause).
The end result is an ALV list, so it is very convenient even for end users.
5, Now let’s use VirtDB Data Unfolder to schedule a periodic extraction of this report to Tableau, MS SQL, Hadoop (Hive) and plain CSV files. We press the Datasource Settings button on the ALV toolbar and define the target system we want to push data to.
6, Now press “Schedule extraction” and enter parameters relevant for the target system.
The standard SAP job scheduler is started so you can use a familiar interface to define your periodic data extraction jobs. You can run the extraction every two weeks or every five minutes.
10, Let’s check how our extraction jobs have worked out and see how our SAP data looks like in our target systems.
SAP data extraction is usually a political game in every large organization. SAP teams protect their SAP systems as much as they can, and they also want to keep their developers utilized by writing custom data extraction reports.
The BI teams on the other hand are not able to get the data from SAP themselves. This is mostly due to the SAP licensing policy (indirect access etc.) and also due to the lack of functional SAP knowledge. That is why they are dependant on the SAP team, so they usually have trouble forcing them to use a 3rd party add-on that reduces the authority, importance and manpower of the SAP team.
This is exactly the key point, how these products are different. The STA OpenSQL Editor and Report Generator are actually ABAP developer tools that every SAP developer likes. Pleasing every involved party makes SAP data extraction projects much more easy going.
For more information about the tools, please visit the links below.
STA OpenSQL Editor: https://sta-technologies.com/opensql-editor-for-sap/
STA Report Generator: https://sta-technologies.com/abap-report-generator/