Posted on June 2013
I originally made this post on my sap123.com website, but am migrating all my posts from there to here.
This post was initially written for SAP (ERP) consultants. However, there is nothing SAP related about this post. You can swap out SAP for whatever business systems your company is using.
This post is not about learning SQL but why you need to be using SQL. This post can have many titles:
Lots of standard SAP reports exist for day-to-day reporting. However, as SAP experts we sometimes want to calculate something that can not be done with existing reports. In these cases, the solution is to either get an extract from somewhere or run a few report (or SE16-type downloads) and merge the data in a spreadsheet (like Excel). Excel VLOOKUP is used to merge the different data sources.
In this article, I am arguing that there are an alternative. Rather use a SQL database for SOME of these “ad-hoc” type analysis. See it as another option depending on what you want to do. In some cases Excel is much better than a SQL database. In some cases you can not do it without a database and sometimes you need both – especially if you want nice looking tables and graphs in the end.
What is the benefits of using a SQL database? It can speed-up and simplify repetitive data analysis activities (and increases your income). It also provides functionality that simply would take too long to implement using a spreadsheet.
Increase your income you may wonder? Well if you have knowledge and can use SQL, you could be in a position where you can get to the answer quicker thereby be more marketable than someone that can not do this. Thereby increasing your income!
The purpose of this series of posts is to get you going on this patch.
WARNING: It will take you minimum 1-2 weeks to get set up and know the basics. So don’t give up to soon.
Here is a grouping of some of the tools used for reporting / analysis of SAP information
But if SQL is so great – why don’t most SAP functional consultant use it then?
Here are some of the reasons:
Lets look at the main differences for using a spreadsheet (Excel) or a SQL database for data analysis:
This is how I look at it – If it is once-off analysis and you know exactly what you will be doing – spreadsheets are great! If you are slightly unsure of steps to perform or you may need to re-do it or need to combine different tables / files – use SQL (database).
The first time you do analysis it might take longer but after that – you are flying.
SQL is repeatable and scalable. Excel analyses is not.
Let’s look at some examples where a database (SQL) will work very well.
Let’s look at some examples
_Customer account - fbl5n_
I downloaded results of transaction FBL5N (customer items) from SAP. Then loaded it into a database and called the table ‘fbl5n’ (see part III on how to do this)
Use SQL statement in line 111 to see how many records are in the table
What document types are used? – see the SQL statement in line 112 below
As you can see below GROUP BY statement in SQL is similar as a PIVOT TABLE function in Excel
_Material masters - table MARA_
I loaded a copy of MARA in my database. Line 2557 below is SQL to determine which unit of measure values (fields MEINS) are used
Selecting a database and start using a SQL database
This is the general process / data flow when using database to process SAP data.
Reminder: It will take you minimum 1-2 weeks to get going. After that the benefits are huge. Don’t give up to soon.
Step 1 is covered in this post. Step 2 - 4 will be handled in part II and part III of this post.
For me – the requirements were:
Any of the client-server databases listed below would be good.
Speak to friends and people at work to get an idea which database to use. Most of my examples in these posts are based on MySQL or PostgreSQL.
In order to interact with the database you need to install a server and client or GUI. Here are some of the popular client software to use with MySQL and PostgreSQL.
If you don’t know which database / client software to use, start with MySQL server [database] and SQLyog Community edition (for Windows) or Sequel Pro (for Apple Mac) [client software].
When installing your database – make a note what the default username / password is – you will need it when connecting from client software. After installing the server and client software – test with simple SQL statement to make sure everything is working. Here are some examples.
Example 1 – Using PostgreSQL database with pgAdmin3 (as client software)
Example 2 - PostgreSQL using Apple Terminal (as client software)
Example 3 – MySQL using Sequel Pro (client software)
If you can execute SQL statements on your PC you have made significant progress.
Some practice – see if you can
In part II we will go through detail of using SQL.
Thanks For Following along.Edit