Skip to main content

Defining Recipes

Recipes describe how to load data from external sources into InterSystems TotalView™ For Asset Management, specifically data for schemas contained in the Data Catalog. Once you have defined and published recipes, you can run them, either on a schedule or manually, thus loading data into the system.

This page discusses creating recipes; another page has details on managing recipes and using the Recipe Dashboard.

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. For each transformation activity, you select a schema (from the set used in the staging activities). Then, for any fields in that schema, you specify any transformations to apply to the staging table after the data is loaded. A transformation can replace an existing value in a field or can insert a value into a new field that you specify, also in the same table.

  3. Validation activities. For each validation activity, you select a schema (from the set used in the staging activities). Then, for any fields in that schema, you specify a validation function to use. The result of the validation checks are saved in new fields that you specify, all in the staging table. Validation errors can halt the recipe or can simply issue warnings, as you choose.

  4. Reconciliation activities. For each reconciliation activity, you select a schema (from the set used in the staging activities) and you also select a secondary source (another schema, also from the set used in the staging tables) that contains comparison data. Then you specify the comparisons that define a valid reconciliation of the data.

  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 an intermediate 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 on the left.

    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, 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 transformations for any of the fields.

  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 the 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 result of this validation is transformed value. 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 indicates the validation check being used, for example CheckItemCount, which becomes %V_CheckItemCount in the staging table.

    • Validation Name—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:

    sample data soruce name, followed by a list of 7 tables

  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:

    Staging Tables Indices list followed by list of 3 indices, then Staging Table fields, followed by the list of fields

  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

FeedbackOpens in a new tab