In Informatica Cloud, all the information is stored on the server maintained by the Informatica and the user does not have access to the repository database.
Hence, it is not possible to use any SQL query to retrieve the information like in Informatica Powercenter.
So we will be going with an alternate approach to read metadata in Informatica Cloud and we will specifically discuss on reading Mapping Configuration task metadata.
It’s not straight forward way like firing a SQL query. Its needs a onetime setup at your end and I will guide you through that process. This will also act as an Auto review accelerator tool for your Mapping Configuration tasks.
Here we will be using the zip file exported from IICS as an input to read the metadata. Next a script to extract the JSON files from the exported zip. A mapping to read the JSON files using a Hierarchy Parser and generate a report with required metadata details.
I have explained briefly on how to fetch the zip file, what would be the contents of the zip file and how to build a script to fetch the JSON files here.
Here we will be discussing on the mapping development to read MCT metadata.
2. Steps involved in the Mapping Development
2.1. Source transformation
The Source transformation reads a flat file containing the list of all the mapping JSON files as a source.
2.2. Hierarchy Parser transformation
The Hierarchy Parser transformation reads the JSON files one after the other and provides a relational output.
A Hierarchy Schema needs to be defined and imported into the Hierarchy Parser transformation.
The Hierarchy Parser parses the JSON input into below relational format. There is a lot of information to consume from the metadata as shown below. I will be reading only root information and parameters information and show how they are linked here.
All the Field Segments are related by Primary Key and Foreign Key relationship created by the Hierarchy Parser transformation.
2.2.1 Understanding Primary key in Hierarchy Parser transformation
2.2.2 Foreign Key in Hierarchy Parser transformation
When you try to map the data from Hierarchy Parser to any other transformation, Informatica prompts you to select a field segment you want to map as shown below.
So you can only map one field segment data to a transformation at a time.
You can see I have pulled the data from Root and Parameters field segment into the Expression transformation in the mapping.
2.3. Joiner transformation
Root and Parameters expression data are joined using the Joiner transformation using the below condition
Root.PK_root = Parameters.FK_root
2.4. Aggregator transformation
When multiple parameters are read, there will be multiple entries created for same MCT. In order to avoid that aggregator is used to remove the duplicates.
2.5. Look up transformation
There is certain information which is not directly present in the MCT JSON.
Instead of Runtime Environment Name and Mapping name associated to the MCT, their ID is provided as shown below.
These values can be looked up in the CSV file and fetch the actual names.
The fields are calculated from expression as below.
2.6. Target transformation
Target is flatfile which reads the information passed.
There will be ton of fields that will be coming to the target. Make sure you only select the required fields in the Incoming fields section of the target so that the output will be clean.
What we discussed so far is just a tip of an iceberg. You can read almost every details of the Mapping configuration task using this method.
Tip: If you are so confused about the joins and lookups, I suggest you pull the root data alone and read the data from target. It gives a lot of important information already.
Then you can experiment joining with other field segments depending on the requirement.
We have a complete 2 hour video course on Udemy explaining how you can build this from scratch. Along with a video guided course you will also get instructor support in case you need any help in building the tool at each and every step with a life time access.
The enrollment link to the course:
Informatica Cloud Data Integration – Automation Project