[informatica] Get stats/statistics mantained by Informatica

So below are few queries which would help you to get the statistics which is saved by Informatica into its repository and the one which is available in Informatica monitor to view:

1. From the below query, you can take up workflow information. Also workflow run id is important, if you need to see more detailed info from other tables:

select* from dbo.OPB_WFLOW_RUN

where WORKFLOW_NAME='wf_'

order by workflow_run_id;

2. From the below query, you can take up session information, like session start time and other details, but it wont show you the row counts that moved.

select *

from dbo.REP_TASK_INST_RUN

where subject_area='INTG_'

and WORKFLOW_NAME='wf_'

and workflow_run_id=2574343;

3. From the below query, you can take up other session details, like how much rows were moved etc.

select * from
OPB_SESS_TASK_LOG where workflow_run_id=2574343;
 

[DI] ETL versus ELT

When does ETL win?

• Ordered transformations not well suited to set processing.
• Integration of third party software tools best managed by Informatica outside of the RDBMS (e.g., name and address standardization utilities).
• Maximize in-memory execution for multiple step transformations that do not require access to large volumes of historical or lookup data (note: caching plays a role).
• Streaming data loads using message-based feeds with "real-time" data acquisition.

When does ELT win?

• Leverage of high performance DW platform for execution reduces capacity requirements on ETL servers - this is especially useful when peak requirements for data integration are in a different window than peak requirements for data warehouse analytics.
• Significantly reduce data retrieval overhead for transformations that require access to historical data or large cardinality lookup data already in the data warehouse.
• Batch or mini-batch loads with reasonably large data sets, especially with pre-existing indices that may be leveraged for processing.
• Optimize performance for large scale operations that are well suited for set operations such as complex joins and large cardinality aggregations.

[DI] A Taxonomy of Data Integration Techniques

 

 There are three main approaches:

1.ETL Approach: (1) Extract from the source systems, (2) Transform inside the Informatica engine on integration engine servers, and (3) Load into target tables in the data warehouse.

2.ELT Approach: (1) Extract from the source systems, (2) Load into staging tables inside the data warehouse RDBMS servers, and (3) Transform inside the RDBMS engine using generated SQL with a final insert into the target tables in the data warehouse.

3.Hybrid ETLT Approach: (1) Extract from the source systems, (2) Transform inside the Informatica engine on integration engine servers, (3) Load into staging tables in the data warehouse, and (4) apply further Transformations inside the RDBMS engine using generated SQL with a final insert into the target tables in the data warehouse.

[Informatica] Tutorial Part 1

Here is the link if Informatica Tutorial Part 1 for beginners:

http://www.youtube.com/watch?v=ufH_n5exxQw

[Informatica] How to execute one or more mappings from workflow

There may be scenarios that you need to execute one or more mapping from eniter workflow which may contains number of mappings.

Here is the way of doing that

dos2unix olapjoblist.properties
cd $INFA_HOME/bin/
while read line  
  do
  
   test=$( ./pmcmd starttask -sv $INTEGRATION_SERVICE -d $DOMAIN_NAME -uv INFA_USER -pv INFA_PASS -f $FOLDER_NAME -paramfile $parameterFile -w wf_olapconsolidation -wait $line)
   
  
   if [ "$?" != 0 ]; then
    # handle error
    echo "Summary: Could not execute task."
   else
    # no error
    echo "Summary: Task executed successfully."
   fi
   echo "Error Code:$?"
   echo "Description: $test"
  
done < $currentpath/olapjoblist.properties

so olapjoblist.properties if a file will all the session names with it

[Informatica] Tutorial - Working with text files

Here is the link of video in which i have created a mapping , workflow for how to take data from one csv and transform and create another csv.

http://www.youtube.com/watch?v=kbwbafnlvoc

informatica_tutorial_textfiles

[Informatica] Tutorial Part 5

Here is the last session of informatica tutorial:

http://www.youtube.com/watch?v=8zdPvAkMv90

[Informatica] Tutorial Part 4

Here is the third part of Informatica Tutorial Part 4.


http://www.youtube.com/watch?v=AuCVmPpRvy4

[Informatica] Tutorial Part 3

Here is the third part of Informatica Tutorial Part 3.

http://www.youtube.com/watch?v=upwtGPn4pDI

[Informatica] Tutorial Part 2

Informatica Tutorial Part 2 for two beginners:

http://www.youtube.com/watch?v=oM2d-IHfRUw

[Informatica] Tutorial Part 1

Here is the link if Informatica Tutorial Part 1 for beginners:

http://www.youtube.com/watch?v=ufH_n5exxQw

[Informatica] Configure Power Center Integration Service

Finally we need to configure Integration Service through which we can execute our workflows.
1. In the Administrator tool, click the Domain tab.
2. On the Navigator Actions menu, click New > PowerCenter Integration Service.
The New Integration Service dialog box appears.
3. Enter the Service Name, License, And Primary Node.
4. Now provide the repository information which you just created, its included repository name, username and password.
5. Click Finish.

[Informatica] Configure Power Center Repository Service

After the successful installation of Informatica we need to configure repository service. These steps need to be done from windows operating system. Any browser should be installed for performing these steps.
1. Open up the browser and type in below format http://DomainName:PortNo/administrator/index.html
Sample: http://informatica-test:6007/administrator/index.html
2. Enter the username and password provided during the installation.
3. In the Administrator tool, click the Domain tab.
4. In the Navigator, select the folder where you want to create the PowerCenter Repository Service. Note: If you do not select a folder, you can move the PowerCenter Repository Service into a folder after you create it.
5. In the Domain Actions menu, click New > PowerCenter Repository Service. The Create New Repository Service dialog box appears.
6. Provide the Repository Service Name and all other database parameters.
7. Click the option to create repository contents.
8. Select the license, node and all other information that need to be provided.
9. Select “UTF-8 encoding of Unicode” in Code page combo box.
10. Click Ok.

[Informatica] Server Installation 9.0.1

Here are steps to install informatica server 9.0.1:

1. Verify that your environment meets the minimum system requirements, and complete the pre-installation tasks as completed in steps 3 and 4 of this guide.
2. Log in to the machine with a system user account.
3. Close all other applications.
4. On a shell command line, run the install.sh file located in the root directory. The installer displays the message to verify that the locale environment variables are set.
5. If the environment variables are not set, enter N to exit the installer and set them as required. If the environment variables are set, enter Y to continue.
6. Enter C for console mode installation.
7. Press Enter.
8. Enter 1 to install Informatica 9.0.1. Review the system requirements and pre-installation tasks. Verify that all installation requirements are met before you continue the installation.
9. Press Enter.
10. Enter the path and file name of the Informatica license key.
11. Enter the path for the installation directory or press Enter to use the default directory. The directory names in the path must not contain spaces or the following especial characters: @|* $ # ! % ( ) { } [ ] , ; '
12. Review the installation information, and press Enter to continue. The installer copies the Informatica files to the installation directory.
13. Enter 1 to create a domain. If you create a domain, the node on the current machine becomes a gateway node on the domain. The gateway node contains a Service Manager that manages all domain operations.
14. Select whether to set up a secure connection between the Service Manager and the Administrator tool. Press 1 to Enable HTTPS for Informatica Administrator.
15. Select the database to use for the domain configuration repository. Press 1 to select Oracle.
16. Enter the Database User ID and Password.
17. Select how you want to specify the database connection URL. Press 1 for JDBC URL.
18. Now enter the properties at the prompt.
a. Database Address: Enter HostName:PortNo
b. Database service name: Enter SID
c. Configure JDBC parameters: Select yes, enter the parameters or press enter to accept the default.
19. If the database contains a domain configuration repository for a previous domain, select 2 to overwrite the data or set up another database. Press Continue to overwrite.
20. Enter the following information at the prompt:
a. Domain name: Enter Domain Name
b. Domain host name: Enter Host Name
c. Node name: Press Enter for default Node Name
d. Domain port number: Press Enter for default port name
e. Domain user name: Enter username, this would be used for initial setup of Informatica
f. Domain user password: Enter Password
g. Confirm password: Enter Password again
21. Now it would ask whether to display the default ports for the domain and node components assigned by the installer. Press 1 for No.
22. Installation is now complete.
23. Go to the Informatica installation directory and write the below command to bring up the Informatica service.
infaservice.sh startup

[Informatica] Software Architecture illustrated

Informatica >> Beginners >> Informatica System Architecture

Informatica Software Architecture illustrated
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. (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)
•Report Metadata (Metadata Reporter)

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.

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.