Direct Query with all joins 'Assume Referential Integrity' ticked is triggering multiple SQL queries

[millwardt]

I have a data model connecting to an Oracle Database with Direct Query.

I'm not sure why, but depending on the combination of tables selected, despite all the relationships being 'Assume Referential Integrity' ticked (which should issue an inner join), the query is not being sent to the Oracle database as a single query.  It appears to be broken into multiple parts.

Unfortunately, as the filters in the query are accross multiple tables, this results in an error of 'The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows.'

If I write the SQL manually it returns just 344 rows.

Is there any way to trace what is causing PowerBI to split this into multiple SQL statements?

[v-xiaoyan-msft]

Hi @millwardt ,

Open the Power Query Editor then for each query, right click on the last applied step in the list:

If the 'View Native Query' option is greyed out, right click on the steps above until it's visible.

This will show you the SQL sent to the underlying data source.

You could capture the SQL queries being passed from Power BI to your Oracle database using your DBMS (e.g. Toad for Oracle).

For original post ,you may refer 

Solved: How to gete power bi report sql (oracle database) ... - Microsoft Power BI Community

Best Regards,
Community Support Team _ Caitlyn

[millwardt]

Hi Caitlyn

Thank you for coming back to me.

Within the Power Query Editor, each of the Queries is selecting an entire Oracle database table, generating queries which are essentially SELECT all column names FROM table name.

Within the model, all queries are then joined with an Assume Referential Integrity setting ticked, which I believe should lead to INNER JOIN.  Unfortunately it seems to be breaking the query into multiple parts which it then joins back together in PowerBI.  Due to how it is splitting these unfortunately each part is returning a large number of rows as the filter criteria is split accross the tables.  If instead it issued a single query with the filter criteria applied it would retrieve just 344 rows, and very quickly from the Oracle database.