Informatica Preformance Tuning

1. Reduce the number of transformations. There is always overhead involved in moving data between transformations.

2. Consider more shared memory for large number of transformations. Session shared memory between 12MB and 40MB should suffice.

3. Calculate once, use many times.

• Avoid calculating or testing the same value over and over.

• Calculate it once in an expression, and set a True/False flag.

• Within an expression, use variable ports to calculate a value than can be used multiple times within that transformation.

• Delete unnecessary links between transformations to minimize the amount of data moved, particularly in the Source Qualifier.

• This is also helpful for maintenance. If a transformation needs to be reconnected, it is best to only have necessary ports set as input and output to reconnect.

• In lookup transformations, change unused ports to be neither input nor output. This makes the transformations cleaner looking. It also makes the generated SQL override as small as possible, which cuts down on the amount of cache necessary and thereby improves performance.

• The engine automatically converts compatible types.

• Sometimes data conversion is excessive. Data types are automatically converted when types are different between connected ports. Minimize data type changes between transformations by planning data flow prior to developing the mapping.

• Plan for reusable transformations upfront.

• Use variables. Use both mapping variables as well as ports that are variables. Variable ports are especially beneficial when they can be used to calculate a complex expression or perform a disconnected lookup call only once instead of multiple times

• Use mapplets to encapsulate multiple reusable transformations.

• Use mapplets to leverage the work of critical developers and minimize mistakes when performing similar functions.

• Reduce the number of non-essential records that are passed through the entire mapping.

• Use active transformations that reduce the number of records as early in the mapping as possible (i.e., placing filters, aggregators as close to source as possible).

• Select appropriate driving/master table while using joins. The table with the lesser number of rows should be the driving/master table for a faster join.

• Redesign mappings to utilize one Source Qualifier to populate multiple targets. This way the server reads this source only once. If you have different Source Qualifiers for the same source (e.g., one for delete and one for update/insert), the server reads the source for each Source Qualifier.

• Remove or reduce field-level stored procedures.

• If you use field-level stored procedures, the PowerCenter server has to make a call to that stored procedure for every row, slowing performance.

4. Only connect what is used.

5. Watch the data types.

6. Facilitate reuse.

7. Only manipulate data that needs to be moved and transformed.

8. Utilize single-pass reads.

9. Sort the input data before passing to Joiner and Aggregate transformation.

10. In Lookup using customize query instead of default query. (Use '--' to overwrite lookup default order by clause).

11. Avoid using un-neccessary columns/port in sql query.

12. Filter un-neccessary data as closer to the source qualifier. (In case of Relational database include filter condition to the sql query).

13. In Joiner consider lesser value of data as Master Table.

14. In-case of mapping partition place aggregate transformation before the partition point.

15. Use Router instead of having multiple Filter transformations.

How to Convert Row to Column in informatica?

(Source)
BRAND_ID,PRODUCT,PRICE
100,Cellphone,10000
100,Television,15000
100,Fridge,9000
101,Cellphone,8000
101,Television,12000
102,Cellphone,9000

(Target)
BRAND_ID, PRODUCT, TOTAL_PRICE
100,Cellphone,Television,Fridge,34000
101,Cellphone,Television,20000
102,Cellphone,9000

Step 1:
Drag and Drop your source and target to mapping designer.
Step 2:
Sorter
Sort the data based upon BRAND_ID
Step 3:
Expression
Create Eleven ports
BRAND_COMPARE (Variable Port) = IIF(OLD_BRAND=BRAND_ID, 1, 0)

OLD_BRAND (Variable Port) = BRAND_ID

PRODUCT_LIST (Variable Port) = IIF(BRAND_COMPARE=1, O_PRODUCT_LIST||','||PRODUCT, PRODUCT)

O_PRODUCT_LIST (Variable Port) = PRODUCT_LIST

ADD_PRICE (Variable Port) = IIF(BRAND_COMPARE=1, (O_PRICE_ADD + PRICE), PRICE)

O_PRICE_ADD (Variable Port) = ADD_PRICE
NEW_ID (Variable Port) = OLD_ID+1
OLD_ID (Variable Port) = NEW_ID
ID (Output Port) = NEW_ID
OUT_PRODUCT_LIST (Output Port) = PRODUCT_LIST
OUT_PRICE (Output Port) = ADD_PRICE
Step 4:
Rank
Create Rank Transformation and drag and drop BRAND_ID, ID, OUT_PRODUCT_LIST, OUT_PRICE to it from expression.
Set properties in Ports Tab.
Select ID as Rank Port (R) should be checked.
Select BRAND_ID as Group
Set Top/Bottom-->TOP in Properties Tab.
Step 5a
Connect BRAND_ID, OUT_PRODUCT_LIST, OUT_PRICE column to target.

How the schedule or unschedule workflow using cron job in unix?

Setting the job execution time

With at, you must specify a time the job should execute. The format you use to indicate the time is very flexible and may consist of the following:

• Time: Enter a one- or two-digit number (0-23) to indicate the start of an hour on a 24-hour clock (e.g., 13 is 13:00 or 1:00pm). To schedule the job to occur at a time other than the start of the hour, add minutes (00-59), optionally separated from the hour by a colon (e.g., 1334 or 13:34). You may follow the number with an am or pm to indicate the specific time on a 12-hour clock (e.g., 1:34pm or 0134pm). You may also use the words now, noon, andmidnight to indicate the current time. If the time you indicate is later than the current time and you haven't specified a date, the job will execute the same day; otherwise, it will execute the next day.
  
• Date: You can schedule a job to execute on a specific date. You can use keywords, such as today, tomorrow, or one of the days of the week. The job will execute at the soonest possible date that meets the requirements. You also may enter a fully qualified date, such as November 9, 2010 . The year is optional, and you can use an abbreviation for the month. As long as the date is unambiguous, your job request will probably succeed.
  
• Increment: You also can specify the execution time by indicating how far in the future it should be, relative to the current time. To do this, enter a plus sign ( + ), followed by a number and then one of the following keywords:
  
  
  • minutes
  • hours
  • days
  • months
  • years
  For example, if the current time is 12:00pm, the increment of +2 weeks would set the execution time at noon on a day two weeks hence.

These elements can be combined, for example:

• To schedule a job for the following Tuesday at a time two hours later than the current time, use: at tuesday +2 hours
• To set the execution time for 9:00 in the morning on the second day of next February, use: at 9am February 2
• To set the execution time for 1:34pm on a date exactly three months from when you issue the at command, use: at 1334 +3 months

Informatica Interview Questions set-8

1.what is a commit interval and explain the types?

A commit interval is the interval at which power center server commits data to targets during a session. The commit interval the number of rows you want to use as a basis for the commit point.

Target Based commit: The power center server 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 and the commit interval.

2.Ddifference between connected and unconnected lookup?

Connected lookup	Unconnected lookup
Receives input values directly from the pipe line.	Receives input values from the result of a clkp expression in a another transformation.
U can use a dynamic or static Cache	U can use a static cache
Cache includes all lokkup columns used in the mapping(that is lookup table columns included in the lookup condition and lookup table 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 informatica server returns the default value for all output ports.If u configure dynamic caching the informatica server inserts rows into the cache.	If there is no matching for the lookup  condition the informatica server returns NULL
Pass multiple output values to another transformatnion.Link lookup/output ports to another transformation	Pass one output value to another transformation.The lookup/output/return port passes the same value to the ---------------------------------------------------------
Supports user-defined default values.	Does not support user-defined default values.

6 3.explain index cache and data cache?

 The informatica server stores conditions values in the index cache and output values in the data cache.

4.explain a work flow process?

 The power center server uses both process memory and system shared memory to perform these tasks.

Load manager process:  stores and locks the workflow tasks and start the DTM run the sessions.

Data Transformation Process DTM: Perform session validations,create threads to initialize the session,read,write and transform data, and handle pre and post session operations.

     The default memory allocation is 12,000,000 bytes.

5.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 you configures the session to use a binary 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.

6.can you 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 have crated mapping parameters or variables.

7.Difference between the source filter and filter?

 Source filter is filtering the data only relational sources. Where as filter transformation filter the data any type of source.

8.What is data driven?

 The information 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.

9.Different Between OLTP and OLAP

	OLTP	OLAP
1	Application Oriented (e.g., purchase order it is functionality of an application)	Subject Oriented (subject in the sense customer, product, item, time)
2	Used to run business	Used to analyze business
3	Detailed data	Summarized data
4	Repetitive access	Ad-hoc access
5	Few Records accessed at a time (tens), simple query	Large volumes accessed at a time(millions), complex query
6	Small database	Large Database
7	Current data	Historical data
8	Clerical User	Knowledge User
9	Row by Row Loading	Bulk Loading
10	Time invariant	Time variant
11	Normalized data	De-normalized data
12	E – R schema	Star schema

10.What are the types of datawarehousing?

EDW (Enterprise datawarehousing)

ü It provides a central database for decision support throughout the enterprise

ü   It is a collection of DATAMARTS

DATAMART

ü   It is a subset of Datawarehousing

ü   It is a subject oriented database which supports the needs of individuals depts. in an organizations

ü   It is called high performance query structure

ü   It supports particular line of business like sales, marketing etc..

ODS (Operational data store)

ü   It is defined as an integrated view of operational database designed to support operational monitoring

ü   It is a collection of operational data sources designed to support Transaction processing

ü   Data is refreshed near real-time and used for business activity

ü   It is an intermediate between the OLTP and OLAP which helps to create an instance reports

11.Data Modeling Types:

ü   Conceptual Data Modeling

ü   Logical Data Modeling

ü   Physical Data Modeling

ü   Dimensional Data Modeling

1. Conceptual Data Modeling

ü   Conceptual data model includes all major entities and relationships and does not contain much detailed level of information about attributes and is often used in the INITIAL PLANNING PHASE

ü   Conceptual data model is created by gathering business requirements from various sources like business documents, discussion with functional teams, business analysts, smart management experts and end users who do the reporting on the database. Data modelers create conceptual data model and forward that model to functional team for their review.

ü   Conceptual data modeling gives an idea to the functional and technical team about how business requirements would be projected in the logical data model.

2. Logical Data Modeling

ü   This is the actual implementation and extension of a conceptual data model. Logical data model includes all required entities, attributes, key groups, and relationships that represent business information and define business rules.

3. Physical Data Modeling

ü   Physical data model includes all required tables, columns, relationships, database properties for the physical implementation of databases. Database performance, indexing strategy, physical storage and demoralization are important parameters of a physical model.

Logical vs. Physical Data Modeling

Logical Data Model	Physical Data Model
Represents business information and defines business rules	Represents the physical implementation of the model in a database.
Entity	Table
Attribute	Column
Primary Key	Primary Key Constraint
Alternate Key	Unique Constraint or Unique Index
Inversion Key Entry	Non Unique Index
Rule	Check Constraint, Default Value
Relationship	Foreign Key
Definition	Comment

Dimensional Data Modeling

ü   Dimension model consists of fact and dimension tables

ü   It is an approach to develop the schema DB designs

Types of Dimensional modeling

ü   Star schema

ü   Snow flake schema

ü   Star flake schema (or) Hybrid schema

ü   Multi star schema

12.what are Important aspects of Star Schema & Snow Flake Schema ?

ü   In a star schema every dimension will have a primary key.

ü   In a star schema, a dimension table will not have any parent table.

ü   Whereas in a snow flake schema, a dimension table will have one or more parent tables.

ü   Hierarchies for the dimensions are stored in the dimensional table itself in star schema.

ü   Whereas hierarchies are broken into separate tables in snow flake schema. These hierarchies help to drill down the data from topmost hierarchies to the lowermost hierarchies.

13.what are the Types of Facts Tables?

• Additive - Measures that can be summed up across all dimensions.
• Ex: Sales Revenue
• Semi Additive - Measures that can be summed up across few dimensions and not with others
• Ex: Current Balance
• Non Additive - Measures that cannot be summed up across any of the dimensions.
• Ex: Student attendance

14.WHY NEED STAGING AREA FOR DWH?

ü   Staging area needs to clean operational data before loading into data warehouse.

ü   Cleaning in the sense your merging data which comes from different source.

ü   It’s the area where most of the ETL is done

Data Cleansing

ü   It is used  to remove duplications

ü   It is used to correct wrong email addresses

ü   It is used to identify missing data

ü   It used to convert the data types

ü   It is used to capitalize name & addresses.

                                 Informatica Architecture

The Power Center domain

            It is a primary unit of the Administration.

            Can have single and multiple domains.

            It is a collection of nodes and services.

Nodes

   A node is the logical representation of a machine in a domain

One node in the domain acts as a gateway node to receive service requests from clients and route them to the appropriate service and node

Integration Service:

Integration Service does all the real job. It extracts data from sources, processes it as   per the business logic and loads data to targets.

Repository Service:

Repository Service is used to fetch the data from the repository and sends it back to the     requesting components (mostly client tools and integration service)

Power Center Repository:

Repository is nothing but a relational database which stores all the metadata created in  Power Center.

Power Center Client Tools:

 The Power Center Client consists of multiple tools.

Power Center Administration Console:

This is simply a web-based administration tool you can use to administer the Power Center installation.

Thanks

Ur's Hari

If you like this post, please share it by clicking on g+1 Button. 

what is Informatica Project Architecture or what is your project Architecture ?

SQL Interview Questions Set-1

1)      FIRST AND LAST RECORD FROM EMP TABLE?

SELECT * FROM EMP WHERE ROWNUM=1 UNION

SELECT * FROM EMP WHERE ROWID=(SELECT MAX(ROWID) FROM EMP);

2)      DISPLAY Nth RECORD FROM EMP TABLE?

SELECT * FROM (SELECT ROWNUM AS RNO,EMP.* FROM EMP) WHERE RNO=&N;

3)      DISPLAY 5^th AND 8^th  RECORD’s FROM EMP TABLE;

SELECT * FROM (SELECT ROWNUM AS RNO,EMP.* FROM EMP) WHERE RNO IN(5,9) ;

4)      DISPLAY ALTERNATIVE ROW’s FROM EMP TABLE?(ODD NUMBER ROWS)

SELECT * FROM (SELECT ROWNUM AS RNO,EMP.* FROM EMP) WHERE MOD(RNO,2)=1;

5)      DISPLAY ALTERNATIVE ROW’s FROM EMP TABLE?(EVEN NUMBER ROWS)

SELECT * FROM (SELECT ROWNUM AS RNO, EMP.* FROM EMP) WHERE MOD (RNO, 2) =1;

6)      DISPLAY LAST RECORD FROM EMP TABLE?

SELECT * FROM EMP WHERE ROWID=(SELECT MAX(ROWID) FROM EMP);

7)      DISPLAY LAST 3 RECORD’s FROM EMP TABLE?

SELECT * FROM (SELECT * FROM EMP ORDER BY ROWID DESC) WHERE ROWNUM<=3;

8)      DISPLAY FIRST 3 RECORD’s AND LAST 3 RECORD’s  FROM EMP TABLE?

SELECT * FROM EMP WHERE ROWNUM<=3 UNION

SELECT * FROM (SELECT * FROM EMP ORDER BY ROWID DESC) WHERE ROWNUM<=3;

9)      DISPLAY 5^th MAXIMUM SALARY OF EMP TABLE?

SELECT * FROM (SELECT DENSE_RANK() OVER (ORDER BY SAL DESC) AS RNK,

EMP.* FROM EMP) WHERE RNK=5;

10)  DISPLAY MAXIMUM SALARY OF EMP TABLE USING JOIN’s?

SELECT DISTINCT (A.SAL) FROM EMP A WHERE &N=(SELECT COUNT (DISTINCT (B.SAL)) FROM EMP B

WHERE A.SAL<=B.SAL);
11)  DISPLAY SALES DEPT AVG(SAL) FROM EMP TABLE?

SELECT AVG(SAL) FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM  DEPT WHERE DNAME='SALES');

12)  DEPT WISE MAXIMUM SALARY OF EMP TABLE?

SELECT * FROM EMP WHERE (DEPTNO,SAL) IN (SELECT DEPTNO,MAX(SAL) FROM EMP GROUP BY DEPTNO);

                                    (OR)

SELECT * FROM EMP  E WHERE SAL=(SELECT MAX(SAL) FROM EMP WHERE DEPTNO=E.DEPTNO);

13)  DISPLAY  AVG(SAL) FROM EMP TABLE?
SELECT EMPNO,ENAME,SAL,(SELECT AVG(SAL) FROM EMP) AVGSAL FROM EMP;
14)  DISPLAY TOP 3 SALARIES FROM EMP TABLE?
SELECT * FROM EMP WHERE 3> (SELECT COUNT(DISTINCT SAL) FROM EMP E WHERE EMP.SAL<E.SAL);
15)  DISPLAY TOP 3 SALARY FROM EMP TABLE?
SELECT * FROM EMP E WHERE 2=(SELECT COUNT(DISTINCT SAL) FROM EMP WHERE SAL>E.SAL);
16)  DISPLAY DUPLICATE  RECORD’s FROM EMPTABLE?
SELECT * FROM EMP  WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM EMP GROUP BY ENAME);
17)  DISPLAY DISTINCT RECORD’s FROM EMP TABLE?
SELECT * FROM EMP  WHERE ROWID IN (SELECT MAX(ROWID) FROM EMP GROUP BY ENAME);
18)  DISPLAY EMPLOYEE WITH CORRESPONDING MANAGER’s?
SELECT A.ENAME EMPLOYEE ,B.ENAME MANAGER FROM EMP A INNER JOIN EMP B ON(A.EMPNO=B.MGR);
19)  DISPLAY BOTTOM   N NUMBER OF RECORD’s IN EMP TABLE?
SELECT * FROM EMP MINUS SELECT * FROM EMP WHERE ROWNUM<=(SELECT COUNT(*) -&N FROM EMP);
20)  DISPLAY TOP 10 SALARIES OF EMP TABLE?
SELECT * FROM (SELECT * FROM EMP ORDER BY SAL DESC) WHERE ROWNUM<=10;
21)  DISPLAY DEPT WISE  AVG(SAL) FROM EMP TABLE?

SQL> SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO;

    DEPTNO   AVG(SAL)

---------- ----------

        10 24233.3333

        20 2647.22222

        30       1900
22)  ELIMINATE THE DUPLICATE RECORD’s WITH OUT USING DISTINCT?

DELETE FROM EMP WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM EMP GROUP BY ENAME);

23)   DISPLAY 5^th  MAXIMUM SALRY OF EMPTABLE?

SELECT  * FROM(SELECT DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS RNK, EMP.* FROM EMP ) WHERE RNK=5;

24)  WRITE A QUERY TO FIND THE HIGHEST SALARY EARNED BY AN EMPLOYEE IN EACH DEPARTMENT AND ALSO THE NUMBER OF EMPLOYEES WHO EARN THE HIGHEST SALARY?

SELECT DEPTNO,

 MAX(SAL) HIGHEST_SALARY,

 COUNT(1) KEEP(DENSE_RANK LAST ORDER BY SAL) CNT_HIGH_SAL

FROM EMP

GROUP BY DEPTNO;

                                                   (OR)

SELECT DEPARTMENT_ID,

 MAX(SALARY) HIGHEST_SALARY,

 COUNT(1) KEEP(DENSE_RANK LAST ORDER BY SALARY) CNT_HIGH_SAL

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID;

25)   WRITE A QUERY TO GET THE TOP 2 EMPLOYEES WHO ARE EARNING THE HIGHEST SALARY IN EACH DEPARTMENT?

SELECT DEPTNO, EMPNO, SAL FROM

 (

SELECT DEPTNO, EMPNO, SAL,

ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC ) R

FROM EMP

) A WHERE R <= 2;

26)   WRITE A QUERY TO FIND THE EMPLOYEES WHO ARE EARNING MORE THAN THE AVERAGE SALARY IN THEIR DEPARTMENT? 

SELECT EMPNO, SAL FROM EMP E_O WHERE SAL >

(SELECT AVG(SAL) FROM EMP E_I WHERE E_I.DEPTNO = E_O.DEPTNO ); 

Thanks

Ur's Hari

If you like this post, please share it by clicking on g+1 Button.