Necessity Of Performance-Tuning

Performance Tuning Overview

The goal of performance tuning is to optimize session performance by eliminating performance bottlenecks. To tune session performance, first identify a performance bottleneck, eliminate it, and then identify the next performance bottleneck until you are satisfied with the session performance. You can use the test load option to run sessions when you tune session performance.

If you tune all the bottlenecks, you can further optimize session performance by increasing the number of pipeline partitions in the session. Adding partitions can improve performance by utilizing more of the system hardware while processing the session.

Because determining the best way to improve performance can be complex, change one variable at a time, and time the session both before and after the change. If session performance does not improve, you might want to return to the original configuration.

Complete the following tasks to improve session performance:

LOOKUP TRANSFORMATION

• Passive Transformation
• Can be Connected or Unconnected. Dynamic lookup is connected.
• Use a Lookup transformation in a mapping to look up data in a flat file or a relational table, view, or synonym.
• We can import a lookup definition from any flat file or relational database to which both the PowerCenter Client and Server can connect.
• We can use multiple Lookup transformations in a mapping.

The Power Center Server queries the lookup source based on the lookup ports in the transformation. It compares Lookup transformation port values to lookup source column values based on the lookup condition. Pass the result of the lookup to other transformations and a target.

We can use the Lookup transformation to perform following:

• Get a related value: EMP has DEPTNO but DNAME is not there. We use Lookup to get DNAME from DEPT table based on Lookup Condition.
• Perform a calculation: We want only those Employees who’s SAL > Average (SAL). We will write Lookup Override query.
• Update slowly changing dimension tables: Most important use. We can use a Lookup transformation to determine whether rows already exist in the target.

1. LOOKUP TYPES

We can configure the Lookup transformation to perform the following types of lookups:

• Connected or Unconnected
• Relational or Flat File
• Cached or Un cached

Relational Lookup:

When we create a Lookup transformation using a relational table as a lookup source, we can connect to the lookup source using ODBC and import the table definition as the structure for the Lookup transformation.

• We can override the default SQL statement if we want to add a WHERE clause or query multiple tables.
• We can use a dynamic lookup cache with relational lookups.

Flat File Lookup:

When we use a flat file for a lookup source, we can use any flat file definition in the repository, or we can import it. When we import a flat file lookup source, the Designer invokes the Flat File Wizard.

Cached or Un cached Lookup:

We can check the option in Properties Tab to Cache to lookup or not. By default, lookup is cached.

Connected and Unconnected Lookup

Connected Lookup	Unconnected Lookup
Receives input values directly from the pipeline.	Receives input values from the result of a :LKP expression in another transformation.
We can use a dynamic or static cache.	We can use a static cache.
Cache includes all lookup columns used in the mapping.	Cache includes all lookup/output ports in the lookup condition and the lookup/return port.
If there is no match for the lookup condition, the Power Center Server returns the default value for all output ports.	If there is no match for the lookup condition, the Power Center Server returns NULL.
If there is a match for the lookup condition, the Power Center Server returns the result of the lookup condition for all lookup/output ports.	If there is a match for the lookup condition,the Power Center Server returns the result of the lookup condition into the return port.
Pass multiple output values to another transformation.	Pass one output value to another transformation.
Supports user-defined default values	Does not support user-defined default values.

  

2 .LOOKUP T/F COMPONENTS

Define the following components when we configure a Lookup transformation in a mapping:

• Lookup source
• Ports
• Properties
• Condition

1. Lookup Source:

We can use a flat file or a relational table for a lookup source. When we create a Lookup t/f, we can import the lookup source from the following locations:

• Any relational source or target definition in the repository
• Any flat file source or target definition in the repository
• Any table or file that both the Power Center Server and Client machine can connect to The lookup table can be a single table, or we can join multiple tables in the same database using a lookup SQL override in Properties Tab.

2. Ports:

Ports	Lookup Type	Number Needed	Description
I	Connected Unconnected	Minimum 1	Input port to Lookup. Usually ports used for Join condition are Input ports.
O	Connected Unconnected	Minimum 1	Ports going to another transformation from Lookup.
L	Connected Unconnected	Minimum 1	Lookup port. The Designer automatically Designates each column in the lookup source as a lookup (L) and output port (O).
R	Unconnected	1 Only	Return port. Use only in unconnected Lookup t/f only.

3. Properties Tab

Options	Lookup Type	Description
Lookup SQL Override	Relational	Overrides the default SQL statement to query the lookup table.
Lookup Table Name	Relational	Specifies the name of the table from which the transformation looks up and caches values.
Lookup Caching Enabled	Flat File, Relational	Indicates whether the Power Center Server caches lookup values during the session.
Lookup Policy on Multiple Match	Flat File, Relational	Determines what happens when the Lookup transformation finds multiple rows that match the lookup condition. Options: Use First Value or Use Last Value or Use Any Value or Report Error
Lookup Condition	Flat File, Relational	Displays the lookup condition you set in the Condition tab.
Connection Information	Relational	Specifies the database containing the lookup table.
Source Type	Flat File, Relational	Lookup is from a database or flat file.
Lookup Cache Directory Name	Flat File, Relational	Location where cache is build.
Lookup Cache Persistent	Flat File, Relational	Whether to use Persistent Cache or not.
Dynamic Lookup Cache	Flat File, Relational	Whether to use Dynamic Cache or not.
Recache From Lookup Source	Flat File, Relational	To rebuild cache if cache source changes and we are using Persistent Cache.
Insert Else Update	Relational	Use only with dynamic caching enabled. Applies to rows entering the Lookup transformation with the row type of insert.
Lookup Data Cache Size	Flat File, Relational	Data Cache Size
Lookup Index Cache Size	Flat File, Relational	Index Cache Size
Cache File Name Prefix	Flat File, Relational	Use only with persistent lookup cache. Specifies the file name prefix to use with persistent lookup cache files.

Some other properties for Flat Files are:

• Date time Format
• Thousand Separator
• Decimal Separator
• Case-Sensitive String Comparison
• Null Ordering
• Sorted Input

4: Condition Tab

We enter the Lookup Condition. The Power Center Server uses the lookup condition to test incoming values. We compare transformation input values with values in the lookup source or cache, represented by lookup ports.

• The data types in a condition must match.
• When we enter multiple conditions, the Power Center Server evaluates each condition as an AND, not an OR.
• The Power Center Server matches null values.
• The input value must meet all conditions for the lookup to return a value.
• =, >, <, >=, <=, != Operators can be used.
• Example: IN_DEPTNO = DEPTNO

In_DNAME = 'DELHI'

Tip: If we include more than one lookup condition, place the conditions with an equal sign first to optimize lookup performance.

Note:

1. We can use = operator in case of Dynamic Cache.

2. The Power Center Server fails the session when it encounters multiple keys for a Lookup transformation configured to use a dynamic cache. 

3. Connected Lookup Transformation

Example: To create a connected Lookup Transformation

• EMP will be source table. DEPT will be LOOKUP table.
• Create a target table CONN_Lookup_EXAMPLE in target designer. Table should contain all ports of EMP table plus DNAME and LOC as shown below.
• Create the shortcuts in your folder.

Creating Mapping:

1. Open folder where we want to create the mapping.

2. Click Tools -> Mapping Designer.

3. Click Mapping-> Create-> Give name. Ex: m_CONN_LOOKUP_EXAMPLE

4. Drag EMP and Target table.

5. Connect all fields from SQ_EMP to target except DNAME and LOC.

6. Transformation-> Create -> Select LOOKUP from list. Give name and click

Create.

7. The Following screen is displayed.

8. As DEPT is the Source definition, click Source and then Select DEPT.

9. Click Ok. 

10. Now Pass DEPTNO from SQ_EMP to this Lookup. DEPTNO from SQ_EMP will be named as DEPTNO1. Edit Lookup and rename it to IN_DEPTNO in ports tab.

11. Now go to CONDITION tab and add CONDITION.

DEPTNO = IN_DEPTNO and Click Apply and then OK.

Link the mapping as shown below:

12. We are not passing IN_DEPTNO and DEPTNO to any other transformation from LOOKUP; we can edit the lookup transformation and remove the OUTPUT check from them.

13. Mapping -> Validate

14. Repository -> Save

• Create Session and Workflow as described earlier. Run the workflow and see the data in target table.
• Make sure to give connection information for all tables.
• Make sure to give connection for LOOKUP Table also.

We use Connected Lookup when we need to return more than one column from Lookup table.There is no use of Return Port in Connected Lookup.

SEE PROPERTY TAB FOR ADVANCED SETTINGS

4. Unconnected Lookup Transformation

An unconnected Lookup transformation is separate from the pipeline in the mapping. We write an expression using the :LKP reference qualifier to call the lookup within another transformation.

Steps to configure Unconnected Lookup:

1. Add input ports.
2. Add the lookup condition.
3. Designate a return value.
4. Call the lookup from another transformation.

Example: To create a unconnected Lookup Transformation

• EMP will be source table. DEPT will be LOOKUP table.
• Create a target table UNCONN_Lookup_EXAMPLE in target designer. Table should contain all ports of EMP table plus DNAME as shown below.
• Create the shortcuts in your folder.

Creating Mapping:

1. Open folder where we want to create the mapping.

2. Click Tools -> Mapping Designer.

3. Click Mapping-> Create-> Give name. Ex: m_UNCONN_LOOKUP_EXAMPLE

4. Drag EMP and Target table.

5. Now Transformation-> Create -> Select EXPRESSION from list. Give name

and click Create. Then Click Done.

6. Pass all ports from SQ_EMP to EXPRESSION transformation.

7. Connect all fields from EXPRESSION to target except DNAME.

8. Transformation-> Create -> Select LOOKUP from list. Give name and click

Create.

9. Follow the steps as in Connected above to create Lookup on DEPT table.

10. Click Ok.

11. Now Edit the Lookup Transformation. Go to Ports tab.

12. As DEPTNO is common in source and Lookup, create a port IN_DEPTNO

ports tab. Make it Input port only and Give Datatype same as DEPTNO.

13. Designate DNAME as Return Port. Check on R to make it. 

14. Now add a condition in Condition Tab.

DEPTNO = IN_DEPTNO and Click Apply and then OK.

15. Now we need to call this Lookup from Expression Transformation.

16. Edit Expression t/f and create a new output port out_DNAME of data type as DNAME. Open the Expression editor and call Lookup as given below:

We double click Unconn in bottom of Functions tab and as we need only

DEPTNO, we pass only DEPTNO as input.

17. Validate the call in Expression editor and Click OK.

18. Mapping -> Validate

19. Repository Save.

• Create Session and Workflow as described earlier. Run the workflow and see the data in target table.
• Make sure to give connection information for all tables.
• Make sure to give connection for LOOKUP Table also.

5. Lookup Caches

We can configure a Lookup transformation to cache the lookup table. The Integration Service (IS) builds a cache in memory when it processes the first row of data in a cached Lookup transformation.

The Integration Service also creates cache files by default in the $PMCacheDir. If the data does not fit in the memory cache, the IS stores the overflow values in the cache files. When session completes, IS releases cache memory and deletes the cache files.

• If we use a flat file lookup, the IS always caches the lookup source.
• We set the Cache type in Lookup Properties.

Lookup Cache Files

1. Lookup Index Cache:

• Stores data for the columns used in the lookup condition.

2. Lookup Data Cache:

• For a connected Lookup transformation, stores data for the connected output ports, not including ports used in the lookup condition.
• For an unconnected Lookup transformation, stores data from the return port.

Types of Lookup Caches:

1. Static Cache

By default, the IS creates a static cache. It caches the lookup file or table and Looks up values in the cache for each row that comes into the transformation.The IS does not update the cache while it processes the Lookup transformation.

2. Dynamic Cache

To cache a target table or flat file source and insert new rows or update existing rows in the cache, use a Lookup transformation with a dynamic cache.

The IS dynamically inserts or updates data in the lookup cache and passes data to the target. Target table is also our lookup table. No good for performance if table is huge.

3. Persistent Cache

If the lookup table does not change between sessions, we can configure the Lookup transformation to use a persistent lookup cache.

The IS saves and reuses cache files from session to session, eliminating the time Required to read the lookup table.

4. Recache from Source

If the persistent cache is not synchronized with the lookup table, we can Configure the Lookup transformation to rebuild the lookup cache.If Lookup table has changed, we can use this to rebuild the lookup cache. 

5. Shared Cache

•  Unnamed cache: When Lookup transformations in a mapping have compatible caching structures, the IS shares the cache by default. You can only share static unnamed caches.
•  Named cache: Use a persistent named cache when we want to share a cache file across mappings or share a dynamic and a static cache. The caching structures must match or be compatible with a named cache. You can share static and dynamic named caches.

Building Connected Lookup Caches

We can configure the session to build caches sequentially or concurrently.

• When we build sequential caches, the IS creates caches as the source rows enter the Lookup transformation.
• When we configure the session to build concurrent caches, the IS does not wait for the first row to enter the Lookup transformation before it creates caches. Instead, it builds multiple caches concurrently.

1. Building Lookup Caches Sequentially: 

2. Building Lookup Caches Concurrently: 

• To configure the session to create concurrent caches

Edit Session -> In Config Object Tab-> Additional Concurrent Pipelines for

Lookup Cache Creation -> Give a value here (Auto By Default)

Note: The IS builds caches for unconnected Lookups sequentially only.

Update Strategy Transformation

• Active and Connected Transformation

Till now, we have only inserted rows in our target tables. What if we want to update, delete or reject rows coming from source based on some condition?

Example: If Address of a CUSTOMER changes, we can update the old address or keep both old and new address. One row is for old and one for new. This way we maintain the historical data.

Update Strategy is used with Lookup Transformation. In DWH, we create a Lookup on target table to determine whether a row already exists or not. Then we insert, update, delete or reject the source record as per business need.

In Power Center, we set the update strategy at two different levels:

1. Within a session
2. Within a Mapping

1. Update Strategy within a session:

When we configure a session, we can instruct the IS to either treat all rows in the same way or use instructions coded into the session mapping to flag rows for different database operations.

Session Configuration:

Edit Session -> Properties -> Treat Source Rows as: (Insert, Update, Delete, and Data Driven). Insert is default. Specifying Operations for Individual Target Tables:

You can set the following update strategy options:

Insert: Select this option to insert a row into a target table.

Delete: Select this option to delete a row from a table.

Update: We have the following options in this situation:

•  Update as Update. Update each row flagged for update if it exists in the target table.
•  Update as Insert. Inset each row flagged for update.
•  Update else Insert. Update the row if it exists. Otherwise, insert it.

Truncate table: Select this option to truncate the target table before loading data.

2. Flagging Rows within a Mapping

Within a mapping, we use the Update Strategy transformation to flag rows for insert, delete, update, or reject.

Operation	Constant	Numeric Value
INSERT	DD_INSERT	0
UPDATE	DD_UPDATE	1
DELETE	DD_DELETE	2
REJECT	DD_REJECT	3

Update Strategy Expressions:

Frequently, the update strategy expression uses the IIF or DECODE function from the transformation language to test each row to see if it meets a particular condition.

IIF( ( ENTRY_DATE > APPLY_DATE), DD_REJECT, DD_UPDATE )

Or

IIF( ( ENTRY_DATE > APPLY_DATE), 3, 2 )

• The above expression is written in Properties Tab of Update Strategy T/f.
• DD means DATA DRIVEN

Forwarding Rejected Rows:

We can configure the Update Strategy transformation to either pass rejected rows to the next transformation or drop them.

Steps:

1. Create Update Strategy Transformation
2. Pass all ports needed to it.
3. Set the Expression in Properties Tab.
4. Connect to other transformations or target.

Performance tuning:

1. Use Update Strategy transformation as less as possible in the mapping.
2. Do not use update strategy transformation if we just want to insert into target table, instead use direct mapping, direct filtering etc.
3. For updating or deleting rows from the target table we can use Update Strategy transformation itself

JOINER TRANSFORMATION

• Connected and Active Transformation
• Used to join source data from two related heterogeneous sources residing in Different locations or file systems. Or, we can join data  from the same source.
• If we need to join 3 tables, then we need 2 Joiner Transformations.
• The Joiner transformation joins two sources with at least one matching port. The Joiner transformation uses a condition that matches one or more pairs of Ports between the two sources.

Example: To join EMP and DEPT tables.

• EMP and DEPT will be source table.
• Create a target table JOINER_EXAMPLE in target designer. Table should Contain all ports of EMP table plus DNAME and LOC as shown below.
• Create the shortcuts in your folder.

Creating Mapping:

1. Open folder where we want to create the mapping.
2. Click Tools -> Mapping Designer.
3. Click Mapping-> Create-> Give mapping name. Ex: m_joiner_example
4. Drag EMP, DEPT, and Target. Create Joiner Transformation. Link as shown below. 

5. Specify the join condition in Condition tab. See steps on next page.

6. Set Master in Ports tab. See steps on next page.

7. Mapping -> Validate

8. Repository -> Save.

• Create Session and Workflow as described earlier. Run the Work flow and see the data in target table.
• Make sure to give connection information for all tables.

JOIN CONDITION:

The join condition contains ports from both input sources that must match for the Power Center Server to join two rows.

Example: DEPTNO=DEPTNO1 in above.

1. Edit Joiner Transformation -> Condition Tab
2. Add condition

• We can add as many conditions as needed.
• Only = operator is allowed.

If we join Char and Varchar data types, the Power Center Server counts any spaces that pad Char values as part of the string. So if you try to join the following:

Char (40) = “abcd” and Varchar (40) = “abcd”

Then the Char value is “abcd” padded with 36 blank spaces, and the Power Center Server does not join the two fields because the Char field contains trailing spaces.

Note: The Joiner transformation does not match null values.

MASTER and DETAIL TABLES

In Joiner, one table is called as MASTER and other as DETAIL.

• MASTER table is always cached. We can make any table as MASTER.
• Edit Joiner Transformation -> Ports Tab -> Select M for Master table.

Table with less number of rows should be made MASTER to improve Performance.

Reason:

• When the Power Center Server processes a Joiner transformation, it reads rows from both sources concurrently and builds the index and data cache based on the master rows. So table with fewer rows will be read fast and cache can be made as table with more rows is still being read.
• The fewer unique rows in the master, the fewer iterations of the join comparison occur, which speeds the join process.

JOINER TRANSFORMATION PROPERTIES TAB

•  Case-Sensitive String Comparison: If selected, the Power Center Server uses case-sensitive string comparisons when performing joins on string columns.
•  Cache Directory: Specifies the directory used to cache master or detail rows and the index to these rows.
•  Join Type: Specifies the type of join: Normal, Master Outer, Detail Outer, or Full Outer.

 Tracing Level

 Joiner Data Cache Size

 Joiner Index Cache Size

 Sorted Input

JOIN TYPES

In SQL, a join is a relational operator that combines data from multiple tables into a single result set. The Joiner transformation acts in much the same manner, except that tables can originate from different databases or flat files.

Types of Joins:

• Normal
• Master Outer
• Detail Outer
• Full Outer

Note: A normal or master outer join performs faster than a full outer or detail outer join.

Example: In EMP, we have employees with DEPTNO 10, 20, 30 and 50. In DEPT, we have DEPTNO 10, 20, 30 and 40. DEPT will be MASTER table as it has less rows.

Normal Join:

With a normal join, the Power Center Server discards all rows of data from the master and detail source that do not match, based on the condition.

• All employees of 10, 20 and 30 will be there as only they are matching.

Master Outer Join:

This join 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.

• All data of employees of 10, 20 and 30 will be there.
• There will be employees of DEPTNO 50 and corresponding DNAME and LOC Columns will be NULL.

Detail Outer Join:

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

• All employees of 10, 20 and 30 will be there.
• There will be one record for DEPTNO 40 and corresponding data of EMP columns will be NULL.

Full Outer Join:

A full outer join keeps all rows of data from both the master and detail sources.

• All data of employees of 10, 20 and 30 will be there.
• There will be employees of DEPTNO 50 and corresponding DNAME and LOC Columns will be NULL.
• There will be one record for DEPTNO 40 and corresponding data of EMP Columns will be NULL.

USING SORTED INPUT

• Use to improve session performance.
• to use sorted input, we must pass data to the Joiner transformation sorted by the ports that are used in Join Condition.
• We check the Sorted Input Option in Properties Tab of the transformation.
• If the option is checked but we are not passing sorted data to the Transformation, then the session fails.
• We can use SORTER to sort data or Source Qualifier in case of Relational tables.

JOINER CACHES

Joiner always caches the MASTER table. We cannot disable caching. It builds Index cache and Data Cache based on MASTER table.

1) Joiner Index Cache:

• All Columns of MASTER table used in Join condition are in JOINER INDEX CACHE.

· Example: DEPTNO in our mapping.

2) Joiner Data Cache:

• Master column not in join condition and used for output to other transformation or target table are in Data Cache.

· Example: DNAME and LOC in our mapping example.

Performance Tuning:

• Perform joins in a database when possible.
• Join sorted data when possible.
• For a sorted Joiner transformation, designate as the master source the source with fewer duplicate key values.
• Joiner can't be used in following conditions:

1. Either input pipeline contains an Update Strategy transformation.
2. We connect a Sequence Generator transformation directly before the Joiner transformation