Machine learning is a hot topic nowadays. Therefore, the R programming language has gained a lot of popularity lately. Microsoft is also pushing in that direction by adding R support in many of its products including Visual Studio ( R Tools for Visual Studio). I assume that some customers want to analyze data from time cockpit in R, too. So I wrote this blog post to demonstrate how you can use time cockpit's OData-based RESTful Web API and TCQL to get data into R for further analysis.

Limitations

Note that time cockpit's RESTful Web API must not be used to export huge amounts of data.

If you want to export tens of thousands of e.g. time sheet records, follow these guidelines:

• Get data in batches (e.g. separate TCQL query per quarter)
• Save data locally (e.g. export it in CSV files using write.table) or use a server infrastructure with its own data storage (e.g. Microsoft's R Server)
• If you need to get fresh data from time cockpit, re-read only data that might have changed (e.g. last few months). Do not re-export everything from day one.

Please keep in mind that time cockpit is a system to support the very process of time tracking. We make it easy to get your data out of time cockpit so you can use downstream analysis tools like Power BI or R. However, time cockpit cannot serve as an online data source for your analysis tools. In case of mass data, you have to build something like e.g. a data warehouse that serves as the data source for your analysis.

Script

The following R script reads invoice data from time cockpit, does some basic analysis, and finally plots the result. I added lots of comments to the script to make it easy to understand. Note that the goal of this sample is not to teach you R. The sample should just show how you can access time cockpit directly from R without having to export/import data using CSV files.

# First, you have to install the packages we need for this sample. 
# Note that you can skip this step if you have these packages already installed.
install.packages("httr")
install.packages("jsonlite")
install.packages("dplyr")

# Once installed, we need to load and attach the packages.
library(httr)
library(jsonlite)
library(dplyr)

# In this sample, we will use time cockpit's query language TCQL to query data. 
# Time cockpit has a RESTful Web API that you can use to execute TCQL queries. 
# Let's set the URL of this Web API.
url <- "https://api.timecockpit.com/select"

# Time cockpit's Web API supports both OpenID Connect and Basic Authentication. 
# To keep things simple, we are going to use Basic Authentication here.
auth <- authenticate("your-timecockpit-user", "your-timecockpit-password")

# Time cockpit's Web API uses JSON. Therefore, we have to set the appropriate HTTP headers.
headers <- add_headers("Accept" = "application/json", "Content-Type" = "application/json")

# Now we can specify our TCQL query. In this example, we are getting invoices from time cockpit.
query <- paste0(
    "From I In Invoice ",
    "Select New With { ",
    "  I.InvoiceNumber, I.InvoiceDate, ",
    "  .Customer = I.Project.Customer.Code, ",
    " .Project = I.Project.Code, I.Revenue",
    "}")
body <- sprintf("{ \"query\": \"%s\" }", query)

# Now we can execute the Web API POST request.
response <- POST(url, auth, headers, body = body)

# OData does not return the plain JSON results. It returns a metadata URL and the result values. 
# The result values can be found in the `value` member of `response`. 
# So let 's get the result from the HTTP response and let `jsonlite` convert it to a data frame.
invoices = fromJSON(toJSON(content(response)$value))

# OData returns dates and numeric values as strings. 
# Therefore, we have to convert some of our columns.
invoices$USR_InvoiceDate <- as.Date(substr(invoices$USR_InvoiceDate, 1, 10), "%Y-%m-%d")
invoices$USR_Revenue <- as.numeric(invoices$USR_Revenue)

# Char columns are returned as lists. We have to flatten them (i.e. produce a vector from them).
invoices$USR_Customer <- unlist(invoices$USR_Customer)
invoices$USR_Project <- unlist(invoices$USR_Project)
invoices$USR_InvoiceNumber <- unlist(invoices$USR_InvoiceNumber)

# Time cockpit automatically creates a unique key for each result row.
# We do not need it in this case. Therefore, we can drop the column.
drops <- c("ObjectUuid")
invoices <- invoices[, !(names(invoices) %in% drops)]

# Let's look at the first five result rows. Looks good, doesn't it?
head(invoices, n = 5)

# Let's calculate the total revenue per customer and visualize it using a bar chart.
revByCust = invoices %>% group_by(USR_Customer) %>% summarise(TotalRevenue = sum(USR_Revenue))
barplot(revByCust$TotalRevenue,
    main = "Revenue by customer", 
    names.arg = revByCust$USR_Customer)

# Finally, let's add a line indicating the average revenue per customer.
abline(h = mean(revByCust$TotalRevenue))

The screenshot at the beginning of this blog post shows the result of the R script. Have fun combining the power of R with time cockpit. Don't forget to give us feedback. Was this script valuable for you?

comments powered by Disqus