DataOps Suite Sandbox for BI Validation

Getting Started – Sandbox Documentation

Introduction to DataOps Suite Sandbox – BI Validator

Welcome to the DataOps Suite Sandbox. Before getting started with the specific use cases you would like to try, we highly recommend going through this video and documentation.

This introduction is going to start with an overview of how test cases are created from scratch, followed by a few exercises. The solutions of the exercises are already present in the Sandbox for reference.

ETL Testcase Creation Flow

DataOps Suite has a full-fledged ETL tool as its backbone to support its ETL Validation capabilities. Thus, it helps to look at the product as kin to typical ETL tools.

Firstly, a source and target connection need to be defined to pull and push datasets with the Suite.

These connections are defined as “Sources” in the Sources Tab and can range from JDBC Connections to Flat Files to APIs and much more.

After the connections are defined, data can be pulled into Suite and then transformed, tested, compared, profiled, analyzed, and/or pushed back into a different target.  This is achieved in the Dataflows Tab.

 

After test cases (dataflows) are created, they can be bundled together, sequenced, and ran as Pipelines in Pipeline Tab. The Scheduler helps you schedule the pipeline run. All dataflow and pipeline runs have a History section to look back at previous runs. 

A vast majority of application uses are to follow here, such as:
  • REST API Connections to DataOps Suite for automation and integration
  • Parameterization
  • Plugins
  • Password Vault
  • CLI Commands
  • In-Suite Wizards for Automation of Test Case Creation

For these, we highly recommend tinkering with the application, going through the documentation, and connecting with our support desk (accessible via the application). We also provide a full fledge training certification for the Suite.

Sandbox Exercises

Before testing specific use cases, kindly attempt the following exercises in the order provided to understand the Suite better. We will be working with the following business use cases as the basis of our exercises.

Our architecture involves an SQL Server, a Tableau Connection, and a Power BI connection. We want to perform the following validation – 

  1. Report Upgrade and Regression on Power BI and Tableau Report
  2. Comparing a BI Report’s Visual’s data against a Dataset
  3. Applying Rules on the Reports for Validation

Source Validation

Note: The first step of the process, the creation of Sources, is already done in the Sandbox.

Before proceeding, kindly validate your Data Sources. To do so –

    1. Go to the “Sources” Tab
    2. Click on “Data Source(s)”
    3. Click on each source and click on “Test” (top-right)
    4. This will validate the connection

 

Post this; the exercise is focused on the “Dataflows” tab. Here you should see three flows already built as solutions for the exercises for reference.

Source Validation

Exercise 1 – Report Upgrade and Regression on Power BI and Tableau Report

Context – Validation of Reports is essential after upgrades and updates. Here, we cover both cases. The Environment has gone through multiple upgrades and updates. We now need to validate if the reports are still valid. We want to compare the reports against each other. In this case, these are Dev and Prod versions of the Sales Production Reports.

Create a new dataflow within the Dataflow Tab using the “Create Dataflow” button (top-right).

Nodes used – BI Upgrade Nodes

If you are not interested in a BI Node, feel free to remove it for clarity. Alternatively, you can choose not to run it. 

 

Power BI Report to Report Showcase
Power BI Report to Report Showcase
Tableau Report to Report Compare

Steps –

  1. Firstly, the steps are identical, irrespective of the BI tool of choice. Follow the steps with the BI tool of your choice.
  2. We will start with the upgrade test case first. Drag and drop the upgrade node of the BI tool of your choice.
    a. Select the connections for both Reports. In the sandbox, we provide a single connection, but in your specific use cases, you can use distinct connections for the same. Additionally, in the advanced options, you can choose the color and opacity of the mismatches.
    b.In the Reports sections, navigate through the workspaces and find the reports “Store Sales Dev Simple” and “Store Sales Production Simple” in the “Sandbox” workspace.
    c.Capture the bookmarks for both reports and Run the node
    d.Note the differences in the “Text” and “Appearance” options.

Results –

  1. While there are differences in the text indicating the distinction between the Dev and Prod versions, we see data aggregate differences in the European Market.
  2. Additionally, we also see the distribution for the Asian market also affected, Total Sales, and a few more aggregates mismatched.
  3. We also see that the alignment for the text “Sum of Sales by Ship Mode” has been shifted from center to left.

Exercise 2 – Comparing a BI Report’s Visual data against a Dataset

Context – Validation of the report must also be against the dataset the data was derived from. Here, we have a Power BI Report whose visual, particularly “Sum of Yearly Income by Total Children”, must be validated against its base dataset in SQL Server. [Sandbox Data Compare Exercise Report in Tableau and DimCustomer_CSV in Power BI ] 

Create a new dataflow within the Dataflow Tab using the “Create Dataflow” button (top-right).

Power BI Data Component
Power BI Data Component
Data Comparison Node
Data Comparison Node

Nodes used – Power BI, JDBC Connector, SQL Node, Data Compare Node

Steps – 

  1. Note that we are using Power BI as the basis in this example. The steps are identical for Tableau, and we have a use case dedicated to that in the Sandbox – “Tableau vs Data Compare” that you can use for reference.
  2. First, using the JDBC Connection, create a node and connect to SQL Server
    a. Pull the DimCustomer Table
  3. Then, using the Power BI node in Sources, connect to your connection and select the report page you want to work with.
  4. Select the exact visual you would like to pull out (in this case, it’s “Sum of Yearly Income by Total Children”.
  5. Before we compare the datasets, we need to aggregate the dataset from SQL Server.
    a. Add a SQL node and, using the Query Builder, build an aggregate that we want to compare with the data from the report.
  6. Add a Data Compare Node that compares the aggregated dataset from the SQL node and the Power BI. Note the results.
Results –
  1. We see that most of the aggregates match.
  2. The aggregate for Total Children 2 does not match.

Exercise 3 – Applying Rules on the Reports for Validation

Context – Based on business requirements, the report in question is “Sales Dev” in the “Sandbox” Workspace and must be validated on a few rules. The test is identical functionally for Tableau Report and Power BI Reports. Therefore, depending on the BI tool of your choice, choose the report “Store Sales Dev” in Power BI or “Report Validation Exercise” in Tableau.

The rules are as follows – 

  1. For Sales by Market Visual – 
     a. The “Market” column should have values ‘Europe’, ‘APAC’, ‘USCA’, ‘LATAM’, and ‘Africa’
     b. Sales should not be negative
     c. Sales should not be null
  2. For Sales by Ship Mode Visual – 
     a. There should be no duplicates for Ship Modes
     b. The total sales of this visual should match the total sales of the “Sales by Market” Visual.

Create a new dataflow within the Dataflow Tab using the “Create Dataflow” button (top-right).

Tableau Data Node
Data Rule Component
Data Rule Component

Nodes used – Tableau or Power BI Source, Data Rule Node

Steps – 

  1. First, using the BI Source connection of Tableau or Power BI, connect to the specific report in question.
    a. For Power BI –
    a. In the “Visuals” Tab, select the visuals whose data you want to pull.
    b. For Tableau –
    a. Select the “Worksheets” button on the right and select with worksheets you would like to pull.
  2. Create 2 Data Rule nodes and select the dataset for each before creating the rules for both.
    a. The application comes with multiple templates of rules. To resolve the business rules needed for this exercise, we need –
    a. Attribute Rule – Null and Negative Check
    b. Domain Rule – List of Values Check
    c. Duplicate Rule – Duplicity Check
    d. Metric Compare Rule – Aggregate Check
Results –
  1. Out of the 5 rules, 3 pass. Null check, Negative check, and Duplicate Check.
  2. The Market column has also failed.
  3. The total sales aggregate from both visuals does not match.

Conclusion

Thank you for completing the exercises. With the basics covered, you and your team should better understand the application’s plethora of nodes and toolsets. As a final test, feel free to apply data observability to your reports and witness the true power of BI Validator. 

Feel free to tinker with the environment to fit your specific use cases and explore all the nodes and applications. The application can also be connected via CLI Commands, REST APIs, and integrated plugins. Our documentation, which can be easily accessed in the bottom left corner of the Suite, will provide your team with ample starting resources. The application comes with wizards, pipelines, and monitoring capabilities that new users can easily configure and deploy. If you need any specific examples, feel free to contact us directly.  

In case of any questions or errors that your team cannot resolve, feel free to contact our support team either by contacting us on our email or using the support portal present in the bottom left corner of the Suite. 

Subscribe us to get updates about our Product Enhancements, Newsletters, Webinars and more

By subscribing you’re allowing data graphs and/or its associates to reach you with periodic informative updates.

Download Datasheet
Download Datasheet
Download Datasheet
Download Datasheet
Download Datasheet

Data Quality

Automate testing of Business Intelligence applications by making use of the metadata available from the BI tools such as Tableau, OBIEE, and Business Objects.

Synthetic Data

Automate testing of Business Intelligence applications by making use of the metadata available from the BI tools such as Tableau, OBIEE, and Business Objects.

ETL Testing

Automate testing of Business Intelligence applications by making use of the metadata available from the BI tools such as Tableau, OBIEE, and Business Objects.

BI Validation

Automate testing of Business Intelligence applications by making use of the metadata available from the BI tools such as Tableau, OBIEE, and Business Objects.
Products

product_menu_icon01

DataOps Suite

End-to-End Data Testing Automation

product_menu_icon02

ETL Validator

Automate your Data Reconciliation & ETL/ELT testing

product_menu_icon03

BI Validator

Automate functional regression & performance testing of BI reports

product_menu_icon04

DQ Monitor

Monitor quality of data being Ingested or at rest using DQ rules & AI

product_menu_icon05

Test Data Manager

Maintain data privacy by generating realistic synthetic data using AI

About
Free Trial