The REA Approach to Database Modeling:ERP System Configurations

ERP System Configurations

SERVER CONFIGURATIONS

Most ERP systems are based on the client-server model, which will be discussed in detail in Chapter 12. Briefly, the client-server model is a form of network topology in which a user’s computer or terminal (the client) accesses the ERP programs and data via a host computer called the server. The servers may be centralized, but the clients are usually located at multiple locations throughout the enterprise. Two basic architectures are the two-tier model and the three-tier model, as described in the following sections.

Two-Tier Model

In a typical two-tier model, the server handles both application and database duties. Client computers are responsible for presenting data to the user and passing user input back to the server. Some ERP vendors

use this approach for local area network (LAN) applications for which the demand on the server is restricted to a relatively small population of users. This configuration is illustrated in Figure 11-3.

Three-Tier Model

The database and application functions are separated in the three-tier model. This architecture is typical of large ERP systems that use wide area networks (WANs) for connectivity among the users. Satisfying client requests requires two or more network connections. Initially, the client establishes communications with the application server. The application server then initiates a second connection to the database server. Figure 11-4 presents the three-tier model.

OLTP VERSUS OLAP SERVERS

When implementing an ERP system that will include a data warehouse, a clear distinction needs to be made between the competing types of data processing: OLTP and OLAP. OLTP events consist of large numbers of relatively simple transactions, such as updating accounting records that are stored in several related tables. For example, an order entry system retrieves all of the data relating to a specific customer to process a sales transaction. Relevant data are selected from the Customer table, Invoice table, and a detailed Line Item table. Each table contains an embedded key (that is, customer number), which is used to relate rows between different tables. The transaction processing activity involves updating the customer’s current balance and inserting new records into the Invoice and Line Item tables. The relationships between records in such OLTP transactions are generally simple, and only a few records are actually retrieved or updated in a single transaction.

OLAP can be characterized as online transactions that:1

• Access very large amounts of data (for example, several years of sales data).

• Analyze the relationships among many types of business elements such as sales, products, geographic regions, and marketing channels.

• Involve aggregated data such as sales volumes, budgeted dollars, and dollars spent.

• Compare aggregated data over hierarchical time periods (for example, monthly, quarterly, yearly).

• Present data in different perspectives such as sales by region, by distribution channel, or by product.

• Involve complex calculations among data elements such as expected profit as a function of sales revenue for each type of sales channel in a particular region.

• Respond quickly to user requests so they can pursue an analytical thought process without being stymied by system delays.

An example of an OLAP transaction is the aggregation of sales data by region, product type, and sales channel. The OLAP query may need to access vast amounts of sales data over a multiyear period to find sales for each product type within each region. The user can further refine the query to identify sales volume by product for each sales channel within a given region. Finally, the user may decide to perform year-to-year or quarter-to-quarter comparisons for each sales channel. An OLAP application must be able to support this analysis online with rapid response.

The difference between OLAP and OLTP can be summarized as follows. OLTP applications support mission-critical tasks through simple queries of operational databases. OLAP applications support management-critical tasks through analytical investigation of complex data associations that are captured in data warehouses. OLAP and OLTP have specialized requirements that are in direct conflict. Figure 11-5 shows how the client-server architecture enables organizations to deploy separate and specialized application and database servers to resolve these conflicting data management needs. OLAP servers support common analytical operations including consolidation, drill-down, and slicing and dicing.

Consolidation is the aggregation or roll-up of data. For example, sales offices data can be rolled up to districts and districts rolled up to regions.

Drill-down permits disaggregating data to reveal the underlying details that explain certain phenomena. For example, the user can drill down from total sales returns for a period to identify the actual products returned and the reasons for their return.

Slicing and dicing enables the user to examine data from different viewpoints. One slice of data might show sales within each region. Another slice might present sales by product across regions. Slicing and dicing is often performed along a time axis to depict trends and patterns.

OLAP servers allow users to analyze complex data relationships. The physical database itself is organized in such a way that related data may be rapidly retrieved across multiple dimensions. Thus, OLAP database servers need to be efficient when storing and processing multidimensional data. Later in the chapter, data modeling and storage techniques that improve data warehouse efficiency will be examined. In contrast, relational databases for operations are modeled and optimized to handle OLTP applications. They concentrate on reliability and transaction processing speed, instead of decision support need.

DATABASE CONFIGURATION

ERP systems are composed of thousands of database tables. Each table is associated with business processes that are coded into the ERP. The ERP implementation team, which includes key users and information technology (IT) professionals, selects specific database tables and processes by setting switches in the system. Determining how all the switches need to be set for a given configuration requires a deep understanding of the existing processes used in operating the business. Often, however, choosing table settings involves decisions to reengineer the company’s processes so that they comply with the best business practices in use. In other words, the company typically changes its processes to accommodate the ERP rather than modifying the ERP to accommodate the company.

BOLT-ON SOFTWARE

Many organizations have found that ERP software alone cannot drive all the processes of the company. These firms use a variety of bolt-on software that third-party vendors provide. The decision to use bolt- on software requires careful consideration. Most of the leading ERP vendors have entered into partner- ship arrangements with third-party vendors that provide specialized functionality. The least risky approach is to choose a bolt-on that is endorsed by the ERP vendor. Some organizations, however, take a more independent approach. Domino’s Pizza is a case in point.

Domino’s Pizza

Domino’s U.S. distribution delivered 338 million pizzas in 1998.2 The company manufactures an average of 4.2 million pounds of dough per week in its 18 U.S. distribution centers. A fleet of 160 trucks carries the dough along with other food and paper products to the 4,500 U.S. Domino’s franchises. Domino’s has no cutoff time for ordering supplies. Therefore, a franchise can call and adjust its order even after the truck has rolled away from the distribution center. To help anticipate demand, Domino’s uses fore- casting software from Prescient Systems Inc., which bolts on to their PeopleSoft ERP system. In addition, they use a system from Manugistics Inc. to schedule and route the delivery trucks. Each truck has an onboard computer system that feeds data into a time-and-attendance system from Kronos Inc., which connects to the PeopleSoft human resources module. Domino’s also has an extensive data warehouse. To anticipate its market, Domino’s performs data mining with software from Cognos Inc. and Hyperion Solutions Corp.

Domino’s had been using these and other applications before it implemented an ERP. The company did not want to retire its existing applications, but discovered that the legacy system required data fields that the ERP did not provide. For instance, the routing system tells the truck drivers which stores to visit and in what order. The ERP system did not have a data field for specifying the delivery stop sequence. The warehousing system needs this information, however, to tell loaders what to put in the trucks and in what order. Having confidence in its in-house IT staff, Domino’s management decided to take the relatively drastic step of modifying the ERP software to include these fields.

Supply Chain Management

Another development regarding the bolt-on software issue is the rapid convergence between ERP and bolt- on software functionality. Supply chain management (SCM) software is a case in point. The supply chain is the set of activities associated with moving goods from the raw materials stage to the consumer. This includes procurement, production scheduling, order processing, inventory management, transportation,

warehousing, customer service, and forecasting the demand for goods. SCM systems are a class of application software that supports this task. Successful SCM coordinates and integrates these activities into a seam- less process. In addition to the key functional areas within the organization, SCM links all of the partners in the chain, including vendors, carriers, third-party logistics companies, and information systems providers. Organizations can achieve competitive advantage by linking the activities in its supply chain more efficiently and effectively than its competitors.

Recognizing this need, ERP vendors have moved decisively to add SCM functionality to their ERP products. ERP systems and SCM systems are now on converging paths. SAP and Oracle have recently added an SCM module, while PeopleSoft has acquired smaller SCM vendors to integrate its SCM soft- ware into future releases. On the other hand, SCM software vendors are also expanding their functionality to appear more like ERP systems. As larger ERP vendors move into the midsize company market, the smaller SCM and ERP vendors will likely be pushed out of business.