The last blogpost within this ACL Analytics™ series is going to get its teeth into relations between tables and wants to take up the issue described in our last entry (Relations: Simple, indirect and through combined text field). First, we will learn about different types of relations between tables and see what kind of questions can be solved very simple by using them. I am going to show you as well, what errors could happen while working with relations and how to avoid them best.
As you already know, ACL™ is perfectly suited for analyzing several tables combined. Therefor so-called cardinalities used in data modeling are playing an essential role. Cardinalities describe the relation between two tables; to be more exact the relation between the records in two tables (Primary and Secondary table). The records can be identified by a unique identifier, we usually talk about (one or more) key field(s). This may sound very technical, but is basically very simple, if we illustrate three basic types with simple examples:
Each record of the first table can only be assigned to one record of the second table and vice versa.
Example (Vendor master list / Vendor evaluation data): Each vendor who is listed in the first table has only one vendor evaluation entry. Vice versa, one vendor evaluation belongs to exactly one vendor.
One record of the first table can be assigned to multiple records in the second table; however a record of the second table is only assignable to one record in the first table.
Example (Vendor master list / Vendor purchase orders): A purchasing document belongs to exactly one vendor (=the vendor the goods were ordered from). But this vendor can be assigned to several purchasing documents (maybe there were 10 purchase orders in 2015 for that vendor already).
Records in the first table can be assigned to multiple records in the second table; but one record in the second table could be assigned to multiple records in the first table as well.
Example (List of materials we need for production / List of all available materials per vendor from our procurement department): One material can be obtained from not only one but a couple of vendors; and one vendor delivers several materials.
In most cases, you have to deal with 1:n relations like in our example in the previous blogpost. There we used a relation to add additional information from other tables related through a key field (e.g. extend purchasing documents by the vendor name).
In ACL™, when creating a relation, it is important to consider from which table you start building a relation. In this context, I already gave you a rule of thumb: Join your tables always starting from transaction data. That means you need to find the table with multiple records for each key field and connect it to the table with the one record per key field.
In our example this would be:
|Data Category||master data||:||transaction data|
|Table Content||vendor||:||purchasing document(s)|
|Common Key Field(s)||VendorID||:||Vendor_Account_Number|
|Key Field Status |
(each vendor occurs in the list only 1 time)
Up to n times
(a vendor can have no purchase orders, 1 purchase order or many purchase orders)
Now this was easy, wasn’t it? However in some cases you may not know by heart whether the tables contain master data or transaction data; or maybe it will not be possible to classify the tables by this categories at all. Let’s have a look at a second example, looking at the tables “PurchasingDocument_Item” and “PurchasingDocument_Header”. If you want to combine those two tables (because maybe the payment terms are in the header table, however the quantity and price are stored on line item level), you need to find out which table will be primary (this is the one you will start with the relation), and which one will be secondary.
A tip on how you can check the key field status of a table easily before creating a relation - Use the ACL™ command for duplicates search: