Read CSV File in ICRT

 

ICRT

let $x := $input.fileEvent[1]/content 

return replace($x,'"id","Name"
','')

for $i in 1 to  count(tokenize($temp.tmp_Source ,'
'))

return <ID>{

replace(substring-before(tokenize($temp.tmp_Source ,'&#xD;')[$i],','),'"',"")}</ID>

for $i in 1 to  count(tokenize($temp.tmp_Source ,'&#xD;'))

return <NAME>{

replace(substring-after(tokenize($temp.tmp_Source ,'&#xD;')[$i],','),'"',"")}</NAME>

let $x := unparsed-text('file:///C:/Users/informatica/lmp_support/INFA_API_Shared/API_ClientConfig/Config/Source1.txt')

return replace($x,'"id","Name"&#xD;','')

Informatica Cloud Interview Questions (IICS)

----------------------------------------------------------------------------

----------------------------------------------------------------------------

• IICS Basics
• IICS Administration
• IICS Data Integration
• IICS Monitor
• IICS Application Integration
• IICS Application Integration Console
• IICS API Manager
• IICS Cloud Integration Hub
• IICS Mass Ingestion

----------------------------------------------------------------------------

----------------------------------------------------------------------------

IICS Basics:

1. What is the difference between Informatica PowerCenter and Informatica Cloud?
2. How to log in to IICS?
3. Where does the metadata of tasks get stored in Informatica Cloud (IICS)?
4. What is the current IICS version available?
5. What are major and minor releases in IICS? and details about the IICS prerelease
6. What services are mainly offered by IICS?
7. What is IPU consumption in IICS?
8. How to run and schedule the jobs? can we run using third-party schedulers?
9. What type of information gets stored in the Informatica Cloud (IICS) repository?
10. Where will data and logs be stored? Can IICS able to see the customer's data?

----------------------------------------------------------------------------

IICS Administration:

1. Explain about the Add new users. Configure user groups and roles for individual users?
2. How to create projects and folders in IICS? How to restrict user permissions on the projects?
3. How to set up SAML Setup in IICS for SSO login?
4. How to Configure Source Control Settings to enable the versioning?
5. How to create a connection in IICS?
6. What is the difference between Native and Add-On Connectors? How to install add-on connectors?
7. Explain the configuration details for the below connectors:
1. File
2. SQL Server
3. Salesforce
4. Snowflake Cloud Data Warehouse V2
5. SFTP V2
6. File Processor
7. Amazon S3 connector
8. Rest V2 Connector
9. Web Service Consumer
10. OData Connector
8. Explain the IICS Schedules, How to create and manage them, and their limitations compared to the third-party schedulers?
9. How to generate the swagger files for REST APIs?
10. What are Asset & Security Logs in IICS
11. How to migrate the IICS assets from the development system to another IICS environment?
12. How to implement the CICD for IICS deployments?
13. What is a Runtime environment?
14. Explain the architecture of secure agents in IICS?
15. How to install an IICS secure agent?
16. Explain how to start and stop the IICS secure agent?
17. Describes the Secure Agent services:
1. Data Integration Server
2. Common Integration Components
3. Process Server
18. How to check the server and job execution logs?
19. what are the IICS admin roles and responsibilities?
20. What is the difference between load balancing and secure agent clustering in IICS?

----------------------------------------------------------------------------

Cloud Data Integration (CDI)

Level-1 (Mappings)

1. What are the CDI mappings and how to create the mapping in IICS
2. What are the active and passive transformations
3. How to use the below transformations
• Expression
• Sorter
• Joiner
• Lookup
• Union
• Router
• Filter
• Rank
•  Aggregator
•  Normalizer
• Transaction Control
4. What are the major differences between the below assets
• Join and Lookup 
• Connected and Unconnected lookup
• Union vs File list
• IIF vs Decode function 
•  SQL override and Lookup override
•  Data cache and Index cache
•  Hierarchical parser and structural parser
• Input and IN/OUT Parameters
• SCD Type 1,2,3
•  Fatal and Nonfatal errors
• Upsert and data-driven
5. What are the different methods to remove duplicates in CDI
6. What is indirect file load and how can we implement that in IICS
7. How will you read the Source JSON/XML file in IICS
8. How to reset the sequence generator when we migrate from DEV to QA
9.  How to execute UNIX/Power shell/python commands in IICS Mapping
10.  What is the biggest mapping you handled as a developer? (SCD TYPE 2)
11.  Explain about SUBSTR, INSTR, ERROR, LKP, and DATE functions
12. What is GCID in the normalizer
13.  Mapping level Performance tuning
14.  Web service consumer transformation
15.  Exception handling and user-defined errors
16.  Types of caches
• 1. Data Cache
• 2. Index Cache
• 3. Static cache
• 4. Dynamic cache
• 5. Persistent Cache
• 6. Re cache (Refill Cache)
• 7. Shared Cache
17.  How to call Stored Procedure in IICS
18.  How to return multiple values from unconnected lookup
19.  What is incremental load, what are the different approaches to implement that
20.  In join transformation, which object will be master and which object will be detailed, based on what metrics we decide that?
21.  How to convert Rows into columns in IICS?
22.  Difference between REST V2 connection and Web service consumer
23.  How to create a business service and how to use it in IICS
24.  How to pass multiple rows to input requests for the web service call
25.  How do decrypt and encrypt the PGP encrypted source flat file?
26.  How to copy/move/remove files from one folder to another folder using a file processor connection?
27.  Can we move the file to the SFTP location using IICS connections?
28. Can we use the command in the source instead of the file list?

Level-2 (Mapping Tasks, Synchronization, Replication, Mass Ingestion)
1) How you implement performance tuning in the Informatica mapping Tasks
2) Error Handling mechanism in data integration
3) What is the mapping task
4) How to schedule the Mapping
5) What is the blackout period in the schedule
6) What is the parameter file and how you use it in the mapping
7) How to enable verbose mode in Informatica data integration
8) What is cross-schema pushdown optimization
9) Tell me below advanced session properties below:

• -> Rollback transactions on error
• -> Commit on the end of the file
• -> Recovery Strategy
• -> DTM process
• -> Incremental Aggregation
• -> Pushdown Optimisation
• -> Session Retry on deadlock
• -> Stop on error

10) Difference between Linear task flow and Taskflow
11) Limitations of Data synchronization task
12) Use of Replication task
13) What is incremental load, full load, and initial load
14) How to perform upsert in Informatica mapping and required constrained to implement
15) How to run Pre and Post SQL commands, Pre and Post Processing commands
16) Difference between Synchronization vs Replication Task
17) Different types of mass ingestion tasks
18) What is Data Masking
19) How to configure maplets in IICS
20) Use of control table in ETL
21) Explain the below components in task flow:

• Data task
• Output variables
• Sub-data task
• Custom error handling
• Email notifications,
• Command task

22) How to call CAI process in DI job 23) How to read parameter file values into MCT
24) How to execute python /Unix /Powershell script using Command task (windows Secure agent)
25) How to execute multiple mapping task instances simultaneously
26) What is the use of Stop on Error property in MCT
27) What is the use of the email notification option in MCT
28) How to use the fixed-width delimited files in the source
29) How to create a Business service and its use case
30) What is the use of hierarchical schema, can we do create without schema
31) How to send an email using the notification step in the task flow
32) Can we send output response to Task flow
33) How to send variables data to mapping columns in the task flow
34) How to get values from mapping columns to task flow variables?
35) How to implement custom error handling in the task flow
36) How to do audit logging in IICS with data task output response variables?
37) How to Trigger the task flow based on file event
38) How to create a file listener and how to trigger task flow
39) when do we use file events and when do we use a schedule?
40) How can we trigger IICS task flow using a third-party scheduler?
41) What is included dependency check-in assets export
42) Best practices for IICS code migrations (export and import)
43) How to implement versioning in IICS
44) what is asset-level permissions and how to use that (ACL)
45) Different types of semi-structured data and how to read in IICS

Administrator

1) Load balancing in Informatica cloud
2) Secure agent architecture
3) Powercenter vs IICS Cloud
4) How to create a secure agent group
5) How to create connections and describe addon connecters vs native connectors
6) How to move IICS code from DEV to QA and different approaches
7) Why do we uncheck Include dependencies while exporting the assets.
8) How to implement versioning in IICS
9) How many Status states are available in monitor and what are those
10) How to check job logs and their default location in SA
11) Session logs vs error logs vs reject data and their locations
12) How to generate dynamic files based on date and time
13) Performance tuning by administrator level
14) How to schedule the mapping task on the last day of the month.
15) How to restart the secure agent in both windows and Linux env
16) What are sub org and their uses
17) How to create SAML authenticated account
18) What is Audit logs and what information will be stored in them and how to download
19) What is Security logs and what information will be stored in them and how to download
20) What are user roles and how can we create custom user roles
21) What are user groups and how can we create custom user groups
22) How to enable web service requests and responses in the log file?
23) What is the MaxDTMBuffer option in the runtime environment
24) How to stop and start the services in the runtime environment
25) Process server responsible for what?
26) Data Integration server responsible for what?
27) What is load balancing and how to implement that
28) Where CAI metadata is stored?
29) Where CDI metadata is stored?
30) How to create the swagger file and where to use it?



Application Integration

1) Explain about Application Integration Architecture and Describe below objects

• Process
• App Connection
• Service connector
• Process object
• Guide

2) How to configure service connector and its uses in ICRT
3) Describe the below components in the process :

• Service
• Sub Process
• Create
• Decision
• Parallel
• Wait
• Fault
• Milestone
• Assignment
• Advance variables
• Jump

4) List of XQuery you used in the previous project.
5) list of expressions used in the assignment
6) Fault handling in ICRT and 3 methods
7) Error logging in ICRT and through the email notification
8) List of connectors available in ICRT
9) How to read and write the content of the delimited file
10) How to connect the database and perform the update, insert and delete operations
11) How to schedule the process
12) Handling the SOAP and Restful API's
13) Run Unix/power shell/Python/Bat command in ICRT
14) How to Run Data Integration Linear task flows or Mapping tasks in CAI
15) How to perform parameterization in ICRT
16) Read a CSV file and display the content in the output variable
17) Write the data into the flat file using the process
18) Convert XML data into JSON format in ICRT
19) what is pagination and how to implement it in the process
20) Difference between SOAP and REST web services
21) How to softcode the URL in binding area
22) How to get simplified output response from entire response
23) What are the different HTTP methods and their uses
24) What are the different header in API
25) What are the different Authentications in API
26) What is body and query parameters in API
27) Standalone and derived process objects in CAI
28) Explain about below app connections:

• Service Connector
• File
• JDBC
• SFTP
• Email
• Restv2
• Salesforce

29) In File/SFTP app connection, what refers to below options

• Polling Interval
• Initial Delay:
• Max Messages Per Poll
• Delete Processed Files:
• Before Move:
• Move To:
• Move If Failure:
• Max Reconnection Attempts:

30) How to run the process in CAI
31) What is verbose mode
32) How to check logs in the CAI console and what are the advanced variables
33) What are deployed assets
34) What is URN mappings
35) How to create BRP file using process developer tool and How to deploy BPR assets in CAI console
36) How to configure Faults alerts in the CAI console
37) How to check process audit Logs
38) How to filter the logs based on faults that occur
39) How to configure process in API Manager
40) What is the difference you saw when the process deployed on Secure Agent and Cloud

SCD Type 2 Implementation in Informatica Intelligent Cloud Services (Data Integration)

Q) How to implement slowly changing dimension (SCD) Type 2 Effective Date mapping in Informatica cloud data Integration?

Step1: Create 2 tables in oracle database as follows
--Source Table

Create Table SCD2_Source
(
  ID Number ,
  Name    Varchar2(30),
  Role    Varchar2(30),
  Sal    Varchar2(30)
);

--Target Dimension Table

Create Table SCD2_Dim
(
  S_key NUMBER PRIMARY KEY,
  ID NUMBER ,
  Name    Varchar2(30),
  Role    Varchar2(30),
  Sal    Varchar2(30),
  Start_Date DATE,
  End_Date DATE,
  Flag char(1),
  OPERATION Varchar2(100)

);

Step2: Login to Infa cloud and select Data Integration services

Step3: Click on New Asset-> Mappings-> Mapping

Drag below components from the toolbar and configure as suggested below

1.Drag source and configure it with source table

2. Drag a lookup transformation and configure it with the target dimension table

3. Connect the source to Lookup and include only the ID field and add a condition  Target_Id = Source_id

4. Drag an expression transformation and connect lookup and Source to it ( It may ask you  to resolve name conflicts, resolve them by using prefix or suffix)

5. Include only the fields that may change from the lookup (Ex., LKP_Role, LKP_Salary, and LKP_S_Key)

6. Add an output port o_SRC_Hashkey as type string(200) and configure it with the expression MD5(Role||To_Char(Sal)) this will be compared with the lookup HashKey to check if there is any change.

7. Add another output port o_LKP_Hashkey as type string(200) and configure it with the expression MD5(LKP_Role||To_Char(LKP_Sal))

8. Add Router and connect the Expression to Router.

9. Create two groups Insert and Update and configure them  INSERT when LKP_S_key is NULL -- (ISNULL(LKP_S_key ) and update when LKP_S_key is not NULL and o_SRC_Hashkey  is not equal to o_LKP_Hashkey --  (NOT ISNULL(LKP_S_key ) AND o_SRC_Hashkey  <> o_LKP_Hashkey

10. Drag an expression and connect the insert group to it and only include source fields in it and add 2 output fields.

     o_Start_Date -- sysdate

      o_flag -- "Y"

11. Drag target (INSERT)and connect the above expression transformation to it and create a sequence generator and also connect it with the target.

12. Map target fields (NEXTVAL from seq will be given to S_Key, o_Start_Date will be given to Start_Date and o_flag will be given to Flag and End_Date will be NULL fro insert Record)

13. Drag an expression and connect the output group of Router to it and include only LKP_S_KEY, Role, SRC. Add two output ports

      o_End_Date -- sysdate

      o_flag -- "N"

14. Drag Target(Existing_Update) and connect the above expression to it. The select operation to Data Driven and Data Driven Condition is DD_UPDATE also add the Surrogate key(S_KEY) as the update column

15. Map S_Key to LKP_S_KEY and o_End_Date to End_Date and o_flag to Flag

16. Add expression and connect the update group from the router to it.

17.Include only the source rows and create two output fields

      o_Start_Date -- sysdate

      o_flag -- "Y"

18. Add target (EXISTING_INSERT) and connect the sequence and the above expressions to it.

19. Map the fields like we did for insert and save the mapping.

Step4: Close the mapping and click on New Asset -> Tasks -> Mapping Task

Input Task Name and select Runtime Environment and Mapping which we created

Step5: Click Next ( Sequence) and Finish

Step6: The Mapping Task will be saved then run the task.