Defining Recipes (2.4)

Structure of a Recipe

Any recipe consists of some or all of the following steps, in order:

1. Staging activities. For each staging activity, you select a data source and then the schemas of interest from that data source.

   When the recipe is run, the data for each schema is loaded into a generated staging table. The system automatically uses the extraction strategy specified in the schema. In the recipe, however, you can choose which fields to copy.

2. Transformation activities, which transform fields in the staging tables. You choose how to transform the value, and you specify whether to replace the existing value or save the transformed value into a new field that you specify (in the same table as the original value).

3. Validation activities, which examine the values in the staging table or tables. For each fields that you choose to validate, you specify the validation function. The results of the validation checks are written to a validation report file. Validation errors can halt the recipe or can simply issue warnings, as you choose; in case of any validation error, the system generates a workflow task for the assigned role (specified when the recipe is scheduled).

4. Reconciliation activities, which compare values in different staging tables from the same run of the recipe. You specify how to match up records and you specify which fields to compare. Then for each comparison, you choose a comparison option. The results are written to a reconciliation report file. Reconciliation errors can halt the recipe or can simply issue warnings, as you choose; in case of any reconciliation error, the system generates a workflow task for the assigned role (specified when the recipe is scheduled).

5. Data promotion activities. For each data promotion activity, you define a set of steps, each of which uses SQL to update a final table, based on the contents of the staging table or tables.

A recipe can also include custom steps at each stage in the processing.

Staging Tables

As a preliminary step, a recipe involves loading data into generated staging tables. The names of these tables are generated, and the table name format is as follows:

Staging_recipeshort_stagingshort_vversion.schemaname

Where recipeshort is the Recipe Short Name, stagingshort is the short name of the staging activity, version is the version of the recipe, and schemaname is the name of the schema as seen in the Data Catalog. (Note that Staging is a default part of this name but is configurable.)

Any staging table includes the following fields:

• Fields maintained and used internally by the system: %IRISRowID, %BatchId, and %StagingAction. The %BatchId field contains the ID of the batch in which the given record was updated; you use this to identify the records to use in the data promotion activity.

• A set of fields whose names match the field names from the data that is loaded into that table; these fields are automatically added as needed.

• Any additional fields defined within the transformation, validation, and reconciliation activities. These field names start with the prefixes %T_, %V_, and %R_, respectively.

As you create a recipe, you may find it helpful to view the structure of the staging table, particularly when you define the promotion activities.

Creating a Recipe

To define a recipe:

1. Click the Recipes icon in the application menu.

   The system then displays the Recipes Dashboard, which lists all the recipes.

2. Click New Recipe in the upper right.

3. For Recipe Name, enter a descriptive name to display in the Recipes Dashboard.

4. For Recipe Short Name, enter a short name to be used within the names of the generated tables used by this recipe.

   The system then displays a page where you can add activities to the recipe.

Adding a Staging Activity

To add a staging activity to a recipe:

1. Display the recipe (from the Recipes Dashboard).

2. Click Add Staging Activity.

3. For Name, enter a descriptive name to display within the recipe.

4. For Short Name, enter a short name to be used within the names of the generated tables used by this recipe.

   If you are going to use only one data source in this recipe, you could use a generic short name like Load, but if you are going to use multiple data sources, it may be helpful for the short names to indicate the data source.

5. For Data Source, select a data source.

6. Click Submit.

   The system then displays a page where you can edit the details of how to use this data source. This page lists all the schemas that belong to this data source, and each schema represents a data asset such as a table, an specific API call, or a specific format of file. You can load data from any number of these data assets, within this staging activity.

7. For each schema of interest, click the schema name in the left. The right side of the page then displays information about the schema, including (if applicable) information about the last time data was loaded from the associated data source.

   Then specify the fields to load into the staging table. To do so, either click Select All Fields or click the check box for each field of interest.

8. When you are temporarily done with this activity, click Save Draft. Or, if you want to add activities that use the staging table, click Publish Activity, which generates the staging table.

9. Click the Recipe link in the upper left to return to the recipe.

Adding a Transformation Activity

To add a transformation activity to a recipe:

1. Display the recipe (from the Recipes Dashboard).

2. Click Add Transformation Activity.

3. For Name, enter a descriptive name to display within the recipe.

4. For Target Source, select a schema; the list includes all schemas used in published staging activities within the same recipe.

   The system then displays the fields in the associated staging table, with options to specify transformations for any of the fields.

5. For each field where you want to apply a transformation, select the field name.

   The system then displays options on the right side of the page. Make edits described below and then click Apply.

   • Function Type—Select one of the following; note that the available options depend on the type of field you are transforming:

     • Code Table Conversion—Transforms the data by using a code table, which provides a set of key/value pairs. If the value being converted matches a key in the given code table, that value is converted to the value associated with that key. Only for string fields.

     • Custom SQL Expression—Transforms the data by using an SQL expression that returns a single value.

     • Lowercase String—Converts to lowercase. Only for string fields.

     • Replace Substring—Performs a substring replacement. Only for string fields.

     • Trim Whitespace—Trims whitespace (leading, trailing, or both). Only for string fields.

     • Trim Characters—Trims a specified set of characters (leading, trailing, or both). Only for string fields.

     • Uppercase String—Converts to uppercase. Only for string fields.

     • Round Number—Rounds the number to the given number of decimal places. Only for numeric fields.

   • Overwrite—Select this if you want the new value to replace the old value. In this case, the system will not generate a new field in the staging table.

     Otherwise, leave Overwrite cleared and specify a new field name (see below).

   • Transformation Name—Specify a short name for this field transformation.

   • Result Field Name—Specify a name for the new field that will store this transformed value. In this case, the system will automatically add a field to the staging table; the actual name of the new field is %T_newname where newname is the name you specified.

   • Result Field Type—Select the type for the new result field.

   For the Code Table Conversion function, also specify:

   • Code Table Name—Specify the name of a table that contains key/value data.

   • Code Table Key Column—Specify the name of the field in that table that contains the keys.

   • Code Table Value Column—Specify the name of the field in that table that contains the associated values.

   For the Custom SQL Expression function, also specify SQL Expression as an SQL expression that returns a single value, such as an expression that combines field names and SQL operators. You must delimit any field name that is an SQL reserved word.

   For the Replace Substring function, also specify:

   • Old Substring— Type the text to be replaced.

   • New Substring— Type the replacement text.

   For the Trim Whitespace function, select Trim Leading Whitespace, Trim Trailing Whitespace, or both.

   For the Trim Characters function, specify one or more characters for Characters To Trim. Then select Trim Leading Characters, Trim Trailing Characters, or both.

6. When you are done editing this activity, click Save Draft. Or click Publish Activity so that the recipe will use it.

7. Click the Recipe link in the upper left to return to the recipe.

Adding a Validation Activity

To add a validation activity to a recipe:

1. Display the recipe (from the Recipes Dashboard).

2. Click Add Validation Activity.

3. For Name, enter a descriptive name to display within the recipe.

4. For Target Source, select the applicable data asset by choosing the schema that describes the asset. The list includes all schemas used in published staging activities within the same recipe.

   The system then displays the fields in the staging table, with options to specify validation rules for any of the fields. Note that any field can have at most one validation rule, within a given validation activity.

5. For each field where you want to apply a validation rule, select the field name.

   The system then displays options on the right side of the page. Make edits described below and then click Apply.

   • Function Type—Select one of the following:

     • Custom SQL Expression—In this case, the field value fails validation if an SQL predicate expression returns false. (You can, for example, compare the value in this field to the value in another field in the same record.)

     • Not Null—In this case, the field value fails validation if it is null.

     • Fixed Numeric Range—In this case, the field value fails validation if it falls outside of a fixed range of numeric values

   • Validation Name—Specify a short name for this field transformation.

   • Result Field Name—Specify a name for the new field that will store either 1 (if the field value passes the validation rule) or 0 (if it does not). The system will automatically add this field to the staging table; the actual name of the new field is %V_newname where newname is the name you specified.

     It is useful to use a name that indicates the validation check being used, for example CheckItemCount, which becomes %V_CheckItemCount in the staging table.

   • Validation Failure—Specify how the system should respond when it encounters a field value that fails this specific rule. A Fatal failure halts the recipe. A Warning failure logs a warning. Both kinds of failures are visible in the Workflow Inbox.

   For the Custom SQL Expression function type, also specify:

   • SQL Operator—Select a comparison operator.

   • SQL Expression—Enter a scalar SQL expression such as a field name or a constant.

   For the Fixed Numeric Range function type, also specify:

   • Minimum— Minimum permitted numeric value.

   • Maximum— Maximum permitted numeric value.

   • Tolerance—Specify the tolerance, or the amount by which the value is permitted to be outside of the specified range.

6. When you are done editing this activity, click Save Draft. Or click Publish Activity so that the recipe will use it.

7. Click the Recipe link in the upper left to return to the recipe.

Adding a Reconciliation Activity

A reconciliation activity uses comparison data that you have loaded (and if needed transformed and validated) within the same recipe.

To add a reconciliation activity to a recipe:

1. Display the recipe (from the Recipes Dashboard).

2. Click Add Reconciliation Activity.

3. For Name, enter a descriptive name to display within the recipe.

4. For Primary Source, select a schema—this indicates the data that you ultimately want to promote and use downstream.

   The area below Primary Source then lists the fields in this schema, including the field or fields that make up the primary key (shown with a special symbol).

5. For Secondary Source, select another schema—this indicates the comparison data to use.

   The area below Secondary Source then lists the fields in this schema.

   The purpose of this page is to create the key map, which specifies how the data will be compared. The key map specifies how to identify records to compare, and it outlines which pairs of fields hold the same or similar values.

6. The first step is to match up the primary keys so that the data can be compared record by record. The easiest way to do this is to click Auto Match Primary Keys. When you do so, the Key Map area shows the primary key fields from the primary source on the left, with the primary key fields from the secondary (comparison) source on the right.

   This indicates how the data will be compared: a record that has a specific primary key in the one source will be compared to the record that has the same primary key in the second source.

7. Now add fields to the comparison, as follows:

   1. Click a field name under Primary Source—this adds the field to the left column in Key Map area.

   2. Look for the field under Secondary Source that should be used for comparison for this field. Click that field name. The system adds this field to the right column in Key Map area.

      Or click Try to Match Selected Column. In this case, the system looks for an appropriately named field in the secondary source.

   To remove an item in the Key Map area, click its X button.

8. When you are done adding fields, click Create. The system then creates the key map for this reconciliation activity. If needed, you can return later and make edits.

   Now the page displays a table listing the fields from the two sources. Here you specify rules used to compare the values.

9. Briefly review the table for familiarity. The Primary Field column lists the fields from the primary source, and the Secondary Field column list the fields from the secondary source. This information is the same as in the key map. In addition, note that the Data Type field indicates the data types of the fields from the primary source (which is helpful in choosing a comparison function).

10. If many or most of the fields have the same name in the two tables, click Quick Match All to quickly create a set of reconciliation rules that you can then review and edit. With this option, the system automatically creates a rule for each pair of fields that have the same name (including case). Each automatically generated rule includes a unique rule name, a comparison function, a generated field name (to hold the comparison result), and (for numeric comparisons), an initial setting for Tolerance.

    Some fields may not yet have rules (which is OK).

11. Make edits (as needed) for each rule:

    1. In Function Type, choose a comparison function.

    2. For Rule Name, click the Edit icon and then type a unique rule name.

    3. For Result Field, click the Edit icon and then type a unique field name. The reconciliation activity will write a 1 or a 0 to this field, to indicate whether reconciliation was successful.

    4. For Tolerance (required for numeric comparison), click the Edit icon and then type the numeric tolerance.

    5. If failing this reconciliation check should be a fatal error (halting the recipe), select the Fatal check box.

12. When you are done editing this activity, click Save Draft. Or click Publish Activity so that the recipe will use it.

13. Click the Recipe link in the upper left to return to the recipe.

Viewing the Staging Table Details

Before you can add a promotion activity, it is helpful to review the structure of the staging table or tables used by the recipe:

1. Display the recipe (from the Recipes Dashboard).

2. At the bottom of the page, see the list in Indices and Staging Table Details.

   This section shows one row for each data source used in the recipe.

3. Click the row for a given data source. The page then lists the generated staging tables associated with this data source. For example:

   

4. Click a table name in this list. The right side of the page then provides details about the fields in this table, along with information about the indices on the table. For example:

   

5. Optionally add indices as needed. To add an index, click New Index and then specify an index name and select one or more properties to be indexed.

Adding a Promotion Activity

A promotion activity loads data from the staging table to a final table, generally by means of custom SQL. The final table must already exist within the system.

To add a promotion activity to a recipe:

1. Display the recipe (from the Recipes Dashboard).

2. Click Add Promotion Activity.

3. For Name, enter a descriptive name to display within the recipe.

4. For Run Order, select or type an integer, which controls the order in which the promotion activities are run.

5. Click New Promotion Item.

6. For SQL Expression, enter an SQL expression that updates the final table using data from the staging table.

   The right side of the screen provides samples to assist you in writing the query.

7. Click Create Activity Item.

8. If necessary, click New Promotion Item and add other promotion steps.

9. When you are done editing this activity, click Save Draft. Or click Publish Activity so that the recipe will use it.

10. Click the Recipe link in the upper left to return to the recipe.

Adding a Custom Activity

A recipe can include custom activities. For example, it may be necessary to have a custom activity that retrieves the data from a data source (for example, to place files within the file system). In such cases, two items are needed:

• Custom code in the form of a business host added within the interoperability engine that system uses internally. The business host must be a business operation or a business process that accepts as input a message of type SDS.DataLoader.CustomActivityRequest. Details are currently beyond the scope of the documentation.

• Configuration of the recipe to use that code at the applicable phase, as follows:

  1. Display the recipe.

  2. Click Add Custom Activity. This displays a dialog box where you specify the details.

  3. For Name, enter the name of this activity.

  4. For Target Business Host, enter the configuration name of the business host.

  5. For When to Run, select Before Staging, Before Transformation, Before Validation, Before Reconciliation, Before Promotion, or After Promotion.

  6. For Enabled, select this check box if the activity should be enabled.

  7. Click Submit.

Step Mode

You can run a recipe in step mode, a mode that lets you use a subset of data—perhaps a very small subset—so that you can test the recipe quickly from end to end. In this mode, you specify the number of rows of data to load into the staging tables (and then to use in the subsequent processing).

To configure a recipe for step mode:

1. Display the recipe.

2. Click the Step Mode toggle.

3. In the box next to Step Mode, select or type the number of records to use.

A recipe remains in step mode until you change the Step Mode toggle back to off.

See Also

• Editing and Managing Recipes

• Scheduling and Running Tasks

• Viewing Run History