v1.4.1 - ADQ Release Notes

ADQ v1.4.1 introduces a host of enhancements and fixes aimed at optimising the user experience, improving workflow efficiency, and bolstering the reliability of data quality assessments.

New Features

DQ Workflow Bug Fixes

Several bug fixes were applied to the DQ workflow, addressing issues related to CSV and Excel input file preparation and rule output validation checks. Users can now execute their data quality workflows with increased confidence and reliability.

Improved Error Handling

Enhanced error handling mechanisms provide users with clearer and more actionable feedback in case of errors, facilitating quicker resolution of misconfigured rules and minimizing disruptions to data quality operations.

Rule Suggestion Modifications

The volume structure and backend workflow of the Rule Suggestion microservice have been re-architected for smoother deployment. Additionally, potential permission issues during file movement for processing have been mitigated, enhancing the overall user experience.

DQ Workflow Improvements

Various enhancements have been made to the rule workflow testing and batch DQ processes, covering areas such as data extraction preparation, handling of advanced insights data, and result validation. These improvements streamline data quality tasks and boost productivity.

Rule Promotion Fixes

Several bug fixes have been implemented for the rule promotion process which promotes rules from lower development environments to higher production environments. Users can confidently promote rules without concerns about potential disruptions.

Database Connection Updates

Default values for JVM arguments and the ability to specify drivers for Insights and Data Issues databases enhance flexibility and compatibility, enabling users to customize database connections more easily and avoid connectivity issues.

Insights Database Validation

A validation layer has been added to ensure accurate storage of workflow executions in the insights database, improving the presentation of results in ADQ Insights and providing users with reliable data quality metrics.

Database Data Source Views Enhancements

Extended support for wildcards in SQL queries simplifies the process of defining rules using database data source views.
Improvements to the SQL parser supports users who wish to construct complex SQL queries for their data extraction, enhancing rule creation efficiency.

Test Connection Performance Improvements

Optimized test connection performance ensures users can verify database connections more efficiently, particularly for sources with undefined schemas.

Known Defects

Spaces forbidden in rule execution group name

Rule execution group names must not contain spaces. Names with spaces will cause the manual trigger of the rule execution group to fail.

Workaround

Do not use spaces in rule execution group names.

Rule results produced by SQL rules must not contain carriage returns or line feeds

The upload of data issues fails if there are carriage returns or line feeds within data columns in the rule results files produced by SQL rules.

Workaround

Use a custom FlowDesigner project rule to perform the logic instead.

Database data source view exports convert decimals past a certain length to scientific notation

When retrieving the rule input from a database data source view created using the SQL builder or the SQL query functionality, if the data column type is a float where the decimal numbers are past a certain length (greater than 10,000,000 or less than 0.001) the JDBC connector exporting the data converts this decimal number into scientific notation.

Workaround

Use a SQL query rule to perform the check rather than a custom FlowDesigner project rule. SQL query rules perform the rule logic directly on the source database so do not need a data source view defined. The SQL query should include a CAST in the query to ensure the data is not output in scientific notation, e.g. CAST(account_balance AS DECIMAL(20, 2)). Note you will need to know the number of digits after the decimal point.

Disabled parameterised reusable rules executing

Disabled parameterised reusable rules which are not suggested by the rule suggestion profile are executing in the DQ workflow causing the process to fail.

Fix

In the SSDQ client perform the following:

Rename solution metrics-producer-reusable to metrics-producer-reusable-old.
Upload the attached solution XML - .
In the now uploaded metrics-producer-reusable clone any custom built reusable rule projects from metrics-producer-reusable-old, and update solution.

Formatting of reusable rule results not executing if failures occur in metrics-producer-reusable

Reusable rules added to My Rules from the rule suggestion must have their rule results reformatted after the rule has ran. The solution in the SSDQ client which performs this is correct-reusable-rule-results. This solution does not execute if the metrics-producer-reusable fails. In the scenario where only a subset of reusable rules fail, correct-reusable-rule-results should still run for the successful rules.

Fix

In solution metrics-controller in the SSDQ client, set the ‘Do ignore failures’ option for solution items metrics-producer-custom and metrics-producer-reusable to true.

Advanced Insights does not load for SQL rules

The drill down from data source to record level is not available for SQL based rules in Advanced Insights.

Workaround

None

Intermittent ADQ service start failure

The server can intermittently failed to start reporting the following failure:

Exception: com.datactics.javautils.file.File_util_exception: Failed to delete
'C:\Datactics \adq\tomcat-base\work'
  com.datactics.javautils.file.File_util.delete(File_util.java:593)
  com.datactics.ssdqui.server.container.Apache_tomcat.configure(Apache_tomcat.java:154)
  com.datactics.ssdqui.server.Application.run_(Application.java:407) 
  com.datactics.ssdqui.server.Application.main(Application.java:123) 
Caused by: java.nio.file.DirectoryNotEmptyException: C:\Datactics\adq\tomcat-base\work

Workaround

Restart server until successful.

Data Issues Displaying Incorrect Record Counts

Legacy DQC data set collection groups can cause inaccurate record counts on the Data Issues screen for Data Stewards

Fix

In solution data-for-dsc in the SSDQ DQM client, modify the --sql-select-query configuration field of item data-for-dsc to the following:

select
dsc.id,
dsc.name, 
dsc.comment,
dsc.records_table_name,
dsc.record_columns_metadata,
sum(case when UPPER(ds.comment) NOT LIKE '%ALLOW%LIST%' then 1 else 0 end) as number_of_clusters,
sum(case when UPPER(ds.comment) NOT LIKE '%ALLOW%LIST%' and rc.state = 'R' then 1 else 0 end) as number_resolved,
sum(case when UPPER(ds.comment) NOT LIKE '%ALLOW%LIST%' and rc.state = 'P' then 1 else 0 end) as number_pending,
sum(case when UPPER(ds.comment) NOT LIKE '%ALLOW%LIST%' and rc.state = 'U' then 1 else 0 end) as number_unresolved,
sum(case when UPPER(ds.comment) LIKE '%ALLOW%LIST%' then 1 else 0 end) as number_allowlist
${sql-select-pkd}
from data_set_collections AS dsc 
inner join data_sets AS ds 
on dsc.id = ds.data_set_collection_id
inner join record_clusters AS rc
on rc.data_set_id = ds.id
group by 
dsc.id,
dsc.name,
dsc.comment,
dsc.records_table_name,
dsc.record_columns_metadata
${sql-select-pkd}

Incorrect Rule Input Data Generated

If one rule uses multiple inputs where two or more of these inputs use a SQL builder database data source view, the data for these inputs will be inaccurate as the final SQL builder rule input generated will overwrite the others.

Fix

Replace solution database-data-source-extraction with the below solution XML.

Outlier Detection Profiling Option Always Available

When setting the property outlier-detection-enabled to false in substitution property set ssdq-settings, the outlier detection option is still available when configuring a profile.

Workaround

Disable the outlier-detection-controller solution item in SSDQ solution adq-profile-controller by setting the Activation Mode of the outlier-detection-controller item to Never.

Incorrect setting for metrics-producer-custom

The metrics-producer-custom solution item should not cause further items downstream to fail.

Fix

In solution ssdq-controller, edit solution item metrics-producer-custom to have tick box ‘do-ignore-failures’ enabled.

Driver not specified in rollback-postprocess

The driver property for the SSDQ results database is not specified in the driver configuration field of the items to upload the aggregated results table in solution rollback-postprocess.

Fix

Modified solutions:

rollback-postprocess so the JDBC items have the driver configuration property set to ${ssdq-database.driver-class}, and
rollback-solution to pass in the substitution property ${ssdq-database.driver-class} to solution item call rollback-postprocess.

Data Issues unavailable due to DQC database column data types no longer supported

For DQC instances initially created before December 2021, following an upgrade the Data Issues can fail to load due to the data types of columns comment, record_columns_metadata and auto_assignment_specification in the data_set_collections table, and the comment column in the data_sets table in the DQC database no longer being supported by Microsoft SQL Server.

Workaround

This involves altering tables within the DQC database so it is advised to have a backup of this database before making the fix.

Run the following commands one by one:

ALTER TABLE data_set_collections ALTER COLUMN comment nvarchar (MAX) NOT NULL

ALTER TABLE data_set_collections ALTER COLUMN record_columns_metadata nvarchar(MAX) NOT NULL

ALTER TABLE data_set_collections ALTER COLUMN auto_assignment_specification nvarchar(MAX)

If there are any errors reported, this will be an existing constraint on the data_set_collections table stopping the column’s data type from being changed. If so, run:

ALTER TABLE data_set_collections DROP CONSTRAINT (name of constraint reported in error)

Then repeat for the data_sets table, so run:

ALTER TABLE data_sets ALTER COLUMN comment nvarchar (MAX) NOT NULL

Likewise if an error is reported run:

ALTER TABLE data_sets DROP CONSTRAINT (name of constraint given in error)

Example SQL

ALTER TABLE data_set_collections DROP CONSTRAINT DF__data_set___comme__33D4B598

ALTER TABLE data_set_collections ALTER COLUMN comment nvarchar (MAX) NOT NULL 

ALTER TABLE data_set_collections ALTER COLUMN record_columns_metadata nvarchar(MAX) NOT NULL

ALTER TABLE data_set_collections ALTER COLUMN auto_assignment_specification nvarchar(MAX)

ALTER TABLE data_sets DROP CONSTRAINT DF__data_sets__comme__37A5467C

ALTER TABLE data_sets ALTER COLUMN comment nvarchar (MAX) NOT NULL SQL

Results table data_entry_definition populated incorrectly when column names contains hyphens

If a rule uses columns whose names contain hyphens, then the column names are split into parts and the different parts are used to populate the data_entry_server, data_entry_database, data_entry_schema, data_entry_table and data_entry_column columns in the data_entry_definition table. This also means that the primary key and break field columns are not correctly marked which will result in the break age table being empty.

Fix

To correct the problem in the SSDQ workflow, update the data_entry_definition_dqc_id project to use the attached project zip -

If any incorrect rows are present in data_entry_definition, then the below solutions can be used to correct these. To do this:

Upload the attached solution xmls -
Run the solution correct-data-entry-definition to correct entries in data_entry_definition.
Run the solution update-days-to-consider to populate the break age table.

Rule count files with no records are incorrectly treated as invalid

There is a valid scenario where the rule count file exported from a rule contains only headers, i.e. there were no records in scope for the rule. Therefore, rule count files in the correct format but with only headers should not be flagged as invalid.

Fix

Replace solution rule-count-validation with the one attached and upload new solution rule-count-validation-only-headers.

Test connection solution fails for Oracle data source

When creating a database connection to Oracle, the test connection solution fails meaning the connection cannot be created.

Fix

In the SSDQ client in the DQM, rename existing solution database-source-test-connection to database-source-test-connection-OLD
Upload attached solution XML - .

Cannot create SQL builder views if there are commas within database column headers

When creating a SQL builder view, if any of the columns in the database tables have commas within their names, the solution which retrieves the list of table and column names, and primary and foreign key information fails.

Workaround

Create a SQL query view instead using an alias for any columns containing a comma in the name.

Notices

PingFederate and Kerberos Authentication Behaviour

Scenario Overview

In environments making use of ADQ with PingFederate and Kerberos for authentication, users may encounter a specific behaviour:

Regardless of the username entered, a user can log in to ADQ if their machine user is a member of the appropriate groups. This means that authentication is based on the machine user rather than the individual’s entered username.

Technical Details

PingFederate Token Handling: This behaviour is partly due to how PingFederate processes and honours authentication tokens.
Auth Model Configuration: Our current authentication model requests a login for each session. However, PingFederate’s token handling mechanism bypasses this by validating the machine user’s group membership.

Implications for Users

Group Membership: Ensure that only authorized machine users are members of the relevant groups, as their membership will govern access to ADQ.
Username Entry: The entered username during login will not affect the authentication outcome if the machine user is appropriately grouped.

Recommendations

Review and manage group memberships to maintain secure access controls.
Inform users about this behaviour to set correct expectations during the login process.