This article was written for SAP Business Analysts but applies to all Business System Analysts. In this post Matimba encourages BAs to know and use 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 reports (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 argue for an alternative. Rather use a SQL database for SOME of these "ad-hoc" type analyses. See it as another option depending on what you want to do. In some cases Excel is much better than a SQL database. In other cases you need a database and sometimes you need both -- especially if you want nice looking tables and graphs. What is the benefit of using a SQL database? It can speed-up and simplify repetitive data analysis activities (and increase your income!) It also provides functionality that simply would take too long to do using a spreadsheet. Increase your income you may wonder? Well if you have knowledge and can use SQL, you can arrive at an answer quicker than the those who cannot use SQL, so you're more marketable! 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 consultants 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 of using a spreadsheet (Excel) or a SQL database for data analysis: This is how I look at it: If it's a once-off analysis and you know exactly what you will be doing – spreadsheets are great! If you are slightly unsure of the steps to perform or you may need to re-do or need to combine different tables/files – use SQL (database). The first time you do analysis it might take a long time but after that – you'll fly! SQL is repeatable and scalable. Excel analysis 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 this 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 to the 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. Select a database and start using a SQL database. This is the general process / data flow when using a 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 too soon. STEPS: 1. Install a SQL database on your PC (this post) 2. Learn basics of SQL 3. Work out best way to get your data in database 4. Use it Which database? 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 works in your environment. 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 is 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 statements 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) Good Luck! Remember, you can do it. Just keep swimming! You can contact Willem at: hello@matimba.com
0 Comments
Your comment will be posted after it is approved.
Leave a Reply. |
Details
AuthorWillem Hoek Matimba
March 2020
Categories |