Incremental refresh vs Historization

[AlexandraSz]

Hello!

Let's say I have a table called Products: when a product is added or either edited, i receive a new row in my database.

Unfortunately these are duplicated, so I would have a row like:

Product 1   Green color   21/02/2023

Then when the product is updated i receive a new row:

Product 1 Blue color 4/4/2024

Both rows would be kept but only the latest would be relevant for reporting.

Users need real time reporting - so first we tried Direct Query and used it on a view that only filters for the latest row for the product. It is working but loading the report takes a long time.

We considered 2 options:

Option 1:

Create 2 views:

- One for historical data, everything before the past 5 days would be imported via Import mode and refreshed once a day.

- Second for live reporting, would be refreshed via direct query continuously during the day.

I even managed to append the direct query and import tables so users could see all in one place

However when publishing to service, I think the direct query function is disabled due to the import table and the data is not really "live".

Option 2:

Create an incremental refresh. We were also able to set this up, but we think it might not be working ideally.

We ticked the "Take the latest data via Direct query" option for the live reporting purpose.

Let's say the product is updated and the direct query picks it up:

Product 1 Blue color 4/4/2024

But in the same time, becase the incremental refresh did not yet happen, the old date is still showing and would disappear only after a regular refresh, right?

Product 1   Green color   21/02/2023

Do you have any suggestions on how to achieve a fast, near real time reporting option?

[Anonymous]

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.

[AlexandraSz]

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.

[lbendlin]

Do you have well maintained indexes (indices) on these three columns?

[lbendlin]

Stay with Direct Query and optimize your data source (SQL Server?)  for the type of queries produced by Power BI. Indexes, statistics, materialized views etc. The works.