Analysis Paralysis

Recently, a data index was provided for financial data that specified whether a specific transaction was “qualified” or “non-qualified”.  In the interest of confidentiality, specifics have be left out of this discussion and example files have been edited to generic data.  The financial transactions were coded in the usual method with a cost center, an account, and a work order.  The index table, however, was unique in that it contained no primary key.  Instead, the data listed ranges of qualified data: [CostCenter_From], [CostCenter_To], [WorkOrder_From], and [WorkOrder_To].  For example, any transaction that contained a Cost Center and Work Order combination that were within the ranges shown in the index was “qualified”.  During an initial review of the dataset, a work-around was created to combine the data with no direct joins, resulting in significant duplication of transactions.  From there, a boolean  field (True=1, False=0) was added to test if the transaction Cost Center and Work Order were within the index range.  After that, data was grouped by the original transaction data and the boolean field was summed as an integer (any transaction where the value was greater than 0 was “qualified”); data checks were instituted to ensure that no duplicates existed in the final table.  Although embarrassing in its simplicity, this approach worked for purposes of the initial review.  However, due to the risk of data duplication and the significant amount of processing time and network storage space required to make this work, the approach was not feasible for utilization on a continuing basis.  The index table, therefore, needed to be converted into a standard relational index.  Visual Basic for Applications (VBA) within Microsoft Access could not handle the massive amount of data involved without crashing, so the conversion was programmed via Audit Command Language (ACL) Scripting.

ACL Example Files (save to C:\ACLExample01\)
ACL Project: http://www.box.net/shared/yx6ale4u15
Original Raw Data Index: http://www.box.net/shared/8tf12lbsg6

ACL Script – Walkthrough

1. The ACL safety routines are set to off in order to prevent ACL from stalling script execution with “are you sure” confirmations. (SET SAFETY OFF)
2. The loop maximum is set to 0, which prevents ACL from automatically timing out if a potential infinite loop occurs.  This can be disastrous during debugging, but was the only way to execute this script, as some indexes required 10,000 loops.  (SET LOOP TO 0)
3. In case this is not the first execution of the script, the final table is deleted (both from the navigator and the actual FIL file).
4. The raw-data table is opened (OPEN Index_Raw)
5. The group command is then utilized (GROUP … END).  This command tells ACL to process everything within the command on each record in the table.
6. The variable “CostCenterFromValue” is initialized to [From_CostCenter] within the raw data.
7. The first loop (LOOP WHILE CostCenterFromValue <= To_CostCenter) is then executed.  This will cause ACL to keep looping until the maximum value for the range is achieved.
8. The variable “WorkOrderFromValue” is then initialized to [From_WorkOrder] within the raw data.
9. The second loop (LOOP WHILE WorkOrderFromValue <= To_WorkOrder) is then executed.  This will cause ACL to keep looping until the maximum value for the range is achieved.
10. CostCenterFrom and WorkOrderFrom are then extracted to what will become the temporary index table.  (EXTRACT FIELDS CostCenterFrom WorkOrderFrom TO “Index_Flat_Temp”)
11. The Work Order variable is then incremented (WorkOrderFromValue = WorkOrderFromValue + 1)
12. The second loop is closed (END), which causes the code to jump back to the validation at #9.
13. The Cost Center variable is then incremented (CostCenterFromValue = CostCenterFromValue + 1)
14. The first loop is closed (END), which causes the code to jump back to the validation at #7.
15. Once CostCenterFromValue is greater than [To_CostCenter] within the first loop, the script will move to the close of the group (END).
16. The raw-data table will then be closed (CLOSE Index_Raw).

At this point, Index_Flat_Temp has been created.  Within the ACL script example, it is assumed that duplicate indices are possible and that the Cost Center and Work Order values should actually be character fields.  Therefore, new [CostCenter] and [WorkOrder] character-type fields are created in the table, which is then summarized by those fields. The DEFINE FIELD and SUMMARIZE commands will be detailed in a future discussion.