Technical Tips and Training

Welcome to the NSSP Technical Tips page, where you’ll find helpful tips, tricks, and training for working with BioSense Platform tools such as ESSENCE, Access & Management Center, and RStudio Professional.

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

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.

Sign in to RStudio

 

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:

Run image

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.

Environment Pane

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.

Environment Page Query

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)

Sharing a Project in RStudio

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:
RStudio right click window
  1. Type the usernames of each person you want to share the project with. Then click the Add button.
RStudio options window
  1. Once you’ve added everyone with whom you want to share the project, click OK at the bottom of the dialog box.
RStudio options prompt
  1. 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.
RStudio prompt
  1. 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/.
RStudio Terminal
  1. The project is now accessible to everyone it was shared with.
Page last reviewed: April 15, 2021