How to Test if Your Data
is Up To Date on
Your Tableau Dashboard
Testing Your Dashboard Data In Tableau
A common requirement when testing if your Tableau dashboards are successfully updated every day is to make sure that yesterday’s or today’s data is available. Data updates can fail from time to time, for instance, if the data isn’t loaded into the data source, or a failed extract refresh doesn’t create an alert. On the other hand, presenting business decision-makers and stakeholders with up-to-date data is crucial for organizations. At Wiiisdom, our mission is to help you release the potential of Data Analytics, and what’s better than data accuracy to ensure proper user adoption and a good experience? More importantly, how can you ensure your data is always accurate? Let’s start with how it works in Tableau today.
Within Tableau there is a workaround with Alert Based Date Calculations, however, there are issues with this approach. Firstly, you need to modify your Tableau document to add a new sheet and new calculated fields. Secondly, you need to create a scheduled subscription and deliver an email alert to certain recipients. In spite of this workaround it also raises questions among users:
- What if the date comparison logic is different in the sheet?
- What if you are not allowed to modify the dashboards?
- What if email alerts are not flexible enough and you want to send notifications to Slack, Rollbar, or other Cloud services?
The below image details the steps you need to take to manually check if your data is updated on a dashboard:
Steps when testing data update manually.
As described in the image, to test if the data is up-to-date on a Tableau workbook in real life, you would open the given visualization in the browser, set a date range filter to cover today’s date, and manually check if the required data is on the dashboard or not.
With Wiiisdom Ops you can say goodbye to manual checking. Our solution provides you with a flexible and reliable way of testing if your Tableau data is up-to-date, and below I’ll explain just how it works:
Automatically Test Your Tableau Dashboard Data
With Wiiisdom Ops, we can set dynamic values for filters and can validate the underlying data with a formula, with an easy way to automate this. Follow the below steps to achieve all that:
Step 1: Create The Task Flow
With our functional testing module, you need to create an interaction flow simulating the actions that you are doing manually to check data availability.
The flow of interactions to check if today’s data is available on the dashboard.
Just as you would do in real life, we need to add four tasks within our functional test:
- Login to Tableau: This will open a browser and will log in to your Tableau Server.
- Open Viz: This will open the dashboard you want to test for today’s data.
- Set Filters — Date Range: This will set a dynamic date filter to show the last 3 weeks’ data.
- Assert Data Rules: This will download the underlying data from a specific worksheet and verify if data from today is included in the dataset.
Step 2: Configuring The Tasks
2.1 Login to Tableau
The login to Tableau task is straightforward, you just need to set a name for this task, as your Tableau Server credentials to log in to Tableau are stored in Context Variables.
2.2 Open Viz
In the Open Viz task, choose the Tableau Viz where your time series data is included, and make sure to check the Refresh data on open option so the dashboard is using data from the data source and not from cache. This is equivalent to clicking on the Refresh button after opening the viz.
Turn on refresh data on open.
2.3 Set Filters — Date Range
Things will start to get more interesting with the Set Filters – Data Range task. We would like to set a date range that includes the last three weeks’ data. We need to use the start and end date with relative seconds. For example, -3600 is the last hour, -86400 is yesterday, 3600 is the next hour, 86400 is tomorrow, etc.
With a small calculation, we can work out that Start Datetime – Relative Seconds needs to be -1814400 (60 (seconds) * 60 (minutes) * 24 (hours) * 7 (days ) * 3 (week) * -1 = 1814400). Don’t forget to add -1 multiplier otherwise, it will set the day in 3 weeks time in the future. End Datetime – Relative Seconds will be 0 as zero always refers to now.
Set dynamic filter values to select the last 3 weeks.
2.4 Assert Data Rules
The Assert Data Rules task runs logical checks on the dataset behind a specific worksheet, based on user-defined rules. You can run the following three checks in Wiiisdom Ops:
- Basic sanity checks (i.e. Nb. of Rows > 0; does it have any data?)
- Column-based rules (i.e. [Transactions] > 0 ; does a specific KPI makes sense?)
- Complex dynamic data validations using Excel-like formulas on the underlying data (i.e. [Net Sales Value] * (1 + [VAT]) = [Gross Sales Value]; do the calculated fields in the workbook work as expected?)
To check if today’s data is on the dashboard we will use the following formula: [DAY(Effective Date)] = TODAY(). Wiiisdom Ops will download the underlying data from the specified worksheet and will evaluate this formula in each row. If it doesn’t find any row where the formula is TRUE then today’s data is not on the dashboard and the test will fail.
Assert Data Settings to check if today’s data is available on the Tableau Dashboard.
You will, of course, need to tailor the formula to your requirements. For example, if you want to check yesterday’s data instead, or if you want to add that as an extra check just replace it or add another formula that looks like this: [DAY(Effective Date)] = TODAY() – 1. Also, be sure to use the column names that represent dates specific to your dashboard.
Step 3: Run, Schedule, And Integrate With External Tools
We are now ready for the first test run. Click on the start button and you will see that Wiiisdom Ops is running the testing steps without manual interactions:
Running the entire flow: Login to Tableau Server, opening viz, setting a filter, and checking today’s data.
The test passed, which means everything is as expected and today’s data is available on the dashboard. You can see this in the console and in the generated HTML report as well. Wiiisdom Ops saves the underlying raw data too as an artifact ( eval-formulas-YYYY-MM-DD_HHMMSS) in case you want to double-check row by row what data was under the worksheet at test run time.
Data validation results logged both into the console and into the generated HTML report.
Schedule and Integrate
Once you are happy with the data validations you will want to run it automatically, for example, every morning at 8 am. You can Schedule Test Runs from Windows Task Scheduler, Cron or you can trigger it from CI tools like Jenkins, Bamboo, etc. Also, if you want to inform people in your organization if something is wrong with the data, you can use the Wiiisdom Ops built-in integrations that will send messages via e-mail, Slack, Rollbar, or other third-party applications.
Ready To Automate?
Although Tableau allows you to check your dashboard data is up-to-date, it is a manual process that takes time and has the potential for errors. Thanks to Wiiisdom Ops, this can all be automated ensuring your Tableau dashboards are displaying the latest data and your business users can trust the data to make the best business decisions.