This paper examines the design and operation of data warehouse architectures, with particular focus on data replication as a core strategy for distributed database management. It surveys common warehouse configurations — including two-tiered and three-tiered architectures — before evaluating the trade-offs between data replication approaches and federated multi-database approaches for integrating heterogeneous databases. The paper uses biological database integration as a case study, illustrating how the P/FDM system and Functional Data Model support federated access across remote data sources. It concludes by contrasting tightly and loosely coupled integration strategies, arguing that a shared data model — rather than a fully centralized or fully decentralized architecture — best balances autonomy, currency, and scalability.
Today, many different data warehouse architectures exist to meet users' requirements. Characteristically, data warehouses comprise a distributed data design in which mass data transfers occur during off hours and widespread interactive querying takes place during peak hours of the day. Correct planning for warehouse operations is therefore critically important, particularly with respect to a company's network communications. To prevent performance problems, systems professionals should be involved in every stage of warehouse planning, expansion, and implementation. Network analysis should consider a number of factors: how frequently data updates should occur, how they ought to be scheduled, when they should happen, how much interactive capacity to permit, how the front-end tools operate, and what user query behavior will look like (Leonard, 2007).
A typical data warehouse architecture consists of data extraction from operational production systems, which is then passed to the warehouse database. A specialized data warehouse server hosts the warehouse databases and decision support tools, including OLAP and knowledge-based tools. This server passes extracted data to the warehouse database and is used by end users to extract data from the warehouse via software applications designed to answer users' questions and meet their information and knowledge processing requirements (Kemme & Alonso, 2000). Although not shown in Figure 1.1, operational production databases are updated continuously via OLTP applications.
Figure 1.1: The Basic Components of a Data Warehouse
A warehouse database is "refreshed" from operational production systems on a periodic basis, usually during off hours when network and CPU utilization is low. In essence, a data warehouse is a specialized database for supporting decision making. Data is drawn from a variety of operational sources and then "scrubbed" to eliminate inconsistencies or errors (Leonard, 2007).
A common and simple type of data warehouse involves a two-tiered, homogeneous architecture. For example, IBM DB2 data on a mainframe computer might be periodically extracted and copied to a DB2 database on a Microsoft Windows NT server. A data access product — such as Information Builders Inc.'s FOCUS Reporter for Windows — can then be used to read, analyze, and report on warehouse data from a front-end graphical client on the Windows NT LAN.
More complex data warehouses are based on a three-tiered architecture that uses a separate middleware layer for data access and translation. The first tier hosts production applications and is generally a mainframe or midrange system, such as Digital Equipment Corporation's VAX or IBM's AS/400 (Angoss Software, 2006). The second tier is a departmental server — such as a Unix workstation or a Windows NT server — located in close proximity to warehouse users. The third tier is the desktop, where IBM PCs, Apple Macintoshes, and X terminals are connected on a local area network (LAN). In this three-tiered architecture, the host (first tier) is devoted to real-time, production-level data processing; the departmental server (second tier) is optimized for query processing, analysis, and reporting; and the desktop (third tier) handles reporting, analysis, and the graphical presentation of data.
In the past, many corporate databases were routinely synchronized and were, in essence, clones of one another. This task, often called "nightly refresh," has been performed for years in mainframe computing environments. When only a few PCs were added to the mix, the job evolved into downloading and uploading data between PCs and the mainframe — a situation that remained quite manageable (Kemme & Alonso, 2000). However, when networks, servers, users spread across multiple time zones, groupware applications, and real-time information dependencies all come into play, this task grows into a network manager's nightmare — commonly known as replication.
Fundamentally, replication reproduces information from one database to another so that the data in both databases remain identical. This can involve transferring data from a central mainframe to branch servers and then down to local workstations, generating news feeds for reporting and analysis requests, connecting networked servers, or operating within virtually any system architecture. Data transfer can be one-way or two-way. It may be event-based (triggered by changes in data values) or time-dependent (executed at regular intervals or nightly).
Replication offers numerous benefits. It is frequently used to distribute processing load across servers. Copies of corporate data are transferred to branch offices, where departmental users can access and use local data more efficiently. Replication can also be a vital component of a data warehouse strategy (Pacitti & Simon, 2000) — that is, merging data from multiple source databases into a single data store for analysis. Maintaining several copies of data also sets the stage for rapid failure recovery and cost-effective load balancing on high-traffic networks.
Replicated database management systems are well suited for applications such as backup, knowledge management, OLAP, and decision support systems (DSS) that do not require up-to-the-minute information. Most managers who use these systems do not require real-time data (Aubrey & Cohen, 1996). Similarly, most organizations performing backups do not need the currency that two-phase commit provides. Many users would likely prefer working with a backup system that is slightly out of sync rather than waiting for the primary database management system to be restored. Replication also allows companies to partition a database and move information closer to the users who work with it most frequently. Response time improves because the data is stored locally rather than at a central site, and wide area network costs decrease because users no longer need to access the network for routine tasks.
As time goes on, new types of databases will continue to emerge. The challenge is to integrate them in a flexible way that allows continued expansion with local autonomy in updating, while also enabling automated search for answers to queries across the entire collection of databases. Two possible architectures for integrating biological databases are outlined here: a data replication approach and a federated approach (Angoss Software, 2006).
In this architecture, all data from the various databases and databanks of interest would be transferred into a single local data repository under a single database management system. This approach is taken by Gray et al. (2005), who proposed an architecture in which the contents of biological databanks — including the EMBL nucleotide sequence databank and SwissProt — are imported into a central repository. However, a data replication approach is not well suited to this application domain for several reasons:
Space. The volume of biological data in accessible databanks and databases is enormous, and new data are being produced at an increasing rate. Only a small number of sites have sufficient disk space to mirror all the data that their clients may require. National bioinformatics nodes currently provide repository services for many databanks. A site wishing to integrate its own confidential local data with existing public resources would be required to mirror at least part of these resources.
Updates. Scientists require access to the most current data. They want online access to results reported in recent journals as soon as those results have been entered into a databank or database. Whenever one of the contributing databases is updated, the same update would have to be applied to the data warehouse. (Corrections and deletions are made to biological databases on occasion, though additions are more frequent.) Another possibility is for the data to be updated locally and periodically copied to a central repository, but this introduces a delay in accessing current information (Applehans et al., 2004).
Autonomy. By adopting a data repository approach, the advantages of individual specialized systems are lost. For instance, many biological data resources have their own custom graphical interfaces and search engines that are tailored to the specific physical representation used with that data set. They also maintain their own update schedules. The sociological importance of a degree of site autonomy should not be underestimated — people prefer to feel that they retain control over their own data and that this control is not forfeited when they begin sharing information.
In summary, a data replication approach would demand human resources, software, and hardware beyond what is realistically available at each site seeking to use the information.
"Federated architecture advantages in space, updates, autonomy"
"P/FDM system and EBI biological database federation"
"Historical debate between centralized and loosely coupled integration"
Always verify citation format against your institution’s current style guide requirements.