REVIEWER: Vikrant Singh, enterprise data warehouse/lead developer for Credit Information Reporting (CIR) and ETL for Zions Bancorporation.
BACKGROUND: Zions Bancorporation operates full-service banking offices in eight western states. In addition to a wide range of traditional banking services, Zions offers a comprehensive array of investment, mortgage and insurance services and has a network of loan origination offices for small businesses nationwide.
PLATFORMS: Sun Solaris, UNIX servers; Windows client; Oracle database engine.
PROBLEM SOLVED: Zions Bancorporation offers many products, including commercial and construction loans, installment loans, savings and deposit accounts, mortgages and credit cards. To identify and monitor a customer's total relationship with the bank, we developed a system called Credit Information Reporting. Essentially, CIR combines the multiple obligations of a customer into a single lending relationship. It provides online debt and deposit totals at both the customer and the vending relationship level, making it possible to create credit presentations, management reporting or loan portfolios. My team and I are responsible for developing the ETL processes that support the CIR system. Using what we call "customerization," we take files from multiple disparate sources, including mainframe legacy systems and Oracle systems and convert them to a common format. We then put those files through an ETL process and populate the CIR data mart. The problem we faced was in the volume of data. The system processes 5.5GB of demographic and account-related data for Zions Bancorporation and three affiliate banks. We needed a solution that could handle all that data.
PRODUCT FUNCTIONALITY: Using DMExpress from Syncsort, we are able to take the 44 source files (5.5GB), run 23 to 25 shared tasks based on predefined, complex joining and filter criteria, and output eight files (two files per bank) with a total volume of 1.5GB - in less than an hour. The output files are then used as input by our ETL tool, which performs the transformation logic and creates four files (one file per bank). These four files are then combined again, using DMExpress, into a single file that is used for downstream processing.
STRENGTHS: The number one strength of DMExpress is its speed. Apart from CIR, we use DMExpress in other processes, especially for file capture or conversion. Wherever we find the need to process large amounts of data in a short amount of time, DMExpress is the obvious choice. The GUI is also a nice touch. Hand coding this type of project at the UNIX level would have been a humongous task. The GUI was invaluable in developing these tasks quickly.
WEAKNESSES: Initially we had minor but persistent issues. I remember one particular problem with job editing. When we would try to edit an existing task, then recompile and run it, most of the time, the job would fail; if I rewrote the job from scratch, incorporating my edits, it would run. Technical support was very helpful in solving the problem, but I do remember that being an issue.
SELECTION CRITERIA: As the customerization process was new to the bank, my team was tasked with selecting a solution that could do what we needed and that wouldn't choke on the large volume of data. It ultimately came down to DMExpress and one other product. The other product failed in a proof of concept that involved files of fairly large sizes and complex join and filter criteria. Unable to handle large volumes of data, the system would often crash before finishing. When it did complete the job, it took three to four times longer than DMExpress. After a successful proof of concept with DMExpress that processed 5.5GB of data with complex join criteria in less than an hour, we knew there was no other option. DMExpress got it done faster.
DELIVERABLES: Our customerization process starts with 44 source files. With DMExpress, we are able to join those files based on predefined joining and filter criteria, and ultimately output one consolidated file, which is then used downstream. From this process, online users are able to access a customer's complete lending relationship with Zions Bancorporation.
VENDOR SUPPORT: Syncsort's technical support is great. They have always been accessible, professional and knowledgeable. Because the product was new when we started using it, we worked closely and successfully with technical support to work out a few kinks.
DOCUMENTATION: The product is so easy to use that I never need the help documentation.
50 Tice Boulevard
Woodcliff Lake, NJ 07677
Register or login for access to this item and much more
All Information Management content is archived after seven days.
Community members receive:
- All recent and archived articles
- Conference offers and updates
- A full menu of enewsletter options
- Web seminars, white papers, ebooks
Already have an account? Log In
Don't have an account? Register for Free Unlimited Access