How to fit 200 Million rows in less than 1 GB in Power BI!

Рет қаралды 13,773

Күн бұрын

Quick question: what do you think, how much memory needs 200 million rows in #powerbi? 2GB, 1GB, 500MB? You're not even close!
Check how you can fit 200 million rows in only 80 MB, without sacrificing performance!
Blog post: data-mozart.com/power-bi-how-...
Phil Seamark's blog post on Filtered Aggregations: dax.tips/2019/10/30/creative-...
Shabnam Watson's blog post on Power BI Aggregations: shabnamwatson.wpcomstaging.co...

Пікірлер: 32

@drivetrainerYT 5 ай бұрын

Very thought-inducive, was listening in the background and had to check if subscribed already. Thank you

@DataMozart 5 ай бұрын

Cheers, thanks!

@SCoetzer Жыл бұрын

Great, but those tables are tiny, only a couple of fields. The fundamental issue with POWERBI is its removing data from a SQL sever/DWH environment to store the data on its own environment and then process that data for visualization. While the SQL server (Synapse) sits with 60 clusters and numerous computing nodes, once it passed the data over to POWERBI, it takes a nap. Why ? The architecture just duplicated data storage, data is processed twice. Why ? My Spark pool is on constant holiday. Synapse cluster is taking a vacation. Where I need processing power is in visualization, processing, turning data into reports.

@komanguy 2 жыл бұрын

Thanks a lot for this tip. Very well explained

@DataMozart 2 жыл бұрын

Glad it was helpful!

@devinpikafire 6 ай бұрын

Very cool, this is extremely helpful. Well explained. Thank you

@DataMozart 6 ай бұрын

Thanks, glad to hear that!

@enriamak 2 жыл бұрын

Very well explained! Thanks👍

@DataMozart 2 жыл бұрын

Glad it was helpful!

@enriamak 2 жыл бұрын

@@DataMozartHi again friend, how can i do to visualize both tables in one, depending on the date? That is, if(dateselected>15/10/2021(recent data), it shows data from hot_table,(else) it shows data from cold_table) That in 1 visualization. It's possible? Thanks a lot.

@getsetgoo3334 4 ай бұрын

excellent info, thanks for sharing

@DataMozart 4 ай бұрын

Glad it was helpful!

@kadirkkkk 2 жыл бұрын

Great job. Avesome tricks

@DataMozart 2 жыл бұрын

Thanks!

@jasonanderson7175 2 жыл бұрын

Great tutorial ! I have a question, I notice that you have a date table in your model. Do you make it correspond to the date of your two other table ?

@DataMozart 2 жыл бұрын

It's a regular date dimension, so once you establish the relationship between the date table and fact table, date table will filter fact table

@pabeader1941 2 жыл бұрын

Overall, pretty amazing!

@DataMozart 2 жыл бұрын

Thanks! 😃

@DamienThorn999 2 жыл бұрын

This doesn't seem to work for more in depth visuals with a lot of unions in it . But it did work for simpler tables where all data is within the same table as long as i also turned of Totals column also.

@ihueghianful Жыл бұрын

Same question here...I hope you get an answer

@rayemeric6532 2 жыл бұрын

Great Stuff, thanks for the video! I have a question regarding this. Is it possible to make something like this with no aggreagation? I have a use-case where i want to have the last 2 months as "Hot" and the older data as "Cold", but because we show the data in a scatter plot with every measurement and limits up to the second, we cant aggreagate this. I tried appending the two queries (Import and Direct) but afterwards i saw its still import. Anyway, keep up the good work!

@DataMozart 2 жыл бұрын

Hm, that's a good question Ray. Honestly, I'm not sure if that could work without aggregations. I believe you can get < 1 sec response from the hot data, but for the cold data it's hard without aggregations

@oeb9003 Жыл бұрын

Hello Ray did you find a way to do this?

@kumarprabhu 2 жыл бұрын

what to do when I want to have high granular level details in my aggregated table...I want to create aggregated table with maximum possible drill down so that I can get all answers from agg table not go to fact table with DQ......But if i add multiple fields my agg becomes heavy...how do we tackle that..please advice

@rajeshk5164 Жыл бұрын

You can try creating multiple aggregated tables (with different columns/levels) by referencing to the same fact table