Data Virtuality Platform 4.4: Security capabilities, UDF, Data quality functions and more

For the Server part this release our top priority is to address security requests. We added two new security functions, AES_ENCRYPT and AES_DECRYPT, and made the encryption key configurable: it can be set through the dv.encryptionKeyPath system property. Please note that as a result of this improvement, the “encryptedProperties” and “encryptedTranslatorProperties” parameters have been introduced to the SYSADMIN.testConnection procedure. For further details on this, please contact our support team. 

This release also introduces support for Data Quality functions contributed by the OSDQ Project.

We fixed several bugs affecting the Apache Parquet connector. The first caused int96 timestamps and int32 dates to be handled as integer numbers — now they are correctly treated as timestamp and date, respectively. Note that you should re-run replications and materializations of the tables that use these data types. The second was preventing temporary files from being removed, and another one caused getting data from a Parquet file containing different data types via FTP to result in an error.

The next important announcement is that we introduced the Data Retention feature — all requests and responses sent from web service data sources can be stored in the logs for further historical activities, analysis or troubleshooting.

We also made it possible to use cache hints for virtual views and stored procedures. More about cache hints read here.

Cloud Agent also received some attention — we enabled uploading and downloading configuration files via web socket connections. 

We've also added a new optional UUID parameter to several system procedures: SYSADMIN.enableSchedule, SYSADMIN.enableSchedulerJob, SYSADMIN.enableOptimization, SYSADMIN.deleteOptimization, SYSADMIN.editBackupJob, SYSADMIN.changeSQLJob, SYSADMIN.changeCopyOverSQLJob, and SYSADMIN.changeCopyOverSourceTableJob. At the same time, the SYSADMIN.dropIndex procedure now has an additional optional parameter recOptUuid. These changes were needed to further improve our Git integration functionalities.

It's worth mentioning that our LDAP Authentication now allows configuration without setting an OU in roleCtxDN, and we've updated the embedded JDK in Data Virtuality Server and Studio to version 11.0.22+7.

We also fixed a number of bugs for this release. These include a bug preventing permissions from being applied to a view if the view is used in a BEGIN END block, a bug causing Java user-defined functions to fail after server restart, a bug causing the Error Handler to return an unclear error message when a new database connection cannot be obtained, and a bug causing dependency handling to fail for views and procedures with the same name if the base data source is recreated. 

Additionally we addressed a bug affecting our OData API where a view with nullable columns that contain NULL could not be accessed and a bug with Git integration when adding or editing schedules for system jobs was not tracked. All works correctly now. 

The Studio has two improvements: now it supports masking OAuth2 credentials in the Generic Web Service data source wizard, and we've adapted the User management tab according to the role mapping functionality.

As for the connectors, we've worked extensively on the Amazon Selling Partner connector where we've added new columns to the FBA Manage Inventory Health Report, DAY reporting to Vendor_Report_Inventory report, support for the parameter sellingProgram for procedures Vendor_Report_Forecasting, Vendor_Report_Inventory, Vendor_Report_Sales, automated rotation of client secret, improved error messaging and reporting workflow, deleted deprecated reports, and fixed a bug causing the requesttime column to be missing from CatalogItems child tables. Please note that this means that replications using CatalogItems procedure or pipe using a target_table parameter must be restarted from scratch into the new set of tables.

For our TikTok connector, we've added new columns to reports from API v1.3, removed clientId from connection properties, and updated the api domain.

We've also fixed two bugs affecting our Amazon Ads connector: one causing Brands_Report_Keywords_SegementQuery to return HTTP 406 error on endpoint and another preventing Sponsored Products AdGroups Report v.3 from returning data, and two bugs with the Microsoft Dynamics BC connector: one where procedures with non-ASCII chars in its name failed retrieving data, and another where special characters in procedure names caused parsing errors when creating a data source. 

As for other connectors, our Exactag connector now supports all available metrics and dimensions, SharePoint supports NTLM authentication, Exact Online supports LAccountClassificationMappings and ReportingBalanceByClassification methods.

And last but not least, we improved startup time for Microsoft Dynamics. It received 4 new parameters:

- OnlyEndpointsCSV - contains a CSV list of endpoints to support;
- OnlyEndpointsFile - contains a file with a list of endpoints to support, where each line contains 1 endpoint;
- ExcludeEndpointsCSV - when no OnlyEndpointsCSV or OnlyEndpointsFile is filled, contains a CSV list of endpoints to exclude;
- ExcludeEndpointsFile - when no OnlyEndpointsCSV or OnlyEndpointsFile is filled, it contains a list of endpoints to exclude.

Here are all issues in this release:

Server

• DVCORE-8351 (New Feature): Add support for Data Quality functions
• DVCORE-7718 (New Feature): Implement a Data Retention feature for web service data sources

• DVCORE-8331 (Improvement): LDAP Authentication: allow configuration without setting an OU in roleCtxDN

• DVCORE-8327 (Improvement): Update embedded JDK in Data Virtuality Server and Studio to version 11.0.22+7

• DVCORE-8322 (Improvement): Add support for AES encryption and decryption SQL security functions

• DVCORE-8321 (Improvement): Make the encryption key configurable

• DVCORE-8284 (Improvement): Improve performance of user context switching in procedures

• DVCORE-8278 (Improvement): Cloud Agent: allow uploading and downloading configuration files via web socket connections

• DVCORE-8230 (Improvement): Add UUID parameter to "SYSADMIN.enableSchedule" and "SYSADMIN.enableSchedulerJob" procedures

• DVCORE-8229 (Improvement): Add UUID parameter to the "SYSADMIN.enableOptimization" procedure

• DVCORE-8228 (Improvement): Add "recOptUuid" parameter to the "SYSADMIN.dropIndex" procedure

• DVCORE-8227 (Improvement): Add UUID parameter to the "SYSADMIN.deleteOptimization" procedure

• DVCORE-8226 (Improvement):Add UUID parameter to the "SYSADMIN.editBackupJob" procedure

• DVCORE-8225 (Improvement): Add UUID parameter to the "SYSADMIN.changeSQLJob" procedure

• DVCORE-8224 (Improvement): Add a UUID parameter to the "SYSADMIN.changeCopyOverSQLJob" and "SYSADMIN.changeCopyOverSourceTableJob" procedures

• DVCORE-8080 (Improvement): Cache hints for views and procedures

• DVCORE-8035 (Improvement): Switch from using PicketBox SecurityVault to Elytron Credential Stores

• DVCORE-7871 (Improvement): Azure Table: improve pushdown and data type determination

• DVCORE-7488 (Improvement): Improve performance of internal metadata and dependency graph operations

• DVCORE-6923 (Improvement): PostgreSQL: add mapping of CITEXT data type to CLOB

• DVCORE-8350 (Bug Fix): Apache Parquet: int96 timestamps and int32 dates are handled as integer numbers

• DVCORE-8345 (Bug Fix): Apache Parquet: temporary files are not removed

• DVCORE-8343 (Bug Fix): Permissions are not applied to a view if the view is used in a BEGIN END block

• DVCORE-8335 (Bug Fix): Computing errors when writing to XML files

• DVCORE-8328 (Bug Fix): Data Virtuality OData API: view with nullable columns that contain NULL cannot be accessed

• DVCORE-8326 (Bug Fix): Apache Parquet: getting data from a Parquet file containing different data types via FTP results in an error

• DVCORE-8324 (Bug Fix): After data source creation failure another data source with the same name cannot be created

• DVCORE-8316 (Bug Fix): Exasol: replication jobs to Exasol fail when the datasource is configured with useFullSchemaName=TRUE

• DVCORE-8287 (Bug Fix): XMLCONCAT function throws an 'Already removed' error when executed concurrently

• DVCORE-8239 (Bug Fix): SELECT INTO fails when storing the data lineage plan into the configuration database is enabled

• DVCORE-8207 (Bug Fix): Git integration: adding or editing schedules for system jobs is not tracked

• DVCORE-8193 (Bug Fix): Snowflake: DAYNAME function returns incorrect results

• DVCORE-8053 (Bug Fix): Dependency handling fails for views and procedures with the same name if the base data soucre is recreated

• DVCORE-7884 (Bug Fix): Java user defined functions fail after server restart

• DVCORE-7752 (Bug Fix): History and Upsert update jobs fail for Exasol with useFullSchemaName=TRUE

• DVCORE-7649 (Bug Fix): Incremental materialization, history update and upsert replications fail if key columns are LOBs and "comparableLobs" is set to TRUE

• DVCORE-7633 (Bug Fix): SAP connector: reading a BLOB column in a loop exhausts connections to SAP if the number of iterations is greater than max-pool-size value

• DVCORE-7465 (Bug Fix): Error Handler: an unclear error message is returned when a new database connection cannot be obtained

• DVCORE-6988 (Bug Fix): INSERT INTO Oracle and Redshift from PostgreSQL fails when source table contains a TIMETZ column

• DVCORE-6261 (Bug Fix): Dependency graph does not show parent view or procedure if there is another object with the same name in the query

Studio

• DVCORE-8339 (Improvement): Mask OAuth2 credentials in the "Generic Web Service" data source wizard

• DVCORE-8292 (Improvement): Adapt the "User management" tab according to the role mapping functionality

Connectors

• SQL-835 (Improvement): TikTok: add new columns to reports from API v1.3
• SQL-833 (Improvement): Exactag: add all available metrics and dimensions to connector

• SQL-832 (Improvement): Microsoft Dynamics: improve connector startup time

• SQL-830 (Improvement): TikTok: remove clientId from connection properties

• SQL-826 (Improvement): Amazon Selling Partner: add new columns to the FBA Manage Inventory Health Report

• SQL-804 (Improvement): Amazon Selling Partner: add DAY reporting to Vendor_Report_Inventory report

• SQL-797 (Improvement): Amazon Selling Partner: support parameter sellingProgram for procedures Vendor_Report_Forecasting, Vendor_Report_Inventory, Vendor_Report_Sales

• SQL-795 (Improvement): Amazon Selling Partner: automate rotation of client secret

• SQL-792 (Improvement): Amazon Selling Partner: improve error messaging, improve reporting workflow, delete deprecated reports

• SQL-771 (Improvement): SharePoint: add support of NTLM authentication
• SQL-714 (Improvement): TikTok: update api domain

• SQL-240 (Improvement): Exact Online: add support of GLAccountClassificationMappings and ReportingBalanceByClassification methods

• SQL-859 (Bug Fix): Microsoft Dynamics BC: procedure freezes on loading data

• SQL-858 (Bug Fix): Microsoft Dynamics BC: procedure with non-ASCII chars in its name fails retrieving data

• SQL-849 (Bug Fix): Amazon Ads: Brands_Report_Keywords_SegementQuery returns http 406 error on endpoint

• SQL-845 (Bug Fix): Amazon Selling Partner: requesttime column is missing from CatalogItems child tables
  Replications, using CatalogItems procedure or pipe, using a target_table parameter, must be restarted from scratch into the new set of tables. The reason is due to the new column added to the child tables of this procedure, which will break the insert.

• SQL-840 (Bug Fix): Microsoft Dynamics BC: special characters in procedure names cause parsing errors when creating a data source

• SQL-836 (Bug Fix): Amazon Ads: Sponsored Products AdGroups Report v.3 does not return data

• SQL-770 (Bug Fix): Hibob: null columns in reports