How to Test if Your Data is Up To Date on Your Tableau Dashboard?
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 update can fail from time to time, for instance, due to data not loaded into the data source, or extract refresh can fail in Tableau without an alert. On the other hand, presenting business decision makers and stakeholders with up to date is crucial for most organizations.
Within Tableau there is a workaround with Alert Based Date Calculations, however there are issues with this approach. First, you need to modify your Tableau document to add a new sheet and new calculated fields. Second, you need to create a scheduled subscription and deliver an email alert to certain recipients.
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? With Kinesis CI we will describe below a flexible and reliable way of testing if your data is up to date.
The below image details the steps you need to take to manually check if data is updated on a dashboard.
Steps when testing data update manually
To test if our data is up to date on our Tableau workbook in real life, we open the given viz in the browser, probably set a date range filter to cover today’s date and check if the required date is on the dashboard or not. Since we can set dynamic values for filters and can validate the underlying data with formulas in Kinesis CI, there is an easy way to automate this.
Step 1: Create the task flow
With Kinesis CI Functional Testing module you need to create an interaction flow simulating the actions that you are doing manually to check data availability.
Flow of interactions to check if today’s data 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 login to your Tableau Server
Open Viz: This will open the dashboard you want to test for today’s 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 when clicking on the Refresh button after opening the viz.
Turn on fresh data 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 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 Kinesis:
Basic sanity checks (i.e. Nr 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 the today’s data is on the dashboard we will use the following formula: [DAY(Effective Date)] = TODAY() . Kinesis CI 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 than 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 Kinesis is running the testing steps without manual interactions:
Running the entire flow: Login to Tableau Server, opening viz, setting filter and checking the today’s data
The test passed, that 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. Kinesis saves the underlying raw data too as an artifact ( eval-formulas-YYYY-MM-DD_HHMMSS) in case if 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:00 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 data you can use the Kinesis CI Built-In Integrations that will send messages to E-mail, Slack, Rollbar, or other third party applications.