Direct Query limitations on single row.

[dp106]

Hi,

I am using power BI service and report is connected to Oracle DB in direct query mode. On-Premise gateway is installed on my local desktop as test.

When i access the report i get the below error message. I am not able to track the row that causes this error as the message seems to be appering very random(sometime it works fine).

amount of single row data on the gateway client has reached the maximum Mb size for a single row

I have reduced the number of columns to the bare minimum needed but still getting the same error.

Has anyone faced similar issues.

Thanks.

[pciavarella]

Same issue here - direct query against Oracle database results in 3.5 million rows by 19 columns, including columns with long hyperlinks.  This worked fine until this month (August) but now  can't display the result.  Same error message, "The amount of a single row data on the gateway client has reached the maximum MB size for a single row."

[pciavarella]

Same issue here - direct query against Oracle database results in 3.5 million rows by 19 columns, including columns with long hyperlinks.  This worked fine until this month (August) but now  can't display the result.  Same error message, "The amount of a single row data on the gateway client has reached the maximum MB size for a single row."

[joostraaphorst]

Hello,

I've the same issue.

I calculated the maximum size of 1 row and that doesn't exceed the 600 bytes...

Is this issue appeared since an update in Power BI services?

Regards,

Joost

[dp106]

Hi,

I identifed records having maximum bytes (top 30) and then tried to fetch all those records in power bi (service) and it seems to working fine. It only errors when i try to fetch a larger dataset.

[v-piga-msft]

Hi @dp106,

If you have solved your problem, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

Best Regards,

Cherry

[joostraaphorst]

Hi Cherry,

when can we expect a solution for this issue.

We are are getting a lot of complaints about our reports at the moment. It was working correct for more then 2 years. Since 2 weeks we are getting this message and are not able to see the data. We don't want change the report for now. We want that this issue will be solved in the next release of Power BI.

Regards,

Joost.

[dp106]

No it’s not resolved yet. We don’t have a solution to the issue.

[SeanB]

Going on over a week for us now and the support continues to struggle answering this issue and at this point I feel pretty helpless as they are clearly not knowledgable enough to troubleshooting it.

Initally they told me that a single row exceeds the 4MB limitation, at which I provided the stats from our db that this clearly is not the case. I also provided them trace routes for both the sql query and powerbi desktop. Now the latest statement from support is that :

Observations and Limitation:

It is identified that the max size limit is 4 MB for a Row however, alongside we also need to consider the overall size of the incoming data which should NOT exceed > 16 MB.

As per the stats shared by you below:

• 9,729 max bytes per row if every field is fully used ( ~ 0.009729 MB)
• 52,000 number of rows 
• 505,908,000 bytes is the total visual size  (~505.908 MB)

In Conclusion

• It is identified that the cap limit set is 16 MB and the report MB forecasted by you is ~505.908 MB which is greater than the threshold.
• I had a conversation with our PG for the latest update and understood that there has been a change on the limitation where the truncated data wasn’t throwing an error previously.
• This was not noticeable because, the data consumption happened by selecting a slicer which would have been always within limit of 4MB or 16 MB.
• In our latest bug which was raised for similar issue it is identified that, while extracting the data from SQL server using Direct Query we are seeing the error which you specified (Which was not the case earlier).

So essentially what it seems they are now telling me is that my report cannot be greater than 16MB. Which from my research is not true (and would be pathetic if it was). I do believe that they are confused with the limitations that exporting a report to excel would have (https://docs.microsoft.com/en-us/power-bi/power-bi-visualization-export-data#limitations-and-conside...), but this is not the issue I am having at the moment.

Microsoft states that "Direct Query can have over 1GB datasets". This documentation can be found here: https://docs.microsoft.com/en-us/power-bi/desktop-use-directquery

The annoying part of all of this is that I will now have to wait another 24 hrs until the come back with yet another useless response.

[jtavolaro]

I also ran into the limitations issue recently.  Using direct query, with an SQL statement selecting only the needed columns.  But I added a calculated field to the result, generating a URL, and this apparantly threw the record size off dramatically.  It's confusing because the same formula is used for multiple reports, just this one had an issue.

[v-piga-msft]

Hi @dp106,

It seems that this error will occur if you have a single row greater than 4 MB in size. You will need to determine what the row is from your data source and attempt to filter it out or reduce the size for that row.

Best  Regards,

Cherry

[dp106]

I know the cause of the error, but unable to track the row that is causing the error. It does work fine i query Subset of the dataset.

Do we have any kind of logging to trace the the record that is causing the error ?

[v-piga-msft]

Hi @dp106,

---

 Do we have any kind of logging to trace the the record that is causing the error ?

---

You need to find the column which contains large size in your Oracle DataBase and reduce it.

Best Regards,

Cherry

[Rajamani]

Hi,

I had the same issue. Try using optional sql statement in the getdata window which will fetches only required columns instead of entire table. This resolved my issue. hope this helps.

[dp106]

Thanks for your repose.
I am already using optional sql statement to fetch only the columns I need.

[SeanB]

We also have been experiencing the same issue for the past 3 days. Been trying to work with support but they have been a trainwreck. Initally they didn't respond to my request for assistance for for several hours when first reported on Tuesday morning. They then set up skype meeting with my dev team and their dev team for Wed morning at 10am - to which they were a no show. They then contacted me 3hrs later stating they were sorry for missing the meeting but this issue was due to the current disruptions they were experiencing and these issues should be resolved by 8pm EST on 8/1. Fast forward to today, issue still exists and just got disconnected from a support call I was on hold for nearly 2 hrs.

We had not made any changes to our reports prior to this issue. Issue is not intermittent, occurs everytime.

[dp106]

Thanks for sharing your experience.

Please update if you find any solution, should help us all.

[Anonymous]

Does your data contain binary type columns (i.e. files held in the data)?

[dp106]

No binary fields.. all data types are varchar2. The error seems to be very random.

[Anonymous]

Same issue here, happend all of a sudden....

Did you already find a solution? 

[dp106]

No solution yet. Though the error message is specific to row limit. I get this error only when I fetch more rows. If I limit it to few records then no issues. I am not sure if we can track the record that is causing this issue.