Creating Powerful Analytics and Data Driven Dashboards through Parameterization

One great way to get insights, and expand the use of your data science and analytics tools is to parameterize your application. By this, we mean adding the ability for people to alter and explore the data and visualisations by controlling different parameters. For example, you may have several geographical regions for your data. To present data for all regions with the ability to view detailed information per region would lead to a bloated and cumbersome experience for your users. Parameterising your dashboard gives the person using your analytics the ability to instantly hone the visuals and data to their interest and needs, providing a data-driven manner for discovering insights and key business actions to take.

The way to do this in Spotfire is through the use of the Text Area. The Text Area allows you to create numerous controls such as drop downs, sliders, and input boxes which can all be based upon data or manually configured. It also allows for the use of CSS (cascading style sheets) and Javascript, opening up huge potential for customising your analytics tools to provide a great user experience (see Making Data Look Good for an example of this).  In this blog, I am going to show a simple example of parameterizing a dashboard to allow the user to explore different factors in assessing water quality data. In this dataset, there are four water quality parameters the users want to analyse (nitrates, phosphates, chloride and ammonia). We want to give our users a tool that allows them to do this analysis in an intuitive, efficient, and dynamic fashion. From this they will be able to identify trends, and step changes in water quality over time, as well as identify hot spots and potential outliers.

Text Areas in Spotfire to Enhance Analytics

So first we create a Text Area, and add a drop down property control. To do this right click on your Text Area, and select Edit HTML (You can also do this by Edit Text Area but I prefer HTML mode). Then click the add Property Control and select Drop-down list (as shown below). From here you first create a document property. This is going to hold the value from the drop down that a user selects (so we can refer to it later in our visualizations):

Adding a drop down to your Text Area

There are many options for the drop down to control what options are shown. It can be based upon data in a column, a set of column names, fixed values, etc. In my case, I am using a set of column names as each water quality parameter is in its own column:

Drop down configuration options

Once done and you save the Text Area you have something like this (Note I also added a year filter beside my new drop-down list - more on this just below):

Parameterised Text Area

You can find more details in the help on adding drop downs: https://docs.tibco.com/pub/spotfire/7.0.1/doc/html/text/text_details_on_property_control.htm

Notice I have a slider also. This was created by editing the Text Area and adding a filter. You can add the filters from any column very easily into your Text Areas. You can control the filter type by altering the filter type from the filter panel, or the data panel.

Now all I need to do to make my visuals react to this drop-down is set the value or Y-axis to use a document property. In my dashboard, I have configured a map chart, and two scatter plots to show water quality results. I want each of these to only show the parameter selected in the drop down I just created. To do this, I change the Y-axis to use this new property. Simply right-click on your axis selected or the axis expression in the properties panel of the visual and select Select from Property:

Set your Y-axis to use a property

Select the property you created to hold your drop down. Now when you change your drop down, the values shown in each visual will react accordingly. You can even make the title of your chart react to this drop down:

Make the chart title dynamic

So I end up with this:

My final dynamic water quality analysis page

And here is the dashboard in action.

Final dashboard

I have used some other built-in features in this dashboard shown above. For example, the black and red dotted lines are a forecast into the future using Spotfire's inbuilt Forecast option. You can right-click on a scatter plot, and select Forecast to do the same in your analyses. You can further configure how this forecast works through the Lines and Connections section of the chart properties page. 

This method I have used (set axis from the property) works well if you have data for different parameters in separate columns (often called wide data). If you have data all in one column with another column that describes what data each row contains (often called long data), then this technique works perfectly also. Simply set your drop-down to be based upon values in a column instead, and then add a filter to each visual from the Data tab of the properties of each visual to filter the column to only the value of the document property. This is done by adding your filter expression to the Limit data using the expression option for your charts:

Adding a filter to your visual based upon the selected parameter

For details on this read this article: https://support.tibco.com/s/article/How-to-filter-values-in-Spotfire-Property-Controls-based-on-selection-in-other-property

There are some other good articles and blogs on this topic published also:

• https://community.spotfire.com/articles/spotfire/dependent-property-controls-in-spotfire/

Parameterised Outlier Analysis

Outliers in data are something anyone who has ever dealt with data must consider. However, outliers can occur for many reasons such as mistakes in data, and unexpected or random events. There are many statistical methods for assessing where outliers occur and which is most effective will change with different datasets and scenarios. For my water quality example, I wanted to do a very simple outlier check that allowed users to set the level at which a result would be considered an outlier. Again a parameterized Text Area comes to our aid here. 

For my simple outlier check, I am going to use the number of standard deviations a sample result is away from the overall mean. However, I want the user to choose this number to find the optimal setting for their current data. As before, I create a Text Area but this time I add a slider control:

I then choose a fixed numerical range and set this to between 3 and 30. You also choose your step size for the slider which in my case is 1.

Now I have a slider that I can use as my multiplication factor for my standard deviation, and therefore I can calculate whether a result is an outlier. I can do this as an expression on my visuals, or as a calculated column as part of a data table. In this case, I want to display a label when a result meets the criteria for an outlier. I can do this by using a custom expression on the label setting of my chart. Below I have created a scatter plot showing the 4 different water quality parameters over time:

I then set my label expression to be:

 If([Result]>(Avg([Result]) Over ([Determinand]) + (${outlierMultiplier} * StdDev([Result]) Over ([Determinand]))),"Outlier",Null)

Note the reference to outlierMultiplier. This is my document property that stores the outlier slider level selected by the user. So as this changes, this equation changes also. The IF statement in this expression means that if the result is not considered an outlier, no label is shown so we only see labels against outlier results. Here is the final tool in action.

At the end of this clip, I use Spotfire's new augmented AI to try and find out what is causing these outliers. In this example, it shows all the results marked as outliers come from one location on the river. This is great and useful insight!

While I have used a very simple outlier method, there is nothing stopping you from doing much more in-depth techniques such as passing this parameter to a TERR, Python, or R script which runs more detailed outlier methods. 

Executing Scripts Based Upon Parameters

The power of using parameterized Text Areas does not stop with simply controlling document properties that can then be referred to in visuals, expressions, and calculated columns. In Spotfire, a change in document property can also trigger a script to run. This means that you can do an almost limitless amount of functionality based upon a changing parameter, or link in a Text Area. For example, in this tool below clicking on any of the buttons (in the left Text Area) is actually changing a document property attached to an IronPython script. This script is then used to alter various settings in the analyses and reconfigure the visuals accordingly:

A fully dynamic and parameterized dashboard

To attach a script to a changing document property, go to File->Document Properties->Properties. Click on the property of interest and select to execute a script when the property changes:

You can try this tool out for yourself as it is published on Scotland's Environment Web website.

This article here is an excellent guide to getting started with IronPython in Spotfire, and has many example scripts ready to use.