SAP123

Why Business Analysts should know and use SQL

Willem Hoek on Jun 17, 2013

Why Business Analysts should know and use SQL

The article was written specifically for SAP Functional consultants and Analysts but equally apply to all Business Analysist.

Lots of standard SAP reports exist for day-to-day reporting. However, as SAP experts we sometimes want to calculate something that cannot 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 is 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 cannot 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!

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:

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 us look at some examples where a database (SQL) will work very well.

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. 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 again: It will take you minimum 1-2 weeks to get going. After that the benefits are huge. Don’t give up to soon.

STEPS:

Install a SQL database on your PC
Learn basics of SQL
Work out best way to get your data in database
Use it

Which database to use?

For me the requirements were:

Free (or low) cost software
Easy to find help on internet (therefore widely used)
Easy to install
Zero (or low) maintenance
Easy to use yet powerful
Standard SQL

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 the database - make a note what the default admin 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. See if you can:

Create a database table
Display the data (using SELECT statement)
Delete the data (using DELETE statement)

Thanks for following allong