Best Practices:How to set the foundation

for a more integrated tomorrow!

Porter BroylesHarris County Auditors OfficeContinuous Controls Analyst II

Continuous Assurance Programó 6 Major Projectsó Projects range from 40-100+ scriptsó New projects are built upon a common “skeleton”.ó New projects use same scripts!ó Projects may perform over 100 different testsó Continuous Assurance results are sent to one of 3 standardized

report formatsó Some projects produce over 10K worth of ACL Logging per day!

Objectives of P&PWant to write projects that ó Are easily expandableó Are easily editableó Others can review and understandó Are modular in design ó Do not pose a risk to currently approved projectsó That minimize the number of reports generated

Outlineó Commandsó Commentsó Commentsó Data Tablesó End of Scriptsó Field Namesó In Scriptsó Naming Conventionsó Opening Sectionsó Other Issues to Consideró Project Namesó Reportsó Results Tablesó Script Libraryó Script Namesó Start of Scriptsó Table Namesó Temporary Tablesó Variable namesó Working tables

ó Naming Conventionsó Script Namesó Table Names

ó Data Tablesó Results Tablesó Working Tablesó Temp Tables

ó Variable Namesó Field Namesó Project Names

ó Commentsó Start of Scriptó In Scriptsó End of Scripts

ó Opening Sectionsó Commentsó Commands

ó Reportsó Other Issues to Consider

Alpha Numeric ConceptWhen writing scripts, all scripts begin with two letters

followed by two numbers.

The letters organize the scripts by type/purpose/objective/process.

The numbers organize the script within the process.

Example:AA01Naming ConventionsAB01 Script NamesAC01 Table NamesAC02 Data TablesAC03 Results TablesAC04 Working TablesAC05 Temp TablesAD01 Variable NamesAE01 Field NamesAF01 Project NamesBA01CommentsBB01 Start of ScriptBC01 In ScriptsBD01 End of ScriptsCA01 Opening SectionsCA02 CommentsCA03 CommandsDA01 Other Issues to Consider

Naming ConventionsScript NamesTable Names

Data TablesResults TablesWorking TablesTemp Tables

Variable NamesField NamesProject Names

CommentsStart of ScriptIn ScriptsEnd of Scripts

Opening SectionsCommentsCommands

Other Issues to Consider

Advance naming conceptUse the Alpha characters to represent the same

processes in every project. For example:

AX## = Start up scriptsBX## = repeating scripts within the projectCX## = Communal scripts DX## = Data Load ScriptsEX## -WX## = Processing/Analytic ScriptsYX## = Administrative FunctionsZX## = Shut down/Clean Up Scripts

Table Naming ConventionsThere are 4 types of tables:

1) Original Data2) Computed data/working tables3) Temporary files4) Final Product(s)/Reports

Table Naming ConventionsOriginal data files---Naming should include 3 parts:

– A prefix to identify the file as original data. – The original table name without any non-alpha-numeric

characters.– The date of the data---consider YYMMDD v MMDDYY

AAA_Employeefile_111013AP_VendorFile_111013STD_memberfile_111013{corporate initials}_Employeefile_111013

Table name – temp filesTemporary files are labeled TEMP001-TEMPXXX.

This allows for ó Easy trouble shooting ó Helps to identify where issues existó Helps to determine if expected results flowed through temp

files

ó Clean up of project via a standardized clean up script

REPORT tables

Reports begin with the word “REPORT” followed by the alpha numeric script identifier that generated the report and a short description.

So one might have a REPORT_DA01_DuplicateAddress.

Working TablesWorking tables are generally one of two types of tables:1) Data derived from basic data tables.2) Data contained within the project, but not derived

from current data tables.

These tables should be descriptive and contained in a special folder.

Standardized Variable NamesI use a two or three part name in creating variables.

Part 1 – the v_ that identifies the value as a variable.Part 2 – the type of variable being definedPart 3 – a short description/qualifier for the variable

(optional)

Sample Variables• V_Path

– V_path_bat– V_path_data– V_path_results

• V_date– V_date_run– V_date_DOS– V_date1

• V_table– V_table_AP

• V_com– V_com_test1

• V_cnt/v_cntr/V_count– V_cnt_test/v_cntr_test

• V_table– v_table_list

• V_define_tablename• V_Drive

Fields NamesField names generated by ACL should

always be readily identified as such. óAmount vs amtóDept vs Department

Use a prefix “F_”ield or something comparable to identify a field name.

COMMENT Every LineBest practice is to put the COM at the start of every record which should be

commented out.

Start of Script CommentComments that go at the start of a script should be

highlighted with two rows of COM followed by asterisks. E.g.:

COM******************COM******************COM Written by Porter Broyles Date: 10/13/11COM Description: This makes it easier to find the start ofCOM subscripts in the log.COM******************COM******************

Opening CommentsComments should be used at the start of every script to:1) Identify who wrote the script2) When the script was written3) When the script was last modified (by whom)4) What the intent of the script is5) Any special considerations

Middle of Script CommentWhen writing comments they should stand out. To help

them stand out add a line before and after the comment itself. This should include the word COM with a series of asterisks after it:

COM*********************COM details of the commentCOM*********************

End of Script CommentsWhen using scripts and subscripts, it can often be a challenge

to determine where one is when reviewing the project log. Whenever a subscript is called, the last command in the subscript should be a note indicating that the subscript is completed:

DO AA01_subscript_A~~~~COM*******************COM Returning from AA01_Subscript_ACOM*******************

Other things to consideró Script Libraryó Development v Productionó Change Controló Version Archivingó Crash Log