## Looker and Warehouse Usage.r

## ------------------------------------------------------------------------------------ ##
## This file was used to calculate the usage by department
## The majority of this is for Looker usage
##  however, there is a tiny four-line section at the top for warehouse uage
##  although the warehouse starting figures are outdated 
## ------------------------------------------------------------------------------------ ##



## ------------------------------------------------------------------------------------ ##
## This part simply shows some starting metrics by warehouse, and by warehouse purpose
## ------------------------------------------------------------------------------------ ##
    ## Note that this original file is very outdated
    f.snowflake_warehouse_usage <- ingest.p("Snowflake Credit by Warehouse.csv")
    warning("The original 'Snowflake Credit by Warehouse.csv' file is very outdated -- Artur provided newer numbers", call.=FALSE)
    DT.snowflake_wh_usage <- fread(f.snowflake_warehouse_usage, header=TRUE, sep=",")
    DT.snowflake_wh_usage[, sum(Credits), by=Purpose]
## ------------------------------------------------------------------------------------ ##


## ------------------------------------------------------------------------------------ ##
## Everything after here is to track and aggregate Looker usage
## ------------------------------------------------------------------------------------ ##
f.orchard_users <- ingest.p("Looker Users - Users.tsv")
DT.orchard_users <- fread(f.orchard_users)[, list(dept=Team, location=Location, user=Name)]

f.looker_usage <- ingest.p("Looker Usage by User and Week 20140420.csv")
DT.looker_usage <- fread(f.looker_usage) %>% cleanColNamesForSQL_
setnames(DT.looker_usage, c("user_name", "history_created_week", "history_average_runtime", "history_total_query_run_count"), c("user", "week", "runtime_minutes", "number_of_queries"))
DT.looker_usage[, week := as.Date(week)]

known_looker_users <- c('Brett Ingalls', 'Carter Moar', 'Haarthi Sadasivam', 'Ian Ross', 'Joseph Long', 'Kenny Cunanan', 'Leon Yin', 'Lindsey Meyer', 'Nicole Beyer', 'Zachary Michel')

## Merge the two tables
matchKey(DT.looker_usage, DT.orchard_users, key="user", organize=TRUE)
DT.orchard_looker_usage <- merge(DT.orchard_users, DT.looker_usage, all=TRUE)

## Set which ones are looker support
DT.orchard_looker_usage[is.na(dept) & user %in% known_looker_users, dept := "Looker Support"]

## Check for NAs in the DEPT -- there should be none
if (DT.orchard_looker_usage[, any(is.na(dept))]) {
  warning("There are NA in dept")
  print(DT.orchard_looker_usage[is.na(dept)])
}

## PRINT OUT USAGE STATISTICS, By Department and By Individual
{
  catn("Usage by Department:")
  DT.orchard_looker_usage[, lapply(.SD, sumn), keyby=dept, .SDcols=c("runtime_minutes", "number_of_queries")][order(runtime_minutes, decreasing=TRUE)] %>% print()

  catn("Usage by Individual")
  DT.orchard_looker_usage[, lapply(.SD, sumn), keyby=user, .SDcols=c("runtime_minutes", "number_of_queries")][order(runtime_minutes, decreasing=TRUE)][number_of_queries != 0] %>% print(nrows=200)
}


## SUM IT UP
## ------------------------- ##

## Last 90 Days, by Dept
DT.orchard_looker_usage.dept_last_3months <- {
  DT.orchard_looker_usage[ week >= today() - 95,
    list( number_of_users   = lunique(user)
        , number_of_queries = sumn(number_of_queries)
        , runtime_minutes   = sumn(runtime_minutes)
        )
    , by=dept
  ][, perc_of_runtime_mins := fwp(round(percOfTotal(runtime_minutes), 3))
  ][order(runtime_minutes, decreasing=TRUE)]
}
f.out <- writeDT(DT.orchard_looker_usage.dept_last_3months)

## Weekly -- no Dept breakdown
DT.orchard_looker_usage.dept_week <- {
  DT.orchard_looker_usage[ week < today()-5, 
    list( number_of_users   = lunique(user)
        , number_of_queries = sumn(number_of_queries)
        , runtime_minutes   = sumn(runtime_minutes)
        )
    , keyby=list(week) ## , dept
  ]
}

DT.orchard_looker_usage.dept_week.melted <- melt.data.table(DT.orchard_looker_usage.dept_week, id.vars="week")
DT.orchard_looker_usage.dept_week.melted[, ma := forecast::ma(value, 3), by=variable]
DT.orchard_looker_usage.dept_week.melted[variable == "number_of_users"] %>% ggLinegraph(x="week", color="variable", y="value")

DT.orchard_looker_usage.dept_week.melted