We use a business app that tracks tickets, projects, and time entries. It has horrible dashboards, but the database with all the info is accessible via REST API. So, I have built several dashboards to display different types of data, and then we display these all over our workplace for visibility. It's great.
But the business app has limits on how many API calls we can do every hour. Which is fine, until I start creating more and more and more of these Retool dashboards. Each dashboard is pulling all the same data from the business app, so that each dashboard can have its own local copy of the data.
Does anyone have any suggestions on how I could decrease the load on the business app API? The built-in Retool database is far too small for me to be able to use that.
I feel like going and setting up another database, like Amazon Redshift or the like, would take a lot of time for me to learn and implement and integrate into all the existing dashboards. Is that the only option? Does anyone have any suggestions? I've been thinking about this for a while, and can't seem to come up with any good ideas.
Depending on how often you need the data to refresh, have you looked at setting caching times in your queries or code blocks?
1 Like
I have a JavaScript query that controls when all the Rest Api queries are run. Some, like querying for time entries, are run every five minutes, because this data changes often. Some, like querying companies, are done once a day. I save the results in a variable, rather than letting the query cache. Part of the reason for using variables is the queries have to be run multiple times to get all the paged data, so I just add the results to the variable.
I think my problem is that every one of our employees needs to have an instance of the Dashboard at their desk, plus all the dashboards that are displayed around the workplace. Each dashboard is pulling the same data. I wish I could make the data accessible in one location, but I don’t know how to do that.
@nl-setech this is a tough problem!
Retool doesn't have built in functionality that will do this for you, you'd have to consolidate the data somewhere that has less restrictions.
The easiest way I can think of to do this would be to use a Retool workflow and some kind of blob storage (like Retool Storage, AWS S3, Azure Blob, etc).
You would set up the workflow to run at whatever cadence you need to refresh the data, have the workflow query the business app, store the data in blob storage, and then update all of your apps to load the data from blob storage instead of from the business app.
This is similar to what you suggested, you'll still need some kind of other datastore, but it would be simpler than a second database like Redshift.
This assumes that all of your apps need exactly the same data.
Good luck!
2 Likes
One solution could be using the built-in caching in the retool queries, so this way you can make a lot of API requests, you need to configure that the cache is shared by all users. This solution should work for some good extend, tried it myself!
The other solution is to use a cloud DB, but it should be something simple and easy to access. AWS is an advanced solution that might be too much to maintain. I personally suggest Supabase which offers a dedicated Postgres DB and this can be easily connected to Retool.
2 Likes
ChatGPT suggested that I use an external database, and do all of my data processing there - then only query the datasets that are needed to populate the charts and tables. Currently, I am pulling all the data and doing all the processing in Retool. It's a major architecture overall, but I think it's the only way to get the performance I need.
1 Like