working with power center 8 desinger

8/3/2019 Working With Power Center 8 Desinger

1/67

INFORMATICAINFORMATICAPOWERCENTER 8.1.0POWERCENTER 8.1.0

DESIGNER


2/67

2integration * intelligence * insight

ContentContent

WORKING WITH POWERCENTER 8 DESIGNER


3/67


Designer OverviewDesigner Overview

Designer

Designer is used to create mappings that contain transformation instructions for theIntegration Service. The Designer has the following tools that we use to analyze sources,design target schemas, and build source-to-target mappings.

Source Analyzer

It imports or creates source definitions.

Target Designer

It imports or creates target definitions.

Transformation Developer

Develop transformations to use in mappings. we can also develop user-definedfunctions to use in expressions.

Mapplets Designer

It Creates sets of transformations to use in mappings.

Mapping Designer

It Creates mappings that the Integration Service uses to extract, transform, and loaddata.


4/67



The following things are displayed in

Designer

Navigator

It connect to repositories, and openfolders within the Navigator. we canalso copy objects and createshortcuts within the Navigator.

Workspace

It opens different tools in thiswindow to create and editrepository objects, such as sources,targets, mapplets, transformations,and mappings.

Output

View details about tasks youperform, such as saving your workor validating a mapping.

Designer Windows


5/67



Status barIt Displays the status of theoperation you perform.

OverviewAn optional window to simplifyviewing a workspace thatcontains a large mapping or

multiple objects. Outlines thevisible area in the workspaceand highlights selected objectsin color.

Instance dataView transformation data whileyou run the Debugger to debug

a mapping.

Target dataView target data while you runthe Debugger to debug amapping.

Designer Windows


6/67



Informatica PowerCenter 8 can access the following data sources and load the data

into the following targets .

Sources

Relational

Oracle

Sybase ASE

Informix

IBM DB2

Microsoft SQL Server

Teradata

File

Flat file

COBOL file

XML file

web log

Application

Hyperion Essbase

IBM MQSeries

IBM DB2 OLAP Server

JMS

Microsoft Message Queue

Mainframe

Adabas

Datacom

IBM DB2 OS/390

IBM DB2 OS/400

Other

Microsoft Excel

Microsoft Access

External web services

PeopleSoft

SAP NetWeaver

SAS

Siebel

TIBCO WebMethods

IDMS

IDMS-X

IMS

VSAM


7/67



Targets

Relational

Oracle

Sybase ASE

Informix

IBM DB2

Microsoft SQL Server Teradata

File

Flat file

XML file

Application

Hyperion Essbase

IBM MQSeries

IBM DB2 OLAP Server

JMS Microsoft Message Queue

Mainframe

IBM DB2 OS/390

IBM DB2 OS/400

Other

Microsoft Access

External web services

MY SAP

PeopleSoft EPM

SAP BW

SAS

Siebel TIBCO

WebMethods

VSAM


8/67


About TransformationAbout Transformation

The transfer of data is called transformation.

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

We configure logic in a transformation that the Integration Service uses to transform data.

The Designer provides a set of transformations that perform specific functions.

Transformations in a mapping represent the operations the Integration Service performs on thedata.

Data passes into and out of transformations through ports that we link in a mapping or mapplet.

Transformations can be Active orPassive.

An active transformation can change the number of rows that pass through it.

A passive transformation does not change the number of rows that pass through it.

Transformations can be connected to the data flow.

An unconnected transformation is not connected to other transformations in the mapping.

It is called within another transformation, and returns a value to that transformation.


9/67


About TransformationAbout Transformation

Tasks to incorporate atransformation into amapping

Create thetransformation

Configure the

transformation

Link thetransformation toothertransformationsand targetdefinitions

Mapping Designer

TransformationDeveloper

Mapplet Designer

Designer Transformations

Aggregator- to do things like "group by".Expression - to use various expressions.

Filter- to filter data with single condition.

Joiner- to make joins between separate databases, file, ODBC sources.

Lookup - to create local copy of the data.

Normalizer- to transform denormalized data into normalized data.

Rank - to select only top (or bottom) ranked data.

Sequence Generator- to generate unique IDs for target tables.

Source Qualifier- to filter sources (SQL, select distinct, join, etc.)

Stored Procedure - to run stored procedures in the database - andcapture their returned values.

Update Strategy - to flag records in target for insert, delete, update(defined inside a mapping).

Router- same as filter but with multiple conditions

Java Transformation- It provides a simple native programming interfaceto define transformation functionality with the Java programminglanguage.

Reusable transformation- is a transformation that can be used inmultiple mappings


10/67


Types Of TransformationTypes Of Transformation

Active Transformation

Filter

Router

Update Strategy

Aggregator

Sorter

Rank

Joiner

Normalizer

Passive transformations

Sequence Generator

Stored Procedure

Expression

Lookup


11/67


Aggregator TransformationAggregator Transformation

The Aggregator is an active transformation. The Aggregator transformation allow us to perform aggregate calculations, such as averages

and sums. The Aggregator transformation is unlike the Expression transformation, in that we can use the

Aggregator transformation to perform calculations on groups. The Expression transformation permit us to perform calculations on a row-by-row basis only. We can use conditional clauses to filter rows, providing more flexibility than SQL language. The Integration Services performs aggregate calculations as it reads, and stores necessary

data group and row data in an aggregate cache.

Components of the Aggregator Transformation Aggregate expression Group by port Sorted input Aggregate cache

Aggregate Expression

An aggregate expression can include conditional clauses and non-aggregate functions. It can

also include one aggregate function nested within another aggregate function, such as. MAX( COUNT( ITEM )

Aggregate Functions The aggregate functions can be used within an Aggregator transformation. We can nest one aggregate function within another aggregate function. AVG COUNT


12/67


Aggregator TransformationAggregator Transformation

Aggregate Functions

FIRST LAST MEDIAN MAX MIN STDDEV PERCENTILE SUM VARIANCE

Conditional Clauses

We use conditional clauses in the aggregate expression to reduce the number of rows usedin the aggregation. The conditional clause can be any clause that evaluates to TRUE orFALSE.

Null Values in Aggregate Functions

When we configure the Integration Service, we can choose how we want the IntegrationService to handle null values in aggregate functions. We can choose to treat null values inaggregate functions as NULL or zero. By default, the Integration Service treats null valuesas NULL in aggregate functions.


13/67


Creating Aggregator TransformationCreating Aggregator Transformation

In the Mapping Designer, click Transformation > Create.

Select the Aggregator transformation. Enter a name for theAggregator, click Create. Then click Done.

The Designer creates the Aggregator transformation.

Drag the ports to the Aggregator transformation.

The Designer creates input/output ports for each port weinclude.

Double-click the title bar of the transformation to open theEdit Transformations dialog box .

Select the Ports tab.

Click the group by option for each column you want theAggregator to use in creating groups.

Click Add and enter a name and data type for theaggregate expression port. Make the port an output port

by clearing Input (I). Click in the right corner of theExpression field to open the Expression Editor. Enter theaggregate expression, click Validate, and click OK.

Add default values for specific ports.

Select the Properties tab. Enter settings as necessary. Click OK.

Choose Repository-Save.


14/67


Expression TransformationExpression Transformation

We can use the Expression transformation to calculate values in a single row before wewrite to the target

We can use the Expression transformation to test conditional statements

To perform calculations involving multiple rows, such as sums or averages we can useaggregator transformation

We can use the Expression transformation to perform any non-aggregate calculations

Creating an Expression Transformation

In the Mapping Designer, click Transformation > Create.Select the Expression transformation and click OK.

The naming convention for Expression transformations isEXP_TransformationName

Create the input ports If we have the input transformation available, we can

select Link Columns from the Layout menu and thendrag each port used in the calculation into theExpression transformation or we can open thetransformation and create each port manually.

Repeat the previous step for each input port we want toadd to the expression

Create the output ports we need


15/67


Expression TransformationExpression Transformation

Setting Expression in ExpressionTransformation

Enter the expression in theExpression Editor we havedisable to in port.

Check the expression syntax byclicking Validate.

Connect to Next Transformation

Connect the output ports to thenext transformation or target.

Select a Tracing Level on theProperties Tab

Select a tracing level on theProperties tab to determine theamount of transaction detailreported in the session log file.



16/67


Filter TransformationFilter Transformation

A Filter transformation is an Active Transformation.

We can filter rows in a mapping with Filter transformation. We pass all the rows from a source transformation through the Filter transformation and

then enter a filter condition for the transformation.

All ports in a Filter transformation are input/output, and only rows that meet the conditionpass through the Filter transformation.

Creating a Filter Transformation

In the Mapping Designer, click Transformation > Create.Select the Filter transformation. Enter a name, and clickOK.

The naming convention for Filter transformations isFIL_TransformationName.

Select and drag all the ports from a source qualifier or othertransformation to add them to the Filter transformation.

After we select and drag ports, copies of these portsappear in the Filter transformation. Each column has bothan input and an output port.

Double-click the title bar of the filter transformation to edit

transformation properties.


17/67


Filter TransformationFilter Transformation

Click the Value section of the condition, and then click the Open button. The Expression Editor appears. Enter the filter condition we want to apply. Use values from one of the input ports in the transformation as part of this condition

However, we can also use values from output ports in other transformations. We may have to fix syntax errors before continuing. Click OK.

Select the Tracing Level, and click OK to return to the Mapping Designer. Choose Repository-Save.

Filter Transformation Tips

Use the Filter transformation early in the mapping.

Use the Source Qualifier transformation to filter.


18/67


Joiner TransformationJoiner Transformation

A Joiner transformation is an active transformation.

Joiner transformation is used to join source data from two related heterogeneous sourcesresiding in different locations or file systems.

We can also join data from the same source.

The Joiner transformation joins sources with at least one matching column.

The Joiner transformation uses a condition that matches one or more pairs of columnsbetween the two sources.

We can use the following sources

Two relational tables existing in separate databases. Two flat files in potentially different file systems. Two different ODBC sources. A relational table and an XML source. A relational table and a flat file source. Two instances of the same XML source.

Creating a Joiner Transformation

In the Mapping Designer, click Transformation > Create. Select the Joiner transformation.Enter a name, and click OK.

The naming convention for Joiner transformations is JNR_TransformationName.


19/67



Drag all the input/output ports from the first source into the Joiner transformation.

The Designer creates input/output ports for the source fields in the Joiner transformation asdetail fields by default. We can edit this property later.

Select and drag all the input/output ports from the second source into the Joinertransformation.

The Designer configures the second set of source fields and master fields by default.

Edit Transformation

Double-click the title bar of the Joinertransformation to open the EditTransformations dialog box.

Select the port tab. Add default values for specific ports

as necessary.Setting the Condition

Select the Condition tab and set thecondition.

Click the Add button to add acondition.

Click the Properties tab and configureproperties for the transformation.

Click OK .


20/67



Defining the Join Type

Join is a relational operator that combines data from multiple tables into a single result set.

We define the join type on the Properties tab in the transformation.

The Joiner transformation supports the following types of joins.

Normal Master Outer

Detail Outer Full Outer

Joiner Transformation Tips

Perform joins in a database when possible.

Join sorted data when possible.

For an unsorted Joiner transformation, designate the source with fewer rows as the mastersource.

For a sorted Joiner transformation, designate the source with fewer duplicate key values asthe master source.


21/67


Lookup TransformationLookup Transformation

A Lookup transformation is a passive transformation.

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 thePowerCenter Client and Integration Service can connect.

We can Use multiple Lookup transformations in a mapping.

The Integration Service queries the lookup source based on the lookup ports in thetransformation.

It compares Lookup transformation port values to lookup source column values based onthe lookup condition.


22/67


Connected Lookup

Receives input values directly fromthe pipeline

Cache includes all lookup columnsused in the mapping

If there is no match for the lookupcondition, it returns thedefault value for all output ports

Pass multiple output values toanother transformation

Supports user-defined defaultvalues

Types of Lookup Transformation

Unconnected Lookup

Receives input values from othertransformation calling: LKP expression

You can use a static cache

Cache includes all lookup/output ports inthe lookup condition

If there is no match for the lookupcondition, returns null

Pass one output value to anothertransformation

Does not support user-defined defaultvalues


23/67



Tasks Of Lookup Transformations

Get a related value.

Perform a calculation.

Update slowly changing dimension tables.

Connected or unconnected.

Cached or uncached.Lookup Components

We have to define the following components when weconfigure a Lookup transformation in a mapping.

Lookup source

Ports

Properties

Condition

Metadata extensions

Edit Transformation


24/67



Creating a Lookup Transformation

In the Mapping Designer, clickTransformation > Create. Select theLookup transformation. Enter a name forthe transformation and Click OK. Thenaming convention for Lookuptransformation is LKP_TransformationName.

In the Select Lookup Table dialog box, we

can choose the following options. Choose an existing table or file

definition.

Choose to import a definition from arelational table or file.

Skip to create a manual definition.

If we want to manually define the lookuptransformation, click the Skip button.

Define input ports for each Lookupcondition we want to define.


25/67



For Lookup transformations that use a dynamic lookup

cache, associate an input port or sequence ID with eachlookup port.

On the Properties tab, set the properties for the lookup.

Click OK.

Configuring Unconnected Lookup Transformations

An unconnected Lookup transformation is separatefrom the pipeline in the mapping. we write anexpression using the :LKP reference qualifier to callthe lookup within another transformation.

Adding Input Ports.

Adding the Lookup Condition.

ITEM_ID = IN_ITEM_IDPRICE


26/67



Setting the properties to port tab And properties tab

Lookup Transformation Tips

Port Tab Properties Tab

Add an index to the columns used in alookup condition

Place conditions with an equalityoperator (=) first.

Cache small lookup tables.

Join tables in the database.

Use a persistent lookup cache forstatic lookups.

Call unconnected Lookuptransformations with the :LKP

reference qualifier.


27/67


Lookup CachesLookup Caches

The Integration Service builds a cache in memory when it processes the first row of data ina cached Lookup transformation.

It allocates memory for the cache based on the amount we configure in the transformationor session properties.

The Integration Service stores condition values in the index cache and output values in thedata cache.

The Integration Service queries the cache for each row that enters the transformation.

The Integration Service also creates cache files by default in the $PMCacheDir.

Types of lookup caches

Persistent cache Recache from database Static cache Dynamic cache

Shared cache


28/67


Rank TransformationRank Transformation

The Rank transformation is Active Transformation

The Rank transformation allow us to select only the topor bottom rank of data.

The Rank transformation differs from thetransformation functions MAX and MIN, to select agroup of top or bottom values, not just one value.

Creating Rank Transformation

In the Mapping Designer, click Transformation >Create. Select the Rank transformation. Enter aname for the Rank. The naming convention forRank transformations isRNK_TransformationName.

Enter a description for the transformation. Thisdescription appears in the Repository Manager.

Click Create, and then click Done.

The Designer creates the Rank transformation.

Link columns from an input transformation to theRank transformation.

Click the Ports tab, and then select the Rank (R)option for the port used to measure ranks.

If we want to create groups for ranked rows,select Group By for the port that defines thegroup. Port Tab


29/67


Click the Properties tab and select whether wewant the top or bottom rank

For the Number ofRanks option, enter thenumber of rows we want to select for the rank.

Change the otherRank transformationproperties, if necessary.

Click OK.

Click Repository > Save.

Properties Tab

Rank TransformationRank Transformation


30/67


Sequence Generator TransformationSequence Generator Transformation

A Sequence Generator transformation is a passive transformation. The Sequence Generator transformation generates numeric values. We can use the Sequence Generator to create unique primary key values, cycle through a

sequential range of numbers.

The Sequence Generator transformation is a connected transformation.

The Integration Service generates a value each time a row enters a connectedtransformation, even if that value is not used.

When NEXTVAL is connected to the input port of another transformation, the IntegrationService generates a sequence of numbers.

When CURRVAL is connected to the input port of another transformation, the IntegrationService generates the NEXTVAL value plus one.

We can make a Sequence Generator reusable, and use it in multiple mappings.

Web might reuse a Sequence Generator when we perform multiple loads to a single target.

If we have a large input file we can separate into three sessions running in parallel, we canuse a Sequence Generator to generate primary key values.

If we use different Sequence Generators, the Integration Service might accidentallygenerate duplicate key values.

Instead, we can use the reusable Sequence Generator for all three sessions to provide aunique value for each target row.


31/67



Tasks with a Sequence Generator Transformation

Create keys

Replace missing values Cycle through a sequential range of numbers

Creating a Sequence Generator Transformation

In the Mapping Designer, select Transformation-Create. Selectthe Sequence Generator transformation. The namingconvention for Sequence Generator transformations isSEQ_TransformationName.

Enter a name for the Sequence Generator, and click Create.Click Done.

The Designer creates the Sequence Generator transformation.

Edit Transformation

Double-click the title bar of the transformation to open the Edit

Transformations dialog box.Properties Tab

Select the Properties tab. Enter settings as necessary. Click OK. To generate new sequences during a session, connect the

NEXTVAL port to at least one transformation in the mapping.



32/67



Sequence Generator Ports

The Sequence Generator provides two output ports:NEXTVAL and CURRVAL.

Use the NEXTVAL port to generate a sequence ofnumbers by connecting it to a transformation or target.

We connect the NEXTVAL port to a downstreamtransformation to generate the sequence based on theCurrent Value and Increment By properties.

Connect NEXTVAL to multiple transformations togenerate unique values for each row in eachtransformation.

We might connect NEXTVAL to two target tables in amapping to generate unique primary key values.

NEXTVAL to Two Target Tables in a Mapping We configure the Sequence Generator transformation as

follows: Current Value = 1, Increment By = 1.

When we run the workflow, the Integration Servicegenerates the following primary key values for theT_ORDERS_PRIMARY andT_ORDERS_FOREIGNtarget tables.

T_ORDERS_PRI

MARY TABLE:

PRIMARY KEY

T_ORDERS_FOR

EIGN TABLE:

PRIMARY KEY

1 2

3 4

5 6

7 8

9 10


33/67



Sequence Generator and Expression

Transformation

We configure the SequenceGenerator transformation asfollows: Current Value = 1,Increment By = 1

Output

key values for theT_ORDERS_PRIMARY andT_ORDERS_FOREIGN target

tables

T_ORDERS_PRIMARY TABLE:PRIMARY KEY

T_ORDERS_FOREIGN TABLE:PRIMARY KEY

1 1

2 2

3 3

4 4

5 5


34/67



CURRVAL is the NEXTVAL value plus one orNEXTVAL plus the Increment By value.

We typically only connect the CURRVAL port whenthe NEXTVAL port is already connected to adownstream transformation.

When a row enters the transformation connected tothe CURRVAL port, the Informatica Server passesthe last-created NEXTVAL value plus one.

Connecting CURRVAL and NEXTVAL Ports to a

Target We configure the Sequence Generator transformation

as follows: Current Value = 1, Increment By = 1.

When we run the workflow, the Integration Servicegenerates the following values for NEXTVAL andCURRVAL.

OUT PUT

When we run the workflow, the Integration Servicegenerates the following values for NEXTVAL andCURRVAL.

If we connect the CURRVAL port without connectingthe NEXTVAL port, the Integration Service passes aconstant value for each row.

NEXTVAL CURRVAL

1 2

2 3

3 4

4 5

5 6


35/67



Only the CURRVAL Port to a Target

For example, we configure the SequenceGenerator transformation as follows.

OUTPUT

Current Value = 1, Increment By = 1

When we run the workflow, the IntegrationService generates the following constantvalues for CURRVAL.

CURRVAL

11

1

1

1


36/67


Source Qualifier TransformationSource Qualifier Transformation

A Source Qualifier is an active transformation.

The Source Qualifier represents the rows that the Integration

Service reads when it executes a session.

When we add a relational or a flat file source definition to amapping source Qualifier transformation automaticallycomes.

Task of Source Qualifier Transformation

We can use the Source Qualifier to perform the followingtasks.

Join data originating from the same source database. Filter records 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 statementfor the Integration Service to read source data.

Default Query of Source Qualifier

For relational sources, the Integration Service generates aquery for each Source Qualifier when it runs a session.

The default query is a SELECT statement for each source

column used in the mapping.


37/67



To view the Default Query

To view the default query.

From the Properties tab, select SQL Query Click Generate SQL Click Cancel to exit

Example of source Qualifier Transformation

We might see all the orders for the month, includingorder number, order amount, and customer name.

The ORDERS table includes the order number andamount of each order, but not the customer name.To include the customer name, we need to join theORDERS and CUSTOMERS tables.

Setting the properties to Source Qualifier

Double-click the title bar of the transformation toopen the Edit Transformations dialog box.

Select the Properties tab. Enter settings asnecessary.


38/67



SQL Query We can give query in the Source Qualifier

transformation. From the Properties tab, select SQL Query The SQL

Editor displays. Click Generate SQL.Joining Source Data

We can use one Source Qualifier transformation to joindata from multiple relational tables. These tables mustbe accessible from the same instance or database

server. Use the Joiner transformation for heterogeneous

sources and to join flat files.

Sorted Ports

In the Mapping Designer, open a Source Qualifiertransformation, and click the Properties tab.

Click in Number of Sorted Ports and enter the number

of ports we want to sort. The Integration Service adds the configured number of

columns to an ORDER BY clause, starting from the topof the Source Qualifier transformation.

The source database sort order must correspond to thesession.


39/67


Stored procedure TransformationStored procedure Transformation

A Stored Procedure is a passive transformation

A Stored Procedure transformation is an important tool for populating and maintainingdatabases. Database administrators create stored procedures to automate tasks that are toocomplicated for standard SQL statements.

Stored procedures run in either connected or unconnected mode. The mode we usedepends on what the stored procedure does and how we plan to use it in a session. we canconfigure connected and unconnected Stored Procedure transformations in a mapping.

Connected: The flow of data through a mapping in connected mode also passesthrough the Stored Procedure transformation. All data entering the transformationthrough the input ports affects the stored procedure. We should use a connectedStored Procedure transformation when we need data from an input port sent as aninput parameter to the stored procedure, or the results of a stored procedure sent as anoutput parameter to another transformation.

Unconnected: The unconnected Stored Procedure transformation is not connecteddirectly to the flow of the mapping. It either runs before or after the session, or is calledby an expression in another transformation in the mapping.


40/67


Stored procedure TransformationStored procedure Transformation

Creating a Stored Procedure Transformation

After we configure and test a stored procedure in thedatabase, we must create the Stored Proceduretransformation in the Mapping Designer

To import a stored procedure In the Mapping Designer, click Transformation >Import Stored

Procedure. Select the database that contains the stored procedure from

the list of ODBC sources. Enter the user name, owner name,and password to connect to the database and click Connect Select the procedure to import and click OK..

The Stored Procedure transformation appears in the mapping.The Stored Procedure transformation name is the same asthe stored procedure we selected.

Open the transformation, and click the Properties tab

Select the database where the stored procedure exists fromthe Connection Information row. If we changed the name ofthe Stored Procedure transformation to something other thanthe name of the stored procedure, enter the Stored ProcedureName.

Click OK.

Click Repository > Save to save changes to the mapping.


41/67


Update StrategyUpdate Strategy

An Update Strategy is an active transformation .

When we design a data warehouse, we need to decide what type of information to store intargets. As part of the target table design, we need to determine whether to maintain all thehistoric data or just the most recent changes.

The model we choose determines how we handle changes to existing rows. InPowerCenter, we set the update strategy at two different levels.

Within a session

Within a mapping

Setting the Update Strategy

We use the following steps to define an update strategy

To control how rows are flagged for insert, update, delete, or reject within a mapping, addan Update Strategy transformation to the mapping. Update Strategy transformations areessential if we want to flag rows destined for the same target for different databaseoperations, or if we want to reject rows.

Define how to flag rows when we configure a session. We can flag all rows for insert,delete, or update, or we can select the data driven option, where the Integration Servicefollows instructions coded into Update Strategy transformations within the session mapping.

Define insert, update, and delete options for each target when we configure a session. On atarget-by-target basis, we can allow or disallow inserts and deletes.


42/67



Creating an Update Transformation

In the Mapping Designer, select Transformation-Create. Select the

Update transformation. The naming convention for Updatetransformations is UPD_TransformationName.

Enter a name for the Update transformation , and click Create. ClickDone.

The Designer creates the Update transformation.

Drag all ports from another transformation representing data we

want to pass through the Update Strategy transformation. In the Update Strategy transformation, the Designer creates a copy

of each port we drag. The Designer also connects the new port tothe original port. Each port in the Update Strategy transformation is acombination of input/output port.

Normally, we would select all of the columns destined for a particulartarget. After they pass through the Update Strategy transformation,this information is flagged for update, insert, delete, or reject.

Double-click the title bar of the transformation to open the EditTransformations dialog box.

Click the Properties tab.

Click the button in the Update Strategy Expression field.

The Expression Editor appears.


43/67



Enter an update strategy expression to flagrows as inserts, deletes, updates, or rejects.

Validate the expression and click OK. Click OK to save the changes. Connect the ports in the Update Strategy

transformation to another transformation or atarget instance.

Click Repository > SaveSetting the Update Strategy for a Session

When we configure a session, we haveseveral options for handling specific databaseoperations, including updates.

Specifying an Operation for All Rows

When we configure a session, we can selecta single database operation for all rows usingthe Treat Source Rows As setting.

Configure the Treat Source Rows As sessionproperty.

Treat Source Rows displays the options like. Insert Delete Update Data Driven


44/67



Specifying Operations for Individual Target Tables

Once we determine how to treat all rows in thesession, we also need to set update strategy optionsfor individual targets. Define the update strategyoptions in the Transformations view on Mapping tabof the session properties.

We can set the following update strategy options forIndividual Target Tables.

Insert. Select this option to insert a row into a targettable.

Delete. Select this option to delete a row from atable..

Update. You have the following options in thissituation.

Update as Update. Update each row flaggedfor update if it exists in the target table.

Update as Insert. Inset each row flagged forupdate. 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.


45/67


Router TransformationRouter Transformation

A Router transformation is an Active Transformation. A Router transformation is similar to a Filter transformation because both transformations

allow us to use a condition to test data. A Filter transformation tests data for one condition and drops the rows of data that do not

meet the condition. However, a Router transformation tests data for one or more conditionsand gives us the option to route rows of data that do not meet any of the conditions to adefault output group.

If we need to test the same input data based on multiple conditions, use a Routertransformation in a mapping instead of creating multiple Filter transformations to performthe same task.

Creating a Router Transformation In the Mapping Designer, click Transformation >

Create. Select the Router transformation. Enter aname for the transformation and Click OK.

The naming convention for router transformation isRTR_TransformationName.

Input values in the Router Transformation Select and drag all the desired ports from a

transformation to add them to the Routertransformation.

Double-click the title bar of the Router

transformation to edit transformation properties.


46/67



Setting the properties to port tab And properties tab

Port Tab Properties Tab

Group tab in Router Transformation

Click the Group Filter Conditionfield to open the Expression Editor.

Enter a group filter condition. Click Validate to check the syntax

of the conditions we entered. Click OK. Connect group output ports to

transformations or targets. Choose Repository-Save.


47/67



Router Transformation Components

A Router transformation has thefollowing types of groups.

Input Output

There are two types of output groups.

User-defined groups Default group

Working with Ports

A Router transformation has input ports andoutput ports.

Input ports reside in the input group, andoutput ports reside in the output groups.

We can create input ports by copyingthem from another transformation or bymanually creating them on the Ports tab. Port tab in Router Transformation


48/67



Connecting Router Transformations in a Mapping

When we connect transformations to a Routertransformation in a mapping consider the followingrules.

We can connect one group to one transformation ortarget.

Connect one port to Multiple Target

We can connect one output port in a group tomultiple transformations or targets.

Connect Multiple out ports to Multiple Target

We can connect multiple output ports in one groupto multiple transformations or targets.


49/67


Reusable TransformationReusable Transformation

Reusable transformation is a transformation that can be used in multiple mappings.

We can create most transformations as a non-reusable or reusable but only create theExternal Procedure transformation as a reusable transformation .

When we add a reusable transformation to a mapping, we add an instance of thetransformation. The definition of the transformation still exists outside the mapping.

Methods To Create Reusable Transformation

Design it in the Transformation Developer

In the Transformation Developer, we can build new reusable transformations.

Promote a non-reusable transformation from the Mapping Designer

After we add a transformation to a mapping, we can promote it to the status of reusabletransformation. The transformation designed in the mapping then becomes an instance of areusable transformation maintained elsewhere in the repository.


50/67


Reusable TransformationReusable Transformation

Creating Reusable Transformation

Goto transformation

developer>transformationtab>make reusable.

Changes that can invalidate mapping

When we delete a port or multiple ports in atransformation.

When we change a port datatype, youmake it impossible to map data from thatport to another port using an incompatibledatatype.

When we change a port name,expressions that refer to the port are nolonger valid.

When we enter an invalid expression in thereusable transformation, mappings that usethe transformation are no longer valid. TheIntegration Service cannot run sessionsbased on invalid mappings


51/67


Java TransformationJava Transformation

The Java transformation is a Active/Passive Connected transformation that provides a

simple native programming interface to define transformation functionality with the Javaprogramming language.

You create Java transformations by writing Java code snippets that define transformationlogic.

The Power Center Client uses the Java Development Kit (JDK) to compile the Java codeand generate byte code for the transformation. The Integration Service uses the JavaRuntime Environment (JRE) to execute generated byte code at run time.

Steps To Define Java Transformation

Create the transformation in the Transformation Developer or Mapping Designer.

Configure input and output ports and groups for the transformation. Use port names asvariables in Java code snippets.

Configure the transformation properties.

Use the code entry tabs in the transformation to write and compile the Java code for thetransformation.

Locate and fix compilation errors in the Java code for the transformation.


52/67


Java TransformationJava Transformation

Enter the ports and use that ports as identifier in javacode.

Go to java code and enter the java code and clickcompile and check the output in the output window.

Create session and workflow and run the session.

Functions

Some functions used in designer are

AVG

Syntax : AVG( numeric_value [, filter_condition ] )

MAX

Syntax: MAX( value [, filter_condition ] )

MIN

Syntax :MIN( value [, filter_condition ] )

INSTR

Syntax :INSTR( string, search_value [, start[,occurrence ] ] )

SUBSTR

Syntax :SUBSTR( string, start[, length ] )

IS_DATE

Syntax:IS_DATE( value )


53/67


Working With Flat FilesWorking With Flat Files

To use flat files as sources, targets, and

lookups in a mapping we must import orcreate the definitions in the repository .

We can import or create flat file sourcedefinitions in the Source Analyzer or createflat file target definitions in the TargetDesigner or import flat files lookups or useexisting file definitions in a Lookup

transformation. When we create a session with a file source,

we specify a source file location differentfrom the location we use , when we importthe file source definition.

Importing a flat file

Goto sources


54/67


Working With Flat FilesWorking With Flat Files

Editing a flat file definition

Table tab

Edit properties such as table name,business name, and flat file properties.

Columns tab

Edit column information such as column

names, datatypes, precision, and formats.

Properties tab

We can edit the default numeric anddatetime format properties in the Source

Analyzer and the Target Designer.

Metadata Extensions tabWe can extend the metadata stored in therepository by associating information withrepository objects, such as flat filedefinitions.


55/67


User Defined FunctionsUser Defined Functions

We can create user-defined functions usingthe PowerCenter transformation language.

Create user-defined functions to reuseexpression logic and build complexexpressions. User-defined functions areavailable to other users in a repository.

Once you create user-defined functions, wecan manage them from the User-DefinedFunction Browser dialog box. We can alsouse them as functions in the ExpressionEditor. They display on the User-DefinedFunctions tab of the Expression Editor.

We create a user-defined function in theTransformation Developer. Configure thefollowing information when we create auser-defined function.

Name

Type

Description

Arguments

Syntax

Steps to Create User-Defined Functions

In the Transformation Developer, clickTools > User-Defined Functions.

Click New

The Edit User-Defined Function dialogbox appears

Enter a function name

Select a function type

If we create a public user-definedfunction, we cannot change the functionto private when we edit the function.


56/67


User Defined FunctionsUser Defined Functions

Optionally, enter a description of the user-defined function.

We can enter up to 2,000 characters.

Create arguments for the user-defined function.

When we create arguments, configure the argument name, data type, precision, and scale.We can select transformation data types.

Click Launch Editor to create an expression that contains the arguments we defined.

Click OK

The Designer assigns the data type of the data the expression returns. The data types havethe precision and scale of transformation data types.

Click OK

The expression displays in the User-Defined Function Browser dialog box.


57/67


Mapplet DesignerMapplet Designer

A mapplet is a reusable object that we create in the Mapplet Designer. It contains a set oftransformations and we reuse that transformation logic in multiple mappings.

When we use a mapplet in a mapping, we use an instance of the mapplet. Like a reusabletransformation, any change made to the mapplet is inherited by all instances of the mapplet .

Usage of Mapplets

Include source definitionsUse multiple source definitions and source qualifiers to provide source data for a mapping.

Accept data from sources in a mapping

If we want the mapplet to receive data from the mapping, we use an Input transformation toreceive source data.

Include multiple transformations

A mapplet can contain as many transformations as you need. Pass data to multiple transformations

We can create a mapplet to feed data to multiple transformations.

Contain unused ports

We do not have to connect all mapplet input and output ports in a mapping

M l t D iM l t D i


58/67


Mapplet DesignerMapplet Designer

Limitations of Mapplets

We cannot connect a single port in the Input transformation to multiple transformations inthe mapplet.

An input transformation must receive data from a single active source.

A mapplet must contain at least one Input transformation or source definition with at leastone port connected to a transformation in the mapplet and same applies for output

transformation.

When a mapplet contains a source qualifier that has an override for the default SQL query,we must connect all of the source qualifier output ports to the next transformation within themapplet.

We cannot include PowerMart 3.5-style LOOKUP functions in a mapplet.

We cannot include the following objects : Normalizer transformations, Cobol sources, XMLSource Qualifier transformations, XML sources and targets, Pre- and post- session storedprocedures and other mapplets.

D t P filiD t P fili


59/67


Data ProfilingData Profiling

Data profiling is a technique used to analyze source data. PowerCenter Data Profiling can

help us to evaluate source data and detect patterns and exceptions. we can profile sourcedata to suggest candidate keys, detect data patterns and evaluate join criteria. Use Data Profiling to analyze source data in the following situations. During mapping development . During production to maintain data quality. To profile source data, we create a data profile. we can create a data profile based on a

source or mapplet in the repository. Data profiles contain functions that perform calculationson the source data.

The repository stores the data profile as an object. we can apply profile functions to acolumn within a source, to a single source, or to multiple sources.

We can create the following types of data profiles.

Auto profile

Contains a predefined set of functions for profiling source data. Use an auto profile duringmapping development.

Custom profileUse a custom profile during mapping development to validate documented business rulesabout the source data. we can also use a custom profile to monitor data quality or validatethe results of BI reports.



60/67



Steps To Create Auto Profile

When we create an auto profile, we canprofile groups or columns in the source.Or, we can profile the entire source.

To create an auto profile.

Select the source definition in theSource Analyzer or mapplet in theMapplet Designer you want to profile.

Launch the Profile Wizard from thefollowing Designer tools.

Source Analyzer. Click Sources >Profiling > Create Auto Profile.

Mapplet Designer. Click Mapplets> Profiling > Create Auto Profile.

You set the default data profileoptions to open the Auto ProfileColumn Selection dialog box whenyou create an auto profile.

The source definition contains 25or more columns.

Optionally, click Description to add a descriptionfor the data profile. Click OK.

Enter a description up to 200 characters. Optionally, select the groups or columns in the

source that you want to profile.

By default, all columns or groups are selected

Select Load Verbose Data if you want theIntegration Service to write verbose data to the

Data Profiling warehouse during the profilesession. By default, Load Verbose Data optionis disabled.

Click Next.

Select additional functions to include in the autoprofile. We can also clear functions we do notwant to include.



61/67



Optionally, click Save As Default to create new default functions based on the functionsselected here.

Optionally, click Profile Settings to enter settings for domain inference and structure inferencetuning.

Optionally, modify the default profile settings and click OK. Click Configure Session to configure the session properties after you create the data profile. Click Next if you selected Configure Session, or click Finish if you disabled Configure Session. The Designer generates a data profile and profile mapping based on the profile functions. Configure the Profile Run options and click Next. Configure the Session Setup options. Click Finish.



62/67



We can create a custom profile fromthe following Designer tools.

Source Analyzer. Click Sources >Profiling > Create Custom Profile.

Mapplet Designer. Click Mapplets> Profiling > Create CustomProfile.

Profile Manager. Click Profile >Create Custom.

To create a custom profile, completethe following.

Enter a data profile name andoptionally add a description.

Add sources to the data profile.

Add, edit, or delete a profilefunction and enable sessionconfiguration.

Configure profile functions.

Configure the profile sessionif we enable session

configuration.

P fil MP fil M


63/67


Profile ManagerProfile Manager

Profile Manager is a tool that helps tomanage data profiles. It is used to set default

data profile options, work with data profiles inthe repository, run profile sessions, viewprofile results, and view sources andmapplets with at least one profile defined forthem. When we launch the Profile Manager,we can access profile information for theopen folders in the repository.

There are two views in the Profile Manager

Profile View: The Profile View tabdisplays the data profiles in the openfolders in the repository.

Source View: The Source View tabdisplays the source definitions in theopen folders in the repository for which

we have defined data profiles.

Profile View

Source View

Debugger OverviewDebugger Overview


64/67



We can debug a valid mapping to gain troubleshooting information about data and error

conditions.

Debugger used in the following situations

Before we run a session

After we save a mapping, we can run some initial tests with a debug session

before we create and configure a session in the Workflow Manager.

After we run a session

If a session fails or if we receive unexpected results in the target, we can run theDebugger against the session. we might also run the Debugger against a session if

we want to debug the mapping using the configured session properties.

Create breakpoints. Create breakpoints in a mapping where we want the IntegrationService to evaluate data and error conditions.

Configure the Debugger. Use the Debugger Wizard to configure the Debugger for themapping. Select the session type the Integration Service uses when it runs Debugger.

Run the Debugger. Run the Debugger from within the Mapping Designer. When we runthe Debugger the Designer connects to the Integration Service. The Integration Serviceinitializes the Debugger and runs the debugging session and workflow.

Monitor the Debugger. While we run the Debugger, we can monitor the target data,transformation and mapplet output data, the debug log, and the session log.

Modify data and breakpoints. When the Debugger pauses, we can modify data and seethe effect on transformations, mapplets, and targets as the data moves through thepipeline. we can also modify breakpoint information.



65/67



Create Breakpoints

Goto mapping


66/67



Choose connections of target andsource and click next.

Click on next

Debug Indicators


67/67

The End

working with power center 8 desinger

Documents