If you are a curious reader interested in learning more about SAP data models, you’ve come to the right place!
Hello Medium readers! I’m excited to share some learnings from a recent project where I dived into the complexity of SAP’s data models.
For confidentiality reasons, I’m unable to share all the project details 🤫. However, I’ll discuss a challenge I faced regarding the complexity of SAP data models: what does the SAP data architecture looks like and how does everything fit into a coherent data model that makes sense for business users?
In this project, my primary focus was to integrate data into an analytics/mining platform using SAP business data on procurement processes. As I progressed, several questions arise regarding data modeling and finding my way through the various tables of SAP’s database.
My goal was to create a coherent data model capable of efficiently powering future dashboards, reports, and other analytical outputs planned in the next steps. To achieve this, I had to fully understand SAP data architecture basics, tables mapping and so on. It was not an easy task but I managed to grasp this new knowledge 🚀
1) SAP, a software leader in Entreprise Ressource Planning
As many of you know, SAP is among the leading providers of ERP software, alongside others such as Oracle and Infor. Currently, two generations of SAP ERP software exist: SAP ECC and its successor, SAP S/4 HANA.
The components of both softwares cover all company functions, which are not only inward-looking, but also outward-looking, for instance on the customer side (CRM) and the supplier side (SCM). It is made up of different modules covering all the needs of a company: Sales, Production, Logistics, Finance, Human Resources, After Sales Service.
It is a software package that consolidates all business processes into a single database. It also automatically considers the interdependencies between different processes. In other words, SAP or ERP softwares in general, serve as the heart of business processes, powered by a vast amount of data and transactions.
For data analytics and process optimization projects, we can only say that it’s beneficial to integrate data from SAP and model it for front-end use to provide an end-to-end view of business processes and identify inefficiencies. However, it can be challenging without first understanding how the data is organized in SAP.
2) SAP’s Data and Tables Types
How is SAP’s data organized? In brief, multiple tables in SAP Database store all the data generated by SAP transactions, such as creating a vendor or a purchase order in the case of procurement functions. It’s also important to note that while some tables remain stable over time, others store data capturing specific business events crucial for daily operations. The example of creating a vendor or a purchase order in SAP illustrates this point well.
Let’s consider for instance a company in the automotive sector. This company would have a vendor table listing those they frequently work with to purchase the materials needed to manufacture cars and other items. The creation of a new vendor in this table is a rare event that may only occur a few times per year. This table is referred to as a Master Table, which only stores Master Data.
Meanwhile, creating a purchase order is an operational task. Purchasing teams within the same company frequently use MM (Material Management) transactions to create purchase orders (e.g. millions of purchase orders). Consequently, the table storing all these purchase orders is referred to as a Transaction Table, and this type of data is known as Transaction Data.
Both tables in SAP ECC and S/4 HANA have specific names. The Vendor Master Table is known as the LFA1 table, while Purchase Orders are found in the EKKO Table. For someone working with SAP tables for the first time, it may not be immediately clear what each table represents based on its name. For instance, if I mention the names MCHA, MSEG, BSEG, etc., you would know these are SAP tables, but wouldn’t necessarily know what information they store. Do they contain production orders, financial accounts, or invoices?
That’s precisely why it was crucial for me to conduct research, take notes, and memorize the tables within my project’s scope. More importantly, I needed to understand the relationships and fields used in each table. At this stage, it’s also important to map this data for quick comprehension.
3) Data Mapping of SAP Procurement Tables
An additional challenge that you could face in this type of project is understanding both the data architecture of SAP and how procurement business processes are defined. Thankfully, my previous experience working on process improvement projects with procurement teams was beneficial when it came to procurement processes. Before stepping into the SAP Data Model or Mapping of procurement tables, let’s briefly review the main objects handled in a procurement process.
To simplify, procurement is the process of purchasing something, which could be raw materials, services, tools, etc., from a vendor. This process involves receiving the purchased items, verifying their condition, and then initiating the payment process, often referred to as the Accounting Payable process. However, we will not cover this process in our scope.
Throughout this process, procurement teams undertake various tasks: they validate purchase requisitions, create purchase orders, and send these orders to vendors. Most of these tasks are performed using ERP software, specifically the MM (Material Management) module in SAP. During this flow of activities, key objects or components transition from one step to the next:
- Purchase Requisitions: These are created internally, often by production teams, to inform procurement teams that a specific item needs to be purchased for production purposes.
- Purchase Orders: These are created by procurement teams and include detailed information about the items to be bought, the quantity, the vendor who should receive the PO, and other relevant data.
- Goods Receipt: Upon receiving the goods mentioned in the PO, a goods receipt is provided by the vendor. This receipt is crucial for verifying that the goods received in the warehouse match what was requested in the purchase order.
- Invoice Receipt: This document confirms that the received goods and services are correct and as per the purchase order. It is frequently used to initiate the payment process to the vendor.
Each object in SAP has its associated Transaction Table that stores all the created objects. For example, as previously mentioned, Purchase Orders are stored in the EKKO Table. However, there is another layer of complexity to consider in SAP Tables: the concept of header and item documents.
In SAP, an object or document such as a purchase order has two levels of representation: the header level and the item level. This is because, conceptually, a document is always created with two levels of information. The header contains general and aggregated data, while the items correspond to specific lines within the same document.
To illustrate, imagine you are buying a PS5, The Last of Us 2, and a headset.
Your final order will include a header with your address and order number. The items you’re purchasing, along with their quantity (in this case, one of each item) and price, will be listed separately in line items
Then, a common question is: does the EKKO table represent the header data or the item data? The correct answer is the header data. The item data is stored in a separate table called EKPO.
This core concept is essential as it applies to the majority of SAP objects/documents. Invoices will have one table for header data and another for items, as will goods receipts. However, purchase requisitions are an exception, having only an items table.
To integrate and analyze SAP procurement’s data, I needed to identify the appropriate tables to extract and understand their relationships to build a corresponding data model. The mapping I did to visualize how these data elements interconnect is detailed below:
Clearly, the mapping I have done primarily focuses on the fundamental transaction tables of the procurement process. Additional tables, such as master tables and other transaction tables — for example, those storing changes to purchase orders — could be included in the mapping.
This mapping also highlights the relationships between tables, whether they are one-to-one, one-to-many, or many-to-many. Furthermore, it includes the fields that form the primary key of each object.
Understanding the role of each table and the potential relationships can be time-consuming. However, numerous resources are available to assist, such as those that helped me create this high-level procurement data mapping. If you’re interested in learning more about SAP tables, consider visiting this website. It provides a detailed overview of each SAP table, including primary keys, fields, possible values, etc. You only need to type the name of the table you’re looking for, for e.g. MSEG and you will get more details about the table’s structure and type of stored information:
Finally, if this article got you more curious about SAP data models for other business functions, don’t hesitate to check SAP Community website. You will find some interesting content, for instance the same high-level data mapping for finance functions:
