Tableau and Spotfire – A Tale of Two Visualization Tools (Calgary Schools)

Tableau and Spotfire – A Tale of Two Visualization Tools (Calgary Schools)

by Patrick Cloutier,

May, 2016.

Introduction

Tableau and Spotfire are both great visualization tools. We will work through creating a set of visualizations combined into one dashboard using both these tools. This will showcase the similarities between both products, but it should be noted that the deeper you dive into the products (the more specific demands you have of the product) the more the differences will be apparent. We will work with data from opendata and the Fraser Institute to create the dashboards. The idea is to take school data for Calgary and create a simple list showing the types of schools and the quantity in each category. The next step is to show a list of schools filtered by menus. The school scores will be shown to the right of this list when available (the ratings are not Calgary specific and don’t cover all the schools).

Step 1: School Type Bar Graph

The first step in this dashboard is to create a bar graph that represents the amount of schools per type of school.

Tableau

bl1

The first step is to create a connection to the data in question. In tableau, you connect to the csv directly and for our purpose, we will not be joining the data. The first connection is to the open data csv that contains all the Calgary school information that we are interested in.

bl2

To start the visualization, we will need to drag the Type dimension into the column section and the number of records measure into the row. This will create our bar-chart.

The next step is to format the chart.

bl3

Right clicking on Type and selecting sort will give us a menu which lets us select how we want to show the information. In this case we want to show the greatest selection on the left and the smallest selection on the right. Select Descending by the Field “Number of Records” with aggregation sum. (Taking the average or max/min will only net us 1 when we need everything in the groups to add up).

bl4

Next we want to show the actual number on the top, so while holding the control button, drag the Type dimension from the columns pane onto the label button.

bl5

Finally, right click on the labels at the bottom of the bar-graph and select Rotate Label.

bl6

We now have a working bar-graph showing us the different number of schools of each type in Calgary.

bl7

The first step is to connect to the data. Just as we did with Tableau, connect to the csv to get started. Select the information that we are interested in and then select ok.

bl8

Here is where we see the main difference between the tools. In Tableau, you just start manipulating your data on the fly. In Spotfire, you select the kind of graph you would like to see before going any further. At the top, select the bar-chart button as show below.

bl9

It will try to auto select what Spotfire determines or estimates to be a good set of data that would correspond to a bar-graph. In the columns selector, use Type and as the height of the bars use Row Count.]

bl10

Just like we did above, right click on the labels and select vertical labels.

The next step is to sort it correctly.  To do this, right click in the visualization and select sort bars by value.  This will sort the bars in the exact manner we wanted.

bl11

bl12

The last step is to show the labels.  Right click on the visualization and select properties.  Then select the labels tab on the left and ensure the show labels for has All selected.  Unselect the bar segments and we should have a very similar graph to the one that was created in Tableau.

Step 2:  School Names

Our next step will be to create a list of the schools available in Calgary.  This list will be our master list whereas the list of ratings will be a smaller list (since not all schools have ratings in the information that was used).

Tableau

bl13

In a new sheet, drag the Name dimension to the rows pane.  Then drag another copy onto the labels button.  This will create two identical lists.  Right click on the Name dimension in the row pane and unselect Show Header.  This will hide the first list and should only show a list of all the schools in Calgary.  To prepare for filtering later, we will create a new calculated field that renames the null values of school boards into ‘No School Board’.

Right click on the whitespace in the Dimensions or Measures pane on the left of the screen, and then select Create Calculated Field.

bl14

bl15

Call this calculated field School Board and write in this code for the calculation:

If isnull([Board])
then ‘No School Board’
else [Board]
end

bl16

All this calculation does is check to see if in the Board field there are null values.  If there are, it replaces that value with No School Board.

Next drag the newly created School Board into the filters, and repeat the steps for Type and Grades.  (Always select all data for now).

Spotfire

bl17

bl18

Just as before, select the appropriate visualization (in this case, select the table icon as show on the left).  This will auto select the current data source that was imported and show all the columns.  To show the only the columns we want, right click on the visualization and select properties, then select columns on the left hand side.

bl19

Select all the columns that we are not interested in (all but Name) and click on the remove button.  This will now show us a list of all the Calgary schools available.

Step 3:  Ratings

The final visualization that we would like to show is the ratings visualization.  This will be a list of schools with their attached rating.  This information is found in a different csv, this time from the Fraser Institute (the information was in a table that was copied and pasted into a csv).

Tableau

bl20

As we did initially, we will add another data source to Tableau.  Click on the top left Data menu and select New Data Source.

bl21

On the left side, click on Text File and select the csv when prompted.  Now that we have this information, we need to correctly link both data sources together.  Typically, if there are field names that are the same, Tableau will automatically create a link (for blending purposes) between them.  In this case, none of the field names are similar so we will need to connect them.

bl22

Select Data -> Edit Relationships.

Select custom so that we can add a relationship and then click on Add as shown below.

bl23

bl24

Match School Name to Name and then click ok to create this link.  All this will tell Tableau is that the Name from one of the data sources is the same as the School Name in the other data source.  There is no other information that is passed along.

Now to create the visualization:

Click on the Calgary School Locations data source on the top left.  Drag the Name dimension from the left to the Row pane.  Next, click on the Rankings data source on the top left.  Drag the School Name next the Name dimension on the Row pane.  There should be a small database icon with an orange check mark next to it.  This means that that data source is being blended into the visualization.

bl25

bl26

Next drag the School Name to the filters pane.  We will remove all the null values to ensure only the schools present in both lists are shown.

bl27

Select None and then select Null on the top left of the window.  Ensure that Exclude is selected at the bottom right of the window.

bl27 bl28

The next step is to get rid of the Name dimension entirely.  We only needed it there to ensure that the Calgary School Locations was the primary data source.  This is really important for when we are pushing information from one visualization to the next.

Right click on the Name dimension and select remove.

bl29

Drag the Rating Measure from the bottom left to the text icon as shown on the left.  This should display all the ratings each school has had.  Since it is possible to get two ratings for a school (elementary and high school) we need to switch the aggregation level from sum to average.

bl30

Right click on the Sum(Rating) in the marks pane and hover over Measure (Sum). Change this to

Average.

bl31

Next we should standardize the number format to have 2 decimals. Right click on Avg(Rating) in the

marks pane and select format.

bl32

Select Number (Custom) and ensure that 2 is in decimal places section, then click ok.

bl33

The last step is optional. Above the ranking, there is nothing indicating what it is, so this step rectifies it. Drag Measure Names to the columns pane. Right-click on it, and select Edit Alias. Rename it to Ranking.

bl34

bl35

To add a new connection with Spotfire, click on the Add Data Tables icon on the top left as show here. Click Add -> File Select the csv file.

bl36

bl37

As we did with Tableau, we will create a relation between the two tables. Right click on the Rankings data table at the top left and select Data Table Properties. Select the relations tab and click on Manage Relations.

Match the School Name with Name and click ok (example below)

bl38

Click on the cross table icon on the top bar as shown on the left side.

bl39

There are a few important pieces on the cross table. First ensure that the data table is pointing to the Calgary School Locations. Drag Names to the left selector. On the bottom drag the Rating from the  Rankings data table and ensure the aggregation is set to average. A warning message at the top indicates that column matches may not be working correctly. Click on this icon.

bl40

This message should pop up. Click on the blue hyperlink at the bottom of the warning.

bl41

Ensure you match Name to School Name so that Spotfire can set up the data appropriately.

bl42

To better format the visualizations, right click anywhere in the visualization and toggle the legend and axis selector in the Visualization Features section.

Step 4: Putting it all together

The final step in this process is to ensure the filtering is working appropriately between the visualizations.

bl43

Create a dashboard, drag the School Types visualization from the left to the main window. Next select the School Names visualization and drag it underneath the current window. The bottom half should darken to represent where the visualization will land.

bl44

Repeat this process for the last visualization and place it on the bottom right.

bl45

The next step involves getting the filters to work properly. Click on Analysis in the menu at the top and select Filters -> Grades, School Board and Type. (You will need to do this sequentially) This should add filters on the right hand side.

bl46

Now we need to edit the look of the filters. Click on the down arrow next to the Type filter and select Single Value (Dropdown)

bl47

Do the same things with the School Board filter, this time selecting Multiple Values (Dropdown) and ensuring that the Only Relevant values option is selected. This demotes one filter to a secondary filter where the first (primary) filter feeds results into the secondary filter (cascading filters).

bl48

Do the exact same process as above with the final filter (Multiple Values(List) needs to be selected instead of Multiple Values (Dropdown))

bl49

Next, go to the School List worksheet. Right click on the filter and select apply to Worksheets -> Selected Worksheets. Check the box for Rankings. Do this with all the filters on this workbook.

bl50

Go back to the dashboard and select Dashboard from the menu at the top and select Actions.

bl51

Ensure the select icon is selected on the right. Select School names as the source sheet (the top selection) and Rankings as the Target sheet (the bottom selection).

As long as the All Fields option is selected, select ok. This completes the Tableau Dashboard.

bl52

Select the filter icon in the top menu (looks like a funnel).

bl53

Right click on the new filter pane and select organize filters. Uncheck all filters but Type, Board and Grades. Click Ok.

bl54

bl55

Right click on the Rankings visualization and select Data on the left side. To filter this table by selecting an item on the list, check the Limit data using markings option. Go to the properties of the top visualization and ensure it isn’t checked as well as any filtering scheme is not checked either (you will need to scroll down).

bl56

Right click on the filters again and click on Filtering Scheme Properties. You should see a list of all the filters. Click rename on all the filters we are currently using and rename them appropriately. This completes our dashboard for Spotfire.

bl57

bl58

Above is the dashboard for Tableau and below is the dashboard for Spotfire. They both are quite similar in looks.

bl59

There are a few differences between these dashboards. The way we built the Tableau dashboard, only the Schools that have a ranking will be shown. Whereas with the Spotfire dashboard, all the schools will be shown and if there is a ranking, it will also be shown. This is a slight difference, but if there is a preference, the same technique can be applied to Tableau to free up the list. Restricting the data on Spotfire is more complicated than Tableau. Since the data is being blended, you cannot restrict the data based on the blended data. Workarounds can be quite complicated but it will not be covered in this tutorial.