Comparing a Tableau report to a database using QuerySurge

Summary

This video tutorial demonstrates how to use QuerySurge to validate the data in a Tableau report. The presenter navigates through a Tableau server to access an 'Economy' report, focusing on validating negative inflation rates. They compare the data displayed in the report to the backend database using a query pair in QuerySurge, which includes a source query connecting to the data mart and a target query connecting to the Tableau server. The tutorial guides viewers on configuring the 'tableau report' function with the correct report and visualization names from Tableau. The process concludes with running the query pair to validate and compare the data, identifying a discrepancy in the inflation rate for 1938 between the database and the report.

Takeaways

• 🌐 The video demonstrates how to use QuerySurge to validate the contents of a Tableau report.
• 📊 The report in focus is an economy report displaying ESPY economic indicators with a focus on years with negative inflation rates.
• 🔍 To validate the data, the presenter shows how to download the data from Tableau and compare it with the backend database.
• 📝 The video explains the setup of a query pair in QuerySurge, which includes a source query and a target query.
• 💾 The source query connects to a data mart and retrieves years with negative inflation rates, while the target query connects to Tableau Server.
• 🔗 The 'tableau report' function in the target query requires the report name and visualization name from the Tableau instance URL.
• ⚙️ The target query uses a 'CAST' function to match the data format from Tableau, which returns data in string format.
• 🆚 The validation process compares the data from the source and target queries to check for discrepancies.
• 🚫 The video identifies a failure in the validation process for the year 1938, highlighting a discrepancy between the backend database and the Tableau report.
• 🔧 The presenter suggests taking the identified discrepancy back to the development team for further investigation.

Q & A

What is the main purpose of the demonstration in the video?

The main purpose of the demonstration is to show how to use QuerySurge to validate the contents of a Tableau report, specifically to check for any negative inflation rates.

How does the presenter access the Tableau report for demonstration?

The presenter accesses the Tableau report by navigating to a browser that connects to the Tableau server and then brings up a specific report called 'economy'.

What visual indicator in the report represents negative inflation rates?

Negative inflation rates in the report are represented by red bars in the visualization.

What action does the presenter take to view the data behind the graph in Tableau?

The presenter clicks the 'Download' button in Tableau and then clicks the 'Data' button to view the data behind the graph.

What are the two components of a query pair in QuerySurge?

A query pair in QuerySurge consists of a source query and a target query.

What does the source query in QuerySurge connect to?

The source query connects to the data mart, which is the database that stores the information represented by the Tableau report.

How does the presenter configure the target query in QuerySurge to connect to Tableau?

The presenter configures the target query to connect to the Tableau server and uses a function called 'tableau report' with parameters for the report and visualization names.

What information is required for the 'tableau report' function in QuerySurge?

The 'tableau report' function requires the report name and the visualization name from the Tableau instance.

How does the presenter identify the report and visualization names in Tableau?

The presenter identifies the report name by taking the portion of the URL after the server that specifies the report name, and the visualization name by copying the sheet name from the 'Rename' option in the Tableau interface.

What does the presenter do to investigate a discrepancy found during the validation process?

The presenter investigates the discrepancy by viewing the query results, analyzing the failure, and comparing the data from the backend system with the report's display.

What is the final step the presenter suggests after identifying a discrepancy?

The final step suggested is to take the discrepancy back to the development team to track down and resolve the issue.

Outlines

00:00

📊 Validating Tableau Report with Query Search

The speaker begins by demonstrating how to use QuerySurge to validate the contents of a Tableau report. They navigate to a browser connected to the Tableau server and open a specific report named 'economy' which displays ESPY economic indicators. The focus is on validating the inflation rate for years with negative values, represented by red bars in the visualization. The speaker explains how to download the data from Tableau to view the actual values behind the graph. To compare this data with the backend database, they access QuerySurge and open a query pair called 'negative inflation rate'. This query pair consists of a source query connecting to the data mart (the database) and a target query connecting to the Tableau server. The source query selects years and average inflation rates from the economic indicators table where the rate is less than zero. The target query is configured to mimic the source query but pulls data from the Tableau server using a function called 'tableau report'. This function requires the report name and visualization name from the Tableau instance, which the speaker extracts from the Tableau URL and the edit button in Tableau, respectively. The purpose is to validate the data from the backend database against the data rendered in the Tableau report.

05:03

🔍 Analyzing Query Results and Discrepancies

After setting up the query pair in QuerySurge, the speaker saves the changes and proceeds to run the query. The process involves making requests to both the data mart and the Tableau server, pulling data from each, and then validating and comparing the two result sets. The outcome shows 12 records returned from both the source and target, indicating 12 years with negative inflation rates. However, there is one failure noted in the validation process. To investigate, the speaker views the query results and analyzes the failure by comparing the source and target results side by side. They discover a discrepancy for the year 1938 where the backend system expects an inflation rate of around 9%, but the Tableau report displays only 2%. The speaker verifies this discrepancy by checking the report for the year 1938, confirming the issue. The final step suggested is to report this discrepancy to the development team for further investigation. This demonstrates the use of QuerySurge to validate and ensure the accuracy of content within a Tableau report.

Keywords

💡Query Surge

Query Surge is a data validation tool that allows users to compare data from different sources to ensure accuracy and consistency. In the video, it is used to validate the contents of a Tableau report by comparing data from a backend database with the data displayed in the report. The tool is crucial for ensuring that the visual representations of data in Tableau are accurate reflections of the underlying data.

💡Tableau Server

Tableau Server is a platform that allows for the creation, sharing, and collaboration on data visualizations and reports. In the context of the video, the server is where the specific report being validated is hosted. The video demonstrates how to navigate to this server and access a particular report, which is central to the validation process being explained.

💡Economic Indicators

Economic indicators are quantitative measures that provide insights into the economic performance of a country or region. In the video, the report being validated displays ESPY economic indicators, and the focus is on validating the inflation rate. The inflation rate is a key economic indicator that reflects changes in the general price level of goods and services.

💡Inflation Rate

The inflation rate is a measure of how the general price level of goods and services in an economy changes over time. In the script, the presenter wants to validate the inflation rate for years that had a negative value, which indicates deflation. This is a critical step in ensuring the accuracy of economic data presented in the Tableau report.

💡Data Visualization

Data visualization refers to the graphical representation of information and data. In the video, the Tableau report contains a visualization that displays economic indicators, including the inflation rate, which is represented by red bars. The visual representation is essential for quickly understanding complex data, and the video aims to ensure that these visuals accurately reflect the data.

💡Query Pair

A query pair in Query Surge consists of a source query and a target query. The source query connects to the data source (e.g., a database) to retrieve the actual data, while the target query connects to the Tableau server to retrieve the data as represented in the report. The video demonstrates how to set up these queries to validate the data against the Tableau report.

💡Data Mart

A data mart is a subject-oriented, integrated, and nonvolatile data repository used for reporting and data analysis. In the video, the source query is set up to connect to a data mart, which stores the economic indicators data. The data from the data mart is then compared with the data in the Tableau report to ensure consistency.

💡Tableau Report Function

The Tableau Report function is a specific function used in the target query to retrieve data directly from a Tableau report. The function requires parameters such as the report name and the visualization name to accurately fetch the data. This function is key to the validation process as it allows for a direct comparison between the backend data and the report data.

💡CAST Function

The CAST function in SQL is used to convert data from one data type to another. In the video, the CAST function is used to convert the data retrieved from the Tableau server, which is in string format, to match the data type of the backend database. This ensures that the data can be accurately compared during the validation process.

💡Validation Discrepancy

A validation discrepancy occurs when there is a difference between the expected data and the actual data. In the video, a discrepancy is found between the backend system's expected inflation rate and the rate displayed in the Tableau report for the year 1938. Identifying and addressing such discrepancies is a critical part of ensuring data accuracy and integrity.

Highlights

Demonstration of using QuerySurge to validate Tableau report contents.

Navigation to a browser connected to a Tableau server.

Introduction of a specific report called 'Economy'.

Description of the report displaying ESPY economic indicators.

Objective to validate the inflation rate for years with negative values.

Explanation of red bars representing negative inflation rates.

Process to view the actual data behind the graph in Tableau.

Introduction to QuerySurge and the concept of query pairs.

Opening a query pair called 'Negative Inflation Rate'.

Explanation of source and target queries in QuerySurge.

Description of the source query connecting to the data mart.

Details of the target query connecting to the Tableau server.

Configuration of the Tableau report function with two parameters.

Method to obtain the report name from the Tableau instance URL.

Method to obtain the visualization name from the Tableau report.

Process of saving changes and running the query pair in QuerySurge.

Explanation of how QuerySurge validates and compares data from both queries.

Identification of a discrepancy between the database and the report for the year 1938.

Recommendation to report the discrepancy to the development team.

Transcripts

[Music]

[Music]

hello everyone what I'd like to

demonstrate for you today is how to use

query Serge to validate the contents of

a tableau report the first thing I'm

going to do in order to demonstrate this

is to navigate to a browser that's

connecting to our tableau server and I'm

going to bring up a specific report in

this case called economy and you can see

the details of this report displays the

ESPY economic indicators and the tests

that I'd like to create is I'd like to

validate the inflation rate for any year

that had a negative value so you can see

that's represented by the red bars

displayed on the visualization so I'd

like to validate these values if I hover

over you'll see the tooltip will give me

the year and the rounded inflation rate

if I want to see the actual data that's

behind this graph what I can do is I can

click the download button in tableau and

click the data button and that will show

you the values that are being

represented in the graph that we're

looking at so I'd like to compare the

data here to the data that's being used

to render the report from the backend

database so to do that let's navigate

over to query search and we're going to

go to our query pairs folder and I'm

going to open up the query prayer called

negative inflation rate you'll see that

each query pair has a source query and a

target query the source query is set up

to connect to our data Mart that is the

database that stores the information

that's being represented by the tableau

report and you'll see it's a pretty

basic query we have a select clause

we're asking for the year and the

average inflation fête value

from the economic indicators table where

the average inflation bait value is less

than zero and we're going to organize

the data by the year in a sending order

on the target side we set up a

connection to our tableau server you can

review how this connection is configured

in one of our previous videos and you

can see the query for the tableau server

connection looks like a standard sequel

request we have a select clause we're

requesting again the year of date and

the average value but instead of

querying it from a specific database

table we have a function here called

tableau report and this function has two

parameters which we're going to show you

how to configure in a moment we have the

same filtering and the same sorting

mechanism using the where and order by

clause notice that we're using a cast

function to take the data because the

data coming back from tableau is always

going to be in a string format so you

want to cast it to match it to the

values that are going to be represented

against the backend database now the

tableau report function will require two

values it will require the information

to know which report you're referencing

as well as the visualization that's

displayed on that report you can get the

report by going to your tableau instance

and taking the portion of the URL that

specifies the report name so anything

past the server it's a view in this case

views regional economy I'm going to take

copy that out of tableau and I'm going

to put that into my function and the

second parameter is the visualization

that's being displayed on that specific

report so in this case we want the

visualization called SNP economic

indicators you can get the specific

visualization name by clicking the edit

button in tableau and what you want is

the corresponding sheet name that is

displayed when you select it so you can

see in this example

Espie returns by decade highlights this

graph which is what I'm looking for so

if you right click and hit rename you

can copy the name directly from this

window and then I can take that and

bring it back into our query search

instance

and that's pretty much it once you've

created both queries you specified the

information on the tableau report you

want you'd make your Save Changes and

then you're ready to go ahead and run

the query pair so what the query is down

doing is it's making a request to the

data Mart pulling that data back

simultaneously making a request to the

tableau server pulling that information

back and then validating the two results

sets and comparing to data and what

you'll see in this case is we had 12

records returned both from the source

and target which means there were 12

years that had negative inflation rates

and you see we had one failure so in

order to investigate that failure we're

gonna click the view query results

button and I'm gonna make this a little

bit larger so we can see it so you have

your source results your target results

and then you can analyze the failure by

clicking the failures tab and I'm gonna

click on the side-by-side view and what

you'll see is we have an error for the

year in 1938 the backend system is

expecting an inflation rate of around 9%

but the report is only displaying an

inflation rate around 2% and of course

we can validate that by taking a look at

the report going to the year 1938 and

you'll see it's only 2% so we have a

discrepancy between the database and the

report and of course what we'd have to

do is take this discrepancy back to the

development team and see if we can track

down where there is a problem and that

demonstrates how you can use query

search to validate content in a tableau

report