After countless requests from customers and prospects, we finally added SAP BW data extraction as a new feature to our product Data Unfolder. The reason behind so many companies face difficulties extracting hierarchies from SAP is that SAP stores hierarchies in table with a tree structure. It contains Node IDs and Parent Node IDs: this is how the hierarchy is modeled. The problem is that if you export this table to an external tool, it is hard to restructure or consume this information for end users and analysts. You’ll have to write additional logic to make this usable.
SAP BW Data Extraction overview
Data Unfolder uses the SAP Open Hub Service to generate tables from SAP BW sources, which later can be exported using Data Unfolder’s Mass Data Extractor. Furthermore, related text, attribute, and hierarchy tables can also be extracted using the Mass Scheduling feature.
In the step by step guide below we will demonstrate the ease of pushing a BW hierarchy from OHS to MSSQL using Data Unfolder’s new features.
Pushing data from SAP BW to MS SQL Step by step
Step 1. Start transaction RSA1 and create an Open Hub Destination as the first step. The screenshot below shows one we have created. You can see that it generates table /BIC/OHZTEST_BW and puts the data in that table.
Step 2. Maintain the hierarchy that you want to export along with the main BW data source. The screenshot below shows a custom Material sales hierarchy for InfoObject DS_MATNR, which is part of the database table /BIC/OHZTEST_BW (generated by Open Hub Services).
Step 3. In the Data source option section select the OHS destination table you want to extract from SAP BW. In our case it will be /BIC/OHZTEST_BW/.
Step 4. Hit the Mass schedule button which will offer more tables types. In this example, we selected all table types, so the program will look for all related text, attribute and hierarchy tables.
Step 5. In the reference table selection window select all reference tables you want to include. We have selected the text, attribute and hierarchy tables for InfoObject DS_MATNR. Hit enter and process to the Mass scheduling screen.
Step 6. In the Mass scheduling screen, we have to select the tables to extract and specify the target system and SQL options to let Data Unfolder know where and how to extract our SAP BW data. These are going to be “MSSQL“ and “overwrite mode” this time. As you can see, the tables we selected in the previous step are all added here, so we can simply press Execute to schedule all extraction jobs at once.
Step 7. After configuring the mass scheduling options, run transaction “/nsm37” and search for our jobs. Don’t forget to select “Scheduled” job status so our freshly scheduled SAP BW tables appear in the search results.
Step 8. On the Job Overview screen select all extraction jobs we just created and click on Release to schedule the extraction.
Step 9. This time we just select immediate start for all jobs and hit save. If all the jobs are finished, let’s check the MS SQL Server.
Step 10. There are a table and a view created for all the SAP BW tables we pushed out to our MSSQL Server. As usual, tables have the original “technical” column names from SAP, while views have the more descriptive field names from the SAP UI. It is pretty easy to join these together to get a single data source that we can use in Tableau. Alternatively, you can join these views in Tableau, as seen in the video above.
Step 11. You can see that the hierarchy table is now flattened to a tabular format, so it is easy to join this table to the rest of the tables and use in any BI tool.
Step 12: Enjoy working on your SAP BW data! You can see that the Material Group comes from the attribute table, the Material description comes from the text table and the flattened hierarchies are added in Tableau to the dashboard.
If you are interested in extracting SAP BW data with hierarchies