We use cookies and other similar technologies (Cookies) to enhance your experience and to provide you with relevant content and ads. By using our website, you are agreeing to the use of Cookies. You can change your settings at any time. Cookie Policy.

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

Originally published August 5, 2010

Business Improvement Opportunities

While Part 1 of this series provided the definition and scope of the enterprise data warehouse (EDW), Parts 2 – 6 address how the EDW is used to reinvent business. Thirty major business improvement opportunities are described. Collectively, they comprise revolutionary change for most manufacturers. Each opportunity is described as follows:
  • Objective describes the goal state.
  • Background describes a typical current state without an EDW.
  • New Process describes recommended processes and EDW functionality.
  • Leadership summarizes management focus required to achieve the results.
  • Results are business improvement opportunity financial benefit potentials as a percent of revenue for a “typical” manufacturer. These should be modified based on current information system capabilities, current costs, and opportunities appropriate to the specific business and industry.
Two common methods for evaluating investments are ROI (return on investment) and NPV (net present value). Some companies use both. In either case, business improvement opportunities (returns) should include both cost reductions and incremental profit margin on increased revenue expectations. Because an EDW is strategic, use of a five-year scope for the investment calculations is recommended. A time-phased financial analysis, recognizing the implementation timing and benefits of each phase, should be used.

Because the cost of implementing an EDW depends on company specifics, notably the number of source systems, this series does not include ROI estimates. But, best practice EDWs have been proven to provide exceptional ROIs.

EDW implementation cost estimates should be based on net added cost beyond spending for the current business intelligence (BI) environment. BI environments with many data marts and operational data stores are expensive. During EDW implementation, spending on data marts and operational data stores should be minimized so the net added cost is less than the total EDW cost. An EDW does not require re-engineering of operational processes, so it is substantially easier, faster, and less expensive than enterprise resource planning (ERP) projects.

Net present value calculations state future costs and benefits in terms of today’s money, using an appropriate annual cost of money to reduce future cash flow values to today’s value. This enables direct comparison of ongoing benefits with one-time investments, such as an EDW implementation. To simplify the concept, NPV for each opportunity is included in Part 8. The stated five-year NPV is four times the annual benefit, using a simple and conservative valuation. This represents roughly an 8% annual cost of money for the next five years. Using a lower value of money or longer time horizon will increase NPV.

Part 2 of this series described business improvement opportunities in the enterprise, Part 3 described opportunities in marketing and sales, Part 4 continued with a description of financial business improvement opportunities, Part 5 covered opportunities in the supply chain, and this installment focuses on opportunities in information technology.

Information Technology

The three business improvement opportunities described in this section primarily impact IT functions.

1. Data Mart Consolidation

Enterprise strategic, tactical, and operational business intelligence (BI) and decision making processes are optimized and costs minimized with one comprehensive integrated EDW, as described in Part 1.

Most large manufacturers have tens, or even hundreds, of redundant data marts, operational data stores, reporting systems, and ETL processes for BI or decision support. Reporting and query functions are often performed on systems designed for transaction processing, which are less efficient for business intelligence (BI). Independent data marts are found in business units, functional areas, subsidiaries, and on people’s desktops. They are almost universally prevalent in marketing, sales, finance, and supply chain functions. The independent data marts require redundant ETL functions and redundant database analyst (DBA) support – even when the user does it himself.

Corporate IT departments often avoid looking too deeply into the real cost of business unit data marts because of friction with business units or business unit IT groups. And, they often overlook the costs of inefficient legacy reporting systems in their own shop. These "resistance to change" obstacles need to be overcome and the benefits quantified in order to gain real commitment and support for the EDW.

Even companies with a corporate BI strategy and an integrated architecture often have many dependent data marts because their database management systems (DBMSs) don't scale to support all the data and users in the enterprise.

New Process:
Replacement of all data marts, operational data stores (ODSs), and reporting systems is a predefined outcome of the EDW project. Transaction processing systems do what they are designed to do best – process transactions. The EDW is fed atomic detail directly from source transaction systems. Call center, sales automation, Internet site, and other customer relationship management (CRM) systems also feed “touch-point” information to the EDW. The ETL process occurs only once – from source systems to the EDW.

History is stored in source transaction systems only as long as required for normal operational processes (typically about one month for order and invoice information). Only detailed, current status queries from inside the company access the transaction processing system. There are no independent or dependent data marts. Operational processes access the EDW, which is also the ODS, for historical information. Master data from all sources is integrated in the EDW, and it enables sharing of master data with other internal and external systems.

A decision to consolidate data marts is often made in IT. However, the decision is often an infrastructure decision, and means simply putting multiple data marts on one machine. Without data integration, putting the data on one machine does not make it an EDW. A common strategy is first to put all the data marts on one machine (“fork-lift” data), then later migrate toward data integration and a real EDW. That approach is possible, and it may create short term savings. But don’t expect it to simplify EDW implementation.

The recommended approach is to get an EDW decision made or approved at the corporate executive level. With corporate executive support, resources can be migrated from other BI initiatives to the EDW implementation, support for competing systems is minimized, and competing projects stopped. Yes, it is possible to get started without getting such corporate executive commitment, but success is difficult and rare.

The savings potential from eliminating all the data marts, ODSs, reporting, and other BI systems is likely to justify the entire EDW implementation, without any additional business benefits. Of course, it will require substantial experience, skill, and tact to discover all the BI systems and all the associated hidden costs. Typical data marts have been found by industry consultants to cost between $250,000 and $1 million annually each, with total cost of at least .1% of revenue. They should all be eliminated, improving profitability by .1% of revenue.

2. Shared BI Applications

Shared common BI applications, developed and supported centrally, meet 80-90% of query and reporting requirements. Expensive redundant application development is avoided. For more unique analyses, or ad hoc queries, a common BI tool is used with people in each business unit and functional area fully trained in its use. Thus, everyone has access to “any question, any time” without waiting for central IT support.

Most companies suffer from an IT backlog of requests for new reports, data marts, cubes, views, or queries required to answer business questions. Business decision makers can’t get the information they need when they need it, so they are frustrated with IT. Even with a data warehouse, it is common for companies to provide a standardized database, but allow applications to be developed in the business units and functional areas. The result is many redundant applications, except for minor report format variations.

Also, many large manufacturers have not standardized on one BI tool set for ad hoc queries. Failure to standardize on one BI tool set results in redundant training, inability to tune for optimal performance, and poor utilization of computing resources. The total cost of buying, maintaining, and licensing multiple tools is high, as is the (hidden) cost of retraining when people move between business units or functional areas. Inconsistent data models and data definitions exacerbate the problem.

New Process:
An EDW with flexible dynamic hierarchies, as described in Part 1, is recommended. When businesspeople are given the ability to maintain hierarchies (such as product, customer, geography, etc.) to match their responsibilities, they can use common query and reporting applications for most common queries. The user chooses the appropriate hierarchies from a menu, the appropriate level of detail from each hierarchy, and the desired time dimension, then submits the query. After getting a response, they can interactively drill down any hierarchy to the level of detail required to take action. This allows all business units to share query or reporting applications for common information like sales, demand, service, profitability, cost, inventory, procurement, etc. (The examples in Part 1 illustrate common applications with the ability to answer thousands of specific queries.)

Additionally, the EDW front-end application for maintaining hierarchies should also enable users to enter performance goals associated with their hierarchies. Then, common exception reporting applications can be used to generate exception messages to appear in personalized portals or e-mail.

Common query and reporting applications supported centrally are built and tuned to perform most efficiently, and are not constrained to using the ad hoc BI tool. (BI tools may emphasize ease of use and development over performance). 80-90% of queries use common applications, thus minimizing redundant development. Common applications are designed for ease of use with intuitive interfaces, requiring minimal end user training. Standardized metadata in the EDW helps assure that both common and ad hoc application development share standard data definitions, data model, and summary algorithms.

One standard BI tool set, for the 10-20% more unique ad hoc queries, reduces developer training and the number of developers required. The tool set (choose from Cognos, Business Objects, MicroStrategy, or others) should include the components needed for active BI, including a standard portal, online analytical processing (OLAP), ad hoc query development, scheduled reporting, messaging, etc.

Corporate executives, as part of the EDW strategy, should endorse and support migration to common applications for optimal EDW benefits. Also, one BI tool set for ad hoc development should be chosen, with investment and support for others phased out. IT leadership should establish the common application development team responsible for understanding BI requirements, identifying and implementing common applications to meet 80-90% of query and reporting requirements. They should be directed to adopt best features of existing applications and to assure that the common application performance is better than similar ad hoc query performance.

For a typical company, direct savings will conservatively increase profitability by .05% of revenue. Best-practice experience is substantially more.

3. ERP Cost Reduction

Business executives and IT people understand that ERP (enterprise resource planning) standardization is not required for standardized business intelligence. BI and decision making requirements for the enterprise are met from the EDW, which may be sourced from multiple, different ERP systems. ERP projects are done only when necessary to improve operational performance or to replace operational systems which are no longer sustainable. [It should be noted that “ERP” is a misnomer commonly used for operational transaction systems.]

It is a common belief in manufacturing companies that operational (“ERP”) system standardization is required to achieve standardized business intelligence and financial reporting. This idea is propagated by ERP software vendors and consultants who make big money doing ERP implementations. It is not unusual for large manufacturers to spend a billion dollars or euros trying to standardize their ERP system(s). Most large manufacturers have not achieved a single ERP system, or even multiple instances of a single ERP design, because of decentralized decision making, acquisitions of companies with different ERP systems, and the challenge of trying to standardize operational processes across business units globally.

New Process:
An EDW project is far easier, faster, and less costly than an ERP project because it does not require re-engineering of operational processes. Data can be extracted and transformed from many different operational systems into one standardized and integrated EDW. The EDW data model and standards are designed for BI, not for transaction processing. ERP systems change as commercial software development technology changes (the latest at the moment being SOA, or service-oriented architecture). The EDW data model and basic functionality does not need to change if it was properly architected to meet long-term “any question, any time, from any perspective” requirements, as recommended in Part 1 (although improving BI tools will provide improving user interfaces and BI functionality).

IT leadership takes responsibility for communicating with corporate and business unit executives to assure broad understanding of the proper reasons for ERP spending versus EDW spending. ERP projects are justified for process improvement or legacy system support issues. Understanding develops that ERP systems do not meet BI requirements. Regardless of ERP standardization, an EDW is required. ERP standardization is not required for BI standardization.

Very conservatively, an EDW will reduce ongoing ERP spending and increase profits by .25% of revenues. Best practice is much higher.

Having concluded our discussion of business improvement opportunities with this installment, Part 7 of this series provides business analyses examples and a business improvement opportunities summary.
  • 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!

Be the first to comment!