More packets of data pass through the internet than there are grains of sand on the earth. Some organizations have already recognized the great potential that lies hidden within their operational and administrative data stores. For that reason, data management and data quality are among the most important considerations for business intelligence practitioners. However, practitioners must spend most of their effort on curating, cleaning, and preparing data before they can glean any meaningful information through analytics.
Increasingly, internal audit functions also are expected to use data analytics to tap into their organization's data stores. To do so, auditors need a way to understand, structure, and catalog that data so it tells a story. In the words of the movie hero, Indiana Jones, "it belongs in a museum."
Internal auditors and data analysts within the Canada Revenue Agency's (CRA's) Audit, Evaluation, and Risk Branch (AERB) are adapting data warehousing principles to create a data museum to support internal audit engagements. This database environment contains useful data curated from various sources to describe historical and current performance levels of CRA operations and administrative activities. The data museum is intended to support a wide variety of engagements at any given time, and could increase internal audit intelligence.
Internal auditors, program evaluation analysts, and risk managers will be able to browse the data museum, helping them provide more insight, oversight, and foresight for the entire organization. The data will be easily accessible in a format that is ready for analysis, and auditors will be able to browse through the relevant exhibits to gain insight into the controls they are examining.
In setting up a data mus-eum, internal audit departments need dedicated "archaeologists" to discover and curate new data sources. These individuals select data sets to add to the museum based on four criteria:
- Relevance – Would the data provide information about internal controls, identifying and mitigating risk? Would it help make data-driven business decisions?
- Reliability – Is the data relatively free from integrity issues? Would it be easy to prepare the data for permanent display and use by auditors?
- Reusability – Will the data be able to support a critical mass of engagements?
- Rarity – Is the data currently unavailable in a format that is ready for immediate use?
In addition to curation, the data museum relies on thoughtful arrangement of exhibits into themes, similar to how traditional museums are organized. Data are extracted from the CRA's data warehouse and source systems, assessed for value, and prepared and made into exhibits that are displayed by theme for internal audit use. Some data artifacts also can be reused in multiple exhibits and categorized in other themes.
If a particular engagement requires new data, which is not available, then a new exhibit can be created. If the new exhibit proves to be reusable for future engagements, then it can become part of the data museum's "permanent collection."
The HR Exhibit
One of the exhibits in the CRA's internal audit data museum contains information about all employees within the agency. The human resources (HR) exhibit is a curated set of data tables from the CRA's HR database, which was prepared and loaded into the museum. These tables include employee status, personal information, payroll, time reporting, and assignment.
In setting up the exhibit, the AERB studied the structure of each table and the relationships among them, allowing the department to automate some aspects of data preparation and maintenance. It used Microsoft SQL Server Integrated Services to extract, transform, and load the data, which is refreshed regularly. The department also continues to search for and add new artifacts to the exhibit to keep it relevant, which enables internal auditors to retrieve recent information about any employee or groups of employees.
Using SQL The fastest way to start exploring the AERB's HR exhibit is to run query statements using Structured Query Language (SQL), which selects records from the exhibit and can be exported into reports. Basic SQL statements are not difficult to formulate, and some of the department's internal auditors are already using them to browse the exhibit to access, analyze, and review its data.
A simple SQL statement is comprised of these elements and expressions:
Internal auditors can use the information in the HR exhibit as evidence in support of engagement observations and findings. There is also potential to uncover risks to achieving control objectives through trend analysis and data analytics.
The SQL statement below is an example of a simple query of the HR exhibit, which produces a list of managers and executives assigned to various cost centers within the CRA.
SQL Statement (Pseudo Code)
Corresponding SQL Statement (From Pseudo Code)
SQL Query Results (From SQL Statement)
Internal audit also designed more complex queries to identify managers and employees who used a large amount of sick leave relative to their vacation leave. The information was used to test management oversight of leave usage and identify where high-risk governance issues may exist.
Tools In addition to using SQL statements, there are other means to browse and analyze the HR exhibit. Because the data museum resides on a platform that supports Open Database Connectivity, auditors can connect to the data with more sophisticated analysis tools as well as import data into traditional audit tools.
For data visualization and advanced reporting, auditors can establish a direct connection to the data with Microsoft SQL Server Reporting Services. For simple reporting, auditors export the results of queries to Excel. The flexibility of the AERB's environment will also allow the department to consider using other data visualization tools.
A Horizontal View A horizontal view of an organization can be achieved by exploring the various exhibits within a data museum holistically. For example, the AERB's HR exhibit could be explored along with a financial transaction exhibit. If high-risk transactions are found within some organizational units, further analysis of HR data could determine whether there is a sufficient number of employees with various roles to achieve effective segregation of duties.
Establishing a data museum can give internal audit departments insight from the vast amounts of data within their organization. To get started, they should:
- Take stock of recent engagements and determine whether there are any frequently used domains of data, which can be formed into exhibits.
- Decide on an environment to house the museum. Choose a relational database system that will meet internal audit's needs.
- Start small. Design the first exhibit, and understand the business line and corresponding data repository. Decide which tables and data fields the department should keep.
- Learn how to write basic SQL statements. This will allow auditors to "interview" the exhibits within the data museum.
- Ensure audit trails and logs have been activated so browsing activities comply with internal security policies. Leverage this ability to validate whether management follow-up occurred.
As an integral part of the internal audit strategy, a data museum can give auditors insight into the functioning of controls, the achievement of business objectives, and the identification of risk. Information extracted from queries also can help auditors scope audit programs appropriately. Auditors can perform more sophisticated analytics on the data during the audit testing phase as well as during audit follow-up to assess whether management action plans resulted in improvements. If the data museum is visited regularly — independent of any particular engagement — then the information could be used as input into risk-based audit planning activities, helping to increase overall internal audit intelligence.