A blog where you can explore everything about Datawarehouse, OBIEE, Informatica, Power BI, QlikView, Hadoop, Oracle SQL-PLSQL, Cognos and much more....
In most of the Informatica Interview the panel will come with
Scenario based questions which is bit tricky and confusing. Here I have listed
few scenario based questions:
Please use SQL overide to pull all records from lookup table which satisfies the condition SUBSTR (Account_number, 1, 2) =’12’ Then filter the rows from lookup which return null for this.
Hi, Let me see if am clear in explaning my scenario, so that i shall get some good repsonses. I have both the source and tgt in oracle db. I am concerned bout 4 columns- CTR_ID, INVS_ID, ALLOC_PCT, SCHEME_ID... CTR and INVS_ID is the unique key combination in tgt. Now i need to do INS/UPD for the belwo scenarios.
I --> For same combination of CTR_ID and INVS_ID, if a rec exists in tgt, then check for scheme_id.. if the srce and target scheme_id are different jus update the scheme_id alone in tgt.. this shall be done as TYPE I ins/update
II --> For same combination of CTR_ID and INVS_ID, if a rec exists in tgt, then check for ALLOC_PCT.. if the srce and target ALLOC_PCT are different, then expire the current record in trgt and insert the new rec with src alloc_pct. this shall be handled in SCD2 if i m not wrong.
III --> here comes the bottleneck!!!.. For same CTR_ID, I may have different INVS_ID in tgt.. I need to for this and apply the above scenarios .. Kindly suggest how to proceed...
Alternate Approach:
ReplyDeletePlease use SQL overide to pull all records from lookup table which satisfies the condition
SUBSTR (Account_number, 1, 2) =’12’
Then filter the rows from lookup which return null for this.
Hi,
ReplyDeleteLet me see if am clear in explaning my scenario, so that i shall get some good repsonses.
I have both the source and tgt in oracle db.
I am concerned bout 4 columns- CTR_ID, INVS_ID, ALLOC_PCT, SCHEME_ID... CTR and INVS_ID is the unique key combination in tgt. Now i need to do INS/UPD for the belwo scenarios.
I --> For same combination of CTR_ID and INVS_ID, if a rec exists in tgt, then check for scheme_id.. if the srce and target scheme_id are different jus update the scheme_id alone in tgt.. this shall be done as TYPE I ins/update
II --> For same combination of CTR_ID and INVS_ID, if a rec exists in tgt, then check for ALLOC_PCT.. if the srce and target ALLOC_PCT are different, then expire the current record in trgt and insert the new rec with src alloc_pct. this shall be handled in SCD2 if i m not wrong.
III --> here comes the bottleneck!!!.. For same CTR_ID, I may have different INVS_ID in tgt.. I need to for this and apply the above scenarios .. Kindly suggest how to proceed...