Cormac Burke & Steve Pontello
|
Great Western Chemical Co. (GWCC) of Portland, Ore., began in late 1998 to plan an enterprise resource planning (ERP) system implementation using Oracle. It hoped the project would improve distribution, centralize facilities management, improve management of the business, and provide strategic reporting for its executives, financial group, and business managers.
GWCC is a large, full-line chemical distributor, with sales exceeding $260 million. Founded in 1956, the company has expanded across North America and into Asia, and focuses on nine primary markets. Dwight Ackerman, GWCC’s IT director, had been through other organizations’ ERP deployments, and was aware there would be gaps in the ERP system’s information. These gaps would clearly impede GWCC’s ability to analyze its business and make informed decisions, and would dampen the ERP system’s success and influence. Ackerman wanted to develop a data warehouse in conjunction with the ERP to ensure a full view of key metrics and information housed within.
Information Requirements
Like most businesses, GWCC had a number of key metrics by which it measures its success, and it was clear that the ERP alone would not provide that information cost effectively. The kinds of information critical to its business management include profitability, customer activity, vendor activity, inventory, and product costs. Many of these measures are viewed from multiple dimensions, including Sales Rep, Time Period as it pertains to GWCC’s business calendar, Geography, and so on. GWCC’s competitive environment is not unique in that a fraction of a cent per unit makes a difference between profit and loss. Dimensional data is required the next day, not at the end of the month. Additionally, it was clear that a data warehouse could be essential in finding and isolating business exceptions and issues, as the “looking glass” into the ERP system.
Methodology and Tool Set
In embarking on this data warehouse project, we used an approach that had been successful with previous clients. This approach comprises an expeditious set of standard, best practice tools and techniques including:
• Standardized baseline mappings of Oracle Applications data models to star schemas
• A common set of Oracle PL/SQL packages and routines to facilitate and simplify the data transformation and movement from the operational system to the data warehouse
• A set of data and metadata standards for ongoing management of the data warehouse
• A metamodel and accompanying repository for metadata visibility and management.
The standardized mappings are based on the Oracle Applications suite (Financials, Manufacturing, and Distribution). The design included detail-level fact tables for Accounts Receivable, Cost, and Orders, to meet all GWCC’s requirements. In addition, we built historical tables to collect daily snapshots of on-hand inventory.
FIGURE 1 ERP and data warehouse system integration architecture.
From this environment, we constructed views to support user analysis of sales, cost of goods sold, and profit information in either multiple base currencies or U.S. dollars. These views continue to evolve depending on user and business requirements. We also built conformed dimension tables, including Customer, Organization, Product, Vendor, and Time. These tables can be used for slicing and dicing detail fact data or, with user-defined summary fact tables, help provide greater flexibility within the data warehouse.
Technologies Deployed
Our decision to choose primarily Oracle-based technologies for the data warehouse was due in part to the existing Oracle installed base and in-house expertise, as well as to Oracle’s strength in the data warehouse arena. The Oracle8 RDBMS is the backbone for the GWCC Data Warehouse environment. Oracle’s support for data partitioning, bitmapped indices, PL/SQL, and parallel query all contributed to the stability, performance, and scalability of the data warehouse’s decision-support system (DSS) environment. The ease of connecting to the Oracle ERP system made Oracle the natural RDBMS choice for the data warehouse. The RDBMS itself resides on a Compaq (DEC) Alpha 4100 platform with two processors, 1GB RAM, and 150GB RAID.
We used Oracle Designer 2000 to model the environment and as the metadata repository. This tool provided, in addition to an easily integrated modeling environment, a repository that we could tightly couple with the warehouse itself.
Initially, we tested Oracle Applications data warehouse toolkit as the core mechanism for data extraction, transformation, and loading (ETL). Although in concept this package seemed to hold great potential for building and supporting an Oracle-based data warehouse, the product’s immaturity and Oracle’s inability to fully support it made us reconsider. Ultimately we used our proprietary ECS set of PL/SQL-based packages geared toward conducting ETL in a standardized, template-driven approach.
The primary end-user tools GWCC uses are Microsoft Excel and Oracle Discoverer. Excel provides a relatively straightforward and familiar user interface to numerous users; Discoverer provides some of the more complex functions (such as drill-down and drill-through to operational tables) that the more sophisticated users require. Constructing the Discoverer end-user layer was an essential step in making the data “business friendly,” thus broadening its appeal and adoption.
Value of Data Warehouse to GWCC
The value of the data warehouse to GWCC has been significant in a number of different ways. First and foremost, it gives the executives and operational managers the daily visibility into their business, via flash reports, to ensure that they are informed well enough to manage the business realistically. The flash reports include a variety of key metrics daily, including product profitability, revenue, and current inventory. This information lets management quickly detect changes and adjust the business accordingly.
Given the value and accuracy of information the flash reports provide, the data warehouse’s business visibility has increased, particularly within the finance organization. The data’s high level of accuracy and availability has increased the executives’ interest in the project. The managers see clearly that the data warehouse has given them an analysis and reporting platform they can depend on and grow as their business needs evolve.
The benefit to the users of online vs. paper reporting is enormous. GWCC management estimates the business saved roughly $300,000 in costs that it would have incurred for building business-critical reports. This fact alone could have justified the data warehouse’s expense.
One of the unanticipated benefits of the data warehouse is that it revealed “bad” ERP data, which in some cases raised questions. After analysis ensured that the data did indeed reflect the operational system accurately, it was clear that business or application problems were compromising data integrity. Exception reporting and ongoing analysis of the data helped find and address various discrepancies, both with GWCC’s business processes and the Oracle application. This ability has proven to be of great value, to both GWCC’s business management and the ERP team, which has modified the application to work around some of the process troubles. GWCC enforced its original design concept of using the data warehouse as a subsidiary ledger to the general ledger. Data warehouse and accounting staff perform a series of daily and weekly activities to reconcile warehouse data to financial reporting data, which boosts executives’ confidence that the data is accurate.
Challenges and Lessons Learned
Many of the challenges we faced and lessons we learned through the development of GWCC’s data warehouse would apply to any data warehouse implementation done in conjunction with an ERP implementation.
Building a data warehouse is always a significant effort, and it will affect most of the same areas of both the business and IT that are involved in the ERP deployment. Corporate and IT management must both commit strongly to balance these two efforts in parallel, as the ERP effort alone tends to be huge. The payoff, however, is that the quality of the data warehouse will greatly benefit from the involvement of the business and ERP implementers in the early stages of design and implementation. You can spend a great deal of time, while building a data warehouse, in researching how data should be manipulated and represented. Readily accessed application knowledge can reduce this time significantly.
The next challenge concerned the tool set. Despite the time we spent learning the Oracle Applications data warehouse tool suite, we soon saw that the ETL tools have a ways to go before they can be trusted to deliver crucial functionality to a business-critical data warehouse. Also, regardless of the tool’s quality, there is no substitute for the business analysis that will accurately document the data transformation routines needed to feed the metadata repository.
A last valuable lesson we learned was that a great way to expose application and business issues is to get a clear, simple view into the ERP data. However, ERP inherently does not provide this view. In GWCC’s case, the data warehouse provided it and greatly improved the company’s ability to address application and business issues quickly to ensure that the application was performing correctly. It continues to be a tool to help provide inputs for process improvement, on both the business and application sides.
Despite the challenges that GWCC faced in embarking on a data warehouse in conjunction with the Oracle ERP implementation, by all accounts the endeavor was worthwhile. The data warehouse delivered on the immediate promise of visibility into key business metrics, which would otherwise be submerged in the ERP system. The warehouse established a scalable, flexible, and responsive reporting and analysis environment, on which GWCC can build as its business grows and needs evolve. It reduced the ERP team’s report-development burden while equipping users with better tools for flexible analysis.
Users who were used to paper reports are increasingly comfortable with flexible templates, which allow them to customize their reports according to their specific needs. And, it proved to be an extremely useful tool in helping GWCC and the ERP team find and resolve issues and discrepancies between business processes and the applications — discrepancies that would otherwise result in questionable and, at times, conflicting result sets in the company’s financials. The company believes that this approach has minimized the cost of reporting while providing the foundation for future enhanced reporting.
Cormac Burke (cburke@ecs-its.com) is the practice manager for data warehousing and e-commerce for ECS, a business unit of CSC.
Steve Pontello (spontell@ecs-its.com) is a principal DSS consultant for ECS.
|
|
|
| |||||||||||||||||||||||||||||||
