Batch,Real Time and CDC -Data Extraction Techniques

1. Batch - {On-Demand, User initiated,Loss of transactional Lineage,Can be scheduled}
2. Real Time-{Maintains transactional Lineage based on architechture,Implemented to meet tighter SLAs}
3. CDC-Change Data Capture-Continuous extraction.Transactrional Lineage is maintained,Delete data is also captured in form of soft deletes.

-For CDC implementation we need to capture CreatedDate and LastModifiedDate of records

Scrum Implementation : Live project experience

So we completed a 3 – Sprint DWH project for a FMCG US based company.

It was a roller coaster ride and we stretched every bit of our mental and physical skin to deliver.

Technical Streams: The project had the following technical streams

1.       Data Modeling – The data modelers started off with their part of analysis and prepared the logical data models of the Salesforce ERP source system.

a.       The analogous tables were physicalised in the Oracle relational dbse which was called as the first layer. The business key was being used as the primary key in this layer.

b.      The second layer was the staging layer where the dimensional model existed(ODS). Facts and dimensions were identified by the onsite teams and the same were incorporated in the logical data model and implemented in the physical data model in the 2^nd layer of the DWH.Here the Surrogate key was the primary key called as the SID or the source identifier.

c.       The presentation layer (BIDW)was the third and the final layer which had the warehouse identifier (WID) as the primary key , it also had the SID.The tables were a mirror image of their siblings in the ODS table.

2.       ETL – Informatica 9.6 on Linux was being used for this project.

3.       Reporting – Cognos 10.1 clients were used.

New Informatica Interview Questions

1.       Name some Informatica Application Services

·         Repository Service

·         Integration Service

·         Data Integration Service

·         Metadata repository service.

·         Analyst Service.

2.       What is a Service Manager?

·         The service manager is the main service of Informatica which is responsible for running of other application services. When we start the Informatica services on the node, we are actually starting the Service manager. Default port 6005.

3.       From where can we call SQL functions in Informatica.

·         Pre/Post session level SQL

·         Source Qualifier override.

4.       What is the minimum number of ports required to Install Informatica.

·         6000 to 6113.

5.       Name the environment variables required to be set at Informatica level.

·         TNS_ADMIN

·         ORACLE_HOME

·         ORACLE_BASE

·         LD_LIBRARY_PATH

·         NLS_LANG

6.       Can you implement High Availability Architecture without a grid?

·         Yes, by adding nodes as primary and backup nodes under the same domain.License should have HA property.

7.       How do you configure a WSDL connection at session level?

·         As an application connectionà WSDL consumer-àAuthentication type Basic.

8.       Should the client and server for Informatica , be of the same version?

·         Yes

9.       What is Target Load Plan?

·         Please study from the guide and understand that the load can be prioritized even if the pipes are originating from the same source.

10.   How to switch off/start Informatica services?

·         Informatica/9.5.1/tomcat/bin infaservice.sh startup/shutdown.

11.   Give an example of usage of shell scripts in pre-post session.

·         Used to FTP the flat file on a particular folder in the unix box from where informatica could read it.

·         Do a validation check, pre-post count check of the feed being processed.

Update Strategy with WSDL target

While importing the Web Service (consumer) as a target we need to specify that the WSDL will be for :

1. insert
2. delete- select delete multiple not used delete record
3. Update

and then use Update strategy with different targets for different kind of DML operations.

How to make an Oracle Relation Connection in Informatica

This was one of the most challenging parts.

Problem – You need to make an Oracle connection at Informatica level so that you may use the same for read/write.

Solution

1.       For Linux and Windows , you need to Install Oracle client (admin version) freely downloadable from the OTN.

a.       Be careful about the bit compatibility, the oracle client and Informatica server bit should be same i.e. 64 bit or 32 bit

2.       Once you have installed the oracle client in the Windows/Linux box along with the Informatica server, you need to make the following environment variable entries:

1. PATH=$PATH/home/oracle/app/oracle/product/11.2.0/client_1/bin
2. ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/client_1
3. ORACLE_BASE=/home/oracle/app/oracle
4. LD_LIBRARY_PATH=/home/oracle/app/oracle/product/11.2.0/client_1/lib
5. TNS_ADMIN=$ORACLE_HOME/network/admin

Preferably you may make these entries at Informatica Administrator>Repository Services>Process tab>Env variables level.

3.       Now to connect to oracle database from Informatica, we can either use ODBC connection or Oracle connection.

a.       Connecting using Oracle drivers

                                                               i.      Make the TNS entry in the tnsnames.ora file,assuming the connection string name=Conn_string

                                                             ii.      At the workflow manager level you need to Connections>Relational Connections>Make new Connection

1.       You will have option of Oracle or ODBC connection, make Oracle connection for TNS entry

2.       Type in the credentials and make the connection string as  Conn_string same as TNS entry.

b.      Connecting using ODBC drivers (Approach not applicable for Linux only for Windows)

                                                               i.      You need to go to> Start>Administrative Tools>ODBC Data Sources>System DSN

                                                             ii.      Make a new System DSN give the DSN Name as Conn1

                                                            iii.      At the workflow manager level you need to Connections>Relational Connections>Make new Connection

1.       You will have option of Oracle or ODBC connection, make ODBC Connection

2.       Name the Connection String as Conn1

                

PMWS_33016--Informatica Authorization error

I have often faced difficulty in configuring a WSDL connection in the workflow manager.Hence listing down the steps:

1. Import the WSDL source/target as Import>WSDL Consumer.-Designer
2. navigate through the options of the import.-Designer
3. Make an application connection in workflow manager as a Web Service consumer connection.
4.Make sure of setting the connection authentication mode as Basic and not PEM. 
5.Enter username and password, Domain need not be entered.

Restoring Informatica Code from existing repository database.

Assumptions

1.       An existing informatica setup with server existing on Linux Box A (internal i.p: a.b.c.d) and repository metadata existing on Relational Database Service (RDS- A)

2.       Following componenets exist on this environment

a.       Domain-Domain_abc

b.      Node-node_abc

c.       Repository Service-RS-A.

d.      Integration Service-IS-A.

3.       Linux Box A gets corrupted. The OS team provides a fresh Linux Box B with the same internal IP (a.b.c.d)

4.       This document will explain how to recover the Informatica code that was made using repository service-RS-A

Steps to be followed

1.       On Linux Box –B – Re-install informatica Server

·         Keep the repository database same

·         Make new Domain- Domain_def

·         Make new node- node_def

2.       Access the administrator console and login using admin credentials.

3.       Configure the Repository RS_def

   We get the following error

 We now try creating the Repository Service with the same name i.e. RS_abc

 Since the content already exists in the database we select the Content exist option as shown below

Repository Service RS_abc is created and is in disabled mode. 

Enable the repository using the enable button. RS_abc is up and running now.

You may now configure the domain and the repository. When you log into the repository you can see the old code.

1

User-Group Creation and Management in Informatica

Please refer the below matrix while making Informatica users/groups. The Matrix also has folder level permission details.

Development –

Repository Service			Admin	Developer	Monitor	Deployer	Operator	User
	Folder
		Create
		Copy
		Manage Versions	X			X
	Runtime Objects
		Create, Edit, and Delete	X	X		X
		Manage Versions	X	X		X
		Monitor	X	X		X	X	X
		Execute	X	X		X	X	X
		Manage Execution	X	X		X	X	X
	Global Objects
		Create Connections	X			X
		Manage Deployment Groups	X			X
		Execute Deployment Groups	X			X
		Create Labels	X			X
		Create Queries	X			X
	Tools
		Access Designer	X	X	X	X	X
		Access Repository Manager	X	X	X	X	X
		Access Workflow Manager	X	X	X	X	X
		Access Workflow Monitor	X	X	X	X	X
	Design Objects
		Create, Edit, and Delete	X	X
		Manage Versions	X	X
	Sources and Targets
		Create, Edit, and Delete	X	X
		Manage Versions	X	X
	Folder Access Development		RWX	RWX	R--	RWX	R-X
	Folder Access Validation		RWX	---	R--	RWX	R-X
	Folder Access Production		RWX	---	R--	RWX	R-X

Validation and Production –

Repository Service			Admin	Developer	Monitor	Deployer	Operator	User
	Folder
		Create
		Copy
		Manage Versions	X
	Runtime Objects
		Create, Edit, and Delete
		Manage Versions
		Monitor	X	X	X	X	X	X
		Execute	X	X	X	X	X	X
		Manage Execution	X	X	X	X	X	X
	Global Objects
		Create Connections	X			X
		Manage Deployment Groups	X			X
		Execute Deployment Groups	X			X
		Create Labels	X			X
		Create Queries	X			X
	Tools
		Access Designer	X	X	X	X	X
		Access Repository Manager	X	X	X	X	X
		Access Workflow Manager	X	X	X	X	X
		Access Workflow Monitor	X	X	X	X	X
	Design Objects
		Create, Edit, and Delete
		Manage Versions
	Sources and Targets
		Create, Edit, and Delete
		Manage Versions