Using R forecast from Tableau


  • RStudio (with forecast package installed)
  • Tableau Desktop (with connectivity established to RServe service. For details of R integration with Tableau, please refer my previous post here)
  • R Programming knowledge
Download source files used for this article

Forecasting allows business to arrive at more realistic estimates and targets for future. Tableau analytics provide the option of generating forecasts which many of you must be familiar with. In this article, we will look at how to make use of the R forecast package from Tableau. This will be a reference for the capabilities that you can bring in from R.

For a simple demonstration of R forecast, we can use the R air passenger time series data set for 2 year forecasting as below

myts1 <- ets(AirPassengers)
plot(forecast.ets(myts1, h=24))


Tableau’s native forecasting has similar capability – an example shown below using the superstore dataset


For the R integration, start a new sheet connecting to globalstore dataset and generate timeseries graph for sales (orderdate by months and Sum[Sales])


To generate a forecast using R package, create a calculated field with the script as shown below


myts <- ts(.arg1,start=c(2011,1), frequency=12);
myforecast <- forecast(myts, h=.arg2[1]);
append(.arg1[(.arg2[1]+1):monthsts],myforecast$mean,after= monthsts
SUM([Sales]),[Forecast Months])

The scripts creates timeseries for sales starting Jan 2011, generates forecast and appends starting x months (specified by parameter ‘Forecast months’) before the last month in the series.

You can view the forecast series by adding calculated field (SalesForecast) to the row. To make it intuitive, create the formula isForecast as below and drag to color.



Forecast vs Actual

To view forecast vs actual side by side, you can add sales to row


But this does not give you a clear understanding or limits your ability to compare. The solution is to bring them together (dual-axis) and then synchronize both axis. The result is shown below


Download source files used for this article



How to integrate R with Tableau

We have seen how R can be integrated to your data science project using Power BI or Visual Studio(RTVS). Now its time to look at R integration with Tableau.

Before we get started with the steps, let us discuss how this is beneficial.

Tableau is a great visualization tool which helps you to understand your data, provide interactivity and assist in making business decisions. R integration is going to bring the capabilities of to your Tableau visualizations – such as statistical functions predictive analysis. The advantage of interactive visualizations in Tableau powered by the complex statistical analysis behind the scenes using R presents a strong case for data scientists to go for this integration.

I have added a high level representation below of this implementation. You can call R functions from Tableau and it passes the result back to Tableau which can be used to generate visualizations. You can utilize all packages (difficult to accomplish using Tableau scripting alone) that are running in R Server and generate visualizations using the resultant data (complex to accomplish using R alone).



As first step, make sure that RServe is running as a service that you can connect to. The screenshots below shows how to install RServe from RStudio.


Start the service


Now RServe is ready for connections. Go to Tableau and choose the option Help–> Settings and Performance–>Manage External Service Connection


In this case, my RServe is running on the same PC. So I selected localhost as server. Default RServe port is 6311. Leave that as is and test your connection as below


Above message confirms that you have established the connectivity with R service.

Next, we will look at an example calling R scripts from Tableau.

R rattle launch Error – libatk-1.0-0.dll is missing from your computer

If you encounter the below error while trying to launch rattle package in R Studio, that indicate that GTK+ is missing.

The program can’t start because libatk-1.0-0.dll is missing from your computer. Try reinstalling the program to fix this problem.

Error in inDL(x, as.logical(local), as.logical(now), …) :
unable to load shared object ‘C:/Users/GEEK2/Documents/R/win-library/3.3/RGtk2/libs/x64/RGtk2.dll’:
LoadLibrary failure: The specified module could not be found.

Solution :  Re-Install package > install.packages(“rattle”)

If the popup asks for installing GTK+, select OK



Get Docker on Windows – Docker Toolbox

How to get Docker installed in your windows? First major consideration is the version of Windows that you are running.

Docker for Windows: Docker for Windows requires 64 bit Windows 10 Pro or Windows Server 2016 and Microsoft Hyper-V. You can find complete requirements here

Docker Toolbox : If your windows does not meet the above requirements, you can install Docker Toolbox, which uses Oracle Virtual Box

Let us look at Docker toolbox installation in detail for those who are on Windows 8 or Windows 7. Make sure that virtualization is supported/enabled in your OS. For Windows 8, verify that from Task Manager -> Performance Tab.


For windows 7, download and run the Hardware-Assisted Virtualization Detection Tool


Next install Docker Toolbox (with default options selected) and launch Docker Terminal


Verify installation by running hello-world command

$ docker run hello-world


Launch Kitematic to search for various docker images available from Docker Hub



On-Premises Data Gateway for Power BI

We have seen different Power BI implementation options in my article here. If you do not want to move your database infrastructure to cloud, but looking for for generating visualizations from Power BI service(cloud) connecting to on-premises data source, that is possible using Data Gateway. Power BI visualizations on cloud use gateway to get data from on premise data (database such as SQL Server, Oracle or even structured files such as spreadsheets or csv files.

Note : Complete on-premises solution is available with the release of Power BI Report Server which is part of Power BI Premium. This article assumes the Power BI service implementation as below and gateway component is highlighted.

Power BI Gateway 1

On-premises data gateway allows Power BI service to connect to your database to refresh dataset. Gateway allows both scheduled refresh and live/direct query.  Time for looking at the steps on how to install and configure the gateway.

Download the standalone data gateway and start the service in on-premises. To download, use the option below after logon to your accountPower BI Gateway Installation_1

After installation, you have to sign in using your Azure account and specify a name to your gateway so that Power BI service tie it to your Power BI account.

Power BI Gateway Installation_3

Power BI Gateway Installation_4

Make sure that On-Premises Data Gateway is running as a windows service in your PC. Ideally this will be one of your on-premises server with connectivity to your database.

Go to Power BI web login, select ‘Manage Gateway’ option as shown below

Power BI Gateway Installation_5

Note that your Data Gateway is already listed there. You need to create a data source and provide authentication details for database connectivity (Windows User details in case of a file data source such as excel spreadsheet)

Power BI Gateway Installation_6

Next, go to your workspace, select the data set and go to ‘settings’ as shown below

Power BI Gateway Installation_7

Under ‘Gateway connection’ section, select your data gateway and click ‘apply’

Power BI Gateway Installation_8

Under ‘Scheduled refresh’ section, turn on ‘Keep your data up to date’ and specify refresh frequency as below

Power BI Gateway Installation_9

That completes it. Your dataset is ready for scheduled refresh connecting to your on-premises data source!

For verifying, you can update data in SQL Server database (local server) and wait for the refresh to happen.

Power BI Gateway Installation_11

Once complete, launch your visualization which uses the dataset and see your updated data reflecting there!

Power BI Gateway Installation_10

Data Analytics – The Power BI way

In my previous articles related to BI, we have explored extensively on Tableau and SAP Business Objects. While I continue the series, it would be unfair if I don’t cover another ’emerging’ solution – Microsoft Power BI (in fact it is a market leading BI solution already, refer to my post for 2017 BI trends here Gartner Magic Quadrant for BI and Analytics)

Those who are familiar with Tableau will find themselves home with Power BI in many ways. However Power BI is unique in terms of features, the publish options, pricing and visualization options such as custom visualizations.

Power BI was completely cloud based solution until recently – i.e, until the release of Power BI Report Server in this month (June 2017).  In the cloud based solution, you have to publish your visualizations to Power BI Service on cloud. You have alternative to Power BI desktop for development in the form of Power BI Service Web Interface which provides similar options for creating and customizing visualizations.

I have sketched a high level cloud based (Power BI Service) implementation architecture below  connecting to on-premises database to understand the different components better.

Power BI Architecture

Implementation architecture using Power BI Service on cloud connecting to on-premises database

The implementation architecture below shows the on-premises solution using Power BI Report Server. In this implementation, development and publishing do not require cloud. Note that the development require installation of custom release version of Power BI Desktop (Power BI Desktop optimized for Power BI Report Server)


PowerBI Architecture_OnPremise

On-Premises implementation architecture using Power BI Report Server

In terms of connectivity, Power BI can connect to almost every data source. However the type and location of data should decide your design and type of connectivity.

On-Premises Database connectivity from Power BI Service : If you have an on-premises database, Power BI service allows the connectivity using gateways. The options are (a) data imported to Power BI service or (b) use direct query or live connections. We will explore these implementation models in detail in the upcoming articles.


Custom Visuals : This is really great feature that Microsoft lets Power BI developers to create and publish their custom visualizations which can be added to your visualization tray and integrate with your reports/dashboards. Thanks for the support to creativity from developer community.

R Integration to Power BI :  Similar to Tableau R integration, we can connect to R for performing complex predictive analysis.


I will cover R integration details and many more in upcoming posts.

Data Analytics using Tableau – Trends & Trend Lines

In my previous blog, I had posted a bar chart showing declining trend in sales of a company over the years. If you notice, it had a trend line to aid visual analytics.


Another example given below shows profit trends by increase in sales for each category.


Trend lines are great visual tools for quick analysis. In the above diagram, its very easy to judge that the increase in sales for Supplies and Tables does not help increasing profit. The same judgement could not have been derived that easy from the clustered circles if the trend lines were not present. The steps to add Trend Lines are explained below.

I have created a scatter plot for Sales vs Profit using the Global Superstore database. Notice the “analytics” tab highlighted below.


Drag ‘Trend Line’ from the options to the visualization area and choose the line type. In this example, I have selected Linear model.



Additional options are available at (Right click -> Trend Lines-> Edit Trend Lines). For example, you can choose to show only one line (uncheck show trend line per color) and view for each category interactively for focused view/analysis as shown below