Data Warehousing

Auto generate sequence numbers in Target tables using Oracle sequence

noreply@blogger.com (Kareem) — Thu, 03 Jan 2019 15:18:00 +0000

· Create a Target table with below structure by adding sequence column

CREATE TABLE "TARGET"."EMP_SEQ"

(

"SEQ_NO" NUMBER,

"EMPNO" NUMBER(4,0) NOT NULL ENABLE,

"ENAME" NVARCHAR2(10),

"JOB" NVARCHAR2(9),

"MGR" NUMBER(4,0),

"HIREDATE" DATE,

"SAL" NUMBER(7,2),

"COMM" NUMBER(7,2),

"DEPTNO" NUMBER(2,0),

PRIMARY KEY ("EMPNO")

);

· Create oracle sequence to generated the sequence values

CREATE SEQUENCE SEQ_NO

START WITH 1

INCREMENT BY 1;

· Create Trigger to assign the values from sequence to SEQ_NO column.

CREATE OR REPLACE TRIGGER SEQ_NO

BEFORE INSERT ON EMP_SEQ

FOR EACH ROW

BEGIN

SELECT SEQ_NO.nextval INTO :NEW.SEQ_NO FROM dual;

END;

· Creating a mapping as below after importing required source and Target definitions.

· Target table field SEQ_NO has the Oracle generated Sequence value in table. Below screen shot

Why Sorter is an Active transformation ?

noreply@blogger.com (Kareem) — Thu, 03 Jan 2019 11:26:00 +0000

The Sorter transformation is almost always changes the order of records - by delivering the records sorted by the sort keys.

There are some cases when a Sorter doesn't sort the data because of input records are already sorted. But still the Sorter MAY change the order of records, so it's always considered as an Active transformation.

The DISTINCT feature is just an add-on.

In most cases it will decrease the number of records (by removing duplicate records), but that doesn't impact the fact that the Sorter is an active transformation: the mere chance of changing the order of records is sufficient for the classification as an "Active transformation".

Informatica PowerCenter Architecture

noreply@blogger.com (Kareem) — Wed, 29 Aug 2018 11:03:00 +0000

Informatica Software Architecture illustrated as :

Informatica ETL product, known as Informatica Power Center consists of 3 main components.

1. Informatica PowerCenter Client Tools:These are the development tools installed at developer end. These tools enable a developer to

Define transformation process, known as mapping. (Power center Designer)
Define run-time properties for a mapping, known as sessions (Workflow Manager)
Monitor execution of sessions (Workflow Monitor)
Manage repository, useful for administrators (Repository Manager)

2. Informatica PowerCenter Repository:

Repository is the heart of Informatica tools. Repository is a kind of data inventory where all the data related to mappings, sources, targets etc is kept. This is the place where all the metadata for your application is stored. All the client tools and Informatica Server fetch data from Repository. Informatica client and server without repository is same as a PC without memory/harddisk, which has got the ability to process data but has no data to process. This can be treated as backend of Informatica. This architecture is visually explained in diagram below:

3. Informatica PowerCenter Server:

Server is the place, where all the executions take place. Server makes Physical connections to sources/targets, fetches data, applies the transformations mentioned in the mapping and loads the data in the target system.

This is the sufficient knowledge to start with Informatica. So lets go straight to development in Informatica.

Informatica Software Architecture illustrated as per knowledge, please feel free add/comment if anything i missed

Convert Julian date in Informatica

noreply@blogger.com (Kareem) — Wed, 29 Aug 2018 10:47:00 +0000

Dates in the Julian calendar are called Julian dates which are not supported in Informatica. We need to convert the date to Gregorian calendar for further Informatica processing.

We can use TO_DATE function to convert the Julian date to Gregorian in an expression and then load to target. The TO_DATE function converts a string with the format you specify to a date-time value. TO_DATE is generally used to convert strings from flat files to date-time values.

TO_DATE (DATE_JULIAN, ‘YYYYDDD’)

Let’s see an example, I have a flat file with employee joining date in Julian. I want to load this date as Gregorian to my target table.

Julian Date: 2015001 (YYYYDDD)
Converted Date : 01-JAN-15

We can use the below expression in an output port using Expression transformation. This will check whether the input Julian date is in ‘YYYYDDD’ format, if YES TO_DATE will convert the date to Gregorian and if NO To_DATE will use a default date of 01-01-0001 (01-JAN-01).

IIF(IS_DATE(Date_Join_Julian,’YYYYDDD’)=TRUE,
TO_DATE(Date_Join_Julian,’YYYYDDD’),TO_DATE(‘01010001′,’MMDDYYYY’))

Input Flat File – Joining Date is in Julian Format (YYYYDDD)

Mapping to Convert Julian to Gregorian Date Using Expression Transformation

Create an output port with Date/Time datatype

Expression Editor – Expression to convert Julian to Gregorian

Session Log – All 5 input rows are processed and load to target.

Target Table – Dates are now converted to Gregorian date format. Since the input dates are not valid for the last two records, it’s been converted to hard coded default values (01-JAN-01).

Save Informatica session logs with Timestamp

noreply@blogger.com (Kareem) — Wed, 18 Jul 2018 03:56:00 +0000

Save Informatica session logs with Timestamp

Informatica PowerCenter gives an option to save the session logs with Timestamp.

Below is the example:

For suppose if we have Session and we chose an option to save Session Logs by Runs (the default option). This property is found in Config Objects tab when we click on Edit Sessions. Once the session we can see the Session log from Workflow monitor and can see a Message similar to Below:

Message: Writing session output to log file [/Informatica_share/SessLogs/session_name.log].

However, if we enable Save Session Log by Timestamp. We get a message similar to below in Session Log.

Message: Writing session output to log file [/Informatica_share/SessLogs/.log.201810128082418].

Notice the session_name.log file name in both the cases. We notice that when we save the Session Log By

Timestamp, the timestamp is appended at the end of the log file name.

In case of Save Session Logs by Runs option we get another option which says,
Save session log for these runs We can specify any numeric value here. Note that for Save Session Log By Timestamp, this option is disabled as unique session log file for each session run is generated based on the session run timestamp.

Saving the multiple session logs for each run will cause space issues on File server. Keep in mind before performing the action.

How To Generate Dynamic Target File In Informatica Based On Column Value

noreply@blogger.com (Kareem) — Tue, 03 Jul 2018 02:30:00 +0000

Generate different flat file target based on the Location name, like separate files for Mumbai.dat, Bangalore.dat, and Delhi.dat

Source Table:

Dept name	Dept ID	Location
DWH	1	Mumbai
Java	2	Bangalore
Dot net	3	Delhi

Sort the source table by the column Location using a Sorter.
Create Expression transformation and create the below ports

IN_LOCATION=LOCATION
V_FLAG=IIF(IN_LOCATION=V_LOCATION,0,1)
V_LOCATION=IN_LOCATION
OUTPUT_FLAG (OUTPUT PORT) = V_FLAG

OUTPUT_FILE_NAME (OUTPUT PORT) = LOCATION ||'.dat'

**** This expression will check when Location changes (Eg.from Mumbai to Bangalore) ****

3. Now we need to connect the Expression Transformation to Transaction Control transformation. Informatica Power Centre allows us to control the roll back and commit on transaction based on set of rows that passes through the Transaction Control transformation. This allows to define your transaction whether it should be committed or rollback based on the rows that pass through, such as based on the Entry Date or some other column.

Use the following syntax for the expression:

IIF (CONDITION, VALUE1, VALUE2)

IIF(OUTPUT_FLAG = 1, TC_COMMIT_BEFORE, TC_CONTINUE_TRANSACTION)

****This expression will write to the output file when location changes (Eg.from Mumbai to Bangalore)

4. Connect to the Target Table

Note: We have to use special port called “Filename" port in the Target file definition.

Informaica Power Center v10 - Features

noreply@blogger.com (Kareem) — Mon, 02 Jul 2018 13:52:00 +0000

Introduction:

Informatica – the world’s leader in ETL market and number one independent software provider officially released Informatica version 10 – a transformative innovation for future of ‘all-things-data’ on October 13 2015.

Informatica v10 is an award-winning data management platform, designed to modernize and boost performance of enterprise data architectures.

With enhanced agility and performance combined with new features, Informatica v10 delivers accurate data at nearly any speed across today’s hybrid IT environments, both cloud and on premise.

Anil Chakravarthy – CEO and former chief product officer – Informatica Corp, in his own words:

“With Informatica v10, architects can modernize their IT environment by leveraging top performance and improved data governance for modern data warehouses, along with the flexibility of full Cloud support via a data hub, and increased end-to-end data integration agility.”

Informatica v10 Features

Informatica v10 is a solution to deliver performance, flexibility, usability, governance and productivity across its three core components:

1. Informatica Power Center

2. Informatica Data Quality

3. Informatica Data Integration Hub

In-detail illustration of features and advancements in each of these components has been described here:

2.1.1. Unrivalled visibility to the true sources of data:

Informatica Power Center provides up to 50x data lineage generation performance advancements over Informatica Metadata Manager 9.6, ensuring timely, end-to-end visibility into the flow of data across complex and fastgrowing data integration environments for improved governance and performance.

2.1.2. Fastest data integration for modern data warehouses:

Version 10 provides up to 5x faster data ingestion for Oracle Exadata, Oracle SuperCluster, SAP Hana and HP Vertica certifying as the optimized solution for these target systems.

2.1.3. Improved IT-Business collaboration Version 10 is a brand new visualization system for data discovery and due to this profiling drive increased understanding of data by business analysts.

2.1.4. Addition of new capabilities at NO additional cost in Power Center redefines what IT should expect from data integration

· 100 analyst tool seats for IT-Business collaboration for agile data integration development.

· Connectors for relational, batch, JDBC and ODBC data sources.

· Real-time engine – time to get started with real-time data warehousing and exchange data among operational applications at any speed – batch, near real time or in real time.

· Advanced data transformation – Enterprise-class data transformation and parsing capability for any data type, regardless of format, complexity or size.

2.2. Informatica Data Quality “Informatica Data Quality Advances Holistic Data Governance”

Informatica Data Quality v10 enables data quality rules to be defined once and deployed anywhere business needs dictate – in the cloud or on-premise

Informatica Data Quality continues its tradition of cleansing and qualifying data according to business rules defined by business strategies and IT architectures. Improvements in Data Quality include Informatica Vibe, which allows the rules to be created against one data set and then applied virtually anywhere – whether in the cloud or on-premises.

2.2.1. Improved data quality collaboration Enhanced Business Rule Builder promotes the participation of business analysts and data stewards in the data quality process.

2.2.2. Increased visibility into data profile changes Historical data profile comparison facilitates change analysis across data profiles.

2.2.3. Fastest quality for modern data warehouse targets Informatica Data Quality v10 provides up to 5x faster data cleansing for Oracle Exadata, Oracle SuperCluster, SAP Hana and HP Vertica.

2.3. Informatica Data Integration

Hub “Informatica Data Integration Hub Advances Pub-Sub-Hub Data Integration” Informatica Data Integration Hub v10, now offers a hybrid publish/subscribe-hub (PubSub-Hub) model for big data, cloud and traditional systems, which is the world’s only modern hub-based data integration product.

Informatica Data Integration Hub is the first and only publish/subscribe hub product to:

· Support automated petabyte-scale data storage on Hadoop for data lakes.

· Unmatched agility, efficiency and ease-of-use from a modern hub-based architecture orchestrating big data, cloud and on-premise Power Center workflows through wizards and a web-based management console.

· Manage hybrid data flows spanning cloud and on-premise – with data pipelines, workflows and visibility across Informatica Cloud and Power Center.

· Secure publish/subscribe of data beyond corporate firewalls to connect remote offices.

"Pub-Sub-Hub," describes the ability to locate all of our data into a single place (that is, the hub) and be able to both publish and subscribe to data that is presented. The efficiency of the Integration Hub allows for large scale operations, such as the automated storage of petabytescale Hadoop data lakes, but some of the most nuanced use cases for the Data Integration Hub, is its ability to modernize old data stores.”

2.4. Informatica v10 Features in a nutshell

· Up to 50x faster data lineage generation (50x times performance improvement in metadata management)

· Up to 5x faster data ingestion and cleansing

· Increased flexibility and agility for hybrid data architectures

· World’s first “Pub-Sub-Hub” data integration hub.

· Expanded connectivity for cloud sources

· Delivering new monitoring dashboard to check system usage and health According to Bloor Research, Philip Howard, March 2014, in Comparative costs and uses for data integration platforms:

“Customers leverage a codeless development environment, automation and intelligence that enables data integration developers to be 40 percent more productive compared to traditional hand-coding and 25 percent more productive compared to developing on IBM, with Informatica version 10”.

Powermart PMREP Commands

noreply@blogger.com (Kareem) — Mon, 02 Jul 2018 09:24:00 +0000

The entire in this section describe changes to pmrep.

Command	Description	Comments
AssignPermission	Assigns permissions on a global object.	New command.
ChangeOwner	Changes the owner name for a global object.	New command.
RollbackDeployment	Rolls back a deployment to purge a deployed version from the target repository or folder.	New command.

Changed PMREP Commands

Command

Description

Comments

Connect

Connects to a repository.

-s option added to specify the security domain that the user belongs to.

Create

Creates the repository tables in the database.

-s option added to specify the security domain that the user belongs to.

CreateFolder

Creates a new folder in the repository.

-o option added to specify the security domain that the owner belongs to.

DeleteObject

Deletes an object.

You can specify “User Defined Function” as object type.

DeployDeploymentGroup

Deploys a deployment group.

-s option added to specify the security domain that the user belongs to.

DeployFolder

Deploys a folder.

-s option added to specify the security domain that the user belongs to.

ListObjects

Returns a list of objects in the repository.

You can specify “User Defined Function” as object type.

ModifyFolder

Modifies folder properties.

You can assign an operating system profile to a folder.

-a option added to specify the security domain that the owner belongs to.

Register

Registers a local repository with a connected global repository.

-s option added to specify the security domain that the user belongs to.

Restore

Restores a repository backup file to a database.

The following option is added:

-s. The security domain that the user belongs to.

The following options are removed:

-	v. User name of the repository administrator or another valid user defined in the external directory service.
-	x. Password for the user name as defined in the external directory service.

-	X. Password environment variable that stores the password of the repository administrator as defined in the external directory service.
-	w. Removes user-name-login mapping.

Unregister

Unregisters a local repository from a connected global repository.

-s option added to specify the security domain that the user belongs to.

Ported pmrep Commands
Command	INFACMD Replacement Command	Description
AddPrivilege	AddGroupPrivilege AddUserPrivilege	Adds a privilege to a user or a group.
AddUserToGroup	AddUserToGroup	Adds a user to a group.
ChangePasswd	ResetPassword	Changes the password of the user currently connected to the repository.
CreateGroup	CreateGroup	Creates a group.
CreateUser	CreateUser	Creates a user.
EditUser	EditUser	Edits the profile of a user.
ListAllGroups	ListAllGroups	Lists all groups registered with the repository.
LsPrivileges	ListGroupPrivileges ListRolePrivileges ListUserPrivileges	Lists the privileges granted to a user or group.
ListAllPrivileges	ListServicePrivileges	Lists all privileges that you can assign to a user or group.
ListAllUsers	ListAllUsers	Lists all users registered with the repository.
RmGroup	RemoveGroup	Removes a group from the repository.
RmPrivilege	RemoveGroupPrivilege RemoveUserPrivilege	Removes a privilege from a user or a group.
RmUser	RemoveUser	Removes a user from the repository.

New PMREP Commands
Command	Description	Comments
DeleteObject	Deletes an object.	New command.
GetConnectionDetails	Displays connection properties and attributes as name-value pairs.	New command.
ListConnections	Lists all connection objects and their respective types.	New command.
PurgeVersion	Removes older or all versions of objects from the repository.	New command. Use the -d option to purge versions of deleted objects. Use -n, -t, or both to purge specified versions of active objects.

Changed pmrep Commands

Command

Description

ListAllUsers

Lists all user names registered with the repository.

You can use the -g option to display the user name followed by all the group names associated with the user separated by a comma. Each line of the output represents information about a single user.

ObjectExport

Exports objects to an XML file.

You can use pmrep to export objects if you have one of the following privileges:

-	Admin Repository
-	Browse Repository

-	Use Designer
-	Use Repository Manager

-	Use Workflow Manager

Note: If you only have the Use Designer privilege, you can export Designer objects only. If you only have the Use Workflow Manager privilege, you can export Workflow Manager objects only.

ObjectImport

Imports objects from an XML file.

You can use pmrep to import objects if you have one of the following privileges:

-	Admin Repository
-	Use Designer

-	Use Repository Manager
-	Use Workflow Manager

Note: If you only have the Use Designer privilege, you can import Designer objects only. If you only have the Use Workflow Manager privilege, you can import Workflow Manager objects only.

TruncateLog

Truncates log details from repository level, folder level, or workflow level.

When you use the -t option to specify an end time, you can enter an integer greater than 0 to specify a number of days before the current date, or you can specify the end time in MM/DD/YYYY HH24:MI:SS format.

UpdateSeqGenValues

Updates transformation properties for Sequence Generator transformations.

You can use the UpdateSeqGenValues command with the Use Designer privilege and write permission on the folder. Previously, you needed the Super User privilege.

New PMREP Commands
Command	Description	Comments
ListGroupsForUser	Lists all the groups that the user belongs to.	New command.
LsPrivileges	Lists the privileges associated with a user or group.	New command. You can use the -o option to list privileges assigned directly to a user.

Changed PMREP Commands
Command	Description	Comments
CreateConnection	Creates a relational or application connection in the repository.	Previously, you could not use this command to create application connections. The -k option has been added to accept name/value pairs as connection attributes for application connections. The -t option has been modified. You can use the option with no argument, or you can enter the value 1. In both cases, you enable trusted-connection security for MS SQL Server connections.
ObjectImport	Imports objects from an XML file.	The -p option has been added. You can use the option to retain persistent mapping variables when you import mappings.

New DEPLOYPARAMS Parameter

Parameter

Required/

Optional

Description

DEPLOYTIMEOUT

Optional

Period of time (in seconds) that pmrep attempts to acquire locks on objects in the target repository. A value of 0 fails the copy operation immediately if pmrep cannot obtain a lock. A value of -1 instructs pmrep to wait indefinitely until it acquires locks or the user cancels the operation. Default is -1.

PMREPAGENT Commands Ported to PMREP
pmrepagent Command	Replacement pmrep Command	Description	Comments
Backup	Backup	Backs up repository.	Existing pmrep command.
Create	Create	Creates repository tables in the database.	New pmrepcommand.
Delete	Delete	Deletes the repository tables from the database	New pmrepcommand.
Registerplugin	RegisterPlugin	Registers a new external module to the repository.	New pmrepcommand.
Restore	Restore	Restores a repository from a repository backup file.	New pmrepcommand.
Unregisterplugin	UnregisterPlugin	Unregisters a plug-in module from the repository.	New pmrepcommand.
Upgrade	Upgrade	Upgrades an existing repository to the latest version.	New pmrepcommand.

. New pmrep Commands
Command	Description	Comments
Create	Creates repository tables in the database.	Ported from pmrepagent.
Delete	Deletes the repository tables from the database.	Ported from pmrepagent.
KillUserConnections	Terminates user connection to the repository.	New command.
ListUserConnections	Lists user connections to the Repository Service.	New command.
RegisterPlugin	Registers a new external module to the repository.	Ported from pmrepagent.
Restore	Restores a repository from a repository backup file.	Ported from pmrepagent.
UnregisterPlugin	Unregisters a plug-in module from the repository.	Ported from pmrepagent.
Upgrade	Upgrades an existing repository to the latest version.	Ported from pmrepagent.
Version	Displays the PowerCenter version number.	New command.

. Changed PMREP Commands
Command	Description	Comments
ApplyLabel	Applies a label to an object or set of objects in a folder.	-g option added to find object dependencies across repositories.
BackUp	Backs up a repository.	-v option added to skip task statistics.
Connect	Connects to the repository.	The following options have been added: -d. Domain name. -t. Resilience timeout.
CreateConnection	Creates a relational connection.	-f option added to enter SQL commands to set the database environment when you connect to the database
CreateDeploymentGroup	Creates a deployment group.	Privileges changed.
CreateFolder	Creates a folder.	Privileges changed. -f option added to change the folder status.
CreateLabel	Creates a label object.	Privileges changed. -t option (label type) removed.
CreateUser	Creates a user.	-i option added to add contact information about the user.
DeleteConnection	Deletes an existing relational connection.	Privileges changed.
DeleteDeploymentGroup	Deletes a deployment group.	Privileges changed.
DeleteFolder	Deletes a folder.	Privileges changed.
DeleteLabel	Deletes a label object and all references to the label.	Privileges changed.
DeployDeploymentGroup	Copies a deployment group.	Privileges changed. The following options have been added: -d. Domain name. -o. Repository Service port number. The following option has been removed: -u. Login user name for the target repository. The following option has been changed: -n. Login user name for the target repository, not the target repository server port number.
DeployFolder	Deploys a folder.	Privileges changed. The following options have been added: -d. Domain name. -o. Repository Service port number. The following option has been removed: -u. Login user name for the target repository. The following option has been changed: -n. Login user name for the target repository, not the target repository server port number.
EditUser	Edits the profile of a user.	-i option added to edit contact information about the user.
ListObjects	Lists objects in the repository.	The Java transformation type has been added.
ModifyFolder	Modifies folder properties.	Privileges changed. -f option added to change the folder status.
Notify	Sends notification messages.	All options removed except -m, the message you want to send.
ObjectExport	Exports objects to an XML file.	The following options have been added: -m. Exports primary key table definitions when you export sources or targets with foreign keys. -s. Exports the original object referenced by the shortcut. -b. Exports non-reusable objects. -r. Exports reusable objects.
ObjectImport	Imports objects from an XML file.	-l option added to log file that records each export step.
Register	Registers local repositories with global repositories.	-d option added to specify domain name. -a and -A (password) options removed.
Run	Displays messages stating whether a run has completed or failed.	-u option added to encode the output file in UTF-8 format.
Unregister	Unregisters local repositories from global repositories.	-d option added to specify domain name. -a and -A (password) options removed.
UpdateSrcPrefix	Updates source table owner names.	Privileges changed.
UpdateStatistics	Updates statistics for repository tables and indexes.	-s option (file of tables to skip) removed.
Validate	Validates objects.	-t option (task or transformation type) removed.

Deprecated PMREP Commands
Command	Description	infacmd Replacement Command
AddRepository	Adds a repository configuration to the Repository Server.	CreateRepositoryService
AddServer	Adds a PowerCenter Server.	CreateIntegrationService
DeleteServer	Deletes a PowerCenter Server.	RemoveService
DisableRepository	Disables a repository.	DisableService
EnableRepository	Enables a repository.	EnableService
RemoveRepository	Removes a repository from the Repository Server cache entry list.	RemoveService
StopRepository	Stops the repository you last connected to using pmrep.	DisableService
UpdateServer	Edits server details.	UpdateIntegrationService or UpdateRepositoryService
UpdateServVar	Updates server variables.	UpdateServiceProcess

Informatica Power center Designer - Functions

noreply@blogger.com (Kareem) — Mon, 02 Jul 2018 07:33:00 +0000

1.1 ISNULL

The ISNULL function returns whether a value is NULL. It is available in the Designer and the Workflow Manager.

ISNULL (value)

Example: The following example checks for null values in the items table:

ISNULL (ITEM_NAME)

ITEM_NAME	RETURN VALUE
Flashlight	0 (FALSE)
NULL	1 (TRUE)
''	0 (FALSE) Empty string is not NULL

1.2 IS_DATE

The IS_DATE function returns whether a value is a valid date. It is available in the Designer and the Workflow Manager.

IS_DATE (value)

Example: The following expression checks the INVOICE_DATE port for valid dates:

IS_DATE (INVOICE_DATE)

This expression returns data similar to the following:

INVOICE_DATE	RETURN VALUE
NULL	NULL
180	0 (FALSE)
'04/01/98'	0 (FALSE)
'04/01/1998 00:12:15'	1 (TRUE)
'02/31/1998 12:13:55'	0 (FALSE) (February does not have 31 days)
'John Smith'	0 (FALSE)

This function can also be used to validate a date for a specified format for which the syntax is

IS_DATE (value, format)

If the format is not specified, ‘MM/DD/YYYY’ is taken as the default format.

1.3 IS_NUMBER

The IS_NUMBER returns whether a string is a valid number. It is available in the Designer and the Workflow Manager.

IS_NUMBER (value)

Example: The following expression checks the ITEM_PRICE port for valid numbers:

IS_NUMBER (ITEM_PRICE)

ITEM_PRICE	RETURN VALUE
123.00	1 (True)
-3.45e+3	1 (True)
''	0 (False) Empty string
+123abc	0 (False)
ABC	0 (False)
-ABC	0 (False)
NULL	NULL

1.4 IS_SPACES:

The IS_SPACES function returns whether a value consists entirely of spaces. It is available in the Designer and the Workflow Manager.

IS_SPACES (value)

Example: The following expression checks the ITEM_NAME port for rows that consist entirely of spaces:

IS_SPACES IS_SPACES (ITEM_NAME)

ITEM_NAME	RETURN VALUE
Flashlight	0 (False)
	1 (True)
Regulator system	0 (False)

1.5 DECODE:

The DECODE function searches a port for the specified value. It is available in the Designer and the Workflow Manager.

DECODE (value, first_search, first_result [, second_search, second_result ]…[, default ] )

Example: We might use DECODE in an expression that searches for a particular ITEM_ID and returns the ITEM_NAME:

DECODE (ITEM_ID, 10, 'Flashlight',

14, 'Regulator', 20, 'Knife', 40,

'Tank', 'NONE’)

ITEM_ID	RETURN VALUE
10	Flashlight
14	Regulator
17	NONE

1.6 IIF

The IIF function returns one of two values we specify, based on the results of a condition. It is available in the Designer and the Workflow Manager.

IIF (CONDITION, VALUE2 [, VALUE2 ] )

Example : IIF( SALES < 100, 0, SALARY )

SALES	SALARY	RETURN VALUE
150	50,000.00	50,000
50	20,000.00	0
NULL	50,000.41	50,000

IIF functions can be nested if there is more than one condition to be tested. But it is always a better option to go for DECODE function when the number of conditions is large since DECODE function is less costly compared to IIF function.

For example, consider the following expression

IIF(MARKS>=90,'A',

(IIF(MARKS>= 75,'B',

(IIF(MARKS>=65,'C',

(IIF(MARKS>=55,'D',

IIF(MARKS>=45,'E',

'F'))))))))

The same result can be obtained with

DECODE (TRUE,

MARKS>=90,'A',

MARKS>=75,'B',

MARKS>=65,'C',

MARKS>=55,'D',

MARKS>=45,'E',

'F')

When the number of conditions increase we will be able to appreciate the simplicity of the DECODE function and the complexity of the IIF function.

In both the cases, If MARKS>90 it will return 'A' though it satisfies all the conditions given. It is because it returns when the first condition is satisfied. Therefore, even if a port satisfies two or more the conditions it will take only the first one. Therefore, Ordering is important in IIF and DECODE functions.

1.7 ERROR:

The ERROR function causes the Informatica Server to skip a record and throws an error message defined by the user. It is available in the Designer.

ERROR (string)

Example: The following example shows how you can reference a mapping that calculates the average salary for employees in all departments of your company, but skips negative values. The following expression nests the ERROR function in an IIF expression so that if the Informatica Server finds a negative salary in the Salary port, it skips the row and displays an error:

IIF (SALARY < 0, ERROR ('Error. Negative salary found. Row skipped.', EMP_SALARY)

SALARY	RETURN VALUE
10000	10000
-15000	'Error. Negative salary found. Row skipped.'

The below example combines two special functions, a test Function and a conversion function.

IIF(IS_DATE(DATE_PROMISED,'MM/DD/YY'), TO_DATE(DATE_PROMISED), ERROR ('Invalid Date'))

1.8 LOOKUP:

The LOOKUP function searches for a particular value in a lookup source column. It is available in the Designer.

LOOKUP (result, search1, value1 [, search2, value2]… )

Example: The following expression searches the lookup source: TD. SALES for a specific item ID and price, and returns the item name if both searches find a match:

LOOKUP (:TD. SALES.ITEM_NAME, :TD.SALES.ITEM_ID, 10, :TD.SALES.PRICE, 15.99 )

ITEM_NAME	ITEM_ID	PRICE
Regulator	5	100.00
Flashlight	10	15.99

Date Functions

Date Format Strings in the Transformation Reference

D, DD, DDD, DAY, DY, J

Days (01-31). We can use any of these format strings to specify the entire day portion of a date. For example, if we pass 12-APR-1997 to a date function, we can use any of these format strings specify 12.

HH, HH12, HH24

Hour of day (0 to 23), where zero is 12 AM (midnight). We can use any of these formats to specify the entire hour portion of a date. For example, if we pass the date 12-APR-1997 2:01:32 PM, we can use HH, HH12, or HH24 to specify the hour portion of the date.

Minutes.

MM, MON, MONTH

Month portion of date (0 to 59). We can use any of these format strings to specify the entire month portion of a date. For example, if we pass 12-APR-1997 to a date function, we can use MM, MON, or MONTH to specify APR.

SS, SSSS

Second portion of date (0 to 59).

Y, YY, YYY, YYYY, RR

Year portion of date (1753 to 9999). We can use any of these format strings to specify the entire year portion of a date. For example, if we pass 12-APR-1997 to a date function, we can use Y, YY, YYY, or YYYY to specify 1997.

1.9 ADD_TO_DATE

The ADD_TO_DATE function adds a specified amount to one part of a date/time value, and returns a date in the same format as the specified date.

Note: If we do not specify the year as YYYY, the Informatica Server assumes the date is in the current century. It is available in the Designer and the Workflow Manager.

ADD_TO_DATE (date, format, amount)

Example : The following expression adds one month to each date in the DATE_SHIPPED port. If we pass a value that creates a day that does not exist in a particular month, the Informatica Server returns the last day of the month. For example, if we add one month to Jan 31 1998, the Informatica Server returns Feb 28 1998.

Also note, ADD_TO_DATE recognizes leap years and adds one month to Jan 29 2000:

ADD_TO_DATE (DATE_SHIPPED, 'MM', 1)

DATE_SHIPPED	RETURN VALUE
Jan 12 1998 12:00:30AM	Feb 12 1998 12:00:30AM

The following expression subtracts 10 days from each date in the DATE_SHIPPED port:

ADD_TO_DATE (DATE_SHIPPED, 'D', -10)

DATE_SHIPPED	RETURN VALUE
Jan 1 1997 12:00:30AM	Dec 22 1996 12:00AM

The following expression subtracts 15 hours from each date in the DATE_SHIPPED port:

ADD_TO_DATE (DATE_SHIPPED, 'HH', -15)

DATE_SHIPPED	RETURN VALUE
Jan 1 1997 12:00:30AM	Dec 31 1996 9:00:30AM

In ADD_TO_DATE function, if the argument passed evaluates to a date that does not exist in a particular month, the Informatica Server returns the last day of the month.

The following expression reveals this.

ADD_TO_DATE (DATE_SHIPPED, 'MON', 3)

DATE_SHIPPED	RETURN VALUE
Jan 31 1998 6:24:45PM	Apr 30 1998 6:24:45PM

1.10 DATE_COMPARE

The DATE_COMPARE function returns a value indicating the earlier of two dates. It is available in the Designer and the Workflow Manager.

DATE_COMPARE (date1, date2)

Example: The following expression compares each date in the DATE_PROMISED and DATE_SHIPPED ports, and returns an integer indicating which date is earlier:

DATE_COMPARE (DATE_PROMISED, DATE_SHIPPED)

DATE_PROMISED	DATE_SHIPPED	RETURN VALUE
Jan 1 1997	Jan 13 1997	-1
Feb 1 1997	Feb 1 1997	0
Dec 22 1997	Dec 15 1997	1

2.1 DATE_DIFF

The DATE_DIFF function returns the length of time between two dates, measured in the specified increment (years, months, days, hours, minutes, or seconds). It is available in the Designer and the Workflow Manager.

DATE_DIFF (date1, date2, format)

Example: The following expressions return the number of days between the DATE_PROMISED and the DATE_SHIPPED ports:

DATE_DIFF

DATE_DIFF (DATE_PROMISED, DATE_SHIPPED, 'D’)

DATE_DIFF

DATE_DIFF (DATE_PROMISED, DATE_SHIPPED, 'DD')

DATE_PROMISED	DATE_SHIPPED	RETURN VALUE
Jan 1 1997 12:00:00AM	Mar 29 1997 12:00:00PM	-87.5
Mar 29 1997 12:00:00PM	Jan 1 1997 12:00:00AM	87.5

We can combine DATE functions and TEST functions so as to validate the dates.

For example, while using the DATE functions like DATE_COMPARE and DATE_DIFF, the dates given as inputs can be validated using the TEST function IS_DATE and then passed to them if valid.

2.2 GET_DATE_PART

The GET_DATE_PART function returns the specified part of a date as an integer value, based on the default date format of MM/DD/YYYY HH24:MI: SS. It is available in the Designer and the Workflow Manager.

GET_DATE_PART (date, format)

Example: The following expressions return the day for each date in the DATE_SHIPPED port:

GET_DATE_PART (DATE_SHIPPED, 'D’)

GET_DATE_PART (DATE_SHIPPED, 'DD’)

DATE_SHIPPED	RETURN VALUE
Mar 13 1997 12:00:00AM	13
June 3 1997 11:30:44PM	3
NULL	NULL

2.3 LAST_DAY

The LAST_DAY function returns the date of the last day of the month for each date in a port. It is available in the Designer and the Workflow Manager.

LAST_DAY (date)

Example: The following expression returns the last day of the month for each date in the ORDER_DATE port:

LAST_DAY (ORDER_DATE)

ORDER_DATE	RETURN VALUE
Apr 1 1998 12:00:00AM	Apr 30 1998 12:00:00AM
Jan 6 1998 12:00:00AM	Jan 31 1998 12:00:00AM

DATE functions combine with Conversion functions also.

The following expression has LAST_DAY and TO_DATE functions nested or combined together.

LAST_DAY (TO_DATE (GIVEN_DATE, 'DD-MON-YY’))

2.4 MAX

The MAX function returns the latest date found in a group. It is available in the Designer.

MAX (date, filtercondition)

We can return the maximum date for a port or group.

Example: The following expression returns the maximum order date for flashlights:

MAX (ORDERDATE, ITEM_NAME='Flashlight' )

ITEM_NAME	ORDER_DATE
Flashlight	Apr 20 1998
Regulator System	May 15 1998
Flashlight	Sep 21 1998
Diving Hood	Aug 18 1998
Halogen Flashlight	Feb 1 1998
Flashlight	Oct 10 1998

RETURN VALUE: Oct 10 1998

2.5 MIN

The MIN function returns the earliest date found in a group. It is available in the Designer.

MIN( date, filter_condition )

Example: The following expression returns the oldest order date for flashlights:

MIN (ORDER_DATE, ITEM_NAME='Flashlight' )

ITEM_NAME	ORDER_DATE
Flashlight	Apr 20 1998
Regulator System	May 15 1998
Flashlight	Sep 21 1998
Diving Hood	Aug 18 1998
Halogen Flashlight	Feb 1 1998
Flashlight	Oct 10 1998
RETURN VALUE: Feb 1 1998

2.6 ROUND

The ROUND function rounds one part of a date. It is available in the Designer and the Workflow Manager.

ROUND( date [, format ] )

Example: The following expressions round the month portion of each date in the DATE_SHIPPED port.

ROUND( DATE_SHIPPED, 'MM' )

ROUND( DATE_SHIPPED, 'MON' )

DATE_SHIPPED	RETURN VALUE
Jan 15 1998 2:10:30AM	Jan 1 1998 12:00:00AM

Similarly, the ROUND function can be used to round off Year, Day or Time portions.

2.7 SET_DATE_PART

The SET_DATE_PART function sets one part of a date/time value to a specified value. It is available in the Designer and the Workflow Manager.

SET_DATE_PART( date, format, value )

Example: The following expressions change the month to June for the dates in the DATE_PROMISED port. The Informatica Server displays an error when we try to create a date that does not exist, such as changing March 31 to June 31:

SET_DATE_PART( DATE_PROMISED, 'MM', 6 )

SET_DATE_PART( DATE_PROMISED, 'MON', 6 )

DATE_PROMISED	RETURN VALUE
Jan 1 1997 12:15:56AM	Jun 1 1997 12:15:56AM
NULL	NULL

Similarly, the SET_DATE_PART function can be used to round off Year, Day or Time portions.

2.8 TRUNC

The TRUNC function truncates dates to a specific year, month, day, hour, or minute. It is available in the Designer and the Workflow Manager.

TRUNC (date [, format] )

Example: The following expressions truncate the year portion of dates in the DATE_SHIPPED port:

TRUNC (DATE_SHIPPED, 'Y' )

TRUNC (DATE_SHIPPED, 'YY' )

DATE_SHIPPED	RETURN VALUE
Jan 15 1998 2:10:30AM	Jan 1 1998 12:00:00AM

Similarly, the TRUNC function can be used to truncate Month, Day or Time portions.

The functions TRUNC & ROUND can be nested in order to manipulate dates.

Informatica Power center Tutorial

noreply@blogger.com (Kareem) — Fri, 29 Jun 2018 09:28:00 +0000

Installation of Informatica

Informatica power center 8.6 Installation

Informatica Power Center

Sources

Targets

Power Center repository

Repository Server

PowerCenter Client

Power Center Server

Database Connections

Powercenter Metadata Reporter

Using the Repository Server Administration Console
Repository Objects
The Power center Design Process
Power Center Client
Workflow Manager
Workflow Monitor
Power center Admin Console
Getting started with Informatica
Changing Data Movement Modes
Code Page Overview
Power Center Server Variable Directories

Informatica Transformations

Expression Transformation
Filter Transformation
Router Transformation
Sorter Transformation
Rank Transformation
Source Qualifier Transformation
Aggrigator Transformation
Joiner Transformation
Sequence Generator Transformation
SQL Transformation
Stored Procedure Transformation
Transaction Control Transformation
Lookup Transformation
Union Transformation
Normalizer Transformation
Update Strategy Transformation
Java Transformation
HTTP Transformation
Transformations in Informatica 9 ....Latest
Define the Properties available in Sequence Generator transformation in brief.

Informatica Topics: -

Incremental Loading for Dimension Tables
Target Load Order
Mapping Debugger
Constraint based Load Order
Change Data Capture (CDC)
Informatica Functions
Setup Environment variables for PMCMD/PMREP
pmcmd Commands
New pmrep Commands
Informatica Server Process
MAPPING PARAMETERS
MAPPLETS
WORKING WITH LINKS
INFORMATICA SCHEDULERS

· Identify the bottlenecks in Informatica?

· Changing Data Movement Modes

· CODE PAGE OVERVIEW

· Informatica Reject File - How to Identify rejection reason

· Identify the bottlenecks in Informatica?

· Working with Post-Session Email

· Output Files in Informatica

· Power Center Server Variable Directories

TASKS in Informatica

Slowly Changing dimensions

SCD Type-1 Implementation in Informatica using dynamic Lookup

SCD TYPE1 MAPPING

Informatica Scd Type-2 implementation

Informatica Performance Tuning

Informatica Interview Questions & Answers

Diff between Informatica 7.1 and 8.6

Diff between Informatica 7.1 and 8.6

Informatica Testing

ETL Life Cycle

ETL Life cycle

Informatica Real-time Project

Informatica Interview Questions on Transformations.

Informatica Interview Questions on Expression Transformation

Informatica Interview Questions on Filter Transformation

Informatica Interview Questions on Aggregator Transformation

Informatica Interview Questions on Joiner Transformation

Informatica Interview Questions on Normalizer Transformation

Informatica Interview Questions on Rank Transformation

Informatica Interview Questions on Router Transformation

Informatica Interview Questions on Sequence Generator Transformation

Informatica Interview Questions on Source Qualifier Transformation

Informatica Interview Questions on Stored Procedure Transformation

Informatica Interview Questions on Update Strategy Transformation

Informatica Interview Questions on Lookup Transformation

Informatica Interview Questions on SQL Transformation

Informatica Interview Questions on Sorter Transformation

Informatica Interview Questions on Transaction Control Transformation

Informatica Interview Questions on Union Transformation

Basic Interview Questions & Answers on Informatica Power center

noreply@blogger.com (Kareem) — Fri, 29 Jun 2018 09:02:00 +0000

1. While importing the relational source definition from database, what are the Meta data of source you import?

A. Source name, Database location, Column names, Data types, and Key constraints

2. How many ways you can update a relational source definition and what r they?

A. 1. Edit the definition

2. Reimport the definition

3. Where should U place the flat file to import the flat file definition to the designer?

A. Place it in local folder

4. To provide support for Main frames source data, which files r used as a source definition?

A. COBOL files

5. Which transformation should u need while using the Cobol sources as source definitions?

· Normalizer transformation which is used to normalize the data.

· Since Cobol sources r often consists of Denormailzed data.

6. How can U create or import flat file definition in to the warehouse designer?

A. U cannot create or import flat file definition in to warehouse designer directly.

B. Instead U must analyze the file in source analyzer, then drag it into the warehouse designer.

C. When U drag the flat file source definition into warehouse designer workspace, the warehouse designer creates a relational target definition not a file definition.

D. If u want to load to a file, configure the session to write to a flat file. When the Informatica server runs the session, it creates and loads the flat file.

7.What is the mapplet?

A. Mapplet is a set of transformations that you build in the mapplet designer and U can use in multiple mappings.

8. what is a transformation?

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

9. What r the designer tools for creating transformations?

A. Mapping designer, Transformation developer, Mapplet designer

10. What r the active and passive transformations?

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

11. What r the connected or unconnected transformations?

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

12. How many ways u creates ports?

A. 1.Drag the port from another transformation

2.Click the add button on the ports tab.

14. What r the reusable transformations?

A. Reusable transformations can be used in multiple mappings. When u need to incorporate this transformation into mapping add an instance of it to Mapping. Later if U change the definition of the

transformation, all instances of it inherit the changes. Since the instance of reusable transformations is a pointer to that transformation can change the transformation in the transformation developer, its instances automatically reflect these changes. This feature can save U great deal of work.

15. What r the methods for creating reusable transformations?

A. Two methods

1.Design it in the transformation developer.

2.Promote a standard transformation from the mapping designer. After U add a Transformation to the mapping, U can promote it to the status of reusable Transformation. Once U promote a standard transformation to reusable status, U can demote it to a Standard transformation at any time. If u change the properties of a reusable transformation in mapping can revert it to the original reusable transformation properties by clicking the revert button.

16. What r the unsupported repository objects for a mapplet?

· COBOL source definition

· Joiner transformations

· Normalizer transformations

· Non reusable sequence generator transformations.

· Pre or post session stored procedures

· Target definitions

· Power mart 3.5 style Look Up functions

· XML source definitions

· IBM MQ source definitions

17. What r the mapping parameters and mapping variables?

Mapping parameter represents a constant value that U can define before running a session. A mapping parameter retains the same value throughout the entire session. when u use the mapping parameter, U declare and use the parameter in a mapping or Mapplet. Then define the value of parameter in a parameter file for the session.

Unlike a mapping parameter, a mapping variable represents a value that can change throughout the session. The Informatica server saves the value of mapping variable to the repository at the end of session run and uses that value next time U run the session.

18. Can U use the mapping parameters or variables created in one mapping into another mapping?

NO. We can use mapping parameters or variables in any transformation of the same mapping or mapplet in which U have created mapping parameters or variables.

19.Can u use the mapping parameters or variables created in one mapping into any other reusable transformation?

Yes. Because reusable transformation is not contained with any mapplet or mapping.

20.How can U improve session performance in aggregator transformation?

Use sorted input.

21.What is aggregate cache in aggregator transformation?

The aggregator stores data in the aggregate cache until it completes aggregate calculations. When u run a session that uses an aggregator transformation, the Informatica server creates index and data caches in memory to process the transformation. If the Informatica server requires more space, it stores overflow values in cache files.

22.What r the difference between joiner transformation and source qualifier transformation?

U can join heterogeneous data sources in joiner transformation which we cannot achieve in source qualifier transformation need matching keys to join two relational sources in source qualifier transformation. Whereas u doesn't need matching keys to join two sources. Two relational sources should come from same data source in source qualifier. whereas U can join relational sources which r coming from different sources in joiner transformation.

23. In which conditions we cannot use joiner transformation (Limitations of joiner transformation)

· Both pipelines begin with the same original data source.

· Both input pipelines originate from the same Source Qualifier transformation.

· Both input pipelines originate from the same Normalizer transformation.

· Both input pipelines originate from the same Joiner transformation.

· Either input pipelines contains an Update Strategy transformation.

· Either input pipelines contains a connected or unconnected Sequence

· Generator transformation.

24. What r the setting that u use to configure the joiner transformation?

Master and detail source, Type of join and Condition of the join

25. What r the join types in joiner transformation?

· Normal (Default), Master outer, Detail outer, full outer

· Master Outer: All rows from Detail and only matching rows from Master

· Detail outer : All rows from Master and only matching rows from Detail.

· full outer join: keeps all rows of data from both the master and detail sources

26.What r the joiner caches?

When a Joiner transformation occurs in a session, the Informatica Server reads all the records from the master source and builds index and data caches based on the master rows. After building the caches, the Joiner transformation reads records from the detail source and perform joins.

27.what is the look up transformation?

Use lookup transformation in u'r mapping to lookup data in a relational table, view, synonym. Flat file Informatica server queries the look up table based on the lookup ports in the transformation. It compares the lookup transformation port values to lookup table column values based on the look up condition.

28.Why use the lookup transformation?

Get a related value. For example, if your source table includes employee ID, but you want to include the employee name in your target table to make your summary data Easier to read. Perform a calculation. Many normalized tables include values used in a Calculation, such as gross sales per invoice or sales tax, but not the calculated value (such as net sales).

Update slowly changing dimension tables. You can use a Lookup transformation to Determine whether records already exist in the target.

29.What r the types of lookup?

Connected and unconnected

30.Differences between 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.

· You can use a dynamic or static cache. You can use a static cache.

· Cache includes all lookup columns used in the mapping (that is, lookup source columns included in the lookup condition and lookup source columns linked as output ports to other transformations). Cache includes all lookup/output ports in the lookup condition and the lookup/return port.

· Can return multiple columns from the same row or insert into the dynamic lookup cache. Designate one return port (R). Returns one column from each row.

· If there is no match for the lookup condition, the PowerCenter Server returns the default value for all output ports. If you configure dynamic caching, the PowerCenter Server inserts rows into the cache or leaves it unchanged. If there is no match for the lookup condition, the PowerCenter Server returns NULL.

· If there is a match for the lookup condition, the PowerCenter Server returns the result of the lookup condition for all lookup/output ports. If you configure dynamic caching, the PowerCenter Server either updates the row the in the cache or leaves the row unchanged. If there is a match for the lookup condition, the PowerCenter Server returns the result of the lookup condition into the return port.

· Pass multiple output values to another transformation. Link lookup/output ports to another transformation. Pass one output value to another transformation. The lookup/output/return port passes the value to the transformation calling :LKP expression.

· Supports user-defined default values. Does not support user-defined default values.

31.what is meant by lookup caches?

The Informatica server builds a cache in memory when it processes the first row of a data in a cached look up transformation. It allocates memory for the cache based on the amount u configure in the

transformation or session properties. The Informatica server stores condition values in the index cache and output values in the data cache.

32.What r the types of lookup caches?

Persistent cache: U can save the lookup cache files and reuse them the next time the Informatica server processes a lookup transformation configured to use the cache.

Recache from database: If the persistent cache is not synchronized with the lookup table, U can configure the lookup transformation to rebuild the lookup cache.

Static cache: U can configure a static or read-only cache for only lookup table. By default Informatica server creates a static cache. It caches the lookup table and lookup values in the cache for each row that comes into the transformation. when the lookup condition is true, the Informatica server does not update the cache while it processes the lookup transformation.

Dynamic cache: If u want to cache the target table and insert new rows into cache and the target, u can create a look up transformation to use dynamic cache. The Informatica server dynamically inserts data to the target table.

shared cache: U can share the lookup cache between multiple transactions U can share unnamed cache between transformations in the same mapping.

33. Difference between static cache and dynamic cache

· Uncached Static Cache Dynamic Cache

· You cannot insert or update the cache. You cannot insert or update the cache. You can insert or update rows in the cache as you pass rows to the target.

· You cannot use a flat file lookup. You can use a relational or a flat file lookup. You can use a relational lookup only.

· When the condition is true, the PowerCenter Server returns a value from the lookup table or cache.

· When the condition is not true, the PowerCenter Server returns the default value for connected transformations and NULL for unconnected transformations.

· For details, see Working with an Uncached Lookup or Static Cache. When the condition is true, the PowerCenter Server returns a value from the lookup table or cache.

· When the condition is not true, the PowerCenter Server returns the default value for connected transformations and NULL for unconnected transformations.

· For details, see Working with an Uncached Lookup or Static Cache. When the condition is true, the PowerCenter Server either updates rows in the cache or leaves the cache unchanged, depending on the row type. This indicates that the row is in the cache and target table. You can pass updated rows to the target table.

· When the condition is not true, the PowerCenter Server either inserts rows into the cache or leaves the cache unchanged, depending on the row type. This indicates that the row is not in the cache or target table. You can pass inserted rows to the target table.

· For details, see Updating the Dynamic Lookup Cache.

34. Which transformation should we use to normalize the COBOL and relational sources?

Normalizer Transformation. When U drag the COBOL source in to the mapping Designer workspace, the normalizer transformation automatically appears, creating input and output ports for every column in the source.

35. How the Informatica server sorts the string values in Rank transformation?

When the Informatica server runs in the ASCII data movement mode it sorts session data using Binary sort order. If U configure the session use a biary sort order, the Informatica server calculates the binary value of each string and returns the specified Number of rows with the highest binary values for the string.

36.What r the rank caches?

During the session, the Informatica server compares an in out row with rows in the data cache. If the input row out-ranks a stored row, the Informatica server replaces the stored row with the input row. The Informatica server stores group information in an index cache and row data in a data cache.

37.What is the Rank index in Rank transformation?

The Designer automatically creates a RANKINDEX port for each Rank transformation. The Informatica Server uses the Rank Index port to store the ranking position for each record in a group. For example, if you create a Rank transformation that ranks the top 5 salespersons for each quarter, the rank index numbers the salespeople from 1 to 5:

38. What is the Router transformation?

A Router transformation is similar to a Filter transformation because both transformations allow you to use a condition to test data. However, a Filter transformation tests data for one condition and drops the

rows of data that do not meet the condition. A Router transformation tests data for one or more conditions and gives you the option to route rows of data that do not meet any of the conditions to a default output

group. If you need to test the same input data based on multiple conditions, use a Router Transformation in a mapping instead of creating multiple Filter transformations to perform the same task.

39.What r the types of groups in Router transformation?

Input group, Output group

The designer copies property information from the input ports of the input group to create a set of output ports for each output group.

Two types of output groups

User defined groups

Default group

U cannot modify or delete default groups.

40.Why we use stored procedure transformation?

For populating and maintaining data bases.

42.What r the types of data that passes between Informatica server and stored procedure?

3 types of data

Input/output parameters

Return Values

Status code.

43.What is the status code?

Status code provides error handling for the Informatica server during the session. The stored procedure issues a status code that notifies whether or not stored procedure Completed successfully. The user cannot see this value. It only used by the Informatica server to determine whether to continue running the Session or stop.

44. What is source qualifier transformation?

Ans. When U add a relational or a flat file source definition to a mapping, U need to connect it to a source qualifier transformation. The source qualifier transformation represents the records that the Informatica server reads when it runs a session.

45.What are the tasks that source qualifier performs?

· Join data originating from same source database.

· Filter records when the Informatica server reads source data.

· Specify an outer join rather than the default inner join

· Specify sorted records.

· Select only distinct values from the source.

· Creating custom query to issue a special SELECT statement for the Informatica server to read Source data.

46. What is the target load order?

U specify the target load order based on source qualifiers in a mapping. If u have the multiple Source qualifiers connected to the multiple targets, U can designate the order in which Informatica server loads data into the targets.

47. What is the default join that source qualifier provides?

Inner equijoin.

48. What r the basic needs to join two sources in a source qualifier?

Two sources should have primary and foreign key relationships.

Two sources should have matching data types.

49.what is update strategy transformation?

This transformation is used to maintain the history data or just most recent Changes into target table.

50.Describe two levels in which update strategy transformation sets?

Within a session. When you configure a session, you can instruct the Informatica Server to either treat all records in the same way (for example, treat all records as inserts), or use instructions coded into the

session mapping to flag records for different database operations.

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

51.What is the default source option for update strategy transformation?

Data driven.

52. What is Data driven?

The Informatica server follows instructions coded into update strategy transformations with in the session mapping determine how to flag records for insert, update, delete or Reject. If u do not choose data driven option setting, the Informatica server ignores all update strategy transformations in the mapping.

53.What r the options in the target session of update strategy transformation?

Insert, Delete, Update, Update as update, Update as insert, Update else insert, Truncate table

54. What r the types of mapping wizards that r to be provided in Informatica ?

The Designer provides two mapping wizards to help you create mappings quickly and easily. Both wizards are designed to create mappings for loading and maintaining star schemas, a series of dimensions related to a central fact table.

Getting Started Wizard. Creates mappings to load static fact and dimension tables, as well as slowly growing dimension tables.

Slowly Changing Dimensions Wizard. Creates mappings to load slowly changing dimension tables based on the amount of historical dimension data you want to keep and the method you choose to handle historical dimension data.

Identification and elimination of performance bottlenecks

noreply@blogger.com (Kareem) — Mon, 28 May 2018 03:10:00 +0000

identification and elimination of performance bottlenecks will obviously optimize session performance. After tuning all the mapping bottlenecks, we 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.

PowerCenter Informatica Pipeline Partition:

The PowerCenter® Partitioning Option increases the performance of PowerCenter through parallel data processing. This option provides a thread-based architecture and automatic data partitioning that optimizes parallel processing on multiprocessor and grid-based hardware environments.

There are different Types of Informatica Partitions, eg.

· Database partitioning
· Hash auto-keys
· Hash user keys
· Key range
· Pass-through
· Round-robin

Informatica Pipeline Partitioning Explained

Each mapping contains one or more pipelines. A pipeline consists of a source qualifier, all the transformations and the target. When the Integration Service runs the session, it can achieve higher performance by partitioning the pipeline and performing the extract, transformation, and load for each partition in parallel.

A partition is a pipeline stage that executes in a single reader, transformation, or writer thread. The number of partitions in any pipeline stage equals the number of threads in the stage. By default, the Integration Service creates one partition in every pipeline stage. If we have the Informatica Partitioning option, we can configure multiple partitions for a single pipeline stage.

Setting partition attributes includes partition points, the number of partitions, and the partition types. In the session properties we can add or edit partition points. When we change partition points we can define the partition type and add or delete partitions (number of partitions).

We can set the following attributes to partition a pipeline:

Partition point:

Partition points mark thread boundaries and divide the pipeline into stages. A stage is a section of a pipeline between any two partition points. The Integration Service redistributes rows of data at partition points. When we add a partition point, we increase the number of pipeline stages by one. Increasing the number of partitions or partition points increases the number of threads.

We cannot create partition points at Source instances or at Sequence Generator transformations.

Number of partitions:

A partition is a pipeline stage that executes in a single thread. If we purchase the Partitioning option, we can set the number of partitions at any partition point. When we add partitions, we increase the number of processing threads, which can improve session performance. We can define up to 64 partitions at any partition point in a pipeline. When we increase or decrease the number of partitions at any partition point, the Workflow Manager increases or decreases the number of partitions at all partition points in the pipeline. The number of partitions remains consistent throughout the pipeline. The Integration Service runs the partition threads concurrently.

Partition types:

The Integration Service creates a default partition type at each partition point. If we have the Partitioning option, we can change the partition type. The partition type controls how the Integration Service distributes data among partitions at partition points.

We can define the following partition types here: Database partitioning, Hash auto-keys, Hash user keys, Key range, Pass-through, Round-robin.

Database partitioning:

The Integration Service queries the database system for table partition information. It reads partitioned data from the corresponding nodes in the database.

Pass-through:

The Integration Service processes data without redistributing rows among partitions. All rows in a single partition stay in the partition after crossing a pass-through partition point. Choose pass-through partitioning when we want to create an additional pipeline stage to improve performance, but do not want to change the distribution of data across partitions.

Round-robin:

The Integration Service distributes data evenly among all partitions. Use round-robin partitioning where we want each partition to process approximately the same numbers of rows i.e. load balancing.

Hash auto-keys:

The Integration Service uses a hash function to group rows of data among partitions. The Integration Service groups the data based on a partition key. The Integration Service uses all grouped or sorted ports as a compound partition key. We may need to use hash auto-keys partitioning at Rank, Sorter, and unsorted Aggregator transformations.

Hash user keys:

The Integration Service uses a hash function to group rows of data among partitions. We define the number of ports to generate the partition key.

Key range:

The Integration Service distributes rows of data based on a port or set of ports that we define as the partition key. For each port, we define a range of values. The Integration Service uses the key and ranges to send rows to the appropriate partition. Use key range partitioning when the sources or targets in the pipeline are partitioned by key range.

Points to consider while using Informatica partitions:

· We cannot create a partition key for hash auto-keys, round-robin, or pass-through types partitioning
· If you have bitmap index defined upon the target and you are using pass-through partitioning to, say Update the target table - the session might fail as bitmap index creates serious locking problem in this scenario
· Partitioning considerably increases the total DTM buffer memory requirement for the job. Ensure you have enough free memory in order to avoid memory allocation failures
· When you do pass-through partitioning, Informatica will try to establish multiple connection requests to the database server. Ensure that database is configured to accept high number of connection requests
· As an alternative to partitioning, you may also use native database options to increase degree of parallelism of query processing. For example, in Oracle database you can either specify PARALLEL hint or alter the DOP of the table in subject.
· If required you can even combine Informatica partitioning with native database level parallel options - e.g. you create 5 pass-through pipelines, each sending query to Oracle database with PARALLEL hint.

noreply@blogger.com (Kareem) — Fri, 25 May 2018 03:51:00 +0000

PL/SQL Records

noreply@blogger.com (Kareem) — Mon, 11 Feb 2013 12:08:00 +0000

What are records?

Records are another type of datatypes which oracle allows to be defined as a placeholder. Records are composite datatypes, which means it is a combination of different scalar datatypes like char, varchar, number etc. Each scalar data types in the record holds a value. A record can be visualized as a row of data. It can contain all the contents of a row.

Declaring a record:

To declare a record, you must first define a composite datatype; then declare a record for that type.

The General Syntax to define a composite datatype is:

TYPE record_type_name IS RECORD

(first_col_name column_datatype,

second_col_name column_datatype, ...);

record_type_name – it is the name of the composite type you want to define.
first_col_name, second_col_name, etc.,- it is the names the fields/columns within the record.
column_datatype defines the scalar datatype of the fields.

There are different ways you can declare the datatype of the fields.

1) You can declare the field in the same way as you declare the fieds while creating the table.

2) If a field is based on a column from database table, you can define the field_type as follows:

col_name table_name.column_name%type;

By declaring the field datatype in the above method, the datatype of the column is dynamically applied to the field. This method is useful when you are altering the column specification of the table, because you do not need to change the code again.

NOTE: You can use also %type to declare variables and constants.

The General Syntax to declare a record of a uer-defined datatype is:

record_name record_type_name;

The following code shows how to declare a record called employee_rec based on a user-defined type.

DECLARE

TYPE employee_type IS RECORD

(employee_id number(5),

employee_first_name varchar2(25),

employee_last_name employee.last_name%type,

employee_dept employee.dept%type);

employee_salary employee.salary%type;

employee_rec employee_type;

If all the fields of a record are based on the columns of a table, we can declare the record as follows:

record_name table_name%ROWTYPE;

For example, the above declaration of employee_rec can as follows:

DECLARE employee_rec employee%ROWTYPE;

The advantages of declaring the record as a ROWTYPE are:

1) You do not need to explicitly declare variables for all the columns in a table.

2) If you alter the column specification in the database table, you do not need to update the code.

The disadvantage of declaring the record as a ROWTYPE is:

1) When u create a record as a ROWTYPE, fields will be created for all the columns in the table and memory will be used to create the datatype for all the fields. So use ROWTYPE only when you are using all the columns of the table in the program.

NOTE: When you are creating a record, you are just creating a datatype, similar to creating a variable. You need to assign values to the record to use them.

The following table consolidates the different ways in which you can define and declare a pl/sql record.

Syntax	Usage
TYPE record_type_name IS RECORD (column_name1 datatype, column_name2 datatype, ...);	Define a composite datatype, where each field is scalar.
col_name table_name.column_name%type;	Dynamically define the datatype of a column based on a database column.
record_name record_type_name;	Declare a record based on a user-defined type.
record_name table_name%ROWTYPE;	Dynamically declare a record based on an entire row of a table. Each column in the table corresponds to a field in the record.

Passing Values To and From a Record

When you assign values to a record, you actually assign values to the fields within it.

The General Syntax to assign a value to a column within a record direclty is:

record_name.col_name := value;

If you used %ROWTYPE to declare a record, you can assign values as shown:

record_name.column_name := value;

We can assign values to records using SELECT Statements as shown:

SELECT col1, col2

INTO record_name.col_name1, record_name.col_name2

FROM table_name

[WHERE clause];

If %ROWTYPE is used to declare a record then you can directly assign values to the whole record instead of each columns separately. In this case, you must SELECT all the columns from the table into the record as shown:

SELECT * INTO record_name

FROM table_name

[WHERE clause];

Lets see how we can get values from a record.

The General Syntax to retrieve a value from a specific field into another variable is:

var_name := record_name.col_name;

The following table consolidates the different ways you can assign values to and from a record:

Syntax	Usage
record_name.col_name := value;	To directly assign a value to a specific column of a record.
record_name.column_name := value;	To directly assign a value to a specific column of a record, if the record is declared using %ROWTYPE.
SELECT col1, col2 INTO record_name.col_name1, record_name.col_name2 FROM table_name [WHERE clause];	To assign values to each field of a record from the database table.
SELECT * INTO record_name FROM table_name [WHERE clause];	To assign a value to all fields in the record from a database table.
variable_name := record_name.col_name;	To get a value from a record column and assigning it to a variable.

PL/SQL Constants

noreply@blogger.com (Kareem) — Mon, 11 Feb 2013 11:57:00 +0000

As the name implies a constant is a value used in a PL/SQL Block that remains unchanged throughout the program. A constant is a user-defined literal value. You can declare a constant and use it instead of actual value.

For example: If you want to write a program which will increase the salary of the employees by 25%, you can declare a constant and use it throughout the program. Next time when you want to increase the salary again you can change the value of the constant which will be easier than changing the actual value throughout the program.

The General Syntax to declare a constant is:

Constant_name CONSTANT datatype := VALUE;

constant_name is the name of the constant i.e. similar to a variable name.
The word CONSTANT is a reserved word and ensures that the value does not change.
VALUE - It is a value which must be assigned to a constant when it is declared. You cannot assign a value later.

For example, to declare salary_increase, you can write code as follows:

DECLARE

salary_increase CONSTANT number (3) := 10;

You must assign a value to a constant at the time you declare it. If you do not assign a value to a constant while declaring it and try to assign a value in the execution section, you will get a error. If you execute the below Pl/SQL block you will get error.

DECLARE

salary_increase CONSTANT number(3);

BEGIN

salary_increase := 100;

dbms_output.put_line (salary_increase);

END;

Implementing Informatica Persistent Cache

noreply@blogger.com (Kareem) — Sun, 10 Feb 2013 09:51:00 +0000

You must have noticed that the "time" Informatica takes to build the lookup cache can be too much sometimes depending on the lookup table size/volume. Using Persistent Cache, you may save lot of your time. This article describes how to do it.

What is Persistent Cache?

Lookups are cached by default in Informatica. This means that Informatica by default brings in the entire data of the lookup table from database server to Informatica Server as a part of lookup cache building activity during session run. If the lookup table is too huge, this ought to take quite some time. Now consider this scenario - what if you are looking up to the same table different times using different lookups in different mappings? Do you want to spend the time of building the lookup cache again and again for each lookup? Off course not! Just use persistent cache option!

Yes, Lookup cache can be either non-persistent or persistent. The Integration Service saves or deletes lookup cache files after a successful session run based on whether the Lookup cache is checked as persistent or not.

Where and when we shall use persistent cache:

Suppose we have a lookup table with same lookup condition and return/output ports and the lookup table is used many times in multiple mappings. Let us say a Customer Dimension table is used in many mappings to populate the surrogate key in the fact tables based on their source system keys. Now if we cache the same Customer Dimension table multiple times in multiple mappings that would definitely affect the SLA loading timeline.

There can be some functional reasons also for selecting to use persistent cache. Please read the article Advantage and Disadvantage of Persistent Cache Lookup to know how persistent cache can be used to ensure data integrity in long running ETL sessions where underlying tables are also changing.

So the solution is to use Named Persistent Cache.

In the first mapping we will create the Named Persistent Cache file by setting three properties in the Properties tab of Lookup transformation.

§ Lookup cache persistent:

To be checked i.e. a Named Persistent Cache will be used.

§ Cache File Name Prefix:

user_defined_cache_file_name i.e. the Named Persistent cache file name that will be used in all the other mappings using the same lookup table. Enter the prefix name only. Do not enter .idx or .dat

§ Re-cache from lookup source:

To be checked i.e. the Named Persistent Cache file will be rebuilt or refreshed with the current data of the lookup table.

Next in all the mappings where we want to use the same already built Named Persistent Cache we need to set two properties in the Properties tab of Lookup transformation.

§ Lookup cache persistent:

To be checked i.e. the lookup will be using a Named Persistent Cache that is already saved in Cache Directory and if the cache file is not there the session will not fail it will just create the cache file instead.

§ Cache File Name Prefix:

user_defined_cache_file_name i.e. the Named Persistent cache file name that was defined in the mapping where the persistent cache file was created.

Informatica – User Defined Functions

noreply@blogger.com (Kareem) — Sat, 09 Feb 2013 17:05:00 +0000

Informatica User Defined Functions are similar to Built-in Functions, where these functions need to be created once and execute multiple times. Transformation logics that are common across the ports are the ideal candidate for User Defined Functions.

Transformation Logic implemented without User Defined Functions

Validation “IIF( ISNULL(LTRIM(RTRIM(INPUT))),’TRUE’,’FALSE’)” is being performed in multiple ports.

The disadvantage with this approach is any changes to this validation need to be done in all the ports.

This can be addressed by creating a User Defined function and have the logic incorporated there.

Steps to Create User Defined Functions

Step 1 : Right-click on the User-Defined Functions folder in a repository folder in the Designer.

Click on “New”

Step 2: In Editor add the transformation logic / validation that needs to be performed.

Click ok and validate the UDF.

User Defined Function – Type:

Public if the function is callable from any expression. Private if the function is only callable from another user-defined function.

To Call User Defined functions from Port:

Informatica Performance Improvement Tips

noreply@blogger.com (Kareem) — Sat, 09 Feb 2013 17:01:00 +0000

We often come across situations where Data Transformation Manager (DTM) takes more time to read from Source or when writing in to a Target. Following standards/guidelines can improve the overall performance.

Use Source Qualifier if the Source tables reside in the same schema
Make use of Source Qualifier “Filter” Properties if the Source type is Relational.
If the subsequent sessions are doing lookup on the same table, use persistent cache in the first session. Data remains in the Cache and available for the subsequent session for usage.
Use flags as integer, as the integer comparison is faster than the string comparison.
Use tables with lesser number of records as master table for joins.
While reading from Flat files, define the appropriate data type instead of reading as String and converting.
Have all Ports that are required connected to Subsequent Transformations else check whether we can remove these ports
Suppress ORDER BY using the ‘–‘ at the end of the query in Lookup Transformations
Minimize the number of Update strategies.
Group by simple columns in transformations like Aggregate, Source Qualifier
Use Router transformation in place of multiple Filter transformations.
Turn off the Verbose Logging while moving the mappings to UAT/Production environment.
For large volume of data drop index before loading and recreate indexes after load.
For large of volume of records Use Bulk Load Increase the commit interval to a higher value large volume of data
Set ‘Commit on Target’ in the sessions

Informatica Development Best Practices – Mapping

noreply@blogger.com (Kareem) — Sat, 09 Feb 2013 16:58:00 +0000

The following are generally accepted “Best Practices” for Informatica PowerCenter ETL development and if implemented, can significantly improve the overall performance.

Category	Technique	Benefits
Source Extracts	Loading data from Fixed-width files take less time than delimited, since delimited files require extra parsing. In case of Fixed width files, Integration service know the Start and End position of each columns upfront and thus reduces the processing time.	Performance Improvement
Source Extracts	Using flat files located on the server machine loads faster than a database located on the server machine.	Performance Improvement
Mapping Designer	There should be a place holder transformation (Expression) immediately after the Source and one before the target. Data type and Data width changes are bound to happen during development phase and these place holder transformations are used to preserve the port link between transformations.	Best Practices
	Connect only the ports that are required in targets to subsequent transformations. Also, active transformations that reduce the number of records should be used as early in the mapping.	Code Optimization
	If a join must be used in the Mapping, select appropriate driving/master table while using joins. The table with the lesser number of rows should be the driving/master table.	Performance Improvement
Transformations	If there are multiple Lookup condition, make the condition with the “=” sign first in order to optimize the lookup performance. Also, indexes on the database table should include every column used in the lookup condition.	Code Optimization
	Persistent caches should be used if the lookup data is not expected to change often. This cache files are saved and can be reused for subsequent runs, eliminating querying the database.	Performance Improvement
	Integration Service processes numeric operations faster than string operations. For example, if a lookup is done on a large amount of data on two columns, EMPLOYEE_NAME and EMPLOYEE_ID, configuring the lookup around EMPLOYEE_ID improves performance.	Code Optimization
	Replace Complex filter expression with a flag (Y/N). Complex logic should be moved to the expression transformation and the result should be stored in a port. Filter expression should take less time to evaluate this port rather than executing the entire logic in Filter expression.	Best Practices
	Power Center Server automatically makes conversions between compatible data types which slowdown the performance considerably. For example, if a mapping moves data from an Integer port to a Decimal port, then back to an Integer port, the conversion may be unnecessary.	Performance Improvement
	Assigning default values to a port; Transformation errors written to session log will always slow down the session performance. Try removing default values and eliminate transformation errors.	Performance Improvement
	Complex joins in Source Qualifiers should be replaced with Database views. There won’t be any performance gains, but it improves the readability a lot. Also, any new conditions can be evaluated easily by just changing the Database view “WHERE” clause.	Best Practices

COMPRESS Function In Informatica

noreply@blogger.com (Kareem) — Sat, 09 Feb 2013 16:52:00 +0000

Compress Function is used while sending large amount of data over a network. It increases the performance by reducing the amount of data Power Center Integration Service writes over the network.

Syntax

COMPRESS( Input Value )

Note: This will return a binary value for an input value and Null for null values.

What are new features of INFORMATICA 9 compared to INFORMATICA 8.6

noreply@blogger.com (Kareem) — Sat, 09 Feb 2013 16:50:00 +0000

Informatica 9 empowers line-of-business managers and business analysts to identify bad data and fix it faster. Architecture wise there are no differences between Informatica 8 and 9 but there are some new features added in powercenter 9.

New Client tools

Informatica 9 includes the Informatica Developer and Informatica Analystclient tools.

The Informatica Developer tool is eclipse-based and supports both data integration and data quality for enhanced productivity. From here you can update/refine those same rules, and create composite data objects - e.g. Get customer details from a number of different sources and aggregate these up to a Customer Data Object.

The Informatica Analyst tool is a browser-based tool for analysts, stewards and line of business managers. This tool supports data profiling, specifying and validating rules (Scorecards), and monitoring data quality.

Informatica Administrator

The powercenter Administration Console has been renamed the Informatica Administrator.

The Informatica Administrator is now a core service in the Informatica Domain that is used to configure and manage all Informatica Services, Security and other domain objects (such as connections) used by the new services.

The Informatica Administrator has a new interface. Some of the properties and configuration tasks from the powercenter Administration Console have been moved to different locations in Informatica Administrator. The Informatica Administrator is expanded to include new services and objects.

Cache Update in Lookup Transformation

You can update the lookup cache based on the results of an expression. When an expression is true, you can add to or update the lookup cache. You can update the dynamic lookup cache with the results of an expression.

Database deadlock resilience

In previous releases, when the Integration Service encountered a database deadlock during a lookup, the session failed. Effective in 9.0, the session will not fail. When a deadlock occurs, the Integration Service attempts to run the last statement in a lookup. You can configure the number of retry attempts and time period between attempts.

Multiple rows return

Lookups can now be configured as an Active transformation to return Multiple Rows.We can configure the Lookup transformation to return all rows that match a lookup condition. A Lookup transformation is an active transformation when it can return more than one row for any given input row.

Limit the Session Log

You can limit the size of session logs for real-time sessions. You can limit the size by time or by file size. You can also limit the number of log files for a session.

Auto-commit

We can enable auto-commit for each database connection. Each SQL statement in a query defines a transaction. A commit occurs when the SQL statement completes or the next statement is executed, whichever comes first.

Passive transformation

We can configure the SQL transformation to run in passive mode instead of active mode. When the SQL transformation runs in passive mode, the SQL transformation returns one output row for each input row.

Connection management

Database connections are centralized in the domain. We can create and view database connections in Informatica Administrator, Informatica Developer, or Informatica Analyst. Create, view, edit, and grant permissions on database connections in Informatica Administrator.

Monitoring

We can monitor profile jobs, scorecard jobs, preview jobs, mapping jobs, and SQL Data Services for each Data Integration Service. View the status of each monitored object on the Monitoring tab of Informatica Administrator.

Deployment

We can deploy, enable, and configure deployment units in the Informatica Administrator. Deploy Deployment units to one or more Data Integration Services. Create deployment units in Informatica Developer.

Model Repository Service

Application service that manages the Model repository. The Model repository is a relational database that stores the metadata for projects created in Informatica Analyst and Informatica Designer. The Model repository also stores run-time and configuration information for applications deployed to a Data.

Data Integration Service

Application service that processes requests from Informatica Analyst and Informatica Developer to preview or run data profiles and mappings. It also generates data previews for SQL data services and runs SQL queries against the virtual views in an SQL data service. Create and enable a Data Integration Service on the Domain tab of Informatica Administrator.

XML Parser

The XML Parser transformation can validate an XML document against a schema. The XML Parser transformation routes invalid XML to an error port. When the XML is not valid, the XML Parser transformation routes the XML and the error messages to a separate output group that We can connect to a target.

Enforcement of licensing restrictions

Powercenter will enforce the licensing restrictions based on the number of CPUs and repositories.

Also Informatica 9 supports data integration for the cloud as well as on premise. You can integrate the data in cloud applications, as well as run Informatica 9 on cloud infrastructure.

Recovery Mode In Informatica

noreply@blogger.com (Kareem) — Sat, 09 Feb 2013 16:44:00 +0000

If your workflow gets failed and the integration service is still able to access the workflow then we can easily recover the workflow. When you recover a workflow it starts from the point where it got interruption

The best way to explain recovery mode is by an example. Suppose that a session fails after loading of one million records in to the target then on recovery it will start from the records after one millionth record(Note: Target load type should be normal

How to Calculate Memory Cache for Informatica Objects

noreply@blogger.com (Kareem) — Sat, 09 Feb 2013 16:42:00 +0000

Cache Calculator Is To Calculate Memory Per Informatica Object.

In Informatica Workflow we have something called Cache Calculator. This is to calculate the Data cache per objects .While working on objects like Lookup Cache, Joiner Cache, Aggregator Cache, Sorter Cache we don’t have worry about the complex memory size used by these objects if we can calculate it by ourselves.

This feature is available with version Informatica 8.1.1 onwards.

Now we will see how to do!

First you need to Login to the Workflow manager and Open the required session.
Once you have opened the session, there you can see mapping tab.
In the mapping tab you can find the transformation objects used in the Informatica Mapping.
Select one of the objects that would use memory for caching.
There you can find the Objects Data Cache Size and Objects Index Cache Size Properties.

The data cache Calculators are different for different objects

Lookups

As you can see below for Lookup transformation there is a calculate button which will calculate the memory size. In order to calculate the lookup cache size we need to give the number of unique rows in a lookup. To calculate the unique rows you can make use of the SQL override or the Session log data

Aggregator

In case of Aggregator transformation as seen below we can calculate the memory size but first we need to determine the number of groups in an aggregator transformation. For that you can use session log data or SQL query

Sorter

For Sorter transformation we need to give the number of rows in sorter

Ranker

For ranker Transformation we need to determine the number of rows in a rank using the session log or SQL

How To Generate Dynamic Target File In Informatica Based On Column Value

noreply@blogger.com (Kareem) — Sat, 09 Feb 2013 16:40:00 +0000

Generate different flat file target based on the Location name, like separate files for Mumbai.dat, Bangalore.dat, and Delhi.dat

Source Table:

Dept name	Dept ID	Location
DWH	1	Mumbai
Java	2	Bangalore
Dot net	3	Delhi

Sort the source table by the column Location using a Sorter.
Create Expression transformation and create the below ports

IN_LOCATION=LOCATION
V_FLAG=IIF(IN_LOCATION=V_LOCATION,0,1)
V_LOCATION=IN_LOCATION
OUTPUT_FLAG (OUTPUT PORT) = V_FLAG

OUTPUT_FILE_NAME (OUTPUT PORT) = LOCATION ||'.dat'

**** This expression will check when Location changes (Eg.from Mumbai to Bangalore) ****

Use the following syntax for the expression:

IIF (CONDITION, VALUE1, VALUE2)

IIF(OUTPUT_FLAG = 1, TC_COMMIT_BEFORE, TC_CONTINUE_TRANSACTION)

****This expression will write to the output file when location changes (Eg.from Mumbai to Bangalore)

4. Connect to the Target Table

Note: We have to use special port called “Filename" port in the Target file definition.

How to Separate Header and Footer from Flat file

noreply@blogger.com (Kareem) — Sat, 09 Feb 2013 16:38:00 +0000

Load the Header records into one target table, say TRGT01, footer records to TRGT02 and detail records to another TRGT03:

Source File

Header”ABC”

@@@@@@@@@@@@

Detail”123”

Detail”456”

Detail”789”

Detail”012”

@@@@@@@@@@@@

Footer”Efg”

1.Create Source definition for the file and connect to an Expression

2.In Expression create two variable ports

V_HEADER= IIF (SUBSTR(COLUMN,1,1) =’Header’,1,0)
V_FOOTER= IIF (SUBSTR(COLUMN,1,1) = ‘Footer’,1,0)

Also create two output ports

O_HEADER=V_HEADER
O_FOOTER=V_FOOTER

3.Create a Router with two user groups

In first group give condition such that IIF(O_HEADER) ='1' then pass to TRGT01
In second group IIF(O_FOOTER) ='1' then pass to TRGT02
And the default group to TRGT3(this will hold the detail records)

Data Warehousing

Auto generate sequence numbers in Target tables using Oracle sequence

Why Sorter is an Active transformation ?

Informatica PowerCenter Architecture

1. Informatica PowerCenter Client Tools:These are the development tools installed at developer end. These tools enable a developer to

2. Informatica PowerCenter Repository:

3. Informatica PowerCenter Server:

Convert Julian date in Informatica

Save Informatica session logs with Timestamp

How To Generate Dynamic Target File In Informatica Based On Column Value

Dept name

Dept ID

Location

DWH

1

Mumbai

Java

2

Bangalore

Dot net

3

Delhi

Informaica Power Center v10 - Features

Powermart PMREP Commands

Command

Description

Comments

AssignPermission

Assigns permissions on a global object.

New command.

ChangeOwner

Changes the owner name for a global object.

New command.

RollbackDeployment

Rolls back a deployment to purge a deployed version from the target repository or folder.

New command.

Changed PMREP Commands

Command

Description

Comments

Connect

Connects to a repository.

-s option added to specify the security domain that the user belongs to.

Create

Creates the repository tables in the database.

-s option added to specify the security domain that the user belongs to.

CreateFolder

Creates a new folder in the repository.

-o option added to specify the security domain that the owner belongs to.

DeleteObject

Deletes an object.

You can specify “User Defined Function” as object type.

DeployDeploymentGroup

Deploys a deployment group.

-s option added to specify the security domain that the user belongs to.

DeployFolder

Deploys a folder.

-s option added to specify the security domain that the user belongs to.

ListObjects

Returns a list of objects in the repository.

You can specify “User Defined Function” as object type.

ModifyFolder

Modifies folder properties.

You can assign an operating system profile to a folder.

-a option added to specify the security domain that the owner belongs to.

Register

Registers a local repository with a connected global repository.

-s option added to specify the security domain that the user belongs to.

Restore

Restores a repository backup file to a database.

The following option is added:

-s. The security domain that the user belongs to.

The following options are removed:

-

v. User name of the repository administrator or another valid user defined in the external directory service.

-

x. Password for the user name as defined in the external directory service.

-

X. Password environment variable that stores the password of the repository administrator as defined in the external directory service.

-