Technical Tips and Training

NSSP–ESSENCE Tips and Training

• Free-text Coding Article Series—how to write ESSENCE free-text queries:

Part 1. Wildcards
Part 2. Underscores_and Brackets [ ]
Part 3. Inclusionary Terms
Part 4. Exclusion Terms and Parentheses
Part 5. A “Starter” Fall-related Injury Query and Examples of Complex Queries
Part 6. Wrapping Things Up

• ESSENCE Online Trainingexternal icon
  Six self-paced modules instruct and assess your understanding of concepts being presented. Topics include overviews of ESSENCE, syndromic surveillance, basic and advanced ESSENCE system components, ESSENCE Chief Complaint Processor, and ESSENCE alerting algorithms. Learn how to perform routine tasks such as create queries and analyze data using multiple visualization types.
• ESSENCE Training on Using Queriesexternal icon, webinar led by Wayne Loschen of Johns Hopkins University Applied Physics Laboratory.
• NSSP ESSENCE Training at PHI Conference pdf icon[PDF]

RStudio Tips

---

How to Do Queries in RStudio Pro

The Adminer tool was retired on October 12, 2020.

No worries! You can do all your SQL queries on the BioSense Platform by using RStudio Pro. Here are some easy steps to follow and code examples for querying data on the BioSense Platform’s Datamart.

First, log into your RStudio Pro account using your Access & Management Center (AMC) username and password.

 

Next, install and load the packages, also called libraries, that you need. These tell RStudio Pro how to connect to the Analytic Datamart. To do this, copy and paste the next two lines into your code.

library(DBI)

library(odbc)

To run pieces of code, you can highlight the code in your R script and use Ctrl + Enter or use the Run button:

Now you can connect to the Analytic Datamart. This step uses the username and password that you’re already using to connect to RStudio Pro, so there’s no need to re-enter them. You can even store this connection as a variable to reuse.

Copy and paste the line below to store a database connection directly to the BioSense Platform database on the Analytic Datamart.

datamart <- dbConnect(odbc::odbc(), dsn = “BioSense_Platform”)

You can confirm that you’re connected to the datamart by looking at the Environment pane on the right side of your screen.

The last step is to write your query and send it to the database. To do this, copy and paste the code below to R:

              table <- dbGetQuery(datamart, “SELECT * FROM Except_Reasons”))

This example pulls information from the Except Reasons table, but you can insert any query you’d like inside the quotation marks. Once the query is complete, the table of query results will appear in your Environment pane.

And that’s it! The code shown below is enough to get you started reviewing your data in RStudio Pro. After you copy and paste this code into RStudio, delete and retype all double-quotes (“).

library(DBI)

library(odbc)

datamart <- dbConnect(odbc::odbc(), dsn = “BioSense_Platform”)

table <- dbGetQuery(datamart, “SELECT * FROM Except_Reasons”)

write.csv(table, file =”TableName.csv”, row.names = FALSE)

The write.csv command will write the results to a .csv file that can be exported and opened as an Excel file.

TIP: After you copy and paste this code into RStudio, delete and retype all double-quotes (“).

Original article in Data Quality Corner: https://www.cdc.gov/nssp/data-quality-corner/sql-queries-rstudio-pro.html

Sharing a Project in RStudio (on the new server)

RStudio Pro lets you access and analyze SQL data stored on the BioSense Platform. You can use RStudio to verify data in the BioSense Platform archive (raw, processed, and exceptions tables), confirm information in your Master Facility Table (MFT), and view and query multiple databases and data tables. You can also create and share projects quite easily. Here’s how:

1. First, you’ll need to create a new project in a new session in your home directory.
2. In the top right corner, click the down arrow next to the new project name and select Share Project:

3. Type the usernames of each person you want to share the project with. Then click the Add button.

4. Once you’ve added everyone with whom you want to share the project, click OK at the bottom of the dialog box.

5. You will receive the error message shown below. Take note of the file name at the end of the path. You’ll need to adjust the permissions on this file in the next step. Click OK.

6. In the left pane of RStudio, click on the Terminal tab. We are going to list the permissions for the files in the shared-projects metadata directory. Update the permissions on the new project that was shared. And then list the permissions again to verify that they were updated correctly.
   1. List the permissions by entering the ls -al /var/lib/rstudio-server/shared-storage/shared-projects/ command and pressing enter.
   2. Update the permissions on the new shared project by entering chmod g+rw /var/lib/rstudio-server/shared-storage/shared-projects/projectfile.proj. Make sure to change projectfile.proj to be the project file in the error message in Step 5. In this example, it is 9fdcf8e0ee7e7.proj.
   3. List the permissions to make sure the changes were successful, ls -al /var/lib/rstudio-server/shared-storage/shared-projects/.

7. The project is now accessible to everyone it was shared with.