Data Virtuality Platform 3.15: 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 a dedicated connector for Microsoft Dataverse as a preview feature - your feedback will be most welcome! We've also updated the embedded JDK in Data Virtuality Server and Studio to version 11.0.21+9.

Thanks to another improvement, the Server now also allows multiple underscores in column names, and the Data Virtuality JDBC driver now has an option to remove double quotes around the catalog name while loading metadata.

Speaking of bug fixes, most importantly, we've resolved the problem causing jobs to sporadically get an incorrect value in "running" column of "SYSADMIN.ScheduleJobs" system table. Now everything is working properly, and we have proper logging for the ScheduleJobs.running field (runningCountCache). We've also fixed two bugs afffecting the job queue functionality: one causing a job to get stuck in QUEUED state forever after deletion of the schedule triggering it, and another where 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. Another bug we've fixed concerned the SYSADMIN.writeConnProp procedure which did not overwrite exising properties - now it works properly, too.

We've also fixed a bug affecting Azure Synapse Analytics where strings containing line feed or carriage return could not be written via BLOB upload mode and a bug with Clickhouse where date and time functions were not pushed down and CONVERT was not supported and causing issues with casting and usage of DISTINCT in queries. Now all is well.

As for the Studio, we've fixed a 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-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-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-8208 (Bug Fix): SYSADMIN.writeConnProp procedure does not overwrite exising properties

• 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-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-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-8259 (Bug Fix): Scheduled jobs are not visible when "Status" column is filtered

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