Many companies face more and more requests to view data at a global level. This presents a challenge in many organizations because data exists across the organization or siloed into systems. This does not allow for a quick and easy rollup into a global reporting instance. Often, this type of landscape occurs because developers configured different systems at different times with various objectives. Proper global governance and vision are necessary to assure that this data rolls up properly to a global enterprise data warehouse (EDW).

This also happens when a system’s landscape includes multiple BW or R/3 instances because these systems often do not share common configuration or master data. Most companies report globally even without an EDW. However, this often involves gathering data manually from the various source systems and synchronizing the data manually to a global view. This requires a lot of resources and it’s easy to introduce errors into the data. Figure 1 shows the architecture of a typical EDW.

Figure 1

EDW architecture with three separate R/3 systems and a legacy transactional system. The transactional systems feed four separate data warehouses and then feed the EDW.

An EDW project dramatically benefits all departments of a company.

• Marketing. An EDW brings visibility to sales and marketing program effectiveness and product line profitability across the entire organization.
  
  
• Pricing and contracts. These departments can better understand pricing and usage demand to optimize negotiation.
  
  
• Forecasting. An EDW provides timely visibility of global demand.
  
  
• Sales. This department can better determine sales profitability and productivity for all territories and regions. They can use results by geography, product, sales group, or individuals across the enterprise.
  
  
• Financial. This department receives daily, weekly, or monthly results quickly, enhancing financial management throughout the organization.
  
  
• Supply chain. An EDW enables faster, thorough analysis of purchase quantities and prices.
  
  
• Inventory management. This department gains a holistic view, which helps identify ways to manage inventory more effectively.
  
  
• Customer service. They can deliver consistent customer service metrics for all facilities.
  
  
• Information technology. This team can reduce its manual consolidation workload by providing each user with fast and easy access to regularly used queries, reports, or analyses.

An EDW project is a difficult and sometimes painful process in many organizations because it combines many diverse groups. An EDW project should meet the following goals:

• Assess data from multiple source systems
  
  
• Extract the data from these systems
  
  
• Align and cleanse the data as necessary
  
  
• Load this data into a new EDW environment so data appears to come from a single source

The source systems may be multiple R/3 systems, BW systems, other legacy systems, or a combination of these. Many different people may own the transactional or master data, which increases the complexity.

Thus, many EDW projects fail or don’t realize their full potential. In fact, some experts estimate that as many as half of all EDW projects fail or have only limited acceptance in the organization. How can you make sure that your project is successful? Understand the challenges involved and implement measures to combat the most common challenges associated with an EDW project.

The overall challenges surface in the following categories:

• Cultural. The culture of the organization may not be ready for an EDW. Although management may feel that an EDW will bring great benefits, the organization may not agree or follow the EDW standards.
  
  
• Political. Many organizations do not want to give up their local reporting flexibility to enter into global standards. The EDW might also reduce the number of reporting systems in an organization. The prospect of retiring formerly essential systems may cause anxiety among users.
  
  
• Technical. Sometimes the technical infrastructure that supports an EDW is not robust enough to handle the data volume. An EDW may require a network upgrade or enhanced hardware to support global reporting. Technical problems may arise when trying to consolidate the disparate systems.
  
  
• Fiscal. An EDW effort can cost a great deal of money because of the significant challenges with data integration, consolidation, and cleansing. This process is most successful if funded as its own project.

I will discuss the top reasons that an EDW project fails and provide some tips to avoid this in your EDW projects. While this list covers some of the main gotchas of EDWs, this is not an exhaustive list. You should use it as a guide to help you plan your EDW strategy.

1. Clearly Define and Communicate EDW Goals

W.H. Inmon, considered by many the father of the data warehouse, believes that if no organizational momentum toward a common goal exists, then the best architecture and framework in the world are bound to fail.

Inmon points out that uniform and clear EDW goals must exist across the organization. Upper management should drive these goals. In addition, end users should demand global reports to do their jobs better. The overall focus of the EDW project must be on the payback and benefits to the organization. You can communicate this message with clear goals and a clear measurement strategy. Concisely describe how to know when you’ve reached the goal and how everyone will benefit.

The EDW is much more likely to fail if managers do not build the data warehouse to solve a well-defined business problem. The specific business problem must document pain points clearly.

In Paul J. Meyer’s book Attitude Is Everything (Paul J. Meyer, 1999), he discusses setting what he calls SMART goals. These consist of a clear set of goals that are specific, measurable, attainable, realistic, and tangible. Apply and generate clear goals and measure strategies for these goals to gauge the success of your EDW project.

• Specific. You have a much greater chance of accomplishing a specific goal than a general goal. To set a specific goal, you must answer the six W questions: Who, What, Where, When, Which, and Why. Clearly state these specifics as part of the EDW strategy. For example, “Drive supplier contracts down 25% by consolidating information and negotiating globally rather than regionally.”
  
  
• Measurable. Establish concrete criteria for measuring progress toward attaining your goals.
  
  
• Attainable. Goals must be attainable to position the organization for success. Get consensus on the EDW goals and if possible, sign off on these in the project charter.
  
  
• Realistic. To be realistic, a goal must be one that you are willing and able to achieve.
  
  
• Tangible. Set clear and tangible measurement standards for success and measure these during milestones in the project. Clearly communicate the progress to the organization.

Another important part of goal tracking is to determine and document the stakeholders in the organization. Stakeholders are individuals or organizations who are actively involved in the project, or who have an interest (positive or negative) in executing or completing the project. Understanding these stakeholders helps to define scope and success. Not meeting the needs or expectations of just one influential and powerful stakeholder at a critical time can doom an otherwise successful project. Thus, you must discover and align stakeholders’ expectations and their impact on the project.

The very nature of an EDW requires a great deal of communication, not only during implementation but throughout to enforce governance. This means that the global team needs to be aware of the ongoing changes to local systems to judge the impact to the EDW.

2. Establish Strong Governance Rules

This vital step helps to regulate the EDW. An EDW is only valuable if the key figure values and characteristics are consistent. If one division calculates net sales one way and another calculates it differently, the aggregation of these values will be meaningless. This can destroy end users’ confidence in the quality of the EDW data! Once they lose confidence, it is often very difficult to win it back.

Many successful EDW projects start with a global template that establishes global rules and standards for data and configuration. You can then enhance these as necessary for local implementations.

You must document, maintain, and communicate the following to the organization:

• Standards for BW development. Clearly write documentation to guide the organization to BW development standards. Create standards for data warehouse development including documentation of query standards, master data standards, ODS development rules, and InfoCube development standards. The standards must allow for enough flexibility in the central model to handle local requirements as they arise. This is important because you cannot always map local requirements to global standards.
  
  
• Naming standards. Create naming standards documentation to make sure that naming conventions, measurements of variables, attributes, characteristics, and queries are consistent. This becomes even more important when you set security standards based on the naming conventions.
  
  
• Ownership standards. Establish who owns master data and the source of that data. This helps to govern a single source of data with clear responsibilities for data integrity.
  
  
• Roles. Define which roles are responsible for development and maintenance tasks and which groups can make changes to the structures. This document makes clear who handles local and global development, administration, and security.
  
  
• Stakeholder documentation. Describe the parties in the organization with a stake in the EDW.
  
  
• Global Key Performance Indicators (KPIs) documentation. Come to a global consensus on any KPIs across the organization. This is not always an easy task in an environment that follows local rules for KPIs. This often allows groups to calculate the same KPI in many different ways in the same organization. To measure KPIs globally, you must establish and maintain a common definition.
  
  
• Data quality audits. Document the details process for ongoing audits and tests of master data and transactional data quality.
  
  
• Security standards documentation. Establish the standards for how to secure data. What process should you use? Will it occur at the query level, at the InfoObject level, or by role?

3. Clean Your Data

One of the most common reasons for an EDW project to fail is that it contains dirty source data with missing, inconsistent, and erroneous data values. The EDW is only as strong as the data values in each data source that feeds it. Thus, pay particular attention to assure that this data is as clean and consistent as possible. While this seems like a rather obvious task, it is amazing how many times people assume that master data and transactional data are consistent. Many projects understate the time and effort you need to test and reconcile the EDW back to the source systems, thus assuring consistency. As already stated, the data quality is the most important factor for the organization to accept the EDW. It is far better to roll out less functionality and make sure that data is consistent than a great deal of functionality, InfoCubes, and queries that are inconsistent.

Remember, data quality is not a one-time concern; it is not uncommon for previously undiscovered data quality problems to occur after initially loading data into the EDW. Therefore, you must install procedures to govern actively and audit data quality regularly.

Master data alignment is the process of removing duplicates and bringing data together with a common key. For example, to perform global reporting on Wal-Mart, you must determine a common Wal-Mart customer number. All Wal-Mart customers must then reference that number. This is not always easy because of the many ways that each of the local systems stores each customer’s data.

Often, different systems do not use a common master data key. However, successful EDW master data needs to use a common key. This effort requires taking existing master data, removing the duplicates, determining commonality, and centralizing this data into one structure. This effort is often a very difficult task.

Most data alignment efforts must take place outside the EDW. This is because it is very difficult for BW to provide active governance and merge and match master data objects because they enter BW from several source systems. This alignment is vital to ensure that data from different source systems do not overwrite each other. For example, without master data alignment, one R/3 system can have customer 1234 as Wal-Mart and another R/3 system could have customer 1234 as Target. If both R/3 systems load customer data into BW, you must ensure that the EDW does not overwrite master data.

If you cannot easily harmonize data, you may want to attempt either a concatenated or compound key to help with this process. The compound master data key approach appends a key to the master data values with a designator for the source system. The concatenated approach increases the size of the master data key and prefixes the value with a source system identifier. Both approaches ensure that you duplicate no data when loading it into BW. See Tables 1 and 2.

The concatenation approach is less invasive and more flexible with the data model than the compounding approach, so in general terms, it makes sense to use the former. It is much more difficult to remove the compound key. Its value must always appear in queries or users can make an incorrect assumption. When you don’t show the source system, BW aggregates values based on the customer number. This can be a problem if the values are inconsistent. This does not occur with the concatenation approach.

When performing this data transformation, it is important for the organization to use consistent tools. These can include the SAP Master Data Management (MDM) module or a third-party extraction, transformation, and load (ETL) tool such as Infomatica or Ascential.

4. Carefully Plan Your Technical Infrastructure

Another reason that EDW projects fail is that designers do not give enough forethought to the organizational demands of a technical infrastructure. For example, if you’re creating a BW EDW for a global organization, you often need to feed the data across a wide area network. R/3 systems that feed BW are frequently located in many different geographical areas. Those R/3 systems need to feed the centralized BW EDW.

SAP issues no specific network sizing recommendations for connecting R/3 and BW. Its general recommendation is to have at least 100 MBit/sec on a dedicated network for reasonable throughput, no matter how often or how much data. This type of recommendation typically requires a hardware upgrade of the wide area network to provide reasonable throughput of data loads into BW and query processing performance.

Outside of data quality, the most popular reason an EDW project fails is that query performance does not satisfy the needs of the organization. Make sure to scale the solution to overcome performance issues.

5. Recruit Experienced Data Modeling/Data Warehouse Staff

This is another issue that seems to be quite obvious but it is consistently a problem in many EDW implementations. Data modeling decisions made early in the process take a great deal of effort to undo. Therefore, plan to get the best and most experienced data architect that you can find. This resource should not only be knowledgeable in data warehousing in general but more specifically in BW. Each data warehouse tool has nuances to data modeling that do not always translate across data warehouse platforms. This resource will be invaluable in mapping out the strategy and implementing a successful EDW.

Gary Nolan

Gary Nolan is an SAP NetWeaver BW-certified consultant and author of Efficient SAP NetWeaver BW Implementation and Upgrade Guide and Efficient SAP NetWeaver BI Implementation and Project Management. Gary specializes in gathering and evaluating requirements, configuring SAP NetWeaver BW, and providing project management, performance management, and data architecture and data modeling expertise. A former platinum consultant with SAP America, Inc., Gary has more than 20 years of SAP experience, working with SAP NetWeaver BW since version 1.2B.

You may contact the author at melvan.consulting@yahoo.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.