What Every IT Auditor Should Know About Transforming Data for CAATs

What Every IT Auditor Should Know About Transforming Data for CAATs

Group 10

Rhaditza Ramadhan (C1I016016)

Hanna Hanifa (C1I016040)

Advances in information technology and computers nowadays have led to the work of computers everywhere in the business community, whether small or large entities. That growth causes an increase as the number of data entities and remains, due to a significant reduction in storage costs. Today, the result is referred to as  "Big data. " Regarded as a hot topic in it, big data is also the result of collecting non-financial data along with financial data. Everything from network logs to industry and economic data is being collected. This fact encourages expert needs in using Computer assisted Audit/engineering (CAATs) tools.

What is CAATs?

Computer-assisted audit techniques (CAATs) is the practice of using computers to automate the IT audit processes. CAATs using basic office productivity software such as spreadsheet, word processors and text editing programs and more advanced software packages involving use statistical analysis and business intelligence tools. But also more dedicated specialized software are available. CAATs have become synonymous with data analytics in the audit process.

None of this is new to the average business person, but there is something many may not realize. Big data is going to get bigger—much bigger—and faster. According to the Berkeley School of Management, more data have been created in the three years 2009-2011 than in the whole of human history.1, 2 According to one expert, in 2005, there were 130 exabytes of data in the digital universe. By 2010, it was 1,227 exabytes—a 10-fold increase. By 2015, it is predicted that the digital universe will contain 7,910 exabytes.3 In addition, from 2011 to 2020, data being managed by IT professionals are expected to increase 50 times, with the number of IT professionals only increasing 1.5 times.

The data warehouse segment of IT has developed a sound methodology to do just that by using extract-transform-load (ETL) techniques and tools to get various source data sets into a single warehouse, where business analytics tools are used to analyze the huge amounts of data amassed in the warehouse. This methodology should be equally beneficial to IT auditors using CAATs and big data.

Using ETL Methodology for CAATs and Data Mining

The ETL approach can be applied to CAATs and data. Most experts agree that the hardest part of using CAATs is the data extraction stage. In the ISACA Journal, Volume 6, 2010, this column addresses an extract issue. In short, the main problem is getting data from operational computers, generally online transaction processing systems, into forms and formats compatible with CAAT.

Three types of issues that cause extracted data to need to be corrected after they are extracted:

1.    Formatting issues related to the way data are formatted in a particular extraction approach.For example, the export could be in a report format (e.g., a digital PDF report) and have a lot of extraneous lines of data (e.g., headings, subtotals). In addition, some reports take a single transaction and list the information on two lines.

2.    The various idiosyncrasies in the way that specific data values are presented and/or formatted. For example, a negative number could have a negative sign in front of or behind the figure, or negative numbers could be put in parentheses. Sometimes data have leading zeros or spaces or trailing zeros or spaces.

3.    When data are not optimal for CAAT commands and procedures. Almost all CAATs have procedures (commands) that are dependent on certain columns being defined as numeric, character or date data.

Figure 1 provides a list of the second type, “messy data” scenarios, and figure 2 illustrates situations of the third type.      

Figure 1 – Examples of Messy Data

Leading or trailing spaces

Leading or trailing zeros

Inconsistencies in the way data values are keyed

Hanging parentheses

Nonprinting characters

Figure 2 – Examples of Optimizing Data

Changing case of character data to something consistent

Filling empty cells with something (e.g., “N.A.” for character data)

Separating dates into four columns for day, month, year and day of week

Splitting a column into two columns or splitting off part of the data value to a separate column

Converting a column defined as a character to numeric, or vice versa

Removing hyperlinks

Converting formulas to values

These situations create a need to clean the data—similar to cleaning data going into a data warehouse. This article refers to this intermediate process as transforming, using the same terminology as the data warehouse ETL methodology.

Tools for Transforming

The key to the transform process is understanding what needs to be transformed and having a suitable tool to perform a specific transform procedure. The IT auditor needs a tool that makes the transform process as easy as possible. In fact, a tool that is good at transforming data could be cost-effective even if a different CAAT is being used to perform the procedures after the data are transformed and loaded into the CAAT

Conclusion

This article discusses the transformation process. In the Data Warehouse, the transformation process is usually considered the most time consuming; The same applies to using the CAATs. The good news is after the extract and changing steps are done correctly, the load section, to get the data to the CAAT, is usually a simple process.

The first point of transformation is that, in order to be effective, IT auditors need to understand the CAAT procedures and commands to determine what needs to be addressed or changed in the data for that command to execute properly. The second point is to understand the need for a methodology or voice approach to transformation. Finally, the IT auditor requires an effective tool that can do most, if not all, of the three types of transformation problems are discussed.