The first step in any data subset is to define the criteria for the data that you want to Subset, and the target location into that you want to move the subsetted data.

These initial parameters are set in the Configuration sheet.

The Configuration sheet must contain the following Control Parameters:

  1. Database Connection: The database that you will connect into, and any credentials necessary for the connection.

  2. Source Database: The database from which data will be subsetted.

  3. Source Schema: The schema or schemas from which data will be subsetted.

  4. Staging Database: The target database into which Subset will be copied. This can be the same as the Source Database, or can be different.

  5. Staging Schema: The schema or schemas into which the Subset will be copied.

Nb.: The connection must have authority to read from the Source Database and to create tables and indexes in the target database. The same connection must be able to insert into the target database.
There are three optional Parameters:

  1. Max Rows: This specifies the maximum size, in rows, of the subsetted data.

  2. Max Iterations: Subsetting can "iterate", re-running a defined Subset to hunt for additional data each time. The Subset job will end once the maximum number of iterations has been reached.

  3. Substitution Parameters: Substitution Parameters can be referenced in the SQL criteria specified under the SQLCriteria and FoundCriteria Up to five Substitution Parameters can be specified in the Configuration Sheet. They can then be referenced in the SQL Criteria and Found Criteria formulae. When "%1" is used in the formula, Substitution Parameter 1 will be used when that criteria is executed by the Subset Actions. When "%2" is included, Substitution Parameter 2 will be used. The same applies for "%3" (Substitution Parameter 3), "%4" (Substitution Parameter 4), and "%5" (Substitution Parameter 5).

Each Control Parameter is specified as a row the Configuration sheet. A blank, template Configuration sheet therefore appears as follows:

Control Parameter

Description

Notes

Example

Variable

Database Connection

The database that you will connect into, and any credentials necessary for the connection.

The format of this connection will vary by database and depends on your own environment.

server=MARTIN-MF424OK\SQL16;user id=sa;password=Password;

varConnectionString

Source Database

The database from which data will be subsetted.

 

SampleCommerce

varSourceDatabase

Source Schema

The schema or schemas from which data will be subsetted.

If more than one source schema is required supply a list delimited by a semi-colon.

dbo

varSourceSchema

Staging Database

The schema or schemas into which the Subset will be copied.

The staging database can be different to the source database, but both must be accessible to the supplied connection.

AdventureWorks

varStagingDatabase

Staging Schema

The schema or schemas into which the Subset will be copied.

If more than one staging schema is required supply a list delimited by a semi-colon.

subset

varStagingSchema

Max Rows

The maximum size, in rows, of the subsetted data.

Optional. If not set this is defaulted to 1,000,000

2000000

varMaxRows

Max Iterations

Subsetting can "iterate", re-running a defined Subset to hunt for additional data each time. You can set the following maximum number of iterations: 1, 2, 3, 4, 5. The Subset job will end once the maximum number of iterations has been reached.

Optional. If not set this is defaulted to 5

9

varMaxIterations

Substitution parameter 1

This will give a value which will be substituted in SQL Criteria or Found Criteria where "%1" is specified.

Optional.

Bikes

varSubParm1

Substitution parameter 2

The substitution value for when "%2" is used as part of the formulae in the SQLCRITERIA or FOUNDCRITERIA sheets.

Optional.

On Promotion

varSubParm2

Substitution parameter 3

The substitution value for when "%3" is used as part of the formulae in the SQLCRITERIA or FOUNDCRITERIA sheets.

Optional.

 

varSubParm3

Substitution parameter 4

The substitution value for when "%4" is used as part of the formulae in the SQLCRITERIA or FOUNDCRITERIA sheets.

Optional.

 

varSubParm4

Substitution parameter 5

The substitution value for when "%5" is used as part of the formulae in the SQLCRITERIA or FOUNDCRITERIA sheets.

Optional.

 

varSubParm5

An example, completed Configuration sheet therefore looks as follows:

The SQLCRITERA Sheet – Setting the basic Subset Criteria

The Basic Control Spreadsheet

TEST DATA AUTOMATION DATA SUBSETTING SUBSET JOB