<-- Back to full color view

Reinventing Business: Enterprise Data Warehouse Business Opportunities for Manufacturing, Part 1

Originally published March 4, 2010

Characteristics of the manufacturing enterprise continue to change at an ever-increasing pace. Globalization or consolidation of supply chains, outsourcing of manufacturing and distribution operations, emerging markets growth, mergers, acquisitions, Six Sigma quality and lean manufacturing processes, and financial reporting regulatory requirements are all driving the need for more comprehensive and timely enterprise information. Yet these same trends challenge IT organizations’ abilities to deliver the information needed to manage manufacturing enterprises.

Many manufacturing companies are spending hundreds of millions, or billions, of dollars or euros on ERP (enterprise resource planning) projects to improve and standardize their operational processes. Many years are required to implement these process changes and systems throughout large manufacturing enterprises. Meanwhile, the ERP systems evolve and change continuously, so multiple systems and versions will continue to exist in most large companies. Acquisitions, mergers, and manufacturing outsourcing almost assure that the enterprise changes faster than processes and systems can be standardized. Thus, few companies are close to attaining the “standardized processes” goal, and even fewer have a single enterprise-wide ERP system.

The primary focus of ERP systems is on operational processes – not on business intelligence. Some standard reporting capabilities may be included, but comprehensive decision making and business intelligence capabilities are not typically available from ERP systems. Thus, even those companies that have attained satisfactory ERP implementation find that they do not have the business intelligence capabilities required for strategic or tactical decision making.

Many manufacturing executives have mandated that they be provided with executive “dashboards” that highlight key performance indicators (KPIs) for their organizations. But, lacking comprehensive, standardized, and integrated information sources, executive dashboards are being assembled via cumbersome processes from many sources of information. They are often updated too infrequently, too late, and provide inadequate detail for decisions or action.

An enterprise data warehouse (EDW) is the best solution for business intelligence. The EDW provides comprehensive and timely information meeting the requirements of all levels of executives, management, and all knowledge workers throughout the organization who use information to make decisions. The EDW has been proven to enable a new and better way of managing a manufacturing enterprise.

Series Overview

Implementing an enterprise data warehouse offers the opportunity to dramatically improve business results. This series of 12 articles describes what exactly an EDW is and how it can be used to run the business, and quantifies dramatic business improvement potentials. Continue reading for a more complete understanding of what it is and what it can do for your enterprise. Many variables affect the specific opportunity for your company, but the impact will be substantial. An EDW represents one of the best investments you can make.

This series of articles describes the enterprise data warehouse and thirty major business improvement opportunities for manufacturing companies. Their combined scope encompasses the entire enterprise and realistically represents reinventing business.
  • Part 1 provides and overview of the series and gives a definition and scope for the EDW

  • Parts 2 – 6 describe and quantify 30 major benefit opportunities

  • Part 7 contains 10 business analysis examples for enabling benefits and summarizes the business improvement opportunities

  • Part 8 describes 30 best practices to ensure success

  • Part 9 briefly describes the governance process and provides a recommended organizational structure

  • Part 10 summarizes the series

Description and Scope of the Enterprise Data Warehouse

An EDW contains integrated, standardized, detailed, comprehensive, current, and historical data, providing a single source of business intelligence supporting strategic, tactical and operational decision making for an enterprise.

Enterprise means that it includes data from all functional areas and business units, and some external sources. It is designed to be used by all knowledge workers, customers, channel partners (wholesalers, distributors, dealers, and retailers), suppliers, and prospective customers (often the global public). For very large conglomerates, “enterprise” may be defined as a segment or major business unit of the parent company, provided that segment does not share common customers, suppliers, or supply chains with other segments of the business. Enterprise data warehouse means standardization and integration of current and historical detailed data in one central database.

Achieving the benefits described in this series requires atomic detail – all available data about each transaction and event, in addition to supplier, channel partner, customer, product, material, bill of material, and hierarchies (“master data”) relevant to business intelligence. This data must be standardized, requiring transformation of data from inconsistent sources into consistent data formats and content in the EDW. Normalization is required to avoid data duplication and assure that all data relationships are defined to support “any question, any time.” Historical detail is kept as long as required for the business or industry – often seven to ten years, but in some cases indefinitely.

Achieving all the benefits described in this series also requires that the EDW is current, or active. Active is defined to mean that data is loaded from all source systems at least daily – but more frequently as required. Current information enables timely tactical and operational analytics and decision making. Active also means that the EDW provides frequent or continuous monitoring of actual business status and results against defined goals, with messaging to responsible people or feedback of data to operational systems.

By integrating detailed cross-functional data from all business units and geographic regions, the EDW enables analysis of business performance and opportunities by market, customer, product, geography and time dimensions spanning corporate business units. A global scope is important for manufacturers who have global customers, global markets, global suppliers, or global supply chains.

Realizing maximum value from the EDW involves business unit cooperation and teamwork to leverage customer and market opportunities across business units in a customer-focused business environment. The EDW supports related-selling opportunities across multiple business units in the enterprise. From the customer perspective, it enables a “one face” business environment, improving customer satisfaction and being “easier to do business with.

The EDW enables everyone in the enterprise to work cooperatively together based on shared information, while allowing business units flexibility to operate appropriately for their product, industry, market, or region. This seeming contradiction is accomplished by providing enterprise-wide standardized views of information, while also providing business-unit-specific views of the same detailed data.

The daily or continuous (“real-time”) process of feeding the EDW involves ETL:
  • Extracting atomic transaction data from transaction processing systems; relevant “event” data from sales contacts, call centers, customer communications, Internet navigation, and documents; and all master data additions or changes, including product documents, images, and bills of material,

  • Transforming incoming data into standardized formats, and

  • Loading it into the EDW standardized database.
To re-emphasize: Atomic data means detailed data from every transaction and event; customer and vendor account; and product, component, raw material, and inventory stock-keeping unit (SKU). Failure to capture atomic information means inaccurate, unreliable or missing data. Loading only summarized data means data accuracy can not be assured and information lacks credibility. Atomic detail enables complete analytic flexibility, with “restatement” of changing territories, regions, markets, customer and vendor hierarchies, product categories, and business unit structures. Restatements require summarizing detailed historic data to match current data summarization hierarchies. If history detail is not retained, restatements are not possible.

Atomic detail is required to enable common views of customers, markets, etc. across business units, while at the same time enabling business units to maintain different, unique views. Atomic detail is also required for drill-down to actionable detail. Detailed data required to achieve all the benefits described in this series includes:
  1. Customer, prospect, and vendor master data, including names, addresses, contacts, industry classifications, hierarchies, and demographics

  2. Product, material, and services master data, including market-focused descriptions, characteristics, standard packaging hierarchies and dimensions (pallet, case, carton, and consumer unit), Global Trade Item Numbers (GTIN), standard material and services classifications (UNSPSC), product hierarchies, documents, manuals, images, training materials, ordering quantities, prices, costs, and bills of material

  3. Customer order and invoice line item details, quantities, unit and total prices, status changes, fulfillment, shipment, logistics, delivery, returns, lot or serial numbers if required, debits, credits, deductions, and payments received

  4. Purchase order and vendor (including transportation carriers) invoice line item details, quantities, unit and total prices, status changes, fulfillment, shipment, logistics, receipts, returns, quality data, lot or serial numbers if required, debits, credits, deductions, and payments

  5. Internal warehouse replenishment and production order line item details, quantities, costs, status changes, fulfillment, shipments, receipts, logistics, and lot or serial numbers if required

  6. Manufacturing process inputs and outputs, including materials, labor, plant and equipment utilization and associated costs, quality data, and lot or serial numbers

  7. Inventory locations, balances, lot or serial numbers if required, and inventory control parameters

  8. Channel partner (wholesalers, distributors, dealers, and retailers) POS data, preferably with customer information, and lot or serial numbers if required

  9. Business information from external data sources, with DUNS numbers (recognized global standard for business identification), SIC codes (standard industry classifications), names, addresses, contact information, credit ratings, and business characteristics

  10. Sales, call center, customer surveys, field service, warranty service, e-mail, Internet clickstream, and other “touch point” information, with associated text describing the contact, complaint, problem, or service issue, relevant product identification, and lot or serial numbers if required

  11. Non-invoice allowances, trade funds, and rebates

  12. Sales and purchase contracts, pricing and contract amounts

  13. Marketing programs, dates, and associated plans

  14. Sales forecasts, financial forecasts and budgets, demand forecasts, supply plans, operational and factory plans

  15. Human resource information, organization hierarchies, and performance goals

  16. Financial assets, liabilities, and balances

  17. All other financial transactions, adjustments, allocations, consolidations, general ledger, and profit and loss statements

  18. Global currency conversion tables and work day calendars, if the scope is global

  19. Standard freight cost tables by mode, source, and destination

  20. Metadata describing atomic data element sources, transformations, valid code content, name and synonyms, business descriptions; and definitions of standard summary algorithms (such as gross sales, net sales, gross margin, profit, etc.)
Some information, such as gross margin and profit, is a result of summary analyses, but is not input to the EDW. This information is derived from cost and invoice information, which are EDW inputs. Service metrics are also critical information components for supply chain management, but are derived from order-related transactions. There are many other such examples of derived data, which may be calculated and stored in the EDW, or calculated on-demand when required.

An EDW requires customer, market, vendor, product, material, organization, and geographic hierarchical relationships to complement atomic information detail. These hierarchies are required to view detailed and summary information from various perspectives or “dimensions.” They may be maintained from internal or external sources. External information sources should typically be used to maintain customer, vendor, and market hierarchies, and to provide material and services classifications. For most manufacturers, it is impractical to maintain these internally because of frequent changes.

To achieve enterprise synergy while retaining business unit flexibility requires easily maintainable hierarchies, specific to business units and functional areas, as well as enterprise hierarchies. For example, marketing, finance, and supply chain typically require different product hierarchies. Customer and market segmentation (represented by hierarchies) often varies by business unit. With detailed data in the EDW, these multiple hierarchies can co-exist. A good way to meet these requirements is for hierarchies to be maintained directly in the EDW via a dynamic front-end process. Appropriate software is available to enable business units and functional areas to update hierarchies online to meet their analytic requirements.

Hierarchies are not typically required for transaction processing. With the EDW meeting all business intelligence requirements, it is appropriate to have hierarchies sourced in the EDW or in a master data management system feeding the EDW.

The charts below illustrate some of the many interactive analytic views of information available from an EDW. Queries, as illustrated by the interconnecting lines, result in summarized, easy to understand results. Well-designed queries will allow interactive drill-down to actionable detail on any of the three hierarchical dimensions (product, customer, and geography hierarchies for the customer focused example, material, vendor and geography hierarchies for the procurement focused example). Queries may also allow drill-across to other facts or time dimensions.

Part 2 of this series will begin addressing how the EDW is used to reinvent business in the enterprise. Business improvement opportunities described in Parts 2 – 6 are grouped into 5 categories:
  • Enterprise

  • Marketing and sales

  • Financial

  • Supply chain

  • Information technology

SOURCE: Reinventing Business: Enterprise Data Warehouse Business Opportunities for Manufacturing, Part 1

  • Allen MesserliAllen Messerli
    Allen Messerli, President of Messerli Enterprise Systems LLC, specializes in enterprise data warehouse consulting, and has provided vision, direction and leadership for 400 major enterprises globally. Previously he had more than thirty years experience in a wide variety of positions at 3M, with an extensive record of successfully managing large-scale, innovative information technology solutions across supply chain, manufacturing, sales and marketing functions. 3M is a diverse global manufacturing company, with 40 business units operating in all countries and selling 500,000 products through most market channels. Al conceived, justified, architected, and directed implementation of 3M’s Global Enterprise Data Warehouse, which contributed more than $1 billion net business benefits with a very large ROI, and is now a global best practice enterprise data warehouse. He has extensive leadership experience in industry, national, and international logistics and electronic commerce organizations, and was a pioneer in electronic business and data warehousing, often speaking on these subjects around the world.

Recent articles by Allen Messerli



Want to post a comment? Login or become a member today!

Posted April 16, 2010 by Phil Bailey phil.bailey@btconnect.com


Thankyou for sharing this very useful and succinct overview of how an EDW should be used to help a manufacturing company. It's a very good reference, and is refreshingly vendor and technology buzzword light.

I look forward to any of your futue posts.


Is this comment inappropriate? Click here to flag this comment.


Copyright 2004 — 2020. Powell Media, LLC. All rights reserved.
BeyeNETWORK™ is a trademark of Powell Media, LLC