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
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
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.
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.
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.
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.
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
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,
- 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
- 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.