0

Below is the scenario.

Scenario:
I am building ShinyDashBoard that will allow end users to track system usability at specific location.

For this I need two tables that are in Oracle Database (As explained below). Also,I have one csv file that I would like to join after data is queried. This csv file contains data regarding System location. The purpose of joining csv file is to retrieve data of all systems located at specific location. This csv file consist of System, Location columns. From end-user perspective they would like to see where all systems are located, and at what frequency are those system being used ?

Table1 consists of User login information. It consist of UserName, UserLogin Date columns

Table2 consist of data regarding which user is using which system.(This table does not contain data of all systems, it only contains data where the system is being currently used). This table consist of UserName and System column.

Question #1: My question is how to approach in building the App ? I read this post where author suggest it's possible to connect to Oracle Database in R-Studio using RODBC package. However, I am not sure where should I put the code for connecting to Oracle database ? Does connection code goes in Global environment ? or does it go in Sever part of shiny App ?

Question #2: How to join external data ? I know I can perform Join on my Table1 and Table2 on "UserName" column. Then I can perform Join on "System" column that is in csv file.Where should the code for Join go ?

Question #3: Regarding username and password part of RODBC package.Since, end-users will be different persons using the app. What should I put for username and password when deploying the application?

I know this question covers various areas, if possible provide basic syntax of how would basic ShinyDashBoard app look like, which can connect to Oracle Database, and query the data from two table and perform join with external csv file,and render data on ShinyDashboard in form of visualization.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
biggboss2019
  • 220
  • 3
  • 8
  • 30
  • 1
    You can use `DBI` with `odbc` to connect to the database; an alternative it the `RODBC` package. While I prefer the former, I'm confident others prefer the latter. There are many tutorials on how to connect in R, starting with https://db.rstudio.com/ (and still many many others). "Joining data" is the same whether externally-queried or not: once in R, it does not matter; one such question is https://stackoverflow.com/q/1299871. – r2evans Aug 04 '19 at 02:47
  • There are plenty of tutorials that combine shiny and db connections too. – r2evans Aug 04 '19 at 02:48
  • @r2evans.. Thanks! Would it be possible to share link of few tutorials ? I couldn't find any on YouTube. Thanks in advance! – biggboss2019 Aug 04 '19 at 13:07
  • 1
    I gave two in the first comment, I'd start there. Otherwise, google, rstudio, and r-bloggers typically have good blogs/tutorials/entries. – r2evans Aug 04 '19 at 15:00

1 Answers1

0

If it is Oracle you will need RJDBC package. https://cran.r-project.org/web/packages/RJDBC/index.html

Darren
  • 83
  • 7