KNOWLEDGE CENTER

Do you spend a long time trying to find out how to analyse SAP data?

KNOWLEDGE CENTER

Do you spend a long time trying to find out how to analyse SAP data?

By Claire Worledge

Do you spend a long time trying to find out how to analyse SAP data?

Over the years, I must have spent on average about 8 hours a week searching on blogs about SAP data. I remember quitting Deloitte and starting as a freelance consultant for some global corporations that wanted analysis of SAP data. And I said to one of my freelance friends “I didn’t know anything at Deloitte!”. 

I had been at Deloitte for 10 years up until then, managing the data analytics department. We had analysed data from lots of different types of systems. I had been doing it day-in-day out and often all night long. I was one of the world’s best experts in ACL (data analytics software) programming by then. But I didn’t know much about SAP!
So that’s when I started discovering really good sites like SE80 and LEANX. Those sites give you great information on tables and fields for SAP.

But I then discovered over the last few years, actually, you also need to understand how the data is organized within those tables. For example, the purchase order data is found in EKKO/EKPO. But actually, there is not only the purchase order data. There is also the purchase requests and the contracts in there. If you don’t realize this fact, then you are going to take too many records and over estimate your value of purchasing.

Another example is the different value fields. The DMBTR field in BSEG has the journal entry value in local currency and the WRBTR field in document currency. Firstly, we have to be careful not to add up the document currency field, otherwise we will be adding up dollars and euros in the same total. Secondly, we have to realise that those fields are not negative or positive. If you want to know if it is negative or positive, you need to refer to the debit/ credit indicator SHKZG.

Other examples are the supplier movements. These are found in the BSAK table. But actually, not all of them. Only the “closed” or “cleared” supplier movements are in the BSAK table. If you want all of the supplier movements, including the open ones, you need to append your data with the BSIK table.

And then there are tables that SAP likes to “fold”. For example, the trial balance table, GLT0. Instead of putting all the data in one column with the month field indicating the month, they put the months in different columns. So, if you want to compare the trial balance table to your general ledger table you need to first unfold the table to get it into the correct structure.

Some more interesting data structures in SAP are “trees” or “hierarchies”. These are found in various places. Just recently, we were making a dashboard on Plant Maintenance. For Plant Maintenance, SAP puts all of the equipment into what they call “Functional locations”. So, to get the functional location, we go to the functional location table, IFLOT. But actually, after a lot of analysing and comparing, we realised that this table is organized as a tree. It means that a record will point to a child record lower in the table, that will point to another child record, that in turn points to another child record. Therefore, before being able to use this table to get the functional location for a particular piece of equipment (and so other information such as criticity, maintenance plan, etc.), we first need to “unravel” the tree.

Other fields that point within a table include the matching key, that is found in the general ledger, the AUGBL+AUGDT field, that enables us to be able to match our invoice lines to our payment lines.

Finally, other interesting challenges in the SAP data structures include dates 'from' and 'to'. This is a concept that we find a lot in HR data. In HR data, we often have tables, such as the actions table, PA0000, that shows the beginning and ending date. If we want to know which action was relevant for an employee at a particular moment in time, we need to take into consideration the date at that moment. For example, what was their salary and position in 2018? It is not enough to only consider their personnel number, we need to consider the beginning and ending date for their position.

The last point about SAP data that we spend a lot of time searching for on blogs is text tables. In SAP, there are many codes: document types, posting keys, transaction codes. For all of these codes, there is a description. This description is sometimes hard-wired in SAP, and sometimes configurable. If it is configurable, it will be in a separate text tables. These text tables are great for including in graphs and detailed tables when we make dashboards, because they enable the reader to straight away see what we are talking about. For example, you could include the GL account (HKONT), but it is much better if you also provide the description of that account from the SKAT table.

And to finish, another final, final point about SAP structures: conditions. SAP likes to have nice complicated ways of helping us to know how to calculate the exchange rate, the price, the discount, the due date. Often, we have many tables to consult, depending on different conditions and algorithms that enable us to know when an invoice is due payment, or what the exchange rate is at the date of the document.

So since leaving Deloitte, myself and my team have been fortunate to work for many companies that use SAP, and have been fortunate to spend many hours per day researching and analysing SAP data.

In the ten years since leaving Deloitte we have created a knowledge base of all of this information and we have put it together in seven book that comes with a tool to get the data out of SAP as well as a platform of 300 dashboards on SAP that covers all areas from purchasing through to treasury. These tools help us to train our staff very quickly and also they help us to help you get a jump start into the world of SAP data analytics.

Thank you for reading!

By Claire Worledge

Claire spent 10 years working for Deloitte, managing the data analytics team. In 2010, she created Aufinia. Aufinia has worked for 50 different companies that use SAP over the years. Today, a lot of their work is for MNCs using SAP who are based in Europe, APAC and the US. Aufinia also works for some major retail companies based in China. 

Claire is a Certified Information Systems Auditor (CISA), Certified Fraud Examiner (CFE) and Certified ACL Data Analyst (ACDA). Aufinia is a global OEM partner with QLIK and also a Galvanize reseller partner for Vietnam and China. Claire has a Masters in IT from University College London, as well as a Bachelors in Biochemistry. 

Want to get instant access to a FREE eBook that outlines the 300 must-have data analytics?

Click below to download our free eBook and we can guarantee it will help you become a recognised leader by starting the transformation of internal control and internal audit!

Do you spend a long time trying to find out how to analyse SAP data?

Over the years, I must have spent on average about 8 hours a week searching on blogs about SAP data. I remember quitting Deloitte and starting as a freelance consultant for some global corporations that wanted analysis of SAP data. And I said to one of my freelance friends “I didn’t know anything at Deloitte!”. 

I had been at Deloitte for 10 years up until then, managing the data analytics department. We had analysed data from lots of different types of systems. I had been doing it day-in-day out and often all night long. I was one of the world’s best experts in ACL (data analytics software) programming by then. But I didn’t know much about SAP!

So that’s when I started discovering really good sites like SE80 and LEANX. Those sites give you great information on tables and fields for SAP.

But I then discovered over the last few years, actually, you also need to understand how the data is organized within those tables. For example, the purchase order data is found in EKKO/EKPO. But actually, there is not only the purchase order data. There is also the purchase requests and the contracts in there. If you don’t realize this fact, then you are going to take too many records and over estimate your value of purchasing.

Another example is the different value fields. The DMBTR field in BSEG has the journal entry value in local currency and the WRBTR field in document currency. Firstly, we have to be careful not to add up the document currency field, otherwise we will be adding up dollars and euros in the same total. Secondly, we have to realise that those fields are not negative or positive. If you want to know if it is negative or positive, you need to refer to the debit/ credit indicator SHKZG.

Other examples are the supplier movements. These are found in the BSAK table. But actually, not all of them. Only the “closed” or “cleared” supplier movements are in the BSAK table. If you want all of the supplier movements, including the open ones, you need to append your data with the BSIK table.

And then there are tables that SAP likes to “fold”. For example, the trial balance table, GLT0. Instead of putting all the data in one column with the month field indicating the month, they put the months in different columns. So, if you want to compare the trial balance table to your general ledger table you need to first unfold the table to get it into the correct structure.

Some more interesting data structures in SAP are “trees” or “hierarchies”. These are found in various places. Just recently, we were making a dashboard on Plant Maintenance. For Plant Maintenance, SAP puts all of the equipment into what they call “Functional locations”. So, to get the functional location, we go to the functional location table, IFLOT. But actually, after a lot of analysing and comparing, we realised that this table is organized as a tree. It means that a record will point to a child record lower in the table, that will point to another child record, that in turn points to another child record. Therefore, before being able to use this table to get the functional location for a particular piece of equipment (and so other information such as criticity, maintenance plan, etc.), we first need to “unravel” the tree.

Other fields that point within a table include the matching key, that is found in the general ledger, the AUGBL+AUGDT field, that enables us to be able to match our invoice lines to our payment lines.

Finally, other interesting challenges in the SAP data structures include dates 'from' and 'to'. This is a concept that we find a lot in HR data. In HR data, we often have tables, such as the actions table, PA0000, that shows the beginning and ending date. If we want to know which action was relevant for an employee at a particular moment in time, we need to take into consideration the date at that moment. For example, what was their salary and position in 2018? It is not enough to only consider their personnel number, we need to consider the beginning and ending date for their position.

The last point about SAP data that we spend a lot of time searching for on blogs is text tables. In SAP, there are many codes: document types, posting keys, transaction codes. For all of these codes, there is a description. This description is sometimes hard-wired in SAP, and sometimes configurable. If it is configurable, it will be in a separate text tables. These text tables are great for including in graphs and detailed tables when we make dashboards, because they enable the reader to straight away see what we are talking about. For example, you could include the GL account (HKONT), but it is much better if you also provide the description of that account from the SKAT table.

And to finish, another final, final point about SAP structures: conditions. SAP likes to have nice complicated ways of helping us to know how to calculate the exchange rate, the price, the discount, the due date. Often, we have many tables to consult, depending on different conditions and algorithms that enable us to know when an invoice is due payment, or what the exchange rate is at the date of the document.

So since leaving Deloitte, myself and my team have been fortunate to work for many companies that use SAP, and have been fortunate to spend many hours per day researching and analysing SAP data.

In the ten years since leaving Deloitte we have created a knowledge base of all of this information and we have put it together in seven book that comes with a tool to get the data out of SAP as well as a platform of 300 dashboards on SAP that covers all areas from purchasing through to treasury. These tools help us to train our staff very quickly and also they help us to help you get a jump start into the world of SAP data analytics.

Want to get instant access to a FREE eBook that outlines the 300 must-have data analytics?

Click below to download our free eBook and we can guarantee it will help you become a recognised leader by starting the transformation of internal control and internal audit!
Copyright © 2021 - Aufinia
For more support contact cjw@aufinia.com
Copyright © 2021 - Aufinia
For more support contact cjw@aufinia.com