Using the utilsFFI R package

Getting started

Installation

Overview

The utilsFFI package is a work in progress R package built by NPS users of FFI (FEAT/FIREMON Integrated) data. Currently this package only has an importData() function that imports all tables in the “dbo” schema from a local instance of an FFI database in SQL Server Management Studio (SSMS). Eventually, this package will allow a user with appropriate access the FFI server to download real-time data tables into R.

In time, more features will be added, such as functions that join the database tables to make them analysis- ready and allow for easy querying and summarizing. This documentation will be updated as more features are added.

Step 1. Install SQL Server Management Studio

Instructions for installing SQL Server Management Studio (SSMS) are currently being developed. Once those are ready, they will be linked here.

Step 2. Restore database from .bak file in SSMS

To restore a database from a .bak file in SSMS, you can either restore through the file menu following the screencast or run the SQL code in SSMS below.

File Menu option:

SQL Code option: The SQL code below will restore a .bak file to a database named “FFI_RA_AGFO”. To use the code below, you only need to change “FFI_RA_AGFO” to match the name of your database (i.e., whatever precedes the .bak in the file name), and change the C:\temp\path to the path where your .bak file lives. Note that files on OneDrive often cause issues. It’s best to restore from locations directly on your C:\ drive or a server.

-- Variables to declare and modify for different database name and file locations
DECLARE @DBNAME NVARCHAR(MAX) = 'FFI_RA_AGFO'
DECLARE @FilePathOrig NVARCHAR(MAX) = 'C:\\temp\\'
DECLARE @FilePathNew NVARCHAR(MAX) = 'C:\\Program Files\\Microsoft SQL Server\\MSSQL15.SQLEXPRESS\\MSSQL\\DATA\\';
DECLARE @SQL NVARCHAR(MAX);

-- Run remaining query to restore database
--USE [master]

DECLARE @DATA NVARCHAR(MAX) = @FilePathNew + @DBNAME + '.mdf';
DECLARE @LOG NVARCHAR(MAX) = @FilePathNew + @DBNAME + '.ldf';
DECLARE @DBFULLPATH NVARCHAR(MAX) = @FilePathOrig + @DBNAME + '.bak';
DECLARE @DBLOG NVARCHAR(MAX) = @DBNAME + '_log';

USE[master]

SET @SQL =
  ' RESTORE DATABASE ['+@DBNAME+'] FROM DISK = '''+@DBFULLPATH+''' WITH FILE = 1, MOVE '''+@DBNAME+''' TO '''+@DATA+''',
    MOVE '''+@DBLOG+''' TO '''+@LOG+''',
    NOUNLOAD, STATS = 5'
EXEC(@SQL)
GO

Step 3. Install R, RStudio, and RTools44 in Software Center

Note that RTools is needed to install R packages from GitHub, and it only works with R versions 4.4.x. While R 4.5 is available on Software Center, the matching RTools45 isn’t available yet. Until that changes, link RStudio to the latest version of R 4.4 (I’m currently using R 4.4.3).

Troubleshooting Build Tools

Unfortunately Software Center installs RTools44 in C:/Program Files/, not C:/, which is where RStudio looks for it by default. The following code helps RStudio find RTools. You may occasionally have to rerun this code (except for the usethis line), so keep it handy. You know when you have to rerun the code when you try to rebuild a package, and a window pops up to ask if you want to install missing build files.

First install the usethis package if you don’t have it installed. Then open the .Renviron using the code chunk below.

install.packages('usethis')
usethis::edit_r_environ()

Next set .Renviron to find RTools on opening by opening the pasting the code below into that file, and saving it. Now close/reopen RStudio, and you should be all set.

Sys.setenv(PATH = paste("C:\\PROGRA~1\\Rtools44\\bin", Sys.getenv("PATH"), sep=";"))
Sys.setenv(BINPREF = "C:\\PROGRA~1\\Rtools44\\mingw_$(WIN)\\bin\\")

Step 4. Install devtools package in R: 

install.packages('devtools')

Step 5. Set up GitHub account if a new user

To install R packages using devtools, you need to have a GitHub user account and active token. Instructions for setting up a GitHub account and connecting it to RStudio are on the IMD Advanced R training website from 2022. Go to Day 4: Version Control > Git and RStudio and complete the steps described. There’s a lot of other good info on using GitHub in other tabs too.

Step 6. Install utilsFFI from GitHub

Note that whenever the utilsFFI package is updated, you can rerun this code to install the latest version.

library(devtools)
install_github("KateMMiller/utilsFFI")

If you don’t want to have a GitHub account, you can also install packages from GitHub by downloading a .zip file of the code (often called forking a repo), then running the code below.

install.packages("utilsFFI.zip")
Troubleshooting GitHub package installation

If you’re unable to install the R package via GitHub (often an error about permission being denied, download the following script from my OneDrive and open it in R: fix_TLS_inspection.R. Download by left clicking on the link, then click in the download arrow in the top left of the screen. Save it to your machine, then open it in RStudio.

Once this script is open in R Studio, press Control + A to select all of the code. Then Control + Enter to run all of the code. Assuming you don’t return any errors, you should be able to install from GitHub. Now try to reinstall utilsFFI. If you’re still running into issues, it could be that devtools is missing some package dependencies, which the error message will often mention. Install any missing packages and then try installing utilsFFI again. If you’re still not successful, send Kate Miller () a screenshot of the error and she’ll help you troubleshoot.

Step 7. Load utilsFFI R package 

library(utilsFFI)

Step 8. Import data

Note that R is not able to connect to files on Sharepoint or MS Teams (Teams also stores all files on Sharepoint). That means you need to store data package files on your local machine or on a server. The default option for importing data will add the data package views (i.e., flatfiles) to an environment called FFI_tables to your Environment work space (i.e. Environment tab in top right panel).

library(utilsFFI)
importData(dbname = "FFI_RA_AGFO")

Optional: Export Data to a zip file

If you want to export all the tables into a zip file of csvs, you can run the exportData() function like below.

Export data using default settings, which will save a zip file named FFI_tables_YYYYMMDD.zip with the YYYYMMDD being a datestamp for when the zip was created, and saved to your working directory.

exportData()

The better approach is to name the zip file the same as the SSMS database that you originally imported. The code below exports a zip named FFI_RA_AGFO_YYYYMMDD.zip to working directory.

exportData(zip_name = "FFI_RA_AGFO")

Export data to a named zip file and a specific directory.

exportData(zip_name = "FFI_RA_AGFO", path = "C:/temp")

Step 9. Play with the data

The functions in the utilsFFI package are designed to work with the views, and are the best way to interact with the data to query by park, site, site type, year, parameter, etc. However, if you want to view the raw data, and you imported the data into the FFI_tables environment, you can access them with the code below:

# See list of the views
names(FFI_tables)

# View one the MacroPlot table
View(FFI_tables$MacroPlot)

# Assign the macroplot table to the object macro, which now behaves like a data.frame.
macro <- FFI_tables$MacroPlot

Getting help

Getting (and improving) help

The functions in utilsFFI have help documentation like any R package. To view the help, you can go to the Packages tab and click on utilsFFI (see below). That will show you all the functions in the package. Clicking on individual functions will take you to the help documentation for that function.

You can also see the help of a function by running, for example: 

?importData

If utilsFFI isn’t loaded yet, you’d run: 

?utilsFFI::importData

Each function’s help includes a Description, Usage (i.e. function arguments and their defaults), Argument options/definitions, and several examples showing how the function can be used.

This is where you come in! If you notice typos or can think of better descriptions, examples, error messages, etc., please send them my way! After we’re more comfortable with R packages and get versed on GitHub, you’ll be able to make those changes directly in the package. For now, you can just send me your suggestions and I’ll make the changes.

Finally, if you ever want to peak under the hood at the function, you can view it several ways.
  1. Keep F2 key pressed and click on the function name in R. This trick works for many but not all functions in R.

  2. View code in the GitHub katemmiller/utilsFFI repo. The functions are in the R folder.