Aggregation to speed up the performance of a Power BI report even if all IMPORTED
Вставка
- Опубліковано 31 лип 2024
- One of the questions I normally get after presenting about aggregations in Power BI is that, does the aggregation works only for Composite mode and in scenarios to speed up the DirectQuery? or does it work in import mode as well? This video is about this question and shows how you can speed up your model with aggregation even if all imported.
Get the links to study more from my blog article here:
radacad.com/aggregation-to-sp...
*******************
Want to learn about Power BI or AI? or take your Power BI and AI knowledge to the next level for building reports? Check out our courses in RADACAD Academy for all aspects of Power BI and AI.
RADACAD courses: learn.radacad.com
Become an academy member: learn.radacad.com/academy/
*******************
********
CONNECT with US!
********
-- / radacad_com
-- / rad_reza
-- / leila_etaati
-- / radacad
-- / leila-etaati-b0304636
-- / rezarad
-- / 6393399
-- radacad.com
******** - Наука та технологія
This is a very simple, but very important point, thank you so much for giving this air time. Cheers
Just came across this video and it has helped me lots with a case I have at work. Many thanks Reza for sharing your knowledge and creating these videos about Power BI
Dear Reza, Absolutely amazing, thank you for sharing your knowledge with us
Reza, another great video, thank you so much!
Simply, thank you!!
Thanks for the explanation Reza :)
I always do Aggs Tables in my models and create explicit measures on that tables, but use big fact table measures only with Drill Through
I should give this a try with some of my Azure Analysis Services (AAS) models.
Great tip
Can I set up aggregations with two import tables. My detailed import FACT table has 50M rows. Then I have a product AGG which has 15M. Lastly I have a HRCHY_Agg that has 5M rows. My dax would be so much faster if I could do this. I can't set the 50M row table to direct query because it is frequently referenced and import is way faster. But when I set the detailed fact table to Import I can't seem to set up aggregations.
very smart
Thank you! Very important topic for big data use cases. I've tried a similar approach, but I've come to a deadlock:
I have a fact table with last 4 years (2020-2023) of data and an aggregated table with the last 2 years (2022-2023) of data. When use the date slicer and pick a date prior to 2022, I get a blank visual because the measure always hits the AGG (which does not have data for this time period) and not the facts table as expected. Have you guys faced a common issue? Is there a workaround?
Hi, will aggregation work with inactive relationship and USERELATIONSHIP function?
But one thing that missed here is that you might have tested it with performance analyser for the delta of using agg table case with non agg table case. Then people understand the significance of this topic...huh??
But any way thank you for your effort, Reza. Big box of love from India
Thank you. Major limitation I see is not having the incremental refresh on dual storage mode tables in composite model which is required for aggregate table. Without incremental refresh, we can not do full refresh of hundreds of millions of rows of data every day. Any suggestions?
Hi.
a Dual storage mode table is normally a dimension with not many rows. if your table has 100s of millions of rows, then it is very likely a fact table, best to be kept DirectQuery, but then the aggregated tables on top of that can be smaller and imported
@@RADACAD Hi, Thank you for quick reply. Dim table is SCD type 2 and has historical data worth of 400 million. Fact table too has hundreds of millions on which we thought of having aggregation layer but found that limitation.
What would generally be the cutoff size for dimensions which we can decide whether its in DQ or dual..?