Learn SQL: Speed-up business analysis of SAP data and increase your income
This is part 1 of the 3 part series.
Part 1 - Why SAP Consultants should know and use SQL
Part 2 - Tips and tricks and SQL cheat sheet
Part 3 - Getting SAP data in and out of your database
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.
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:
• Not everyone is interested in data analysis to understand how processes / businesses work
• Using a database and SQL is more difficult than just using a spreadsheet like Excel.
• For the average consultant (doing average work) a spreadsheet is good enough!
• It takes time and effort to learn this new skill – two weeks minimum.
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
See Part II and Part III of this series for more about the use of databases for SAP data analysis
Selecting a database and start using a SQL database
This is the general process / data flow when using database to process SAP data.
It will take you minimum 1-2 weeks to get going. After that the benefits are huge. Don’t give up to soon.
1. Install a SQL database on your PC
2. Learn basics of SQL
3. Work out best way to get your data in database
4. Use it
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:
• Free (or low) cost software
• Easy to find help on internet (therefore widely used)
• Zero (or low) maintenance
• Easy to install
• Easy to use – yet powerful
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 and SQLyog Community edition (for Windows) or Sequel Pro (for Apple Mac) .
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
• Create a database table
• Display the data (using SELECT statement)
• Delete the data (using DELETE statement)
In part II we will go through detail of using SQL.
Sign up below to get email when Part II is online.