Tech Tip: Comparing Databases

One of CaseWare IDEA®’s most valuable features is its ability to connect, compare and match information from different databases to get a complete view of what is transpiring across the organization. From evaluating annual sales performance to cross-matching budgets with actual costs, the possibilities of are limited only by your imagination.

 

The Compare Databases task lets you identify differences in a single Numeric field between two databases (referred to as the primary and secondary databases), for a specified common match key. The databases being compared need to be in the same project folder. This feature is commonly used to compare the same data at two points in time, such as the payroll at the beginning and end of the month to identify changes in salary for each employee (the match key). Neither the match key fields, nor the fields for comparison need to have the same names in the two databases, but the match key fields must be of identical type.

 

There may be more than one record for each value in the match key in either or both of the databases. The Compare Databases task compares the totals for the Numeric field in each database. Since it first summarizes then compares the databases, it identifies how many records there are in each database for each match key field.

 

Step-by-Step
Before beginning the Compare Databases task, ensure that the primary database is active.

1. 1. On the “Analysis” tab, in the “Relate” group, click “Compare” to open the Compare Databases dialog box. Specify the Numeric field in the primary database to be compared by clicking on the “Total Field.”

 

1. 1. Click “Select” to select the secondary database.

 

1. 1. Specify the Numeric field in the secondary database to be compared by clicking on the “Total Field.”

 

1. Click “Match” to specify the match key which is the field or fields that will be used to create the output database as a child to the active database and includes the following fields in addition to the field(s) which make up the key:
   • P_NRECS: Number of records for the match key field(s) in the primary database.
   • P_TOTAL: Total of the selected Numeric field values for the match key field(s) in the primary database.
   • S_NRECS: Number of records for the match key field(s) in the secondary database.
   • S_TOTAL: Total of the selected Numeric field values the match key field(s) in the secondary database.
   • DIFFERENCE: P_TOTAL minus S_TOTAL

 

Different Ways Databases Relate:
• One-to-One – The field used to connect both tables only has unique values in every row.
• One-to-Many — One table holds unique values for every row, but the other table holds duplicate values for any or all of the corresponding values in the first table.
• Many-to-Many – On both sides of the table, there are duplicated values, causing excessive calculations for every query run against it.

 

Using IDEA to Connect & Compare Databases:
In a previous Tech Tip we shared how to use IDEA’s Join and Visual Connector features to help you search for matches and correlations between different data sets. Now we will share different ways databases become more powerful by linking the data located in separate files.

 

• Join provides a side-by-side comparison to create a wider file with more fields in the record.
• Visual Connector does the same, but you can work with multiple files to produce a broader record.
• Append brings multiple files/pages together to create longer, continuous files, including common column names for each file.
• Compare takes a specified numeric field from each file and compares the totals for specified data values between the files. For example, you can match on SALESREP and report the total sales, number of records in each and the amount difference between the two.

 

“Join” is intended for One-to-One or Many-to-One relationships. The secondary file limited to 1 record per Key Field(s) used for the match. IDEA will always link from the primary file to the 1st record found in the secondary file, ignoring the 2nd record. For these situations, use “Visual Connector” to perform Many-to-Many relationships.

 

 

With “Join” the resulting file will never have more records than the original primary file. With “Visual Connector,” each key value in the primary file will link to a matching key value in the secondary file – the number of records in the resulting file increasing exponentially.

 

“Append” basically “stacks” the files. Detailed Sales appended to Detailed Sales Previous Year results in 1438 records.

 

 

 

 

“Compare” works exactly as it it sounds. It summarizes the primary and secondary files, performs an “all records join” on the selected key(s) and presents the results.

 

 

For the “Match_Key” total number of records, total value in P (primary) and S (secondary) plus the difference.
In this example, you’ll see Sales Reps 119-128 are new for the current year, with no sales in the prior year.

 

 

We’re here to help you put data analytics to work with support every step of the way!

Please contact our experts via the Help Desk for assistance utilizing any of IDEA’s features or functions.

Best Practices , CaseWare IDEA , Tech Tip