Forum Discussion
Inner Join with Direct Query without pulling all data to client
Hi,
I'm using Direct Query to Oracle db
If I just add 1 table, PowerBi does not load all the data to client for the reports to work (applies filters and aggregations on report refresh).
However if I have 2 tables (A, B), joined via a unique key - K(a), K(b), and define a new merged query (inner join), then PowerBI tries to pre-load all the data to client (which does not work, millions of records).
The tables A, B container a lot of data and the reason for using Direct Query is performance. However PowerBi joins (merge query) seems to defeat the purpose of using Direct Query.
Am I missing something?
Note that I know I can handcraft my own join and work around the issue, I'm wondering whether there is a built-in way for joins to evaluate at report time (with filters and aggregations applied), rather then pre-fetch all the data?
Regards,
Stefan
2 Replies
Anonymous , have you used +0 in measure or crossjoin.
Have you used bi-directional joins?
refer to this
https://guyinacube.com/2019/01/30/power-bi-query-reduction-when-using-directquery/
https://community.powerbi.com/t5/Desktop/Query-Reduction/td-p/964484
Hi amitchandak ,
thank you for responding.
I'm not using any meassures or crossjoins. And DAX joins seem to only work on equivalent structure tables (not my case)
bi-directional joins seem to have no effect on merge query creation, likewise reducing query. They all seem to only have influence after the data is filtered.
Note that I can't even create the merged query because PowerBi seems to be overloaded with data.
