Ensuring Compliance with ACL

1
Ensuring Compliance with ACL

• A Developers 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
• ltlocation of acl.exegt ltlocation name of
  projectgt /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
Naming Conventions Why

• 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

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
Indicator ACL Script name Status
Producing wells abandoned in prior months Venting Sour Gas Abandoned_Wells Venting_Sour_Gas Active In Test

• 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 gt 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
Started Script Ended
(Several rows before here)
112654 M_Run_CAIs
112655 A_Abandoned_Wells
A_Abandoned_Wells 112802
112803 A_Blending_Concerns
A_Blending_Concerns 112836
112837 A_Invalid_Metering
23
Progress Report Completed
Started Script Ended
A_Wrong_Type 113926
113927 M_Report_Master
113928 RP_Fac_Details
113934 RP_BA_Details
113936 RP_Report_Files
M_Grand_Master 113954
24
Compiling Report Data

• Create list of existing result files
• Cycle through, adding contents of each
• Dir "_v_HomeRF_.FIL" Suppress To Report_list
• v_Number_of_Files Write1
• v_Counter1
• Do R_Fac_Details_B While v_Number_of_Files gt
  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_Counter1

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 Developers 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 Albertas 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 gt 20
• and (Volumetric_Imbalance_Percentage gt 10.0
• or Volumetric_Imbalance_Percentage lt -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 months 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 gt 1.15 to
• We do this for Fluid Activity combinations
• Facility ID Gas_Production Gas_Flaring
  Gas_Disposition
• Extract record if Oil_Production gt 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 gt (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(XX) - (sum(X) sum(X)))
• N 12
• Sum(X) 78 (i.e., 1 2 3 4 ... 12)
• Sum(XX) 650
• Sum(X)Sum(X) 6084

42
Calculating Slope

• Slope (N sum(XY) - (sum(X) sum(Y))) / (N
  sum(XX) - (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