Defining Recipes (2.13)

Creating a Recipe

To define a recipe:

1. Click the Recipes icon in the application menu.

2. Click New Recipe .

3. For Recipe Name , enter a descriptive name to display in the Recipes Dashboard. This name cannot be changed later.

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

5. For Recipe Group , optionally select the name of a recipe group .

   You can move this recipe later to another group or remove it from the group.

6. Click Submit .

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

Adding a Staging Activity

A staging activity populates one or more staging tables. To add a staging activity to a recipe:

1. Display the recipe:

   1. Click the Recipes icon in the application menu.

   2. If the recipe is in a group, expand that group.

   3. Click the recipe name.

      The recipe is then displayed on the right side of the page.

2. In the Staging Activities table, click Add 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. This name cannot be changed later.

   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 .

   This selection cannot be changed later.

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 table, a specific API call, or a specific format of file. You can load data from any number of these items, within this staging activity.

7. For each schema that you want to include in this staging activity:

   1. Click the schema name on 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.

   2. Configure the schema options as needed. For example, you can select Stage on Target Table or Use SQLQuickload. For details about these options, see Staging Activity.

   3. Select the fields to load.

8. When you are temporarily done with this activity, click Save Draft . Or if you are done defining the activity, click Publish Activity .

   When you save the draft for the first time, the Staging Table field is updated to show the name of the staging table . The table is not generated until you publish the activity. If you want to add additional activities that use the staging table, it is necessary to publish the staging activity.

   Caution:

   After you publish the activity, you must be granted access to the generated staging table by an administrator before you can create any additional activities in the recipe that depend on that staging table. Contact your administrators to update your role privileges as needed.

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

Adding a Transformation Activity

A transformation activity transforms data in a staging table. To add a transformation activity to a recipe:

1. Display the recipe.

2. Make sure that you have published the staging activities for the staging tables you want to transform.

3. In the Transformation Activities table, click Add Activity.

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

5. 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.

6. 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. Configure the transformation and click Apply. For descriptions of the available options and functions, see Transformation Activity and Transformation Functions.

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

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

Adding a Validation Activity

A validation activity validates data in a staging table and generates a validation report; the report will flag any rows that failed the validation rules that you specify. For each validation rule, you specify whether to halt the recipe or to simply issue a warning. In the case of any validation error, the system generates a workflow task for the assigned role (specified when the recipe is scheduled ).

To add a validation activity to a recipe:

1. Display the recipe.

2. In the Validation Activities table, click Add Activity .

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

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

5. Click Publish.

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

6. 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. Configure the validation rule and click Apply. For descriptions of the available options and functions, see Validation Activity and Validation Functions.

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

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

Adding a Reconciliation Activity

A reconciliation activity compares data in a staging table to comparison data, which can be either data loaded within the same recipe or data from another data source. This activity generates a reconciliation report.

To add a reconciliation activity to a recipe:

1. Display the recipe.

2. In the Reconciliation Activities table, click Add Activity.

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

4. In the Primary Source section, specify the table that you ultimately want to promote and use downstream:

   1. For Data Source, select the location of the table. Typically this is Staging Tables.

   2. For Tables, select the table.

5. In the Secondary Source section, specify the comparison table:

   1. For Data Source, select the location of the table.

   2. For Tables, select the table.

6. Click Submit.

7. On the Key Map Definition tab, optionally select Report Missing Records from Primary Source (Full Outer Join).

   The page displays the fields in the primary source. You must define how records in the primary source correspond to records in the secondary source.

8. Define the key map:

   If both sources have primary keys, you can click Auto Match Primary Keys to automatically map them.

   Otherwise, map fields manually:

   1. Click a field under Primary Source to add it to the key map.

   2. Select the corresponding field under Secondary Source, or click Try to Match Selected Column.

   To remove a mapping, click the X next to the entry in the Key Map area.

9. Click Create to save the key map.

10. Click Reconciliation.

    The page displays fields from both sources. Use this view to define how field values are compared.

11. If many fields have the same name in both sources, click Quick Match All to automatically create comparison rules. You can then review and modify these rules as needed.

12. For each field comparison, select the row and configure the reconciliation rule.

    For descriptions of Function Type, Rule Name, Result Field, Tolerance, and Fatal, see Reconciliation Activity.

13. When you are done editing this activity, click Save Draft or Publish.

14. 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. In particular, make a note of the field names that you need to use. To see the definition of a staging table:

1. Display the recipe.

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.

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

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 copies data from staging tables to final tables or files.

To add a promotion activity to a recipe:

1. Display the recipe.

2. In the Promotion Activities table, click Add Activity.

3. For Activity Name, enter a descriptive name.

4. For Run Order, select or type an integer.

5. Configure remaining options and click Submit. For option descriptions, see Promotion Activity.

6. Click New Item.

7. Configure the item as described in Adding a Promotion Activity Item.

8. Click Save Activity Item.

9. When finished, click Save Draft or Publish Activity.

10. Click Recipe to return.

Adding a Promotion Activity Item

A promotion activity consists of one or more activity items. Each item defines how data is written to a target using SQL.

For descriptions of item options such as Run Order, Description, Operation Type, and SQL Expression, see Promotion Activity.

The right side of the page provides sample queries that you can use as a starting point.

Below SQL Expression, you can insert tokens such as staging tables and snapshots.

For each promotion activity item, the system compares the number of source fields to the number of target fields. If these do not match, the system displays an error and does not allow the activity to be published. The system also validates that data types are compatible.

To insert a reference to a staging table:

1. Place the cursor in SQL Expression.

2. Select Staging Activity under Placeholders.

3. Select the staging activity and data schema.

4. Click Add to SQL.

{mystage}.samplecsv

This token is replaced with the actual staging table name when the recipe runs.

To insert a reference to a snapshot:

1. Place the cursor in SQL Expression.

2. Select Snapshot under Placeholders.

3. Select the snapshot that you want to reference.

4. Click Add to SQL.

{Snapshot.mysnapshot}

This token is replaced with the actual snapshot reference when the recipe runs.

To filter by the current recipe run:

1. Add a WHERE clause if needed.

2. Select BatchID.

3. Click Add to SQL.

%BatchId={%BatchId}

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. To configure a recipe to use custom code:

  1. Display the recipe.

  2. In the Custom Activities table, click Add Activity .

  3. For Activity 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

• Introduction to Recipes and Staging Tables

• Activity Type Reference

• Editing and Managing Recipes

• Managing Recipe Groups

• Scheduling and Running Tasks

• Viewing Run History (includes information on viewing dropped records, validation reports, and reconciliation reports)