Joiner Transformation

1. Joiner Transformation
   

 

Joiner transformation joins two related heterogeneous sources residing in different locations or file systems.

 

• We use the joiner to join two sources with at least one matching port.
  

 

• Joiner typically combine information from two different sources that do not have matching keys such as flat file sources.
  

 

• Joiner allows to use join sources that contain binary data.
  

 

There are some limitations on the pipelines we connect to the joiner. We cannot use a joiner in the following situations.

 

• Both input pipelines originate from the same Source Qualifier transformation.
  
• Both input pipelines originate from the same Normalizer or Joiner.
  
• Either input pipeline contains an Update Strategy transformation.
  
• We connect a sequence generator transformation directly before joiner.
  

 

Master and Detail Source

 

When we add the ports of a transformation to a Joiner, the ports from the first source are automatically set as detail sources. Adding the ports from the second transformation automatically sets them as master sources.

 

Join Types

 

• Normal (Default)
  
• Master Outer
  
• Detail Outer
  
• Full Outer
  

 

Master – Detail Join Rules

 

If a session contains a mapping with multiple joiner transformations, the IS reads rows in the following order.

 

• For each joiner, the IS reads all the master rows before it reads the first detail row.
  

 

• For each joiner, the IS produces output rows as soon as it reads the first detail row.
  

 

If you create a mapping with two joiners in the same target load order group, make sure each joiner receives detail rows from a different source pipeline, so that IS reads the rows according to the master-detail join rules.

 

 

 

 

 

 

 

Joiner Caches

 

When we run a session with joiner, the IS reads all the rows from the master source and builds index and data caches based on the master source. Since the cache read only the master source rows, we should specify the source with fewer rows as the master source.

 

During a workflow, the joiner compares each row of the master source against the detail source. The fewer unique rows in the master, the fewer iterations of the join comparison occur, which speeds the join process.

 

 

Join Condition

 

Both ports in a condition must have the same data type. We need to convert the data types for non-matching data types.

If the data types do not match, the mapping will be invalid.

Join condition only supports equality between fields.

The joiner does not match null values.

To join rows with null values, we can replace null input with default values, and then join the default values.

 

Join Types

 

Normal Join

 

The IS discards all rows of data from the master detail source that do not match based on the condition.

 

Master Outer join

 

It keeps all rows of data from the detail source and the matching rows from the master source. It discards the unmatched rows from the master source.

 

 

Detail Outer join

 

It keeps all rows of data from the master source and the matching rows from the detail source. It discards unmatched rows from the detail source.

 

Full Outer Join

 

It keeps all rows of data from both the master and detail sources.

 

 

• A normal or master outer join performs faster than a full outer or detail outer join.
  
• If a result set includes fields that do not contain data in either of the sources, the joiner populates the empty fields with null values.
  

 

 

 

 

Performing a join in the database

 

Performing a join in the database is faster than performing a join in the session. In some cases, its not possible, such as joining tables from two different databases or flat file systems.

 

To perform a join in the database

 

• Create a pre-session stored procedure.
  
• Use the Source Qualifier to perform the join.
  

 

Mapping Parameters and Mapping Variables

1. Mapping Parameters and Mapping Variables
   

 

Mapping parameters and variables represent values in mappings and mapplets.

 

Mapping Parameter

 

A mapping parameter represents a value that we can define before running a session. A mapping parameter retains the same value throughout the entire session.

 

 

For example, you want to use the same session to extract transaction records for each of the customers individually. Instead of creating a separate mapping for each customer account, you can create a mapping parameter to represent a single customer account. Then use the parameter in a source filter to extract only data for that customer account. Before running the session, you enter the value of the parameter in the parameter file.

 

 

If the parameter is not defined in the parameter file, the Integration Service uses the user-defined initial value for the parameter. If the initial value is not defined, the Integration Service uses a default value based on the data type of the mapping parameter.

 

IsExprVar: TRUE or FALSE

 

 

Determines how the Integration Service expands the parameter in an expression string. If true, the Integration Service expands the parameter before parsing the expression. If false, the Integration Service expands the parameter after parsing the expression.

 

Note: If you set this field to true, you must set the parameter data type to String, or the Integration Service fails the session.

 

 

 

The Integration Service looks for the value in the following order:

 

1. Value in parameter file

2. Value in pre-session variable assignment

3. Value saved in the repository

4. Initial value

5. Datatype default value

 

 

 

Mapping Variables

 

A mapping variable represents a value that can change through the session. The Integration Service saves the value of a mapping variable to the repository at the end of each successful session run and uses that value the next time you run the session.

 

 

Use mapping variables to perform incremental reads of a source. For example, the customer accounts in the mapping parameter example above are numbered from 001 to 065, incremented by one. Instead of creating a mapping parameter, you can create a mapping variable with an initial value of 001. In the mapping, use a variable function to increase the variable value by one. The first time the Integration Service runs the session, it extracts the records for customer account 001. At the end of the session, it increments the variable by one and saves that value to the repository. The next time the Integration Service runs the session, it extracts the data for the next customer account, 002. It also increments the variable value so the next session extracts and looks up data for customer account 003.

 

 

The Integration Service holds two different values for a mapping variable during a session run:

 

 

♦Start value of a mapping variable

♦Current value of a mapping variable

 

Start value

 

The start value is the value of the variable at the start of the session. The start value could be a value defined in the parameter file for the variable, a value assigned in the pre-session variable assignment, a value saved in the repository from the previous run of the session, a user defined initial value for the variable, or the default value based on the variable data type.

 

Current value

 

The current value is the value of the variable as the session progresses. When a session starts, the current value of a variable is the same as the start value. As the session progresses, the Integration Service calculates the current value using a variable function that you set for the variable. The final current value for a variable is saved to the repository at the end of a successful session. When a session fails to complete, the Integration Service does not update the value of the variable in the repository. The Integration Service states the value saved to the repository for each mapping variable in the session log.

 

If a variable function is not used to calculate the current value of a mapping variable, the start value of the variable is saved to the repository.

 

 

Aggregation type

 

The Integration Service uses the aggregate type of a mapping variable to determine the final current value of the mapping variable.

 

Types

 

--> Count

--> Max

--> Min

 

You can configure a mapping variable for a Count aggregation type when it is an Integer or Small Integer. You can configure mapping variables of any data type for Max or Min aggregation types.

 

Variable functions

 

Use variable functions in an expression to set the value of a mapping variable for the next session run.

 

 

SetMaxVariable. Sets the variable to the maximum value of a group of values. It ignores rows marked for update, delete, or reject. To use the SetMaxVariable with a mapping variable, the aggregation type of the mapping variable must be set to Max.

 

 

SetMinVariable. Sets the variable to the minimum value of a group of values. It ignores rows marked for update, delete, or reject. To use the SetMinVariable with a mapping variable, the aggregation type of the mapping variable must be set to Min.

 

 

SetCountVariable. Increments the variable value by one. In other words, it adds one to the variable value when a row is marked for insertion, and subtracts one when the row is marked for deletion. It ignores rows marked for update or reject.

 

 

SetVariable. Sets the variable to the configured value. At the end of a session, it compares the final current value of the variable to the start value of the variable. Based on the aggregate type of the variable, it saves a final value to the repository. To use the SetVariable function with a mapping variable, the aggregation type of the mapping variable must be set to Max or Min. The SetVariable function ignores rows marked for delete or reject.

 

The Integration Service does not save the final current value of a mapping variable to the repository when any of the following conditions are true:

 

 

♦The session fails to complete.

♦The session is configured for a test load.

♦The session is a debug session.

♦The session runs in debug mode and is configured to discard session output.

 

 

Use a variable function in any of the following transformations:

♦Expression

♦Filter

♦Router

♦Update Strategy

 

 

à These will appear in the variables tab of the Expression editor.

 

à When you use mapping parameters and variables in a Source Qualifier transformation, the Designer expands them before passing the query to the source database

 

à When you create a reusable transformation in the Transformation Developer, use any mapping parameter or variable. Since a reusable transformation is not contained within any mapplet or mapping, the Designer validates the usage of any mapping parameter or variable in the expressions of reusable transformation for validation.

 

à Enclose string and date time parameters and variables in quotes in the SQL Editor.

 

à Mapping parameter and variable values in mapplets must be preceded by the mapplet name in the parameter file, as follows:

mappletname.parameter=value

mappletname.variable=value

 

à You cannot use variable functions in the Rank or Aggregator transformation.

 

Default Values for Mapping Parameters and Variables Based on Data type

 

String – Empty String; Numeric – 0;

Date/time – 1/1/1753 A.D

 

à Source qualifier filter condition: state = '$$State'

Filter transformation filter condition: state = $$State

Normalizer Transformation

1. Normalizer Transformation
   

 

The Normalizer transformation receives a row that contains multiple-occurring columns and returns a row for each instance of the multiple-occurring data. The transformation processes multiple-occurring columns or multiple-occurring groups of columns in each source row.

 

The Normalizer transformation parses multiple-occurring columns from COBOL sources, relational tables, or other sources.

 

For example, you might have a relational table that stores four quarters of sales by store. You need to create a row for each sales occurrence. You can configure a Normalizer transformation to return a separate row for each quarter.

 

 

The following source rows contain four quarters of sales by store:

Store1 100 300 500 700

Store2 250 450 650 850

 

Output

 

Store1 100 1

Store1 300 2

Store1 500 3

Store1 700 4

Store2 250 1

Store2 450 2

Store2 650 3

Store2 850 4

 

 

The Normalizer transformation generates a key for each source row.

 

 

 

Types

 

VSAM Normalizer transformation

 

    A non-reusable transformation that is a Source Qualifier transformation for a COBOL source. The Mapping Designer creates VSAM Normalizer columns from a COBOL source in a mapping. The column attributes are read-only. The VSAM Normalizer receives a multiple-occurring source column through one input port.

 

Pipeline Normalizer transformation

 

    A transformation that processes multiple-occurring data from relational tables or flat files.

You might choose this option when you want to process multiple-occurring data from another transformation in the mapping.

 

 

A VSAM Normalizer transformation has one input port for a multiple-occurring column. A pipeline Normalizer transformation has multiple input ports for a multiple-occurring column.

When you create a Normalizer transformation in the Transformation Developer, you create a pipeline Normalizer transformation by default. When you create a pipeline Normalizer transformation, you define the columns based on the data the transformation receives from another type of transformation such as a Source Qualifier transformation.

 

The Normalizer transformation has one output port for each single-occurring input port.

 

Generated Column ID (GCID)

 

    The Normalizer transformation has a generated column ID (GCID) port for each multiple-occurring column. The generated column ID is an index for the instance of the multiple-occurring data. For example, if a column occurs four times in a source record, the Normalizer returns a value of 1, 2, 3, or 4 in the generated column ID based on which instance of the multiple-occurring data occurs in the row.

 

The Normalizer transformation has at least one generated key column in the output row.

 

At the end of each session, the Integration Service updates the generated key value in the Normalizer transformation to the last value generated for the session plus one.

 

-->

You can change the ports on a pipeline Normalizer transformation by editing the columns on the Normalizer tab for relational. To change a VSAM Normalizer transformation, you need to change the COBOL source and recreate the transformation.

 

 

Properties

 

Reset

 

    At the end of a session, resets the value sequence for each generated key value to the value it was before the session.

 

Restart

 

    Starts the generated key sequence at 1. Each time you run a session, the key sequence value starts at 1 and overrides the sequence value on the Ports tab.

 

 

Normalizer tab

 

    The Normalizer tab defines the structure of the source data.

    

    Level: Group columns. Columns in the same group occur beneath a column with a lower level number. When each column is the same level, the transformation contains no column groups.

 

    Occurs: The number of instances of a column or group of columns in the source row.

When you create a pipeline Normalizer transformation, you can edit the columns. When you create a VSAM Normalizer transformation, the Normalizer tab is read-only.

 

 

 

VSAM Normalizer Transformation

 

 

    The VSAM Normalizer transformation is the source qualifier for a COBOL source definition. A COBOL source is a flat file that can contain multiple-occurring data and multiple types of records in the same file.

 

    VSAM (Virtual Storage Access Method) is a file access method for an IBM mainframe operating system. VSAM files organize records in indexed or sequential flat files.

 

    A COBOL source definition can have an OCCURS statement that defines a multiple-occurring column. The COBOL source definition can also contain a REDEFINES statement to define more than one type of record in the file.

 

 

Normalizer Tab for a VSAM Normalizer Transformation

 

    POffs: Physical offset. Location of the field in the file. The first byte in the file is zero.

    Plen: Physical length. Number of bytes in the field.

    

    Level: Provides column group hierarchy. The higher the level number, the lower the data is in the hierarchy. Columns in the same group occur beneath a column with a lower level number.

 

    Picture: How the data is stored or displayed in the source. Picture 99V99 defines a numeric field with two implied decimals. Picture X(10) indicates ten characters.

 

    Key Type: Type of key constraint to apply to this field. When you configure a field as a primary key, the Integration Service generates unique numeric IDs for this field when running a session with a COBOL source.

 

    Redefines: Indicates that the column REDEFINES another column.

Transaction Control Transformation

1. Transaction Control Transformation
   

 

We can control commit and roll back transactions based on a set of rows that pass through using Transaction Control transformation.

A transaction is the set of rows bound by commit or roll back rows.

 

Use the Transaction Control transformation to define conditions to commit and roll back transactions from transactional targets.

 

In PowerCenter, you define transaction control at two levels:

 

Within a mapping: within a mapping, you use the Transaction Control transformation to define a transaction. You define transactions using an expression in a Transaction Control transformation. Based on the return value of the expression, you can choose to commit, roll back, or continue without any transaction changes.

 

Within a session: When you configure a session, you configure it for user-defined commit. You can choose to commit or roll back a transaction if the Integration Service fails to transform or write any row to the target.

 

When you run the session, the Integration Service evaluates the expression for each row that enters the transformation. When it evaluates a commit row, it commits all rows in the transaction to the target or targets. When the Integration Service evaluates a roll back row, it rolls back all rows in the transaction from the target or targets.

 

If the mapping has a flat file target you can generate an output file each time the Integration Service starts a new transaction. You can dynamically name each target flat file.

 

Properties

 

Transaction control condition

 

Enter the transaction control expression in the Transaction Control Condition field. The transaction control expression uses the IIF function to test each row against the condition. Use the following syntax for the expression:

 

IIF (condition, value1, value2)

 

IIF (NEW_DATE = 1, TC_COMMIT_BEFORE, TC_CONTINUE_TRANSACTION)

 

The Integration Service evaluates the condition on a row-by-row basis. The return value determines whether the Integration Service commits, rolls back, or makes no transaction changes to the row. When the Integration Service issues a commit or roll back based on the return value of the expression, it begins a new transaction.

 

 

Use the following built-in variables in the Expression Editor when you create a transaction control expression:

 

TC_CONTINUE_TRANSACTION: The Integration Service does not perform any transaction change for this row. This is the default value of the expression.

 

TC_COMMIT_BEFORE: The Integration Service commits the transaction, begins a new transaction, and writes the current row to the target. The current row is in the new transaction.

 

TC_COMMIT_AFTER: The Integration Service writes the current row to the target, commits the transaction, and begins a new transaction. The current row is in the committed transaction.

 

TC_ROLLBACK_BEFORE: The Integration Service rolls back the current transaction, begins a new transaction, and writes the current row to the target. The current row is in the new transaction.

 

TC_ROLLBACK_AFTER: The Integration Service writes the current row to the target, rolls back the transaction, and begins a new transaction. The current row is in the rolled back transaction.

 

 

If the transaction control expression evaluates to a value other than commit, roll back, or continue, the Integration Service fails the session.

 

Example

You want to use transaction control to write order information based on the order entry date. You want to ensure that all orders entered on any given date are committed to the target in the same transaction.

 

You can also use Custom transformations configured to generate transactions to define transaction boundaries.

 

Transaction Control transformations can be either effective or ineffective for the downstream transformations and targets in the mapping. The Transaction Control transformation becomes ineffective for downstream transformations or targets if you put a transformation that drops incoming transaction boundaries after it. This includes any of the following active sources or transformations:

 

Aggregator transformation with the All Input level transformation scope

Joiner transformation with the All Input level transformation scope

Rank transformation with the All Input level transformation scope

Sorter transformation with the All Input level transformation scope

Custom transformation with the All Input level transformation scope

Custom transformation configured to generate transactions

Transaction Control transformation

 

 

 

 

Notes

 

--> Transaction Control transformations connected to any target other than relational, XML, or dynamic MQSeries targets are ineffective for those targets.

 

--> You must connect each target instance to a Transaction Control transformation.

 

-->You can connect multiple targets to a single Transaction Control transformation.

 

--> You can connect only one effective Transaction Control transformation to a target.

 

--> You cannot place a Transaction Control transformation in a pipeline branch that starts with a Sequence Generator transformation.

TRANSFORMATION 3

Target Load Order

 

We specify a target load order based on the Source Qualifier transformations in a mapping. If you have multiple Source Qualifier transformations connected to multiple targets, you can designate the order in which the Integration Service loads data into the targets.

 

constraint-based loading

 

If one Source Qualifier transformation provides data for multiple targets, you can enable constraint-based loading in a session to have the Integration Service load data based on target table primary and foreign key relationships.

 

 

 

1. Stored Procedure Transformation
   

 

A stored procedure is a precompiled collection of Transact-SQL, PL-SQL or other database procedural statements and optional flow control statements, similar to an executable script. Stored procedures are stored and run within the database.

 

You might use stored procedures to complete the following tasks:

 

--> Check the status of a target database before loading data into it.

--> Determine if enough space exists in a database.

--> Perform a specialized calculation.

--> Drop and recreate indexes.

 

Stored procedures also provide error handling and logging necessary for critical tasks.

 

You might use a stored procedure to perform a query or calculation that you would otherwise make part of a mapping.

 

There are three types of data that pass between the Integration Service and the stored procedure:

 

Input/Output Parameters

        

For many stored procedures, you provide a value and receive a value in return. These values are known as input and output parameters.

 

Return Values

 

The Stored Procedure transformation captures return values in a similar manner as input/output parameters, depending on the method that the input/output parameters are captured.

If a stored procedure returns a result set rather than a single return value, the Stored Procedure transformation takes only the first value returned from the procedure.

 

Status Codes

        

Status codes provide error handling for the Integration Service during a workflow. The stored procedure issues a status code that notifies whether or not the stored procedure completed successfully. The Integration Service uses it to determine whether to continue running the session or stop.

 

Connected Stored Procedure

 

The flow of data through a mapping in connected mode also passes through the Stored Procedure transformation. All data entering the transformation through the input ports affects the stored procedure. You should use a connected Stored Procedure transformation when you need data from an input port sent as an input parameter to the stored procedure, or the results of a stored procedure sent as an output parameter to another transformation.

 

We can go for Connected when we want to

 

--> Run a stored procedure every time a row passes through the Stored Procedure transformation.

--> Pass parameters to the stored procedure and receive a single output parameter.

--> Pass parameters to the stored procedure and receive multiple output parameters.

    

Un Connected Stored Procedure

 

The unconnected Stored Procedure transformation is not connected directly to the flow of the mapping. It either runs before or after the session, or is called by an expression in another transformation in the mapping.

 

 

 

 

 

 

We can go for Connected when we want to

 

--> Run a stored procedure before or after a session.

--> Run a stored procedure once during a mapping, such as pre- or post-session.

--> Run nested stored procedures.

--> Call multiple times within a mapping.

 

 

Stored Procedure Types

 

Normal. The stored procedure runs where the transformation exists in the mapping on a row-by-row basis. This is useful for calling the stored procedure for each row of data that passes through the mapping, such as running a calculation against an input port. Connected stored procedures run only in normal mode.

 

Pre-load of the Source: Before the session retrieves data from the source, the stored procedure runs. This is useful for verifying the existence of tables or performing joins of data in a temporary table.

 

Post-load of the Source: After the session retrieves data from the source, the stored procedure runs. This is useful for removing temporary tables.

 

Pre-load of the Target: Before the session sends data to the target, the stored procedure runs. This is useful for verifying target tables or disk space on the target system.

 

Post-load of the Target: After the session sends data to the target, the stored procedure runs. This is useful for re-creating indexes on the database.

 

You can run more than one Stored Procedure transformation in different modes in the same mapping. For example, a pre-load source stored procedure can check table integrity, a normal stored procedure can populate the table, and a post-load stored procedure can rebuild indexes in the database.

 

--> Stored Procedure transformations are created as Normal type by default, which means that they run during the mapping, not before or after the session.

 

-->If stored procedures exist in the database that do not contain parameters or return values, they appear in a folder called PROCEDURES otherwise in the FUNCTIONS folder.

 

 

Configuring an Unconnected Transformation

 

When using an unconnected Stored Procedure transformation in an expression, you need a method of returning the value of output parameters to a port.

    

--> Assign the output value to a local variable.

--> Assign the output value to the system variable PROC_RESULT.

 

By using PROC_RESULT, you assign the value of the return parameter directly to an output port, which can apply directly to a target. You can also combine the two options by assigning one output parameter as PROC_RESULT, and the other parameter as a variable.

 

Use PROC_RESULT only within an expression. If you do not use PROC_RESULT or a variable, the port containing the expression captures a NULL. You cannot use PROC_RESULT in a connected Lookup transformation or call text.

 

If the stored procedure returns a single output parameter or a return value (but not both), you should use the reserved variable PROC_RESULT as the output variable. In the previous example, the expression would appear as:

:SP.GET_NAME_FROM_ID(inID, PROC_RESULT)

 

inID can be either an input port for the transformation or a variable in the transformation. The value of PROC_RESULT is applied to the output port for the expression.

 

If the stored procedure returns multiple output parameters, you must create variables for each output parameter. For example, if you create a port called varOUTPUT2 for the stored procedure expression, and a variable called varOUTPUT1, the expression appears as:

 

:SP.GET_NAME_FROM_ID(inID, varOUTPUT1, PROC_RESULT)

 

Calling a Pre- or Post-Session Stored Procedure

 

You may want to run a stored procedure once per session. For example, if you need to verify that tables exist in a target database before running a mapping, a pre-load target stored procedure can check the tables, and then either continue running the workflow or stop it. You can run a stored procedure on the source, target, or any other connected database.

 

Stored Procedure à Properties --> Call Text

 

This is the name of the stored procedure, followed by all applicable input parameters in parentheses. If there are no input parameters, you must include an empty pair of parentheses, or the call to the stored procedure fails.

For example, to call a stored procedure called check_disk_space, enter the following text:

check_disk_space()

 

if the stored procedure check_disk_space required a machine name as an input parameter, enter the following text:

check_disk_space(oracle_db)

 

When passing a datetime value through a pre- or post-session stored procedure, the value must be in the Informatica default date format and enclosed in double quotes as follows:

SP("12/31/2000 11:45:59")

 

 

Error Handling

 

Sometimes a stored procedure returns a database error, such as "divide by zero" or "no more rows." The final result of a database error during a stored procedure depends on when the stored procedure takes place and how the session is configured.

You can configure the session to either stop or continue running the session upon encountering a pre- or post-session stored procedure error. By default, the Integration Service stops a session when a pre- or post-session stored procedure database error occurs.

    

Session --> Config Object --> On Stored Procedure error --> Stop, Continue

 

 

If the database returns an error for a particular row, the Integration Service skips the row and continues to the next row. As with other row transformation errors, the skipped row appears in the session log.

 

Notes

 

à A single output parameter is returned using the variable PROC_RESULT.

 

à When you call a stored procedure in an expression, use the :SP reference qualifier.

 

à Use PROC_RESULT to apply the output parameter of a stored procedure expression directly to a target. You cannot use a variable for the output parameter to pass the results directly to a target. Use a local variable to pass the results to an output port within the same transformation.

 

à Nested stored procedures allow passing the return value of one stored procedure as the input parameter of another stored procedure. For example, if you have the following two stored procedures:

 

 

−get_employee_id (employee_name)

−get_employee_salary (employee_id)

And the return value for get_employee_id is an employee ID number, the syntax for a nested stored procedure is:

:sp.get_employee_salary (:sp.get_employee_id (employee_name))

 

à Do not use single quotes around string parameters. If the input parameter does not contain spaces, do not use any quotes. If the input parameter contains spaces, use double quotes.

 

TRANSFORMATON 3

1. Expression Transformation
   

 

We can use Expression transformation to calculate values in a single row.

 

1. Filter Transformation
   

 

A filter transformation test data for one condition and drops the rows that do not meet the condition.

 

Filter condition:

 

Filter condition is an expression that returns TRUE or FALSE. Any expression that returns a single value can be used as a filter.

 

If the filter condition evaluates to NULL, the row is treated as FALSE.

 

The filter condition is case-sensitive.

 

à To maximize the session performance, keep the Filter transformation as close as possible to the sources in the mapping.

 

à The SQ transformation provides an alternative way to filter rows. Rather than filtering rows within a mapping, the SQ filter rows when it reads from source.

 

SQ transformation only lets us filter rows from relational sources, while the filter transformation filter rows from any type of source.

 

Filter condition in the SQ transformation only uses standard SQL. The filter transformation can define a condition using any statement or transformation function that returns either TREU or FALSE.

 

 

 

 

1. Source Qualifier
   

 

The Source Qualifier transformation represents the rows that the Integration Service reads when it runs a session.

 

Tasks:

 

à Join data originating from the same source database. We can join two or more tables with primary key-foreign key relationships by linking the sources to one Source Qualifier transformation.

 

à Filter rows when the Integration Service reads source data

 

à Specify an outer join rather than the default inner join.

 

à Specify sorted ports.

 

à Select only distinct values from the source

 

àCreate a custom query to issue a special SELECT statement for the Integration Service to read source data. For example, you might use a custom query to perform aggregate calculations.

 

 

Properties

 

SQL Query -- Defines a custom query that replaces the default query the Integration Service uses to read data from sources represented in this Source Qualifier transformation. A custom query overrides entries for a custom join or a source filter.

 

User-Defined Join -- Specifies the condition used to join data from multiple sources represented in the same Source Qualifier transformation.

 

Source Filter -- Specifies the filter condition the Integration Service applies when querying rows.

 

Number of Sorted Ports --Indicates the number of columns used when sorting rows queried from relational sources.

 

Select Distinct --Specifies if you want to select only unique rows.

 

Pre-SQL -- Pre-session SQL commands to run against the source database before the Integration Service reads the source.

 

Post-SQL -- Post-session SQL commands to run against the source database after the Integration Service writes to the target.

 

 

 

Default Join

 

 

When you join related tables in one Source Qualifier transformation, the Integration Service joins the tables based on the related keys in each table.

This default join is an inner equijoin, using the following syntax in the WHERE clause:

Source1.column_name = Source2.column_name

 

The columns in the default join must have:

 

--> A primary key-foreign key relationship

--> Matching data types

 

You might need to override the default join under the following circumstances:

 

--> Columns do not have a primary key-foreign key relationship.

--> The data types of columns used for the join do not match.

--> You want to specify a different type of join, such as an outer join.

 

 

Creating Key Relationships

 

 

You can join tables in the Source Qualifier transformation if the tables have primary key-foreign key relationships. However, you can create primary key-foreign key relationships in the Source Analyzer by linking matching columns in different tables. These columns do not have to be keys, but they should be included in the index for each table.

 

 

For example, the corporate office for a retail chain wants to extract payments received based on orders. The ORDERS and PAYMENTS tables do not share primary and foreign keys. Both tables, however, include a DATE_SHIPPED column. You can create a primary key-foreign key relationship in the metadata in the Source Analyzer.

 

The primary key-foreign key relationships exist in the metadata only. You do not need to generate SQL or alter the source tables.

 

 

Outer Join

 

Use an outer join when you want to join two tables and return all rows from one of the tables.

 

 

 

The Integration Service supports two kinds of outer joins:

 

--> Left. Integration Service returns all rows for the table to the left of the join syntax and the rows from both tables that meet the join condition.

 

--> Right. Integration Service returns all rows for the table to the right of the join syntax and the rows from both tables that meet the join condition.

 

Normal Join Syntax

 

To create a normal join, use the following syntax:

{ source1 INNER JOIN source2 on join_condition }

 

Left Outer Join Syntax

 

To create a left outer join, use the following syntax:

{ source1 LEFT OUTER JOIN source2 on join_condition }

 

 

Right Outer Join Syntax

 

To create a right outer join, use the following syntax:

{ source1 RIGHT OUTER JOIN source2 on join_condition }

 

 

 

Important Points

 

 

--> When you use a datetime value or a datetime parameter or variable in the SQL query, change the date format to the format used in the source. The Integration Service passes datetime values to source systems as strings in the SQL query. The Integration Service converts a datetime value to a string, based on the source database.

 

For example, to convert the $$$SessStartTime value for an Oracle source, use the following Oracle function in the SQL override:

to_date ('$$$SessStartTime', 'mm/dd/yyyy hh24:mi:ss')

 

 

--> You can enter a parameter or variable within the SQL statement, or you can use a parameter or variable as the SQL query. For example, you can use a session parameter, $ParamMyQuery, as the SQL query, and set $ParamMyQuery to the SQL statement in a parameter file.

 

-->When you use a string mapping parameter or variable in the Source Qualifier transformation, use a string identifier appropriate to the source system. Most databases use a single quotation mark as a string identifier.

 

--> When output is deterministic and output is repeatable, the Integration Service does not stage source data for recovery.

 

--> You must connect the columns in the Source Qualifier transformation to another transformation or target before you can generate the default query.

 

--> If the source table has more than 1,000 rows, you can increase performance by indexing the primary key-foreign keys. If the source table has fewer than 1,000 rows, you might decrease performance if you index the primary key-foreign keys.

 

 

--> When you override the default SQL query for a session configured for pushdown optimization, the Integration Service creates a view to represent the SQL override. It then runs an SQL query against this view to push the transformation logic to the database.

 

TRANSFORMATION 2

1. Variable Initialization
   

 

Numeric ports : 0

String ports : Empty string

Date/time ports: 01/01/1753

 

1. System Default values for ports
   

 

All transformations use default values that determine how the IS handles input null values and output transformation errors.

 

Input, Output, Input/Output ports are created with a system default value that we can override with a user-defined default value.

 

Input port: The system default value for input port is NULL.

 

Output port: The system default value for output transformation errors is ERROR, It appears as ERROR('Transformation error). If a transformation error occurs, the IS skips the row. The IS notes all input rows skipped by the ERROR function in the session log file.

 

Variable ports do not support default values. The IS initializes variable ports according to the data type.

 

1. User defined default values
   

 

We can override the system default values with user-defined default values.

 

Input ports: We can create user-defined default values for input ports if we do not want the IS to treat null values as NULL.

 

Output ports: We can enter user-defined default values for output ports if we do not want the IS to skip the row or if we want the IS to write a specific message with the skipped row to the session log file.

 

Note:

 

The IS ignores user-defined default values for unconnected transformations. For example, if we call a lookup or stored procedure transformation through an expression, the IS ignores any user-defined default values and uses the system default value only.

 

à SQ, Rank, Transaction control transformations does not support input default value for input and input/output ports.

 

à Aggregator, Filter, Router, SQL, SQ(n/a), Transaction Control (n/a) , US (n/a) does not support output default value for output ports.

 

à We can enter any constant expression as a default value. A constant expression is any expression that uses transformation functions (except aggregate functions) to write constant expressions. We can not use values from input or output ports.

 

à We can use ERROR or ABORT functions for input and output port default values, and input values for input/output ports. The IS skips the row when it encounters the ERROR function. It aborts the session when it encounters the ABORT function.

 

1. Transformation Error
   

 

When a transformation error occurs and we didn't override the default value, the IS performs…

à Increases the transformation error count by 1

à Skips the row and writes the error and input row to the session log file or row error log. The IS does not write the row to the reject file.

 

Note: Reusable transformation instances do not inherit changes to property settings, only modifications to ports, expressions, and the name of the transformation.

 

1. Aggregator Transformation
   

   
    

   Aggregator transformation performs aggregate calculations such as averages and sums.
   

   
    

   We can use conditional clauses to filter rows in aggregate transformations , providing more flexibility than SQL language.
   

   
    

   Eg: SUM(COMM, COMM>0)
   

   
    

   We can also use non-aggregate functions in the aggregate expression.
   

   
    

   IIF(MAX(QTY)>0,MAX(QTY),0)
   

   
    

   We can include one aggregate function nested within another aggregate function.
   

   
    

   Eg: MAX(COUNT(EMPNO))
   

   
    

   
    

   Incremental aggregation:
   

   
    

   When the IS performs incremental aggregation, it passes new source data through the mapping and uses historical cache data to perform new aggregation calculation incrementally.
   

   
    

   Aggregate cache:
   

   
    

   The IS stores data in the cache until it completes aggregate calculations. It stores group values in an index cache and row data in the data cache.
   

   Sorted Input:
   

   
    

   We can improve aggregator transformation performance by using sorted input option. When we use sorted input , the IS assumes all data is sorted by group and it performs aggregate calculations as it reads for a group.
   

   
    

   If we use sorted input and do not sort data correctly, the session fails.
   

   
    

   Do not use sorted input
   

   
    

   à if the aggregate expression uses nested aggregate functions.
   

   
    

   à The session uses incremental aggregation.
   

   
    

   à Source data is data driven.
   

   
    

   When any of these conditions are true, the IS processes the transformation as if you do not use sorted input.
   

   
    

   
    

   Data must be sorted by the aggregator group by ports , in the order they appear in the aggregator.
   

TRANSFORMATION 1

1. Transformation
   

   A transformation is a repository object that generates, modifies or passes data.
   

   
    

2. Active transformation
   

   
    

   à
   Change the number of rows that pass through the transformation. For example, the Filter transformation is active because it removes the rows that do not meet the filter condition.
   

   
    

   à
   Change the transaction boundary. For example, Transaction control transformation is an active because it defines a commit or roll back transaction based on an expression evaluated for each row.
   

   
    

   à
   Change the row type. For example, Update strategy transformation is active because it flags rows for insert, update, delete or reject.
   

   
    

Notes:

        à All multi-group transformations are active because they might change the number of rows that pass through the transformation.

 

        à We cannot connect multiple active transformations to single input group active transformation. For example, SQ_emp, SQ_dept à Filter is not possible.

    

        à We cannot connect multiple active transformations to single input group passive transformation, because the Integration service may not be able to concatenate the rows passed by active transformation. For example, SQ_emp, exp_dept à Exp.

 

        à The sequence generator transformation is an exception to this rule. We can connect SG transformation and an active transformation to single input group active or passive transformation because SG does not receives input data, it generates unique numeric value.

 

1. Passive transformation
   

 

A passive transformation does not change the number of rows that pass through it, maintains the transaction boundary and transaction type.

 

We can connect two passive transformations to the same downstream active or passive transformation.

 

1. Unconnected Transformation
   

 

An unconnected transformation is not connected to other transformation in the mapping. It can be called within another transformation and returns a value to that transformation.

 

 

 

 

 

1. Application Source Qualifier
   

 

It is an active transformation represents the rows that the IS reads from an application such as ERP.

 

1. Active: Normalizer, XML generator, XML parser, XML SQ, US
   

   Passive: Data masking, External procedure, HTTP, Lookup, Input, output, Stored procedure.
   

   Active/passive: Custom, Java, SQL, Unstructured data.
   

   
    

2. Multi-group transformations
   

 

Joiner: Contains two input groups, the master source and detail source, and one output group.

Router: Contains one input group and multiple output groups.

Union: Contains multiple input groups and one input group.

Custom: Contains any number of input and output groups.

 

1. Blocking Transformation
   

   
    

   A blocking transformation is a multiple input group transformation that blocks incoming data. IS block data at input group while the IS waits for a row from different input group.
   

   
    

   à Joiner transformation configured for unsorted input.
   

   à Custom transformation with the Inputs May Block property enabled.
   

 

1. Comment indicator: -- or //
   

 

 

1. Local variables
   

 

We can use local variables to stored data temporarily, to store values from prior rows, to capture multiple return values from stored procedure, to store the result of an unconnected lookup.

 

We can use local variables in Aggregator, Expression and Rank transformations.

 

1. Order of evaluation (Port Order)
   

   
    

   Input ports, Variable ports, Output ports.
   

   
    

   Input ports: The IS evaluates all input ports first since they do not depend on any other ports. Therefore we can create input ports in any order.
   

   
    

   Variable ports: Variable ports can reference input ports and variable ports, but not output ports. The IS evaluates variable ports after input ports.
   

   
    

   
    

   Output ports: Output ports can reference input ports and variable ports. The IS evaluates output ports last.
   

Pavan Kumar Paluvadi invites you to Freelancer.com

Hi,

Pavan Kumar Paluvadi is inviting you to join Freelancer.com

Pavan Kumar Paluvadi 11 August 2011

Freelancer.com is the world's largest micro-outsourcing site. We have over 2 million registered professionals ready to bid on your jobs and produce high quality work.

The average job is under $200 USD and you can outsource anything, whether it be web design, graphic design, marketing or sales. Turbocharge your business today!

Looking for work? We also have the largest network of employers ready to hire just about any skill set.

pvsnpkumar.dwhetl@blogger.com was invited to join Freelancer.com by Pavan Kumar Paluvadi.

Performance Testing Citrix Presentation Server™ Platform

Citrix Presentation Server™ provides dynamic solutions to information access issues, but presents potential complications that are unique to this platform.

While you are confident that your decision to use Citrix will provide cost-efficient accessibility to necessary applications, your business and technical teams are wondering whether it will scale. 

What do you do? 

Conventional scalability/load/performance tools will not work with applications built with Citrix. Why? Because these tools have been built to capture communication at the protocol level. 
Citrix communicates through its proprietary ICA protocol. Few of the conventional tool vendors have a testing solution for this protocol. 

RTTS, the leader in test automation, provides scalability/load/performance testing of Citrix Presentation Server and the ICA architecture that circumvents this issue. 
We implement the leading edge Citrix testing tools that utilize unique, event and image-driven verification and synchronization of objects from the user perspective.

 

The test tool has these capabilities: 

• Recognize and evaluate the targeted application's response during test script playback by comparing the application's display to images stored in a user-created baseline. 

• Interact with the target application by issuing standard keyboard and mouse events to the system under test in accordance with the user defined test script logic. 

• Use the tool to act as a "virtual" human user to detect, evaluate, measure, and respond to the on-screen activity based on the model represented by the test script logic. 

• Provide performance benchmarking response-timer functions that allow measurement of response times to within milliseconds. 

• Can scale up to as many concurrent users as the business demands. 
 

RTTS is able to deliver a Citrix testing service that incorporates planning, design, scripting, test execution and analysis of the performance of your Citrix environment. We provide all-inclusive model that incorporates short-term pricing for rental of the Citrix load testing tool and expert programming and analysis services to implement the tool and interpret the results. 

We can identify and manage system performance risk, helping to avoid downtime, improve company productivity and deliver reliable services.