Why daily and monthly aggreated tables are a proper solution (DirectLake over OneLake)?

[jaryszek]

Hello Guys,

this is my model:

and using DirectLake on OneLake. 

I have a milions of records so this should optimize the model. But why? What is a engine behind and how technically it will be faster than having everything in one Daily Fct table ?

Can anybody explain why this will be faster ?

Best,
Jacek

[v-hjannapu]

Hi @jaryszek,
Thank you for posting your query in Microsoft Fabric Community Forum.
 When you use both monthly and daily tables with Direct Lake, Power BI automatically picks the right table based on your report view. If your report shows data by month, it will read from the smaller monthly table instead of scanning the big daily table with millions of rows. This reduces the amount of data processed and makes it faster. Direct Lake also reads data directly from OneLake without loading everything into memory, which improves speed even more. So, your model is faster because Power BI uses summarized data when possible and detailed data only when needed.

Hope this helps if you have any queries we are  happy to assist you further.
Best regards, 
Community Support Team.

[jaryszek]

Thank you,

can you write more about " When you use both monthly and daily tables with Direct Lake, Power BI automatically picks the right table based on your report view. "?

How to set up it?

Best,
Jacek

[v-hjannapu]

Hi @jaryszek,
That part works when you set up aggregation tables in your model. Basically, you tell Power BI that your monthly table is a summarized version of your daily table. Once that link is defined, Power BI will automatically use the monthly table when your report shows data by month or year, and only go to the daily table when you look at detailed daily-level visuals.

To do this, open Model view, check that your tables are properly related, then go to the Aggregations option and map the monthly table to the daily one for example, group by Month, sum Cost, etc. After that, Power BI handles it automatically in the background.

Hope this helps.

Best regards,
Community Support Team.

[jaryszek]

Thank you, but this is misleading - it can work only for direct query, not direct lake 

[v-hjannapu · Accepted solution]

Hi @jaryszek,

Thank you for pointing that out. The aggregation feature works only in DirectQuery mode, not in Direct Lake.In Direct Lake, Power BI reads data directly from OneLake, so the automatic aggregation setup is not supported right now.

If you want better performance, you can create your own summarized tables for example, monthly or weekly level inside the Lakehouse and use them in visuals where detailed data is not required. This way, the report will still perform faster.

please go through with the below document hope it may resolve your Issuehttps://learn.microsoft.com/en-us/power-bi/transform-model/aggregations-advanced

Hope this gives you more clarity.

Best regards,
Community Support Team.