03.02.2015
Robert Galetzka
Author: Robert Galetzka

Com­par­ing ACL™ Script­ing to SQL Quer­ies (Part 1)

The following blog post was written by our trainee Robert, who is studying business informatics at the Deggendorf Institute of Technology. During his internship he compared ACL™ scripting technology with queries created in SQL. Since the latest version of our extraction tool dab:Exporter offers SQL as target format as well, we wanted to get an overview regarding common features as well as differences of these analytic solutions. This is the first part of his article; the second part will be published here next week.

ACL™’s analytic solutions do have an own (and pretty cool) programming- / scripting language where reusable analytic routines can be created. Many companies do not use only ACL™ solutions but also other software for data analysis in their company. Often data is stored in SQL databases. These data could easily be imported in ACL™ and analyzed there. But an interesting question is how such analytic routines which can be created and performed in ACL™ would look if they would need to be done directly in the database, using SQL statements.

I am working as part of my internship at the dab: GmbH on the comparison of ACL™ with SQL by converting existing ACL™ analytic routines (“Scripts”) to SQL queries. In this blog post I will give an insight on the approach in general as well as the implementation/conversion and the differences between SQL queries and ACL™ scripts which I encountered.

For comparing ACL™ scripting and SQL queries, I looked at an existing solution which is coded in ACL™ and started porting it to SQL. Let me first explain you what the starting point was:

I. My task

The company dab offers a solution called „AnalyticSuite“, which is an extensive portfolio of pre-defined analytics for data out of SAP®. It allows the data analyst to answer business questions by the push of a button; the content is focused on analytic questions of Internal Audit, Compliance and Controlling. The analytics have been programmed in ACL™ using the ACL™ scripting language and can be run in ACL™ Analytics Desktop or Analytics Exchange.

The main component of the dab:AnalyticSuite are single base tables which are put together from a lot of different SAP® raw tables. They contain all information necessary for a big variety of analytic purposes, so the data analyst / auditor does not having to deal with the topic of data preparation anymore, which is a great time saver.

Another feature of the dab:AnalyticSuite is the portfolio of pre-defined analytic routines which can be applied on each Audit Object. This allows ad-hoc data analytics by the push of a button. For procurement these are for example questions like "Invoice date before PO date”, "Price Changes", "Unlimited delivery tolerances", “Contract circumventions” and many more. For more details, please refer to the dab:AnalyticSuite page; for more examples of data analytic routines have a look at the catalogue “Analytic solutions for your SAP® data”.

The following picture shows four examples for Audit Objects: Purchasing documents, Vendor accounting documents, Sales documents and Customer accounting documents. On top level there are some examples for analytic routines that can be run on that data.

Figure 1 – dab:AnalyticSuite Architecture (old)

As a basis for my Audit Object SQL prototype I used the Audit object “Purchasing”. My first goal was to be able to perform the data preparation, which means to create the base table containing all information concerning the SAP® purchase orders in SQL. After managing to do so, my next step would be to port some of the pre-defined analytic routines to SQL as well.

To perform the task I had to dig into the existing ACL™ scripts and „translate“ them step by step into SQL statements, but I had to consider the following premises.

  • Keep all the features: If possible, I had to create the SQL prototype as close to the original solution as possible, including all features that the ACL™ implementation of the dab:AnalyticSuite is offering.
  • Retain data integrity: Whenever doing analytics, especially in context of Internal Audit, keeping up the data integrity is essential; I had to make sure that the output (record counts, results, computations, check sums) are exactly the same as in the original solution.

II. ACL™ vs. SQL – The key dif­fe­ren­ces I en­coun­te­red

To give a little spoiler here: I successfully managed to create the SQL prototype, and to fulfill all conditions. However it I was not able to find an equivalent SQL command for all the feature ACL™ offers, so I had to find some workarounds for solving some challenges. In this chapter I will look at the key differences in detail:

  1. Working with multiple tables: The powerful ACL™ feature “Relations” does not exist in SQL
  2. ACL™ allows you to differ between column names and column descriptions, SQL does not
  3. The „Cross tabulate“ command in ACL™ does not have an as convenient equivalent in SQL
  4. Entering user-defined parameters is easier in ACL™ using the built in „Dialog“ feature

I will give you some details regarding these points now, before I summing up my conclusions.

1. Working with multiple tables: The powerful ACL™ feature “Relations” does not exist in SQL

To create an Audit Object, a lot of SAP® tables need to be combined in the first place, to add as many pieces of information to the base table as possible. To achieve this, ACL™ offers the commands “JOIN”, but also “RELATION”. Using the RELATIONS-command is very convenient in cases like that, as it allows you to access up to 18 tables in one go. When setting up the relations between the tables, basically the data does not get processed yet, this makes the data preparation quick, easy and transparent. No big temporary data processing procedure is needed, almost no temporary fields created at that point.

However SQL does not offer that “RELATIONS” command. This means, I had to use the “JOIN” to achieve the same results and combine the data which was necessary for building the Audit Object. To avoid creating duplicates, the joins need to be set up correctly by making use of the appropriate primary and secondary key field logic.

Now this may not sound like a major difference, however it lead to a lot more and a lot more complex program code compared to the equivalent ACL™ scripts. As a result, my work got more complex, and whenever adjustments, error tracking or enhancements were needed, it was more time consuming and more intransparent compared to ACL™ - even for an experienced SQL programmer.

2. ACL™ allows you to differ between column names and column descriptions, SQL does not

In SAP®, fields in tables have a technical field name plus a field title. For example if you take the field which contains the purchase order line item, the title is “Purchasing document line item”, or on a German system “Positionsnummer des Einkaufsbelegs”; the technical name however is “EBELP” in both cases. This means, the technical name is universally valid worldwide no matter what language setting or SAP® version it is; the field title depends on language settings and may not be unique within a table. This concept makes it solid from a technical perspective as well as user-friendly, as the data is easy to interpret. ACL™ has the same concept as SAP® tables have, it differs between column name and title as well. When writing ACL™ scripts to create re-usable analytics, you always use the technical names in the program code, then your script will be independent from language or version of the SAP® system where the data was pulled from. Yet in your results, you will still be able to see the column title.

Figure 2- Technical column name and column title in ACL™

The column names in SQL are less flexible compared to those in ACL™, as SQL only allows one content to be used as name, so you have decide whether to use technical name or column title (which is basically a decision between having a unique key, or a good usability by self-explaining names). I started with using the technical names (in my convention starting with the table name, then followed by the columns technical name). The following picture shows some fields of the table containing purchasing line items:

Figure 3 – Column name in SQL before renaming

The column names would be universally valid worldwide, no matter what language or verison it is - universally valid, yet hard to interprete. To make it as comparable to the ACL™ solution as possible and to improve usability, I renamed them in SQL by making a combination of technical names and description. Finally this meant overwriting the technical name by speaking names including the technical name in parentheses, as the following picture shows:

Figure 4 – Column name in SQL before renaming

Doing this, as result I had Audit Object tables which were quite userfriendly, but this necessary renaming to make it easier to interprete has one major drawback. As I explained above, there are analytic routines that can be performed based on the Audit Objecct base tables. However, the technical table names do not exist anymore, which makes it hard to write universally valid SQL statements. So I had to rename the column names again every time when an analytic step was performed. This again ensured a consistent base table within which each column could be addressed properly. At the end of each analytic test, the the technical names are again complemented with the descriptive name to make it easier for the end-user to look at the result. To summarize, I started with technical names, then renamed it to technical name + column titles to have proper looking base tables; whenever an analytic was performed on these base tables I replaced it with the technical names temporarily, and once the result was generated it got equipped with technical name + column title again. So it was possible because I found a technical workaround, however you noticed that this was quite a hazzle compared to ACL™ where all of this is already available without doing any effort on top.

The column titles and the missing „RELATION“ command were not the only challenges I faced when comparing ACL™ to SQL. Two further aspects I will explain in the second part of this article, which will be published next week in this blog.

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

To contact the author you can also use LinkedIn or XING (you may have to login first before you can access these links).

LinkedIn: http://de.linkedin.com/pub/stefan-wenig/54/1b8/b30

XING: https://www.xing.com/profile/Stefan_Wenig2?sc_o=mxb_p


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