SQL DirectQuery: JOIN in SQL or PowerBI?

[Anonymous]

Hi there,

Short Version: When using SQL Server in DirectQuery, is it more efficient to perform dataset JOINS in the SQL query in the data source for the metrics that will be reported on together (JOINING by SiteID and Timestamp) or to pull the metric tables in individually and then join them with relationships inside of PowerBI?

Longer Version: I'm designing a PowerBI report to be embedded in a customer-facing application, I know three metrics will always be reported on together. I originally pulled each of the fact tables in separately in PowerBI and then used a relationship on Timestamp and SiteID to have them visualized together. However, after some thought, I considered the option of joining the three metrics in a single SQL query as a data source and then having that as one PowerBI DirectQuery table. Is there a PowerBI best practice/rule of thumb around which method makes more sense and why?

Thanks,

Steve

[GilbertQ]

Hi there

My question would be around why using DirectQuery?

I have found that very often performance will be slower when using DirectQuery, and to get it running fast, especially with a lot of users will become an expensive exercise because the SQL DB will need to be optimized quite a lot.

If you could import the data into Power BI and let it refresh every hour, the reports will run super fast.

Also I would suggest not creating a relationship on a timestamp, because every timestamp is unique, rather look at separating the date and time.

[Anonymous]

@GilbertQI am using DirectQuery because real-time data reporting is a requirement, every five minutes. That's why I have to use date and time to match on as well.

[GilbertQ · Accepted solution]

Thanks for letting me know, if that is the case then your longer method should work best where you can join all the data at the source.

I would also limit the amount of data returned. This will ensure that your SQL system could not be overloaded with too many queries running at once (Especially if there are multiple people running reports with multiple visuals, because a single visual can create multiple queries)