Data Virtuality Platform 4.2: a very big and diverse release again

This is yet another very big release, so this announcement will again focus on the most important new features, improvements, and bug fixes.

The Server now has dedicated connectors for Microsoft Dataverse and Kafka (both, for now, are preview features - your feedback will be most welcome!) and supports Databricks as a data source. We've also updated the embedded JDK in Data Virtuality Server and Studio to version 11.0.21+9.

In this release, we've worked extensively on our LDAP authentication support: we've improved the performance of LDAP making the first login faster and fixed several bugs with LDAP Authentication: one preventing non-admin users from creating temporary tables by default, another preventing members of the default admin group from changing jobs owned by other users, and another resulting in user with a Common Name other than displayName being unable to run any job as CALLER. For the resolution of the latter bug, we've updated the handling of the "displayUserName" LDAP configuration parameter used as "userName" in Data Virtuality. Previously, if "displayUserName" was not specified in "dvserver-standalone.xml" file, the system defaulted to using the user's "displayName" LDAP attribute value as "displayUserName". If the "displayName" was not available, the "distinguishedName" LDAP attribute value was used instead, as every LDAP user has a distinguishedName. Similarly, if displayUserName was specified but some LDAP users lacked the used LDAP attribute, the system defaulted to the distinguishedName LDAP attribute value. Now, the "distinguishedName" LDAP attribute is no longer used in cases where the user does not have a "displayName", or the attribute specified in "displayUserName" is missing. Also, an error will occur when reading Data Virtuality users if "displayUserName" is not specified in the config, or if it is set to an attribute that some users do not have (e.g, "cn"). Therefore, it is crucial to specify "displayUserName" accurately. If your DV LDAP config has such users, this information is for you:

• Please extent the DV LDAP config setting "searchFilterUsers": The "searchFilterUsers" shall contain an expression that checks for the LDAP attribute, that is used in DV LDAP configuration as "displayName", to be not empty;
• The generic syntax is "(&amp;(first condition)(<LDAP attribute configured as "displayUserName">=*))";
• If you have "searchFilterUsers" and "displayUserName" not explicitly configured (=default settings are active), please add this option: <module-option name="searchFilterUsers" value="(&amp;(objectClass=user)(displayName=*))"/>;
• If you have "searchFilterUsers" and/or "displayUserName" explicitly configured (=non default settings), please adapt accordingly.

Our OAuth implementation also received attention in this release: we've added the functionality to load and store SSO users and roles and the ability to use Data Virtuality roles for SSO users.

As for OData, we've fixed two bugs one of which was causing queries with COUNT and LIMIT to fail and another resulted in LIMIT not being pushed down. Now all is well.

We've also worked on our Git integration: fixed a bug causing the SYSADMIN.gitExclude procedure to not work after SYSADMIN.gitInit call and another bug causing the SYSADMIN.purgeObjects procedure to fail on some objects.

We've fixed a bug where SYSADMIN.setRemark broke procedure definition in case owner and runAs are not set clearly - now it sets procedure definition correctly and a bug causing jobs to sporadically get an incorrect value in "running" column of "SYSADMIN.ScheduleJobs" system table because there was no logging for runningCountCache. Now runningCountCache is being logged as it should. Another bug we've resolved affected Azure Synapse Analytics where strings containing line feed or carriage return could not be written via BLOB upload mode.

We've also fixed a bug where some UTILS procedures must run with execute as OWNER - now users can run any of them as long as they have the permission to run the procedure, and a nasty bug where creating several ONCE schedules for the same job lead to NullPointerException in the server log and schedules with NULL as the next fire time value. Now all ONCE schedules get correctly deleted after the job executions. Another bug we've fixed caused jobs to get stuck in the QUEUED state forever after deletion of the schedule triggering it - now such jobs get the correct state.

As for the Studio, the login dialog now supports the "useDvRoles" SSO property, and the useDvRoles checkbox is present in the OAuth/OpenID parameters. We've fixed a bug resulting in the semicolon missing in the "jdbc-properties" value passed to SYSADMIN.createConnection call for Data Virtuality Server data source and another bug preventing scheduled jobs from being visible when "Status" column is filtered.

We've also updated the Exporter to properly handle SSO users and roles after the cache introduction.

Our Connectors list now include a new Workday connector, and the Hubspot connector has been refactored for better performance and got some important improvements (it now supports the "Social Media Broadcasts", "Contacts", and "Get submissions for a form" reports). As for the existing connectors, our team worked particularly extensively on Amazon Selling Partner, Amazon Ads, and Google Analytics 4.

For the Amazon Selling Partner connector, most importantly, we've added six new reports: Report_FBAStorageFees (with the GET_FBA_STORAGE_FEE_CHARGES_DATA report), Report_FlatFileReturnsbyReturnDate, Report_RequestedorScheduledFlatFileOrderShipping, Report_RequestedorScheduledFlatFileOrderTax, Report_ScheduledOrderShipping, and Report_ScheduledOrderTax. We've also added the "location" parameter to the "Inventory Ledger Report - Summary View" report. The newly added parameters are aggregateByLocation, aggregateByTimePeriod, FNSKU, MSKU, ASIN. And we've fixed a bug causing Report_FBAManageInventoryHealth" to return duplicates - for this, we removed the "days_step" and "days_redownload" parameters and added "maintain_history" instead. Also, the logic of the report was corrected, because the report always returns data for the latest available "snapshot_date".

The Amazon Ads connector, most importantly, now includes best practices templates and also has six new reports working with v.3: Report_Products_Targeting, Report_Products_SearchTerms, Report_Products_PurchasedProducts, Report_Products_Campaigns, Report_Products_AdvertisedProducts, and Report_Brands_PurchasedProducts. We've also added support for API v.3 and v.4,  aligned the connector procedures with the recent changes in the API by removing the "adGroupId" parameter from the procedure ProductAds (use "adGroupIdFilter" instead). We've also improved the connector procedures to update data in BigQuery target tables with less number of SQL queries and changed max download date for V2 consolidated reports from 50 days to 59 days. We've also fixed a bug which caused subtables created by some reports to sometimes contain inconsistent partial data for one of profileId-s when procedure loops over several of them and another bug causing the timestamp and profile id columns to be missing in the child tables of some procedures. To resolve this issue, we've added the columns "profile_id" and "collected_at" to all subtables saved along the main table set in parameter target_table for 13 procedures: v3_Brands_Ads, v3_Brands_AdsBeta, v3_Brands_Campaigns, v3_Brands_CampaignsBeta, v3_Brands_Creatives, v3_Display_OptimizationRules, v3_Products_AdGroups, v3_Products_Campaigns, v3_Products_Keywords, v3_Products_NegativeKeywords, v3_Products_NegativeTargetingClauses, v3_Products_ProductAds, and v3_Products_TargetingClauses. Another nasty bug we've fixed caused the v3_Products_CampaignNegativeKeywords and v3_Products_CampaignNegativeTargetingClauses procedures to fail with "Unexpected multi-valued result" error. Now all works well.

As for Google Analytics 4, we've improved the connector to switch to reading columns dynamically and allow specifying a target virtual schema name on procedure creation via "CreateProcedure_ReplicateData" call.

Here are all issues in this release:

Server

• DVCORE-8267 (New Feature): Microsoft Dataverse: create a dedicated connector for the Microsoft Dataverse JDBC (preview)

• DVCORE-7769 (New Feature): ksqlDB: create a ksqlDB connector for Kafka (preview feature)
• DVCORE-7402 (New Feature): Databricks: add support for Databricks as a data source

• DVCORE-8258 (Improvement): Allow multiple underscores in column names

• DVCORE-8216 (Improvement): Data Virtuality JDBC driver: add an option to remove double quotes around the catalog name while loading metadata

• DVCORE-8202 (Improvement): Update embedded JDK in Data Virtuality Server and Studio to version 11.0.21+9

• DVCORE-8175 (Improvement): OAuth2 Authorization: add functionality to load and store SSO users and roles

• DVCORE-8146 (Improvement): Support AES for internal symmetric encryption

• DVCORE-8084 (Improvement): OAuth 2 Authorization: add ability to use Data Virtuality roles for SSO users

• DVCORE-8018 (Improvement): OData: improve procedure handling

• DVCORE-8293 (Bug Fix): DuckDB: data source creation fails with SQLFeatureNotSupportedException error as DuckDB JDBC driver does not implement "getTypeInfo" method

• DVCORE-8291 (Bug Fix): Incorrect permission is applied in case procedure is called in an expression assigning the result of this procedure to a variable

• DVCORE-8273 (Bug Fix): Some UTILS procedures must run with execute as OWNER

• DVCORE-8272 (Bug Fix): LDAP Authentication: non-admin users cannot create temporary tables by default

• DVCORE-8271 (Bug Fix): LDAP: first login is slow due to unnecessary updates of permissions table in the config database

• DVCORE-8265 (Bug Fix): Procedures with IN parameters fail if EXECUTE AS is set to OWNER and owner is not a current user

• DVCORE-8262 (Bug Fix): Temporary table creation fails if done via EXECUTE IMMEDIATE

• DVCORE-8261 (Bug Fix): LDAP Authentication: user with a Common Name other than displayName cannot run any job as CALLER The "has" and "stripDomain" functions are introduced. They can now be utilized within the "displayUserName" value.

• DVCORE-8260 (Bug Fix): LDAP Authentication: members of the default admin group cannot change jobs owned by other users

• DVCORE-8254 (Bug Fix): Azure Synapse Analytics: strings containing line feed or carriage return cannot be written via BLOB upload mode

• DVCORE-8252 (Bug Fix): "Performance metrics collection task" runs are not cleaned up from internal config database tables

• DVCORE-8250 (Bug Fix): Job sporadically gets an incorrect value in "running" column of "SYSADMIN.ScheduleJobs" system table

• DVCORE-8249 (Bug Fix): SYSADMIN.setRemark corrupts procedure lacking "OWNER" or "EXECUTE AS" value specified explicitly in the definition

• DVCORE-8241 (Bug Fix): Git integration: SYSADMIN.gitExclude procedure does not work after SYSADMIN.gitInit call

• DVCORE-8240 (Bug Fix): Git integration: SYSADMIN.purgeObjects procedure fails on some objects and does not have a flag to ignore purge in Git

• DVCORE-8222 (Bug Fix): MySQL: replication of binary data into MySQL results in an OutOfMemoryError when BULKINSERTMULTITHREAD mode is used

• DVCORE-8221 (Bug Fix): The SYSADMIN.changeJobParameters procedure fails if the "runAs" parameter is used but the value is not changed

• DVCORE-8220 (Bug Fix): It is possible to set owner to an empty value for procedures, jobs, and schedules

• DVCORE-8219 (Bug Fix): Unclear error message when trying to change the owner of a procedure by a user without admin rights

• DVCORE-8217 (Bug Fix): Permission error message does not contain the name of the accessing object which caused it

• DVCORE-8208 (Bug Fix): SYSADMIN.writeConnProp procedure does not overwrite exising properties

• DVCORE-8203 (Bug Fix): Second import of a view with a remark set via SYSADMIN.setRemark procedure breaks the view definition

• DVCORE-8190 (Bug Fix): ClickHouse: date and time functions are not pushed down, CONVERT is not supported and causing issues with casting and usage of DISTINCT in queries

• DVCORE-8185 (Bug Fix): MAINTENANCE mode: states of recommended optimizations are updated on the server start if MAINTENANCE mode is enabled

• DVCORE-8184 (Bug Fix): MAINTENANCE mode: virtual views and procedures get recreated on the server start when MAINTENANCE mode is enabled

• DVCORE-8174 (Bug Fix): Job queue: creating several ONCE schedules for the same job leads to NullPointerException in the server log and schedules with NULL as the next fire time value

• DVCORE-8171 (Bug Fix): Job queue: job gets stuck in QUEUED state forever after deletion of the schedule triggering it

• DVCORE-8055 (Bug Fix): OData: queries with COUNT and LIMIT fail

• DVCORE-8021 (Bug Fix): OData: LIMIT is not pushed down

• DVCORE-7990 (Bug Fix): Snowflake: pushdown is not enabled for ORDER BY NULLS FIRST and LAST

• DVCORE-7838 (Bug Fix): Dependent job starts as many times as the base job ID is used in the chain string

Studio

• DVCORE-8269 (New Feature): Add "useDvRoles" SSO property support to the login dialog

• DVCORE-8263 (Bug Fix): Semicolon is missed in the "jdbc-properties" value passed to SYSADMIN.createConnection call for Data Virtuality Server data source

• DVCORE-8259 (Bug Fix): Scheduled jobs are not visible when "Status" column is filtered

Exporter

• DVCORE-8270 (New Feature): Update the exporter to properly handle SSO users and roles after the cache introduction

Connectors

• SQL-690 (New Feature): Workday: create a connector

• SQL-810 (Improvement): Amazon Vendor: add an option to choose data for non-daily granularity in "pipes__Vendor_Report_NetPureProductMargin" report

• SQL-809 (Improvement): Walmart Marketplace: add "Payment Statement Report" and "Performance Report"

• SQL-801 (Improvement): Amazon Selling Partner: add "location" parameter to the "Inventory Ledger Report - Summary View" report

• SQL-799 (Improvement): Microsoft Dynamics 365 Business Central: add handling for non-unique property names

• SQL-786 (Improvement): Amazon Selling Partner: add "Marketplace ASIN page view metrics" report

• SQL-774 (Improvement): Google Analytics 4: switch to reading columns dynamically

• SQL-773 (Improvement): Google Analytics 4: allow specifying a target virtual schema name on procedure creation via "CreateProcedure_ReplicateData" call

• SQL-760 (Improvement): Amazon Ads: add "groupBy" and "campaignPlacement" parameters to the "Report_Products_Campaigns"

• SQL-755 (Improvement): Amazon Selling Partner: change the report parsing encoding to UTF-8

• SQL-749 (Improvement): Amazon Ads: add "attributedUnitsOrdered1dSameSKU" metric to the "Consolidated_Report_ProductAds" procedure

• SQL-739 (Improvement): Microsoft Dynamics: reduce the number of created subtables and keep entities of the same type in one table

• SQL-718 (Improvement): Microsoft Excel Online: support additional parameters to get drives and files from "sites"

• SQL-717 (Improvement): Walmart Marketplace: add a procedure for the MultiNodeInventoryForAllSkuAndAllShipNodes operation

• SQL-682 (Improvement): Amazon Ads: add "Budget Usage Report" to Pipes
• SQL-659 (Improvement): Amazon Ads: add support for API v.3 and v.4

• SQL-650 (Improvement): Amazon Selling Partner: fix and improve the connector procedures to fully support new API versions matching the "2022-xx-xx" pattern

• SQL-634 (Improvement): Amazon Selling Partner: create additional reports

• SQL-630 (Improvement): Amazon Ads: improve connector procedures to update data in BigQuery target tables with less number of SQL queries

• SQL-628 (Improvement): Amazon Ads: set 'all' as a default value of the "creative_type" filter in "Brands_Report"

• SQL-624 (Improvement): Amazon Ads: add "campaignBudget" metric to the "Display_Report"

• SQL-613 (Improvement): Klaviyo: update the connector to the latest stable API version

• SQL-610 (Improvement): Amazon Ads: change max download date for V2 consolidated reports from 50 days to 59 days

• SQL-596 (Improvement): HubSpot: add procedures for LineItems, Associations and Products

• SQL-590 (Improvement): Amazon Ads: add best practice templates

• SQL-583 (Improvement): Amazon Ads: add support for sponsored "Products" and "Brands"reports of v.3

• SQL-561 (Improvement): Amazon Selling Partner: add "GET_FBA_STORAGE_FEE_CHARGES_DATA" report

• SQL-550 (Improvement): HubSpot: add "Social Media Broadcasts" and "Contacts" reports

• SQL-540 (Improvement): HubSpot: add "Get submissions for a form" report

• SQL-509 (Improvement): HubSpot: refactor connector

• SQL-499 (Improvement): Amazon Advertising: add "Brand Metrics" and "Attribution" reports

• SQL-492 (Improvement): Amazon Ads: align the data types used in the connector procedures to improve consistency

• SQL-430 (Improvement): Amazon Ads: align the connector procedures with the recent changes in the API

• SQL-814 (Bug Fix): Walmart: Inventory procedure throws an input parameter API error

• SQL-811 (Bug Fix): Amazon Ads: subtables created by some reports may contain inconsistent partial data for one of profileId-s when procedure loops over several of them

• SQL-807 (Bug Fix): Matomo: "Live_LastVisitsDetails" procedure fails with "Element is not defined by any relevant group" error

• SQL-800 (Bug Fix): Amazon Selling Partner: slow "Orders" job always writes data to child tables

• SQL-796 (Bug Fix): Amazon Selling Partner: "Report_FBALongTermStorageFeeCharges" report does not write all data to the target table

• SQL-793 (Bug Fix): Amazon Ads: timestamp and profile id columns were missed in the child tables of some procedures

• SQL-781 (Bug Fix): HubSpot: "pipes_Contacts" procedure is missing and "Contacts" procedure with "target_table" value passed executes forever

• SQL-779 (Bug Fix): Amazon Vendor: there is no option to choose data for non-daily granularity in "Vendor_Report_NetPureProductMargin" report

• SQL-777 (Bug Fix): Amazon Selling Partner: "Report_FBAManageInventoryHealth" report returns duplicates

• SQL-746 (Bug Fix): Amazon Vendor: "installExamples" procedure should generate all views without throwing an error

• SQL-745 (Bug Fix): Amazon Ads: "v3_Products_CampaignNegativeKeywords" and "v3_Products_CampaignNegativeTargetingClauses" procedures fail with "Unexpected multi-valued result" error

• SQL-722 (Bug Fix): HubSpot: running “Hubspot.CreateProcedure_Meetings” procedure results in an error

• SQL-720 (Bug Fix): Facebook: page and post insights procedures are not auto generated

• SQL-693 (Bug Fix): Microsoft Excel Online: the connector fails to install if the path to a file is not defined

• SQL-684 (Bug Fix): Microsoft Dynamics: dynamically created procedures fail with specific values of expand parameters

• SQL-677 (Bug Fix): Amazon Selling Partner: some reports fail with an error when "target_table" parameter is not filled

• SQL-599 (Bug Fix): Google Ads API: no data is returned by "Performance_AdGroupAd"report due to keyword metrics usage

• SQL-567 (Bug Fix): Facebook: "get_campaigns_and_creatives_by_account_id" report has an incorrect page size value

• SQL-381 (Bug Fix): Google Search Ads: reports with date range parameters fail with errors when date range is more than 365 days