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 8

Originally published October 21, 2010

Part 1 of this series defined the EDW and summarized its data contents. Parts 2, 3, 4, 5 and 6 described and quantified 30 major benefit opportunities. Part 7 contained 10 business analysis examples for enabling benefits and summarized the benefit potentials as a percent of annual revenues. The series now continues with a description of 30 best practices to ensure success.

Best Practices

Although not intended to be a comprehensive “how to” book on building an EDW, these top 30 best practices summarize the most critical elements leading to EDW success.

1. Linkage with Corporate Business Goals

Understanding the corporate business goals, as expressed by the CEO or Executive Committee, then linking and demonstrating EDW functionality related to these goals, is typically required to achieve and maintain support of top executives. This requires significant one-on-one communications with all senior executives and Executive Committee review.

2. Executive Support for Vision, Strategy, Investment

Gaining real support from the Executive Committee, or its equivalent, involves more than a vote for funding. All members should be committed to the EDW vision and strategy, including alignment of their respective functional area or business unit with the strategy and plan. [Continuing to initiate competing business intelligence (BI) projects is not commitment. Such redundancy is expensive, inefficient, and is exactly what you are trying to eliminate.] The people working on competing data mart or reporting projects should be integrated into the EDW support organization. Critical business projects nearing completion may be finished, but newer BI initiatives should be integrated with the EDW implementation, with appropriate priority. All the existing data marts, reporting systems, and BI applications supported by IT or business groups should be migrated to the EDW, with migration strategies and plans established for each.

3. Enterprise Justification, Capitalization, Amortization

Justification requires ROI projections and ongoing measurement of costs and benefits. Both should include impacts on all business areas – not just IT. Of course, IT itself should expect significant cost reductions. Capitalization of both the platform and development is appropriate due to the strategic nature of the investment. Capitalizing each phase, then amortizing it over 3-5 years starting at implementation time, avoids a negative impact on business unit operational performance by aligning costs with benefits. Amortization should spread costs to all business units, not just first implementers.

4. Centralized EDW Support Organization

The EDW is a centralized information strategy and requires a centralized support organization going beyond infrastructure. Without this type of organization, progress will likely be slow because key resources will have competing work priorities, potential benefits will not be fully achieved, and competing projects will likely continue. The centralized information strategy and support organization does not imply centralized business management. In fact, it enables decentralized business management.

5. Corporate Information Ownership and Access Policy

Achieving the benefits of an EDW requires broad usage and access to information for all business units and functional areas. Traditional security policies were typically established by the functions from which the data came. These policies should be changed to be based on the need to know and the opportunities derived from sharing information. Access to specific categories of information should be approved at appropriate levels of user management, not by source function management. This requires additional trust, education regarding non-disclosure, management understanding their approval responsibilities, database level security controlled by “views” that limit the accessible information, and auditing to assure there is no misuse.

6. Source Responsibility for Data Quality

Good data quality is vital to EDW success, but good data content can only come from source functions and systems. Data content can not be successfully corrected by the EDW or EDW support functions. Management of source functional areas must be held accountable for source data accuracy. Data quality ultimately should be treated like product quality – with everyone held accountable for the data they input. Senior management should understand, approve, and support this policy.

7. Warranty that EDW Data Reflects Source

The EDW support organization must take total responsibility to assure that EDW data accurately reflects source system data, and to provide visibility of the source system and individual or functional area responsible for the data. [Source information is a component of metadata, which should be contained in the EDW.] Of course, the EDW quality function should attempt to identify and highlight questionable data, and provide ongoing feedback to the source. Given the ability to drill down to detail, and a policy of highlighting questionable data (embarrassment policy), EDW users can find the source of questionable data and get it fixed via the source functions. Data corrected at the source is automatically fed to the EDW.

8. No Required Changes to Source Systems or Processes

It may be tempting to make the EDW dependent upon ERP (operational) system changes. Waiting for ERP changes is likely to result in never achieving the EDW goals, since ERP systems are constantly evolving. And, it is important to capture history from current systems even if the ERP is expected to change. Getting the data into the EDW now may significantly reduce the cost of ERP changes. It may also be tempting to wait for missing data to be included in source systems, or for bad data to be fixed. Again, waiting will likely mean you don’t achieve the goals. Rather, the EDW will provide an excellent tool to report and highlight source system inadequacies and get them fixed.

9. Prioritize by Need, 6-month Phases, Scale, Reiterate…

An EDW is not built overnight, nor is it built in one big bang. After establishing EDW strategy and vision, identify a high corporate priority which is feasible to implement. Then deliver real value in six months or less, on-time and within budget. Establishing credibility in the first phase is essential, so assure the right resources are in place. Delivering phase one value, as promised, will make it easy to get approval for phase two.

10. Leadership Focus, Credibility, Experience, Persistence

These leadership traits are required to achieve the success factors already identified. Substantial business and management experience is required for enough credibility to obtain the requisite executive support. Forming the effective, integrated EDW support organization demands strong leadership able to identify and recruit the best people from inside and outside the enterprise. A strong results focus and persistence are needed to assure success. Success will be assured when implementation has achieved a critical mass of usage across all functional areas and business units, when competing data marts are phased out, and when all BI applications utilize the EDW.

11. Comprehensive Logical and Physical Data Models

A comprehensive logical data model is required to assure that all data is included and that all the data relationships enabling any question from any EDW user can be answered. These relationships will almost certainly exceed those that exist in operational systems and data marts. People with strong business experience and the ability to think out of the box should define the logical data model requirements, with the support of a good data modeler. It will be easier to start with a good commercial model for your industry, available from a vendor. A good physical data model must reflect the entire logical model and enable required performance for BI applications.

12. Global Enterprise Data Standardization

Different operational or ERP systems typically exist in large companies, so there are inconsistent data values and meanings. These need to be transformed to standard values in the EDW that are usable throughout the company for business intelligence. This may require some adjustment on the part of users and sometimes, in the short term, reverse transformations for operational intelligence integration with operational systems. But, over the longer term, operational systems may migrate to use the common data standards, and new systems should be implemented using the common data standards. Over time, significant productivity and communication benefits will occur from this standardization.
13. Subject Area Owners Assure Data Integration

Subject area owners (SAOs) are people in the EDW support organization with full responsibility for data integration and standardization. They assure that data in their respective subject areas meets the requirements of all functional areas and business units. They also assure that data redundancy is eliminated and data is standardized. These responsibilities are executed through the leadership of subject area user forums consisting of representatives of business areas using the data. The SAOs and the user forums represent the key data governance process.

14. Any Question, Anytime, Anywhere

Using a combination of shared applications and an ad hoc business intelligence (BI) tool, businesspeople should have access to any information “on-demand.” This requires a centralized group of specialists to construct shared applications. And, it requires trained individuals positioned to support knowledge workers by assisting with ad hoc queries. They should be trained to provide ad hoc queries on a fast turnaround basis using the selected BI tool. In both cases, these should be full-time positions. Any question anytime drives the inclusion of all atomic detail information in the EDW.

15. Single Version of the Truth – Normalized Data

The EDW approach eliminates multiple data marts, cubes, or star schemas designed to meet requirements of individual business units, functional areas, and user perspectives. Data in an EDW is normalized, avoiding redundancy and assuring standardization. All functional areas use the same data source with “views” providing each functional area or business unit with easy access to the data they need. For example, transactional and master data is stored only once in the EDW – not replicated into multiple data marts for each functional area (finance, sales, marketing, and supply chain) or business unit. Data marts and data replication introduce excess costs, errors, and inconsistent definitions of information resulting in different answers depending on who asks and where the information is obtained.

16. Atomic Data for Accountability and Analysis

Atomic data includes all data content from all source systems. Capturing detailed atomic data from all source systems and loading it accurately into the EDW assures that the EDW meets the requirement of “any question, anytime.” Information about the source system and persons or organizations responsible for source data should be part of the metadata in the EDW. When there are questions about summary information, the EDW user can drill down to the atomic data. This visibility drives source accountability for accuracy. And, detailed atomic data is often needed to provide enough information to support reliable analysis and timely decisions.

17. Touch It, Take It Data Sourcing

Traditional BI projects involve major resources and time to define requirements, determine where and how to get the information, then to deliver an application. As additional BI functionality is needed, or additional requirements are identified, BI teams go back again and again to the same source systems to extract more information. Such ETL reengineering is time consuming, expensive, and disruptive. The best-practice EDW approach is faster and less costly if all information from source system files or databases is extracted when they are first “touched.” The information can be loaded into the EDW, even though it may not be used until later phases.

18. Capture All Events and Touch Points

Each transaction, event, or touch point that creates data in a source system is important to include in the EDW for comprehensive business intelligence. Transactions may be physical or financial, and internal or external. Events may be triggered by internal system processes, not human activity, yet represent critical BI information (such as an order released for filling based on inventory availability or an automatically assigned promise date). Touch points include customer calls, complaints, website navigation (clickstream), or any other non-transactional activity important for business intelligence, such as providing a complete view of the customer, vendor, or partner.

19. Drill Down to Actionable Detail

The EDW will provide not only online summary reporting, but the ability to drill down interactively (real-time) to the detail necessary to take action. Taking action often requires visibility and analysis of specific transactions. Closing the gap between reporting and action will yield substantial productivity benefits and organizational optimization, whereas the gap often results in failure to act on problems. The ability to drill down quickly from summarized reports or scorecards to lower levels of detail is required to close the gap.

20. Drive toward a Role-Based Event-Driven Culture

The EDW support group should actively lead the enterprise away from standard, scheduled reports to a “role-based, event-driven” culture. This change is dependent on clear definition of responsibilities with defined goals and metrics (e.g., KPIs for sales, profitability, costs, quality, inventories, on-time delivery, etc.). With responsibilities and KPI goals included in the EDW, applications can actively compare results with goals and notify the responsible person when action is required. These notifications include the ability to drill-down to actionable detail, particularly exception detail representing missed goals. Prompt action using timely, accurate, comprehensive business intelligence distinguishes successful companies. The goal of the role-based, event-driven approach is to minimize the time and effort involved in knowing when to take action. Rather than people needing to peruse detailed reports to find exceptions or out-of-control situations, EDW-based BI applications do the perusing for them based on predefined rules or goals.

21. Shared BI Applications Reduce Redundancy

More BI efficiency and effectiveness will result from centrally supported, shared BI applications for common types of queries. Users should have a portal providing easy access to commonly used information such as customers, products, revenues, profits, prices, account status, customer service, inventory, assets, procurement, etc. A drop-down menu approach is used to let users select an appropriate view of the information they need based on hierarchies of business unit, product, market, customer, geography, and time dimensions. Drill-down to more detail within any hierarchy or view should be enabled. This approach provides optimal performance for common query types, and avoids the massive redundancy involved when hundreds or thousands of users write similar ad hoc queries to access commonly used information. A good goal is for 80-90% of BI queries to use shared applications.

22. Dynamic Hierarchies and Views

Hierarchies or “views” of data are constantly changing, and are often different for each functional area. Examples include product segmentation, business unit alignment, market and channel segmentation, customer organization and ownership, geographic responsibilities, sales territories, and internal management responsibilities. These views or hierarchies should be dynamically maintainable online, enabling “real-time” summary data restatement reflecting the view required by the user. The functionality should support “effective dating” allowing “what-if” versus “as-is” scenarios to be rapidly and accurately evaluated. It also supports keeping a history of “as-was” hierarchies, if required for historical reference (without keeping summarized data). Dynamic hierarchies are a powerful tool for marketing, sales, financial, and supply chain management to analyze customer segmentation, product segmentation, profitability and customer service, manage broker or sales territories and compensation, and a wide range of other analytics.

23. Eliminate the Corporate IT Bottleneck

Many companies suffer from the IT (or IS) 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. It is better to proactively create the EDW, provide a BI tool for ad hoc queries, build shared applications to enable 80-90% of common queries, and train the business unit BI support function. This EDW approach will result in faster and more effective BI application implementations, and reduce the costs of redundant query development. It enables “any question, anytime” without waiting in the IT queue. IT becomes the enabler, not the bottleneck.

24. Timely Current and Historical Information

The best-practice active EDW will be updated at least daily but, ideally, it will be updated near real-time or continuously. This provides a single point of access to current status information, enables role-based, event-driven functionality (see point 20 above), and provides the historical information often required for timely action. For many enterprises, seven to ten years of history detail is needed to meet all requirements, but industries with massive amounts of detail (retail, telecom, etc.) typically keep less transactional history (like POS and call detail). Other industries (like transportation equipment manufacturers) may need much more history. Before you replace detail with summary information for historical purposes, remember that summaries cannot be restated when hierarchies change!

25. Master Data Management Focal Point

Master data includes relatively static reference data like customer, prospect, product, material, service, vendor, person, location, and work center information. There may be many sources of master data, but the EDW provides a focal point to consolidate and rationalize master data, then to share it with operational systems. Thus, for example, customer and product data can be pushed to multiple fulfillment systems. New product data can be pushed to channel partners like distributors or retailers. People or systems responsible for setting up new accounts may access the EDW to find existing data, avoiding future redundancy.

26. Views as Virtual Data Marts

Views are techniques to simplify EDW access for specific areas and uses. Views minimize the knowledge required for ad hoc developers or business users in functional areas, provide predefined calculations or algorithms, facilitate easier implementation of security, and simplify complex query definition (i.e., table joins). Views make the EDW more user-friendly to business users, acting as virtual data marts – a useful concept in reality and politically.

27. Think Big, Start Small – Phased Implementation

Establish the five-year vision and plan. Define the first phase with specific functional deliverables due in six months or less. Get executive support for the vision (per point 2 above) and funding for the first phase. Implement on time and within budget. This approach minimizes risk and helps get funding approval. The vision is nice, but executives like fast results. Then, do the next phase and scale the platform for greater usage. The functionality to be delivered in each phase will drive the source systems and subject areas to be included. But, remember, “touch it, take it” (see point 17 above).

28. ERP for Better Process, EDW for Better Information

The EDW data model and data standardization should not be dependent upon an ERP system that will change. It is best for the EDW data to reflect long-term business intelligence and decision support requirements. These may be different than transaction processing requirements. The EDW does not need to change when ERP systems change; thus, it accommodates business acquisitions and ERP upgrades. This requires EDW subject area owners to be experienced, yet able to think “out of the box.” They need to envision the “any question, anytime” perspective from the viewpoint of all the functional areas including external users like suppliers, channel partners, and customers. The EDW is likely to become the enterprise data model, with operational system implementations adopting its data standards rather than vice versa.

29. Pick the Right Technology

Make the right platform technology decision based on proven performance and references. It must be easily scalable, high-availability, and high performance for continuous loading with concurrent simple and complex queries. MPP technology will be required for medium and large enterprises. If in doubt, benchmark. Remember, the goal is to integrate all data with everyone using it, so benchmark continuous loading, many concurrent users, and the right combination of simple and complex queries.

30. Continuous Communications throughout the Enterprise

Continuous communications and marketing to all functional areas and business units is required. It is easy for people to forget about the EDW and revert to “going it alone.” Be sure everyone, at all levels, knows where you are going, what is available today, and coming deliverables. Initial communication and an annual update from the CEO are appropriate to remind people of the executive commitment, and can be used to challenge competing initiatives. Distribute a periodic glossy brochure, in addition to memos and emails. These are less likely to be discarded, and can be useful pinups on bulletin boards and cubicle walls. Be sure everyone understands that it is their system, not yours.

Part 9 of this series will cover governance and organization.
  • 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!