All,
As requested I've included the code that I use to ingest Tessitura Data Directly into R.
This assumes that you have published a View or Table in such a way that you can see the view using odbc and your windows login credentials.
I'm trying to provide lots of comments below. At some point, I will get this into a bitbucket repository.
----------------------------------------------------
------
title: "Getting Tessitura data into R"
author: "Tom Brown"
date: 'August 17, 2017 - '
params:
db_database: impresario
db_server: tessi-db-server #The Name of your database server
db_timezone: America/New_York
---
```{r setup, include=FALSE}
# Installing Libraries. You only need to run each line once
# uncomment by removing the # from the three lines below.
#install.packages('tidyverse')
#install.packages('DBI')
#install.packages('odbc')
# Re-Comment the above 3 lines with a '#' once libraries are installed.
#Load Libraries into memory each time you start R
library(tidyverse) #Load the tidiverse
library(DBI) #Genaric tidyverse database
librarylibrary(odbc) #ODBC Driver Library
#setup Tessi database Connection using server listed in params
# {SQL Server Native Client 11.0} odbc driver
db <- DBI::dbConnect(odbc::odbc(),
driver = "{SQL Server Native Client 11.0}",
server = params$db_server,
database = params$db_database,
Trusted_Connection = "yes",
timezone = params$db_timezone)
# {SQL Server Native Client 11.0} odbc driverdb <- DBI::dbConnect(odbc::odbc(), driver = "{SQL Server Native Client 11.0}", server = params$db_server, database = params$db_database, Trusted_Connection = "yes", timezone = params$db_timezone)
db <- DBI::dbConnect(odbc::odbc(), driver = "{SQL Server Native Client 11.0}", server = params$db_server, database = params$db_database, Trusted_Connection = "yes", timezone = params$db_timezone)
driver = "{SQL Server Native Client 11.0}", server = params$db_server, database = params$db_database, Trusted_Connection = "yes", timezone = params$db_timezone)
server = params$db_server, database = params$db_database, Trusted_Connection = "yes", timezone = params$db_timezone)
database = params$db_database, Trusted_Connection = "yes", timezone = params$db_timezone)
Trusted_Connection = "yes", timezone = params$db_timezone)
# This is the rmarkdown version of the database connection chunkknitr::opts_chunk$set(connection = db)```
```{sql today_orders, include=FALSE, output.var='today_orders', cache=TRUE}select *from t_order as tor with (nolock)where -- We actually touch some very old orders each year during merges. tor.order_dt > = DATEADD(day, -1, GETDATE())```
select *from t_order as tor with (nolock)where -- We actually touch some very old orders each year during merges. tor.order_dt > = DATEADD(day, -1, GETDATE())```
from t_order as tor with (nolock)
where tor.order_dt > = DATEADD(day, -1, GETDATE())```
```{r view data,}View(today_orders)
View(today_orders)```