Datawarehouse Architect

What is Incremental Aggregation?   Answer:   We can enable the session option, Incremental Aggregation for a session that includes an Aggregator Trans-formation. When the Integration Service performs incremental aggregation, it actually passes changed source data through the mapping and uses the historical cache data to perform aggregate calculations incrementally. Sorted input for aggregator transformation will improve performance of mapping. How-ever, if sorted input is used for nested aggregate expression or incremental aggregation, then the mapping may result in session failure. Explain why? Answer: Interview questions on aggregator transformation in informatica - Part - 4   In case of a nested aggregation, there are multiple levels of sorting associated as each aggregation function will require one sorting pass, and after the first level of aggregation, the sort order of the group by column may get jumbled up, so before the second level of aggregati...

Under what conditions selecting Sorted Input in aggregator will still not boost session per-formance?   Answer: Incremental Aggregation, session option is enabled. The aggregate expression contains nested aggregate functions. When session property, Treat Source rows as is set to data driven. Under what condition selecting Sorted Input in aggregator may fail the session? Answer: If the input data is not sorted correctly, the session will fail. Also if the input data is properly sorted, the session may fail if the sort order by ports and the group by ports of the aggregator are not in the same order. Suppose we do not group by on any ports of the aggregator what will be the output. Answer: If we do not use an input port in group-by neither in aggregate expression, the Integration Ser-vice will return only the last row value of the column for the input rows. For example, if we have 100 rows coming from source then aggregator will output only th...

How does Aggregator Transformation handle NULL values?   Answer: By default, the aggregator transformation treats null values as NULL in aggregate functions. But we can specify to treat null values in aggregate functions as NULL or zero.   What are the performance considerations when working with Aggregator Transformation?   Answer: Interview questions on aggregator transformation in informatica Filter the unnecessary data before aggregating it. Place a Filter transformation in the mapping be-fore the aggregator transformation to reduce unnecessary aggregation. Improve performance by connecting only the necessary input/output ports to subsequent transformations, thereby reducing the size of the data cache. Use Sorted input which reduces the amount of data cached and improves session performance. Aggregator performance improves dramatically if records are sorted before passing to the aggregator and “Sorted Input” option under aggregator properties...

1. What is an Aggregator Transformation?   Answer:   An aggregator is an Active, Connected transformation which performs aggregate calculations like AVG, COUNT, FIRST, LAST, MAX, MEDIAN, MIN, PERCENTILE, STDDEV, SUM and VARIANCE. 2. How an Expression Transformation differs from Aggregator Transformation?   Answer:   An Expression Transformation performs calculation on a row-by-row basis, whereas an Aggregator Trans-formation performs calculations on groups. 3. Does an Aggregator Transformation support only aggregate expressions?   Answer:   Apart from aggregate expressions, aggregator transformation supports non-aggregate expressions and con-ditional clauses.   4. Give one example for each of Conditional Aggregation, Non-Aggregate expression and Nested Aggregation.   Answer: Use conditional clauses in the aggregate expression to reduce the number of rows used in the ag-gregation. The conditional clause can be any clause that ...

1. OPB_SUBJECT - PowerCenter folders table This table stores the name of each PowerCenter repository folder. Usage: Join any of the repository tables that have SUBJECT_ID as column with that of SUBJ_ID in this table to know the folder name. 2. OPB_MAPPING - Mappings table This table stores the name and ID of each mapping and its corresponding folder. Usage: Join any of the repository tables that have MAPPING_ID as column with that of MAPPING_ID in this table to know the mapping name. 3. OPB_TASK - Tasks table like sessions, workflow etc This table stores the name and ID of each task like session, workflow and its corresponding folder. Usage: Join any of the repository tables that have TASK_ID as column with that of TASK_ID/SESSION_ID in this table to know the task name. Observe that the session and also workflow are stored as tasks in the repository. TASK_TYPE for session is 68 and that of the work...

List of connection using alter in env sql SELECT DISTINCT D . OBJECT_NAME , C . DB , C . USERNAME , C . ATTR_VALUE AS ENVIRONMENT_SQL FROM OPB_CNX D ,     (SELECT DISTINCT A . OBJECT_NAME CONN , B . OBJECT_ID ,                      A . CONNECT_STRING DB , A . USER_NAME USERNAME ,                      B . ATTR_VALUE FROM OPB_CNX A ,             (SELECT B . OBJECT_ID , B . OBJECT_SUBTYPE ,                     B . OBJECT_TYPE , B . ATTR_VALUE                     FROM OPB_CNX_ATTR B  ...