Ensuring compliance with acl
Download
1 / 42

Ensuring Compliance with ACL - PowerPoint PPT Presentation


  • 118 Views
  • Uploaded on

Ensuring Compliance with ACL. A Developer’s View Reg Brehaut, ACDA. 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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Ensuring Compliance with ACL' - kert


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Ensuring compliance with acl

Ensuring Compliance with ACL

A Developer’s View

Reg Brehaut, ACDA


Our project characteristics
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


Compliance assessment process
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


Assumption vs reality
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


Challenges issues
Challenges/Issues

  • Structuring a Project for automatic execution

  • Naming Conventions

  • Preparing the data

  • Tracking Progress

  • Using Statistics

  • Limitations/Frustrations with ACL


Automatic execution
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


Structuring for automatic execution
Structuring for Automatic Execution

  • Get and prepare the data

  • Run the Analytic Scripts

  • Compile the report files



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

Naming Conventions: Why

We have a problem keeping all this organized


Acl beef 1 limit on name length
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


Acl beef 2 no search function
ACL Beef 2: No Search function

Not sure where (in a hundred scripts)

a file gets created?

Good luck finding it!


Naming conventions connections
Naming Conventions: Connections

A_Script_Name

produces

T_Script_Name temporary files

and

R_Script_Name result files


Preparing the data
Preparing the Data

Clean/Standardize the Data

  • Include only rows and fields needed

  • Consistent names, formats & sizes


Preparing the data1
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


Controlling execution of scripts
Controlling Execution of Scripts

Spreadsheet:

  • Import into Source file (S_Indicators)

  • Extract Active items to E_Indicators


Controlling execution of scripts1
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


Controlling execution of scripts2
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


Tracking progress
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

***********************************************************************


Tracking progress1
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

***********************************************************************


M run cais with progress
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


M run cais with progress1
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


Progress report
Progress Report

“Status” File



Compiling report data
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


Adding results to report file
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


Adding removing analytics
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


Acl beef 3 no across field statistics
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


Acl beefs 4 6
ACL Beefs 4 – 6:

  • Only One thing Open at a Time

  • ACL does not support continuation lines

  • ACL does not do Documentation well


Ensuring compliance with acl a developer s view
Ensuring Compliance with ACLA Developer’s View

Questions?


Reg brehaut
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


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


Continuation lines
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


Data in multiple tables problem
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


Data in multiple tables solution
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


Flatten the data
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


Restructure for easier analysis
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


Across field comparisons
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 …


Across period comparisons
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


Row statistics mean
Row Statistics: Mean

delete field Mean OK

define field Mean Computed (Month1 + Month2 + Month3 + Month4 + Month5 + Month6 + Month7 + Month8 + Month9 + Month10 + Month11 + Month12) / 12


Row statistics stddev
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


Calculating slope
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


Calculating slope1
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


ad