Data Virtuality Platform 3.14: a very big and diverse release

This is a big and diverse release (again!), so we'll focus on the new features and the most important improvements and bug fixes in this announcement.

The Server now supports the pass-through authentication mechanism for connecting to Snowflake data sources. We invite you to read the dedicated documentation for detailed information on this feature.

As for improvements, you can now use spaces in column names when creating tables and allow or deny login for users. Also, the internal-backup role is now marked as a build-in role as it should.

We've also carried out a number of updates: the JDBC driver for Snowflake has been updated to version 3.13.29; the JDBC driver for SQL Server, to version 12.4.0.jre11; the JDBC driver for PostgreSQL, to version 42.6.0; the embedded JDK in Data Virtuality Server and Studio, to version 11.0.20+8; the Google Ads API, to v14.1. The latter means some significant changes: the "campaign_criterion_simulation" table has been removed, and the tables "campaign_search_term_insight", "customer_search_term_insight", "product_link", "qualifying_question", "travel_activity_group_view" and "travel_activity_performance_view" have been added. Some columns (e.g. "target_cpm", "ad_group_explorer_auto_optimizer_setting_opt_in") were removed from tables, and some columns were added. The full lists of removed and added objects would be too long for this announcement, so if you need them, please reach out to us.

We've worked a bit on the integration with OData API: now you can use absolute path and case-sensitive WEBSERVICE_PUBLIC_URL in references, and we've got rid of a nasty bug which prevented the OData API from being used in combination with LDAP. 

The job queue functionality also got an improvement and a bug fix: now you can check the thread pool status for each queue handler, and we've resolved an issue with the threadCount parameter sporadically showing incorrect number of active threads in the SYSADMIN.JobQueueStats table.

Speaking of bug fixes, we've also fixed a  bug causing the first command after LEAVE to be ignored; and a 'Batch not found in storage' error on servers with large number of optimizations and materializations. For the Excel connector, we've fixed a bug where the formatStrings translator property was not set to TRUE by default when "importer.allColumnsAsString" model property was.

As for the Studio, we've adapted the Oracle ADWC data source wizard to use the tns_admin connection property.

On the Exporter side, we've fixed a bug which prevented changed passwords for the built-in users from being exported - now all is well.

Speaking of the Connectors, there are two new connectors in the family: Hibob and SendGrid.

For the Amazon Selling Partner connector, we've removed the "expected rate" information from the "Orders via slow API" template, fixed the bug causing the "Quantity Price" columns in Report_ActiveListings to have incorrect data types resulting in errors on procedure call, and fixed another bug resulting in the "has_local_inventory" column being missing from the FBA Fee Preview Report.

For the Microsoft Dynamics 365 Business Central connector, we've added an option to switch between incremental and full replication, made columns for incremental data loading case insensitive, and improved the behaviour in cases when a procedure returns lack of access rights error messages. Here, we've added a new parameter skip_errors (default: false) that changes behaviour by not breaking procedure execution when errors are returned from the server. In this case, the error message will appear only in the log. The same behaviour will be observed when the target_table parameter is filled. We've also fixed a bug causing the items procedure to return only partial data and another bug causing the filter parameter to fail when used with incremental data loading and when the connector was slow while starting.

For the Google Search Ads 360 connector, we've fixed a very nasty bug preventing the AdGroup report from returning data for some metrics. In doing so, we've reordered columns returned by the following report procedures: ReportAccount, ReportAd, ReportAdGroup, ReportAdGroupTarget,  ReportAdvertiser, ReportBidStrategy, ReportCampaign, ReportCampaignTarget, ReportConversion, ReportFeedItem, ReportFloodlightActivity, ReportKeyword, ReportNegativeAdGroupKeyword, ReportNegativeAdGroupTarget, ReportNegativeCampaignKeyword, ReportNegativeCampaignTarget, ReportPaidAndOrganic, ReportProductAdvertised, ReportProductGroup, ReportProductLeadAndCrossSell, ReportProductTarget, ReportVisit. The new order of returned columns has the following structure: segments, attributes, metrics. Segments relating to dates have been removed from requests, and sets of columns returned by procedures.

Our Google Analytics Data connector now supports custom dimensions and metrics, and we've fixed a number of bugs. One of them prevented a data source from being created because of a removed column "audienceId", another caused procedures to not consider startDate or endDate, and yet another resulted in propertyId in procedures being nullable. Now everything works fine.

The Shopify connector now includes best practice templates, the API has been updated to version 2022-10, and we've fixed the bug causing the Orders procedure to only return 250 rows and errors affecting the SmartCollection, pipes__Store_Metafields, OrdersRefunds, pipesOrders, pipes__OrdersRefunds, pipes__InventoryLevels, and pipes__ProductListings procedures.

Here are all issues in this release:

Server

• DVCORE-8074 (New Feature): Snowflake: add pass-through authentication support

• DVCORE-8158 (Improvement): Allow spaces in column names on table creation Please use the allowsSpecialCharsInColumnName translator property to enable the use of spaces in column names

• DVCORE-8150 (Improvement): OData API: use absolute path and case sensitive WEBSERVICE_PUBLIC_URL in references

• DVCORE-8149 (Improvement): Update embedded JDK in Data Virtuality Server and Studio to version 11.0.20+8

• DVCORE-8141 (Improvement): Web service connector: add support for NTLM authentication

• DVCORE-8076 (Improvement): Add a possibility to forbid login for users Procedure SYSADMIN.allowUserLogin was added

• DVCORE-8033 (Improvement): Reduce the log level of the warning informing that no Analytical Storage is connected to the system

• DVCORE-7950 (Improvement): Google Ads API: update to v14.1

• DVCORE-7943 (Improvement): Job queue: introduce a way to check the thread pool status for each queue handler

• DVCORE-7782 (Improvement): Snowflake: update the JDBC driver to version 3.13.29
• DVCORE-7695 (Improvement): SQL Server: update the JDBC driver to version 12.4.0.jre11
• DVCORE-7677 (Improvement): PostgreSQL: update JDBC driver to version 42.6.0
• DVCORE-8189 (Bug Fix): The first command after LEAVE gets ignored

• DVCORE-8188 (Bug Fix): Job queue: "threadCount" sporadically shows incorrect number of active threads in "SYSADMIN.JobQueueStats" table

• DVCORE-8169 (Bug Fix): Job interruption sporadically leads to storing of NULL as an "interruptedBy" value in "SYSLOG.JobLogs"

• DVCORE-8144 (Bug Fix): NetSuite: timestamp-based data filtering causes errors

• DVCORE-8137 (Bug Fix): A 'Batch not found in storage' error on servers with large number of optimizations and materializations

• DVCORE-8100 (Bug Fix): Escape sequence parse error on TEXTTABLE due to wrong escaping order

• DVCORE-8098 (Bug Fix): LDAP Authentication: OData API cannot be used in combination with LDAP

• DVCORE-8046 (Bug Fix): Oracle and NetSuite: CLOB values get truncated when INSERT INTO other data sources

• DVCORE-8030 (Bug Fix): Excel: "formatStrings" translator property is not set to TRUE by default when "importer.allColumnsAsString" model property is

• DVCORE-7890 (Bug Fix): Azure Synapse Analytics: not all the rows are materialized by incremental materialization job

• DVCORE-7767 (Bug Fix): Ordinality column in TEXTTABLE breaks column order

Studio

• DVCORE-8176 (Improvement): Add the possibility to allow or deny login for users

• DVCORE-8096 (Improvement): Adapt the Oracle ADWC data source wizard to use tns_admin connection property

• DVCORE-8180 (Bug Fix): "internal-backup" role is not marked as a built-in role

Exporter

• DVCORE-8178 (Bug Fix): Changed passwords for the built-in users are not exported

Connectors

• SQL-708 (New Feature): Hibob: create connector
• SQL-169 (New Feature): SendGrid connector: create connector

• SQL-789 (Improvement): Amazon Selling Partner: remove "expected rate" information from "Orders via slow API" template

• SQL-787 (Improvement): Google Ads API: ensure compatibility with v14.1
• SQL-785 (Improvement): Amazon Vendor: add rapid retail analytics reports

• SQL-780 (Improvement): Microsoft Dynamics: remove unnecessary logging from internal_Authenticate procedure

• SQL-765 (Improvement): Piwik: add custom data handling
• SQL-761 (Improvement): Google Analytics Data: support custom dimensions and metrics

• SQL-725 (Improvement): Microsoft Dynamics 365 Business Central: add an option to switch between incremental and full replication

• SQL-713 (Improvement): Matomo: add the getUniqueVisitors report
• SQL-689 (Improvement): Shopify: create best practice templates

• SQL-638 (Improvement): Microsoft Dynamics 365 Business Central: some procedures return lack of access rights error messages

• SQL-623 (Improvement): Microsoft Dynamics 365 Business Central: make columns for incremental data loading case insensitive

• SQL-411 (Improvement): Shopify: update to API version 2022-10
• SQL-250 (Improvement): Pardot connector: improve authentication failure messages

• SQL-791 (Bug Fix): Amazon Selling Partner: "Quantity Price" columns in Report_ActiveListings have incorrect data types resulting in errors on procedure call

• SQL-790 (Bug Fix): Google Ads API: Performance_AdGroupAd procedure fails with INVALID_ARGUMENT error

• SQL-776 (Bug Fix): Shopify: the SmartCollection pipes procedure throws error

• SQL-772 (Bug Fix): Google Analytics Data: data source cannot be created because of a removed column "audienceId"

• SQL-769 (Bug Fix): Shopify: Orders procedure only returns 250 rows

• SQL-763 (Bug Fix): Amazon Selling Partner: "has_local_inventory" column is missing from the FBA Fee Preview Report

• SQL-762 (Bug Fix): Google Analytics Data: procedures do not consider startDate or endDate

• SQL-751 (Bug Fix): Google Analytics Data: propertyId in procedures is nullable

• SQL-697 (Bug Fix): Linkedin: internal_OAuth_step1 and internal_OAuth_step3 procedures are missing

• SQL-675 (Bug Fix): Microsoft Dynamics 365 Business Central: Items procedure returns only partial data

• SQL-652 (Bug Fix): Microsoft Dynamics 365 Business Central: Filter parameter fails when used with incremental data loading and connector is slow while starting

• SQL-516 (Bug Fix): Shopify: the pipes__Store_Metafields procedure throws an 'Invalid BigDecimal format in String' error

• SQL-422 (Bug Fix): Shopify: OrdersRefunds pipes procedure is redundant and pipesOrders filters only open default order statuses

• SQL-365 (Bug Fix): Shopify: pipes__OrdersRefunds, pipes__InventoryLevels, and pipes__ProductListings fail

• SQL-325 (Bug Fix): Google Search Ads 360 connector: AdGroup report does not return data for some metrics