Just from the time savings and data quality improvements, EDC has already realized a full return on its investment for each of the implemented data marts. Other initiatives look similarly promising, as the SAS system providing the platform for the data warehouse and various data marts has proven to be reliable, robust and comprehensive. In addition, the data captured for one data mart can be leveraged for others. The corporate performance balanced scorecard, for example, is estimated to be two to three times more effective than the earlier developed exposure data mart. Further, the data required for the scorecard is now beginning to be exploited as the basis for a comprehensive “customer view” in support of marketing and business development efforts. Nonetheless, the team is not content with just these initial results. Upcoming initiatives will be able to leverage previous investments, better inform the business and, it is expected, provide an impetus for innovation as data becomes information that can be quickly accessed by all staff.

Before the implementation of the data warehouse, generating the three monthly risk management reports required 257 subprocesses, including steps for data retrieval, data manipulation, data verification and rework. Now, two-thirds of those processes have been eliminated, and 95 percent of the remaining steps have been automated. Not only has the streamlining freed one full-time employee to do more strategic work, such as developing EDC’s overall risk management framework – work of much higher value than the reconciliations and error-checking she did before – it also reduces the potential for error by eliminating many points at which the data had previously touched and manipulated.

The number of data sources feeding the system has also been cut by two-thirds, from 29 to 12, with eight of the 12 now being direct feeds. These automated feeds – which further limit the potential for error common to a manual extraction process of copying and pasting information from a spreadsheet – help EDC efficiently and effectively meet its own stringent standards for data quality.

In achieving the self-service objective, EDC has succeeded in providing relevant, timely and accurate information via tools and services that allow users to help themselves. All reports are now delivered through the EDC intranet, and the number of requests being met has more than tripled since the program’s start – currently more than 10,000 hits per month; that number is expected to be doubled by the end of 2005.

Other benefits include improved data validations, enhanced data integration and improved documentation through the custom developed metadata capabilities using SAS software. Previously, data was taken from system extracts and then manually fed into the system, meaning it had to be reviewed, reconciled and, in many cases, corrected. With the new rigorous processes of the data warehouse, errors are identified earlier and are fully quantified, enabling correction and elimination of future problems in the data source. Continued tracking of errors has also revealed an 80 percent reduction in errors. Further, this number is expected to continue to decline, translating into increasingly higher-quality information delivered to managers and executives – and a clearer picture of the company’s overall risk exposure. It is now correct to say that the documented data truly supports one version of the truth at EDC.

The key internal sponsors of the project were business leaders of various areas, including the vice president and chief risk officer, the vice president of corporate planning and the vice president of marketing. In addition, the data warehouse strategy was endorsed by all senior executives as well as the board of the company.

No one was opposed to the development of the application, but there were conflicts with resource availability. In addition, Corporate Business Systems and Marketing had gone through the planning and design phase in 1997 and again in 2000 on the Balance Scorecard and deemed it too complex and risky to implement. Therefore, some were skeptical that this program could be accomplished successfully.

The developers worked very closely with the end users during the planning and development phases. At the beginning, for the balanced scorecard, for example, there was just one person who knew the business rules for reporting, so that person was heavily involved in the process, as were other users for different parts of the project.

One of the biggest challenges in completing the project was gaining momentum and support at all levels of the agency. Team leaders believed the key would be quickly deriving measurable business benefits from the warehouse.

One of the issues they faced early on was how to spread the notion that data is an asset to the organization. Putting a dollar value around this asset and expressing the value of this data in a tangible way would go a long way toward resolving the inevitable data quality issues.

They hoped to foster corporate awareness and dialogue about information quality by illustrating a real-life situation where quality issues are effectively addressed when this technology is applied.

So they decided the first business use of the warehouse would be to implement a new, monthly risk management report that analyzes commercial obligor credit exposure. As part of a three-report package distributed monthly to the board of directors, this information goes to the heart of EDC’s mission – delivering financial and insurance services that other lenders often deem too risky. (See the answer to question II.a. for a detailed listing of the benefits EDC has realized.)

After meeting its initial goals, the project continued to evolve over time. As others at EDC began to see the potential benefits of having access to additional information, more reports were requested and developed for senior executives and other employees at the company.

The data warehousing methodology and ETL processes were developed internally and built around a component architecture so that macros and other code could be reused. All field validation of data being loaded into the data warehouse was table-driven, meaning that as new data was being added to the warehouse only the table needed to be updated to validate the new fields instead of making changes to the ETL programs.

EDC used a formal software development life cycle tailored for its purposes by borrowing different aspects of various system development life cycle (SDLC) models that have been used in the last 20 years. The usual (SDLC) stages of a project were followed such as project planning, requirements definition, design, build, integration, testing, and acceptance. However, EDC tried to time box each delivery of a project into six months. Some projects have been scoped out to be eighteen months in duration but are split into multiple phases and built incrementally to deliver value to the business more quickly. The team involved business users in testing at multiple stages to identify potential deviations to user requirements.

EDC’s IT governance process – an advanced system that is supported by three distinct bodies: the technology initiatives group, the information systems steering committee and the board of directors – brought structure to the project. Together, these groups ensured that goals would be reached. The governance process requires that proposed projects be defined in detail before implementation begins. The various groups scrutinized every element: the size, required resources, possible alternatives, identifiable risks and possible mitigations. Project organization, responsibilities, deliverables, costs, benefits, financial analysis and overall project plan also received careful study. Once reviewed and approved, the business case became the charter plan for implementation. In November 2001, the information systems steering committee endorsed the data warehouse strategy. Calling for a multiyear, multimillion-dollar commitment, the strategy addressed current concerns while simultaneously positioning the corporation for the future.

Particularly aggressive in a number of areas, the plan took the uncompromising position that all future corporate and management reporting (as opposed to operational reporting) would be sourced through the data warehouse. Further, the team set out to continue making tangible deliveries to the business while constructing the underlying infrastructure. Other than seed money to establish and maintain the base infrastructure, there would be no funding for data mart development. Such funding would come only from business sponsors, thus ensuring alignment with business imperatives. Similarly, the team would not retrofit any existing data mart, but would integrate it into the foundation layer whenever a new project involving that specific data mart was approved.

The team set exceptionally high standards for data quality and business-rule accuracy, operating on what it called the “zero/100 rule”: Information had to be 100 percent accurate, or it would not be included in the data warehouse or data mart. There was absolutely no tolerance for error or inaccuracy, nor was a data mart to be deployed if there were any outstanding issues or questions regarding its structure or use. These mandates are fully backed by EDC’s general IT governance processes as well as by project management best practices. The zero/100 rule helped the project significantly by ensuring that the data was accurate and useful to people, speeding up adoption of the project.

With this data warehouse initiative, the technology was the easy part. Using SAS, EDC replaced many disconnected sources of data – application extracts, personal spreadsheets, MS Access databases – with an integrated structure that supports reliable and robust tools, processes and procedures.

EDC managers selected SAS data warehousing and business intelligence technologies to meet their demands for a complete strategy for information management. SAS was chosen because of its many capabilities, including facilitating customer self-service, providing quick access to last-business-day information and its ability to serve as a single, trusted source for enterprisewide information.

The operating system and database platform are both from Microsoft: Windows 2003 and SQL. Windows 2003 was a pervasive system. The database platform, Microsoft SQL, was chosen for two reasons: 1) because EDC managers wanted a more robust repository and 2) the team wanted to conform to some of the technical architectural directions and infrastructures that were already in place at EDC for the sake of consistency.

For the overall system architecture, data is gathered from more than 12 mission-critical applications residing on either the mainframe, AS/400 or MS SQL server databases, including raw data feeds. Components include those raw data feeds, SAS ETL Studio, SAS/IntrNet, SAS Enterprise Miner and OLAP cubes.

Data marts include:

• Credits Administration System (CAS DM) – a large collection of data on EDC’s short-term insurance business. It contains five years of history on credits extended to buyers worldwide and is used to assess risk, develop pricing and risk models, and report on portfolio composition and changes.
• Market Risk Management – a tool that consolidates and reports on corporate treasury exposures and positions in order to inform and fine-tune daily trading operations. The availability of vital information prior to executing trades rather than after significantly enhances employees’ decision-making processes, giving them a competitive edge that benefits not just EDC but its customers, too.
• Loans Provisioning – aggregate information about EDC’s foreign loan portfolio. It is used to help understand the extent and composition of risk as well as to help set risk appetite and loan-loss provisioning.
• Corporate Results – complete balanced scorecard summary and detailed information pertaining to performance against business targets as well as considerable customer-related information.
• Corporate Exposure – a comprehensive view of risk exposures by country, industry and obligor. Used to provide reporting for the EDC board of directors and to establish and monitor compliance limits, it includes consolidated information from all business lines.

These data sources feed the corporate data warehouse, where transformation and management processes take place and a metadata catalog is created. Because the data warehouse is the main vehicle to aggregate and then disseminate corporate information, quality issues demand a consistent and reusable process and methodology. Mapped data flows and processes, as well as a defined approach to monitoring, measuring and quantifying information, ensure consistency. With the exception of the CAS DM, the foundation warehouse and all data marts are refreshed daily.

The data warehouse team also developed two innovative approaches to enhance development and operation:

• A parameter-driven tool to define significant parts of the ETL process, resulting in speedier development and greater consistency.
• A method for generating dynamic reports in the EDC intranet environment, significantly reducing the maintenance costs when minor changes to the overall warehouse are introduced to existing data marts.

The architecture also requires the source application systems to push data to the warehouse rather than the more common practice of the data warehouse pulling data from the applications.

This design has enabled EDC to replace hundreds of independent data marts with an integrated structure that supports reliable and robust tools, processes and procedures. Now, one trusted source of information facilitates customer self-service and provides near real-time data.

Initially, a core team of six people was established to support all aspects of the data warehouse initiative (e.g., operations, strategy, support, developing and planning), supplemented with one to two contractors on short-term engagements.

The team’s skill set is varied and robust. Most have had experience with mainframe application development and business analytics in a data warehouse environment. The data architect ensures that the central repository is optimized for more general use with other applications.

EDC’s strategy for business intelligence and data warehousing was developed and approved in early 2001. The team then set out to build the infrastructure to support the data warehouse while simultaneously working on two other data mart developments. The infrastructure for the data warehouse and two data marts were delivered in November 2002. Since then, the team has delivered four more data marts and continues to work on developing others.

Based on the strategy that was approved in 2001, EDC has actually delivered more than was first anticipated. During the period from when the strategy was initially approved by executives in 2001 until today, each initiative has been handled under its own business case with its own business plan, and in every instance, the objectives have been met.

As noted in the answer to question II.a, the implementation has been a resounding success. Indeed, the process could be used as a blueprint for future IT implementations at EDC. As one team member shared, this is the only time during the 30 years of experience of the development team that the strategy was articulated and then adhered to at a 90 percent level. The fundamental thrust and directions guided the evolution of all the data marts and architecture from the beginning, and now in 2005, EDC has achieved the majority of what it set out to do. This is a great example of congruence between a strategic objective and its realization.