Ensuring compliance with acl
This presentation is the property of its rightful owner.
Sponsored Links
1 / 42

Ensuring Compliance with ACL PowerPoint PPT Presentation


  • 88 Views
  • Uploaded on
  • Presentation posted in: General

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

Download Presentation

Ensuring Compliance with ACL

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


Project structure

Project Structure


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

CommentScript 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


Progress report completed

Progress Report: Completed


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


Ensuring compliance with acl

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


  • Login