Informatica DTCC library error report - Informatica Marketplace

Informatica DTCC library error report - Informatica Marketplace

Informatica DTCC library error report This document describes an enhanced HTML error report which can be created by leveraging Informatica’s DTCC libr...

697KB Sizes 0 Downloads 9 Views

Recommend Documents

Informatica PowerExchange for IDMS - Informatica Marketplace
Informatica PowerExchange® for IDMS is part of Informatica's extensive PowerExchange family of universal data access an

Supported Versions - Informatica Marketplace
100 Chandler Bing 26/06/1978 New York. 01/01/2009. Now the mapping will load the historical data in SCD Type-2 dimension

Supported Versions - Informatica Marketplace
Chandler Bing. 2000. 300. 1200. 300. Joey Frank. 2000. 300. 1350. 350. EMP_Name. EMP_Year Expenses_Type Expenditure. Rac

Installation Guide - Informatica Marketplace
Set the group sticky bit on individual Fast Clone executables to enable them to run .... as a 32-bit Oracle source datab

Touchpoint Fact Sheet - Informatica Marketplace
Fact Sheet. iWay Touchpoint adapters provide pre-packaged multi-step processes that integrate standard business document

Informatica Data Services - Informatica Knowledge Base
15.10.2010 - Dieses Produkt enthält Software, die von Mozilla (http://www.mozilla.org/) entwickelt wurde, Software ....

Evaxyx Siperian BPEL Framework - Informatica Marketplace
The code is more or less meaningless to anybody outside the development team. A typical enterprise application contains

landbank - Informatica
Farmers and fisherfolk might not seem a prime customer base for a major bank, but they are for the Land Bank of the Phil

Mobile ID - Informatica
Determine if a phone number collected in call centers, marketing campaigns, lead ... Mobile ID from Informatica fully co

Wipro MiDAS - Informatica
Aug 22, 2011 - Wipro MiDAS: The solution for high-tech manufacturers. Wipro Technologies, a leading analytics and ... Wi

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.

The main code resides under Main.tgp which calls the following components in shown sequence. 1. DTCC_FILE_PARSER – this is a component of type parser which parses the DTCC file and extracts the relevant information from each line such as line index and the entire line content and stores it in an internal xml reference 2. DTCC_ERROR_XML_PARSER – this is a parser component which parses the error xml file created by the Informatica library and extracts the relevant error info from each reject and cross references that with the internal xml reference thus combining error and non-error lines into a single xml reference 3. CreateHtml – this is a serializer component which takes the internal xml reference and converts it into the target html result file. The html contains the css and javascript code inline. All parsers are placed Parsers.tgp and the serializer is under Serializers.tgp. Variables.tgp has all the variables used in the DT service and finally Keys.tgp carries the key definition for the internal xml reference.

Known limitations   

The HTML report currently works well with Google chrome (version 43 onwards). The HTML report will not render well with IE or Mozilla Firefox or Opera. The formatting of the HTML report is controlled via inline CSS and javascript is employed to support the on-click functions. This cannot be changed unless the underlying code is modified. The HTML report is tested with smaller DTCC files (< 10 MB) and will not render well with larger DTCC files.