Hi Patrick - so would you ever put multiple aggregations in one Agg table? Or do you have separate tables for each level of aggregation for this to work? I know that in power query you can add multiple aggregations inside the Group function, but just wondering if that will work with this type of hybrid load and direct query model for aggs.
Hi, Never known that feature is already in Power BI! Do you know is this is also available in SSAS 2019? Most of our reports use Live Connection to SSAS tabular model on premise.
thanks Patrick, not aware of this feature in Power BI. Just one question, I have a scenario where I have the detailed fact table at Date level. I create aggregate fact table at Month level which is joined with Dim_Month table. If my query is at Year level, how Power BI know that it should query from Aggregate_Month_Fact instead of Detailed Fact? Is there a way in Power BI where I can set the context based on Hierarchy levels we define?
Hi Patrick! Nice video! A qq. Just for curiosity, what's the size of this model which has 4M rows. These aggregation tbls are with storage mode as "Import" and you configure the aggregate fields to the other tbls of the model which could be storage as "Dual" or "Direct Connect". So, every time that the PBI is refreshed to retrieve new data it will also create new rows in the aggregation tbls, correct? In the real world, what would be the best practices when creating model with aggregations in regards to performance? Thanks & stay @Home&Healthy! :)
Nice video as always, but Patrick... Why changing precedence made the difference? Why it was set 0 a then 20 (and not other number)? I don't follow it to be honest...
@Guy in a Cube Got a question for you. Let's say I have 3 aggregate tables. 1. State level - 15,000 rows 2. State and Store level - 2 million rows 3. State, Store, and Item level - 75 million rows. The direct query fact table probably have like 500 million rows or more. Without setting the precedent number in those 3 aggregate tables, it seems like the aggregate always hit the table #3 (75 mil rows), no matter if I do state or state + store level. I don't see much different in speed. My question. Is it worth creating those 2 other aggregate tables and importing those 2 million rows? The model will keep growing and eventually those 2 million rows could turn to be a 10 million rows. The refresh time will be longer and longer. I'm trying to save refreshing time and space. I can't schedule the refresh time since the data doesn't come out on the same date every month. Thanks for your time!
Nice thanks, you guys don’t know how much your videos help me
Appreciate that Marius! 👊
Hi Patrick - so would you ever put multiple aggregations in one Agg table? Or do you have separate tables for each level of aggregation for this to work? I know that in power query you can add multiple aggregations inside the Group function, but just wondering if that will work with this type of hybrid load and direct query model for aggs.
Hi,
Never known that feature is already in Power BI!
Do you know is this is also available in SSAS 2019? Most of our reports use Live Connection to SSAS tabular model on premise.
thanks Patrick, not aware of this feature in Power BI. Just one question, I have a scenario where I have the detailed fact table at Date level. I create aggregate fact table at Month level which is joined with Dim_Month table. If my query is at Year level, how Power BI know that it should query from Aggregate_Month_Fact instead of Detailed Fact? Is there a way in Power BI where I can set the context based on Hierarchy levels we define?
Hi Patrick! Nice video! A qq. Just for curiosity, what's the size of this model which has 4M rows. These aggregation tbls are with storage mode as "Import" and you configure the aggregate fields to the other tbls of the model which could be storage as "Dual" or "Direct Connect". So, every time that the PBI is refreshed to retrieve new data it will also create new rows in the aggregation tbls, correct? In the real world, what would be the best practices when creating model with aggregations in regards to performance? Thanks & stay @Home&Healthy! :)
Nice video as always, but Patrick... Why changing precedence made the difference? Why it was set 0 a then 20 (and not other number)? I don't follow it to be honest...
@Guy in a Cube Got a question for you. Let's say I have 3 aggregate tables.
1. State level - 15,000 rows
2. State and Store level - 2 million rows
3. State, Store, and Item level - 75 million rows.
The direct query fact table probably have like 500 million rows or more.
Without setting the precedent number in those 3 aggregate tables, it seems like the aggregate always hit the table #3 (75 mil rows), no matter if I do state or state + store level. I don't see much different in speed. My question. Is it worth creating those 2 other aggregate tables and importing those 2 million rows? The model will keep growing and eventually those 2 million rows could turn to be a 10 million rows. The refresh time will be longer and longer. I'm trying to save refreshing time and space. I can't schedule the refresh time since the data doesn't come out on the same date every month. Thanks for your time!
Hello,
I also got the same doubt. Do you have answer for this?
This is Bananas...Thanks a lot.