105 foundations of acl

144 Pages·2005·7.87 MB·English

9
105 FOUNDATIONS OF ACL ieb76e- 4AP.
CONCEPTS AND PRACTICES

Data you can trust. Results you can see.
Copyright © 2008 ACL Services Ltd. All rights reserved.
No part of these materials may be reproduced, stored in a retrieval system, or transmitted, in any form or by any
means (photocopying, electronic, mechanical, recording, or otherwise), without permission in writing from the
publisher, except by a reviewer who may quote brief passages in a review.
ACL Services Ltd.
1550 Alberni Street
Vancouver, BC
Canada V6G 1A5
Telephone: [phone]
Fax: [phone]
E-mail: [email protected]
Web: www.acl.com
Printed in Canada
ACL, the ACL logo, Audit Command Language, and Access Command Language are trademarks of ACL
Services Ltd. Microsoft and Windows are registered trademarks of Microsoft Corporation. z/OS is a registered
trademark of IBM Corporation.
ITDE18025
Edition 4.3.2
May 1, 2008
Contents
Module 1: Fundamentals 1 Creating Tables from dBASE Data 26
Understanding Database Concepts 2 Creating Tables from Report Files 28
Data 2 Creating Tables from Adobe PDF Data '32
Files, Records, and Fields 2 Reusing Table Layouts 33
Understanding ACL 3 Copying and Renaming Table Layouts 33
Who Uses ACL? 3 Linking Table Layouts to New Data Sources 33
What Can I Do with ACL? 3 Importing Table Layouts from Other ACL Projects 33
Viewing ACL 4 Exporting Table Layouts 33
The ACL Interface 4
The ACL Project 5 Module 3: Expressions 35
Understanding Expressions 36
Understanding Data Analysis 6
Creating Filters 37
Elements of Data Analysis 6
Filter Elements 37
The Data Analysis Cycle 6
Filter Examples 37
Module 2: Data Access 9 Creating Filters 38
Understanding the Data Access Process 10 Naming and Saving Filters 38
Locating Data 11 Creating Computed Fields 39
Creating Partnerships with Information Staff 11 Unconditional Computed Fields 39
Educating Yourself and Your Staff 11 Conversion Computed Fields 39
Identifying Available Data 11 Conditional Computed Fields 40
Determining Available Data Formats 11
Acquiring Data 13 Module 4: Data Integrity Verification 41
Understanding Data Integrity 42
Determining Your Analytical Objectives 13
Sources of Error 42
Requesting Data 13
Testing Data Integrity 43
Transferring Data 15
Checking Validity 44
Accessing Data 16
Using the Verify Command 44
Creating Tables to Access Data 17
Creating Tables from Flat Files 17 Confirming Control Totals 46
The Count Command 46
Creating Tables from MS Excel Data 20
Creating Tables from MS Access Data 22 The Total Command 46
The Statistics Command 47
Creating Tables from ODBC-Compliant Data 23
ACLI CERTIFIED iii
11.1.1.13“
Contents
What If Control Totals Do Not Match? 47 Comparing Sort and Index 67
Checking for Correct Bounds 48 Quick Sort 67
The Statistics Command 48 Combining Data 68
The Between( ) Function 49 The Extract Command with the Append Option 68
What If Your Records Are Not within Specified Bounds? .49 The Join Command 69
Finding Missing Items 50 The Relations Command 74
The Gaps Command 50 Relating Tables 75
The ISBLANK( ) Function 51 Comparing Joins and Relations 78
What If You Find Missing Items in Your Table? 52
Finding Duplicates 53 Module 6: Reporting Results 79
Reporting with ACL 80
The Duplicates Command 53
Creating a Report from a View 81
What If You Find Duplicate Entries? 54
Creating New Views 81
Testing for Reliability 55
Formatting Views 81
Recalculating Numeric Values 55
Designing Report Layouts 83
What If Your Computed Values Do Not Match? 55
Printing Reports 83
Using Other Data Integrity Tests 56
Testing Reasonableness 56 Advanced Reporting 85
Testing Relations 56 Creating Sorted Reports 85
Creating Reports That Contain Subtotals 86
Module 5: Data Analysis 57 Creating Multiline Reports 87
Profiling Data 58 Generating Reports with Crystal Reports 89
The Classify Command 58 Creating Crystal Report Templates 89
The Summarize Command 58 Generating Reports Using Custom Templates 90
Comparing Classify and Summarize 59 Creating Graphs 91
The Cross-Tabulate Command 60 Viewing Graphs Created by a Command 91
The Stratify Command 61 Creating Graphs from a View 91
The Age Command 63 Using Graph Options 91
Isolating Data 64 Using the Log 93
Filters 64 Adding and Viewing Log Sessions 93
The Extract Command 64 Adding Comments 93
The Export Command 65 Exporting from the Log 94
Organizing Data 66 Copying and Pasting Results 94
The Sequence Command 66 Searching the Log 94
The Sort Command 66 Documenting Your Analysis 95
The Index Command 66 Create Project Notes 95
iv Foundations of ACL: Concepts and Practices
Module 1: Fundamentals Understanding Database Concepts
Understanding Database Concepts
Data Files, Records, and Fields
Data is everywhere. Every time you use a credit card, make a Modern data structures are built from files. A file is a named
telephone call, or log on to a website, you leave a digital footprint. collection of information stored or processed as an individual
Yet tew of us understand what data is. entity.
For example, consider the following string of numbers: A file is composed of a number of smaller units, called records and
fields. A record is a collection of related information grouped for
04092003
processing. A field is an area in a record where a particular class of
You might interpret this string of numbers as: data is stored.
A useful analogy for understanding these concepts is the
An account number 04092003
telephone book, which is a collection of information about the
A transaction amount $40,920.03 telephone users in a particular community.

A date April 9, 2003 A telephone book can be thought of as a file. Each entry for a
or, depending on your date preference,
telephone user is a record and each type of information repeated in
September 4, 2003
every record—the name, address, and telephone number—is a
field.
To work with raw data such as this string of numbers, you must
know how to interpret it and what to do with it. In the same way, a
computer must first be told how to interpret raw data and how to
manage it. Helmi Liimatta 2376 Front St. [phone] I
For example, assume an employee enters a duplicate employee Sean Ganton 1949 Scotia St. [phone]
number so that he or she receives two checks for the same pay
Chris Rockstad 4923 Yew Ave. [phone]
period. To identify this problem, the computer must be
programmed to interpret the raw data as employee numbers and Jacob Friesen 54 King Rd. [phone]
to identify the duplicate employee numbers as irregularities.
Records
Fields
fig. 1-1 Data structure sample
2 Foundations of ACL: Concepts and Practices
Fundamentals
To use ACL effectively, it is necessary to understand key data analysis
concepts and the data analysis cycle. You must learn how to navigate the
ACL interface, how to store and organize your work in ACL projects, and
how to work with the many analytical tools ACL provides. This module
Understanding Database Concepts
introduces you to data analysis and the basics of working with ACL
Understanding ACL software.
Viewing ACL
Understanding Data Analysis
Print Project Contents 95
View and Print a Table History 95
Record Notes 96
Activities 97
Fundamentals 98
Data Access 100
Data Integrity Verification 101
Data Analysis 102
Reporting Results 110
Solutions 111
Fundamentals 112
Data Access 115
Data Integrity Verification 117
Data Analysis 120
Reporting Results 131
Index 133
ACriu
........ ..
Contents
vi Foundations of ACL: Concepts and Practices
Module 1: Fundamentals Understanding ACL
Understanding ACL
ACL is a tool that lets you read and analyze data. The data may be Process different data types from different systems
in different types of files scattered across numerous databases on
ACL can combine data from modern databases, legacy systems,
different platforms. Regardless, ACL can help you access the data,
and report files. You can then analyze the data as if it was all from
ask intelligent questions about it, and distill the information
the same source.
needed to answer those questions.
Process files of any size rapidly
Who Uses ACL?
Unlimited file-size capability and high-speed processing make it
ACL is used by data analysts, auditors, accountants, and other possible to analyze millions of records in seconds with ACL.
business professionals who need timely access to data and the
means to analyze it efficiently and effectively. The information Analyze large data sets in their entirety
ACL provides allows confident management planning and
The large capacity and high speed of ACL allow you to work with
decision-making.
files in their entirety instead of through samples. Knowing that
you have examined an entire file gives you greater confidence that
What Can I Do with ACL?
your results are complete and correct.
Your use of ACL is limited only by your imagination. Because ACL
Automate analytical procedures
provides such a huge range of options, you can accomplish almost
any goal in the realm of data analysis. Automating your analysis can save time and effort. ACL scripts
can be programmed to be interactive or to run as continuous
Gather essential information for decision making monitoring applications.
-
ACL offers a full range of analytical tools, including functions,
Maintain a record of your work
commands, and scripts. These tools let you find answers to your
questions quickly and easily. When you use ACL, every step you take is recorded in the log. This
log, or audit trail, lets you review, document, and repeat the steps
Retain the integrity of your data of your analysis.
Because ACL is a read-only application, it cannot alter the data
source. This lets you access production data directly and safely.
AC LI 3
Module 1: Fundamentals Viewing ACL
Viewing ACL
ACL provides the means to access and analyze your data through: Welcome tab
• Multi-level organization within a project
The Welcome tab is a fast and easy way to navigate between your
• Easy creation and manipulation of project elements projects in ACL. By clicking the links provided, you can create a
• Analytical tools such as functions, commands, and scripts project, open a project, or access different parts of the ACL
website. When online, you can download product enhancements,
submit support requests, and search the knowledge base.
The ACL Interface
Project Navigator
When you open ACL, you are presented with the application
window showing the Welcome Tab, the Project Navigator, and the The Project Navigator has an Overview tab and a Log tab. The
Status Bar. Overview tab lets you organize items in your ACL project into
folders. The Log tab lets you browse a chronological record of your
past activities, view command results, and save portions of the log
k Eck Eke Anahne Stowing Ptopicat■ons Took Serve, Wind. Nob
rt& A . 1'4;11 !Jitr 7.77. 77; 0) : to another file.
• >
• N Wekome
( No Open Poled )
Status bar
AC
Welcome to The status bar displays information about the active table,
including the table name, the number of records, and any active
filter criteria.
1.4,1.•/Int,
Welcome Tab
Project Navigator
Status Bar '
fig. 1-2 ACL interface
4 Foundations of ACE: Concepts and Practices