-3

I have an application which records the company man hours of employees and other related details. Because it gets updated everyday or when a new employee is added, it tends to lead to a very large data set. To analyze this data, I'm planning on using Power BI.

The issue that I'm running into is that the REST API is only returning the first 4000 records, but my data set is larger than that.

My idea is to store all the records in a local folder and connect that folder to Power BI. Using the REST API I will just take the records that have been updated and merge/override them with the rows which are there in the folder using Merge functionality in Power BI. (In Power BI we can merge two tables with same schema and remove duplicates automatically)

Can anyone give me some example as how can I achieve this? (as on API for getting only the updated data)

Or is there is any other solution which better fits my problem?

Thanks in advance.

Improve this question
5
  • 4
    You haven't provided any information on the application and its REST API, so we can't really help.
    – Dan Wilson
    Nov 18, 2019 at 17:02
  • Well lets say its a .Net application with defined REST Apis. I dont see any need of application. i am more looking in terms of what could be solution for this scenario.
    – Ankit Kumar
    Nov 18, 2019 at 19:03
  • 3
    Does your rest API provide pagination? I.e. 4000 records at a time, but offsetting the start value. That's typically how APIs work with large data sets.
    – Berin Loritsch
    Nov 26, 2019 at 15:20
  • The REST API should have a mechanism to paginate. Your first call will contain the first 4000 records with some metadata info like first recordid and last recordid. Using the last record id, you can further make another call to get the next set of 4000 records. If the API does not give you this info, then it is probably poorly designed/implemented. REST APIs are ideally meant for small-sized frequently accessed data. If you are accessing only once or few times a day, it is better the API provider gives you batch synchronization options (a csv file that you can download)
    – Thyag
    Nov 27, 2019 at 0:59
  • @AnkitKumar, is it a 3rd party API? If it is, is the 4000 record limit just a limitation of the API?
    – Keagan Ladds
    Nov 27, 2019 at 13:48

1 Answer 1

Reset to default
-1

Either you need to modify you REST API to deliver more than 4000 records. If you can't do that (e.g. it's a third party server/API), you need to make your own form of pagination. E.g. you can get the first 4000 records, get the last id and in the next request ask for records with id larger than the last id. Or by date/time, so ask for records within a time slot, and then do multiple requests. In case the API does not have this kind of pagination, you can perhaps ask for only deleted or new records, and this way get what you need. It's difficult to give a precise answer when we do not have any information/specification/documentation for the API.

Improve this answer
1
  • 3
    Thats not an answer. The question isn't even answerable, as you've said.
    – marstato
    Nov 27, 2019 at 8:24

Not the answer you're looking for? Browse other questions tagged or ask your own question.