Postgresql database and relational tables - refreshing locally works but refreshing on gateway fails

[TimothyJames]

In a nut shell my company's power BI has a table (Original Table being queried) that has relational columns that are foreign keyed to a different table. What happens locally is that you can "Expand" the columns in power query and then pull in the columns of those related tables.

We are not always expanding those tables, so the query at the end of refresh still has "TABLE" on each row. Currently - we're looking at just removing the columns that are problematic right now, but long term that's not very useful.

That refreshes with no issues locally and on deployment it uses the same DB user to connect.

However - when it refreshes it fails with something like 

{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"The column 'SCHEMA.RELATED_TABLE' of the table wasn't found."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Detail","detail":{"type":1,"value":"SCHEMA.RELATED_TABLE"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"Expression.Error"}}],"exceptionCulprit":1}}} Table: ORIGINAL_TABLE_BEING_QUERIED

I cannot figure out why it differs between locally and gateway.

[TimothyJames]

Hey Ibendin, thanks for the follow up. Do you have a recommendation as to where I would be good to read up on the mashup engine?

I was under the impression that this specific table was exclusively using the desktop (locally) and gateway (for auto refresh) 

Also - if the specificity of the table is irrelevant, please let me know as that would also help me learn a bit more. Appreciate it! Thank you!

[lbendlin · Accepted solution]

How Power Query Thinks: Taking the Mystery Out of Streaming and Query Folding (Video) | Ben Gribaudo

[TimothyJames]

Thank you! I'll take a look at it!

[lbendlin]

There are four different mashup engines at work

- desktop

- gateway

- dataflows gen1

- dataflows gen2

- Power BI Service

(ok, ok, five)

There is no guarantee that these mashup engines work the same way (and there are known differences for example when it comes to encryoted connections)

Fetching related tables is generally not a great idea.  Better to fetch each table separately, avoid the cost of expansion, and join them in the Power BI data model if needed.