Forum Discussion
Postgres DirectQuery "Requires Data Gateway"
Yes Anonymous - if you have data that requires the gateway, like the CSV files, then it can make the issue more complex.
Can you get it working if you remove everything but the Postgre data? Just whack all of that other stuff and save as a new file. Then add data sources back until you hit the one causing the issue. You didn't say where your CSVs were. A gateway is needed for local/network files or SharePoint On Prem. Should not be needed for SharePoint online, Data Lake, and other online resources.
But mixing and matching can still cause issues that are frustrating to trouble shoot. That is why I suggest do 1 data source, then add until it breaks and fix the breaks one at a time.
Given the error you have, something in your report thinks it needs a gateway to access the "data source file"
Thank you for the thorough answer and clarification edhans!
I got the native query working last night in desktop, enabled the query_folding flag, and it returns exactly what I want.
I checked the native query step and it doesn't appear anything like yours does; it's just a single in-line SQL statement.
However, checking in on the webapp this morning I still see the same gateway error.
What's even more confusing is that a coworker created a small status-view report of some aggregated database values with DirectQuery and his just worked. And he's touched Power BI a total of 2 days. This is why I'd rather not go the data gateway route yet (also because it's another level of hassle).
A difference between my report from his is that I have some csv fact tables related to the direct queries. Would Mixed-Storage mode/Composite-Modeling be the problem?
Yes Anonymous - if you have data that requires the gateway, like the CSV files, then it can make the issue more complex.
Can you get it working if you remove everything but the Postgre data? Just whack all of that other stuff and save as a new file. Then add data sources back until you hit the one causing the issue. You didn't say where your CSVs were. A gateway is needed for local/network files or SharePoint On Prem. Should not be needed for SharePoint online, Data Lake, and other online resources.
But mixing and matching can still cause issues that are frustrating to trouble shoot. That is why I suggest do 1 data source, then add until it breaks and fix the breaks one at a time.
Given the error you have, something in your report thinks it needs a gateway to access the "data source file"
edhans The CSV's are stored in OneDrive, but I set them to import mode (I imported them as CSV's, not as a OneDrive source). So that's definitely the issue then.
I stripped everything back and it's now working! (I'm sad that I had to remove so much though 😞 )
Fortunately it all lives in the server, so I can get it by DQ alone. Are calculated tables allowed as well, though? I had one, but noticed its storage mode was set to import.
Thank you so much for your help!
You can have calculated tables in Direct Query. You can even have import tables in DQ. But those will only refresh on the scheduled refresh, not live like the DQ queries will. That would be composite mode.
You can get your OneDrive CSVs as cloud by connecting using SharePoint Folder connector. Get to your OneDrive via the O365 portal page. You'll get a URL like:
https://tenant-my.sharepoint.com/personal/user_name_tenantname_com/_layouts/15/onedrive.aspxJust use the
https://tenant-my.sharepoint.com/personal/user_name_tenantname_com/part of the URL.