However, two optional sheets of parameters can also be configured at this point: the FoundCriteria sheet and Softkeys sheets. This provides additionally flexibility and customisation in the definition of a Subset.
As described in the process overview, a Subset will run until either a maximum specified number of rows is created, a maximum number of recursions is reached, or the "Found Criteria" are fulfilled.
FoundCriteria are optional parameters that control when a subset will stop. They are specified in the Basic Control Spreadsheet, using the FoundCriteria sheet.
When a Subset "stops", no more rows will be added into the staging database.
If multiple FoundCriteria are supplied, Subsetting will stop when one FoundCriteria has been met.
The FoundCriteria sheet follows a similar format to the SQLCriteria sheet. Each FoundCriteria is specified in a new row. Each row includes the following columns:
Active: "Yes" or "No".
Table_Owner: An undelimited source owner/schema name.
Table_Name: An undelimited source table name.
Criteria: A criterion suitable for inclusion in a SQL WHERE clause.
Met: After a Subset is run, the automation will update this column to say whether the criteria have been met or not. The resultant cell will contain "Yes" or "No".
A blank FoundCriteria sheet therefore appears as follows:
An example FoundCriteria sheet appears as follows:
In this example, the Subset will run until it finds a given SalesReasonName in the DimSalesReason table. This SalesReasonName is specified as Substitution Parameter 2 in the Configuration spreadsheet, specified here as '%2' in the criterion.
Another example FoundCriteria is
"SELECT COUNT( * ) FROM stagingDB.stagingSchema.Table_Name WHERE "[ProductName]='Bikes'"
This FoundCriteria specifies that a Subet will run until a ProductName called "Bike" is found in the DimSalesReason Table of the schema.
TEST DATA AUTOMATION DATA SUBSETTING SUBSET JOB