Using QuerySurge to Test & Validate Data in Microsoft Power BI

Summary

This demo tutorial showcases how to use Query Search to extract data directly from Power BI visualizations for comparison with other BI reports or data stores. It guides through logging in, creating a query pair, and setting up a connection to Power BI. The process involves obtaining a group ID, report ID, page name, and visualization title from Power BI. A SQL query is then constructed using a special function to pull data, which can be used for complex operations like joins and aggregations within Query Search. The demo concludes with a test run showing real-time data extraction and comparison, highlighting the accuracy and potential of this tool for data analysis.

Takeaways

• 🔐 Start by logging into Query Search with an admin account.
• 📁 Select 'Query Pairs' and choose a folder to create a new query pair for Power BI data extraction.
• 💾 Name the query pair uniquely, such as 'Power BI', and save it to proceed.
• 🔗 Establish a connection to Power BI if not already set up, as it's essential for data querying.
• 🔍 Identify the Group ID from the Power BI workspace URL, which is crucial for locating the specific workspace containing the report.
• 📊 Find the Report ID by opening the specific report in Power BI and extracting the identifier from the URL.
• 📑 Obtain the Page Name from the report's pages list or by renaming the page in Power BI's edit mode.
• 📊 Copy the title of the specific visualization (e.g., a bar chart) that you want to extract data from.
• 💬 Use a SQL query with the 'power bi report' function to structure the data extraction from Power BI, inputting the Group ID, Report ID, Page Name, and Visualization Title.
• ⚙️ Run the query to extract data and compare it with other data sources or reports, ensuring the data matches the visualization in Power BI.

Q & A

What is the purpose of the demo on query, search, and Power BI?

The purpose of the demo is to show how to query data directly from a Power BI visualization to compare it against other BI reports or back-end data stores using query search.

How does one begin using query search in the demo?

To begin using query search, one logs in with an admin account, selects query pairs, chooses a demo folder, and creates a new query pair by clicking the 'create new query pair' button.

What is a query pair in the context of the demo?

A query pair is a set up in query search where one can start creating tests against Power BI visualizations.

What is required to set up a connection for Power BI in query search?

To set up a connection for Power BI, one needs to select a connection from the drop-down menu. If a Power BI connection hasn't been set up before, a video tutorial provided in the description can guide the user through the process.

What information is needed to query data out of Power BI?

To query data out of Power BI, one needs the group ID, report ID, page name, and the title of the visualization.

How is the group ID obtained in Power BI?

The group ID, which is a unique identifier for the workspace containing the report, can be found in the URL when selecting the workspace in Power BI.

What is the report ID and how is it located?

The report ID is an identifier added to the URL when opening the specific report in Power BI that one wants to test.

Why is the page name important when querying data from Power BI?

The page name is important because it helps to narrow down the exact page containing the visualization from which data needs to be extracted.

How does one extract the title of a visualization in Power BI?

One can extract the title of a visualization by clicking on the visualization, selecting 'format', and copying the title.

What is the SQL query structure used to extract data from Power BI visualizations?

The SQL query structure uses a simple SELECT statement with a special function called 'power bi report' that allows using the extracted information as a table for complex SQL operations.

How does the demo show the extraction process works?

The demo shows the extraction process by running the same query on both the source and target sides, which should match 100%, demonstrating how data is pulled from Power BI.

What does the final comparison of data in the demo reveal?

The final comparison reveals that the data extracted by query search matches exactly with what is seen in the Power BI visualization, confirming the accuracy of the extraction process.