I am a junior data analyst at a finance company. We are only 2 in the IT department and both at the start of our carer, that is why I am asking here for advice. In the past, I have created some small app with the help of R and shiny and they were very well accepted. That's also why they trusted me with a new project, that is a bit more challenging. The project is to create an app that shows the progress of the debt collection department. The first page would be a simple overview (table probably) of all clients the goal for the moth the realized amount and some other statistics something like this:
And when one would click on the client it would open another page with detailed data about the collection of that client with advanced statistics and graphs.
My main question is about how to access the data. What are good practices here?
Do I just connect shiny to the main database write a bunch of SQL statements and process and store everything in the data frames and variables inside r? Maybe I am wrong here, but one of the concerns is that this way would “jam” the connection to the database since shiny is reactive?
Is it a better practice to create views on the database that will store the pre-processed data and that way the number of SQL queries of R in the database would be minimal?
The optimal way would be that when a new client is added in the database, it would be automatically in the shiny app so that the clients would not be hard coded.
Are there any other options? How would you guys address this issue?
Every advice is welcome! And thank you!