v1.4.0 - ADQ Release Notes

ADQ v1.4 introduces a comprehensive set of enhancements aimed at improving security, functionality, performance, and usability. These updates encompass a wide range of improvements ensuring a smoother and more efficient user experience overall.

New Features

Security Enhancements and Dependency Updates

Addressed security vulnerabilities by upgrading Apache Tomcat dependency.
Added new permission for DQC user interface access.
Improved data issues access control by enforcing users to select a default data issues assignee from a predefined dropdown when building a DQ rule. This ensures only users who have access to the data sources the DQ check is running on can see the records for remediation in Data Issues. The dropdown also allows issues to be assigned to a user group where ADQ ensures this group has permission to view the data being assessed. Given this, the checkbox allowing users to choose whether issues should be automatically assigned by the SSDQ workflow has been removed.
Added permission to allow or disallow users to enable the Python profiling option when configuring a rule suggestion profile. This means only the column headers of the data being profiled are used by the rule suggestion microservice and no data is extracted for the profile or for review.
When resolving data issues, records can be assigned to user groups or unassigned. The option to assign data issues to all users has been removed to ensure only users with the correct data source access permissions can resolve their breaks.

Database Support

DQM underlying database now supports PostgreSQL instances.

Functionality Enhancements

Enabled bulk-assign functionality where users can assign data issues in bulk to themselves or mark the issue as unassigned.
Added additional filters to My Rules library for filtering by rule ID and rule name.
Easier access and simpler navigation to resolving data issues by adding a direct link from the offending record in Advanced Insights to the same record in Data Issues.
When reviewing DQ rules suggested by the ML rule suggestion profiling, all rules are by default disabled so only rules you wish to add to your library require a simple click.
Predictive insights timeline has been simplified to look similar to the standard record timeline. The timeline now has an entry for the first day a break happens/is predicted to happen and another one when the break has been remediated/is predicted to be remediated.
Users no longer need to specify a rule permission group; ADQ ensures proper access control.
Allow no default tab to be specified when creating an insights view.

UX Improvements

Several UX improvements were made to grids and statuses which includes a larger header text, hover row styling and colour coded status pills.

Performance Improvements

Improved performance of Predictive Insights timeline.
Enhanced data issues loading performance.

Notifications and Messages

Assist user in locating input data extractions by extending the successful notification message shown after input data extraction launch to instruct the user to navigate to the Input data extractions layout to locate the extracted input data.
Advanced Insights now informs the user if there is no data in the underlying database for analysis.
Improved error handling when a user attempts to create a data source view which already exists. If the user attempts to create a duplicate view now, a dialogue is displayed explaining the issue.

Data Source Management

Users must now specify unique names for all data sources across all of the different data source types to avoid confusion when building rules on their data.
Fixed the issue where it was not possible to create data source views on CSV or Excel files if there were no database data source connections setup.

Corrections

Resolved the issue where filtering on the Data Issues grid stopped working after reloading the data by navigating to a different section and back again.
Addressed the issue where users were forced to run outlier detection as part of a profiling job. The outlier detection profiling is now optional.
Modified workflow test for reusable rules to address invalid results.

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}

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.

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.

Cannot create database connection if column names contain commas

When creating a database connection, the solution which tests the connection before successful creation fails if there are commas within any of the database table column headers.

Fix

Contact Datactics to receive updated packages for DQM toolsets and ADQ server:

data-quality-manager-toolsets-2024.02.22.1.zip
adq-server-2024.02.23.201-linux.zip or adq-server-2024.02.23.201-windows.zip

If columns containing commas in the name must be extracted from the database source then a SQL query view must be created for the extraction and an alias must be used where the alias:

Can only contain letters, digits, underscores and hyphens,
Must start with a letter or underscore,
Must be no more than 256 characters long, and
Must not start with “___dqc___”.

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.