Willem Hoek

Why all Business Analysts should know and use SQL

Jun 17, 2013

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:

Why you need to know SQL
If you are a Excel power user, you must know SQL

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:

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.

Some examples

Customer account

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 Master records

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.

STEPS:

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

Step 1 is covered in this post. Step 2 - 4 will be handled in part II and part III of this post.

Which database?

For me – the requirements were:

Open Source
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 [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)