Forum Discussion
PowerBI data refresh failures
Hi, SaravananM
The error reported for you is that the advanced capacity needs to be optimized. You can try to optimize the model.
Consider the optimization possibilities for a DirectQuery model.
At the datasource layer:
- The datasource can be optimized to ensure the fastest possible querying by pre-integrating data (which is not possible at the model layer), applying appropriate indexes, defining table partitions, materializing summarized data (with indexed views), and minimizing the amount of calculation. The best experience is achieved when pass-through queries need only filter and perform inner joins between indexed tables or views.
- Ensure that gateways have enough resources, preferably on dedicated machines, with sufficient network bandwidth and in close proximity to the datasource.
At the model layer:
- Power Query query designs should preferably apply no transformations - otherwise attempt to keep transformations to an absolute minimum.
- Model query performance can be improved by configuring single direction relationships unless there is a compelling reason to allow bi-directional filtering. Also, model relationships should be configured to assume referential integrity is enforced (when this is the case) and will result in datasource queries using more efficient inner joins (instead of outer joins).
- Avoid creating Power Query query custom columns or model calculated column - materialize these in the datasource, when possible.
- There may be opportunity to tune DAX expressions for measures and RLS rules, perhaps rewriting logic to avoid expensive formulas.
The size of a Premium capacity determines its available memory and processor resources and limits imposed on the capacity. The number of Premium capacities is also a consideration, as creating multiple Premium capacities can help isolate workloads from each other.
For more information on optimization, please refer to this document. https://docs.microsoft.com/power-bi/admin/service-premium-capacity-optimize
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Michael, Also I need to know how to kill those long running session in PowerBI service.
Capacity management can be tricky and there is usually not a single process that you can kill that will relieve all the pressure, perhaps there is simply too many reports and datasets running at the same time in which case you may need to look at increasing the size of the capacity. You will want to reduce the amount of memory used by optimising and consolidating data sets, and reduce CPU usage by refreshing data less often, improving DAX queries and limiting concurrent sessions(if possible), but again no silver bullet.
Microsoft does have some guidance on how to sort this out. Microsoft Power BI Premium capacity scenarios - Power BI | Microsoft Docs
#I can't think of why your data would be wrong, this does not seem right.