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.

Recipe page select Recipe

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.

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

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

Validations

After a connection has been established, select a table.

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.
   
2. Select a Column from the drop-down list to display the Comparison Type option.
   
3. Select a Comparison Type from the drop-down list, and then enter the Comparison value.
   
4. Select Add to complete the 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

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

Dimension Validation

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

After configuring the connection, select a dimension 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.

The following column identifiers are available:

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 .

Range Check

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

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 1 and 100.

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.  

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

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.

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.

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 1 for the column SalesOrderNumber when grouped by the column CustomerPONNumber.

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.

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.
   
2. Select a Column to validate.
   
3. Select the appropriate format type from the Is dropdown.
   
4. Select Add to add the validation.

Add Column validation

Column 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.

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.