Pages

Sunday, February 8, 2015

Tableau Testing Automation using Selenium IDE

Have you ever had to test a million reports on Tableau and take screenshots to prove they are working (or not as the case might be)? Well, maybe not a million but most Tableau implementations grow quite quickly as it is so much fun to use. A typical use case for this sort of testing would be a migration to another server or a server upgrade.

Recently I have spent a day testing Tableau reports for one of my clients, taking lots of screenshots. A big part of my test included:
  1. open a view in Tableau
  2. take a screenshot
  3. go back and repeat from step 1.
Does this make you want to automate the process? Yes, that is what my partner said when I told him how I've spent my day. Solution by Ralf Weinand:
  1. Download and install Firefox browser
  2. Download Selenium IDE: http://www.seleniumhq.org/projects/ide/
  3. Download a Firefox add-on 'Selenium IDE Flow Control' https://addons.mozilla.org/en-US/firefox/addon/flow-control/
  4. In Firefox open Selenium IDE: Tools -> Selenium IDE
  5. Create a new test as per screenshot below (or copy-paste the code below). Adjust the server name and the location where to save the screenshots.
  6. Go grab some coffee. Happy testing!

Code credit: Ralf Weinand

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head profile="http://selenium-ide.openqa.org/profiles/test-case">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<link rel="selenium.base" href="https://localhost/views" />
<title>Tableau Test</title>
</head>
<body>
<table cellpadding="1" cellspacing="1" border="1">
<thead>
<tr><td rowspan="1" colspan="3">New Test</td></tr>
</thead><tbody>
<tr>
    <td>open</td>
    <td>http://localhost/views</td>
    <td></td>
</tr>
<tr>
    <td>store</td>
    <td>1</td>
    <td>x</td>
</tr>
<tr>
    <td>storeXpathCount</td>
    <td>//div[1]/div[4]/div[1]/div[2]/form/div/div/div/div[2]/span/span[2]/div[1]/div/div/span/a</td>
    <td>max</td>
</tr>
<tr>
    <td>while</td>
    <td>(${x}&lt;=${max})</td>
    <td></td>
</tr>
<tr>
    <td>clickAndWait</td>
    <td>//div[1]/div[4]/div[1]/div[2]/form/div/div/div/div[2]/span/span[2]/div[1]/div[${x}]/div/span/a</td>
    <td></td>
</tr>
<tr>
    <td>storeAttribute</td>
    <td>//div[1]/div/div[1]/span[1]/div[1]/span[2]@title</td>
    <td>workbook</td>
</tr>
<tr>
    <td>storeLocation</td>
    <td>url</td>
    <td></td>
</tr>
<tr>
    <td>storeEval</td>
    <td>'${url}'.split('/').last();</td>
    <td>sheet</td>
</tr>
<tr>
    <td>storeEval</td>
    <td>'${url}'.split('/').slice(-2, -1)[0];</td>
    <td>workbookurl</td>
</tr>
<tr>
    <td>storeEval</td>
    <td>'${workbooknametemp}'.replace('/','_');</td>
    <td>workbookname</td>
</tr>
<tr>
    <td>echo</td>
    <td>${workbookurl}</td>
    <td></td>
</tr>
<tr>
    <td>echo</td>
    <td>${workbookname}</td>
    <td></td>
</tr>
<tr>
    <td>echo</td>
    <td>${sheet}</td>
    <td></td>
</tr>
<tr>
    <td>pause</td>
    <td>10000</td>
    <td></td>
</tr>
<tr>
    <td>captureEntirePageScreenshot</td>
    <td>C:\Users\julia\Desktop\tableau_selenium_test_workbooks\${workbookurl}_${sheet}.png</td>
    <td></td>
</tr>
<tr>
    <td>open</td>
    <td>http://localhost/views</td>
    <td></td>
</tr>
<tr>
    <td>waitForElementPresent</td>
    <td>//div[1]/div[4]/div[1]/div[2]/form/div/div/div/div[2]/span/span[2]/div[1]/div[${max}]/div/span/a</td>
    <td></td>
</tr>
<tr>
    <td>storeEval</td>
    <td>storedVars['x'] = ${x}+1</td>
    <td></td>
</tr>
<tr>
    <td>echo</td>
    <td>${x}</td>
    <td></td>
</tr>
<tr>
    <td>endWhile</td>
    <td></td>
    <td></td>
</tr>
<tr>
    <td>selectWindow</td>
    <td>name=navbar-iframe</td>
    <td></td>
</tr>
<tr>
    <td>clickAndWait</td>
    <td>link=Sign In</td>
    <td></td>
</tr>
<tr>
    <td>selectWindow</td>
    <td>null</td>
    <td></td>
</tr>
<tr>
    <td>type</td>
    <td>id=Email</td>
    <td>julia.gusman@gmail.com</td>
</tr>
<tr>
    <td>type</td>
    <td>id=Passwd</td>
    <td>julia007</td>
</tr>
<tr>
    <td>clickAndWait</td>
    <td>id=signIn</td>
    <td></td>
</tr>
<tr>
    <td>clickAndWait</td>
    <td>link=Drag and Drop</td>
    <td></td>
</tr>
<tr>
    <td>click</td>
    <td>//img[@alt='Create new post']</td>
    <td></td>
</tr>
<tr>
    <td>click</td>
    <td>//body[@id='blogger-app']/div[3]/div[3]/div/div/div/form/div/span/button[4]</td>
    <td></td>
</tr>
<tr>
    <td>click</td>
    <td>css=span.GCUXF0KCNIB</td>
    <td></td>
</tr>
<tr>
    <td>click</td>
    <td>link=Edit</td>
    <td></td>
</tr>
<tr>
    <td>selectFrame</td>
    <td>Rich text editor</td>
    <td></td>
</tr>
<tr>
    <td>click</td>
    <td>css=img</td>
    <td></td>
</tr>
<tr>
    <td>selectWindow</td>
    <td>null</td>
    <td></td>
</tr>
<tr>
    <td>click</td>
    <td>id=tr_delete-image</td>
    <td></td>
</tr>
<tr>
    <td>click</td>
    <td>css=div.tr-icon.tr-image</td>
    <td></td>
</tr>
<tr>
    <td>click</td>
    <td>css=input[type=&quot;file&quot;]</td>
    <td></td>
</tr>
<tr>
    <td>type</td>
    <td>css=input[type=&quot;file&quot;]</td>
    <td></td>
</tr>
<tr>
    <td>click</td>
    <td>id=picker:ap:0</td>
    <td></td>
</tr>
<tr>
    <td>selectWindow</td>
    <td>name=Rich text editor</td>
    <td></td>
</tr>
<tr>
    <td>click</td>
    <td>css=img</td>
    <td></td>
</tr>
<tr>
    <td>selectWindow</td>
    <td>null</td>
    <td></td>
</tr>
<tr>
    <td>click</td>
    <td>id=tr_x-large_link</td>
    <td></td>
</tr>
<tr>
    <td>selectFrame</td>
    <td>Rich text editor</td>
    <td></td>
</tr>
<tr>
    <td>click</td>
    <td>css=img</td>
    <td></td>
</tr>
<tr>
    <td>selectWindow</td>
    <td>null</td>
    <td></td>
</tr>
<tr>
    <td>click</td>
    <td>id=tr_original_link</td>
    <td></td>
</tr>
</tbody></table>
</body>
</html>
Posted by at 4 comments:

Wednesday, November 5, 2014

Year on Year, Month on Month and Actuals for Current Month

Tableau makes it really easy to compare a metric to what it was in a previous period, such as a year ago (often referred to as 'Year on Year') or a month ago ('Month on Month'). These can be displayed using the table calculations. However, things become a little more tricky if we start combining this with filters.

What if we want to display Year on Year (YoY) as well as Month on Month (MoM) and current figure but we only want to display one month on the view.


First thing you would try to do is filter for current month but then the MoM and YoY figure go missing:


Since Tableau applies filters on the data source, once you filter say for the latest month only the comparison to previous year or previous month is not possible any more.

A little trick to get around this is to use a table calculation in the filter. Table calculations happen on the view rather than in the data source. This means that Tableau will bring in all the data, calculate MoM, YoY and then apply the filter.

So what calculation can we use for the filter? For example, you could set up a calculation to get the figure from next month. If we only have numbers until December 2014, then the January 2015 figure will be empty. This means that 'next month figure' for December 2014 will be empty. Add the new table calculation 'next moth' to filter and select 'special' -> 'null values'.

You can download an example below.
Posted by at No comments:

Thursday, October 16, 2014

Controlling Filter Selection

Creating filters with Tableau is so easy! Right-click, select 'show quick filter' and you have a selection list visible. You can also customize it to show as drop-down, list or slider. By default your filter will include all values from the database. You can also restrict filter options based on additional filters. Below is an example of a 'Country' filter that got generated from my data.
We can see that some data does not have 'Country' populated, therefore the filter has a 'null' value. I don't want my users looking at the 'null' country. I could add a filter and exclude null, but that would be a filter on the field 'Country', therefore I will not be able to have a second filter that is controlled by the user. There are two options to get around this. 1. Duplicate the field 'Country' and use a exclude null filter on that. 2. Create a set and use set as a filter. I personally, find the second option more elegant, so my example is based on that option. Here are the steps: Right-click on the field 'Country', select 'Create Set'. This looks very much like a filter, so go ahead and select all, pick a few values or use a condition. Then drag the set onto the filter shelf. Right-click on the set on the filter shelf and select 'Show Quick Filter' Notice, only selected values are available in the filter. Here is an example that restricts the drop-down to the top 20 countries by population.
Posted by at No comments:

Tuesday, September 23, 2014

Your Data Over Time: Displaying YTD and Last Several Periods

Displaying cumulative total alongside the detailed view is a common requirement. Say you know what your profit for the current year (YTD) has been a few weeks ago but you are interested in seeing how those last  few weeks have impacted the overall picture. It is useful to look at the cumulative profit since the start of the year and how it has been changing over the past few weeks.

To get the cumulative total we can use a table calculation in Tableau. Now what if we want to view the running total as well as the detailed view but restrict the details to last few periods only?

Notice, if you simply apply the filter on dates (let's assume we want to look at last eight weeks), you will get a result similar to the one below.


Have a closer look at the screenshot above and you will notice the problem: our YTD Profit does not include any data prior to the 10th of November. But we know we have been making profit all year and we want to see that reflected in the overall YTD.

Here is the trick:

If you have done our advanced Tableau training you would be familiar with the fact that filters occur on the data source. This is the reason we run into the issue above. However, we know that table calculations happen directly in Tableau after all the data has been pulled from the data source. Consequently, if we use a filter on a table calculation, we will filter the data after it has been brought into Tableau.

We create a table calculation to retrieve the data from 'next eight periods'. Then we filter out everything where data for the 'next eight periods' exists (not null). This will only keep the last eight periods as we do not have any data from the future.

Here are the steps based on Superstore sample data:

  1. Create the view with continuous week on Columns, Profit on Rows and another copy of profit on rows with the table calculation 'running sum'.
  2. Add a third copy of the field Profit to Rows.
  3. right click on the new copy and select 'Add table calculation'.
  4. Select Difference from Previous Across the table.
  5. In the Table Calculation pop-up select Customize.
  6. Adjust the formula to "LOOKUP(ZN(SUM([Profit])), " Give it an appropriate name e.g. 'Next Eight Periods'.
  7. Put the new field 'Next Eight Periods' on filter, select special 'null values'.
  8. Remove the third copy of Profit from rows as we don't need it any more.

You can download and explore the result below:




Posted by at No comments:

Wednesday, August 27, 2014

Publish Survey Results with Tableau

I feel a little guilty about this one as at the time I was asked this question I didn't realize this was possible. This was a while ago so hopefully my smart student got it all figured out anyway :)

Whenever we are publishing survey results we usually want to present aggregated data (average, median, min, max, etc.) and make sure the responses cannot be traced back to the individuals. Think about your employee engagement survey, salary surveys, etc.

Let's have a look how this can be easily done with Tableau. As an example we will take course satisfaction survey. We gather individual responses as per made-up data below:


We want to display the average rating per course for this instructor, which might look like this:
Now this is great except the end-user looking at this chart might become curious about the individual student ratings. Let's assume we don't have much security around the workbook / server. We might even want to publish this view on Tableau Public. Let's also assume that the original data was in the spreadsheet and we have created a Tableau extract and used that as a data source. If our smart end-user wants to see the details, they can use the 'view data' option on the server and download the full set of data as .csv file.

What we really want, is to only include the summarized data in the extract.
Here is the trick:

1. In the data Window, right-click on the dimension you want to hide (in our example 'Name').
2. Make sure the default aggregation on the rating field is set to 'average' (right click on 'rating' in the data window, select 'default properties', aggregation 'average'). Note, the default is usually sum but in our example we want to show the average rating.
3. Create an extract with the option 'Aggregate data for visible dimensions' and viola!



Now the detailed information is no longer included in the extract, so it is safe to publish and distribute; you can even put it up on Tableau Public.
Posted by at No comments:
Subscribe to: Comments (Atom)