In idea-analytics/ideadata: Tools and Utilities for accessing IDEA's Data Warehouse

knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)

The goal of ideadata is to make getting data out of IDEA's data warehouse as simple as possible. In order to do that, ideadata abstracts away from a lot of the scaffolding to make connections to the a given database (calls to DBI or odbc with connection strings and often complicated SQL code) as well as the often complicated SQL code to pull the table, columns, and rows you need to get analyzing.

This vignette takes up the "connect to the database" bit. In order to connect to IDEA's SQL Server instances (yes, it's plural, as there are a number of instance hosting many databases in our warehouse) you need to authenticate (i.e, share your credentials with the database) and you need a method of establishing the connection from your computer (i.e., a driver).

Also we don't want to push up our credentials into our git repository; even though they are private, you risk exposing them to your coworkers. A best practice here is to use an .Renviron file, which typically lives in the user's home director. It saves key-value pairs of variables and values that R loads on start-up and are available to in R environment. Saving your credentials and other data helps grease the skids for ideadata to able to authenticate on your behalf.

One line of code should get you going, but you'll need to have the following available before you start:

1. your IDEA user name
2. your IDEA password
3. A driver description (more on this below)

library(ideadata)

setup_creds()

IF you don't have an .Renviron file in the right place for your OS, setup_creds will create one. It will then prompt your for pieces of information:

1. your IDEA user name
2. your IDEA password
3. you driver description

Since you already got that info ready you can type each in turn when prompted and hit return. If you want to see what exactly has happened you can use the {usethis} package's edit_r_environ() function, which will open up your .Renviron file right in your RStudio source pane.

Where do I find this SQL Server driver description?

The tl;dr version: here

The longer version. The latest general availability ODBC (that's Open Database Connectivity, which you can read more about here driver for SQL Server is Microsoft ODBC Driver 17 for SQL Server. This is your best bet if you are starting off fresh with a new computer. Follow the [directions here]((https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15) for your OS to install the ODBC driver on your computer.

If you use ODBC Driver 17 for SQL Server then all you need to type in at the driver prompt provided by setup_creds is literally that name:

ODBC Driver 17 for SQL Server.

That's it. No quotes. No braces. You can even simply copy and paste into the console pane (i.e., the REPL) in RStudio when prompted after you run setup_creds().

If you have any trouble here, feel free to reach out to Chris or Edison.

idea-analytics/ideadata documentation built on Feb. 1, 2024, 5:40 a.m.