I'm building a shiny app using flexdashboard that fetches data from a company data base.
The app will be running on shiny server (open source edition) from linux.
I would like the app to get the latest data whenever it is invoked by a user.
I expect only a handfull of users at a time.
I was wondering if the community could offer some advice on how to best implement this in flexdashboard.
I was thinking to fetch the data in the global chunk, but I'am unsure if this will ensure that I always get the latest data. Is it better to supply a fetch data button?
As a general rule, I would think that it is better to implement logic of getting data out of the database directly in the format your users need. For example, if you want to plot some data and show it to the user, then clicking the button "Plot" will trigger the function that gets data from the database, transforms it there (e.g., using dplyr to generate SQL statements) and gets out only the data that is required for plotting. This way you don't need to think about data caching in the app. After all, databases are designed specifically for this task, so it shouldn't be a problem.
But, of course, your specific use-case can be different. For example, if users only need a small amount of data and data doesn't change much (or users don't care enough if they see stale data) then approach with caching on start might be reasonable.
In principle you can fetch data from database either:
outside the main ui/server block, and the data gets updated only when the app starts. This leads to fast loading times and (very) stale data or
inside the server function - say in renderPlot() if the end result is a drawn plot. This leads to slower loading times and fresh data; you do not really need a special fetch data button unless your data is very volatile
It is best to try both, and see what works best in your specific context (not two apps are exactly alike).
I have been incapacitated by the flu the last week - so I apologize for not responding to you suggestions before now.
Thank you so much every-body.
I'm up and running with my app using the advice from the community.
I have adopted the DBI / dbplyr / dplyr approach.
Even-though this speeds up the query I still have some waiting time before the final data is fetched after filtering server side.
During this waiting period, nothing happens, and I was wondering if there is a way to put a "please wait - fetching data" message or a spinning wheel until data has been fetched?
PS
Another key learning is to not used {r global} chunk option in my flex-dashboard as this simply does not work for me; whatever is put here will not be read again the next time the app is accessed through the web-site.
Hi! So sorry to hear that you been sick, but am glad that it sounds that you are recuperating.
Quick question, is the waiting period caused by having to wait for the database to perform all of the aggregations, or due to amount of data transmitted back to the dashboard? If the later, you may consider to avoid any detail level data sets being pulled back into the dashboard, unless the end-user click "see detail". The goal should be for all parts of the dashboard should be a summarization of the data.
The wait is caused by the data transfer. I do some modelling and aggregating in the app that I do not believe I can do server side (but I certainly will re-check this). While crunching I would like to inform the user that the wait is due to action and not an error, e.g. a message or a status bar.
Cool. I think some of the complex stuff can be pushed back to the server, such as predictions and plot calculations, by using things like the dbplot and tidypredict packages, or the principles that those packages use which is to leverage rlang to write the dplyr/tidyeval code on the fly, code that emulates the algorithm inside the DB.