Using utilsFFI R pkg.

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 has functions to import all tables in the “dbo” schema from a local instance of an FFI database in SQL Server Management Studio (SSMS), export those tables to a zip file of csvs for more widespread use, and a function that joins tables into analysis-ready views. Eventually, this package will allow a user with appropriate access the FFI server to download real-time data tables into R, and functions for querying and summarizing the analysis-ready views. 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. Until then, the best directions currently (though a few years out of date and only accessible by DOI users) can be found here: SOP_SQLServer_Setup.docx.

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 as of 8/20/25. 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.

RTools step 1. Install the usethis package if you don’t already have it installed.

install.packages('usethis')

RTools step 2. Open the .Renviron file by running the line of code below.

usethis::edit_r_environ()

RTools step 3. Copy the following text and paste it into the .Renviron file, save, then close/reopen RStudio. You don’t need to run anything for this step. Copy/paste are all you need to do.

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')

Once you’ve successfully completed Steps 1-4, you should only have to repeat them if you have a new computer or if you move to R 4.5 or higher (note: R 4.5 will require installing RTools45).

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

You must have a GitHub login and be logged into GitHub to install packages from GitHub and for this step to work

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

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

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 (kathryn_miller@nps.gov) a screenshot of the error and she’ll help you troubleshoot.

Step 7. Load utilsFFI R package

library(utilsFFI)

Import/Export Functions

Import Data

Import FFI database tables

Once you complete the installation steps and can load the utilsFFI package, you can 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 FFI database tables to an environment called FFI_tables in your Environment work space (i.e. Environment tab in top right panel).

Import data using type = ‘local’, which is a database restored in SSMS called FFI_RA_AGFO.

library(utilsFFI)
importData(type = 'local', dbname = "FFI_RA_AGFO")
# equivalent to code below because type = 'local' is default
importData(dbname = "FFI_RA_AGFO")

Import zip file of exported FFI tables that are csvs.

importData(type = 'csv', import_path = "C:/temp/FFI_table_export_20250624.zip")

Explore the raw data

The functions in the utilsFFI package are designed to work with flattened views of the FFI tables, rather than the raw tables themselves, 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 look at the raw data tables, and you imported the data into the FFI_tables environment, you can access them with the code below:

# See list of the tables
sort(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

Export Data

Export tables to zip

If you want to export all the tables into a zip file of csvs, you can run the exportData() function like below. 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.

Export data using default settings

. This 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()

Export 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")

Make Views

Make the views

This function generates flattened views of FFI data by protocol to make them stand-alone, analysis-ready using the code below. The views join individual tables in the database together and only keep the most important columns. Views were designed using FFI data from NGPN parks and may need to be tweaked for other parks/projects. If export_views = TRUE, will export the views as a zip file. Next I’ll build an importViews() function that will allow the user to import these views into R. Functions for querying and summarizing FFI data in this package will then use the views.

Make views using default settings

makeViews()

Generate views and export zip to working directory

makeViews(export_views = T)

Generate views and export zip to specified path and name

makeViews(export_views = T, export_path = "C:/temp", zip_name = "FFI_view_export")

Explore the views

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 look at the raw data tables, and you imported the data into the FFI_tables environment, you can access them with the code below:

# See list of the views
sort(names(FFI_views))

## [1] "Cover_Points_metric"      
## [2] "Cover_Species_Composition"
## [3] "Density_Belts_metric"     
## [4] "Disturbance_History"      
## [5] "MacroPlots"               
## [6] "SampleEvents"             
## [7] "Taxa_Table"

# View one the MacroPlot table
View(FFI_views$Cover_Points_metric)

# Assign the Cover_Points_metric view to the object covpts, which now behaves like a data.frame.
covpts <- FFI_views$Cover_Points_metric

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.