The Configuration sheet
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:
Database Connection: The database that you will connect into, and any credentials necessary for the connection.
Source Database: The database from which data will be subsetted.
Source Schema: The schema or schemas from which data will be subsetted.
Staging Database: The target database into which Subset will be copied. This can be the same as the Source Database, or can be different.
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:
Max Rows: This specifies the maximum size, in rows, of the subsetted data.
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.
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:
