How to Aggregate Large Datasets in Power BI (with Tristan Malherbe)

Рет қаралды 8,347

Havens Consulting

Күн бұрын

Пікірлер: 29

@mpikers Жыл бұрын

This is by far the best video I've seen on aggregations and dealing with large data sets. Much appreciated.

@damionc 2 жыл бұрын

Very good video on aggregations. Really appreciate it. Feeling very confident to try this.

@charleykirton 10 ай бұрын

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. 😊

@carloscantu75 2 жыл бұрын

Just what I needed. Thank you!

@HavensConsulting 2 жыл бұрын

You're so welcome!

@wayneedmondson1065 2 жыл бұрын

Interesting! Thanks for sharing this information. Thumbs up!!

@raghun2636 Жыл бұрын

wonderful video..Great job Tristan and Haven consulting for putting this together.

@noahhadro8213 Жыл бұрын

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.

@TristanMalherbe Жыл бұрын

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.

@FabioRBelotto 5 ай бұрын

Great vídeo! Thanks

@rodrigosouza6327 2 жыл бұрын

Really good presentation. Congratts!

@TristanMalherbe 2 жыл бұрын

Thanks Rodrigo!

@AHMEDALDAFAAE1 Жыл бұрын

Thank you for this great video! 😊My question is which is more efficient the aggregate table in a composite model or incremental refresh?

@srinithv625 Жыл бұрын

Great Video, is there any source i can download the presentation files? thank you

@alexmcqueen5563 2 жыл бұрын

Really interesting talk - thanks!

@HavensConsulting 2 жыл бұрын

Glad you enjoyed it!

@muzahidali12 9 ай бұрын

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 ?

@HavensConsulting 9 ай бұрын

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.

@HavensConsulting 9 ай бұрын

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).

@HavensConsulting 9 ай бұрын

www.linkedin.com/in/nexus150/ his LinkedIn if you'd like to reach him

@raj7sam 10 ай бұрын

Any videos on how to create those aggregated tables?

@HavensConsulting 10 ай бұрын

A good step by step blog from Reza would be good for that. radacad.com/power-bi-aggregation-step-1-create-the-aggregated-table

@rapatel1642 10 ай бұрын

How do you do a distinct count with these agg tables to map it back to your dq fact?

@HavensConsulting 10 ай бұрын

You can use either DAX DISTINCT or Power Query Group By options to create a table with distinct IDs at any level :)

@francisjohn6638 Жыл бұрын

Super informative !

@HavensConsulting Жыл бұрын

Glad it was helpful!

@RakeshKumar-gr1yg Жыл бұрын

How did the number of rows dropped from 300+Mn to 180 k after removin few columns The grain is still the date ....

@TristanMalherbe Жыл бұрын

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 !