October Power BI release of the Connector for Dremio causing queries to fail with "String value resulted in invalid numeric." – Dremio Support

Summary

This article describes a problem that has occurred with the October release of the PBI driver for Dremio.

Reported Issue

We have heard through our customers, that after an automated update of the PBI driver for Dremio, queries started to fail with the message "String value resulted in invalid numeric."

Relevant Versions

All versions of Dremio and the October 2024 MS release of the Dremio connector.

Troubleshooting Steps

Checking the PBI error for details, you see this error message:

Data source error: DataSource.Error: ODBC: ERROR [22018] [Microsoft][Support] (40321) String value '' resulted in an invalid numeric.. DataSourceKind = DremioCloud. DataSourcePath = Dremio Cloud: ;enterprisedremio-odbc.cdt.maersk-digital.net:443. OdbcErrors = #table({"SQLState", "NativeError", "Message"}, {}). . The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action. Table: my_table.

Cause

Microsoft released an updated PBI driver for Service (Cloud) around Sunday the 13th of October 2024 and for Desktop, automated updates started around Sunday 20th of October 2024. The driver is owned and deployed by Microsoft.

For the most up to date information on this issue, please use this link and MS reference PB911592 Please note, you have to be a PBI admin, to see the updates.

Steps to Resolve

The latest we have through interactions with our customers and Microsoft:

We have been informed that a PBI Service fix has been developed for the US regions but are awaiting confirmation regarding the EMEA region and it's ETA.
There is a workaround by installing PBI Gateway, which can bypass this problem as you can choose the version to install (pre October 2024) where there is no automation to upgrade the driver.

PBI Gateway documentation:
- - https://docs.dremio.com/cloud/sonar/client-apps/microsoft-power-bi/working-from-datasets/administering-connectivity/#enabling-users-of-power-bi-service-to-connect-to-dremio-cloud-via-power-bi-gateway
  - You would have to install the gateway similar to on-prem (perhaps on a Cloud VM), and as we understand the connector is shipped per:
    
    https://docs.dremio.com/cloud/sonar/client-apps/microsoft-power-bi/working-from-datasets/troubleshooting-connectivity/#power-bi-gateway-installation
  - Be aware that you would need to install the September (or earlier) version of the PBI Gateway.
- Our local tests show:
  - If we create a report in Service or Desktop, with the October release, the reports will fail.
  - If we take a Desktop report that was created with the September (or earlier) driver, and export this to PBI Service, this runs.
  - If we take a Desktop report that was created with the September (or earlier) driver, update the connection, and then publish, this also fails.

Next Steps

Dremio continues to work with Microsoft in getting a fixed driver published as soon as possible. Please continue to follow up with Microsoft and seek their confirmation on the driver update and if the above options would work for your configuration.

Additional Resources