Optimization of Rest API json result

[amilpbi]

Beginner to Power BI,
We have client's API that generates ~2-5 million highly dimensional records everyday that they wish to visualise, 
I was able to put the api into power bi and get the data but the data becomes way too big to do any power query operation to optimize the table once api loads into the table. 
Is there any way to preprocess the api so that we can design a better data model before loading using proper aggregation and Star schema so that we are able to visualize the data efficiently. We were given just the url, tokens and dynamic key generator to work with. Client may not make changes to their api. 
Also, Client only has PPU license so using Data Factory or real time intelligence or any other Fabric product may be out of project's entire budget. 

Is there any way to solve this?

[Anonymous · Accepted solution]

Thanks for the reply from lbendlin , please allow me to provide another insight:

Hi, @amilpbi 
 

Firstly, you can implement filtering and pagination when calling the API, which will effectively control and limit the amount of data returned.

Below are relevant screenshots from the documentation:

For further details, please refer to:

Make API calls | Cloudflare Fundamentals docs
REST API Design: Filtering, Sorting, and Pagination | Moesif Blog

Secondly, you can pre-filter the data in Power BI using Power Query, and then adjust the model in the Desktop application.

Here are the relevant screenshots from the documentation:

For further details, please refer to:

Optimization guide for Power BI - Power BI | Microsoft Learn

Best Regards,

Leroy Lu

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

[lbendlin]

highly dimensional records 

What are your plans?  Completely denormalize?  Or normalize into dimension and fact tables?

Have you considered loading the json into a database that can handle hierarchical data , and query that database?

[amilpbi]

normalise into Facts and Dimension tables in a Star schema and make aggregations for efficient and optimal data model. 
How to even do that? Where to Start?

[lbendlin]

You start by clearly defining your business goals ( write them down if you can). Then you extract the necessary data points into their respective tables.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...