Forum Discussion
API connection, time series analysis issue
Choose a balance between reducing the strain on the source system (pull only the data that you need) and your maintenance effort (DB management) . Instead of a db you can also consider CSV or Parquet files for your storage.
Because I need up to date data + historical data, I guess I need to make a database and to schedule api calls each day so the dataset is up to date.
This is my line of thinking:
1. Setup Azure SQL database
2. Scrypt makes api calls each day and fills the database with new data every day.
3. PowerBI connects to Azure SQL database
4. Scheduled refresh in PBI Service.
Did I miss something? Does this setup make any sence? Any alternative ideas?
Does the data behind the API change after the fact? For example if you pull yesterday's data, will that then be it or do you have to repull it after some time to catch the changes?
Good question.
In perfect world the data shouldn't change. But some changes due to errors are possible (manual changes by admin to the original data due to human error or similar).. So it's possible that it will be needed to repull the data, let's say once a week or once a month?
Any ideas how to address the possible issue?
I would recommend you consider setting up incremental refresh. Then you can decide if you want to schedule the refresh automatically, or if you want to manually update certain partitions.
The API query may not fold, so the performance improvements will be minimal. But the refresh flexibility may be worth it.
So you don't recommend makind some database in between powerbi and api? Will it be possible tu pull years of data with incremental refresh? Where is all the data stored that way?
A side question: If we plan to make a future proof solution, with possibility that data from other APIs is loaded in the future to the model, would you then recomend making a database? So to make a database structure, map the corresponding fields in api and fill the database, possibly from 2 or more different sources and make reports as it's one dataset. Hope I made this part clear..