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

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 ...

Sometimes we need to find the indexes present for a table or the table structure or db links present etc. To find these details we have some standard DB queries which gives us the required results. The following queries are applicable to only Oracle database. SELECT * FROM ALL_TABLES   The following query lists out all the tables present in the database. If you want search a particular table we can use it in the following way SELECT * FROM ALL_TABLES WHERE TABLE_NAME LIKE '%emp%' SELECT * FROM ALL_TAB_COLUMNS This query list out all the columns. If we want to know a particular column is present in which all tables we can frame the query in the following way SELECT * FROM ALL_TAB_COLUMNS WHERE COLUMN_NAME='ID'; SELECT * FROM ALL_DB_LINKS This query will return the list of db links present in the database SELECT * FROM ALL_INDEXES This query will list out all the indexes present in the database.If we want to find the indexes present for a particular t...

Its been some time since the last post, but now i am back with a pretty interesting article Performance tuning is a very vital aspect in any project. The faster and better interface is always appreciated and wins laurels When the file size/ table which act as our sources are huge in size and the throughput is very low then we can go for partitioning. In partitioning we can split the data into various threads so data will be processed faster  If the informatica we are using is a standard edition then partitioning feature will not be available so now what we can do is  increase DTM buffer size. It will be present in session properties under performance section. By default it will be in auto mode we can change it to say 220mb,1gb etc depending on size of the source data.This will increase the throughput If the lookup transformation is taking is long time than we can go for joiner transformation to perform the same operation. As joiner will be better in performance...

One of the primary reasons why TERADATA is considered such a valuable product in market because of the utilities that are provided by teradata database. They are five teradata utilities available They are a)BTEQ b)FAST EXPORT c)MLOAD d)FLOAD e)TPUMP Let me give an overview about these utilities a)BTEQ - It stands for basic teradata query. In BTEQ we can perform various DDL and DML operations i.e. create,insert,merger,update,delete.  It can be run in two modea i.e. interactive and batch mode.  In here we can use BT & ET functions these represent begin transaction and end transaction respectively.  We can write multiple sql statement with in these two and commit action will be performed upon successful execution of all statements  If any of the sql statements fails then the data will be roll-backed. b)FAST EXPORT - FastExport ,the name itself is spells to exports data from Teradata to a Flat file. But BTEQ also does the same thing.The main difference i...