Advanced Tableau Analytics – Multi Condition Triggers and Alerts in Bollinger Bands

We have explored Bollinger Bands implementation in my previous post. In this article, we will explore deep on implementing advanced features such as multi-conditions and alerts on Bollinger Bands.

As we visualize the trend of data (e.g. stock price movement),  don’t you think it is beneficial to have alerts in visualization if the price violates upper or lower control limits?  Using Tableau, that can be straightforward implementation with a calculated field for condition and using a Mark Label on the chart as shown belowTableau_Bollinger_Multi_Chart_Conditions

Tableau_bollinger_band3

Tableau_Bollinger_Multi_Chart_Conditions_2

But what if the requirement is to show this alert based on one more condition that was to meet/trigger in another sheet or  based on two or three such conditions from different sheets?

Multi-Chart conditions

Going a step further, imagine that you want to have a dashboard that shows alerts based on combination of conditions from different sheets. Here the implementation gets tricky as Tableau by default is not designed for inter-chart interaction as each sheet generates visualizations based on its underlying data and data source.

One way of implementing is to use multiple data sources and link them in your chart. This is possible if you can link (join) data source of ChartA with that of ChartB with a common dimension. The resulting implementation can have scenarios like :

  • Trigger condition in ChartA has been met
  • Trigger condition in ChartB has been met
  • In Chart C, check for its own Trigger condition, check for trigger in ChartA and ChartB display an Alert, which can be a strategy or aid a decision ( for example, in Stock Trading it can be a BUY/SELL/HOLD decision)

In real-lime, this can be extended further for advanced features to generate Dynamic Dashboards and email alerts. With analytics evolving smarter, you don’t want to view all data visualizations everyday and will be interested only in those visualizations where the triggers have been met.

The final implementation is below

Tableau_Bollinger_Multi_Chart_Conditions_3.PNG

I will update this post with source and implementation steps shortly.

Advertisements

Configure Azure Public Load Balancer Part -2

This is part-2 of previous article and we are configuring a public load balancer in Azure which for distributing/load balancing traffic between VMs where we host an application or service.

Please read Part-1 before going through steps in this article.

In Part-1 lab, we have added two VMs (Windows Server 2016), specified Virtual Network, configured Availability sets and their public IPs. We also did setup and deploy a web application on IIS in both VMs and tested accessing those using individual URLs.

In Azure portal, add a load balancer as shown below:

AzureLoadBalancer-21

Choose the type of load balancer as “Public”

AzureLoadBalancer-22

AzureLoadBalancer-27

Next step is to select new Load Balancer that is created and go to “Front end IP configuration” and add a public IP address. Note that once completed, we will be using this IP address for accessing the web application (MyWebApp that we created in Part-1 lab) instead of individual IPs for VMs.

AzureLoadBalancer-31

 

Select “Backend Pools” next and click “Add”. Select your availability set where both of our VMs are associated.

AzureLoadBalancer-33

AzureLoadBalancer-34

 

Now you will be able to associate  both VMs to the backend pool of load balancer as shown below.

AzureLoadBalancer-36.png

Once done, go to Health Probes, and add a health probe for this load balancer with default settings.

The next important step is to set Load balancing rules. Add one as shown below.

AzureLoadBalancer-40

AzureLoadBalancer-41

Go to LoadBalancer Front End and you can find the public IP address has been associated and configuration is looking good

AzureLoadBalancer-42.png

 

Now it is time to test accessing your web application. Note that we access the web application using  LoadBalancer public IP.

http://<load balancer IP address>/MyWebApp

The demo below shows the results when I accessed it using two browser instances. Loadbalancer directed one to VM1 and the other to VM2

AzureLoadBalancer-43.png

 

Assume that one of your Virtual Machine is down or under maintenance, the load balancer auromatically redirects the traffic to other available VMs. In our case, I have stopped VM1 (WebServerVM1) to simulate such a scenario.

AzureLoadBalancer-44.png

Once VM is stopped, refresh the browser instance which was loaded from that VM1. Note that the load balancer redirects the request to VM2 as shown below.

AzureLoadBalancer-45.png

Details of internal load balancing scenarios will be discussed in another article.

Configure Azure Public Load Balancer Part -1

It has been several months since I covered Azure topics here. As we revisit Azure, I would like to start with Load Balancer.

AzureLoadBalancer-1

Microsoft Azure provides load balancer options for the following :

  • Load balance external traffic to your VMs (Public Load Balancer or External Load Balancer)
  • Load balance traffic inside your Azure virtual network (Internal Load Balancer)
  • Port forward traffic to a specific port on specific VMs

Note : If you are looking for how to distribute traffic between different data centers/regions, explore Azure Traffic Manager.

In this article, we will configure an external load balancer. This requires you to create couple of VMs in your Azure account and configure IIS and a web applicationon it first, which is covered in Part-1 here.

First create two VMs. In this case, I selected Windows Server 2016. I named them as WebServerVM1, WebServerVM2

AzureLoadBalancer-1.jpg

AzureLoadBalancer-2

Create an availability set as shown below:

AzureLoadBalancer-4

Create Virtual Network (or use an existing one), and create and assign a public IP for both VMs. As shown below, now we have the VMs and the related resources created.

AzureLoadBalancer-9

Next step is to remote (RDP) to both VMs. Then install and configure IIS on both using Server Manager.

AzureLoadBalancer-11

 

AzureLoadBalancer-14

On each VMs, add inbound rule in Windows Firewall to allow port 80.

AzureLoadBalancer-18

Create demo application in IIS (named as MyWebApp in this example) on both VMs and place a simple HTML file as home page. For our demo, I added a text in html home page content to identify whether it is deployed in VM1 or VM2.

AzureLoadBalancer-19

This completes our VM and application configuration on both VMs.

Going back to Azure portal; we also need to set Inbound Security Rule in Network Security Group for both VMs.

  • Select NSG resource.
  • Click on Inbound Security Rules
  • Add rule for port 80 as shown below

AzureLoadBalancer-16

Once this is completed, you can access applications on both VMs using their individual URLs using their public IPs as demonstrated below.

AzureLoadBalancer-20

Next step is to configure our public load balancer for these two VMs. We will explore that in our next lab/article.

 

 

 

Bollinger Bands analysis using Tableau

Download source files used in this article

Volatility analysis charts using Bollinger Bands are often used for trading decisions. Those who track stock price movements must be familiar with charts types shown below

Tableau_bollinger_band1

Bollinger Bands are intervals drawn on price chart at standard deviation levels above and below the corresponding moving average.

Bollinger Bands consist of :

  • an N-period moving average (MA)
  • an upper band at K times an N-period standard deviation above the moving average (MA + Kσ)
  • a lower band at K times an N-period standard deviation below the moving average (MA − Kσ)

Let us see how can we develop this chart using Tableau.

In this example, I connect to a database table which has daily price information of a stock. Simple data source – dimension as Date and measure as price.

Step one is to create a line chart, as shown below

Tableau_bollinger_band2

Next we need to add 4 calculated files

  • Moving Average
  • Standard Deviation
  • Upper Band
  • Lower Band

Tableau_bollinger_band3

Note: The above formulas have used 20 as N-period (look back period) and 2 as K-times to multiply since these are  typical values used in real world. If we need to change these dynamically, we can consider using them as parameter fields.

Add ‘Measure Names‘ as filter to your sheet (select MA, LB and UB in the filter selection). Then drag and drop ‘Measure Values’ to Rows in the chart.

Tableau_bollinger_band4.PNG

Choose ‘Dual Axis’ for Measure Values and synchronize the axis as next step.

Tableau_bollinger_band5.PNG

We have the Bollinger Bands generated; however the color schemes are not intuitive. So lets modify that a little.

Here is the final chart

Tableau_bollinger_band6.PNG

Download source files used in this article

Cluster Analysis using Tableau and R – Part-2

Related article : Cluster Analysis using Tableau and R – Part-1

We have performed clustering analysis from both Tableau and R in my previous post. Tableau uses k-means algorithm for cluster analysis which partitions the data into k clusters with a center or mean value of all the points in each. Clustering is based on the distance each measure lies from the center.

Let’s look into that in detail.

First generate a cluster scatter plot in Tableau as we did in part -1 using Iris data set.

clustering_tableau_R10

Right click on the cluster that we added and choose Describe Clusters option

clustering_tableau_R11

This provides you the clustering details given below

clustering_tableau_R12

Now lets perform the k-means clustering from R and print the cluster

#copying iris to myiris variable
myiris<-iris
#Remove Species column
myiris$Species <- NULL
#clustering
kmeans.result <- kmeans(myiris,3)

#print the cluster data
kmeans.result

clustering_tableau_R13

Check the cluster means against that of Tableau cluster centers. Aren’t these comparable? However Tableau clustering analysis is limited and the default one is k-means as compared to the number of packages and functions available in R to perform various types of clustering. I will dedicate a future article to cover cluster and fpc packages in R.

In this case, what options that a Tableau user has for extended/advanced clustering? The answer is R integration by calling R packages from Tableau using similar steps that I explained in this article.

Cluster Analysis using Tableau and R – Part-1

This article introduces you to similar clustering analysis on your data using Tableau and R. Data files and source used in this post can be downloaded using the link below.

Download source files used in this article

Clustering is the assignment of a set of observations into subsets (called clusters) so that observations in the same cluster are similar in some sense. We will perform the analysis in both Tableau and R using the same data.

Clustering Analysis using Tableau

To start with, we connect Tableau to Iris data set.  You can download this from UCI Machine Learning Repository

First connect the Tableau work book to this csv data source and launch a new sheet. Drag the measures petal length, petal width to columns and sepal length, width to rows.

clustering_tableau_R

Next, disable aggregation of measures using Analysis->Aggregate Measures

clustering_tableau_R1

Alternately, to keep it simple, you can choose to analyze only 2 measures as shown below. But in this article, we go with all 4 measures as above

clustering_tableau_R2.PNG

If you observe, these scatter-plots does not identify or differentiate any groups. However in our case, the data set already has a column specifying flower species of these measures. So let us view it by dragging ‘Species’ to color which shows the distinct species groups as below:

clustering_tableau_R3

Well, imagine what if we didn’t had the ‘species’ data handy and we wanted to identify the clusters based on the measures. Lets see how it can be accomplished using Tableau Cluster Analysis.

Start with our initial plot, i.e.

clustering_tableau_R1

Go to Analytics tab, and drag ‘Clusters’ as shown in the screen capture below. Tableau automatically identifies the number of clusters.

clustering_tableau_R4

Leave the defaults

clustering_tableau_R5.PNG

clustering_tableau_R6

Note that we have got exactly same cluster grouping as we got using ‘Species’ dimensi.on data.

Tableau uses k-means algorithm for cluster analysis which partitions the data into k clusters with a center or mean value of all the points in each. Clustering is based on the distance each measure lies from the center.

Cluster Analysis using R

To start with, let us revisit Tableau plot for iris data between petal.length and petal.width with cluster analysis.

clustering_tableau_R7

Let’s go to RStudio, and plot this using ggplot (note that iris is available as part of the datasets installed with R)

library(ggplot2)
ggplot(iris, aes(Petal.Width, Petal.Length, color = Species)) + 
geom_point()

clustering_tableau_R8

Note that we get identical grouping in R plot, but we used species column data to group (color) the data.

Let us look at how to perform the cluster analysis to identify clusters in R.

First take a copy of iris dataset to another variable

#cluster analysis - Biju Paulose
#-------------------------------
#copying iris to myiris variable
myiris<-iris
#printing data 
myiris

For our analysis, we do not want to use species column/data. So lets remove that from the new dataset.

#Remove Species column
myiris$Species <- NULL
#printing data to verify
myiris

Lets use k-means function for generating 3 clusters and plot the data

kmeans.result <- kmeans(myiris,3)
# plot the clusters
plot(myiris[c("Petal.Width", "Petal.Length")],col=kmeans.result$cluster)

The result is given below.  As you can compare with the analysis performed in Tableau above, we could generate the same clustering of data from R. We will examine these more closely in my next article.

clustering_tableau_R9

Download source files used in this article