Informatica Powercenter Express - Aggregator Transformation

Aggregator transformation is used to perform aggregate calculations, such as averages and sums. The Data Integration Service performs aggregate calculations as it reads and stores data group and row data in an aggregate cache.

The transformation language has the following aggregate functions

 AVG

COUNT

FIRST

LAST

MAX

MEDIAN

MIN

PERCENTILE

STDDEV

SUM

VARIANCE

An Aggregator transformation has the following port types

Input Receives data from upstream transformations.

Output Provides the return value of an expression.

Pass-Through Passes data unchanged.

Variable Used for local variables.

Group by Indicates how to create groups. When grouping data, the Aggregator transformation outputs the last row of each group unless otherwise specified.

Advanced properties for an Aggregator transformations

Cache Directory

Local directory where the Data Integration Service creates the index cache files and data cache files. If you have enabled incremental aggregation, the Data Integration Service creates a backup of the files each time you run the mapping. The cache directory must contain enough disk space for two sets of the files.

Data Cache Size

Data cache size for the transformation.

Index Cache Size

Index cache size for the transformation.

Sorted Input

Select this option only if the mapping passes sorted data to the Aggregator transformation.

Tracing Level  
Amount of detail that appears in the log for this transformation. You can choose terse, normal, verbose initialization, or verbose data. Default is normal.

Tips to Improve performance while using Aggregator Transformation

• Use sorted input to decrease the use of aggregate caches. Sorted input reduces the amount of data cached during mapping run and improves performance. Use this option with the Sorter transformation to pass sorted data to the Aggregator transformation.
• Limit the number of connected input/output or output ports to reduce the amount of data the Aggregator transformation stores in the data cache.
• If you use a Filter transformation in the mapping, place the transformation before the Aggregator transformation to reduce unnecessary aggregation.

Scenario For Aggregator Transformation

Calculate total salaries paid to employees in each department

*In case of any questions, feel free to leave comments on this page and I would get back as soon as I can.

Informatica Powercenter Express Administrator

Informatica Administrator is the administration tool that you use to administer the informatica domain and informatica security. Use the Administrator tool to do tasks like managing logs, domain objects, and user permissions, monitoring jobs and applications that run on the Data Integration Service and managing users, groups, roles, and privileges.

To log in to the Administrator tool, enter the URL for the Administrator tool login page. For the personal edition the URL is

http://localhost:7009/administrator/#admin

The Administrator tool has the following tabs:

Domain  View information about the domain and view and manage objects in the domain.

Logs View log events for the domain and services within the domain.

Monitoring  Monitor Data Integration Services and integration objects that run on the Data Integration Service. View the status of profile jobs, preview jobs, mapping jobs, and workflows for the Data Integration Service.

Security Manage users, groups, roles, and privileges. If you have PowerCenter Express Personal Edition, you do not have access to the Security tab.

Powercenter Express Administrator 

*In case of any questions, feel free to leave comments on this page and I would get back as soon as I can.

Informatica Powercenter Express - Transformation Basics

Transformation 

It is an object that generates, modifies, or passes data. Transformations can be active/passive or connected/unconnected.

Active transformation changes the number of rows that pass through a transformation or it changes the row type. We cannot connect multiple active transformations or an active and a passive transformation to the same downstream transformation, because the Data Integration Service might not be able to concatenate the rows passed by active transformations.

Passive Transformation does not change the number of rows that pass through the transformation, maintains the transaction boundary, and maintains the row type. 

Connected Transformation is connected to the data flow.

Unconnected transformation is not connected to other transformations in the mapping. An unconnected transformation is called within another transformation, and returns a value to that transformation.

Transformation	Type	Description
Aggregator	Active/Connected	Performs aggregate calculations
Expression	Passive/Connected	Calculates a value
Filter	Active/Connected	Filters data
Java	Active or Passive/ Connected	Executes user logic coded in Java. The byte code for the user logic is stored in the repository
Joiner	Active/Connected	Joins data from different databases or flat file systems
Normalizer	Active/Connected	Processes source rows that contain multiple-occurring data and returns a target row for each instance of the multiple-occurring data
Sorter	Active/Connected	Sorts data based on a sort key
Rank	Active/Connected	Limits records to a top or bottom range
Lookup	Active or Passive/ Connected or Unconnected	Look up and return data from a flat file, logical data object, reference table, relational table, view, or synonym
Update Strategy	Active/Connected	Determines whether to insert, delete, update, or reject rows
Sequence Generator	Passive/Connected	Generates a numeric sequence of values
Union	Active/Connected	Merges data from different databases or flat file systems
SQL	Active or Passive/Connected	Executes SQL queries against a database
Router	Active/Connected	Routes data into multiple transformations based on group conditions

Informatica Powercenter Express Developer - User Interface and Importing Relational and Flatfile Objects

Developer tool is used to design and implement data integration solutions like importing metadata, creating connections, creating logical data objects, creating mapping and workflows.

Informatica Developer User Interface

Import Flatfile and Relational Data Object

*In case of any questions, feel free to leave comments on this page and I would get back as soon as I can.

Informatica Powercenter Express Architecture

Powercenter Express Architecture

Informatica Domain is the fundamental administrative unit in Informatica. Application clients, application services, and repositories are components that run within the Informatica domain.

Services for the domain include the Service Manager and a set of application services:

Service Manager  manages all domain operations. It runs the application services and performs domain functions on each node in the domain like authentication, authorization, and logging.

Application Services represent server-based functionality, such as the Model Repository Service and the Data Integration Service.

Data Integration Service is an application service that performs data integration tasks like previewing data and running profiles and mappings for Informatica Developer.

Model Repository Service is an application service that manages the Model repository which is a relational database that stores the metadata for projects created in Informatica Developer. The Model repository also stores run-time and configuration information for applications that are deployed to a Data Integration Service.

Application Clients is a group of clients that you use to send requests to the Service Manager or application services.

Informatica Developer  is an application client that developers can use to design and implement data integration solutions.

Informatica Administrator  is an application client that consolidates the administrative tasks for domain objects such as services, connections, and licenses.

Repositories are a group of relational databases that store metadata about objects and processes required to handle user requests from application clients.

Model repository is a relational database that stores the metadata for projects and also stores run-time and configuration information for applications that are deployed to a Data Integration Service.

Domain configuration repository is a set of domain metadata tables stored in a relational database. Each time an administrator makes a change to the domain, the Service Manager writes the change to the domain configuration repository.

Profile warehouse is a relational database that the Data Integration Services uses to store profile results.

Download and Install Informatica Powercenter Express

Download free Informatica powercenter express in the following site

https://community.informatica.com/solutions/pcexpress

Select click to download the personal edition which is free. If you have an account then login otherwise sign up. After  login it will lead you to the following page:

Download whichever version is suitable for your pc and the license key.

The following table lists the minimum system requirements for Informatica installation on windows

Installation of Informatica power center express

Close all other applications

Unzip the downloaded file

Run the install.bat file

Installation wizard will pop up. Review the terms of the license agreement and accept the terms and click next.

 

Enter the path where you want to install the client. Default is \Informatica\PCExpress and enter the  license file location and name of the file. Click Next.

Review preinstallation summary. Click Next.

After installation and configuration setup the administrator credentials and click next.

The following table describes the properties that you specify:

 

After defining the domain the installation is complete. Click done.