Informatica DTCC library error report This document describes an enhanced HTML error report which can be created by leveraging Informatica’s DTCC library and B2B data transformation.
Introduction Informatica provides libraries to provide the ability to parse and generate industry standard formats such as DTCC, SWIFT, SEPA, HIPAA, HL7, NACHA to name a few. Industry standard files have complex requirements and are not easy to read and understand. Except for some of the more recent formats most of them are NOT self-describing and require specifications (published yearly, bi-yearly or quarterly) by the specific organization. Leveraging Informatica B2B DT allows Informatica customers to extend their data integration capabilities both on-premise and in the cloud to generate such industry standard files and messages. A quick few lines on DTCC first. DTCC stands for Depository trust and clearing corporation (http://www.dtcc.com/) Specifically focusing on the insurance and retirement services (http://www.dtcc.com/investmentproduct-services/insurance-and-retirement-services.aspx) indicates the need to support the creation and parsing of the following types of DTCC files
AAP – asset pricing APP – application and premium COM – commissions FAR – financial activity reporting IFT – in force transaction LNA – licensing and appointments POV – positions and valuations REN STL – Settlement processing SUB – subsequent premium
The Informatica DTCC library allows creating and parsing the above formats of files as specific by DTCC.
Challenge In this document we will discuss another aspect of industry standard files and maintenance, this is error reporting. When the Informatica DTCC library (this typically applies to any Informatica library) parses an industry standard file it creates an xml representation of the content in the file as the output. Any errors identified during the processing of the file are also represented as xml. Similarly, when creating a DTCC file using the DTCC library the DTCC file is created and any errors that occurred during the process are captured as xml. Errors occurred here indicate any violations of the rules as specified by DTCC. The errors xml created by the Informatica library looks like the one shown below.
The screen shot above shows two errors, every error is encapsulated inside the node. In this case the errors occurred in line numbers or line index 2 and 3 respectively. Each reject node also indicates the actual reject code as specified by DTCC and the corresponding reject reason or description. Additionally, the specific value of the data causing the error and its actual position in the line is also shown. In order to get to the actual error and to get the full picture of the issues/rejects in this DTCC file, you will need to open the corresponding DTCC file as well. The corresponding DTCC file is shown here.
In this snapshot I have highlighted the first error which is actually line index 2 since the Informatica library does NOT count the data trak header starting with HDR.S. The blue dots indicate the erroneous lines. For now just consider line index 1 and the associated error. Using the position start and end I was able to decipher that the highlighted section which includes whitespaces and the number 11 is causing the error. As expected by DTCC this value should be numeric and not contain any spaces. Since the length of this field is 12, it should be 000000000011. Overall, a DTCC file can be pretty large and it could contain multiple errors per line and multiple rejects in a file. Tracking and identifying errors in this manner is time consuming, cumbersome and error prone.
GOAL The end goal is to leverage the output of the DTCC library along with the source DTCC file and provide a visual indication of each error that is present in the file along with the ability to look at each error in the DTCC file. Attached in the appendix is a DT service which will expect the following as input 1. The actual DTCC file (COM, POV, LNA, FAR etc) 2. The errors xml file created by the Informatica DTCC library And as output generates an HTML report will the error details of every line in the DTCC file. Each error is highlighted in red and each line with errors is marked in red font. By clicking the individual error the user can see the error details and by clicking the error count in the left pane all errors in the file are shown. Additionally all errors can be seen in one shot by clicking the error count shown as 23 in below example. The errors are displayed in tabular format which makes it easy to see all errors at once.
Screenshots On opening the HTML report
After clicking total error count in left navigation pane
Each error in the file is shown in tabular format; this makes it very easy to view all the errors. On clicking individual errors in DTCC file
On opening the HTML report for a valid DTCC file
How to use 1. Unzip the DT service in the zip file to any location on your machine. The unzipped directory should contain the following files.
2. Import the DT service to your local DT studio client using the import wizard if you want to see the project code more closely.
And finally select the name you want to import with, default is already selected for you, and hit finish once you are all set. This will pull the DT service into your local workspace. 3. Optionally you can skip step 2 and deploy this DT service in your local environment by copying the entire folder associated to this DT service under the ServiceDB location. Make sure you aren’t deploying the DT service with the same name twice. If you do need to do that then you will need to remove the existing directory in the ServiceDB and then copying the folder into the ServiceDB or overwrite the contents on the server. 4. If integrating with Informatica Powercenter and this is the typical integration we see then it is a good idea for the developer of the Informatica mapping to place this DT service in their local ServiceDB as well. This will allow them to select this DT service from the drop down when configuring the unstructured data transformation in Powercenter. 5. Finally, if you want to just use this DT service to create HTML reports and require the corresponding Powercenter code then import the attached workflow xml file into Informatica Powercenter through Repository Manager. This workflow assumes that the name of the DT service is DTCC_HTML_ERROR_REPORT. Use the import workflow feature in Repository manager to import this workflow into the repository. The defaults are set for the following mapping parameters in the Powercenter mapping as shown below. You can override these values by leveraging a parameter file.
$$DTCC_FILE is set to $PMSourceFileDir/DTCC.txt where the source file directory will depend on your installation of powercenter services. $$DTCC_LIBRARY_ERROR_XML is set to $PMSourceFileDir/errors.xml and $$DTCC_HTML_ERROR_REPORT is set to $PMTargetFileDir/DTCC_ERROR_REPORT.html In order to execute the workflow a dummy text file is needed under $PMSourceFileDir called input.txt as shown in the session settings below. This needs to have one row and one column such as the value 1 for instance. This ensures the DT service runs once.
Components of DT service This section provides a high level component explanation of the DT service.