-
Find out duplicate records using aggregator
How to find out duplicate records using aggregator. Pls explain with example. What is the need for placing joiner after aggregator. I just tried like the following:Count the records and group by all the columns. if the count=1 then it will be a valid record. what to do if the count >2.means file contains duplicate records.Why we need to use joiner after aggregator?
VISHNU VARDHAN
- Nov 17th, 2015
Use HAVING Condition in SELECT clause, You can write code like below to test, if it is greater than one then it is duplicate otherwise Unique.
Codepriyank_divya
- Aug 7th, 2012
Hi, Its similar to the SQL query, SELECT * FROM ,,.... FROM TABLE_NAME GROUP BY ,,..... HAVING COUNT(*)=1 Similarly in Informatica Aggregator transformation, select group by for all the columns and...
-
Difference between stop & abort
What is the difference between STOP and ABORT options in Workflow Monitor?
-
Flat File as Source in Informatica
How can we receive source as flat file in informatica, from where we get this flat file?
-
What are 2 modes of data movement in Informatica Server?
The data movement mode depends on whether Informatica Server should process single byte or multi-byte character data. This mode selection can affect the enforcement of code page relationships and code page validation in the Informatica Client and Server.a) Unicode - IS allows 2 bytes for each character and uses additional byte for each non-ascii character (such as Japanese characters)b) ASCII - IS...
Read Best Answer
Editorial / Best Answer
Answered by: purushotham
- Aug 9th, 2005
the two types of modes are:
1) Normal Mode in which for every record a separate DML stmt will be prepared and executed
Bulk Mode in which for multiple records DML stmt will be preapred and executed thus improves performance.amolrenose
- Sep 18th, 2011
Data Movement Modes
1. ASCII mode :- Single byte of data is processed.
2. UNICODE mode :- Two bytes of data is processed.
Data Loading Modes
1. Normal Mode :- Commit is executed after 10000 records (default).
2. Bulk Mode :- Commit is executed after all records have been loaded. -
Explain the informatica Architecture in detail
Read Best Answer
Editorial / Best Answer
Rajsuni- Member Since Apr-2011 | Apr 1st, 2011
Informatica Architecture :
client tools-
Repositoy Manager-> Pc Designer-> WFManager-> WFMonitor
| | | |
(creat,mdify,del-folders, (src&Tar def, (create tasks (display
privilages& access RS) mapings,maPlets)connect to work flow) reult/output)
---------------------
Powercenter tools--
PC service <--------------------->Repository Database
|
|
<-------> PC Server<---->
here: Pc-PowerCenter.
venkat m
- Sep 20th, 2011
Informatica Architecture : client tools- Repositoy Manager-> Pc Designer-> WFManager-> WFMonitor | ...
ram_infa
- Sep 15th, 2011
When we install the informatica powercenter, the following components gets ionstalled 1) powercenter clients 2) Integration service 3) powercenter Repository service 4) powercenter Repository 5) power...
-
Transaction control transformation to get flat file names
Hi Folks,
I have scenario like, I have ten flat files to be loaded in to target but I need to load file names in to a table in mapping level. I think we can do achieve though TC transformation. Unfortunately I did not get though.. Please advise how to implement the logic. ( I need to capture in mapping level only).
This question has been asked one of the interviews.BRAHMAIAH
- Dec 30th, 2014
Hi folks, If all files have structure then we go for Indirect file method.we set this property at session level by selecting the source def. After define of the source def at mapping level just do...
Mayur
- Jun 23rd, 2014
Got to flat file properties and there you will find one icon Add FileName column to this table just click on that option and you can able to add currently processing file. Thank you
-
Informatica number formatting
How to format phone number 9999999999 into (999)999-9999 in informatica
-
Discuss which is better among incremental load, Normal Load and Bulk load
Read Best Answer
Editorial / Best Answer
gazulas- Member Since Jan-2006 | Jan 29th, 2006
if supported by the database bulk load can do the loading faster than normal load.(incremental load concept is differnt dont merge with bulk load, mormal load)
ram_infa
- Sep 14th, 2011
It's totally depends upon the requirement. generally bulk load is faster caz it won't write the data to database logs so we can't do session recovery where as in normal load it is very slow comparing to bulk but the advantage is we can do session recovery..
vikota
- Apr 12th, 2011
Incremental load: Incremental means suppose today we processed 100 records ,for tomorrow run u need to extract whatever the records inserted newly and updated after ...
-
Hw to work with pmcmd on windows platform
Joe
- Feb 7th, 2013
ECHO OFF This automates the informatica workflow run using pmcmd ECHO c:informaticaserverinpmcmd startworkflow -u username -p password -s server:host -f Folder -wait MYWORKFLOW
dl_mstr
- Feb 11th, 2008
C:-->Program Files-->Informatica PowerCenter 7.1.3-->Server-->bin-->pmcmd.exe
-
Write a query to retrieve latest records from the target table means if we have used scd2 version type of dimension, than retrieve the record with highest version no.for eg verno id loc 1 100 bang 2 100 kol 1 101 bang 2 101 chenwe have to retrieve 100/kol and 101/chen. how it is possible through query.
Chetan
- Mar 2nd, 2018
What if the we have
1,100,Banglore
2,100,chennai
3,100,Mysore
1,101,bangalore
2,101,Kol
Your query will retun 2,3 and we will get
2,100,chennai
3,100,Mysore
2,101,Kol as outputpriyank_divya
- Jul 9th, 2012
Sorry for my previous post. I missed a bit of question.
select * from where version in (select max(version) from group by id, loc_no) -
Convert Source Column into Target Rows
What transformation do you use to convert source column into target rows? Explain
vijayant.sanjay
- Sep 7th, 2011
Normalizer is basically used to convert rows to columns. I don't think alone normalizer is sufficient to convert columns into rows. In certain scenario you use this sequence of t/f to convert columns ...
Ishaan_Kulshrestha
- Apr 14th, 2010
Hi Sahan,Please let me know how aggregator is used here. I think Aggregator is required when we denormalise data and here our requirement is to Normailse the data.Please correct me if I am wrong. Thanks
-
What are Aggregate transformation?
Aggregator transformation allows you to perform aggregate calculations, such as averages and sums.
Read Best Answer
Editorial / Best Answer
Answered by: Praveen vasudev
- Sep 12th, 2005
Aggregator transform is m uch like the Group by clause in traditional SQL.
this particular transform is a connected/active transform which can take the incoming data form the mapping pipeline and group them based on the group by ports specified and can caculated aggregate funtions like ( avg, sum, count, stddev....e.tc) for each of those groups.
From a performanace perspective if your mapping has an AGGREGATOR transform use filters and sorters very early in the pipeline if there is any need for them.
veepee
sivakp
- Mar 13th, 2011
1. Aggrigator transformation allows to perform aggrigate calculation, such as SUM, MAX, MIN, FIRST, LAST2. Aggrigator transformation allows to perform aggrigate calculation of group.
shr_4
- Oct 27th, 2010
To perform Group by calculations we use Aggregator Transformation.It perform calculations similar to Expression Transformation.But difference between both is that Aggregator Transform...
-
Why we use lookup transformations?
Lookup Transformations can access data from relational tables that are not sources in mapping. With Lookup transformation, we can accomplish the following tasks:Get a related value-Get the Employee Name from Employee table based on the Employee IDPerform Calculation.Update slowly changing dimension tables - We can use unconnected lookup transformation to determine whether the records already exist...
Read Best Answer
Editorial / Best Answer
Answered by: prodyot Sarkar
- Jul 31st, 2007
The following reasons for using lookups.....
1)We use Lookup transformations that query the largest amounts of data to improve overall performance. By doing that we can reduce the number of lookups on the same table.
2)If a mapping contains Lookup transformations, we will enable lookup caching if this option is not enabled .
We will use a persistent cache to improve performance of the lookup whenever possible.
We will explore the possibility of using concurrent caches to improve session performance.
We will use the Lookup SQL Override option to add a WHERE clause to the default SQL statement if it is not defined
We will add ORDER BY clause in lookup SQL statement if there is no order by defined.
We will use SQL override to suppress the default ORDER BY statement and enter an override ORDER BY with fewer columns. Indexing the Lookup Table
We can improve performance for the following types of lookups:
For cached lookups, we will index the lookup table using the columns in the lookup ORDER BY statement.
For Un-cached lookups, we will Index the lookup table using the columns in the lookup where condition.3)In some cases we use lookup instead of Joiner as lookup is faster than joiner in some cases when lookup contains the master data only.
4)This lookup helps in terms of performance tuning of the mappings also.
Satya
- Aug 9th, 2014
Use lookup transformation in a mapping to
--get related values
--perform complex calculation
--Handle slowly changing dimension.gazulas
- Apr 22nd, 2009
Thats a good question, suppose say you have 40 transformations in your mapping which invole complex agg, functions,? so in middle of the mapping there is a requirement to get the data from some? x t...
-
What is the way to add the total number of records that have been read from SRC in the TGT file as last line?
What is the way to add the total number of records that have been read from SRC in the TGT file as last line? Let me clear you if my TGT is a flat file and want to add total number of records that are written in TGT as a last line with sysdate.
-
What is Session and Batches?
Session - A Session Is A set of instructions that tells the Informatica Server How And When To Move Data From Sources To Targets. After creating the session, we can use either the server manager or the command line program pmcmd to start or stop the session.Batches - It Provides A Way to Group Sessions For Either Serial Or Parallel Execution By The Informatica Server. There Are Two Types Of Batches...
Read Best Answer
Editorial / Best Answer
sanghala- Member Since Apr-2006 | May 14th, 2007
Session: A session is a set of commands that describes the server to move data to the target.
Batch : A Batch is set of tasks that may include one or more numbar of tasks (sessions, ewent wait, email, command, etc..,)
There are two types of batches in Informatica:1. Sequential: When Data moves one after another from source to target it is sequential
2. Concurrent: When whole data moves simultaneously from source to target it is Concurrent
ram_infa
- Sep 9th, 2011
Session is nothing, but it tells the integration service, when and how to move the data from sources to target
Group of sessions is called a batch..ravindra
- Jul 15th, 2011
Session is an object of repository,which instructs the informatica server(integration service)to execute the mapping with given database connection's...
-
-
What are Target Types on the Server?
Target Types are File, Relational and ERP.
Read Best Answer
Editorial / Best Answer
manojkumar_dwh- Member Since Apr-2007 | Apr 14th, 2007
PowerCenter can load data into the following targets:
- Relational. Oracle, Sybase, Sybase IQ, Informix, IBM DB2, Microsoft SQL Server, and Teradata.
- File. Fixed and delimited flat file and XML.
- Application. You can purchase additional PowerCenter Connect products to load data into SAP BW. You can also load data into IBM MQSeries message queues and TIBCO.
- Other. Microsoft Access.
You can load data into targets using ODBC or native drivers, FTP, or external loaders.
-
How do you identify existing rows of data in the target table using lookup transformation
Can identify existing rows of data using unconnected lookup transformation.
Read Best Answer
Editorial / Best Answer
Answered by: SK
- Aug 30th, 2007
There are two ways to lookup the target table to verify a row exists or not :
1. Use connect dynamic cache lookup and then check the values of NewLookuprow Output port to decide whether the incoming record already exists in the table / cache or not.
2. Use Unconnected lookup and call it from an expression trasformation and check the Lookup condition port value (Null/ Not Null) to decide whether the incoming record already exists in the table or not.doppalpaudi
- Jul 9th, 2010
Lookup Transformation is used to cheek weather the data is present in target or not.This transformation is of 2 types 1. Connected lookup transformation2. Unconnected lookup transformation.By using above both we can cheek the target for data .
svivekananda
- Jun 26th, 2010
We can use both connected and unconnected lookups to check the existing records in the target table.
-
We can insert or update the rows without using the update strategy. Then what is the necessity of the update strategy?
Mohan
- Aug 12th, 2018
1. You cannot perform DD_Delete unless you have update strategy.
2. While updating a record, ideally created date should not be changed. for that we definitely have to create two targets (insert and update)vskogirala
- Feb 15th, 2008
In update strategy ,1)An insert in the source an insert in the target2)An update in the source is an update in the target update strategy provides 4 types of data driven functions 1)DD_INSERT2)DD_UPDA...
-
How to only last 100 records from a flatfile?
Thru informatica how we can load only last 100 records.
Informatica Interview Questions
Read Best Answer
Ans