Approach for loading the big json files

[PBILearner2022]

Hi PBI Experts,

I am building a pbi dashboard by using JSON file. I used a sample json(7 mb) file and built the data model, Dashboard. When i recvied the acutal data and I perform refresh with the actual data(76mb) it keeps on loading it took more than 5 hours to load the data.

When I wanted to do some transformation in power query it keeps on loading and i am not able to do it

is there any way we could load it faster?

Kindly suggest the best approach for loading the big json files in power BI

[PBILearner2022]

Hi @v-jialongy-msft , Thank you for the suggestion.

In my scenario incremenatal refresh wont be working since we will be refreshing different data sets each time.

We will try to parse json / load the json to SQL server and give it a try 

[v-jialongy-msft]

Hi @PBILearner2022 

Can you tell me if your problem has been solved, if yes, please accept it as a solution

Best Regards,

Jayleny

[PBILearner2022]

Hi @v-jialongy-msft , We were not able to solve this isssue in Powebi, Instead we used python for parsing json aand stored it in xls and consuming in the dashboard.

We are planning to load this data data to sql server data base as next steps and consume it as source for powerbi.

[v-jialongy-msft]

Hi @PBILearner2022 

Handling large JSON files in Power BI can indeed be challenging, Here are some strategies to help improve the loading time and efficiency:

Incremental Refresh:

• If your dataset gets updated with new data periodically, consider using Power BI's incremental refresh feature. This way, you only load new or changed data rather than reloading the entire dataset each time.
  
  
  

Optimize the JSON File Structure:

• Ensure that the JSON file is well-structured and optimized for parsing. A deeply nested or poorly structured JSON file can significantly slow down the import process

Increase Resources:

• Ensure that your machine or the server running Power BI has enough resources (RAM and CPU) to handle large files. Power BI operations are resource-intensive.

Optimize Data Model:

• After loading, optimize your data model. Remove unnecessary columns, use efficient data types, and ensure that relationships are correctly set up. This can help improve overall dashboard performance.

Upgrade Power BI Version:

• Ensure you are using the latest version of Power BI, as Microsoft continuously improves performance and data handling capabilities.

Use a Database:

• In some cases, it may be more efficient to load the JSON data into a database (like SQL Server) and then connect Power BI to the database. Databases are optimized for handling large datasets and complex queries.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.