We currently have a Power BI report used as a live dashboard for customer service. The data needs to be fresh to be relevant (number of customers waiting in line etc.), so we have a set a page refresh every 15 seconds. The page contains 12 visuals that are querying the data source using direct query. Historical data is also imported and shown in the report, so this is a composite model. The data source is a REST api, so we are using a Cdata custom connector to connect to it using direct query.
The issue is that the REST api endpoint for the data source is being hammered (2 tv screens x 12 visuals every 15 seconds = close to 2 requests per second) and the third party provider of the api has rate limits in effect that will stop the dashboard from being updated. All of the 12 visuals are calling the exact same api endpoint (www.exampleservice.com/export?date=today).
We have considered several approaches to reduce the load on the data source:
1. Use a custom solution to call the api endpoint every 15 seconds, store the results somewhere and point Power BI to this temporary storage instead. This would reduce the load on the source from 24 requests per 15 seconds to 1 request per 15 seconds. We would ideally like to store the result as raw json and not in a database, so we won't have to update the schema if the provider changes it. We are considering using an Azure storage account for storing the raw json, which is about 50 kb in size.
2. Use a custom solution to call the api endpoint every 15 seconds, store the results in a database and point Power BI to this database instead. Similar to above. We would need to update the schema if that changes.
3. Enable caching directly in the custom connector. The custom connector from Cdata supports caching data and use the gateway PCs local storage for that. This helps, but it seems to still consider every 12 visuals as separate queries, so the load is not reduced sufficiently to get below the rate limit.
4. Use Azure Analysis Services or similar to load the data. We are not familiar with that (we only use Power BI dataflows at the moment), so would have to learn.
5. Use a custom solution to call the api endpoint every 15 seconds, then push the data to a Power BI push dataset. However, since direct query to Power BI datasets does not support automatic page refresh, we are restricted to using the data in a Power BI dashboard. This is not sufficient, as we need the layout flexibility of a regular Power BI report.
Would really appreciate any advice or best practice for building real time dashboards in Power BI against api endpoints!
