During the past year, we have seen tremendous interest in data warehousing from companies that have implemented or are in the process of implementing Enterprise Resource Planning (ERP) solutions. Companies have realized that to maximize the value of the information stored in their ERP systems it is necessary to extend these ERP architectures to include more advanced reporting, analytical and decision support capabilities. This is best accomplished through the application of data warehousing tools and techniques. "ERP-DW" is the integration of data warehousing with one or more ERP packages, such as SAP, Oracle, PeopleSoft or Baan, to provide a comprehensive information delivery solution. Our experience has shown that the ERP system cannot meet all of the analytical and reporting needs of an organization for a variety of reasons including:

  • The requirement to integrate information not contained in the ERP systems;
  • ERP databases and files have been designed to optimize performance for getting data into the applications and, therefore, lack the constructs required for multidimensional analysis; and
  • Most ERP solutions lack the advanced functionality of today's leading reporting and analytical tools; and for those ERP systems that have this functionality, companies are hesitant to use it because of the performance impact on their operational (on-line) systems.

There are a number of unique challenges associated with ERP-DW projects. The good news is that effectively implemented, an ERP-DW can significantly extend the value of the ERP to the organization.
The business case for an ERP data warehouse goes beyond traditional analytical needs and reinforces the need to plan for an "enterprise information delivery architecture," not just the ERP or data warehousing architecture. This can be decomposed into two components ­ enterprise information and delivery architecture.

Enterprise Information. One of the great benefits of ERP solutions is that by their nature they force the integration of corporate data. Companies are able to move from "stovepiped" transaction systems that typically contain duplicate and inconsistent data to a well-defined source of key data. Most executives expect that the implementation of the ERP will correct the kinds of information discrepancies that result when reporting is done from multiple legacy stovepiped systems.

It is essential then to avoid stovepiping data "out" of the ERP package into disassociated data marts. Even though the data is consistent at the source, uncoordinated extraction timing, inconsistent duplication of data and multiple definitions of key data elements will result in discrepancies in reports across subject areas. This can easily create the same frustrations in the boardroom that the company faced before the ERP was implemented.

To avoid this situation, the data warehousing architecture must consider requirements at the enterprise level. Separate strategic initiatives will need similar data; this data should be extracted only once from the ERP system and at the appropriate level of detail to support multiple information needs across the organization.

Information Delivery. ERP solutions are built on on-line transaction processing (OLTP) architectures which excel at processing transactions and getting data in, but are not designed for the most efficient retrieval of data nor for the provision of robust analytical capabilities. What is often overlooked with ERP implementations is that operational reporting may create a significant degradation in OLTP performance. Degradation of this kind is most likely where there are: high transaction volumes, many customers/products/markets or other dimensions, 7 x 24 operations, the need for summary as well as detail data, and dynamic hierarchical reporting structures.

Many companies have addressed this challenge by moving general reporting functions off the ERP server to an operational data store (ODS), data mart or other data warehousing architecture. Similarly, there are other implications that affect how you configure the ERP system. Do you establish complex reporting configurations on the ERP system; or do you simply ensure that the right raw data is collected, and then leverage the enterprise information delivery architecture for flexible reporting and analysis? In our experience, these architecture decisions should be made during the analysis and design stages of the ERP implementation.

What makes an ERP-DW different from data warehousing using other sources of data?

You may find it more difficult than you expect to get data out of an ERP system. Proprietary storage formats, complex data structures with thousands of tables, a variety of extraction strategy choices and difficulty in capturing changed data make the ERP data extraction process very complex. You may also have to work within limited batch windows on the OLTP system, where data extraction schemes that are extremely resource intensive can add to the challenge.

The good news? Many ERP solution providers, such as SAP and PeopleSoft, are providing enhanced data extraction functionality as an integral component of their analytic reporting application solutions. Further, we have seen an explosion in the number of companies focused on delivering information extraction functionality optimized for specific ERP solution.

Another challenge peculiar to ERP-DW is the inherent conflict in "process-driven" and "analytical-driven" information requirements. The stakeholders associated with an ERP implementation often do not "sync-up" with the stakeholders involved in the analytical system implementation. This may mean that input critical to strategic reporting requirements may be overlooked, resulting in the organization being unable to capitalize on the true value of an enterprise information delivery architecture.

Once again, some good news comes from the technology providers themselves, where their efforts to incorporate analytical reporting functionality in their solution architectures continues to raise the awareness levels of this key issue at all levels in the enterprise. Further, our experience shows that there needs to be a great deal of coordination between ERP "process" and data warehousing "analytical" efforts. While the coordination of effort can start at any point in the life cycle of the ERP implementation, we have seen that those companies who incorporate the process of data warehousing early in the ERP implementation tend to maximize the value of both investments. Specifically, companies should focus their teamwork and collaboration efforts to:

  • Decide what reporting should be done through the ERP system and what should be done to meet performance and reporting functionality requirements. A spectrum of reporting needs will develop ­ some best for the ERP system and some best left for a data warehousing architecture.
  • Decide whether the data warehousing architecture can be used as a "hub" to handle interfaces to downstream reporting applications as an alternative to developing custom extraction solutions.
  • Establish a strategy around historical data. What will be converted to the ERP and what should be brought into the data warehouse?
  • Coordinate the roll out of the ERP applications with the data warehousing architecture. Depending on when specific business functions are implemented, there may be a need to develop multiple extraction processes from both legacy and ERP applications.

Finally, many exciting changes are taking place in the ERP market with significant implications for enterprise information architectures. As mentioned earlier, ERP technology providers have embarked on significant investments to offer analytical reporting architecture solutions, including data extraction, business content applications and the analytical tools themselves. We have also seen an increased interest in industry vertical reporting solutions that are integrated with an ERP package. While these "packaged" solutions may shorten the implementation time for a specific analytical reporting application, they do not replace the need for an enterprise information delivery architecture.
In summary, the fact remains that companies throughout the world are investing heavily in developing both ERP and data warehousing solutions. Unfortunately, in many cases, these are being planned and managed as separate, non-integrated efforts. However, it is encouraging to note that this trend is shifting, and companies realize that it is only through the integration of ERP and data warehousing that they will be able to maximize the value from both of these strategic investments.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access