The Configuration sheet

Search Knowledge Base by Keyword

< Back

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:

Data Subsetting

Control ParameterDescriptionNotesExampleVariable
Database ConnectionThe 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 DatabaseThe database from which data will be subsetted.SampleCommercevarSourceDatabase
Source SchemaThe 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.dbovarSourceSchema
Staging DatabaseThe 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.AdventureWorksvarStagingDatabase
Staging SchemaThe 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.subsetvarStagingSchema
Max RowsThe maximum size, in rows, of the subsetted data.Optional. If not set this is defaulted to 1,000,0002000000varMaxRows
Max IterationsSubsetting 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 59varMaxIterations
Substitution parameter 1This will give a value which will be substituted in SQL Criteria or Found Criteria where “%1” is specified.Optional.BikesvarSubParm1
Substitution parameter 2The substitution value for when “%2” is used as part of the formulae in the SQLCRITERIA or FOUNDCRITERIA sheets.Optional.On PromotionvarSubParm2
Substitution parameter 3The substitution value for when “%3” is used as part of the formulae in the SQLCRITERIA or FOUNDCRITERIA sheets.Optional.varSubParm3
Substitution parameter 4The substitution value for when “%4” is used as part of the formulae in the SQLCRITERIA or FOUNDCRITERIA sheets.Optional.varSubParm4
Substitution parameter 5The 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:

Data Subsetting