Ensuring Compliance with ACL

1. Ensuring Compliance with ACL A Developer’s View Reg Brehaut, ACDA

2. Our Project Characteristics • Compliance, not Financial • Volumes of Oil & Gas, not dollars • Continuous Monitoring • Watch every operator, not audit specific ones • Consistency, not Creativity • 600 Operators, one database • 100% Automated

3. Compliance Assessment Process • Operators enter data over several weeks • Our data refreshed after deadline • ACL project runs over night • Operators receive and review reports • Auditors receive and review reports • Auditors call Operators on specific items • Operators make changes to processes

4. Assumption vs. Reality Assumption: Lack of Compliance will show up in data Reality: Noncompliance that does not affect the data will never get caught by our analysis

5. Challenges/Issues • Structuring a Project for automatic execution • Naming Conventions • Preparing the data • Tracking Progress • Using Statistics • Limitations/Frustrations with ACL

6. Automatic Execution • No user interface • Run by Windows scheduler • Command line executes master script <location of acl.exe> <location & name of project> /b master_script • Master script calls all other scripts • Separate routine (not ACL) produces and distributes reports

7. Structuring for Automatic Execution • Get and prepare the data • Run the Analytic Scripts • Compile the report files

8. Project Structure

9. Scripts 134 scripts in 5 folders: 6 Master scripts 26 to prepare the data 90 Analytic Scripts 6 for Reporting 6 Utilities Tables 183 tables in 5 folders: 45 Source tables 34 Extracts tables 11 Base tables 90 Analytic Results 3 Report Results Naming Conventions: Why We have a problem keeping all this organized

10. ACL Beef 1: Limit on Name Length S_ = source tables E_ = extract tables D_ = data preparation scripts A_ = analytic scripts RF_ = Facility Results RO_ = Operator Results

11. ACL Beef 2: No Search function Not sure where (in a hundred scripts) a file gets created? Good luck finding it!

12. Naming Conventions: Connections A_Script_Name produces T_Script_Name temporary files and R_Script_Name result files

13. Preparing the Data Clean/Standardize the Data • Include only rows and fields needed • Consistent names, formats & sizes

14. Preparing the Data Clean/Standardize the Data • Include only rows and fields needed • Consistent names, formats & sizes Modify for easier scripting • Flatten Data to avoid repetitive coding • Restructure for easier analysis

15. Controlling Execution of Scripts Spreadsheet: • Import into Source file (S_Indicators) • Extract Active items to E_Indicators

16. Controlling Execution of Scripts M_Analytic_Master script: open E_Indicators count v_Number_of_Scripts = Count1 v_CAI = 1 Do M_Run_CAIs While v_Number_of_Scripts >= v_CAI

17. Controlling Execution of Scripts M_Run_CAIs script: Open E_Indicators Locate Record v_CAI v_ScriptName = substr("A_" + ACL_Indicator + Blanks(30),1,31) Do %v_ScriptName% v_CAI = v_CAI + 1

18. Tracking Progress Comment *********************************************************************** ** Script Name: A_Abandoned_Wells ** Description: Creates file where Production is reported for ** Abandoned Wells ** Requirements: ** Output: RF_Abandoned_Wells ** Written By: Reg Brehaut and Barb Ramsay, Nov 2008 ** Modified By: ** Version: 1.0 ***********************************************************************

19. Tracking Progress Comment Script Name: A_Abandoned_Wells Comment *********************************************************************** ** Description: Creates file where Production is reported for ** Abandoned Wells ** Requirements: ** Output: RF_Abandoned_Wells ** Written By: Reg Brehaut and Barb Ramsay, Nov 2008 ** Modified By: ** Version: 1.0 ***********************************************************************

20. M_Run_CAIs, with Progress Open E_Indicators Locate Record v_CAI v_ScriptName = substr("A_" + ACL_Indicator + Blanks(30),1,31) Do %v_ScriptName% v_CAI = v_CAI + 1

21. M_Run_CAIs, with Progress Open E_Indicators Locate Record v_CAI v_ScriptName = substr("A_" + ACL_Indicator + Blanks(30),1,31) v_StartTime = TIME() Extract "%v_StartTime%" as "Started" "%v_ScriptName%" as "Script" Blanks(8) as "Ended" to "Status" Append Do %v_ScriptName% v_EndTime = TIME() Extract Blanks(8) as "Started" "%v_ScriptName%“ as "Script" "%v_EndTime%" as "Ended“ to "Status" Append v_CAI = v_CAI + 1

22. Progress Report “Status” File

23. Progress Report: Completed

24. Compiling Report Data • Create list of existing result files • Cycle through, adding contents of each Dir "%_v_Home%RF_*.FIL" Suppress To Report_list v_Number_of_Files = Write1 v_Counter=1 Do R_Fac_Details_B While v_Number_of_Files >= v_Counter

25. Adding Results to Report File Open Report_List Locate Record v_Counter v_File = Alltrim(Clean(File_Name '.') )+ Blanks(35) Open %v_File% Indicator = substr(v_File,4,31) Extract fields Facility_Id Indicator substr(Explanation + Blanks(250),1,250) as "Explanation" to "RP_Fac_Details" Append v_Counter = v_Counter+1

26. Adding & Removing Analytics Adding: • Write & test the script • Add it to the control spreadsheet • No change required to any other script Removing: • Change status in control spreadsheet

27. ACL Beef 3: No Across-Field Statistics • All Statistics are single field across rows • ACL provides Range, Mean & StdDev • Manually calculate Slope • Statistics on Statistics • Calculate the StdDev for sets of numbers • Determine the Mean and StdDev of these StdDevs • Follow-up on those that are very different

28. ACL Beefs 4 – 6: • Only One thing Open at a Time • ACL does not support continuation lines • ACL does not do Documentation well

29. Ensuring Compliance with ACLA Developer’s View Questions?

30. Reg Brehaut • Winner of the ACL Impact Award 2009, for “Most Promising Novice” • System Architect and Developer, Trainer, Technical Writer • Currently instructing in the evenings at SAIT; has instructed at the U of C, Mount Royal College and across the US • Developed the Compliance Assessment system using ACL for Alberta’s Energy regulator

31. Oops… The following slides are best viewed by clicking on links in the related pages (which appear before this slide) Each set of linked slides ends with a “Back” link to return you to the slide you came from

32. Continuation Lines Extract record ; for activity_year_month = _v_EndingYearMonth ; and Fluid_Type = "WATER" ; and Activity_Type = "DIFF" ; and Activity_Quantity > 20 ; and (Volumetric_Imbalance_Percentage > 10.0 ; or Volumetric_Imbalance_Percentage < -10.0) ; to RF_Metering_Diff_High_Water back

33. Data in Multiple Tables: Problem Accounts Table: Account Number (key to Account Name file) CustVend Number (key to Customer/Vendor file) Division Number (key to Division Name file) Project Number (key to Project File) Transaction Type (key to Transaction Name file) Amount Date Period

34. Data in Multiple Tables: Solution Open Accounts Open AccountNames Sec Join … to Temporary_1 (to get account names) Open Open CustomerVendor Sec Join … to Temporary_2 (to get customer/vendor names) Open Open DivisionNames Sec Join … to Temporary_3 (to get division names) Open Extract record if [condition is true] to Final_Result

35. Flatten the Data • Do all joins once, save as working table • Use only working table as source • Scripts now just two lines Open Working_Table Extract record if [condition is true] to Final_Result back

36. Restructure for Easier Analysis For Example… • By Division, what are Expenses as a % of Revenue? • By Account, how does this month’s amount compare to 12 month average? Can be done, but takes several steps because data is on different records Put data on same record

37. Across-Field Comparisons Create a record for each division: Div # Acct1 Acct2 Acct3 … Acct57 Extract record if Acct42 / Acct12 > 1.15 to … We do this for Fluid & Activity combinations Facility ID Gas_Production Gas_Flaring Gas_Disposition … Extract record if Oil_Production > Gas_Production and … to …

38. Across-Period Comparisons Create a record for each account: Acct# Month1 Month2 Month3 … Month12 Average StdDev Extract record if Month12 > (Average * 1.15) to … back

39. Row Statistics: Mean delete field Mean OK define field Mean Computed (Month1 + Month2 + Month3 + Month4 + Month5 + Month6 + Month7 + Month8 + Month9 + Month10 + Month11 + Month12) / 12

40. Row Statistics: StdDev Variance = sum of the square of the differences from the mean delete field Variance OK define field Variance Computed ((Month1 - Mean) * (Month1 - Mean)) + ((Month2 - Mean) * (Month2 - Mean)) + ((Month3 - Mean) * (Month3 - Mean)) + ((Month4 - Mean) * (Month4 - Mean)) + ((Month5 - Mean) * (Month5 - Mean)) + ((Month6 - Mean) * (Month6 - Mean)) + ((Month7 - Mean) * (Month7 - Mean)) + ((Month8 - Mean) * (Month8 - Mean)) + ((Month9 - Mean) * (Month9 - Mean)) + ((Month10 - Mean) * (Month10 - Mean)) + ((Month11 - Mean) * (Month11 - Mean)) + ((Month12 - Mean) * (Month12 - Mean)) delete field StdDev OK define field StdDev Computed root(Variance,4) back

41. Calculating Slope Slope of the Line (i.e. Regression Analysis) = (N * sum(XY) - (sum(X) * sum(Y))) / (N * sum(X*X) - (sum(X)* sum(X))) N = 12 Sum(X) = 78 (i.e., 1 + 2 + 3 + 4 + ... + 12) Sum(X*X) = 650 Sum(X)*Sum(X) = 6084

42. Calculating Slope Slope = (N * sum(XY) - (sum(X) * sum(Y))) / (N * sum(X*X) - (sum(X)* sum(X))) delete field SumXY OK define field SumXY computed Month1 * 1 + Month2 * 2 + Month3 * 3 + Month4 * 4 + Month5 * 5 + Month6 * 6 + Month7 * 7 + Month8 * 8 + Month9 * 9 + Month10 * 10 + Month11 * 11 + Month12 * 12 delete field SumY OK define field SumY computed Month1 + Month2 + Month3 + Month4 + Month5 + Month6 + Month7 + Month8 + Month9 + Month10 + Month11 + Month12 delete field Slope OK define field Slope Computed ((12 * SumXY) - (78 * SumY)) / ((12 * 650) - 6084) back