How To: Extract SAP ERP Data
by Andrew Bryson,
Data Consultant, Eyecademy
Let’s set the scene. You, your clients, or your client’s clients have SAP implemented into the business. You need to pull information from all areas of the system, such as client information, material information, material cost prices, supplier information, sales transactions, client specific pricing, special offers pricing etc… the list goes on. You’d also like to see all this information together in a single view and add data from other sources (e.g. currency exchange rates).
This can all be done in the SAP application; however, it’s going to be both timely and expensive. From several discussions I have had with different clients, it’s going to be too expensive. So, you start looking elsewhere for an alternative solution – how hard can it be, right?
Being someone that has read a lot about SAP but with little hands-on experience, my assumption that simply producing several SQL queries with a few joins to pull data out was somewhat, naïve.
The SAP database consists of a plethora of 80K+ tables all readily named and labelled in German. This can be a slight hinderance to begin with (unless you’re fluent in German) but after some research, you can uncover some useful SAP table relationships online.
However, what you soon will find is that not only are most of the tables redundant, not all tables are created equally in SAP. There are two types specifically: Transparent Tables and Pool Tables.
Transparent Tables are visible and can be queried directly at the database level. Pool Tables are constructed differently through the application layer of the SAP system, and are not visible or available from the database directly. Additionally, another issue is that these Pool Tables are essential for retrieving data across modules as they act as reference tables.
Taking all of that into account, how can we get around this issue?
Here comes the great part. There are connectors available on the market that have the capability to
Additionally, unlike traditional ETL tools, there is now a new generation of self-service tools available that eliminate the requirement for coding or query writing. This means with now some basic knowledge about databases, or any data they’re familiar with, the end user can pull a workflow together using drag and drop functionality.
These tools are intuitive to use, and if you’re familiar with Excel formulas then picking this up will come quite naturally.
Let’s look at a specific example, Material List Prices:
In this workflow we can see the specific components required to pull this data from SAP:
- SAP Logon – Holds your SAP
- SAP Table – SAP tables you can query
- Join – Interface to join your tables
- Summarize – Allows you to perform aggregations, group by etc.
- Select – Allows you to select your output fields and to provide field alias’.
- Browse – Great to get a look at what your output will look like at any level of the workflow
- Output Data – Defines the destination location, including Excel, CSV, SQL Database etc.
The workflow joins 3 tables to produce 2 specific outputs; current material list price and historic material list prices. KONP is a condition table that holds the price values for all conditions and MAKT is the material description table. More importantly, the connector has allowed us to see the pool table, A006. This table holds the material number and the condition ID which pulls MAKT and KONP together. This enables us to now see the current and historical list price for each material item.
This data can be pumped into a database or data warehouse for further processing, or we can filter it as it is so we can look at the latest price for comparison purposes. The output can also be fed into a different workflow to enrich the data further, for example to add supplier information or client information to give you more dimensions to interrogate the data with. You can also use this output in order to update prices and have them written back into SAP using another workflow.
Overall, it’s fair to say that the combination of new generation ETL tools with SAP connectors, is a winner. The tool and the connector accelerate the extraction and blending of SAP data resulting in very substantial time and monetary savings. This is true compared to other “traditional” methods which would require significantly more adoption time to both learn and implement.
The future applications of these tools also look very promising. It opens the door to things like migrating data from SAP to other applications and vice versa. Essentially, this new technology enables us
Eyecademy is Scotland’s leading Data, Analytics & Insights provider, working with enterprise clients across the UK to deliver innovative solutions to business problems.
Over our 11 years of operation, we have built an enviable reputation for excellence in the provision of end-to-end Data Quality, Business Intelligence & Software Development Consultancy Services.