Datawarehouse Architect

You can alter or override the default query in the mapping by entering SQL override in the Properties settings in the Sources qualifier. You can enter any SQL statement supported by the source database. Overriding the SQL Query You can override the SQL query for a relational source. To override the default query for a relational source: 1. In the Workflow Manager, open the session properties. 2. Click the Mapping tab and open the Transformations view. 3. Click the Sources node and open the Properties settings. 4. Click the Open button in the SQL Query field to open the SQL Editor. 5. Enter the SQL override. 6. Click OK to return to the session properties.

By default, the Integration Service updates target tables based on key values. However, you can override the default UPDATE statement for each target in a mapping. You might want to update the target based on non-key columns. When the Integration Service executes SQL against a source, target, or lookup database, it searches the reserved words file stored in the Integration Service installation directory. It encloses matching reserved words in quotes. If you use target update override, you must manually put all reserved words in quotes. For a mapping without an Update Strategy transformation or a Custom transformation with the update strategy property enabled, configure the session to mark source rows as update. The Target Update option only affects source rows marked as update. The Integration Service processes all rows marked as insert, delete, or reject normally. When you configure the session, mark source rows as data-driven. The Target Update Override only affects source rows mar

If the lookup table is on the same database as the source table in your mapping and caching is not feasible, join the tables in the source database rather than using a Lookup transformation. If you use a Lookup transformation, perform the following tasks to increase performance: · Use the optimal database driver. · Cache lookup tables. · Optimize the lookup condition. (<,>,<=,>=,!=) · Filter lookup rows. · Index the lookup table. · Optimize multiple lookups. Using Optimal Database Drivers The Integration Service can connect to a lookup table using a native database driver or an ODBC driver. Native database drivers provide better session performance than ODBC drivers. Caching Lookup Tables If a mapping contains Lookup transformations, you might want to enable lookup caching. When you enable caching, the Integration Service caches the lookup table and queries the lookup cache during the session. When this option is not enabl

19. Pmcmd command usage in Informatica. Pmcmd command in Informatica is used to run a workflow in command prompt. 18. How to load rows into fact table in data warehouse A fact table is centralized table in data modeling which has foreign keys and measures. Fact table is surrounded by multiple dimension table.so we have to get primary keys of all dimension tables and the measures to load into fact.   20. Difference between stop and abort in Informatica Stop- will stop the task Abort – will force stop if the task does not stop by stop process.

The types of pushdown optimization: Source-side pushdown optimization. The Integration Service pushes as much transformation logic as possible to the source database. Target-side pushdown optimization . The Integration Service pushes as much transformation logic as possible to the target database. Full pushdown optimization. The Integration Service attempts to push all transformation logic to the target database. If the Integration Service cannot push all transformation logic to the database, it performs both source-side and target-side pushdown optimization. Running Source-Side Pushdown Optimization Sessions When you run a session configured for source-side pushdown optimization, the Integration Service analyzes the mapping from the source to the target or until it reaches a downstream transformation it cannot push to the source database. The Integration Service generates and executes a SELECT statement based on the transformation logic for each transformation it can push to t

Depending on the database workload, you might want to use source-side, target-side, or full pushdown optimization at different times. For example, use source-side or target-side pushdown optimization during the peak hours of the day, but use full pushdown optimization from midnight until 2 a.m. when database activity is low. To use different pushdown optimization configurations at different times, use the $$PushdownConfig mapping parameter. The parameter lets you run a session using the different types of pushdown optimization. The settings in the $$PushdownConfig parameter override the pushdown optimization settings in the session properties. Complete the following steps to configure the mapping parameter: 1.     Create $$PushdownConfig in the Mapping Designer. 2.     When you add the $$PushdownConfig mapping parameter in the Mapping Designer, use the following values: Field Value Name $$PushdownConfig

User-defined functions are extensions to PowerCenter transformation language. It allows you to create complex expressions using the built-in functions and operators and reuse them in PowerCenter mappings. User-defined functions are two types. Public : Callable from any user-defined function, transformation expression, link condition expression, or task expression. Private : Callable from another user-defined function. Create a private function when you want the function to be part of a more complex function. The simple function may not be usable independently of the complex function. Creating User Defined Functions You can create a user defined function from Informatica PowerCenter Designer tool. You can invoke the User-Defined Function dialog box from main menu Tools > User-Defined Functions > New. If u want a user defined function to convert the phone number into (XXX) XXX-XXXX format. We can define the function here to convert PHONE_NUMBER into required string formatted

For passing alternative rows in to two targets the flow of the mapping is SQ->sequence generator ->Router->TGT1,TGT2 Router filter condition is ‘(mod (sno, 2) =0)’ give u the even number rows pass it to one target (TGT1) and the default port of the router gives u odd number rows pass it to TGT2.

SQ->SG->AGG->ROUTER->TGT AGG-> MAX(SNO) ROUTER->MAX(SNO)-N The default port in router will give u the last n records.

Deployment group is a global object which consists of objects from one or more folders. Deployment group is used to copy objects from one or more folders to another folder or repository. You can create, edit, or delete deployment groups. You can copy a deployment group and the objects in the deployment group to a target repository. Deployment Group Tasks: You can do the following tasks when working with deployment groups: · Create deployment group: You can create a global object for deploying objects from one or more folders. · Edit deployment group: You can modify a deployment group. You can change the static deployment group to dynamic deployment group and vice versa. · Configure privileges and permissions: Configure permissions on a deployment group and the privilege to copy a deployment group. · View the objects in a static or dynamic deployment group: You can preview the objects that the Repository Service will deploy. · Add or remove

          We take for eg: Source Sid code value 1 A ABC 1 B BCD 1 C CCD 2 A SDC 2 C ASD 3 D DFG                         Target Sid code A code B code C code D 1 abc bcd ccd <null> 2 sdc <null> asd <null> 3 <null> <null> <null> dfg                         The flow of mapping is Sq->Agg->Tgt1, tg2, tgt3… Use Aggregator Create Four Ports each having MAX (DECODE (CODE,'A', VALUE)), MAX (DECODE (CODE,'B', VALUE)), MAX (DECODE (CODE,'A', VALUE)), MAX (DECODE (CODE,'D', VALUE)) Group it by SID   Connect thos