How to fit 200 Million rows in less than 1 GB in Power BI!
Ғылым және технология
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...
Пікірлер: 33
Very thought-inducive, was listening in the background and had to check if subscribed already. Thank you
@DataMozart
6 ай бұрын
Cheers, thanks!
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.
Very cool, this is extremely helpful. Well explained. Thank you
@DataMozart
7 ай бұрын
Thanks, glad to hear that!
Thanks a lot for this tip. Very well explained
@DataMozart
2 жыл бұрын
Glad it was helpful!
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.
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
excellent info, thanks for sharing
@DataMozart
4 ай бұрын
Glad it was helpful!
Great job. Avesome tricks
@DataMozart
2 жыл бұрын
Thanks!
Overall, pretty amazing!
@DataMozart
2 жыл бұрын
Thanks! 😃
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
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
Can you have more than two hot/cold fact tables? Like a hot, cold, and coldest fact table? Working with ~300 million rows currently.
@DataMozart
Жыл бұрын
Hi Mariam, Absolutely! You can create as many tables as you want, but be careful because of the measure complexity.
While connecting sharepoint excel to power bi getting error as the data in the preview has been truncated due to size limits. Pls assist i need to link the file to bi
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?
Hi please suggest can i import 3-5 million records into power bi desktop from sql server... Is this possible for pro users. Thanks in advance
@DataMozart
4 ай бұрын
Hi, it depends how many columns you have, their cardinality, etc. But, in most cases, yes, 3-5 million rows can be easily imported in Power BI.
Shouldn't there be a connection between the Hot and Hot Agg table? So that Power BI knows how to traverse from one to the other?
@DataMozart
2 жыл бұрын
Great question! Luckily, Power BI "knows" that on its own, so we don't need to set any explicit connection between these two. Hope this helps!
How to handle like 2,000 column😅😅😅😂😂😂??