Forum Discussion
Confused about joins
Hi Anonymous ,
You're thinking too much in the moment - think end-product:
Once you've done your T1/T2 merge, you right-click on the T2 query and UNCHECK 'Enable Load'. This prevents this query having to be run when you actually refresh your report in the service, as it doesn't need to be materialised for the data model. This just leaves your merged T1 query to be run which, as you can see, has all the details it needs within its folded native query to produce your merged T1 in the model.
Pete
Anonymous - Unfortunately, there's really no "one size fits all" approach.
I agree, that 'best practice' would suggest to let Power BI handle the query/query folding as it knows the most efficient way to do this. This goes against everything I believe, I come from a SQL background also.
I like the option that BA_Pete mentioned about forcing the query folding, however, I tested this just now, for a scenario I'm working with, and it seems that option errors as soon as your native query has joins to other tables.
Check out this post on stack overflow, specifically, the comments from smpa01 about the Table Variable. It may be of use. (I think it is the same smpa01 that is here in the PBI community)
My advice for what it's worth...
- Do everything as close to the source as you can.
- ALWAYS use import if your query is acceptable in terms of performance, the issues you can face with Direct Query in both PQ and DAX are numerous and annoying.
- If you have to use Direct Query, use aggregation tables where possible and test they are being hit (easy to do in DAX Studio).
The typical pattern I follow for my queries, if experience tells me that 'best practice' would be slow, is to keep it to as few steps as possible.
e.g.
let
RunSQL = Sql.Database("MyServer", "MyDatabase", [Query=MyQuery])
in
RunSQLWhere 'MyQuery' is my SQL query pasted into the advanced editor of another query (keeps things tidy and easier to edit).
KNP ,
I like this idea of having a dedicated query just for the SQL native query. One of my gripes around native queries is that the SQL work is 'hidden' in the M query and difficult to reference or reuse.
One question:
- Presuming the native SQL query (T1) is not loaded to the model, and that the query that references it as [Query=MyQuery] (T2) fully folds, does this solve the problem of merges on T2 breaking folding?
Pete