How should I deal with large dataset in PowerBI for json data?

[cte_crest]

Hi guys, 

I am new to PowerBI. However, in the last month, I have done extensive research on PowerBI. I wanted some suggestions on how to implement a use case.

I have a large flow of JSON data which can be fetched from the rest API, which is about 2.5 Million records per day. I am trying to solve it using various ways:

1. Using the PowerBI push APIs: But, there is a cap of a maximum of 5 Million records per table for data pushed through APIs.
2. Using Native Connector: But, custom rest APIs doesn't support incremental refresh. So, every time I refresh, I would have to fetch all of those records again, which would be very inefficient. Also, I think the desktop allows max data of 1 GB.

So, the above approaches which I found promising won't work. So, is there a way that I am unaware of to fulfil such use case? 

Basically, I want to fetch a large amount of JSON data through API and store it in PowerBI dataset.  

Also, is there any alternative way such that I can push my JSON data to intermediate storage, and fetch from there in PowerBI?

I am stuck at this point, and any help would be highly appreciated.

[v-shex-msft]

HI @cte_crest,

AFAIK, power bi desktop report file does not have a limitation on data amount, this size limit should more relate to your hard disk storage and system memory. (system memory used to processing and calculation DAX formulas in your report)
Current power bi service has one Gb dataset size limit on publishing report, it can be increased if you upgrade premium license above some level.

What is Power BI Premium?#size-considerations 

Large models in Power BI Premium (preview) 

For large data sources from rest api, I'd like to suggest you store to local file and create a batch script or plan to auto sync to the location database. (e.g: SQL, MySQL, Excel, Access) After these steps, you can simply get data from the synced database without a huge refresh/processing times.

>>Also, is there any alternative way such that I can push my JSON data to intermediate storage, and fetch from there in PowerBI?

I do not so recommend you try this method if you not familiar with programming languages for API development.

You need to process a program to pull data from your rest API and cache to a temp file(due to data amount, I do not so recommend you cache whole records and processing these in system memory) and get the last records from power bi push dataset compare with cached temp file records to filter and push lastest records. (your API does not contain incremental refresh feature do you need to manually compare with records)

Import JSON Data in Excel 2016 or 2019 or Office 365 using a Get & Transform Query 

Power BI REST APIs / Push Datasets 

Push Datasets - Datasets GetTables 

Push Datasets - Datasets PostRows 

Regards,

Xiaoxin Sheng

[cte_crest]

@v-shex-msft 

Thank you very much for your reply. 

So, as I understood, one possible approach is to store the data from the API to the database. So, in this case, are there any limits as to what maximum data can I import from Database at a time through direct query and import mode?

So, the API I use has Date Range Parameters, Start Date and End Date. But, it is not a Database. So, in this case, would it be possible to create a custom connector and implement incremental in it? I am not sure if an incremental refresh is even possible with custom APIs.

The help is highly appreciated.

[v-shex-msft]

Hi @cte_crest,

#1, You can refer to the below document to know more about these limitations:

Power BI REST API limitations 

#2, I'd recommend you take a look at the incremental refresh feature:

Incremental refresh in Power BI 

Regards,

Xiaoxin Sheng