Forum Discussion
Gateway Refresh Redshift - DM_GWPipeline_Gateway_MashupDataAccessError
Connections/refreshes for a read-only user via Power BI Desktop have been working for months. Now, trying to set up the scheduled refreshes, I run into the error below (in screen shot). Each time a manual or scheduled refresh occurs, it fails on a different table in the data source. We are connecting to a AWS Redshift instance through a Gateway. This is what has been done so far:
- Cleared the caches
- Reset all permissions in Desktop and re-entered credentials
- Confirmed that Privacy Level is set the same (Encrypted, Organizational) on the data source via the Service Data Source settings and via Desktop
- Not only does it work in Desktop to build and refresh these Redshift tables, but I can take the same user and do select queries via other tooling as well
If this was truly a permissions issue, I would expect to see it everywhere. Are there some other Gateway settings or configuration that might be missing?
TIA,
-A
After some trial and error, we ended up creating a separate ODBC connection to the Aurora instance that held some tables we accessed through an external schema within our Redshift instance. I did testing with only the Redshift tables with no issues refreshing through the gateway. Once I introduced even one table from the external Aurora schema, the refreshes started to fail. Unsure of what the actual root cause is, but the workaround is solid.
3 Replies
I can't explain why this was working but now isn't but I have just revolved an issue generating the same underlying error code as the one you are seeing. In my case a query was merging on-premise SQL DB and Sharepoint online. Raised a ticket with Microsoft support and the only resolution tha tworked was to copy the SQL data through the gateway using a PBI Dataflow and then consume the Dataflow from the Power BI report.
Interesting. I'm not merging anything. Doing an import of less than 10 tables/views from Redshift, and doing a little bit of transformation on them (removing some columns, changing case, etc). The only thing that is a little different is that there is 1 view and 1 table that are aliases inside that instance that come from Aurora. However, when the refreshes fail, they also randomly fail on tables that are coming directly from Redshift. I might create a simple report and import only 1 Redshift table and give that a try just to rule that out.
After some trial and error, we ended up creating a separate ODBC connection to the Aurora instance that held some tables we accessed through an external schema within our Redshift instance. I did testing with only the Redshift tables with no issues refreshing through the gateway. Once I introduced even one table from the external Aurora schema, the refreshes started to fail. Unsure of what the actual root cause is, but the workaround is solid.