The SQLCRITERA Sheet – Setting the basic Subset Criteria

Search Knowledge Base by Keyword

< Back

The SQLCriteria Sheet provides the basic criteria that will drive your Subset.

Each criterion is specified as a row in the spreadsheet.

Each criterion is applied to a given table, that must be specified. Each row further requires a Table Owner. A Table Owner is an undelimited source owner or schema name.

The criterion must each be suitable for inclusion in an SQL WHERE Clause.

Each row can additionally specify whether the criterion will be active or not during the subset. This enables you to maintain a master set of criteria for a given database, quickly toggling between re-usable criteria.

Finally, you can set whether the criteria should include children or not.

The columns for each row in the SQLCriteria sheet are therefore as follows:

  1. Active: “Yes” or “No”.
  2. Table_Owner: An undelimited source owner/schema name. For instance, the default SQL Server owner is “dbo”.
  3. Table_Name: An undelimited source table name.
  4. Criteria: A criterion suitable for inclusion in a SQL WHERE clause, for example “[ProductName]=’%1’”
  5. IncludeChildren: “Yes” or “No”. This specifies whether Child tables will be included in the Data Subset.

A blank SQLCriteria sheet therefore appears as follows:

Data Subsetting

A complete criterion appears as follows:

VIP Subset 3.4

This example criterion will subset all entries in the Dimension Table for Product Category that have a given EnglishProductCategoryName.

The EnglishProductCategoryName is specified as the Substitution Parameter 1 in the Configuration Sheet. This is denoted in the above criterion by ‘%1’

Below is a more complex example, demonstrating the use of Children columns.

Data Subsetting

In this example, there is a Fact table with multiple child Dimension tables. The Subset criterion specifies that the Subset will pull a specific Sales Order Number from the Fact table named “FactInternetTables”. The Subset will additionally pull the data relevant to the subsetted records from 17 child Fact tables.