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
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.
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! 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?
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..?
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!!
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
Thanks for the explanation Reza :)
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.
I should give this a try with some of my Azure Analysis Services (AAS) models.
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! 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?
Great tip
Hi, will aggregation work with inactive relationship and USERELATIONSHIP function?
very smart
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..?