Solution for Informatica problems

============================================================== A Comparison of Oracle's DATE and TIMESTAMP Datatypes Oracle date and time data types, calculations around these data types, and just plain who to use them often plauge Oracle users more than they should. Here is an article I wrote a while back but still holds some good insight (I think) to using these data types. Hope you agree. If you want to store date and time information in Oracle, you really only have two different options for the column's datatype. Lets take a quick look at these two datatypes and what they offer. DATE datatype This is the datatype that we are all too familiar with when we think about representing date and time values. It has the ability to store the month, day, year, century, hours, minutes, and seconds. It is typically good for representing data for when something has happened or should happen in the future. The problem with the DATE datatype is its' granularity when trying to determi...

=========================================================================== Informatica Power Center performance – Concurrent Workflow Execution What is concurrent work flow? A concurrent workflow is a workflow that can run as multiple instances concurrently. What is workflow instance? A workflow instance is a representation of a workflow. How to configure concurrent workflow? 1) Allow concurrent workflows with the same instance name: Configure one workflow instance to run multiple times concurrently. Each instance has the same source, target, and variables parameters. Eg: Create a workflow that reads data from a message queue that determines the source data and targets. You can run the instance multiple times concurrently and pass different connection parameters to the workflow instances from the message queue. 2) Configure unique workflow instances to run concurrently: Define each workflow instance name and configure a workflow parameter file for the instance. You can defi...

=========================================================================== Informatica Performance Improvement Tips We often come across situations where Data Transformation Manager (DTM) takes more time to read from Source or when writing in to a Target. Following standards/guidelines can improve the overall performance. Use Source Qualifier if the Source tables reside in the same schema Make use of Source Qualifer  “Filter” Properties if the Source type is Relational. If the subsequent sessions are doing lookup on the same table, use persistent cache in the first session. Data remains in the Cache and available for the subsequent session for usage. Use flags as integer, as the integer comparison is faster than the string comparison. Use tables with lesser number of records as master table for joins. While reading from Flat files, define the appropriate data type instead of reading as String and converting. Have all Ports that a...

================================================================================= What is Pushdown Optimization and things to consider The process of pushing transformation logic to the source or target database by Informatica Integration service is known as Pushdown Optimization. When a session is configured to run for Pushdown Optimization, the Integration Service translates the transformation logic into SQL queries and sends the SQL queries to the database. The Source or Target Database executes the SQL queries to process the transformations. How does Pushdown Optimization ( PO ) Works? The Integration Service generates SQL statements when native database driver is used. In case of ODBC drivers, the Integration Service cannot detect the database type and generates ANSI SQL.  The Integration Service can usually push more transformation logic to a database if a native driver is used, instead of an ODBC driver. For any SQL Override, Integration service creates a view (PM_*) in the...

  SQL query select OPB_SUBJECT.SUBJ_NAME, OPB_MAPPING.MAPPING_NAME,OPB_SRC.source_name from opb_mapping, opb_subject, opb_src, opb_widget_inst where opb_subject.SUBJ_ID = opb_mapping.SUBJECT_ID and OPB_MAPPING.MAPPING_ID = OPB_WIDGET_INST.MAPPING_ID and OPB_WIDGET_Inst.WIDGET_ID = OPB_SRC.SRC_ID and OPB_widget_inst.widget_type=1;

Que. How to remove special characters like ## in the below ...   Can any one suggest...   Prod_Code   ---   #PC97##   #PC98##   #PC99##   #PC125#   #PC156#   ---   ---   ---   #PC767#   #PC766#   #PC921#   #PC1020   #PC1071   #PC1092   #PC1221   i want to remove that special characters....   i want to load in the target just   Prod_Code   ---   PC9   PC98   PC99   PC125   PC156 . Ans: In expression ,use the replacechar function and in that just replace # with null char. REPLACECHR Availability: Designer Workflow Manager Replaces characters in a string with a single character or no character. REPLACECHR searches the input string for the characters you specify and replaces all occurrences of all characters with the new character you specify. Syntax REPLACECHR( CaseFlag , InputString , OldCharSet ...

A delta load, by definition, is loading incremental changes to the data. When doing a delta load to a fact table, for example, you perform inserts only... appending the change data to the existing table. Top Delta checks can be done in a number of ways. Different logics can accomplish this. One way is to check if the record exists or not by doing a lookup on the keys. Then if the Keys don't exist then it should be inserted as new records and if the record exist then compare the Hash value of non key attributes of the table which are candidates for change. If the Hash values are different then they are updated records. (For Hash Values you can use MD5 function in Informatica) If you are keeping History (Full History) for the table then it adds a little more complexity in the sense that you have to update the old record and insert a new record for changed data. This can also be done with 2 separate tables with one as current version and another as History version.

Definition: Surrogate key is a substitution for the natural primary key in Data Warehousing. It is just a unique identifier or number for each row that can be used for the primary key to the table. The only requirement for a surrogate primary key is that it is unique for each row in the table. It is useful because the natural primary key can change and this makes updates more difficult. Surrogated keys are always integer or numeric.  Scenario overview and details To illustrate this example, we will use two made up sources of information to provide data about customers dimension. Each extract contains customer records with a business key (natural key) assigned to it. In order to isolate the data warehouse from source systems, we will introduce a technical surrogate key instead of re-using the source system's natural (business) key. A unique and common surrogate key is a one-field numeric key which is shorter, easier to maintain and understand, and independent from changes i...