II. ACL™ vs. SQL – The key differences I encountered
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:
- Working with multiple tables: The powerful ACL™ feature “Relations” does not exist in SQL
- ACL™ allows you to differ between column names and column descriptions, SQL does not
- The „Cross tabulate“ command in ACL™ does not have an as convenient equivalent in SQL
- 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.