Forum Discussion
Visual initial loading time retrieval
hi desperate need help
I have a report that connects to a database in DirectQuery mode. However, the file is large (9 MB). When users first launch the report, the three visuals show as empty with continuous loading icons and take several minutes to appear. Is there a solution to reduce the initial loading time to 1–2 seconds? I cannot modify the DAX measure and Direct query because I will get blamed. How can I achieve an initial loading time of under 2 seconds if the relationship remains active?
this is how it works
When the relationship is active, the initial loading of these 3 visuals in Power BI takes 1 minute.
When the relationship is inactive, the initial loading takes 3 seconds.
How can I achieve the initial loading of under 2 seconds if the relationship is active?
Hi CU08101843,
The fact that the visuals load in about 3 seconds when the relationship is disabled is a very strong clue.
I would not focus on the 9 MB PBIX size here. In DirectQuery, the important part is the SQL Power BI generates against the source.
With the relationship active, Power BI may need to join the two tables for each visual, and that join can become expensive depending on:
relationship cardinality join key data type indexes filter direction source table sizeMicrosoft’s DirectQuery guidance specifically recommends checking the generated source query and optimizing the relationship/join columns.
I would start with Performance Analyzer and copy the query for one of the slow visuals.
Then run that query directly against the database and check:
- execution plan
- indexes on both relationship keys
- whether the join columns use the same data type
- whether casts or calculated expressions are being introduced
- whether the relationship is bidirectional unnecessarily
One setting worth testing is Assume referential integrity.Microsoft documents that, when the data satisfies the requirements, Power BI can use an INNER JOIN instead of an OUTER JOIN, which can make DirectQuery joins more efficient.
The requirements are important though:
- no null/blank values on the many-side key
- every many-side key must have a matching value on the one side
Microsoft explains that behavior in the Assume referential integrity documentation.So my troubleshooting order would be:
Performance Analyzer -> inspect generated SQL -> validate indexes and join keys -> test Assume referential integrity if the data allows it -> review relationship direction/cardinalityIf the same SQL query still takes close to a minute when run directly against the database, then the main fix will be at the source/index/query-plan level rather than in DAX.
Getting every DirectQuery visual under 2 seconds may not always be realistic, but a jump from 3 seconds to 60 seconds because of one relationship is definitely something I would investigate at the generated join first.
AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
7 Replies
v-kathullacCommunity Support
Hi CU08101843 ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
Regards,
Chaithanya- v-kathullac
Hi CU08101843 ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
Regards,
Chaithanya - Praful_Potphode
Super User
Hi CU08101843
we cannot bring the loading time down all of sudden.below are some of the best practices that you can follow in your model.
- try to change storage mode of your powerapp table to import.
- use performance analyzer to identify which visual is loading slow.if its table with 23 column then reduce the columns in landing page so that it loads fast.create a drillthrough page which has all 23 columns.
- try to save your report with default filters applied so that data load is less which in turn improves visual load time.
Please give kudos or mark it as solution once confirmed.
Regards,
Praful
Praful_Potphode - thanks, your solution may help others
- try to change storage mode of your powerapp table to import.
Answer: Changed to import, but it does not auto reflect the input that user key in from powerapp to the table
any help on workaround where when users first launch the report, the three visuals already show something (not empty). Users just want it to see something instead of empty for 30seconds. desperate need help. the top said if i cant solve this i will need to be out.- Praful_Potphode
- In that case design a new landing page with no visuals and from there navigate to actual report
- Restrict the filters to show a month or a day's data
Please give kudos or mark it as solution once confirmed
Regards,
Praful
- try to change storage mode of your powerapp table to import.
jubinsoniSolution Sage
Hi CU08101843,
I think the key point is that your test already tells you where the problem is. With the relationship inactive the visuals load in 3 seconds, so the cost comes from the join that DirectQuery generates when the relationship is active, not from the measures.
In DirectQuery every visual sends SQL to the source, and an active relationship turns into a join in that SQL. If Power BI cannot assume the keys always match, it uses outer joins, which are much heavier on a large table. You do not need to touch the DAX or the queries to improve that. The relationship setting is described in the Assume referential integrity documentation
The setting only helps if it is true, meaning every row on the many side has a matching key on the one side. It is also worth checking that both tables come from the same source, because a relationship across two different sources cannot be pushed down as a single join. To see the SQL that is sent, run Performance Analyzer and look at the query for the slow visuals, as described in the Performance Analyzer documentation
So I would work through it in this order
1. Performance Analyzer -> copy the query of the slow visual 2. Relationship -> Assume referential integrity (if the data allows it) 3. Source database -> index the join key columns 4. Both tables in the same source -> join is pushed to SQL Active relationship -> JOIN in the SQL -> 1 minute Inactive relationship -> no JOIN -> 3 secondsPlease also test it with a quick change first. Turn on Assume referential integrity on a copy of the file, and compare the Performance Analyzer duration for those same three visuals. If the source has an index on the key columns, the improvement is usually visible straight away.
So I would start with the relationship setting and the indexes, because neither of them needs a change to the DAX or the DirectQuery source. Please let us know the result and mark a reply as the solution if it helps.
AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
- ShivekMaharaj
Community Champion
Hi CU08101843,
The fact that the visuals load in about 3 seconds when the relationship is disabled is a very strong clue.
I would not focus on the 9 MB PBIX size here. In DirectQuery, the important part is the SQL Power BI generates against the source.
With the relationship active, Power BI may need to join the two tables for each visual, and that join can become expensive depending on:
relationship cardinality join key data type indexes filter direction source table sizeMicrosoft’s DirectQuery guidance specifically recommends checking the generated source query and optimizing the relationship/join columns.
I would start with Performance Analyzer and copy the query for one of the slow visuals.
Then run that query directly against the database and check:
- execution plan
- indexes on both relationship keys
- whether the join columns use the same data type
- whether casts or calculated expressions are being introduced
- whether the relationship is bidirectional unnecessarily
One setting worth testing is Assume referential integrity.Microsoft documents that, when the data satisfies the requirements, Power BI can use an INNER JOIN instead of an OUTER JOIN, which can make DirectQuery joins more efficient.
The requirements are important though:
- no null/blank values on the many-side key
- every many-side key must have a matching value on the one side
Microsoft explains that behavior in the Assume referential integrity documentation.So my troubleshooting order would be:
Performance Analyzer -> inspect generated SQL -> validate indexes and join keys -> test Assume referential integrity if the data allows it -> review relationship direction/cardinalityIf the same SQL query still takes close to a minute when run directly against the database, then the main fix will be at the source/index/query-plan level rather than in DAX.
Getting every DirectQuery visual under 2 seconds may not always be realistic, but a jump from 3 seconds to 60 seconds because of one relationship is definitely something I would investigate at the generated join first.
AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
