Hi Patrick! How are you? Happy Sunday & Stay Healthy! The aggregation tbl in the data model has the storage model as import. So, when we refresh it the new data will be imported to the PBI model, correct? In short, as the time goes by the size of my PBI file will increase, correct? Have a great Sunday & stay home & healthy!!!
Hey Patrick! Big fan here, thank you for all your work! Aggregation tables are a 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 (2 billion records) 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. Am I missing something important? Does the AGG table need to encompass the full dataset?
Dear Patrick, thanks for all the great videos. I have a task in hand that I solved, but not in the exact way that I wanted, and I believe you're the guy to help me out. I have a table with sales and a date table (to simplify) and I have a bunch of graphs filtered by year and month (2 different slicers). In the same page a need a graph of the last 12 months of sales by month, starting on the one on the slicer (not today) going back. I could do it with two extra disconnected slicers (year and month), but in no way it worked with the same existing two. It seems that the issue is in reading the values from the original slicers, as putting DATE(YY, MM, DD) works fine, but when I try DATE(SELECTED VALUE(year), SELECTED VALUE(month), 1), it didn't work. I created two variables to hold the initial and final dates, and tried using datesbetween, datesinperiod and even direct filtering Date>=inicial_date && Date
Hello Gentlemen, was wondering what would you recommend is a solid shortcut path to passing the 70-778 exam ? I have taken a few practice exams and score 50-60% so definitely need to improve .. Maybe a video about 70-778 exam path to success haha
Hi Patrick--so here's a question. I noticed that in the Aggregation table, there are fields coming from Fact tables and Dim tables. Is an AGG table more like a Fact table? Or are there in fact three types of tables (Fact, Dim, and Agg)? And a corollary--it is possible then to have duplicative relationships in a model? Meaning, I have a relationship between a fact table and a dim table, but then when I aggregate on a particular dim, I have (potentially) another relationship?
hmmm Will need to go back through to see where it is pulling items from the dimension into the fact. The Agg table is a summarized view of the Fact table. So, really the fact table at a higher grain. Both the fact table and the agg table will need to have a relationship to the associated dimension :)
@@GuyInACube Late question When you add a relation between the products table and the aggregation table you will have a circular dependency 4 minutes and 43 seconds into the video? Second: Are there shortcomings of using extended events instead of profiler? Might be interesting to hear about this tomorrow.
Thank you, bro!
This is great content. There's a lot of value in here. Thank you!
I was today years old when I learned about SQL server profiler plugged in PowerBI, tks Patrick, Adam and team
Learned a new feature on the DAX Studio. Thanks Patrick!!
Awesome!! 👊
Hi Patrick! How are you? Happy Sunday & Stay Healthy! The aggregation tbl in the data model has the storage model as import. So, when we refresh it the new data will be imported to the PBI model, correct? In short, as the time goes by the size of my PBI file will increase, correct? Have a great Sunday & stay home & healthy!!!
Hey Patrick! Big fan here, thank you for all your work!
Aggregation tables are a 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 (2 billion records) 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.
Am I missing something important? Does the AGG table need to encompass the full dataset?
Hello,
Did you get the solution for this?
@nidhisingh4973 Hello!
Unfortunately, you need your AGGs need to encompass the full dataset. There's no way around.
Dear Patrick, thanks for all the great videos. I have a task in hand that I solved, but not in the exact way that I wanted, and I believe you're the guy to help me out. I have a table with sales and a date table (to simplify) and I have a bunch of graphs filtered by year and month (2 different slicers). In the same page a need a graph of the last 12 months of sales by month, starting on the one on the slicer (not today) going back. I could do it with two extra disconnected slicers (year and month), but in no way it worked with the same existing two. It seems that the issue is in reading the values from the original slicers, as putting DATE(YY, MM, DD) works fine, but when I try DATE(SELECTED VALUE(year), SELECTED VALUE(month), 1), it didn't work. I created two variables to hold the initial and final dates, and tried using datesbetween, datesinperiod and even direct filtering Date>=inicial_date && Date
Hello Gentlemen, was wondering what would you recommend is a solid shortcut path to passing the 70-778 exam ? I have taken a few practice exams and score 50-60% so definitely need to improve .. Maybe a video about 70-778 exam path to success haha
Hi Patrick, how would this work if you want to aggregate at a higher date dimension, fx by month?
Hi Patrick--so here's a question. I noticed that in the Aggregation table, there are fields coming from Fact tables and Dim tables. Is an AGG table more like a Fact table? Or are there in fact three types of tables (Fact, Dim, and Agg)? And a corollary--it is possible then to have duplicative relationships in a model? Meaning, I have a relationship between a fact table and a dim table, but then when I aggregate on a particular dim, I have (potentially) another relationship?
hmmm Will need to go back through to see where it is pulling items from the dimension into the fact. The Agg table is a summarized view of the Fact table. So, really the fact table at a higher grain.
Both the fact table and the agg table will need to have a relationship to the associated dimension :)
@@GuyInACube Late question When you add a relation between the products table and the aggregation table you will have a circular dependency 4 minutes and 43 seconds into the video? Second: Are there shortcomings of using extended events instead of profiler? Might be interesting to hear about this tomorrow.
SuperB content
Thanks! 👊
You know what else can cause PowerBI fail to hit an agg table? Forgetting to setup one of the aggregation types >.