Datawarehouse Architect

CUME (PERSONAL_SALES)  PERSONAL_SALES      RETURN VALUE 40000                40000 80000                120000 40000                160000 60000                220000 MOVINGSUM (SALES,2) ROW_NO               SALES      RETURN VALUE 1                600               NULL 2                504   ...

Pipeline Partitioning a mapping in Informatica means to partition the pipeline inside the mapping. A pipeline will contain the source, transformations and targets. Mapping will be subdivided into many pipelines are called stage pipeline. After enabling partitioning in stage pipeline when we run the integration service it runs faster and we can expect more performance. The integration service will run the partition threads concurrently. To enable partition · Set the partition points in the mapping · Set the number of partitions · Set the partition types Partition points marks the boundaries that divide the pipeline into stages. By default Integration Service will keep partition at various transformations. Partition points mark the points in the pipeline where the Integration Service can redistribute data across partitions. When we give Partition points it actually creates various stage pipelines inside the mapping. Now we need to decide how many partitions we n...

Totally there are 10 SESSIONS 1. SESSION : FOR MAPPING EXECUTION 2. EMAIL : TO SEND EMAILS 3. COMMAND : TO EXECUTE OS COMMANDS 4. CONTROL : FAIL, STOP, ABORT 5. EVENT WAIT : FOR PRE_DEFINED OR POST_DEFINED EVENTS 6 EVENT RAISE : TO RAISE ACTIVE USER_DEFINED EVENT 7. DECISSION : CONDITION TO BE EVALUATED FOR CONTROLING FLOW OR PROCESS 8. TIMER : TO HALT THE PROCESS FOR SPECIFIC TIME 9. WORKLET TASK: REUSABLE TASK 10. ASSIGNEMENT: TO ASSIGN VALUES WORKLET OR WORK FLOW VARIABLES

Can’t compare lookup and joiner since each has its own functions. Sometimes joiner gives more performance and sometimes lookups. In case of Flat file, generally, sorted joiner is more effective than lookup, because sorted joiner uses join conditions and caches less rows. Lookup caches always whole file. If the file is not sorted, it can be comparable. In case of database, lookup can be effective if the database can return sorted data fast and the amount of data is small, because lookup can create whole cache in memory. If database responses slowly or big amount of data are processed, lookup cache initialization can be really slow .Then it can be better use sorted joiner, which throws data to output as reads them on input.

Using more than one session by linking it is called as batches. It is of two types 1. Sequential batches.(session runs one after another) 2. Concurrent batches.(session runs at same time) We can declare dependencies in between session by giving the command ‘ $PrevTaskStatus = SUCCESSFUL’ in sequential batches.

If we are using emp table as target in scd then the query is Select * from emp_t a where a.version = (Select max (b.version) from emp_t b where a.empno=b.empno)

Informatica primary uses load manager and data transformation manager  (dtm) to perform extracting, transformation and loading. Load manager reads  parameters and variables related to session, mapping and server and passes  the mapping parameters and variable information to the DTM.DTM uses this  information to perform the data movement from source to target