03.07.2015
Anton Grening
Author: Anton Grening

ACL An­a­lyt­ics™ – Re­la­tions: Sim­ple, in­di­rect and through com­bin­ed text field

In the first two blog posts of that ACL Analytics™ series (Four practical text field functions, The Classify workaround) we have seen how text fields can be combined easily and what benefits we gain from it. Today I will show you another use case for this functionality.

An essential advantage of ACL Analytics™ is that it is not only ideally suited for working with table or spreadsheet-like data (which is something which we primarily associate with Microsoft Excel™), but also includes the part of connecting several tables via key fields like Microsoft Access™ is doing.

As the information to be analyzed are usually distributed across several tables (as in fact, most of the data comes from relational databases), ACL Analytics™ provides the ability to connect them through relations to perform cross-table analytics.

The consideration of purchasing documents is a good example here. SAP® and other ERP systems manage these and other relevant information in several tables. The purchasing documents themselves are divided into two tables, “purchasing document header” and “purchasing document items”. Information about vendors, the company code or the place of storage are saved in their own master data tables. So-called “key fields” identify certain records in a table and enabling the connection to other tables at once. For example, the field “company code” in table purchasing document items is a key field that refers to the company code master data table. Now, if you want information from this table e.g. the name of the company code, just use the following procedure:

Figure 1: Defining a simple relation between two tables

  1. Open the table "PurchasingDocument_Item" and call the relations dialog from the menu (Data -> Relate)
  2. Press Add Table..
  3. Select the appropriate table (In this case "CompanyCode_Masterdata")
  4. Link the common key fields via drag and drop

After the process is completed with Finish... you won’t notice any changes. This is because the required field (the company code name) must first be inserted into your view (though you could already at that moment in time use it in all of your commands, but sometimes doing an eyeball-check provides assurance that everything worked correctly) Proceed as follows:

Figure 2: Insert a field from a related table into a view

  1. Select Add Columns from the context menu after right-clicking on a column in your data sheet. (The new column will be added to its left)
  2. Coose the table you want to add the field from. (LIst of all related tables to the current table)
  3. Add the required field by double-click on it or use the arrow buttons to move it to the selected fields
  4. Confirmation with OK will add the field to the view and displays the information linked from the master data table

In addition to these simple relations, also indirect relations are possible in ACL Analytics™. That means you connect a table indirectly via another one, in order to get more detailed information on a data set, where no direct key field connection between these two tables exists.A good example for this is adding supplier information to the purchasing document items.

At first you will need to connect the document items through the common document number field with the header information table. Only this purchasing document table is containing the vendor number you need to connect with the vendor master data table to get vendor information like name and city.

Figure 3: Define an indirect relation via an intermediate table

You will notice that key fields play an important role while connecting tables, so you need to consider some details when proceeding. ACL Analytics™ checks at this point only if the equality of data types correspond; different field names however (in our example: Vendor_Account_Number and VendorID) are possible anytime. At the same time connections through field like Vendor_Account_Number (text field) from document header table with City (text field) from vendor master data are also possible but do not make any sense. Since there will be no matches on the contents of fields that link won’t lead to any results and your new added field remains blank. Therefore it is advisable to know the key fields of the tables and check the assignment in relations dialog exactly.

Another point that must be considered in terms of key fields is the fact that some tables can’t uniquely identify records by using only one key field. In such cases we have to use the already known functionality of combining fields. Connection with information from the storage table delivers a good example for this situation. The “storage location” master data table needs two fields (plant and storage location) to clearly determine a record. If you now want to add a field from this table (e.g the storage description) to your purchasing document items, you first need to create a combined field in both tables (plant + storage location) and relate the two tables using this new key field.

Figure 4: Create a simple relation based on a combined text field

Figure 5: Table with transaction data, including added information from various master data tables

These three examples showed how to relate tables and analyze appertaining fields from different tables together in ACL Analytics™. Therefore relations between tables via key fields are required. But it is essential here that this connection of tables has different types of relations with so-called cardinalities which are playing an essential role. What distinctions can be found here, what must be considered and what errors may occurs, will be discusses in detail in our next blog entry.

A first hint in advance: Join your tables always starting from transaction data to master data like in our example (purchasing documents to several master data tables)

If you have questions or comments, feel free to contact 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