Audimation Services has been acquired by Caseware International Learn More.

X
Icon


Blog Image

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. 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. Click “Select” to select the secondary database.

 

    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.

 

Visual-Connector

 

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.

 

 

Append Databases

 

 

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.

 

Compare Databases

 

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.

 

Match Key

 

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



Posted By

By Kris Willison
Kris joined the Professional Services team in January of 2015 as a Solutions Specialist. She has an extensive background in Software and Database Development accumulated from thirty years in IT support with twenty years’ experience in database development, cleanup, audit and migration using Microsoft Access. In her time with Audimation, she has received client praise for her “Top Tier” engagement on Monitor and Scripting projects. Kris enjoys looking at problems from new angles to determine the most efficient means of meeting the clients’ needs. Kris has been breeding/showing purebred Balinese cats since 1972 and Oriental Longhairs since 1996. She also hosts one of the largest online pedigree database sites for Siamese and related breeds with nearly 600 users worldwide.


Related Posts
Harnessing Big Data to Find Fraud?
Jan 01 Houston - January 2014 - Big data analytics holds big promise when it comes to helping companies identify fraud, even before a compliance failure unfolds. There...
Tech Tip: Magic of Field Statistics
Aug 03 IDEA’s Field Statistics can save you a wealth of time. It should be the first thing you go to after importing a file. Why? Every control total is avail...
Take a Lean Six Sigma Approach Using Data Analytics
Oct 16 Henry Ford once said that if he had asked people what they wanted, they would have said faster horses. Constant innovation keeps businesses thriving. Regar...
BROWSER NOT SUPPORTED

This website has been designed for modern browsers. Please update. Update my browser now

×