In-Depth

Export Development Canada (EDC)

I. Project Information

a. Company and division name: Export Development Canada (EDC)
b. Web site URL: www.edc.ca
c. Project designation: Data Warehousing
d. Brief explanation of the goals of the project:

As EDC continually expands its portfolio and its customer base, it also creates large internal demands for more and more information. Asked to find a solution to these growing demands, the data warehousing team realized that a warehouse to serve as a single version of the truth for the entire enterprise was needed. The goal was to build an agile, industrial-strength infrastructure that would provide easy access to the accurate data that EDC needed to manage ever-changing business needs. Documented business rules, data definitions and identified ownership would reduce risk and provide supporting analysis to a wide audience. Ultimately, it was expected that the consistency and accuracy of the information would foster collaboration and increase communication, allowing EDC to optimize operations across the board.

e. Brief description of the business risks involved:

The business risks were not associated with developing the data warehouse. Rather, the real risk was in not developing it at all. The company needed reliable information on two distinct segments: financial risk and customers.

Without the data warehouse, EDC would not have sufficiently detailed insight into its business dynamics, for example. EDC was determined to improve its ability to understand and manage customer relations; if the system had not been built, employees would have had a very hard time understanding those needs.

Another integral factor to the organization’s success is the timely delivery of accurate information to mitigate risk. Before implementing this data warehousing program, the method used for getting information was time consuming. Frequently, monthly reports weren’t available until 15 days after month’s end and required considerable effort to validate and reconcile data. With the new system, EDC now makes that same information readily available – consistently – by 8:00 a.m. every day on its intranet, ensuring that all employees have easy access to the information they need to perform their jobs.

f. Brief description of how the system helps users: The new business intelligence infrastructure lets users help themselves to timely and accurate information, with all reports available through the EDC intranet. Benefits of the system include improved data validations, enhanced data integration, improved efficiency (freeing up resources for higher value-added activities), improved documentation and broader accessibility to information.

II. Organizational Objectives

a. What short- and long-term benefits did the organization achieve from the project? Did the solution meet the projected goals for saving time and money? How were benefits measured? Was the system mission critical to the organization?
b. Describe the business purpose of the new system.
c. Describe the features of the new system.
d. Explain the functions of the new system.
e. Who were the internal sponsors of the project? Which officials or groups were opposed to developing the application? Why?
f. Were users of the system involved in the project during the planning and development phases? If so, how?
g. What were the greatest challenges in completing this project? How were they overcome?
h. Were the goals changed as the project progressed? If so, what were the changes and why were they made?

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.


III Category

Please indicate the Innovator Award category as listed below. (Categories reflect the editorial organization of Application Development Trends. Many projects can reasonably be fit into multiple categories. Try to fit your project into the category that best reflects the tools and technologies used. Don’t worry about whether you picked the right category; we will review all submissions carefully and will make changes where appropriate.

Data Warehousing

Emphasizes the design and development processes and tools used in enterprise data warehousing projects, including: data mining tools, online transaction processing systems, data extraction and transformation tools, database management systems, universal data management systems, query and reporting tools.

THE PROJECT


IV. Methodology/Process

a. Describe how productivity tools or techniques were used in the project.
c. Was a formal or informal software development life-cycle methodology employed? If yes, please describe it.
d. What formal or informal project management methodologies and/or tools were used to manage the project? If used, please describe how.
e. Were software quality metrics used? If so, what were they, and did using them significantly help the project?

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.


V. Technology

a. What were the major technical challenges that had to be overcome to complete the project successfully? How did the team respond to those challenges?
b. What software tools, including databases, operating systems and all development tools, were selected for the project? Why were they selected over competing tools? What process was used to select development tools and software platforms?
c. Describe the overall system architecture. Were elements of the technical infrastructure put in place to support the new system? Please describe.
d. What characteristics of the tools and technologies used were most important in achieving the business purposes of the system?

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.


VI. Project Team

a. What was the size of the development team?
b. Describe the software development experience of the team members.
c. What was the composition and skill level of the team? Did development teams require training to work with the technology?
d. Please list team members and their titles.
e. How many person-months/days did the project take, and over what calendar time frame? Was a formal schedule created at the start of the project? Did the project stay on schedule?
f. Did management and the user community consider the project a success?
g. If you had to do the project over again, would you do anything differently? If yes, please explain why.

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.

Featured

Upcoming Events

AppTrends

Sign up for our newsletter.

I agree to this site's Privacy Policy.