10.12.2014
Michael Baumgartner
Author: Michael Baumgartner
connect on xing

Im­port­ing SAP® Ta­bles in ACL™ An­a­ly­tics

Exporting data from SAP® and getting it into ACL™ manually can be painful sometimes. For doing a full scope audit, sometimes over 100 SAP® tables are needed, and to solve this challenge we developed our extraction tool dab:Exporter to do this automatically and user-friendly.

However sometimes you might not have such an interface at hand, so you need to do these steps manually. In this article I will show you how importing SAP® tables in ACLTM Analytics can be done.

In July my colleague Florian Popan has already described how tables can be extracted from SAP® by using transaction SE16 (see blog post “Formats for downloading by SAP® transaction SE16” for details). I will follow up on that by explaining which download format is the most appropriate one and how files can be imported through the example of SAP® table BSAK.

Down­load for­mat and file struc­ture

To download data from table BSAK you start transaction SE16 and display the data after entering (optional) selection criteria. (For more details, please refer to our blog post from July, which explains SAP® transaction SE16 in detail.) Once the data is shown you go to the menu bar and select “Edit --> Download”. In the “Save list in file…” dialog window, you choose the format “unconverted”.

Figure 1: Display data in SAP® transaction SE16

As the “unconverted” option will create a text file on your hard drive (or network folder), you have to provide file name and path accordingly. In our example we used the original table name “BSAK” with ending “.TXT”. To start the download, select “Generate”.

Figure 2: Selecting the download format .txt® transaction SE16

You can open then file after downloading with any text editor (if the data is not too big). Looking at our example with Microsoft Notepad, the text file contains the following data:

Figure 3: Structure of the text file we downloaded from SAP®

Looking at the data we can identify three areas:

  • One line with meta data:
    • 97 of 179 fields were downloaded. As the maximum we can be download via SE16 is a width of 1023 characters, not all fields of table BSAK could have been extracted.
    • 11 fixed columns, which mean the table BSAK in total contains 11 primary key fields.
  • List of field names. For our example we used the technical field names, which are independent from logon language or SAP version.
  • Detail lines: This is the content of the table; the single financial transactions which we will need to have available in ACL™ to perform our data analytics. Apart from pure content there are also additional characters and elements which are not part of the content itself, like the “||” in front of field “MANDT” and several other pipes which are used as field separators.

All records do have the same structure and layout, similar to a print image / report file. As ACL™ offers an option to import report files, we will use that to get the data in. We will have a look at the detailed how-to now:

Im­port­ing the print file in ACL™

Start ACL™ and open an existing ACL™ project. Go to the menu bar and select „File->New->Table“ to start the „Data Definition Wizard“. Go through the wizard steps, and select our file „BSAK.txt“ for importing. In the next wizard step, make sure that the file format “Print Image (Report) file” is used.

Figure 4: File format selection for importing data to ACL™

Once you reached the step „Print Image File Definition“, you need to define all fields you want to import. You can do this as follows:

Start with highlighting one field, by simply marking up its content with your left mouse button. In our example I started with the field BUKRS (=Company Code). Once it has been highlighted, a dialog pops up where you can name the field and decide about its data type. I named it “BSAK_BUKRS” and defined it as text field using the type “Character”.

Figure 5: Defining the very first field of the report file

The next step is an important one, as it determines what ACL™ will identify as detail record. To achieve this, we have to point ACL™ at some kind of anchor or pattern, which is located ideally on the very same place in every record, and has the same content in each single detail line. ACL™ will use it to identify the detail records. In our case, the field MANDT (=Client) has always the same value throughout the whole file, as we only have data from one SAP® system. After highlighting “800” the detail record definition pops up, but we can confirm it without further changes. (Side note: If we were not sure about the “800” whether it is really the same in every record, we could have selected the decimal point of the date field as well, as it is always on the same position and content-independent, as will always be a point).

Figure 6: Defining the detail records

Selecting the pattern is a one-time job, because as ACL™ is now able to identify each detail record. We now can go through straight-forward and define field by field for importing. I will give you one example for each of the following data types: Text, Numeric and Date. (Side note: If you want to double-check the original SAP® field types and length, you can do so by using SAP® T-Code SE11.)

The field BSAK_MANDT can be defined as text field with a length of 3 characters. When defining a text field, make sure that the starting position and the field length are correct. Just make an eyeball-check by looking at the data previewed in the wizard.

Figure 7: Defining a text field

Field BSAK_AUGDT (=Clearing date) is a date field. The important thing is to define the date layout or date format correctly to allow ACL™ to interpret the values the right way. In our case the data is in format “DD.MM.YYYY”. Keep in mind that this is the way ACL™ will READ the data, and there is only one way to do it correctly. Formatting how the data should be DISPLAYED is independent from that and can be set up later on in the options.

Figure 8: Defining a date field

When importing numeric fields, make sure that you cover the complete field length when highlighting it, as maybe you only see small numbers on your screen, but somewhere below bigger numbers could show up. Also the format and the number of decimals need to be defined correctly. Make sure to double-check what thousands separator and decimal place indicator is used. In our example, the decimal indicator is a comma, and the thousands separator is the point. We indicated this as format, and there are also ACL™ settings in the options where you can switch this the other way around if necessary.

Figure 9: Defining a numeric field

Now we covered all relevant data types – the rest of the fields can be defined step by step accordingly. Once finished, just click on continue to get a field overview, where you still can do some adjustments or for example fine-tune the field titles.

Figure 10: Refining field properties

After that, the import is finished.

Sum­ma­ry

In this article I explained how text format report files which have been extracted from SAP® by using T-Code SE16 can be imported in ACL™. If you only have to deal with one table, it looks straight-forward, but if you need more data, it can be pretty time-consuming and fault-prone. Moreover, downloading data manually from SAP® has some constraints, like being limited to a width of 1023 characters. This means you will not be able to download all fields at once, plus you need to split the download and put it together bit by bit in ACL™ afterwards. Also there might be performance or SAP® timeout issues if you try to download big data, like millions of G/L records for example.

So in a professional environment, the method which was described in this blog post should be only applied to smaller amounts of data. However in real-life usually a lot of tables are necessary for doing more complex data analytics like needed in Internal Audits. In such cases, having a proper interface to SAP® in place absolutely makes sense, and with a data extraction tool like the dab:Exporter, the data gets exported automatically, without harming the systems performance and already in ACL™ format, so no further input is required.

Best regards
Michael Baumgartner

P.S. For any comments on that article, feel free to write us at info@dab-gmbh.de.


Comments (0)
Be the first who comments this blog entry.
Blog login

You are not logged in. Please log in to comment this blog entry.

go to Login