Data Acquisition Tips from the Pros

Regardless of what data analytics tool you use, your results are only as good as the data you’re using. Data acquisition and preparation is a S. L. O. W. process, even if you know what data you need. We’ve rounded up some time-saving tips and advice from data analytics pros.

Organizations across all industries are dealing with unprecedented amounts of data. Spend time exploring the types of data your organization is creating and collecting. When evaluating your data universe, consider these questions:

• What kind of data do we need?
• How owns the data?
• What systems gather the data we rely on currently?
• Are the data and systems complex?
• Are your systems integrated with one another?
• Is the data clean and organized?

Communicate with Data Stewards

Ben Johnson with Utah State University shared some of the lessons he learned from a mentor who was retiring and his own experiences navigating the data acquisition process at the most recent IDEA User Conference.

IT, data stewards and database administrators understand data sources and the underlying systems to help you identify what data is available for testing. Clear and regular communication will make them more inclined to cooperate, especially if they understand you are part of their team. Take a genuine interest in them. Get them to talk about their interests, visit their offices and make a connection. Work alongside them to help them understand your role and goals.

Tips for working through the data request and acquisition process:

• Begin by educating the data steward(s) about what you’re doing and why you need the data
• Start early – Identify what data you need and make requests as soon as possible
• Work with company management to identify skilled IT resources which might be able to help with data acquisition requests
• Rally management to gain the support and trust of IT
• Be prepared for pushback and questions
• Educate data stewards about IDEA’s read-only access capability to provide assurance that source data remains protected

Beware of Canned Reports

One of the more common responses from IT is, “We can’t get the data in the format you’ve requested.” However, there are very few accounting systems that cannot provide the necessary information in the format needed. Canned reports often lack complete data, such as date, time, owner, etc. Be sure to include all the fields you will need to get a complete set of data.

Data Wrangling guru Mario Perez has created several videos for professionals to help them put data analytics to work. To get the right fields, he recommends working with IT to get a data dictionary, also known as a record layout.

• Contains all the fields in the file you are requesting
• Provides the field name, type of field and description
• Gives you a better understanding and context about the different fields in a file you want to request

Working with Tables

Most large ERP systems like Oracle and SAP use standard tables. Auditors can request the Entity Relationship Diagram (ERD) located within the Technical Reference Manual to decipher where the data “lives” and the tables required for analysis. Some companies create custom extensions which are built into the application. The database administrator should have information about the data stored within these custom tables.

Working with the 3 Most Common File Types

Data acquisition and analysis expert Kevin Rockecharlie with BKM Sowan Horan shared tips for working with the three most common file types during a webcast to IDEA users, including what to ask for to get the right data in a workable format for analysis.

If you can select the export file type, ask for a delimited file using the pipe symbol ‘|’ as the delimiter. The reason for asking for pipe as a delimiter is that it is less commonly used in traditional datasets, whereas commas are used frequently in description or note fields. However, if this is not possible most systems allow you to extract data using following file types:

Comma Separated Values (.csv)

Files are commonly used to transport large amounts of tabular data between companies or applications that are not directly connected. The files are easily editable using common spreadsheet applications like Microsoft Excel. Fields are separated by commas, and records are separated with system end of line characters.

The key to importing .csv files or any other delimited files, is knowing the delimitating character or field separator, which is often a “,” or “;”. The import assistant feature in IDEA will help you select “Delimited” as the file type and specify the field separator based on whether characters are separated by commas, colons, semicolons, tabs or spaces.

Print Reports (.pdf or .txt)

Can range from simple to extremely complex. The Report Reader within IDEA reverse engineers PDFs into clean data table imports, and can read both structured PDFs and those with irregular sizing and positioning. Some key criteria to keep in mind are:

• Files should be system print files – avoid scanned paper when possible
• Files should be true-text font type – ask for this format when making the data request
• Files should not contain any shapes, text boxes, references, etc.

Original generation files are easier to import into IDEA. When in doubt, contact the IDEA Help Desk for assistance and additional tips.

Microsoft Excel (.xls)

The key to importing spreadsheets is formatting the information properly prior to importing.

• Require First row field names (no spaces)
• Remove blank columns (creates noise in the spreadsheet)
• Remove No (sub)totals
• All fields in Excel format (i.e., character, number, date, amount, etc.)

Don’t Forget to Reconcile Source Data

Once information is imported into IDEA, all information must be reconciled to the source data. The most effective way to do this is through “Control Totals.” The “Control Totals” can be used to calculate a grand total for numeric fields. In addition, you can use the “Field Statistics” to view fields of data type: Numeric, Dates and Time. Through this view you can see the grand total as well as the absolute grand total and check for any items that may have come in as errors. If the data does not reconcile, import errors may go undetected. This may include underlying issues with data supplied by the client. You can view both the “Control Total” and the “Field Statistics,” under the Properties window.

More than ever, leaders are relying on data to make decisions. Good data acquisition and preparation practices give you confidence that the data they rely on is accurate and complete. If you hit any roadblocks along the way, contact the IDEA Help Desk – we’re here to help you put data analytics to work!

Need Help from the Pros?

Our Technical Services team specializes in data acquisition, importing and reconciliation services. We can handle all your needs from working with data stewards to obtain the right data in optimal formats to importing large or complex data and confirming control totals for an extra measure of accuracy. Turn your challenges over to our pros to save hours of effort! Contact [email protected] to discuss your goals.