The Last, Best Place to Integrate the Enterprise: Why Organizations Need an Enterprise Data Warehouse

Published in TDAN.com July 2000

Abstract. Today’s information-age enterprise is in a constant dialog with suppliers, customers, competitors, markets, resources, and government agencies. The enterprise develops a number
of processes to develop, create, market, sell, and support its goods and services. Information systems support these processes. The enterprise mirrors its environment, and since its environment is
complex, the enterprise is complex. The enterprise also needs to measure its performance in its environment in order to survive, adapt, and grow. While the large, mature enterprise sometimes seems
to be a coherent whole, it is actually highly fragmented because of six main forces: (1) deregulation, (2) functional organization, (3) innovation, (4), mergers and acquisitions (5) technology
accretion, and (6) a lack of blueprints for information systems. Even though enterprise fragmentation makes enterprise measurement difficult, the enterprise must still measure itself to adapt and
survive in its environment. There are five main ways to measure enterprise performance: (1) operational systems, (2) independent data marts, (3) application integration, (4), the operational data
store and (5) the central data warehouse (with dependent data marts). While each of these five components may have a place in the decision-support environment, the central, enterprise data
warehouse (with dependent data marts) is the last, best place to integrate and measure the enterprise.

Have you gotten DSL service recently? Was it a good experience for you? About six months ago I decided to take the leap to DSL from plain, old dial-up Internet service. We were fortunate enough to
live close enough to a central office to qualify, so I requested DSL service from my local phone company and Internet Service Provider. Getting the service was not easy or simple. To make a very
long story short, it took many phone calls and several missed service calls to get the service. I had to call several numbers to get the status of the installation. People in one group didn’t
know the status of the installation since people in another group were doing the work, and the systems didn’t make the status visible to all parties. This is just one example of a business
process that is fragmented and multiple systems that don’t interact. I have come to expect poor service and processes from large, mature organizations.

This article examines the impact of business forces on the enterprise and its information systems. It looks at six forces that fragment the enterprises and common ways to measure performance.

Conversations in the Ecosystem

Fragment. n. 1. A part broken off or detached. [i]

Today’s information-age enterprise is in constant dialog with suppliers, customers, competitors, markets, resources, and government agencies. The enterprise develops a number of processes to
develop, create, market, sell, and support its goods and services. Information systems support these business processes, and the data produced by these systems represent the enterprise’s
conversation with its environment and with itself. However, while the processes may look pristine on paper, they are implemented through a series of handoffs between departments and systems. In
other words, the enterprise and its processes are fragmented.

A number of forces, structures, and practices fragment the information-age enterprise. [ii] This article will concentrate on

  • Deregulation
  • Functional Organization
  • Mergers & Acquisitions
  • Innovation
  • Technology Accretion
  • Lack of blueprints for technology, applications, and data

Deregulation

Apart from recent regulatory action in the United States (Microsoft, MCI Worldcom/Sprint), for the last 20 years, government in the United States and Britain has been reducing its role in
regulating activities in the market. [iii] It has been allowing market forces a more free play in the economy rather than governing them by
regulation. Several industries have been freed from a number of government regulations, including airlines, financial services, telecommunications, and, soon, electric power. As a result of less
government regulation, companies have been able to set their own prices and schedules (airlines), operate across state lines and offer additional product lines (financial services), and force
companies to interoperate (telecommunications).

Deregulation has made it easier for organizations to acquire and merge with organizations, offer new products, and enter business segments that were previously off-limits to them. It has split up
companies’ value chain, introduced more competition, encouraged innovation, and reduced prices. One consequence of deregulation is new products. New products mean new data elements in
systems, new tables or files, or new systems.

Functional Management

In the GM tradition, large, mature organizations are typically organized and managed by functions such as Sales, Marketing, Operations, Human Resources, Accounting & Finance, Engineering, etc.
[iv] Figure 1 shows this classical organization. People, budgets, and systems are grouped together to perform part of a given process. However,
business processes cut through functional areas; groups and systems must hand off work to people and jobs that belong to different functional areas.

Figure 2 shows an example of the handoffs that occur between functional areas. This kind of diagram has become so common that it has a name—swim-lane diagrams. [v] Each handoff is an opportunity for delay or miscommunication.

 



Figure 1. Classical functional organization.

 



Figure 2. Business process that crosses functional boundaries.

There are alternate forms of organization, which may integrate business processes more than a functional organization. These include cross-functional teams, matrix organizations, and
process-oriented organizations. However, the classical, functional organization seems to be most common in the day-to-day operations of the enterprise.

In many cases, the IT organization mirrors the business function. Sales may have its own IT function, as does accounting, human resources, and line functions. The fragmentation in the business
process is reflected in the information systems that support it.

Mergers & Acquisitions

In the United States it seems that almost every other day we read about another merger. This month’s merger news relates to UAL and US Airways and MCI Worldcom and Sprint. Mergers &
accusations have become more and more popular in a number of industries, especially telecommunications, oil & gas, pharmaceuticals, finance, and automobile manufacturing—some of the same
industries that have been recently deregulated. Recent mergers include Exxon/Mobil, Worldcom/MCI, SBC/Ameritech, Travelers/Citicorp, Bell Atlantic/GTE, AT&T/Tele-communications, Nations
Bank/Bank of America. The list goes on.

Organizations may acquire or merge with other organizations in order to fill out a product line, acquire employees, extend geographic reach, or respond to deregulation. [vi] Organizations will likely have different cultures, policies & procedures. They will almost certainly have duplicate, different information
systems.

When two companies merge, management has a number of decisions to make regarding information systems: Which systems should we keep? Which ones should we throw away? Which ones should we replace? In
almost all scenarios, data must migrated from one system to another. Data elements and formats will likely be different. Poor data quality is likely to increase, and custom interfaces are yet
another opportunity to corrupt data in its movement from one system to another.

Innovation

There has been a stream of innovation from organizations, especially in the form of new products. As an example, a listing of telecommunications offerings include numeric paging, alphanumeric
paging, paging acknowledgement, two-way paging, ISDN, caller-ID, call waiting, call return, call block, analog cellular, digital cellular, cable modems, wireless.

Innovation is especially apparent in the world of information technology. Over the last 30 years, a steady stream of new technology has flowed from places like Palo Alto (Workstation, Icons),
AT&T (UNIX, C, C++), Redmond (Windows), Armonk (PC, RDBMS), Sun (Java), and organizations have eagerly adopted these technologies. At the risk of oversimplifying, today’s organizations
implement information systems from four main (commercial) streams of technology, including

  • mainframe-based computing (MVS/CICS/VSAM/JCL/COBOL)
  • LAN-based computing (XBASE, Clipper, Novell, Access)
  • Client-Server (UNIX, C, C++, RDBMS, PowerBuilder, Visual Basic)
  • Distributed objects (All of number three plus CORBA, COM/DCOM)

New products typically require either new support systems or a change to an existing support system. They also require training and new policies & procedures. The new product may not be
compatible with old products and standards.

Technology Accretion

Technology streams includes data management technology such as

  • Flat file/Application (VSAM, DBASE, C-ISAM)
  • Non-relational database management systems (IMS, IDMS, ADABAS)
  • Relational database management systems (Oracle, DB 2, Sybase, Informix)
  • Object database systems (Poet, Versant)
  • Desktop database system (Dbase, MS Access, FoxPro)
  • Multidimensional database system (Essbase, OLAP Services, Oracle Express)
  • High-performance technology (NCR/Teradata, IBM SP2/UDB EEE, Sybase IQ)

While organizations have eagerly embraced new information technology, systems implemented in older technology tend to remain in the application portfolio, especially if they are vital to the
organization. In large organizations it would not be uncommon to find 1960s-style COBOL programs and Essbase. New technology gets layered on top of old technology.

Diverse technologies tend to have different traditions, APIs, protocols, and supporting technology. Vendors will retrofit a technology to work in a different stream. Can you imagine MVS (or OS/390)
running TCP/IP? Or a client/server version of IMS? Strange but true.

The end result of technology accretion is a patchwork of information systems built in different, incompatible technologies.

Information System Blueprints

In the rush-to-implementation, architecture documents of a system are often seen as “documentation”—an optional evil that merely describes, after the fact, what the project team
has built or assembled. The conception of the system takes place in meetings or in someone’s head, and it’s documented on a whiteboard marked “DO NOT ERASE.”

Some modeling tools only have one level of abstraction so that the logical view of the system is the same as the physical view of the system. High-level blueprints or models of a system don’t
have enough detail to guide construction so low-level models tend to be the models that are maintained, especially source code and Data Definition Language (DDL).

A major trend in the last several years has been buying packaged systems rather than building custom systems. When an organization buys a packaged system, it buys its data architecture too.
Packaged systems tend to hide or obscure their data architecture and the linkage to higher level models or business processes often don’t fit very well.

Information systems should be consciously modeled before they are built, and there should be several types and levels of models. A lack of middle-level models produces information systems that are
misaligned with business processes.

Consequences of Fragmentation

The results of the forces of deregulation, mergers & acquisitions, innovation, technology accretion, and the lack of information systems blueprints leads to a fragmented enterprise and
fragmented information systems. The information systems environment of today’s large, mature organization is characterized by a

  • Diverse mix of technology
  • Bias towards low-level models (source code & DDL)
  • Plethora of (custom) interfaces among systems
  • Need for diverse skill-sets to support different technologies
  • Poor quality data
  • Redundant data & data formats
  • Poor or incomplete data architecture

Files and tables for customer and product (and many others) are likely to be found in many different parts of the organization. Data sources will have different formats for same data, different
meaning for the same data, different names for the same data. In many cases business rules won’t be enforced by systems.

Packaged systems hide or obscure their data architecture, and many ERP systems are a special case of hideous data elements. Some systems hide or obscure their semantics and make it difficult to get
data out.

To be sure, some technology does help integrate systems such as ODBC, ANSI SQL, POP3, EDI, XML, but most new technology fragments technologies and systems.

Measurement must occur with data, and the fragmented systems environment makes measurement difficult since data from parts of various processes is spread out all over the enterprise, in different
formats, in different technologies. Still the enterprise must measure itself.

Measurement

Organizations must measure their performance in order to adapt to their environment. Measurement provides feedback to the organization. In addition, the Securities and Exchange Commission (SEC)
requires reports for many organizations. Finally, financial markets demand to know the prospects of an organization.

Measurements of the enterprise and its processes fall into three broad categories [vii]:

  • Financial
  • Timeliness
  • Quality

Financial measurements involve common monetary metrics such as revenue, earnings, customer & product profitability, and Top 20/Bottom 20 (stores, customers, salesperson, etc.). Timeliness
measurements may involve on-time delivery and turnaround time. Examples of quality measurements include customer complaints, defective parts, and amount of scrap work.

Measures may be leading or lagging indicators. Lagging indicators give a picture of what happened in the past, such as quarterly revenue (determined after the quarter is over). Leading indicators
give a clue to what might happen in the future such as housing starts or the number of accepted credit applicants.

Five Ways to Measure

There are five main information systems that measure performance. They include

  • Operational Systems
  • Operational Data Store
  • Independent data marts
  • Application Integration
  • Central Data Warehouse/dependent data marts

Operational Systems. Operational systems support the day-to-day activities of the business, but they usually have a number of built-in reports, which provide common metrics for how
things are going with the functional area the application supports.

However, there are a number of issues with reports in operational systems.

1. Built-in reports may not be flexible enough for the business communities reporting needs. Ad hoc queries may be out of the question because

2. reporting queries access the same data structures or database system that support the day-to-day business so there may be performance issues.

3. Operational systems generally don’t manage data for some of the more interesting reports that combine data from multiple areas such as employee productivity (e.g., sales & employee
hours worked). Business analysts, especially financial analysts, tend to combine information from various systems into an Excel spreadsheet. However, they must also usually rekey some data, since
reference data in their spreadsheet is not maintained. This practice results in duplicate, unmanaged data, and data quality issues.

4. Tables in operational systems tend to be complicated to query (if in a relational database system).

5. If the system uses older data management technology or flat-file technology, the system may require a third generation language to access system data sets. Business users often don’t have
this kind of skill.

Independent Data Mart(s). Independent data marts help resolve issues of performance and complexity of data structures by moving data into a separate system that receives data
directly from operational systems. Data marts often implement “star” schemas consisting of “fact” and “dimension” tables. Their “fact” tables
correspond to activities in the organization’s value chain such as sales, employee payroll, and deliveries. Stars may be implemented in relational database technology (Oracle, DB2, Sybase),
or in multidimensional database technology (Essbase, OLAP Services, Oracle Express). A number of OLAP reporting tools support both the relational version of data marts (ROLAP) or the
multidimensional version of data marts (MOLAP).

There are a number of issues with multiple independent data marts. Multiple data marts that receive the same or similar data may have conflicting values, depending on how or when the data was
extracted or manipulated. There may also be issues of the meaning of the data. Multiple data marts may mean multiple extracts from operational systems, thereby putting an extra load on the
operational system. Multiple extracts add complexity to scheduling and dependencies among extract jobs. Some extract issues can be resolved by a staging area—which may start to look like an
operational data store. If the data management technology doesn’t support drill-across (combining data from multiple systems), data from multiple areas may be combined through application
integration reports.

Application Integration. Application integration (in a reporting context) involves a program that combines data from multiple systems into a report, e.g., a sales data mart and a
payroll data mart. If the systems are implemented in different technologies, the reporting technology must use multiple APIs (such as some dialect of SQL and OLE or DDE). These programs require
knowledge of multiple APIs to develop and maintain programs. Finally, low-level programming is not nearly as efficient as standard high-level reporting tools.

Operational Data Store. The operational data store offloads processing for reporting from the operational systems’ platform onto a decision-support platform. Data is copied
into a standalone system from operational systems. The simplest cases of a “dump and load” may not change data structures. Other configurations may optimize data structures for
reporting. An Operational Data Store could integrate data from several systems. While it may be able to satisfy requirements for multiple subject areas, it does not have a lot of historical data.
It cannot satisfy reports for trending over “long” periods of time. Since it does allow for some integration, the Operational Data Store is sometimes called the “leading edge of
the data warehouse.”

Central Data Warehouse. The central data warehouse stores a “single version of the truth.” It receives data from multiple operational systems (or an operational data
store). It is the source of all reporting, and it may feed multiple data marts. Data is organized by subject area, which allows for data integration. The warehouse also stores historical data,
which allows for trending over time.

There are also a number of issues in creating an enterprise data warehouse. It is difficult to get commitment from all relevant parts of the enterprise to participate in an enterprise effort. The
effort must quickly produce something of value else the entire project may collapse of its own weight. Finally, the project must find the right balance in the tension among the realities of the
data, the needs of a business unit, and the enterprise perspective.

Conclusion

Today’s enterprise is fragmented. Its markets and resources are fragmented. Its processes and information systems are fragmented. It is constantly having conversations with customers,
markets, and resources, and it must measure its performance to survive and adapt. However, it’s difficult to measure the enterprise and its processes because the data that represents the
conversations with its environment is spread out all over the enterprise—in different systems, in multiple different formats, in different technology. The only way to integrate the enterprise
is to integrate the data that represents enterprise conversations in a central data warehouse—the last, best place to integrate the enterprise.

References

[i] American Heritage Dictionary, Second College Edition.

[ii] Other forces that fragment the enterprise are employee turnover, outsourcing, global markets, and lowered trade barriers.

[iii] Yeargen, Danien, and Joseph Stanislaw, The Commanding Heights: The Battle Between Government and the Marketplace that is Remaking
the Modern World
, New York, Simon & Schuster, 1998.

[iv] Mintzberg, Walter, Mintzberg On Management. New York, The Free Press, 1989. Mintzberg discusses seven enterprise
configurations, including entrepreneurial, machine, diversified, professional, innovative, missionary, political.

[v] This kind of diagram is found in Improving Performance: How to Manage the White Space on the Organization Chart, by Geary
Rummler and Alan Brache. It’s called a ”Process Map.”

[vi] Colvin, Geoffrey, “Value Driven; M&A And You: Career Power”, Fortune, June 22, 1998.

[vii]The Multidimensional Manager

Share

submit to reddit

About Van Scott

Van Scott is a creative enterprise architect with a very strong methodological bent. He is intrigued by the enterprise, enterprise architectures, and data warehousing.  Very project-focused.  Known for taking notes, listening, and reading. Published author in music, psychology, and data management.  Practicing musician and composer.  Principal consultant at Sonata Consulting, Inc.  You can reach Van at scottv1@airmail.net or 940.566.3503. Current project is building a large call detail data warehouse for a firm in the telecommunications industry.

Top