IT Pandora (ETL,DB,UNIX,AWS,NodeJS,Github etc.)

Source Qualifier is Active Transformation Source Qualifier is connected transformation. It should be there in pipeline. This is default transformation which comes along with source instance when you drag and drop into mapping area.But there is some exceptional case with XML and COBOL sources. This is mandatory transformation to read data from sources and it converts the source data types to the informatica native data types. So, you should not alter the data types of the ports in the source qualifier transformation. If we are not using Push Down Optimization, then every port of source qualifier should have link from source instance. Otherwise, workflow will fail. If we are using Push Down Optimization, then at-least one port of source qualifier should have link from source instance. Otherwise, workflow will fail. Source Qualifier Properties: Below are the Source qualifier properties and there are enable when we are using tables as sources. In case of files as source, th...

Unlike the other databases Teradata and Oracle, We need to get the view definition in POSTGRE database from pg_views table. Query: SELECT definition FROM pg_views WHERE viewname='<View_name>'; The above query will give us the complete view definition. To see only columns and it's datatypes, we can use below: \d+  <view_name>;

If there is a requirement to load data into target tables with the same workflow by passing different values for parameters, we knew that we can change parameter file every time to pass new value to parameters. But this requires manual intervention. This manual changes will not be encouraged in production environment for any client. To avoid this manual changes to parameter files every time before you run for particular set, We can use "Configure Concurrent Execution" option from workflow properties. To do this, we need to create different parameter files as per the requirement. To enable concurrent execution, go to EDIT WORKFLOW. We can see the option "Configure Concurrent Execution". Enable this option.Once enabled, one button "Configure Concurrent Execution....." will be enabled. Click on this button and you will get another dialog box. Here, we need to pass parameter file details which we were created earlier. Thank you...And ...

It's been long time, since the last post. Let's get to know about mongo database. So mongo is one of the front runners in nosql databases. Nosql databases do not adhere to rules and principles to standard relational databases. So coming to Mongo it a document oriented database which is written in C++.   Mongo is an open source database. It is simple to install more of a plug and play type.   Once you are done with the mongo installation To start the mongo server in our local machine. Execute the command in command terminal   C:\MongoDB\bin\mongod.exe --config="C:\MongoDB\mongo.config"   To start the client in our local machine execute the following command in the command terminal   C:\MongoDB\bin\mongo   Upon executing we can see the following screen      By default there will be a test database created in our mongo database. So it will be connected to that particular database.   We can create our ...

For Aggregating data from a number of rows into a single row we have various functions in oracle some of them are listagg function, xmlagg and wm_concat function Consider the following example Dept No Emp Name 10 John 11 Neo 12 Anderson 12 Trinity 11 Morpheous 12 Smith Now we want the data in the following way aggregated based on Dept No Dept No Emp Name 10 John 11 Morpheous,Neo 12 Anderson,Smith,Trinity We can achieve this by using the LISTAGG function in the following way SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees FROM   emp GROUP BY deptno; Using XMLAGG function SELECT deptno,  RTRIM ( xmlagg (xmlelement (c, ename || ',') order by ename).extract ('//text()') , ',' ) AS...

We have already covered how a dynamic look up works the same can be found out from the following link http://dwbuddy.blogspot.com/2014/06/dynamic-look-up-in-informatica.html  So let's see how we can use it in a regular SCD type 2 mapping. The logic used in SCD type 2 is same as discussed in earlier blog i.e.  http://dwbuddy.blogspot.com/2015/01/scd-type-ii-implementation-in.html . The only difference being here is we have used dynamic look up instead of a static one. We go for dynamic look up when there are duplicate entries for a PK value and only the latest record present in the file should be termed as active. Let us consider the following sample data So  for EMP_ID 101 we have two records in the same day flow so the latest record i.e. with salary as 1500 should be termed as Active record. The mapping will look in the following way So in here we have a dynamic lookup couple of expression transformations,a transaction control transformation which is use...

Queries in informatica are useful in many ways. We can use it to identify mappings across repository in which a particular table is being used.We can use it to identify invalid objects in repository. We can use it to identify if any changes were made to objects on a given day.We even use them for deployment purpose this has been explained in earlier article. Please find the link below http://dwbuddy.blogspot.com/2014/01/labelling-and-creating-queries-in.html Suppose we know only table name and we want to know how it is populated and in which all mappings it is being used across informatica repository. We can achieve this by creating query in following way In repository manger go to tools there we will be having queries .Click new and give the parameters as mentioned in the screenshot Upon executing the query i.e. clicking the blue arrow icon all the mappings along with folder name which has table Employee being used will be displayed. Click save so that query can be used...

One of the most common used transformation in Informatica is Expression transformation. In Expression transformation we can perform various operations such as data conversions i.e to_date,to_char, string manipulation such as substr,instr etc. Now coming to one of the widely and prominent task which we perform using Expression transformation is looping a value. Expression transformation has three types of ports i.e. input,variable and output.Only output port values can be propagated to next transformations. So in order to pass values of input and variable ports to next level of transformation these must be assigned to output ports.The order of execution in Expression transformation is top to bottom and first input then variable and finally output ports are processed. let us consider the following scenario   The files should be generated with employee name as file name and that particular file should have the details of that respective employee only, if the employee has more t...

One of the most important concept  in Data ware housing. SCD Type II  means history of data should be saved.In here we can use status flags,dates and versioning to store the data. Consider the following example So during the first run on jan 1 2015 we got the data as A1B2 and 12345. And on jan 5 2015 we got the data as A1B2 and 56789. So as part of SCD type II we need to maintain history hence the latest record should be inserted and earlier record should be updated. In order to identify the latest record we have columns namely status,startdate,enddate The latest record will have status have ÁCT and End Date will be a high end date (12/31/9999).Job_id is the primary key In informatica for implementing SCD type II the mapping will look in the following way So in here we have transformation such as aggregator,lookup,expression,router and update Aggregator transformation is used to remove any duplicates coming from source. We create lookup on the ...