The FOUNDCRITERIA Sheet – Specifying any Found Criteria

Search Knowledge Base by Keyword

< Back

Once you have completed the Configuration sheet and SQLCRITERIA sheet, you are ready to perform a Basic Subset.

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:

  1. Active: “Yes” or “No”.
  2. Table_Owner: An undelimited source owner/schema name.
  3. Table_Name: An undelimited source table name.
  4. Criteria: A criterion suitable for inclusion in a SQL WHERE clause.
  5. 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:

Data Subsetting

An example FoundCriteria sheet appears as follows:

Data Subsetting

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.