Working with QuerySurge™ QueryPairs

A QueryPair consists of two queries that are to be run against Source and Target data stores in order to test one aspect of the data relationship between the Source and Target. Typically, a single QueryPair will test a specific mapping (or one aspect of a mapping) between the Source and Target.

QueryPair Queries

---

A couple of general comments about QueryPair Queries are in order:

• QuerySurge places no limitations on the query syntax that you use - you are free to use any syntax (standard, proprietary or custom) that your data source understands.
• There are a few QuerySurge-specific naming limitations. See Reserved Words and Terms, Reusable Query Snippet Names, Snippet References.
• For QueryPair- and Query-related sizes, see For sizes, see Sizes of QuerySurge Things.

Creating QueryPairs

---

All QueryPairs are stored in the QueryPairs Folder or in one of its subfolders. You are free to create folders and subfolders as needed. Reusable Query Snippets are stored in the Query Snippets Folder or one of its subfolders.

Create a new QueryPair by right-clicking on a folder and selecting Create New QueryPair. Aside from a name for your new QueryPair, you can provide an optional description and an optional Mapping ID. Once you have created your QueryPair, a side-by-side set of editors opens for the Source and Target queries. In addition to inputting and editing your queries, you can also assign a Connection to each Query in the QueryPair editor, which indicates the data source against which the Query is to be run (more about where connections come from in the Administration section).

Source and Target Queries

---

When you open an existing QueryPair, you see the Source and Target editors in the QueryPair tab, followed by the QueryPair ID:

QueryPair Properties

The Properties tab shows the QueryPair Name, Description and Mapping properties:

Optional settings for QueryPair analysis also appear on this tab. All of these may be edited and saved at any time.

The optional settings that you may configure are:

1. Data Type Checking - QuerySurge will check data types within broad parameters - meaning it will ignore differences between all types of 'string' data, all types of 'integer' data, all types of 'date' data, etc., but will error out if Source and Target columns are different in these broad categories. If you select Type Checking Off, then QuerySurge will view all data as a string type.
2. Row Count Options - Row count differences between Source and Target may be scored as failing, warning or ignored (the default is to score this condition as failing). See Row Count Mismatches for more information.
   
3. Shared Key Column Definition - Using defined key column(s), QuerySurge can detect whether data differences between Source and Target ResultSets are due to true data mismatches or are due to nonmatching rows between Source and Target. See Key Column Specification for more information.
   
4. Column Threshold Options - Thresholds can be set for the comparison between numeric columns - either based on percentage ("If Source column 2 is within a set % of Target column 2, then the QueryPair passes") or on an absolute threshold value ("If Source column 3 is within a fixed absolute value of Target column 3, then the QueryPair passes"). For full details of this feature, see this article.
   
5. Duplicate Row Options - If you know that your data has duplicate rows, then QuerySurge's standard or default comparison algorithm will not work - it may return spurious results. If you deal with data that has duplicate rows, then you'll need to configure your QueryPair to Enable Analysis Support for Duplicates. This switches the comparison algorithm to an procedure that can handle duplicate rows. For additional details, see this article.
   

Design-Time Run

---

After opening a QueryPair, you can click on the Design-Time Run tab. The Design-Time Run tab displays detailed results for each QueryPair. If the QueryPair has previously been run in Design-Time mode, the previous run results will be showing. If a Query Pair has never been run, the following icon appears:

In order to execute a Design-Time run, click on the Run button. Remember, you must have an Agent up and connected to QuerySurge for a Run to execute. Once a Run completes, a display showing the results appears.

You may use the View Query Results button to inspect the detailed results for each Query, and the Run Again button to re-execute the Design-Time run.

Note: You can only see results for the previous Design-Time run, and that once you start a Design-Time run, any previous Design-Time run for the Query are deleted.

The Design-Time Run tab displays detailed results for each QueryPair. To view further information, you can click the “View Query Results” button at the bottom of the page. This view shows the Source and Target data as well as the comparison data, Source query, and Target query.

SOURCE / TARGET DATA (SORT, FILTER)

The Source and Target data can be sorted by an ascending or descending sort. The columns can also be filtered by a keyword or phrase of the user’s choice. To display all of the data, uncheck the filter checkbox.

COMPARISON DATA (EXPORTING, # OF ITEMS)

The comparison tab (third tab after Source and Target tabs) is named according to the results of the run (“Failures”, “No Failures”). In this tab, the user can also sort and filter the information. This data can be displayed in stacked view or side by side view using the icons in the upper left of the tab ( ). In the Stacked View, the data is shown in a single column with corresponding rows stacked on each other. In the Side By Side View, the data is shown in two columns, with the Source data son the left and the Target data on the right.