how to resolve this error while getting data from databricks from power bi.Not able to load table in power bi

Question

how to resolve this error while getting data from databricks from power bi.Not able to load table in power bi

Pratibha Khare 30

DataSource.Error: ODBC: ERROR [HY000] [Microsoft][DSI] (20039) Cannot store ""."".""."REMARKS" value in temporary table without truncation. (Column metadata implied a maximum of 512 bytes, while provided value is 559 bytes

Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2023-10-24T16:22:17.82+00:00

Hello Pratibha Khare,

I am checking to see if you had a chance to look into the above answer.
Sadiq Rahimansa 10 Reputation points

2023-12-21T07:54:25.25+00:00

This is because the comments in the Databricks table metadata in contains text more than 512 characters, which is quite normal. Power BI Enhanced metadata reader internally refers the "comments" columns as "REMARKS" and it seems to be have a size limitation of 512. If possible, if you could reduce the size of the comments, that will resolve it. For me, I can't do that because we use it for our data catalog and I am stuck now. Waiting for response from MicroSoft.
Fabiënne 10 Reputation points

2024-01-18T09:02:11.5166667+00:00

Hi Sadiq, having exactly the same issue here. Did you get an answer from Microsoft?
Fabiënne 10 Reputation points

2024-01-18T09:38:12.49+00:00

Thanks for your very quick reply! I also run into this security issue.
Sadiq Rahimansa 10 Reputation points

2024-01-18T10:29:55.8433333+00:00

Please let me know if you manage to test it.
Fabiënne 10 Reputation points

2024-01-29T09:28:28.9233333+00:00

Hi Sadiq, this solution worked out for me. I got some help from Microsoft with this workaround. We copied another ini file and changed the name to a microsoft.sparkodbc.ini file under the C:\Program Files (x86)\Microsoft Power BI Desktop\bin\ODBC Drivers\Simba Spark ODBC Driver. By means of Notebook (++), ran as administrator, we were able to change the content of the file to: [Driver]
MaxCommentLen=2048 In case of issues, the file needs to be deleted. The official route still is to make the comment shorter in Databricks.
Sadiq Rahimansa 10 Reputation points

2024-01-31T09:20:46.2233333+00:00

Thanks for the update Fabiënne. We also managed to test it yesterday and it worked. We will go ahead and roll out the changes, but I am cautious on what could be the performance impact. Would PBI reserve more space in the memory and impact the dashboard data import size and performance? Watching for that. Will report back if I see any.

5 answers

Your answer

Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2023-10-24T16:22:17.82+00:00

Hello Pratibha Khare,

I am checking to see if you had a chance to look into the above answer.
Sadiq Rahimansa 10 Reputation points

2023-12-21T07:54:25.25+00:00

This is because the comments in the Databricks table metadata in contains text more than 512 characters, which is quite normal. Power BI Enhanced metadata reader internally refers the "comments" columns as "REMARKS" and it seems to be have a size limitation of 512. If possible, if you could reduce the size of the comments, that will resolve it. For me, I can't do that because we use it for our data catalog and I am stuck now. Waiting for response from MicroSoft.
Fabiënne 10 Reputation points

2024-01-18T09:02:11.5166667+00:00

Hi Sadiq, having exactly the same issue here. Did you get an answer from Microsoft?
Fabiënne 10 Reputation points

2024-01-18T09:38:12.49+00:00

Thanks for your very quick reply! I also run into this security issue.
Sadiq Rahimansa 10 Reputation points

2024-01-18T10:29:55.8433333+00:00

Please let me know if you manage to test it.
Fabiënne 10 Reputation points

2024-01-29T09:28:28.9233333+00:00

Hi Sadiq, this solution worked out for me. I got some help from Microsoft with this workaround. We copied another ini file and changed the name to a microsoft.sparkodbc.ini file under the C:\Program Files (x86)\Microsoft Power BI Desktop\bin\ODBC Drivers\Simba Spark ODBC Driver. By means of Notebook (++), ran as administrator, we were able to change the content of the file to: [Driver]
MaxCommentLen=2048 In case of issues, the file needs to be deleted. The official route still is to make the comment shorter in Databricks.
Sadiq Rahimansa 10 Reputation points

2024-01-31T09:20:46.2233333+00:00

Thanks for the update Fabiënne. We also managed to test it yesterday and it worked. We will go ahead and roll out the changes, but I am cautious on what could be the performance impact. Would PBI reserve more space in the memory and impact the dashboard data import size and performance? Watching for that. Will report back if I see any.

Answer 1

Sachith Gunawardena 5

use native query in power BI to get data in to the power BI - problem get solved

Answer 2

Hello Pratibha Khare,

Welcome to the Microsoft Q&A forum.

from the error message, the column “REMARKS” has a maximum length of 512 bytes, but the provided value is 559 bytes

Can you please review your "REMARKS" column in the Databricks? If there are any values that exceed 512 bytes,

you may need to truncate or otherwise modify these values before attempting to load the data into Power BI.

Use Power Query: If you’re using Power BI Desktop, you can use Power Query to connect to your Databricks data. In Power Query, you can transform it before loading it into Power BI

I hope this helps.

Please let me know if you have any further questions

Answer 3

Yes. Response from Msft below:

The idea here is to modify the parameter MaxCommentLen to accommodate the required size you need for the column. This can just be done through the Power BI Desktop, since we ‘rent able to modify ODBC this parameter on PBI Service. We can achieve it following the next steps: 1. Create/edit a file inside your PowerBI Desktop installation folder, located at … Microsoft Power BI Desktop\bin\ODBC Drivers\Simba Spark ODBC Driver**microsoft.sparkodbc.ini1. In the file you can add/modify the below config for the value:[Driver]**MaxCommentLen=2048 1. Save and restart PBI Desktop.

However, we found that this folder is quite restricted and no one has permissions to modify it. We are working with our security team to gain access. So, haven't tested it yet.

Answer 4

Mike 0

Updating the ini file didn't work for us. We ended up adding the property to the registry entry. While this resolved the "Remarks" error we were getting, the metadata ("Remarks") isn't populating in Power BI ("Description").

Answer 5

Consulta_Nativa

I was able to work around this problem by drawing inspiration from suggestions I found here and on the Databricks website. I used the native query feature in Power BI to list only the fields that did not have the issue of extensive comments, and it worked. I identified the problematic columns by using the DESCRIBE database.table function in Databricks, and fortunately, the fields were ones that I didn't need

https://kb.databricks.com/en_US/unity-catalog/error-when-trying-to-load-a-dataset-after-integrating-unity-catalog-metadata-with-power-bi

Share via

how to resolve this error while getting data from databricks from power bi.Not able to load table in power bi

5 answers

Your answer