Informatica Cloud vs Power Center

Know the differences between the Informatica power center and cloud tools. And the choose the right tool for your organization.

Both the power center and the cloud tools are products of the Informatica company. They serve different purposes and at the same time, they have some of the features in common. In this article, we will compare these two products and bring out the main differences between these two.

Architecture Differences

Informatica cloud architecture is pretty simple. Even developers can install and manage the servers. More on the architecture can be known at FolksTalk Website. One has to download the secure agent from the Informatica cloud admin tool and install it on the server. After installation, the username and token need to be entered and then the server is automatically tied to your organization. Adding more servers to the grid is also quite easy. just install the secure agent and add it to the group. The performance of the server can be tuned from the Run time environment in the admin window. A repository database is not required as it is maintained by the Informatica itself in the cloud.

Informatica architecture is quite complex and requires administrator to install and manage the servers. You can get the details about the architecture at Edureka website. First, the admin has to install one of the supported repository databases like Oracle or SQL Server. Then the Informatica services ( like integration and repository) need to be installed on a server. At the time of installation, the repository database connection details have to be provided. Maintaining a grid also requires a lot of steps. That is why an administrator is required to maintain the respiratory database and the servers.

Batch Processing

Both tools support the processing of huge data. This is called batch processing. The ICS app in the cloud supports the batch data processing and the power center tool also does the same thing.

The power center is quite good when it comes to batch processing. It can process bulk data and apply complex transformations on the data. It has a lot of bully in transformations that can massage the data. It gives good performance when compared to the ICS.

The ICS is designed to process low amounts of data. This tool also has all the required transformations to manipulate the data. One good feature in the ICS tool is that one can replicate all the objects data from the source database to the target database in a single task. This feature is not available in the power center.

Both the tools support making API requests through HTTP and Web Service connectors. When making API calls, the mappings need to process low amounts of data. Otherwise, this will cause a performance issue.

Real-Time Processing

Both tools can process the data in near real-time by running the mappings quite frequently. However, with cloud tools, we can develop APIs to integrate multiple applications. There is no option in the power center tool for developing the APIS. When the organization has a requirement for API development, then it should go with the Cloud integration tool.

WorkFlow or Task Designing

The power center gives flexibility to the developer to design the process flow in sequential or parallel or both or based on conditions. It also gives the flexibility to change from one design pattern to another pattern. In the ICS tool, the developer has to choose the design pattern before creating the task flow itself and it is also rigid to change between design patterns.

Performance Tuning

As the complete software of the power center is installed on our servers only, it gives complete flexibility to the admins to tune the resources and improve the performance. Whereas the cloud tool doesn't give complete flexibility to tune the resources as some of the hardware/software is maintained by the Informatica itself on the cloud.

Miscellaneous Features

The ICS provides a process developer tool using that developers can develop their own connectors to access third-party applications. No such tool is provisioned in the PowerCenter.

The ICS provides hybrid solutions through which integrations can be developed between cloud & cloud, cloud & on-premise, on-premise & on-premise. Whereas the PowerCenter tool can only access the on-Premise data set.

In the ICS, there is no need to install any client applications on the Personal computer. All the apps can be accessed from the browser and the task flows can be developed through browser UI. In power Center, the client applications need to be installed on the Personal computer. This provides for faster development and the developers do not lose any code in case of network failure.

I hope you all got a good knowledge of the differences between the cloud and PowerCenter tools. Both tools are good in their own aspects. Based on the features, data volume and company requirements, you can decide on what tool to purchase and use.

Informatica Architecture

Informatica 9 Architecture

Nice video on informatica architecture version 9.

Guidelines for Creating Parameter Files Informatica

Use the following rules and guidelines when you create parameter files:

 List all session parameters. Session parameters do not have default values. If the Integration Service cannot find a value for a session parameter, it may fail the session, take an empty string as the default value, or fail to expand the parameter at run time. Session parameter names are not case sensitive.

 List all necessary mapping parameters and variables. Mapping parameter and variable values become start values for parameters and variables in a mapping. Mapping parameter and variable names are not case sensitive.

 Enter folder names for non-unique session names. When a session name exists more than once in a repository, enter the folder name to indicate the location of the session.

 Precede parameters and variables in mapplets with the mapplet name. Use the following format:

mapplet_name.parameter_name=value mapplet2_name.variable_name=value

 Use multiple parameter files. You assign parameter files to workflows, worklets, and sessions individually. You can specify the same parameter file for all of these tasks or create multiple parameter files.

 When defining parameter values, do not use unnecessary line breaks or spaces. The Integration Service interprets additional spaces as part of a parameter name or value.

 Use correct date formats for datetime values. Use the following date formats for datetime values:

 MM/DD/RR

 MM/DD/YYYY

 MM/DD/RR HH24:MI

 MM/DD/YYYY HH24:MI

 MM/DD/RR HH24:MI:SS

 MM/DD/YYYY HH24:MI:SS

 MM/DD/RR HH24:MI:SS.MS

 MM/DD/YYYY HH24:MI:SS.MS

 MM/DD/RR HH24:MI:SS.US

 MM/DD/YYYY HH24:MI:SS.US

 MM/DD/RR HH24:MI:SS.NS

 MM/DD/YYYY HH24:MI:SS.NS

You can use the following separators: dash (-), slash (/), backslash (\), colon (:), period (.), and space. The Integration Service ignores extra spaces. You cannot use one- or three-digit values for year or the “HH12” format for hour.


 Do not enclose parameter or variable values in quotes. The Integration Service interprets everything after the first equals sign as part of the value.

 Use a parameter or variable value of the proper length for the error log table name prefix. If you use a parameter or variable for the error log table name prefix, do not specify a prefix that exceeds 19 characters when naming Oracle, Sybase, or Teradata error log tables. The error table names can have up to 11 characters, and Oracle, Sybase, and Teradata databases have a maximum length of 30 characters for table names. The parameter or variable name can exceed 19 characters.

Where to Use Parameters and Variables Informatica

You can use parameters and variables to assign values to properties in the Designer and Workflow Manager and to override some service and service process properties. For example, you can use a parameter to specify the Lookup cache file name prefix or the default remote directory for an FTP connection.

If the property is a SQL statement or command, you can either use parameters and variables within the statement or command, or you can enter a parameter or variable in the input field for the property, and set the parameter or variable to the entire statement or command in the parameter file.

For example, you want to use a parameter or variable in a relational target override. You can enter a parameter or variable within the UPDATE statement of a relational target override and define the parameter or variable below the appropriate heading in the parameter file. Or, to define the UPDATE statement in a parameter file, complete the following steps:

1. In the Designer, edit the target instance, enter session parameter $ParamMyOverride in the Update Override field, and save the mapping.

2. In the Workflow Manager, configure the workflow or session to use a parameter file.

3. Set $ParamMyOverride to the SQL UPDATE statement below the appropriate heading in the parameter file.


You can also use a parameter file to override service and service process properties defined in the Administrator tool. For example, you can override the session log directory, $PMSessionLogDir. To do this, configure the workflow or session to use a parameter file and set $PMSessionLogDir to the new file path in the parameter file.

You can specify parameters and variables for the following PowerCenter objects:

 Sources. You can use parameters and variables in input fields related to sources.

 Targets. You can use parameters and variables in input fields related to targets.

 Transformations. You can use parameters and variables in input fields related to transformations.

 Tasks. You can use parameters and variables in input fields related to tasks in the Workflow Manager.

 Sessions. You can use parameters and variables in input fields related to Session tasks.

 Workflows. You can use parameters and variables in input fields related to workflows.

 Connections. You can use parameters and variables in input fields related to connection objects.

 Data profiling objects. You can use parameters and variables in input fields related to data profiling.

Parameter and Variable Types Informatica

A parameter file can contain different types of parameters and variables. When you run a session or workflow that uses a parameter file, the Integration Service reads the parameter file and expands the parameters and variables defined in the file.

You can define the following types of parameter and variable in a parameter file:

 Service variables. Define general properties for the Integration Service such as email addresses, log file counts, and error thresholds. $PMSuccessEmailUser, $PMSessionLogCount, and $PMSessionErrorThreshold are examples of service variables. The service variable values you define in the parameter file override the values that are set in the Administrator tool.

 Service process variables. Define the directories for Integration Service files for each Integration Service process. $PMRootDir, $PMSessionLogDir, and $PMBadFileDir are examples of service process variables. The service process variable values you define in the parameter file override the values that are set in the Administrator tool. If the Integration Service uses operating system profiles, the operating system user specified in the operating system profile must have access to the directories you define for the service process variables.

 Workflow variables. Evaluate task conditions and record information in a workflow. For example, you can use a workflow variable in a Decision task to determine whether the previous task ran properly. In a workflow, $TaskName.PrevTaskStatus is a predefined workflow variable and $$VariableName is a user-defined workflow variable.

 Worklet variables. Evaluate task conditions and record information in a worklet. You can use predefined worklet variables in a parent workflow, but you cannot use workflow variables from the parent workflow in a worklet. In a worklet, $TaskName.PrevTaskStatus is a predefined worklet variable and $$VariableName is a user-defined worklet variable.

 Session parameters. Define values that can change from session to session, such as database connections or file names. $PMSessionLogFile and $ParamName are user-defined session parameters.

 Mapping parameters. Define values that remain constant throughout a session, such as state sales tax rates. When declared in a mapping or mapplet, $$ParameterName is a user-defined mapping parameter.

 Mapping variables. Define values that can change during a session. The Integration Service saves the value of a mapping variable to the repository at the end of each successful session run and uses that value the next time you run the session. When declared in a mapping or mapplet, $$VariableName is a mapping variable.


You cannot define the following types of variables in a parameter file:

 $Source and $Target connection variables. Define the database location for a relational source, relational target, lookup table, or stored procedure.

 Email variables. Define session information in an email message such as the number of rows loaded, the session completion time, and read and write statistics.

 Local variables. Temporarily store data in variable ports in Aggregator, Expression, and Rank transformations.

 Built-in variables. Variables that return run-time or system information, such as Integration Service name or system date.

 Transaction control variables. Define conditions to commit or rollback transactions during the processing of database rows.

 ABAP program variables. Represent SAP structures, fields in SAP structures, or values in the ABAP program.

Informatica Parameter Files Overview

A parameter file is a list of parameters and variables and their associated values. These values define properties for a service, service process, workflow, worklet, or session. The Integration Service applies these values when you run a workflow or session that uses the parameter file.

Parameter files provide you with the flexibility to change parameter and variable values each time you run a session or workflow. You can include information for multiple services, service processes, workflows, worklets, and sessions in a single parameter file. You can also create multiple parameter files and use a different file each time you run a session or workflow. The Integration Service reads the parameter file at the start of the workflow or session to determine the start values for the parameters and variables defined in the file. You can create a parameter file using a text editor such as WordPad or Notepad.

Consider the following information when you use parameter files:

 Types of parameters and variables. You can define different types of parameters and variables in a parameter file. These include service variables, service process variables, workflow and worklet variables, session parameters, and mapping parameters and variables.

 Properties you can set in parameter files. Use parameters and variables to define many properties in the Designer and Workflow Manager. For example, you can enter a session parameter as the update override for a relational target instance, and set this parameter to the UPDATE statement in the parameter file. The Integration Service expands the parameter when the session runs.

 Parameter file structure. Assign a value for a parameter or variable in the parameter file by entering the parameter or variable name and value on a single line in the form name=value. Groups of parameters and variables must be preceded by a heading that identifies the service, service process, workflow, worklet, or session to which the parameters or variables apply.

 Parameter file location. Specify the parameter file to use for a workflow or session. You can enter the parameter file name and directory in the workflow or session properties or in the pmcmd command line.

Command Line Programs Overview

Informatica includes command line programs that you use to complete tasks from any machine in the Informatica environment. The command line programs allow you to run a subset of tasks that you can complete in Informatica Administrator. For example, you can enable or disable a Repository Service from the Administrator tool or the infacmd command line program. 

Informatica includes the following command line programs:

	infacmd. Use infacmd to access the Informatica application services.
	infasetup. Use infasetup to complete installation tasks such as defining a node or a domain.
	pmcmd. Use pmcmd to manage workflows. You can start, stop, schedule, and monitor workflows using pmcmd.
	pmrep. Use pmrep to complete repository administration tasks such as listing repository objects, creating and editing groups, and restoring and deleting repositories.

To run command line programs on UNIX, you may need to set the library path environment variable to the location of the Informatica utilities.

For ease of use, you can configure environment variables that apply each time you run the command line programs. For example, you can set an environment variable for the default domain name, user, and password to avoid typing the options at the command line.

Checking In and Out Versioned Repository Objects

When you work with versioned objects, you must check out an object if you want to change it, and save it when you want to commit the changes to the repository. You must check in the object to allow other users to make changes to it. Checking in an object adds a new numbered version to the object history.

Checking In Objects

You commit changes to the repository by checking in objects. When you check in an object, the repository creates a new version of the object and assigns it a version number. The repository increments the version number by one each time it creates a new version.

To check in an object from the Workflow Manager workspace, select the object or objects and click Versioning > Check in. If you are checking in multiple objects, you can choose to apply comment to all objects.

If you want to check out or check in scheduler objects in the Workflow Manager, you can run an object query to search for them. You can also check out a scheduler object in the Scheduler Browser window when you edit the object. However, you must run an object query to check in the object.

If you want to check out or check in session configuration objects in the Workflow Manager, you can run an object query to search for them. You can also check out objects from the Session Config Browser window when you edit them.

You also can check out and check in session configuration and scheduler objects from the Repository Manager.

Viewing and Comparing Versioned Repository Objects

You can view and compare versions of objects in the Workflow Manager. If an object has multiple versions, you can find the versions of the object in the View History window. In addition to comparing versions of an object in a window, you can view the various versions of an object in the workspace to graphically compare them.

Use the following rules and guidelines when you view older versions of objects in the workspace:

 You cannot simultaneously view multiple versions of composite objects, such as workflows and worklets.

 Older versions of a composite object might not include the child objects that were used when the composite object was checked in. If you open a composite object that includes a child object version that is purged from the repository, the preceding version of the child object appears in the workspace as part of the composite object. For example, you might want to view version 5 of a workflow that originally included version 3 of a session, but version 3 of the session is purged from the repository. When you view version 5 of the workflow, version 2 of the session appears as part of the workflow.

 You cannot view older versions of sessions if they reference deleted or invalid mappings, or if they do not have a session configuration.

Opening an Older Version of an Object

When you view an older version, the version number appears as a prefix before the object name. You can simultaneously view multiple versions of a non-composite object in the workspace.

To open an older version of an object in the workspace:

1.  In the workspace or Navigator, select the object and click Versioning > View History.

2.  Select the version you want to view in the workspace and click Tools > Open in Workspace.


Comparing Two Versions of an Object

You can compare two versions of an object through the workspace, Navigator, or the View History window.

To compare two versions of an object:

1.  In the workspace or Navigator, select an object and click Versioning > View History.

2.  Select the versions you want to compare and click Compare > Selected Versions.

-or-

Select a version and click Compare > Previous Version to compare a version of the object with the previous version.

The Diff Tool appears.

 

Workflow Tasks

You can create the following types of tasks in the Workflow Manager:

 Assignment. Assigns a value to a workflow variable. For more information, see Working with the Assignment Task.

 Command. Specifies a shell command to run during the workflow. For more information, see Command Task.

 Control. Stops or aborts the workflow. For more information about the Control task, see Control Task.

 Decision. Specifies a condition to evaluate. For more information, see Working with the Decision Task.

 Email. Sends email during the workflow. For more information about the Email task, see Sending Email.

 Event-Raise. Notifies the Event-Wait task that an event has occurred. For more information, see Working with the Event Task.

 Event-Wait. Waits for an event to occur before executing the next task. For more information, see Working with the Event Task.

 Session. Runs a mapping you create in the Designer. For more information about the Session task, see Sessions.

 Timer. Waits for a timed event to trigger. For more information, see Workflow Schedules.

Workflow Manager Tools

To create a workflow, you first create tasks such as a session, which contains the mapping you build in the Designer. You then connect tasks with conditional links to specify the order of execution for the tasks you created. The Workflow Manager consists of three tools to help you develop a workflow:

	Task Developer. Use the Task Developer to create tasks you want to run in the workflow.
	Workflow Designer. Use the Workflow Designer to create a workflow by connecting tasks with links. You can also create tasks in the Workflow Designer as you develop the workflow.
	Worklet Designer. Use the Worklet Designer to create a worklet.

Workflow Manager Overview

In the Workflow Manager, you define a set of instructions called a workflow to execute mappings you build in the Designer. Generally, a workflow contains a session and any other task you may want to perform when you run a session. Tasks can include a session, email notification, or scheduling information. You connect each task with links in the workflow.

You can also create a worklet in the Workflow Manager. A worklet is an object that groups a set of tasks. A worklet is similar to a workflow, but without scheduling information. You can run a batch of worklets inside a workflow. 

After you create a workflow, you run the workflow in the Workflow Manager and monitor it in the Workflow Monitor.

Performance Tuning Overview

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

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

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

Complete the following tasks to improve session performance:

1.	Optimize the target. Enables the Integration Service to write to the targets efficiently.
2.	Optimize the source. Enables the Integration Service to read source data efficiently.
3.	Optimize the mapping. Enables the Integration Service to transform and move data efficiently.
4.	Optimize the transformation. Enables the Integration Service to process transformations in a mapping efficiently.
5.	Optimize the session. Enables the Integration Service to run the session more quickly.
6.	Optimize the grid deployments. Enables the Integration Service to run on a grid with optimal performance.
7.	Optimize the PowerCenter components. Enables the Integration Service and Repository Service to function optimally.
8.	Optimize the system. Enables PowerCenter service processes to run more quickly.

Decimal - Informatica

When a session runs without high precision, the Integration Service converts decimal values to doubles. The transformation Decimal datatype supports precision of up to 28 digits, while the Double datatype supports precision of up to 15 digits. Therefore, precision loss occurs if the decimal value has a precision greater than 15 digits.

For example, you have a mapping with Decimal (20,0) that passes the number 40012030304957666903. If the session does not run with high precision, the Integration Service converts the decimal value to double and passes 4.00120303049577 x 10¹⁹.

To ensure precision of up to 28 digits, use the Decimal datatype and enable high precision in the session properties. When you run a session with high precision, the Integration Service processes decimal values as Decimal. Precision loss does not occur in a calculation unless the result produces a value with precision greater than 28 digits. In this case, the Integration Service stores the result as a double.

Bigint - informatica

In calculations that can produce decimal values, the Integration Service processes bigint values as doubles or decimals. When a session contains a calculation that can produce decimal values and runs without high precision, the Integration Service converts bigint values to doubles before it performs the calculation. The transformation Double datatype supports precision of up to 15 digits, while the Bigint datatype supports precision of up to 19 digits. Therefore, precision loss can occur in calculations that produce bigint values with precision of more than 15 digits.

For example, an expression transformation contains the following calculation:

POWER( BIGINTVAL, EXPVAL )

Before it performs the calculation, the Integration Service converts the inputs to the POWER function to double values. If the BIGINTVAL port contains the bigint value 9223372036854775807, the Integration Service converts this value to 9.22337203685478e+18, losing the last four digits of precision. If the EXPVAL port contains the value 1.0 and the result port is a bigint, this calculation produces a row error since the result, 9223372036854780000, exceeds the maximum bigint value.

When you use a bigint value in a calculation that can produce decimal values and you run the session with high precision, the Integration Service converts the bigint values to decimals. The transformation Decimal datatype supports precision of up to 28 digits. Therefore, precision loss does not occur in a calculation unless the result produces a value with precision greater than 28 digits. In this case, the Integration Service stores the result as a double.

High Precision Data Overview

High precision data determines how large numbers are represented with greater accuracy. The precision attributed to a number includes the scale of the number. For example, the value 11.47 has a precision of 4 and a scale of 2. Large numbers can lose accuracy because of rounding when used in a calculation that produces an overflow. Incorrect results may arise because of a failure to truncate the high precision data.

High precision data values have greater accuracy. Enable high precision if you require accurate values.

You enable high precision on the properties tab of the session. The Integration Service processes high precision data differently for bigint and decimal values.

Target-Based Commits

During a target-based commit session, the Integration Service commits rows based on the number of target rows and the key constraints on the target table. The commit point depends on the following factors:

	Commit interval. The number of rows you want to use as a basis for commits. Configure the target commit interval in the session properties.
	Writer wait timeout. The amount of time the writer waits before it issues a commit. Configure the writer wait timeout in the Integration Service setup.
	Buffer blocks. Blocks of memory that hold rows of data during a session. You can configure the buffer block size in the session properties, but you cannot configure the number of rows the block holds.

When you run a target-based commit session, the Integration Service may issue a commit before, on, or after, the configured commit interval. The Integration Service uses the following process to issue commits:

	When the Integration Service reaches a commit interval, it continues to fill the writer buffer block. When the writer buffer block fills, the Integration Service issues a commit.
	If the writer buffer fills before the commit interval, the Integration Service writes to the target, but waits to issue a commit. It issues a commit when one of the following conditions is true: The writer is idle for the amount of time specified by the Integration Service writer wait timeout option. The Integration Service reaches the commit interval and fills another writer buffer.

Note: When you choose target-based commit for a session containing an XML target, the Workflow Manager disables the On Commit session property on the Transformations view of the Mapping tab.

Commit Points Overview

A commit interval is the interval at which the Integration Service commits data to targets during a session. The commit point can be a factor of the commit interval, the commit interval type, and the size of the buffer blocks. The commit interval is the number of rows you want to use as a basis for the commit point. The commit interval type is the type of rows that you want to use as a basis for the commit point. You can choose between the following commit types:

	Target-based commit. The Integration Service commits data based on the number of target rows and the key constraints on the target table. The commit point also depends on the buffer block size, the commit interval, and the Integration Service configuration for writer timeout.
	Source-based commit. The Integration Service commits data based on the number of source rows. The commit point is the commit interval you configure in the session properties.
	User-defined commit. The Integration Service commits data based on transactions defined in the mapping properties. You can also configure some commit and rollback options in the session properties.

Source-based and user-defined commit sessions have partitioning restrictions. If you configure a session with multiple partitions to use source-based or user-defined commit, you can choose pass-through partitioning at certain partition points in a pipeline.

Running Full Pushdown Optimization Sessions

To use full pushdown optimization, the source and target databases must be in the same relational database management system. When you run a session configured for full pushdown optimization, the Integration Service analyzes the mapping from the source to the target or until it reaches a downstream transformation it cannot push to the target database. It generates and executes SQL statements against the source or target based on the transformation logic it can push to the database.

When you run a session with large quantities of data and full pushdown optimization, the database server must run a long transaction. Consider the following database performance issues when you generate a long transaction:

	A long transaction uses more database resources.
	A long transaction locks the database for longer periods of time. This reduces database concurrency and increases the likelihood of deadlock.
	A long transaction increases the likelihood of an unexpected event.

To minimize database performance issues for long transactions, consider using source-side or target-side pushdown optimization.

Running Target-Side Pushdown Optimization Sessions

When you run a session configured for target-side pushdown optimization, the Integration Service analyzes the mapping from the target to the source or until it reaches an upstream transformation it cannot push to the target database. It generates an INSERT, DELETE, or UPDATE statement based on the transformation logic for each transformation it can push to the target database. The Integration Service processes the transformation logic up to the point that it can push the transformation logic to the database. Then, it executes the generated SQL on the target database.

Source-Side Pushdown Optimization Sessions

When you run a session configured for source-side pushdown optimization, the Integration Service analyzes the mapping from the source to the target or until it reaches a downstream transformation it cannot push to the source database. 

The Integration Service generates and executes a SELECT statement based on the transformation logic for each transformation it can push to the database. Then, it reads the results of this SQL query and processes the remaining transformations.