These features are available for viewing QueryPair results both in the Design view (Design-Time Run results) and in the Runtime Dashboard (Scenario executions).
CLOB Peeking
Under certain circumstances, "string" results are returned in QuerySurge as CLOB data types. This can occur for a few reasons (as outlined in our QuerySurge and CLOB Data article). Results flagged as CLOB's in QuerySurge are displayed with the notation "{clob}" by default in Results and show the icon in the column header:
The "{clob}" notation is used to prevent pushing potentially large numbers of large result objects across your network to browsers.
There are three ways to view data contained in a field which has been flagged as a CLOB. The first is to right click any individual field and select View Field in Popup. A popup will appear which contains the CLOB data stored in the field. A second option is to use the View Options menu in the upper right of the data display to Peek Clob Fields:
The third options is to set the Peek Clob Fields settings in the User Profile > Preferences options. Note, selecting this option will always automatically resolve the CLOB to its underlying value but only for the individual user that selects the option:
When selected, all columns flagged as CLOBs return a subset of the data contained in each field (first 100 characters of the CLOB by default):
Note: The Peek Clob Fields option is disabled if the result set contains no CLOB columns.
CLOB Peeking Server Settings
There are a few configuration options available to control the behavior of CLOB Peeking, available in QuerySurge Administration -> Server Properties:
- Enable Clob Peeking (default: on): Turn the ability to peek CLOBs on or off completely, for all users. Disabling peeking avoids the potential for processing significant amounts of data and transferring it between QuerySurge and the browser.
-
Peek Clob Length (default: 100 characters): Maximum number of characters to retrieve from each CLOB value when peeking. If the original value is shorter than this maximum, it is displayed or exported unchanged; if longer, it is truncated to this length with ellipses ("...") appended. Increasing this value will increase the amount of data processing/transfer when viewing/exporting results.
Note: Full CLOB data can be exported on a field-by-field basis by right-clicking on any field and selecting Save field contents in a text file.
Show Whitespace
Often data validated by QuerySurge contains leading or trailing whitespace characters which can be flagged as data failures by QuerySurge when the ETL process has added or truncated these characters. When these types of failures occur it is often hard to determine the differences between source/target records. To assist with identifying these characters the view option Show Whitespace adds indicators of trailing and leading spaces () or tabs (
):
Hide Trailing Zeros (v. 7.2+)
When viewing results in QuerySurge, the data displayed will be displayed in the same format that is returned from your data source. This can lead to insignificant digits being displayed for numeric fields in QuerySurge UI. In the below example, a query was executed against an Oracle database which returns all numeric fields as a number with high precision and scale.
To make analyzing and viewing the results from these numbers easier, Insignificant digits can be truncated in the UI as seen below.
Correlate Rows
Note: Correlate Rows can only be utilized when Shared Key columns are specified on a QueryPair.
When viewing failures, it is possible for results rows to be out of alignment in the side-by-side view mode, when non-matching rows exist. In the example below an additional row for CUSTOMER ID "001492" exists in the target which causes the results to be misaligned for record "001491"
To assist with easier analysis of result data, the Correlate Rows option automatically adds blank rows to re-align the resultset, as seen below:
Correlate Rows is enabled by default when Shared Keys are specified for a QueryPair. (This option can be disabled in View Options if desired).
Note: When utilizing Correlate Rows, ensure that filter/order are the same between the Source and Target result set.
Show Inferred Data Mismatches
When viewing results in QueryPairs without Shared Keys, QuerySurge is formally unable to determine whether a mismatched record is a Data Failure or a Non-Matching Row. The reason for this is that QuerySurge does not assume, in the absence of Shared Keys, the significance of one column over another. Thus, for example, a mismatched Source row might be related to a mismatched Target row, or it might be a true Non-Matching row (meaning it is not related to any Target row). There is no way to distinguish between these two situations without Shared Keys. Records like this are highlighted in light red to indicate that they are a non-categorized failures:
Built into QuerySurge is an inference engine which attempts to identify data discrepancies between the Source and Target rows when no Shared Keys are specified. The logic utilized by QuerySurge is based upon the row number of the record from the Source/Target. This means ordering of records from your resultset can have a significant impact on the algorithm. To enable this setting, select View Options -> Show Inferred Data Mismatches.
Once enabled QuerySurge highlights each cell that it infers is a data mismatch. An example of this is below:
Note: Use this feature with care! The highlighted cells are "best guess" inferences, not absolute mismatches. Absolute determination of Data Failure Rows and Non-Matching Rows can only be achieved when Shared Keys are used.