Back

Tableau & R Integration: Time Series Analysis

15.05.2019

The aim of this post is to present how R functions can be incorporated in Tableau calculation fields and later used in visualisations. Steps below summarize how the integration and analysis can be done using time series data.

1. Launch RServe

  • In RStudio, connect to R server using RServe package
  • Note that using RStudio should remain open during analysis

Rserve is a TCP/IP server which allows other programs to use facilities of R. In our case, we integrate R backend for computation of statstical models or plots in Tableau. More info https://www.rforge.net/Rserve/

2. Connect to your data in Tableau

We use monthly data for temperature in Ljubljana, Slovenia from January 2011 up to April 2019.

It consists of two fields only, Datum and Temperature.

Data are available at meteo.si – Uradna vremenska napoved za Slovenijo – Državna meteorološka služba RS – Vreme podrobneje.

3. Establish connection to R in Tableau

  • Help
  • Settings & Performance
  • Manage External Service Connection
    • Server: localhost
    • Port: 6311
  • Note that for Python connection one need to use port 9004 and library TabPy

4. Calculated fields using SCRIPT functions

  • R functions are used in Tableau as table calculations
  • Four data types are supported: boolean, integer, real and strings

SCRIPT_BOOL , SCRIPT_INT , SCRIPT_REAL , SCRIPT_STR

  • Arguments being passed to R must be aggregated like SUM(Temperature), MIN(Temperature), MAX(Temperature), ATTR(Temperature)

Example below demonstrates how to create a measure which calculates fit of an ARIMA model:

SCRIPT_REAL(“library(forecast);
myts <- ts(.arg1, frequency = 12, start = c(2011, 1));
mod <- auto.arima(myts, seasonal = TRUE);
mod$fitted
“,
AVG([Temp_]))

  • Note that one need to use .arg1 so that Temperature is passed as an argument in the ts() function. In general more than one argument can be passed to the script: we use .arg2, .arg3 … and list fields after AVG(Temp) seperated with commas.

5.  Tables

We can compare temperature to the fitted values from ARIMA as well as from exponential smoothing model (ETS). Both measures are table calculations and by default they are computed as Table (down), which means that they consider all data points in the estimation of the model. Results can be cross-checked in R.

We can modify the computation within visualization as Pane (down) for example.
That would mean that the R script would estimate points in a given year based on solely 12 points given from the data.

6. Graphs

Now that we are confident about computed values, we move on to visualize temperature and their respective fitted values. We can notice that ARIMA model in the first year almost eniterly coincides with the actual data as the orange curve covers the blue one. There is no obvious difference between two models, both seem to be capturing well the regular seasonal pattern of the temperature.

7. Forecasting

For this purpose we expand data with empty lines for future dates and create measures Arima forecast and Ets forecast based on R function forecast(). The following visualization can be obtained.

As regards forecasting, Tableau itself already has it implemented via Analysis -> Forecast. It uses exponential smooting method and it selects best out of eight models based on AIC criterion. Algorithms used in Tableau differ from the ets() in R as they follow different practices in selecting smoothing parameters.
Discrepancies in the results might also be due to different optimization criterions as the algorithm can be either minimizing RMSE or maximizing likelihood function. The performance of the algorithm is sensitive to the nature of data, meaning that the fit and forecast are more accurate if the data are regularly distributed with a clear trend and seasonal component, like temperature.
As a counterexample, data on gold prices (provided by Yahoo) do not have clear trend nor seasonal effect, hence they are harder to predict. Details about forecast in the visualization can be investigated with a right click and then Forecast Options… or Describe forecast… We can inspect how model choice impacts the forecast under Forecast Options and see the characteristics of the best model under Describe Forecast.

More about forecast implemented in Tableau can be read here: How Forecasting Works in Tableau – Tableau

TemperaturesDownload