Comparing a Tableau report to a database using QuerySurge
Summary
TLDRThis 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
📊 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.
🔍 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.
Mindmap
Keywords
💡Query Surge
💡Tableau Server
💡Economic Indicators
💡Inflation Rate
💡Data Visualization
💡Query Pair
💡Data Mart
💡Tableau Report Function
💡CAST Function
💡Validation Discrepancy
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
Посмотреть больше похожих видео
Dream Report: Acquiring Data from SQL Server
Using QuerySurge to Test & Validate Data in Microsoft Power BI
Create Advance PDF Reports in Oracle APEX Comprising Grouping | Chart | Pivot Table - Part 33
Dream Report: Acquiring Data using SQL Statements
Dream Report: Data Logging Configuration
Dream Report: Rockwell Connection Configuration
5.0 / 5 (0 votes)