Forum Discussion
An error occurred while processing the data in the dataset in PowerBI service
Hi,
I found the actual issue and believe got solution for this.
So the error which I posted in my ticket orginally is correct but misleading as well. My PowerBI data model with 4 million records was just 120 MB whereas when I try loading 5 million records I was getting that error. I dont believe my powerbi model file does go over 1GB max limit.
To investigate the issue further I created a VM on azure with big config of around 28GB RAM, mult-cores prcessor etc. I tried refreshing my model on that machine keeping an eye on the Activity Monitor. I found that all the rows were successfully read into the model without any high use of resources but after reading all the rows then something happened underneth which quickly spiked-up to the full usage of RAM and it took a while before finishing the complete Refresh. I checked the PowerBI model file size... surprisingly it was just under 410 MB only.
So what is the issue????
Then I looked at the my model to investigate what took it to consume such a high amount of RAM after reading all the rows, and there I found the real issue... I created around 15 calculated columns (mostly int i.e. 64bits)... and I beleive these calulated columns were actually needed huge amount of memory to process i.e. (15 columns x 64 bits x 5,000,000 rows = aroung 4 GB ).
I discussed this with Microsoft and they confirmed that because PowerBI is a software as a service there is an upper cap limit on the max resource usage and this cannot be altered for any client requests. The compression engine in Power BI doesn't like wide data as much as it likes long data.
Ironically, I had to convert all of my calculated columns into caluclated measures and that fiixed the issue. The calculated columns are actually required to be calculated and stored as a physical column within the model whereas calulated measures get calulated later within the memory.
Let me know if anyone have any other important thought on this.
Thanks
IP
Hi,
I found the actual issue and believe got solution for this.
So the error which I posted in my ticket orginally is correct but misleading as well. My PowerBI data model with 4 million records was just 120 MB whereas when I try loading 5 million records I was getting that error. I dont believe my powerbi model file does go over 1GB max limit.
To investigate the issue further I created a VM on azure with big config of around 28GB RAM, mult-cores prcessor etc. I tried refreshing my model on that machine keeping an eye on the Activity Monitor. I found that all the rows were successfully read into the model without any high use of resources but after reading all the rows then something happened underneth which quickly spiked-up to the full usage of RAM and it took a while before finishing the complete Refresh. I checked the PowerBI model file size... surprisingly it was just under 410 MB only.
So what is the issue????
Then I looked at the my model to investigate what took it to consume such a high amount of RAM after reading all the rows, and there I found the real issue... I created around 15 calculated columns (mostly int i.e. 64bits)... and I beleive these calulated columns were actually needed huge amount of memory to process i.e. (15 columns x 64 bits x 5,000,000 rows = aroung 4 GB ).
I discussed this with Microsoft and they confirmed that because PowerBI is a software as a service there is an upper cap limit on the max resource usage and this cannot be altered for any client requests. The compression engine in Power BI doesn't like wide data as much as it likes long data.
Ironically, I had to convert all of my calculated columns into caluclated measures and that fiixed the issue. The calculated columns are actually required to be calculated and stored as a physical column within the model whereas calulated measures get calulated later within the memory.
Let me know if anyone have any other important thought on this.
Thanks
IP