Data mart Development and Enterprise Reporting
Data mart and Enterprise Reporting builds structure for future growth
Seligman Data Corp., the shareholder service agent for the Seligman Group of Funds, processes all transactions for more than 50 mutual funds managed by J.&W. Seligman, one of the oldest and most respected investment banks in the United States.
Business Challenge
Seligman Data Corp. (SDC) had an aging mainframe reporting system based on Dbase2 and Computer Associates’ EZtreive reporting system. This system produced text-based reports that were printed and distributed through interoffice mail. SDC recognized the need for an upgrade, and selected Oracle 8 as the new database platform and Crystal Reports as the l reporting tool. With these two decisions made, the task of integrating the database and reporting system now had to be tackled.
Some of the key objectives that needed to be met:
- A multipart mainframe extract needed to be loaded each night into Oracle.
- A complex set of dependencies and checks needed to be done automatically and reliably before loading the database and before generating reports.
- Reports based on the previous day’s transactions needed to be available by 7am, even though the mainframe extract wasn’t available until 3:00 a.m.
- Weekends and holidays posed special challenges because files can be delayed and servers are often off-line.
- Notifications need to be sent to multiple people regarding the progress of each night’s load, via email and pager.
- The data mart needed to integrate with various custom applications, including a complex CRM system.
- Crystal Reports generated from Oracle needed to be exported to text for a legacy report viewing system.
Data Warehouse Center Solution
Meeting these key objectives required a reporting solution that could follow a complex, multi-branching flowchart of steps. Seagate Info offered a cost-effective, event-driven reporting choice that incorporated Crystal Reports. A series of batch files were created to load the database, each dependent on the previous one. Once the database was loaded, a final batch file triggers the reports to process, but only if a series of checks are met. Along each step, Seagate’s notification feature was used to send email and pager messages to administrators.
Seagate Info’s event-driven scheduling capabilities were used to build in a high level of fault-tolerance. Holidays, weekend processing, database failure, and corrupt/late file transfers were all dealt with appropriately. The new system also handled unplanned events, such as those occurring after a disaster.
This integration needed to tie the new CRM system to the data mart. Since both systems relied on Oracle 8i, Oracle’s DB Link feature was used to seamlessly tie the two separate systems together. Data on each system would appear to be local, and could be replicated if necessary.
Seligman leveraged Data Warehouse Center expertise in data warehousing to help fit the loading and reporting requirements into the very narrow, four-hour nightly window. A combination of indexing, summary table creation, script optimization, and performance tuning were all used to decrease total processing time.
Integrating the text-based report viewing system into the Enterprise Reporting system was done through Seagate’s scheduling interface. Reports are scheduled to run nightly, and automatically exported to text format to a shared directory using a specific naming convention.
Return on Investment
The data mart and reporting systems continue to grow in size, scope, and importance. Recently, all of the old phone system reports were upgraded to the latest version of Crystal and are now being distributed through Seagate Info. What started out as a “nice to have” project several years ago is now considered mission-critical.