Knowledge is power: Module 10: Workflow Sessions

Module Objectives

After completing this unit, you should be able to understand about:

–Workflows,

–Variables,

–Scripting

WHAT IS A WORK FLOW?

•A work flow defines the decision- making process for executing data flows.

•For example, elements in a work flow can determine the path of execution based on a value set by a previous job or can indicate an alternative path if something goes wrong in the primary path.

JOBS VS WORKFLOW

•Jobs are special workflows.

•Jobs are special because you can execute them.

•Almost all of the features documented for work flows also apply to jobs, with one exception: jobs do not have parameters.

ELEMENTS IN WORK FLOWS

•Workflow

•Dataflows

•Scripts

•Conditionals

•While loops

•Try/catch blocks

ORDER OF EXECUTION IN WORK FLOWS

Sequential Dataflow execution from left to right

PARALLEL DATAFLOW EXECUTION

PARALLEL EXECUTION OF COMPLEX WORKFLOWS

USING SCRIPTING LANGUAGE AND VARIABLES

•You can increase the flexibility and reusability of work flows and data flows using local and global variables when you design your jobs.

•E.g. Single Data flow can be used for processing either US or UK data run when at different times.

•Query Transform can have where clause as $ region & based on the value of based on the parameter value data will be processed either for US or UK.

VARIABLES CAN BE USED AS FILE NAMES FOR:

•Flat file sources and targets.

•XML file sources and targets.

LOCAL VARIABLES, GLOBAL VARIABLES,SUBSTITUTION PARAMETERS

•Global variables are defined at Job level & can be used by any object with in the job.

•Local variables are defined at work flow or dataflow level.

•Substitution parameters are defined at Repository level. Can be used by any job object.

PARAMETERS

•Parameters can be defined to:

–Pass their values into and out of workflows

–Pass their values into data flows

DEFINING PARAMETERS

•There are two steps for setting up a parameter for a work flow or dataflow:

–Add the parameter definition to the flow.

–Set the value of the parameter in the flow call.

SETTING GLOBAL VARIABLE VALUES

•Values for global variables can be set out side a job:

–As a job property

–As an execution or schedule property

–It can also be set while running job from designer

–Data Integrator saves schedule property values in the repository. However, these values are only associated with a job schedule, not the job itself. Consequently, these values are viewed and edited from within the Administrator.

UNDERSTANDING DATA INTEGRATOR SCRIPTINGLANGUAGE

•Introduction.

•Language Syntax

–Supports ANSI SQL-02 varchar behavior

–Treats an empty string as zero length varchar value (instead of NULL)

–Evaluates comparisons to FALSE.

–Uses new is NULL and IS NOT NULL operators in Data Integrator Scripting language to test for NULL values.

–Treats trailing blanks as regular characters, instead of trimming them, when reading from all sources.

–Ignores trailing blanks in comparisons in transforms (Query and Table_Comparison) and functions (decode, if then else, lookup, lookup_ext, lookup_seq)

BASIC SYNTAX RULES

•Statement send with a semicolon (;)

•Variables begin with the dollar sign ($)

•String values are enclosed in single quotes (‘)

•Comments begin with pound(#)

COMPARISON RESULTS FOR THEVARIABLE ASSIGNMENTS $VAR1 = NULL AND $VAR2=NULL

Conditions Translates to Returns

If (NULL=NULL) NULL is equal to Null FALSE

If (NULL! =NULL) NULL is not equal to Null FALSE

If (NULL=‘ ’ ) NULL is equal to empty string FALSE

If (NULL! =‘ ’ ) NULL is not equal to empty string TRUE

If (‘bbb’= NULL) bbbis equal to Null FALSE

If (‘bbb’ !=NULL) bbbis not equal to Null FALSE

If (‘bbb’ =‘ ’) bbbis equal to empty string FALSE

If (‘bbb’ !=‘ ’) bbbis not equal to empty string TRUE

If ($var=NULL) NULL is equal to Null FALSE

If ($var! =NULL) NULL is not equal to Null FALSE

If ($var1=‘ ’) NULL is equal to empty string FALSE

If ($var1 !=‘ ’) NULL is not equal to empty string FALSE

If ($var1=$var2) NULL is equal to Null FALSE

If ($var1!=$var2) NULL is not equal to Null FALSE

COMPARING TWO VARIABLES ALWAYS TEST FOR NULL

Conditions Recommendations

If ($var1=$var2) Do not compare without explicitly testing for NULLS. Business Objects does not recommend using this logic because any relational comparison to NULL value returns FALSE.

If (($var1 IS NULL) AND ($var2 IS Will execute the TRUE branch if NULL)) OR ($var1-$var2)) both $var1 and $var2 are NULL, or if neither are NULL but are equal to each other.

WORKFLOW OBJECT-SCRIPTS

•Scripts are single-use objects used to call functions and assign values to variables in a work flow.

WORKFLOW-TRY/CATCH BLOCKS

•A try/catch block is a combination of one try object and one or more catch objects that allow you to specify alternative work flows if errors occur while Data Integrator is executing a job.

TRY-CATCH