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.
Hi Noah - great question ! As of today and as I have mentionned in my presentation the detailed table has to be in DirectQuery mode. You can build your logic by implementing IF statements and managing yourself the different aggregates though.
This has been fantastically helpful, thank you! Just to say though, the precendence in factors of 10 allows for easy insertion of additional tables in future development/maintenance. 😊
Brilliant approach.I have a question, if we create incremental refresh and publish the report we can't downlaod the report/dataset from PBI service and it has to be managed by XMLA end point. Can we edit/modify the agg tables (Manage aggregations part) using XMLA end point using tools like Tabular editor or ALM Tool kit ?
For the mapping of the columns in DQ for an Agg table. I do believe that's only possible in desktop. I even asked a few colleagues and they weren't aware of any way to do that for the model in the service. Vs republishing the PBIX from desktop, and letting the model do a full refresh.
Two other responses actually from Ricardo Rincon From Ricardo: docs.tabulareditor.com/te2/Useful-script-snippets.html#setting-up-aggregations-power-bi-dataset-only Taking advantage of the topic, some time ago there were some comments about the need to allow that the detail table of the aggregations can be import and not only direct query, so I share with you a trick that I learned from Ruben Pertusa Lopez in a conference talk to make aggregations with import detail table (yes, aggregations with import detail table), it does not always work but it is interesting and very simple, the only thing you need is to define an aggregation of precedence 0 with the same fields of the detail table, with this the engine should never have to go to the origin direct query (again I comment that it does not work in all cases) but in the last instance it will look for the data in the detail table import (the aggregation with precedence 0).
Yes but I only have ~ 4 years of data (so ~ 1,460 dates). My Agg1 is at the date / vendor / rates / Store & Forward flag / Payment type so ... 100k rows totally makes sense !
This is by far the best video I've seen on aggregations and dealing with large data sets. Much appreciated.
Very good video on aggregations. Really appreciate it. Feeling very confident to try this.
Great Video, is there any source i can download the presentation files? thank you
wonderful video..Great job Tristan and Haven consulting for putting this together.
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.
Hi Noah - great question ! As of today and as I have mentionned in my presentation the detailed table has to be in DirectQuery mode.
You can build your logic by implementing IF statements and managing yourself the different aggregates though.
This has been fantastically helpful, thank you! Just to say though, the precendence in factors of 10 allows for easy insertion of additional tables in future development/maintenance. 😊
Interesting! Thanks for sharing this information. Thumbs up!!
Brilliant approach.I have a question, if we create incremental refresh and publish the report we can't downlaod the report/dataset from PBI service and it has to be managed by XMLA end point. Can we edit/modify the agg tables (Manage aggregations part) using XMLA end point using tools like Tabular editor or ALM Tool kit ?
For the mapping of the columns in DQ for an Agg table. I do believe that's only possible in desktop. I even asked a few colleagues and they weren't aware of any way to do that for the model in the service. Vs republishing the PBIX from desktop, and letting the model do a full refresh.
Two other responses actually from Ricardo Rincon
From Ricardo:
docs.tabulareditor.com/te2/Useful-script-snippets.html#setting-up-aggregations-power-bi-dataset-only
Taking advantage of the topic, some time ago there were some comments about the need to allow that the detail table of the aggregations can be import and not only direct query, so I share with you a trick that I learned from Ruben Pertusa Lopez in a conference talk to make aggregations with import detail table (yes, aggregations with import detail table), it does not always work but it is interesting and very simple, the only thing you need is to define an aggregation of precedence 0 with the same fields of the detail table, with this the engine should never have to go to the origin direct query (again I comment that it does not work in all cases) but in the last instance it will look for the data in the detail table import (the aggregation with precedence 0).
www.linkedin.com/in/nexus150/ his LinkedIn if you'd like to reach him
Thank you for this great video! 😊My question is which is more efficient the aggregate table in a composite model or incremental refresh?
Great vídeo! Thanks
Just what I needed. Thank you!
You're so welcome!
Really good presentation. Congratts!
Thanks Rodrigo!
How do you do a distinct count with these agg tables to map it back to your dq fact?
You can use either DAX DISTINCT or Power Query Group By options to create a table with distinct IDs at any level :)
Any videos on how to create those aggregated tables?
A good step by step blog from Reza would be good for that. radacad.com/power-bi-aggregation-step-1-create-the-aggregated-table
Super informative !
Glad it was helpful!
Really interesting talk - thanks!
Glad you enjoyed it!
🎉❤
How did the number of rows dropped from 300+Mn to 180 k after removin few columns
The grain is still the date ....
Yes but I only have ~ 4 years of data (so ~ 1,460 dates).
My Agg1 is at the date / vendor / rates / Store & Forward flag / Payment type so ... 100k rows totally makes sense !