One type of mapping I find particularly complicated to build is one using the SAP iDoc connector. The complicate part is achieving the correct value nesting. It is complicated because when you create the mapping, the nesting is not visible. It is created by the use of Primary and Foreign key fields (GPK and GFK in the mapplet).
I have seen several approaches to this that work and the approach I am going to share here is the one that I came up with and use because I understand it. YMMV.
First, I create a list of GPK and GFKs from the mapplet XML. These values can be found in the TRANFORMATION nodes within the MAPPLET node.
With this list in hand, I create the document GPK with a field that will be unique per iDoc. In this case I have a unique entity ID in my source and a date field that will make the entry for the entity unique. I prefix this key with the OTYPE because I may have multiple entries for the same object on the same date but different OTYPEs.
Creating the GPK_DOCNUM Value
Then, for each immediate child, I define its GFK and GPK using the node names as prefixes:
Creating the GPK_E2PLOGI001 ValueCreating GPK_E2PITYP001 ValueDefining First Child of E2PITYP001
The above examples goes three levels deep. If your iDoc requires nest your nodes in a different pattern, generate your GPK values in such as way that they can be properly created in both the parent and child node.
For ease of maintenance, I name the fields in the expressions the same as they are defined in the mapplet so that I can use Automatic field mapping, which is eliminates the need to manually re-map anytime I make a change:
Auto-mapping Fields for ease of Maintenance
I hope this saves you some of the time it took to get me to this approach.
In the Target Org, From the Administer Menu, Select Migrate ObjectsOn the Migrate Objects Page Click Start MigrationClick the Log In… ButtonEnter credentials for an Administrator Account in the Source OrgOnce logged in you can click the Add Objects buttonSelect Objects to Migrate.
Save time by selecting objects at the top of related hierarchies, such as Task Flows will automatically select the tasks, and any objects required to support the task (such as Mappings and Connections) where selecting an items lower in the hierarchy (such as Connections) will not automatically select their parents. Close the dialog by clicking the OK button (not pictured).
Back on the Migrate Objects Page Click the OK ButtonChoose Carefully Whether to Overwrite or Not
If you are migrating updated objects you will see the prompt above. You may wish to rename the existing objects before migrating. You may also want to delete the existing objects as the Overwrite behavior may not be what you expect.
Once the migration is complete, you will need to review your objects to confirm or correct any Org-specific values such as the Secure Agent name or credentials for Connections that require them.
Informatica introduced their cloud initiative back in 2006. It has grown to encompass many data-related services including cleansing, EDI, MDM, etc. To set the context of this writing, by “Informatica Cloud” it is meant to include only the separate-but-related application- and data-centric aspects, sometimes referred to as the Informatica Cloud Integration Platform as a Service (iPaaS). iPaaS includes the Cloud Data Integration functionality based on (and separate from) their flagship Power Center ETL application and the Cloud Application Integration based on (and distinct from) the ActiveVOS platform that Informatica acquired back in 2013.
To save bytes, Informatica Cloud Data Integration will be referred to as ICS, and Informatica Cloud Application Integration (a.k.a, Real Time Integration) as ICRT.
Software Silos
Given the background, it is not very surprising that ICS and ICRT are mostly used separately for their key purposes. If there is some data that needs to move from system A to system B, ICS is the tool, and if a workflow needs to happen in real time, ICRT is the way to go. Both of these are valid assumptions, and the fact that ICRT is an additional cost to the default ICS included with iPaaS strengthens this viewpoint.
ICS provides a robust API for managing objects and running tasks. There is a connector in ICRT that provides wizard-driven access to the ICS API. ICRT processes can be exposed as web services that provide both a SOAP and ReST interface. In short, despite their distinct natures, ICS and ICRT can be easily integrated out-of-the-box (or, out-of-the-cloud, in this case).
Gain Elevators
Informatica provides ICS connectors for many third-party systems that are frequently integrated through ICS, such as SAP, Workday and Salesforce, in addition to common protocol connectors like SOAP, ReST and JDBC. In theory, there are very few systems that cannot be integrated in an ETL-manner using ICS, and this is also true in practice. That said, “able to” and “easy to” are important factors to consider when planning an integration project within delivery scope and maintenance goals.
Most of the connectors for ICS are also exposed in ICRT when enabled or installed. ICRT has a very robust architecture for creating Service Connectors to SOAP and ReST services that can be used by Processes that can in turn (as mentioned earlier) be exposed as SOAP or ReST services.
Super Mash
Not all web services are created equally. Where some provide a straight-forward interface to elicit data in a format ready for inter-platform translation, most are intended for look ups and transactions rather than being a source for batch-loading data. Informatica provides some connectors that wrangle popular APIs, such as Salesforce.com, into a structure that is easy to work with. Other services may have a connector that is more suited to being an ETL target, or meant more for the “citizen developer” to be able to load data into reporting format. Informatica also provides standard ReST and Web Service adapters, but if the API response is several layers deep it can be complex and confusing getting at the values using a graphical design platform such as ICS.
Fortunately, ICRT provides a way to quickly create a Service Connector for any standard API. The Service Connector provides a wizard to turn the API response into an object that can then be streamlined and simplified for easy management in an ICRT processes. The ICRT process can perform further transformations, such as renaming fields and formatting data types, or simply act as a pass-through for outputting the more digestible response format.
Once the ICRT process is connected to the Service Connector, you have the option of beginning your integration in either ICRT or ICS, depending on the nature of the integration. For example, if there is a great deal of processing to be done in ICRT before the data is ready for ICS, it is simpler to initiate the process in ICRT, output the service response to a disk location, and then call ICS to perform the ETL steps with the file as a source. Alternatively, when the response is quick or ICRT is only acting as a proxy to simplify the response, the ICRT process can be exposed as a service and that service called by ICS as the ETL integration source.
For Instance…
Here is a real-world example of where this approach is useful. Informatica provides a perfectly functional connector to Workday. The connector provides full access to the Workday APIs. The Workday APIs, however, are not very simple to use. They provide a some control over the response format, but anything beyond limited data in common fields is deeply nested within complex objects. Note in the image below the number of fields available:
Workday ICS Data Source
Using an ICRT Service Connector, we can take this complex response and immediately simplify it:
ICRT Service Connector Simplified XML Object Definition
The Service Connector above can be run by an ICRT Process that will map the fields to a process object with the same names as the target system fields (SAP in this example) and then provides them directly to the mapping as a flat data set:
Simplified Source in ICS from ICRT
Granted, the mapping could still have been accomplished without the use of ICRT. By introducing ICRT as a proxy to the web service, development can be done faster by parsing simple XML rather than traversing complex nested objects. With the field names being defined in ICRT, if it is necessary to redefine the field sources there is no need to trace back through transformations in a Mapping to locate what may have been impacted.
Avoid a Clash
Only one instance of a Mappings task can be running at a time. To avoid the error “The Mapping Configuration task failed to run. Another instance of the task is currently running”, use a unique Mapping Configuration Task per process. In the case of Data Synchronization Tasks, many of the same tasks can be performed by a mapping, which can have multiple Mapping Configuration Tasks calling.
Conclusion
In most cases Informatica Cloud Data Integration functionality is all that is needed and desired to integrate data between systems. In some cases where web services are the source and the format of the service response is nested and complex, using Informatica Cloud Application Integration as a proxy service to simplify the response to just the fields needed for transformation can be a time saver both in the creation of the integration and its future maintenance.
It is great that Informatica provides an SAP iDoc connector rather than having to manage such a complex integration manually. Connector-specific validations happen at the connector level, so it makes sense that the required GFK or GPK fields are validated as having a value at run time rather than design time.
In the version as of this writing, the validation only checks that the fields have values set, and not that the values are valid within the same iDoc. To clarify, the GFK and GPK values specific to a mapping would be impossible for the connector to validate as they are very enterprise-specific. However, while it could be assumed that validation within the specific mapping instance would occur to check if a GFK in one segment matches the GPK of the segment it must relate to, that is not the case. The developer must be careful that these values are set correctly within the mapping and valid between the segments.
Recently I had the need to encrypt a file before sending it via FTP. A colleague of mine (JayJay Zheng) had discovered that a Mapping object could perform an FTP by configuring the Source transformation with a File Processor connection and then entering the FTP connection details as Query Options on the connection as shown below:
SFTP with a Mapping Object
When it came time to encrypt the file prior to transmission, I found KB 476543, HOW TO: Encrypt the data in Data Synchronization task within Informatica Cloud, which did the trick. But in a later design review session, the same colleague pointed out that the number of objects to be maintained could be reduced by using the same Mapping configuration approach as done for FTP with the encryption and running them in sequence within the mapping:
