Left outer join with Direct Query

[madhaviml]

Hello,

Is there any way to Use outer joins either Left outer or right outer join with the Direct Query.

I can't use Import as its HUGE dataset.

thanks in advance

[ToddChitt]

If you have data that huge, I think Direct Query is NOT the answer. The answer is to try to figure out WHY the SQL query takes so long and throws an error? How complex is the SQL query? How many tables joined together? I have seen queries of up to 30 or 40 million rows in Power BI without issue. Yes, it take 20 minutes to refresh, but was only needed once per day, plus we implemented Incremental Refresh, an advanced feature.

Instead of one big query, what if you brought in each table seperately, AND ONLY the fields you want. AND only a subset of the ROWS you want (start small, like a month)

Best practice with SQL sources in Power BI is to filter the ROWS first, then reduce the column. Right click on the last step of the Applied Steps and select "View native query". That is the actual statement that Power BI will issue to the SQL Server! (It's called "query folding" and does not work with sources like Excel or CSV files.) If you cannot see that option on the last step, then look at previous steps. At some point along those Applied Steps, Power Query decided that it can't push that logic down to the server, it cannot write that as a T-SQL statement. So EVERY step from then on has to be accomplished by the CLIENT (read: your workstation) which is probably not as powerful as the server. 

Next, check if you can cover the query with an Index in SQL Server on the tables in question. That could be a BIG win.

[foodd]

@ToddChitt , awesome use case and solution narrative.

[madhaviml]

yes, this works. but I need to give the ability to users to 'type in' their own dynamic filter value or pass date parameter. So I can't  restrict data using filters in the sql query. because of the huge data, the sql query doesn't run , gives me spool errors.

[ToddChitt]

As in: selecting "Direct Query" as the mode and writing your own SQL query statement in the Advanced box, with a LEFT OUTER JOIN in it?

Question back to you: Have actually TRIED it? Did it work? If not, show us the error message.