Chapter 8:

1. Chapter 8: CAATTs for Data Extraction and Analysis IT Auditing & Assurance, 2e, Hall & Singleton IT Auditing & Assurance, 2e, Hall & Singleton

2. DATA STRUCTURES • Organization • Access method IT Auditing & Assurance, 2e, Hall & Singleton

3. Access:Non-IndexMethods HashingPointers INDEX File DATA File Access:Index Methods Data Organization SEQUENTIAL ISAM RANDOM SEQUENTIAL RANDOM IT Auditing & Assurance, 2e, Hall & Singleton

4. FILE PROCESSING OPERATIONS • Retrieve a record by key • Insert a record • Update a record • Read a file • Find next record • Scan a file • Delete a record Individual Records Table 8-1 IT Auditing & Assurance, 2e, Hall & Singleton

5. DATA STRUCTURES • Flat file structures • Sequential structure [Figure 8-1] • All records in contiguous storage spaces in specified sequence (key field) • Sequential files are simple & easy to process • Application reads from beginning in sequence • If only small portion of file being processed, inefficient method • Does not permit accessing a record directly • Efficient: 4, 5 – sometimes 3 • Inefficient: 1, 2, 6, 7 – usually 3 IT Auditing & Assurance, 2e, Hall & Singleton

6. DATA STRUCTURES • Flat file structures • Indexed structure • In addition to data file, separate index file • Contains physical address in data file of each indexed record IT Auditing & Assurance, 2e, Hall & Singleton

7. DATA STRUCTURES • Flat file structures • Indexed random file • Records are created without regard to physical proximity to other related records • Physical organization of index file itself may be sequential or random • Random indexes are easier to maintain, sequential more difficult • Advantage over sequential: rapid searches • Other advantages: processing individual records, efficient usage of disk storage IT Auditing & Assurance, 2e, Hall & Singleton

8. DATA STRUCTURES • Flat file structures • Indexed Sequential Access Method (ISAM) • Large files, routine batch processing • Moderate degree of individual record processing • Used for files across cylinders • Uses number of indexes, with summarized content • Access time for single record is slower than Indexed Sequential or Indexed Random • Disadvantage: does not perform record insertions efficiently – requires physical relocation of all records beyond that point – SOS • Has 3 physical components: indexes, prime data storage area, overflow area [Figure 8-4] • Might have to search index, prime data area, and overflow area – slowing down access time • Integrating overflow records into prime data area, then reconstructing indexes reorganizes ISAM files IT Auditing & Assurance, 2e, Hall & Singleton

9. Random DBMS etc. Legacy systems ISAM Legacy systems Sequential 1990 1980 1960 1970 EVOLUTION OF ORG./ACCESS METHODS IT Auditing & Assurance, 2e, Hall & Singleton

10. Efficient ISAM Random Sequential Inefficient Access single records Access entire files IT Auditing & Assurance, 2e, Hall & Singleton

11. POINTER STRUCTURE • Stores the address (pointer) of related record in a field with each data record [Figure 8-6] • Records stored randomly • Pointers provide connections b/w records • Pointers may also provide links of records b/w files [Figure 8-7] • Types of pointers [Figure 8-8]: • Physicaladdress – actual disk storage location • Advantage: Access speed • Disadvantage: if related record moves, pointer must be changed & w/o logical reference, a pointer could be lost causing referenced record to be lost • Relative address – relative position in the file (135th) • Must be manipulated to convert to physical address • Logical address – primary key of related record • Key value is converted by hashing to physical address IT Auditing & Assurance, 2e, Hall & Singleton

12. DATABASE STRUCTURES • Hierarchical & network structures Uses explicit linkages b/w records to establish relationship • Relational structure • Uses implicit linkages b/w records to establish relationship: foreign keys / primary keys IT Auditing & Assurance, 2e, Hall & Singleton

13. Relational Records: “Foreign Keys” in one record establishes relationships to related records in other files. CUSTOMERS INVOICES INVENTORY IT Auditing & Assurance, 2e, Hall & Singleton

14. DATABASE STRUCTURES • Relational structure • User views • Data a particular user needs to achieve his/her assigned tasks • A single view, or view without user input, leads to problems in meeting the diverse needs of the enterprise • Trend today: capture data in sufficient detail and diversity to sustain multiple user views • User views MUST be consolidated into a single “logical view” or schema • Data in the logical view MUST be normalized IT Auditing & Assurance, 2e, Hall & Singleton

15. DATABASE STRUCTURES • Relational structure • Importance of data normalization • Critical to success of DBMS • Effective design in grouping data • Several levels: 1NF, 2NF, 3NF, etc. • Un-normalized data suffers from: • Insertion anomalies • Deletion anomalies • Update anomalies • One or more of these anomalies will exist in tables < 3NF IT Auditing & Assurance, 2e, Hall & Singleton

16. DATABASE STRUCTURES • Relational structure • Auditors and data normalization • Database normalization is a technical matter that is usually the responsibility of systems professionals. • The subject has implications for internal control that make it the concern of auditors also. • Most auditors will never be responsible for normalizing an organization’s databases; they should have an understanding of the process and be able to determine whether a table is properly normalized. • In order to extract data from tables to perform audit procedures, the auditor first needs to know how the data are structured. IT Auditing & Assurance, 2e, Hall & Singleton

17. EMBEDDED AUDIT MODULE • Identify important transactions live while they are being processed and extract them • Examples • Errors • Fraud • Compliance • SAS 78, SAS 94, SAS 99 / S-OX IT Auditing & Assurance, 2e, Hall & Singleton

18. EMBEDDED AUDIT MODULE • Disadvantages: • Operational efficiency – can decrease performance, especially if testing is extensive • Verifying EAM integrity - such as environments with a high level of program maintenance • Status: increasing need, demand, and usage of COA/EAM/CA IT Auditing & Assurance, 2e, Hall & Singleton

19. GENERALIZED AUDIT SOFTWARE • Brief history • Most widely used CAATT • Usages include: • Footing and balancing entire files or selected data items (e.g., extending inventory) • Selecting and reporting detail data • Selecting stratified statistical samples from data files • Formatting results into audit reports (auto work papers!) • Printing confirmations • Screening / filtering data • Comparing multiple files for differences • Recalculating values in data IT Auditing & Assurance, 2e, Hall & Singleton

20. GENERALIZED AUDIT SOFTWARE • Popular because: • GAS software is easy to use and requires little computer background • Many products are platform independent, works on mainframes and PCs • Auditors can perform tests independently of IT staff • GAS can be used to audit the data currently being stored in most file structures and formats IT Auditing & Assurance, 2e, Hall & Singleton

21. GENERALIZED AUDIT SOFTWARE • Simple structures [Figure 8-19] • Complex structures [Figures 8-20, 8-21] • Auditing issues: • Auditor must sometime rely on IT personnel to produce files/data • Risk that data integrity is compromised by extraction procedures • Auditors skilled in programming better prepared to avoid these pitfalls IT Auditing & Assurance, 2e, Hall & Singleton

22. ACL • ACL is a proprietary version of GAS • Leader in the industry • Designed as an auditor-friendly meta-language (i.e., contains commonly used auditor tests) • Access to data generally easy with ODBC interface IT Auditing & Assurance, 2e, Hall & Singleton

23. Chapter 8:CAATTs for Data Extraction and Analysis IT Auditing & Assurance, 2e, Hall & Singleton IT Auditing & Assurance, 2e, Hall & Singleton