Architecting for CRM

Published in January 2002

When correctly designed, an enterprise customer resource (ECR) can provide powerful customer data to your contact staff. This informative article, reprinted from DB2 magazine, details critical
steps in ECR design to insure optimal performance and cost savings … key questions to be addressed … and essential tools and tasks required.

The days of the unsophisticated, uninformed, easily led customer are long past. The information revolution has created a consumer base unafraid to challenge conventional business wisdom. Customers
see that they have a multitude of choices regarding delivery channels, product packaging, loyalty rewards, service levels, and, ultimately, price. The product-based differentiation that once
existed has given way to more subtle distinctions. Service, delivery method, and price have become the predominant factors influencing consumer decision making.

How has your business responded? Most likely you have developed new ways to meet customer demands through Internet commerce, sophisticated phone voice response systems, and customized packaging and
pricing. These kinds of techniques for managing customer relationships are becoming essential. However, they are expensive to implement and maintain, their actual return on investment may not be
readily calculable, and they may consume resources to the point of distraction. So when you do make the investment, you want your customer relationship management (CRM) systems to function
optimally. And because data sharing is such a key success factor, CRM solutions must be planned for at an enterprise level, rather than be permitted to evolve as non-integrated silos of

Customer Information Repositories

Successfully managing customer relationships means that your organization and, most specifically, your customer contact representatives know as much as possible about your customers. This
information need goes far beyond just knowing their names, addresses, and transaction history. Additional mission-critical profile information includes:

  • Recent customer contact history
  • Profitability measurements
  • Various customer scores (such as credit, propensity to buy, and segment)
  • Descriptive commentary
  • Campaign solicitations received and responded to
  • Summaries of product transaction trends

Traditionally, this type of information has been stored in data warehouses and used to make back-end analytical decisions and facilitate marketing initiatives. But it is essential that this genre
of data also be available in a “front-line” environment, where the actual customer interaction takes place.

This does not mean that front-line representatives should be given query access to enterprise data warehouses or business-line data marts. Such a gateway would introduce confusion and degrade the
quality of the customer contact experience. Customer service representatives do not typically have the necessary training and time to write fast and efficient queries against a data warehouse.

First-generation customer information repositories consist of centralized customer information files containing names, addresses, account identifiers, and basic profile information. These
centralized files are used primarily as a means of consolidating name and address maintenance and customer lookup in a transactional environment. Typically, the customer information within these
systems is limited and, in order to access information about the customer’s products, the user must leave the alpha index system and log into the application systems.

Second-generation repositories integrate customer information files with operational systems to support routine customer service. This type of solution provides a broader view of the customer than
first-generation solutions. It also controls information consistency, eliminates duplicate information entry, and is easily navigable. Second-generation systems typically have a relational database

Third-generation repositories – ECRs – integrate relational database design with operational systems (which may include first- and second-generation systems). In addition to name and address
maintenance, advanced features of ECRs include customer activity summaries, organizational hierarchy tracking, product hierarchy tracking, household information, customer-to-customer relationships,
referential relationship history, and point-of-sale trending and analytics.

As I’ve mentioned, typical second-generation customer information systems link the transactional systems used to manage the business end of a firm to a database containing the names and addresses
of all those individuals or organizations who may have some type of ownership relationship to the products in question. However, they lack insight into customer buying trends and behavior.

In third-generation systems, data warehousing capabilities overcome this shortfall; their massive, detail-rich repositories make it possible to conduct significant levels of analysis and research
on customers and their transactional behavior within the functional areas of the enterprise most concerned with such matters — chiefly marketing and finance.

The principal design difference between the ECR and earlier generations of customer information systems is that the ECR incorporates table structures from operational customer files and the data
warehouse, forming a hybrid of traditional entity-relationship concepts and warehouse star schemas.

The ECR permits the storage and retrieval of base-level customer information along with analysis-oriented extensions native to the warehouse.

Planning An ECR

Before designing and implementing an ECR, there are several fundamentals to examine about your business. These include:

  • The human processes that drive your business. Without having a clear picture of your organization’s everyday interaction with customers as well as a vision of where your firm needs to go to
    satisfy new demands, any effort to institute an ECR will achieve only marginal success at best.
  • The data necessary to support the ECR. As you begin to focus on individual customer needs, you will identify new requirements that will challenge existing schools of thought on what information
    should be available at the front line. Your organization will face tough questions about how much information is enough, what kind of data should be made generally available, and what kind of
    security access to provide. The art of relationship management is still evolving, and its precise informational needs have yet to be conclusively defined.
  • The technical delivery mechanisms that will enable the distribution and use of ECR data. New pathways, such as the Internet and intranets, now make it possible to develop and deploy intuitive
    user applications that can easily be modified and redeployed. These applications may serve as actual customer touchpoint interfaces when deployed for e-commerce. Existing platforms and legacy
    systems will need to be integrated with the ECR and/or replaced in order to realize the full potential.

Implementing An ECR

There are several ways to implement an ECR. Organizations that currently have both first-generation customer service file-based systems and a data warehouse can build an ECR as an extension to both
of these, rather than as a direct replacement for either. In fact, an ECR should not be considered a replacement for a warehouse, primarily because it is never opened up to ad hoc querying in the
manner in which warehouses are.

When considering whether to replace your first- or second-generation CRM systems with an ECR, you should evaluate the performance requirements, update-processing windows, and the scope of effort to
replace legacy application interfaces. If your organization doesn’t have the time or financial commitment to rearchitect these components, the alternative is to implement the ECR as a distinctive
information repository of its own.

Figure 1 shows how to implement an ECR without replacing your current systems: Legacy applications are linked to customer information files and displayed through some form of platform automation
interface. In an organization with a data warehouse, there would potentially be frequent feeds (nightly, weekly, and so forth) from both the customer information files and legacy applications into
the warehouse. Data from these systems would be extracted, transformed, and loaded via homegrown programs or programs developed with third-party tools. Data warehouses and unlinked data sources
(such as external lists) would receive and send data to and from the warehouse via a similar extract, transformation, and load (ETL) layer. (Note that the data warehouse is considered non-volatile,
meaning it is appended to rather than updated.)


In the environment shown in Figure 1, the ECR becomes an extension of both the customer information file system and the data warehouse by taking feeds from both systems. New customer records
collected in the customer information file system and/or legacy applications are inserted into the ECR, which then feeds them to the data warehouse. These customer records are scrubbed,
deduplicated, and householded at ECR load time, so that the ECR has consistent, reliable views of customer information.

The warehouse can pass data back to the ECR after enhancing it with demographic, psychographic, and customer score information, as defined by the ECR design. The ECR itself is considered
semi-volatile because segments of the database can be updated and deleted, while the remaining segments are viewed as appended only.

Access to the ECR would best be facilitated by Internet- or intranet-based applications deployed on customer representative desktops. This form of implementation enables you to enhance or modify
the applications with as little overhead, workflow impact, and expense as possible. In cases where customer files are currently accessed with 3270 screens, you can use terminal emulation in
conjunction with ECR access applications to provide “hot key” back-and-forth capability between the platforms. In time, as regular maintenance and replacement cycles occur at the legacy
application layer, you’ll want to migrate away from the customer information file system altogether (hence the dotted lines in the diagram). Ultimately, the entire flow of customer information
would be rerouted to begin at the ECR.

Critical Tools And Tasks

Your data delivery system can only be successful if you base it on a solid understanding of each of your organization’s key customer information processes and activities. So before designing
databases and interface layers or accessing applications, you’ll need to conduct a review of how customer information contributes to the daily business of the organization. It must be clear what
goes on with this information across the institution every day. (Don’t confuse this task with determining how an activity is carried out, which is far more useful when designing user applications
that must meet specific workflow requirements.)

Once you’ve mapped these activities and you’ve created a conceptual data model, you can create a matrix that will permit the development team to identify which customer information functions have
the highest implementation priority. This way, you guarantee that the first phases of the project will bring the highest return on investment, making it easier to justify subsequent phases.

The success or failure of your ECR depends to a large degree on the quality of your database model development process, so don’t fall victim to a rush job. The final designs should incorporate any
issues raised during discussions of the system’s value and vision as well as the contributions of managers and front-end users who leverage the database on a daily basis.

You’ll need to develop four types of data models when architecting your ECR:

  1. A business function model, which describes the functions, processes, and activities that depict what occurs in your organization relative to customer information.
  2. A conceptual data model, which provides descriptions of the data entities, relationships, and business rules necessary to understand the high-level information requirements of a
    customer-oriented organization.
  3. A logical data model, which takes the conceptual data model to the next level by converting those relationships that reflect many-to-many linkages into associative entities and adding
    business-named attributes to every entity in a normalized (nonredundant) fashion.
  4. A physical data model, which is the realization of the design concepts and business needs put forth in the conceptual and logical data models.

Concurrent to developing sound, comprehensive models, you’ll need to evaluate several key software components:

An RDBMS for the various database components of an overall CRM solution. Historically, operational systems have needed the inherent horsepower and scalability of engines like DB2, while the
analytical database field is replete with different products optimized for warehousing. Be certain that the selected platform is the one that fits the transaction/record volumes and specific
requirements for update frequency.

The good news for DB2 environments is that DB2 Universal Database (UDB) version 6.1 has registered 20 to 120 percent better performance (over earlier releases) for transactional applications such
as those involved in CRM work. DB2 UDB’s object-relational Extenders and built-in analysis functions also provide the integrated analysis environment necessary for enterprise-strength CRM.

Middleware to create the communication layer between the new ECR and existing systems and databases. This “glue” should comprise extract, transformation, and load tools as well as messaging
software (such as MQ Series). The programs these tools create will integrate and transport critical data between legacy sources and new operational and/or analytical repositories. When developing
the middleware tier, you’ll also need to consider appropriate placement of data quality utilities.

Luckily, a number of strong middleware options are available for DB2-based applications. IBM Visual Warehouse, and a number of integratable partner products all provide advanced capabilities for
transforming data from a variety of source systems. And DB2 DataJoiner provides access to heterogeneous distributed relational databases. Most recently, IBM’s new Enterprise Information Portal
promises to standardize access to data in a disparate collection of data sources.

Front-end tools, including tools for generalized free-form queries against the warehouse and data marts, Java-based application development toolkits for creating user interface programs that
provide operational access to the ECR, and components for an intranet infrastructure to distribute user applications. Brio, Business Objects, and Cognos all provide analytical tools suites tuned
specifically for DB2 UDB and integrated with IBM’s Visual Warehouse middleware.


Any endeavor with such a broad enterprise impact as CRM will undoubtedly run into roadblocks and challenges. Some of these will be purely political — issues stemming from stakeholder
conflicts and customer ownership debates between functional areas (such as marketing, sales/service, and so forth). These types of problems are tricky to navigate, but failure to face them can be
deadly to a project’s success. Seek the assistance of business sponsors and project champions to mediate these disputes.

  • Designing an overall architecture that allows rapid integration with legacy systems while creating an open-ended platform positioned for new customer contact channel growth
  • Achieving optimal performance of a system that is hybrid in nature (transactional and data warehousing)
  • Managing workflow around extraction, transformation, and load processes to ensure data quality at the ECR
  • Staying abreast of new forms of analysis becoming available to shed light on customer relationships
  • Driving business actions for the improvement of customer relationships when CRM systems uncover problems

You’ll also face difficult choices of whether to build or buy key components such as data models, middleware tools, and front-end applications. Use of industry analysts such as META Group, Gartner
Group, and Tower Group can be effective in concentrating your list of possible products and vendors.

How To Get There From Here

Your current environment probably contains pieces of an ECR in various stages of completion. Once you’ve assessed the current state of your environment, you’re ready to create a strategic plan.
This plan, together with the data models, database engines, middleware and front-end tools, will provide a CRM systems blueprint.

The requirements for access to and manipulation of customer information may vary across your enterprise. However, these requirements usually have a common basis in that they all use similar
customer data elements as a starting point. This combination of diverse aggregation and processing requirements with similar baseline information needs supports the value of a CRM strategy that
consists of a series of tactical implementation projects, with shared operational systems, product application files, and customer files, providing a common foundation.

Article reprinted with permission of Innovative from the Spring 2000 issue of DB2 Magazine
Copyright ©2000 Innovative Systems, Inc.

Share this post

scroll to top
We use technologies such as cookies to understand how you use our site and to provide a better user experience. This includes personalizing content, using analytics and improving site operations. We may share your information about your use of our site with third parties in accordance with our Privacy Policy. You can change your cookie settings as described here at any time, but parts of our site may not function correctly without them. By continuing to use our site, you agree that we can save cookies on your device, unless you have disabled cookies.
I Accept