Forum Discussion
How power BI handle large DataSet?
Hey Maggi029 ,
from my perspective, you are facing or will be facing some challenges these challenges are
- Power BI file size limitations for upload, these limits are ~2GB for PRO licensing and ~10GB for Premium per capacity licensing
- Using Python inside the Power BI for data wrangling/data shaping
This is how I would tackle this
- Separate the content from the data by using two different pbix files, one that only publishes the data model to the service and a 2nd one that publishes the content. The content pbix is using the Power BI dataset as a data source. One advantage of this approach is that you gain more flexibility if architectural changes are happening.
- I would not use python for data shaping / data wrangling inside Power BI. This is simply for the following reasons. Using python (or R) for data shaping / data wrangling inside Power Query requires the on-premises gateway in personal mode if you are planning for scheduled data refresh. Some organizations (e.g. the one I'm working for) are not allowing the use of gateways in personal mode. Requesting one can lead to discussions with your Power BI service administrators.
Some architectural considerations
To overcome the file size limit that can be uploaded I would consider moving my python code to Azure, e.g. creating an Azure Automation script or writing an Azure function. This code then creates JSON documents. Each document contains data for a day or for a month, whatever you consider useful. The JSON document can be stored inside an Azure blob store. Then the files will be picked up by Power Query. You can create your data model using just a small number of JSON documents, publish the pbix file to the service, copy all the JSON documents to the blob container, manually refresh the dataset, done!
Hopefully, this provides some ideas on how to overcome your challenges.
Regards,
Tom
Hi TomMartens
Thanks for your reply , I think you suggestion have some useful benefits. I have few dounts needs to clarify on your suggestion ,since im new to this method.
1. Separate the content from the data by using two different pbix files - What is usecase for this method, is it also supports size management?
2.. Azure Automation script- According to my understanding, you're suggesting that you write an Azure automation script that pulls data from elastic search and converts it into small documents and save it in blob storage, which you can then connect to PowerBI using Azure Blob Storage. Correct me if i was wrong.
Thanks
Hey Maggi029 ,
in regards to 1
next to be able to develop the data model and the content in parallel, it's important to know, that you can't download the pbix file once you have enabled incremental refresh.
Incremental refresh does not directly support handling large files, but you can reduce the duration of a data refresh as only the incremental will be added to the model. This will become a little bit more complex if the source are files, but there are many posts out there, which are describing the proper configuration. The one of GilbertQ is my favorite: How you can incrementally refresh any Power BI data source (This example is a CSV File) - Reporting/Analytics Made easy with FourMoo and Power BIin regards to 2
Yes, accessing files from a blob store, is more simple than publishing a single large pbix file to the Power BI service.
Regards,
Tom
