Forum Discussion
Incremental refresh vs Historization
Hi AlexandraSz ,
I want to validate the valuable input provided by lbendlin . Their initial thoughts helped guide my approach. However, I noticed that more detail is needed to fully understand the problem.
You can focus on optimising your DirectQuery solution for real-time reporting, specifically by improving the performance of your queries and underlying views.
1. Make sure that the database views used in DirectQuery mode are optimised for performance. This may involve indexing strategies or materialising views (if supported by the database system) to speed up queries that filter the latest rows for each product.
2. Consider Aggregate Tables: For historical data, consider creating aggregate tables in Power BI that summarise key metrics. These tables can be used in conjunction with DirectQuery to improve performance. More details can be found at the link: Automatic aggregations overview - Power BI | Microsoft Learn.
If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks to you both!
To give you a few extra details:
The data source is Azure synapse analytics
We don't perform too complex operations, we usually JOIN two tables and use a similar operation to get the latest rows:
....Select statement
ROW_NUMBER() OVER (PARTITION BY [PRODUCT ID], [TYPE] ORDER BY [TIMESTAMP] DESC) AS rownumber
....
from sourcedata where rownumber = 1
Unfortunately we need all data displayed, we don't need aggregations, we need only a simple table and a few slicers. We would only need for the report to load a bit faster than it is currently.
Do you have well maintained indexes (indices) on these three columns?
