Archive for the ‘Informatica Interview Questions’ Category
Data modeling is probably the most labor intensive and time
consuming part of the development process. Why bother especially if you
are pressed for time? A common response by practitioners who write on
the subject is that you should no more build a database without a model
than you should build a house without blueprints.
The goal of the data model is to make sure that the all data objects required by the database are completely and accurately represented. Because the data model uses easily understood notations and natural language , it can be reviewed and verified as correct by the end-users.
The data model is also detailed enough to be used by the database developers to use as a “blueprint” for building the physical database. The information contained in the data model will be used to define the relational tables, primary and foreign keys, stored procedures, and triggers. A poorly designed database will require more time in the long-term. Without careful planning you may create a database that omits data required to create critical reports, produces results that are incorrect or inconsistent, and is unable to accommodate changes in the user’s requirements.
The goal of the data model is to make sure that the all data objects required by the database are completely and accurately represented. Because the data model uses easily understood notations and natural language , it can be reviewed and verified as correct by the end-users.
The data model is also detailed enough to be used by the database developers to use as a “blueprint” for building the physical database. The information contained in the data model will be used to define the relational tables, primary and foreign keys, stored procedures, and triggers. A poorly designed database will require more time in the long-term. Without careful planning you may create a database that omits data required to create critical reports, produces results that are incorrect or inconsistent, and is unable to accommodate changes in the user’s requirements.
We can use variable port in the following transformations.
1. Expression Transformation
2. Aggregated transformation
3. Rank Transformation
1. Expression Transformation
2. Aggregated transformation
3. Rank Transformation
There is a property at session level “Collect Performance Data“, you can select that property. It gives you performance details for all the transformations.
By default, the Integration Service generates an ORDER BY clause
for a cached lookup. The ORDER BY clause contains all lookup ports. To
increase performance, you can suppress the default ORDER BY clause and
enter an override ORDER BY with fewer columns.
Note: If you use pushdown optimization, you cannot override the ORDER BY clause or suppress the generated ORDER BY clause with a comment notation.
The Integration Service always generates an ORDER BY clause, even if you enter one in the override. Place two dashes ‘–’ after the ORDER BY override to suppress the generated ORDER BY clause. For example, a Lookup transformation uses the following lookup condition:
ITEM_ID = IN_ITEM_ID
PRICE <= IN_PRICE
The Lookup transformation includes three lookup ports used in the mapping, ITEM_ID, ITEM_NAME, and PRICE. When you enter the ORDER BY clause, enter the columns in the same order as the ports in the lookup condition. You must also enclose all database reserved words in quotes. Enter the following lookup query in the lookup SQL override:
SELECT ITEMS_DIM.ITEM_NAME, ITEMS_DIM.PRICE, ITEMS_DIM.ITEM_ID FROM ITEMS_DIM ORDER BY ITEMS_DIM.ITEM_ID, ITEMS_DIM.PRICE –
To override the default ORDER BY clause for a relational lookup, complete the following steps:
1. Generate the lookup query in the Lookup transformation.
2. Enter an ORDER BY clause that contains the condition ports in the same order they appear in the Lookup condition.
3. Place two dashes ‘–’ as a comment notation after the ORDER BY clause to suppress the ORDER BY clause that the Integration Service generates.
If you override the lookup query with an ORDER BY clause without adding comment notation, the lookup fails.
Note: Sybase has a 16 column ORDER BY limitation. If the Lookup transformation has more than 16 lookup/output ports including the ports in the lookup condition, override the ORDER BY clause or use multiple Lookup transformations to query the lookup table.
Note: If you use pushdown optimization, you cannot override the ORDER BY clause or suppress the generated ORDER BY clause with a comment notation.
The Integration Service always generates an ORDER BY clause, even if you enter one in the override. Place two dashes ‘–’ after the ORDER BY override to suppress the generated ORDER BY clause. For example, a Lookup transformation uses the following lookup condition:
ITEM_ID = IN_ITEM_ID
PRICE <= IN_PRICE
The Lookup transformation includes three lookup ports used in the mapping, ITEM_ID, ITEM_NAME, and PRICE. When you enter the ORDER BY clause, enter the columns in the same order as the ports in the lookup condition. You must also enclose all database reserved words in quotes. Enter the following lookup query in the lookup SQL override:
SELECT ITEMS_DIM.ITEM_NAME, ITEMS_DIM.PRICE, ITEMS_DIM.ITEM_ID FROM ITEMS_DIM ORDER BY ITEMS_DIM.ITEM_ID, ITEMS_DIM.PRICE –
To override the default ORDER BY clause for a relational lookup, complete the following steps:
1. Generate the lookup query in the Lookup transformation.
2. Enter an ORDER BY clause that contains the condition ports in the same order they appear in the Lookup condition.
3. Place two dashes ‘–’ as a comment notation after the ORDER BY clause to suppress the ORDER BY clause that the Integration Service generates.
If you override the lookup query with an ORDER BY clause without adding comment notation, the lookup fails.
Note: Sybase has a 16 column ORDER BY limitation. If the Lookup transformation has more than 16 lookup/output ports including the ports in the lookup condition, override the ORDER BY clause or use multiple Lookup transformations to query the lookup table.
The lookup SQL override is similar to entering a custom query in a
Source Qualifier transformation. You can override the lookup query for a
relational lookup. You can enter the entire override, or you can
generate and edit the default SQL statement. When the Designer generates
the default SQL statement for the lookup SQL override, it includes the
lookup/output ports in the lookup condition and the lookup/return port.
Override the lookup query in the following circumstances:
1. Override the ORDER BY clause. Create the ORDER BY clause with fewer columns to increase performance. When you override the ORDER BY clause, you must suppress the generated ORDER BY clause with a comment notation.
Note: If you use pushdown optimization, you cannot override the ORDER BY clause or suppress the generated ORDER BY clause with a comment notation.
2. A lookup table name or column names contains a reserved word. If the table name or any column name in the lookup query contains a reserved word, you must ensure that all reserved words are enclosed in quotes.
3. Use parameters and variables. Use parameters and variables when you enter a lookup SQL override. Use any parameter or variable type that you can define in the parameter file. You can enter a parameter or variable within the SQL statement, or you can use a parameter or variable as the SQL query. For example, you can use a session parameter, $ParamMyLkpOverride, as the lookup SQL query, and set $ParamMyLkpOverride to the SQL statement in a parameter file.
The Designer cannot expand parameters and variables in the query override and does not validate it when you use a parameter or variable. The Integration Service expands the parameters and variables when you run the session.
4. A lookup column name contains a slash (/) character. When generating the default lookup query, the Designer and Integration Service replace any slash character (/) in the lookup column name with an underscore character. To query lookup column names containing the slash character, override the default lookup query, replace the underscore characters with the slash character, and enclose the column name in double quotes.
5. Add a WHERE clause. Use a lookup SQL override to add a WHERE clause to the default SQL statement. You might want to use the WHERE clause to reduce the number of rows included in the cache. When you add a WHERE clause to a Lookup transformation using a dynamic cache, use a Filter transformation before the Lookup transformation to pass rows into the dynamic cache that match the WHERE clause.
Note: The session fails if you include large object ports in a WHERE clause.
6. Other. Use a lookup SQL override if you want to query lookup data from multiple lookups or if you want to modify the data queried from the lookup table before the Integration Service caches the lookup rows. For example, use TO_CHAR to convert dates to strings.
Override the lookup query in the following circumstances:
1. Override the ORDER BY clause. Create the ORDER BY clause with fewer columns to increase performance. When you override the ORDER BY clause, you must suppress the generated ORDER BY clause with a comment notation.
Note: If you use pushdown optimization, you cannot override the ORDER BY clause or suppress the generated ORDER BY clause with a comment notation.
2. A lookup table name or column names contains a reserved word. If the table name or any column name in the lookup query contains a reserved word, you must ensure that all reserved words are enclosed in quotes.
3. Use parameters and variables. Use parameters and variables when you enter a lookup SQL override. Use any parameter or variable type that you can define in the parameter file. You can enter a parameter or variable within the SQL statement, or you can use a parameter or variable as the SQL query. For example, you can use a session parameter, $ParamMyLkpOverride, as the lookup SQL query, and set $ParamMyLkpOverride to the SQL statement in a parameter file.
The Designer cannot expand parameters and variables in the query override and does not validate it when you use a parameter or variable. The Integration Service expands the parameters and variables when you run the session.
4. A lookup column name contains a slash (/) character. When generating the default lookup query, the Designer and Integration Service replace any slash character (/) in the lookup column name with an underscore character. To query lookup column names containing the slash character, override the default lookup query, replace the underscore characters with the slash character, and enclose the column name in double quotes.
5. Add a WHERE clause. Use a lookup SQL override to add a WHERE clause to the default SQL statement. You might want to use the WHERE clause to reduce the number of rows included in the cache. When you add a WHERE clause to a Lookup transformation using a dynamic cache, use a Filter transformation before the Lookup transformation to pass rows into the dynamic cache that match the WHERE clause.
Note: The session fails if you include large object ports in a WHERE clause.
6. Other. Use a lookup SQL override if you want to query lookup data from multiple lookups or if you want to modify the data queried from the lookup table before the Integration Service caches the lookup rows. For example, use TO_CHAR to convert dates to strings.
You can alter or override the default query in the Source Qualifier
transformation by changing the default settings of the transformation
properties. Do not change the list of selected ports or the order in which they appear in the query. This list must match the connected transformation output ports.
When you edit transformation properties, the Source Qualifier transformation includes these settings in the default query. However, if you enter an SQL query, the Integration Service uses only the defined SQL statement. The SQL Query overrides the User-Defined Join, Source Filter, Number of Sorted Ports, and Select Distinct settings in the Source Qualifier transformation.
Note: When you override the default SQL query, you must enclose all database reserved words in quotes.
When you edit transformation properties, the Source Qualifier transformation includes these settings in the default query. However, if you enter an SQL query, the Integration Service uses only the defined SQL statement. The SQL Query overrides the User-Defined Join, Source Filter, Number of Sorted Ports, and Select Distinct settings in the Source Qualifier transformation.
Note: When you override the default SQL query, you must enclose all database reserved words in quotes.
1. Have all naming standards been used?
2. Have the mappings/workflows been documented? Including:
– Descriptions and text within the PowerCenter mappings and workflows;
– Restart instructions; and
– Special handling instructions.
3. Have the mappings/workflows been sufficiently tested? Have they run in Test with no errors at least twice, preferably longer?
4. Have DDL scripts moved to production? Is there additional objects/DDL to move to production for source or lookup tables?
5. Optional, but critical: Have the business users reviewed the test data and approved the results?
6. Has a folder comparison been done between the source and target repositories? Have all folder differences been explained as being necessary?
7. Have reasonable error checks been coded for in the error handling routines?
8. Has the default session configuration been used/altered for this folder? If there have been changes, list the changes.
9. Has the fail parent option been checked?
10. Have all test settings such as “Collect Performance Data” been disabled?
11. Is there sequence generators that need the current values adjusted? What should the current value be?
12. Have any reusable objects been used? Have they been documented?
13. Have post-session emails been set up and are the proper email addresses used? (Indicate whether a failure is critical or not.)
14. Have relational connections been reviewed and are they correct?
15. Have mapping and session parameters and variables been reviewed, values set?
16. Has a code review been completed? Check for compliance with standards? Check performance efficiency?
17. Have the session failure variables been reviewed?
2. Have the mappings/workflows been documented? Including:
– Descriptions and text within the PowerCenter mappings and workflows;
– Restart instructions; and
– Special handling instructions.
3. Have the mappings/workflows been sufficiently tested? Have they run in Test with no errors at least twice, preferably longer?
4. Have DDL scripts moved to production? Is there additional objects/DDL to move to production for source or lookup tables?
5. Optional, but critical: Have the business users reviewed the test data and approved the results?
6. Has a folder comparison been done between the source and target repositories? Have all folder differences been explained as being necessary?
7. Have reasonable error checks been coded for in the error handling routines?
8. Has the default session configuration been used/altered for this folder? If there have been changes, list the changes.
9. Has the fail parent option been checked?
10. Have all test settings such as “Collect Performance Data” been disabled?
11. Is there sequence generators that need the current values adjusted? What should the current value be?
12. Have any reusable objects been used? Have they been documented?
13. Have post-session emails been set up and are the proper email addresses used? (Indicate whether a failure is critical or not.)
14. Have relational connections been reviewed and are they correct?
15. Have mapping and session parameters and variables been reviewed, values set?
16. Has a code review been completed? Check for compliance with standards? Check performance efficiency?
17. Have the session failure variables been reviewed?
VSAM (Virtual Storage Access Method) is a file access method for an IBM mainframe operating system. VSAM organize records in indexed or sequential flat files.
- The integration service increments the generated key sequence number each time it process a source row. When the source row contains a multiple-occurring column or a multiple-occurring group of columns, the normalizer transformationreturns a row for each occurrence. Each row contains the same generated key value.
- The normalizer transformation has a generated column ID (GCID) port for each multiple-occurring column. The GCID is an index for the instance of the multiple-occurring data. For example, if a column occurs 3 times in a source record, the normalizer returns a value of 1,2 or 3 in the generated column ID.
When the integration service processes an unsorted joiner transformation, it reads all master rows before it reads the detail rows. To ensure it reads all master rows before the detail rows, the integration service blocks all the details source while it caches rows from the master source. As it blocks the detail source, the unsorted joiner is called a blocking transformation.
No comments:
Post a Comment