Best approach API calls

[Tinus1905]

I'm using PowerBi to get files from the API. The total amount of records are more than 500.000.

So to speed up the progress I made an manual export of the site from beginning date -> yesterday. The export is on our database and with PowerBi I have a table with this export and a table with an "live" API from today.

So far so good but in a few weeks the "live" API table will fill up again with a lot of data and it will be slow.

The solution I had in mind:

1: The "live" API table is only the data of this week and the other table is all the data before this week.

How can I make a table with all the data before this week without this table to call the API over and over again. So the "live" API calls for this week must push the data into the "historical" table.

If there is a better/other solution for this, what should that solution be?

[AnalyticsWizard]

@Tinus1905 

The solution you're considering is to partition your data into two sets: historical data (data before this week) and live data (data from this week). This is a common approach for managing large datasets and is known as incremental refresh. In Power BI, incremental refresh is designed to load only the most recent data that needs to be refreshed.

Here’s how you can implement your solution:

1. Partition Your Data: Set up your historical data table to pull from your database, which contains records until yesterday. Then, set up your live API table to only pull records from today onwards.

2. Incremental Refresh: Power BI Pro and Premium allow you to set up incremental refresh policies on your datasets. You can define a policy where the historical data does not need to refresh, while the live API data refreshes daily.

Here’s the conceptual way to do it:

- In Power BI Desktop, go to the table properties for your historical data table.
- Set a filter on the table for dates less than the start of this week.
- In your dataset settings on the Power BI Service, set an incremental refresh policy where data from the last N days is refreshed. You can define N to suit how frequently your live API table updates (e.g., 7 days for a week).

3. Automate Data Movement: Automate the movement of data from the live API table to the historical table in your database, not in Power BI. Use scheduled scripts or database jobs (SQL Server Agent jobs if you're using SQL Server) to move the data. Once the data is moved to the historical table, Power BI will not see it in the live API table the next time it refreshes, so it naturally becomes part of the historical data set.

4. Optimize Refresh Schedule: In Power BI Service, schedule the refresh for the live API table to occur as frequently as needed (daily or several times per day). Make sure this aligns with your data movement schedule.

5. Use Query Parameters: If you’re using query parameters to filter API requests by date, make sure they are dynamic and adjust according to the current date.

A better solution might be to handle the data partitioning logic on the server side, not through Power BI. This way, Power BI always connects to two separate tables/views: one for historical data and another for live data. The database server would be responsible for moving data from the live table to the historical table, ensuring that Power BI’s live data table always contains only current week’s data.

Implementing the above strategy should ensure that your Power BI dataset remains performant by not fetching more data than necessary during each refresh.

Keep in mind that with Power BI Pro, you are limited in the number of refreshes per day, so align your strategy accordingly.

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

[Tinus1905]

@AnalyticsWizard I think its better that I store the API data on our database. After that I use PowerBi. 

What kind of free tool can I use to get the API data into our database? The API data has to be updated a couple of times per day and only calls the newest data (data is over the 500.000 records), all that has to be stored in the database.    

[EmanuelTavares]

Hi @Tinus1905 

Having two tables to separate historical data from recent makes no sense to me.

I suggest you create an incremental ETL process and read all data from a table. If can set the process frequency to update the database many times daily.

You also can run this process using a serverless service, such as Azure Functions or AWS Lambda.

Regards.

Emanuel