Using Direct Query mode causes full data pull and a subsequent timeout

[Anonymous]

We have a PostgreSQL database on AWS RDS with over 500M rows on which we have built a Power BI report.  We are unable to use the report in Import mode because of the volume of data involved.  The report contains one text filter and two slicers.  It was originally developed with a reduced data set with the tables imported in "Import" mode and it worked fine.  However, when we switched over to Direct Query, all visuals fail to render with the error: "OLE DB or ODBC error: [DataSource.Error] PostgreSQL: Exception while reading from stream".  In addition to these tables that have been imported in Direct Query mode, there are five static tables that have been entered into the pbix file for use in another report as a result of which, the pbix file has mixed mode storage.  

When the report is published to app.powerbi.com and launched, it tries to pull the entire dataset and we see the same error across all visuals.  "The visual has exceeded the available resources" and the details hyperlink shows "The XML for Analysis request timed out before it was completed. Timeout value: 225 sec."  It appears that the report is issuing a null query to the database and ending up fetching the entire data set.  Is there a way to stop this behavior and restrict the rows being fetched so that the report loads blank by default and issues a query to the database only when a value is entered into the text filter?

Thank you for your time.

[v-yingjl]

Hi @Anonymous ,

I'm afraid not. The error in power bi desktop is that the query times out. Refer: PostgreSQL.Database 

In power bi service, it returns the error, because your visual has attempted to query too much data to complete the result with the available resources. You'd better try filtering the visual to reduce the amount of data in the result. 

About direct query mode, you can refer this document: About using DirectQuery in Power BI 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yingjl ,

Thank you for your response.  Our use case is that we enter a value into the search bar and it displays data corresponding to that entity in the report.  However, if we restrict the data set at the query level, all the required data does not come into the report when we enter text in the search bar.  Is there a way to parameterize the input from the search bar so that the report does not load anything by default and only loads content after querying the database for that single row?  

Regards,

Janak

[edhans]

No. When you load the report in your browser, it loads, by default, what you tell it to, and if your slicers/filters are blank, it will load everything. 

Consider setting a filter or slicer to be very restrictive when you save the report and publish to the server. Then when you load, it will only load a small subset of data until you change the filter.

[Anonymous]

Hi @edhans ,

Thank you for your response.  We had originally published the report with a single row being published.  The problem is that when we clear the filter to look for another record, the report tries to load the full data set before trying to apply the filter.  As a result, we run into the issue I had originally described.

Regards,

Janak

[edhans · Accepted solution]

Change the defaults in Power Query desktop to only redo the data when the user presses APPLY.

[Anonymous]

Thank you @edhans.  I had tried this option and it is crashing my report for some reason as soon as I enable it.  I updated to the June release of Power BI Desktop as well but when I did that, I was unable to open my pbix file and this seems to be a known issue with the June release.  Is there a way I can parameterize user input and use it to restrict the queries via the advanced editor?

[edhans]

I'm not aware of this being an issue with the June release, and would seem to be a critical issue. I just set up a Direct Query and set the apply button option and it works fine. If you use anything in the advanced query when you set it up, you will break query folding and disable Direct Query altogether, forcing it to Import Mode.

[Anonymous]

Thank you for your response @edhans.  Our report has a couple of columns that have been removed from the table and that change shows up in the Advanced Editor - I'm not sure if this would break query folding. 

I'm sorry I didn't explain myself clearly when I mentioned the June release.  Initially, I was still using the May release of Power BI Desktop and I tried setting the options to display the "Apply" button on the filters.  As soon as I did that, my application would throw an error and close.  So I tried updating Power BI Desktop to the June version.  After the upgrade, I was not able to open the report file at all so I uninstalled the June release and installed the May release once again.  When I mentioned the known issue, I was referring to this thread that I came across when I was trying to research what was possibly going wrong https://community.powerbi.com/t5/Issues/Can-t-open-Power-BI-after-June-2020-update-quot-We-weren-t-a...

Regards,

Janak

[edhans]

Ok @Anonymous - helpful. I'm reading through the 6+ pages. Bad that many affected. Seems to be Windows 7 though, both 64 bit and 32 bit. I've not seen a WIndows 10 post yet.

That said, the solution I provided will work once ou get your desktop install fixed. The issue is not with the solution I provided. Could you consider marking it as the solution so others can find it. And for you I recommend you file a support ticket with MS so they can get it fixed.

FWIW a new Desktop was released last thurs/fri in the Windows 10 store. I am sure the EXE that you can download was also updated. Check it out.