Any best practice for using direct query connection?

[dmkblesser]

The company has decided to use direct query to connect to the cloud data source for its tracebility. We are aware of the 1 million rows limitation and the potential slowness when querying data from the source. Additionally, the table view tab will no longer be available in Power BI and you lose the ability to easily look up tables and data.

Can anyone share their experience regarding the impact of solely using direct query on reports, from report building to accessing reports?

For report developers, will it be more difficult to write measures due to the limitations? Are there any limitations in the query editor when it comes to transforming the data?

How do you handle large data when you encounter the limitation issue? I am aware that the admin can change the settings, but if we leave the settings unchanged, what is the best approach to deal with large data?

Any suggestions or shared experiences would be greatly appreciated!

[dmkblesser]

Thanks @aj1973 for your response. I want to understand the 1 million rows limiation a bit more. 

I have a table with more than 70 million rows. The table has  a column named 'price' and another column named 'date'.

When I wrote a measure basically just sum(price) and I'm able to put it in a card visual and display it without any issues. In this case, the underlying data is already exceeding 1 million rows, why does this measure work? 

On another attempt. I put the `date` and `price` in a table and select `do not summarize` for the `price`, so in this case, my data again is exceeding 1 million rows, but the table show up okay. 

Now, when I add the measure to the table, I'm getting the limitation error message. Just would like to understand why it happens at this time instead of the previous two attempts. 

Thank you

[aj1973]

Here's what is happening:

However, the limit can occur in cases where Power BI doesn't fully optimize the queries sent, and requests some intermediate result that exceeds the limit.

The limit can also occur while building a visual, on the path to a more reasonable final state. For example, including Customer and TotalSalesQuantity could hit this limit if there are more than 1 million customers, until you apply some filter. The error that returns is: The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows.

I got it from here

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about

[dmkblesser]

Hi @aj1973, I have read the documents in the past. In the example you quoted, is TotalSalesQuantity a measure? 

In my previous response,  "On another attempt. I put the `date` and `price` in a table and select `do not summarize` for the `price`, so in this case, my data again is exceeding 1 million rows, but the table show up okay." 

Isn't this result contradictory to the example from the documentation? 

[aj1973]

@dmkblesser 

A limit of 1 million rows can return on any query. There's a fixed limit of 1 million rows that can return in any single query to the underlying source. This limit generally has no practical implications, and visuals won't display that many points. However, the limit can occur in cases where Power BI doesn't fully optimize the queries sent, and requests some intermediate result that exceeds the limit.

Meaning if you have optimized queries then the implications are null