SentryOne Test Visual Studio Extension Recipes

SentryOne Test Recipes are a simple way to create tests based around smaller pieces of functionality and help you get quickly started with testing.

Important:  Recipes do not support connections to an Oracle Database.

SentryOne Test Recipe selection
Recipe page select Recipe

SentryOne Test Recipe connection
Recipe page select Connection

The Recipe page allows you to choose the recipe that you want to use to create a test. If the recipe requires any connections you can specify those connections, either using an existing asset or configuring a new connection.SentryOne Test Recipe Configuration page

The Configuration page is specific to the recipe. This is an example of the Uniqueness recipe, in which we select a table to check and the columns which form the unique key. For more information about the individual recipes, see the individual recipe below.

Data Profile Validation

Data Profile Validation

The Data Profile Validation allows you to test column data based on user-defined rules.

Validations

SentryOne Test Data Profile Validation recipe Choose table

After a connection has been established, select a table.

SentryOne Test Data Profile Validation recipe Columns Validations

Once a table is selected, the Column Validation settings become visible. 

To create a validation, complete the following steps:

  1. Select New to display the Column and Comparison value options.
    SentryOne Test Data Validation recipe New Column Validation
  2. Select a Column from the drop-down list to display the Comparison Type option.
    SentryOne Test Data Profile Validation select Column
  3. Select a Comparison Type from the drop-down list, and then enter the Comparison value.
    SentryOne Test Data Profile Validation recipe select Comparison Type and Comparison value
  4. Select Add to complete the Column Validation.
    SentryOne Test Data Profile Validation Add Column Validation

More than one validation can be added to column(s). In the example above, validation has been added to the BirthDate column is Greater Than 0

Advanced tab

SentryOne Test Data Profile Validation recipe Advanced tab

Select the Advanced tab to create a WHERE clause to filter the data. 

Note:  Do not add the WHERE clause, just add the filter criteria. For example, BusinessEntityID >= 500 AND BusinessEntityID <= 1500.

Dimension Validation

Dimension Validation

The Dimension Validation allows you to test their dimension tables for inconsistent data.

After configuring the connection, select a dimension table.

SentryOne Test Dimension Validation recipe Choose table

Once a table is selected, identify each column based on its type. For example, Surrogate Key, Business Key, SCD Type 1 or Type 2 change, etc.

SentryOne Test Dimension Validation Recipe Identify Columns

The following column identifiers are available:

  • Fixed
  • Type 1
  • Type 2
  • Surrogate Key
  • Business Key
  • Active Indicator
  • Start Date
  • End Date
  • Not Used

You also need to select the Effective Date Handling and the Gap Tolerance. (None or End date equals start date of next version) and the Gap Tolerance .

SentryOne Test Dimension Validation Effective Date Handling and Gap Tolerance

OptionDescription
Effective Date HandlingThe following options are available:
  • None 
  • End date equals start date of 
Gap ToleranceThe amount of time allowed between the expired record and the start of the new record.
Range Check

Range Check

The Range Check recipe allows you to select a table and column to check that all values fall within a specific range.

SentryOne Test Range Check recipe

In the example above, we are checking that the table dbo.DimReseller does not have any values in the MinPaymentAmount column that are not between and 100.

Row Count Comparison

Row Count Comparison

The Row Count Comparison recipe allows you to generate a test that compares row counts between databases. 

For this recipe, select two connections and then configure the tables to map.  

SentryOne Test Row Count Comparison recipe

Note:  By default, all tables with the same name are mapped automatically.

Select Add to add new tables, and then select the source and target tables using the drop-down boxes.

SentryOne Test Row Count Comparison recipe Add tables

Configure optional filters for both sides. In the example below only columns for the table dbo.DimOrganization are included where PercentageOfOwnership is greater than 0.

SentryOne Test Row Count Comparison recipe optional filters

Note:  Filters are any valid SQL syntax that can be placed in the WHERE clause.

Additionally, we can create a data-driven test where the row count comparison for each individual table is created as it's own test case by selecting Create a data-driven test with a case for each table.

SentryOne Test Row Count Comparison recipe Data driven test case for each table

Standard Deviation

Standard Deviation

The Standard Deviation recipe allows you to check that the standard deviation for a column is below a given maximum, optionally specifying columns to group by.

In the example below, we are checking that the table dbo.FactInternetSales does not have a standard deviation over for the column SalesOrderNumber when grouped by the column CustomerPONNumber.

SentryOne Test Standard Deviation recipe

Text Format Validation

Text Format Validation

The Text Format Validation allows you to validate columns that use a common format such as email, phone numbers, and state code patterns. 

After configuring the connection, select a table.

SentryOne Test Text Format Validation recipe Choose table

Once a table is selected, the Column Validation settings become visible. Create a validation by completing the following steps:

  1. Select New to display the Column selector drop-down list.
    SentryOne Test Text Format Validation recipe New validation
  2. Select a Column to validate.
    SentryOne Test Text Format Validation recipe Choose column
  3. Select the appropriate format type from the Is dropdown.
    SentryOne Test Text Format Validation recipe Is comparison selection
  4. Select Add to add the validation.
SentryOne Test Text Format Validation recipe Add column validationAdd Column validation
SentryOne Test Text Format Validation recipe column validation addedColumn validation added

In the example below, the Phone column's text format is being checked to ensure it follows the North American phone number format.

SentryOne Test Text Format Validation recipe example

Uniqueness

Uniqueness

The Uniqueness recipe allows you to select a table to check for duplicates, and select the columns that make up the unique key for that check.

In the example below, we are checking that the table dbo.DimAccount does not have more than one row for each AccountKey.

SentryOne Test Uniqueness recipe