Versions:4.0+
The Query Wizard is a tool that allows you to generate QueryPairs automatically from a pair of Connections.
This is useful for Business Analysts, SMEs or other team members who have data and business-rule knowledge, but who are new to SQL. QuerySurge power users can benefit from the Query Wizard as well by using it to generate "scaffolding" QueryPairs that can then be elaborated to test transformations of increasing complexity.
IN THIS SECTION
Accessing the Query Wizard
To access the Query Wizard, select the Launch Query Wizard item in the Design Menu.
The Query Wizard will launch and you'll see the welcome. To get started, click the Next button in the lower right.
How Does It Work
The Query Wizard works by helping you map tables from a Source Connection to a Target Connection. Based on your input, the Wizard will automatically create QueryPairs that compare data based on the verification type that you have chosen.
Using the Query Wizard
SELECTING YOUR SOURCE AND TARGET CONNECTIONS
The Source and Target Connections are chosen by selecting a Connection from the respective drop-down list. When a Connection is selected, you will be able to view the Connection Metadata Status below it. This shows the last time that QuerySurge refreshed the metadata for the Connection, as well as display any errors or warnings that may have occurred when the metadata was last refreshed.
UPDATING CONNECTION METADATA
If the metadata for a Connection is stale, you'll need to update it. To update the metadata for a Connection, all you need to do is to click on the Update button for the Connection, to the right of the Connection information. 
(see the red arrows in the image above.)
NOTE This operation may take some time to complete (especially if you have a large number of tables and columns), but you may close the Query Wizard without interrupting the operation.
CHOOSING A VALIDATION TYPE
The Query Wizard provides the following validation options:
- Column-Level Comparison - for Big Data stores/Data Warehouse mappings where column-level specification, filtering and sorting are options.
- Table-Level Comparison - for Migration projects and database upgrades with no transformation at all.
- Row Count Comparison - for quick row-count checks on Big Data stores, Data Warehouses, Data Migration and upgrade projects.
Choose the type of comparison that you want to use (The Query Wizard defaults to Column-Level Comparison.)
CREATING A COLUMN-LEVEL COMPARISON
Choose Your Schema
On the schema selection screen, select one (or more schema) for the Source and the Target Connections. This will populate the available tables for mapping.
Choose Your Tables
To configure a Table relationship, under Create a Table Relationship, drag and drop a table from Source to Target. This creates a relationship between the Source and the Target table that you have connected, which is indicated by a connection line.
Once you have made a relationship between a Source table and a Target table, the Wizard shows the columns for each table under Create Column Relationships. In addition, "SELECT *" queries will appear in the lower pane of the Wizard for the tables you have selected. If you want, you can save the QueryPair without specifying anything further.
Choose Your Columns
If you want to move down to the column level, you can create Source and Target column relationships under create Column Relationships, simply by dragging a Source column onto a Target column. You may make as many of these relationships as you like. When you connect a Source/Target column pair, note that the Source and Target auto-generated queries in the lower pane update to reflect your column choices. In the example below, we selected two Source/Target column pairs.
Columns can also be automatically linked using the "Auto Link" button highlighted below. Auto Link will connect any columns which have the same name and ignores case differences.
Filtering and Sorting
If you want to further refine your queries, you can either filter or sort (or both) your data. To create a filter, click on the green Add New Criteria button in Filtering and Sorting. Under Table, choose Source or Target; under Column, choose the column you want to filter or sort on. For Operation, choose the type of action you want to perform, and if a Value is needed by the Operation you selected, choose a Value (for example, if you chose a column of type Date, with the Operation 'After', the value will be a specific date after which the rows are included).
CREATING A TABLE-LEVEL COMPARISON
Choose Your Schema
On the schema selection screen, select one (or more schema) for the Source and the Target Connections. This will populate the available tables for mapping.
Choose Your Tables
To configure a Table relationship, under Create a Table Relationship, drag and drop a table from Source to Target. This creates a relationship between the Source and the Target table that you have connected, which is indicated by a connection line.
Once you have made a relationship between a Source table and a Target table, the Wizard shows the auto-generated "SELECT *" queries in the lower pane of the Wizard for the tables you have selected. You can now save your QueryPair.
CREATING A ROW COUNT COMPARISON
Choose Your Schema
On the schema selection screen, select one (or more schema) for the Source and the Target Connections. This will populate the available tables for mapping.
Choose Your Tables
To configure a Table relationship, under Create a Table Relationship, drag and drop a table from Source to Target. This creates a relationship between the Source and the Target table that you have connected, which is indicated by a connection line.
Once you have made a relationship between a Source table and a Target table, the Wizard shows the auto-generated "SELECT COUNT(*)" queries in the lower pane of the Wizard for the tables you have selected. The queries may contain type casts to adjust for differences between database products. You can now save your QueryPair.
NAMING AND ORGANIZATION
The Validation Name will be used as the name of the folder where the QueryPairs will be created, as well as the name of the generated Test Suite.
The QueryPairs will be automatically named using the following format:
Source table name - Target table name
If you want to edit the name, just double-click on the name to make it editable.
NOTE If a QueryPair name exceeds 127 characters, it will be truncated.
WORKING WITH QUERY WIZARD-GENERATED QUERYPAIRS
Once QueryPairs have been created, they can be modified just like any other QueryPairs. The same applies for any generated Test Suites.
Comments
0 comments
Please sign in to leave a comment.