The REA Approach to Database Modeling:What Is an ERP?

What Is an ERP?

ERP systems are multiple module software packages that evolved primarily from traditional manufacturing resource planning (MRP II) systems. The Gartner Group coined the term ERP, which has become widely used in recent years. The objective of ERP is to integrate key processes of the organization such as order entry, manufacturing, procurement and accounts payable, payroll, and human resources. By doing so, a single computer system can serve the unique needs of each functional area. Designing one system that serves everyone is an undertaking of massive proportions. Under the traditional model, each functional area or department has its own computer system optimized to the way it does its daily business. ERP combines all of these into a single, integrated system that accesses a single database to facilitate the sharing of information and to improve communications across the organization.

To illustrate, consider the traditional model for a manufacturing firm illustrated in Figure 11-1. This company employs a closed database architecture, which is similar in concept to the basic flat-file model. Under this approach, a database management system is used to provide minimal technological advantage over flat-file systems. The database management system is little more than a private but powerful file sys- tem. As with the flat-file approach, the data remain the property of the application. Thus, distinct, separate, and independent databases exist. As is true with the flat-file architecture, there is a high degree of data redundancy in a closed database environment.

When a customer places an order, the order begins a paper-based journey around the company where it is keyed and rekeyed into the systems of several different departments. These redundant tasks cause delays and lost orders, as well as promote data entry errors. During transit through the various systems, the status of the order may be unknown at any point in time. For example, responding to a customer query, the marketing department may be unable to look into the production database to determine whether an order has been manufactured and shipped. Instead, the frustrated customer is told to call manufacturing. Similarly, the procurement of raw materials from suppliers is not linked to customer orders until they reach the manufacturing stage. This results in delays because manufacturing awaits the arrival of needed materials or in excessive investment in inventories to avoid stock-outs.

The lack of effective communication between systems in the traditional model is often the consequence of a fragmented systems design process. Each system tends to be designed as a solution to a specific operational problem rather than as a part of an overall strategy. Furthermore, because systems designed in-house emerge independently and over time, they are often constructed on different and

incompatible technology platforms. Thus, special procedures and programs need to be created so that older mainframe systems using flat files can communicate with newer distributed systems that use relational databases. Special software patches are also needed to enable commercial systems from different vendors to communicate with each other as well as with custom systems that were developed in-house. Although communications between such a hodgepodge of systems is possible, it is highly fragmented and not conducive to efficient operations.

ERP systems support a smooth and seamless flow of information across the organization by providing a standardized environment for a firm’s business processes and a common operational database that sup- ports communications. An overview of ERP is presented in Figure 11-2. Data in the operational database are modeled, structured, and stored in accordance with the internal attributes of the data. They remain in- dependent of any specific application. Extensive data sharing among users occurs through application- sensitive views that present the data in a way that meets all user needs.

ERP CORE APPLICATIONS

ERP functionality falls into two general groups of applications: core applications and business analysis applications. Core applications are those applications that operationally support the day-to-day activities of the business. If these applications fail, so does the business. Typical core applications include, but are not limited to, sales and distribution, business planning, production planning, shop floor control, and logistics. Core applications are also called online transaction processing (OLTP) applications. Figure 11-2 illustrates these functions applied to a manufacturing firm.

Sales and distribution functions handle order entry and delivery scheduling. This includes checking on product availability to ensure timely delivery and verifying customer credit limits. Unlike the previous

example, customer orders are entered into the ERP only once. Because all users access a common data- base, the status of an order can be determined at any point. In fact, the customer will be able to check the order directly via an Internet connection. Such integration reduces manual activities, saves time, and decreases human error.

Business planning consists of forecasting demand, planning product production, and detailing routing information that describes the sequence and the stages of the actual production process. Capacity planning and production planning can be very complex; therefore, some ERPs provide simulation tools to help managers decide how to avoid shortages in materials, labor, or plant facilities. Once the master production schedule is complete, the data are entered into the MRP (materials requirements planning) module, which provides three key pieces of information: an exception report, materials requirements listing, and inventory requisitions. The exception report identifies potential situations that will result in rescheduling production, such as late delivery of materials. The materials requirements listing shows the details of vendor shipments and expected receipts of products and components needed for the order. Inventory requisitions are used to trigger material purchase orders to vendors for items not in stock.

Shop floor control involves the detailed production scheduling, dispatching, and job costing activities associated with the actual production process. Finally, the logistics application is responsible for ensuring timely delivery to the customer. This consists of inventory and warehouse management, as well as ship- ping. Most ERPs also include their procurement activities within the logistics function.

ONLINE ANALYTICAL PROCESSING

An ERP is more than simply an elaborate transaction processing system. It is a decision support tool that supplies management with real-time information and permits timely decisions that are needed to improve performance and achieve competitive advantage. Online analytical processing (OLAP) includes decision support, modeling, information retrieval, ad hoc reporting/analysis, and what-if analysis. Some ERPs sup- port these functions with their own industry-specific modules that can be added to the core system. Other ERP vendors have designed their systems to accept and communicate with specialized bolt-on packages that third-party vendors produce. Sometimes the user organization’s decision support requirements are so unique that they need to integrate in-house legacy systems into the ERP.

However business analysis applications are obtained or derived, they are central to their successful function as a data warehouse. A data warehouse is a database constructed for quick searching, retrieval, ad hoc queries, and ease of use. The data are normally extracted periodically from an operational database or from a public information service. An ERP system could exist without having a data warehouse; similarly, organizations that have not implemented an ERP may deploy data warehouses. The trend, however, is that organizations that are serious about competitive advantage deploy both. The recommended data architecture for an ERP implementation includes separate operational and data warehouse databases. We will examine issues related to the creation and operation of a data warehouse later in the chapter.