In Part 1 of this article (DM Direct, June 2002), we explored the basic categories of ETL tools and their characteristics. We also developed a list of criteria to consider when evaluating ETL tools. We will now consider the effects of ERP, CRM and EAI on ETL Tool Selection. There is also some pointed commentary on the role of consultants and vendors in your selection process.

ETL Selection Criteria: ERP, CRM and Other Complexities

The ongoing industry trend toward integration of applications in the form of suites and bundles has added new challenges for ETL. These integrated application suites, including enterprise resource planning (ERP), customer relationship management (CRM), supply chain management (SCM) and others provide a common data architecture for their various application modules. These architectures can actually complicate the process of extracting meaningful and timely data for integration. SAP is just one example of a very complex source system. Some CRM vendors combine dozens of previously freestanding applications into a suite with some shared data architecture. May ETL tools now provide extraction utilities specific to many ERP and CRM suites. These can prove invaluable for owners of these source systems. They will not provide more than half the story since they cannot provide target models and transform logic for those targets. Some ETL tools are now bundled, resold or simply preferred by certain ERP/CRM vendors. This does not rule out your use of other tools. Here are the primary issues we consider when ERP, CRM and other complex integrated applications are involved:

  • Percent of data to be sourced from within the application suite
    1. ERP and CRM applications typically represent only a fraction of the enterprise data generated by transactional systems.
    2. External data from suppliers, service bureaus and others is playing an increasingly important role in the overall mix of data sources.
  • Timing of the DW, before, during or after ERP/CRM implementation
    1. We find a "staggered parallel" approach most useful to support BI delivery concurrent with ERP/CRM adoption.
    2. May data warehouses are built subsequent to the application and must consider their overall scope as a result.
  • Use of ERP/CRM vendor DW "Solution" or non-vendor specific
    1. Some vendor products (SAP BW3, PeopleSoft EPM 8) are proving to be quite competent for ERP and non-ERP data. Most offerings are not so competent.
    2. The downstream impacts of a packaged solution, in terms of data models, BI tool support and meta data availability can reduce the value of vendor solutions.
  • The degree of customization employed by the ERP/CRM adoption
    1. Highly customized application suites bear little resemblance to their "generic" siblings. Data modeling complexity is greatly increased and so is ETL complexity.
    2. The use of the application suite as a reporting vehicle is often increased by customization; this can decrease the value of a data warehouse or operational data store.
  • The planed or existing use of an operation data store (ODS)
    1. The ODS can provide a daily operational reporting function and cushion the effects of customization of ERP/CRM systems.
    2. The ODS can also enterprise BI delivery if designed and executed improperly.
  • BI delivery requirements
    1. Some analysis of the known BI requirements is merited during the ETL selection process.
    2. The potential for multiple BI tools is very high and meta data exchange can prove very valuable.

ETL Selection Criteria: EAI, Real-Time Messaging and Other Magic

One other issue is currently driving the redesign of many ETL products and the selection of these tools by customers. The drive towards real- time application and data integration. Please consider our approach to these issues.

Integration and Where it Occurs

We believe that you can stratify your information environment into three basic layers: interface, application and data. The reason for this stratification is to force some discipline into the tool selection process. It also respects the basic tenets of the Zachman Framework for Enterprise Architecture and helps us identify the best places to focus on integration for a given set of requirements.

Interface-level integration is often conducted through Web-based interfaces that reach into operational systems and data. These interfaces obscure or abstract the complexity of connectivity and source system access from users. These interfaces typically do little or nothing to actually integrate the data the results from user interaction with underlying source systems.

Application integration occurs in many forms. ERP and other suites provide one means of integrating data from disparate modules. Enterprise application integration (EAI) offers another means of connecting disparate applications to one another. These methods are focused on real-time messaging or better means of connectivity. The operational imperative is the support of multiple, dispersed application logic dependencies. The front office sales management functions need to update the manufacturing and inventory functions in real time so customers and partners can draw on our just in time inventory model. This is the mantra of the "x degrees of separation" crowd.

Data integration and accumulation is the "deepest" level of integration. This is because it is not transitory or operational in nature. Data integration and accumulation requires consistent definitions, rules, processes and outcomes. It also requires a commitment to the long-term accumulation of mission-critical information. Data integration is actually the art and science of information construction. Data is integrated and enriched with contextual, or reference data points, to support decision making across time. That is the goal of business intelligence.

Considerations for the real-time imperative include:

  • Are we trying to integrate and accumulate application data?
  • Have we already built a standardized messaging system for this data?
  • Is this throw away data related to point-in-time status?
  • How soon must this be available for reports and queries?
  • Are we going to ensure consistency with long-term reporting and analysis?

A Word about Vendors

Don't be confused by vendor noise about the complexity of competitive products. My advice is to filter out vendor claims about competitors and focus only on claims about their own products. Force vendors to prove their claims in demonstrations and proofs. Don't accept off-site prepared demonstrations as proof of anything. Provide sample data and meta data and ignore vendors that will not respond. It is perfectly acceptable to allow the vendor to create an off-site proof using your meta data and sample data but the demonstration of that working proof should include customization of the work and some estimates as to the time it took to complete the proof. The first vendor that says, "we can do that" deserves a thorough evaluation. The first vendor that says, "you don't need that" deserves a dial tone, unless of course they can illustrate a better way to solve your business or technical problem.

Some vendors shun analyst and other external attention and prefer to remain mysterious. If they do not allow third parties to evaluate their product then they increase the burden for customers to engage in even more exhaustive and stringent evaluation processes. No problem, just take it out of their price tag. They may be great for your needs, but if they drag you through a process of analysis others don't require then make them pay for it - literally. Quantify the costs of providing your own research and comparison of products and then discount their best bid by that amount.

A Word about Independent Consultants

My grandfather taught me that "Advice is only worth what you pay for it, but you don't know its cost until you take it". There are many ways to provide consulting and most are generally valid and valuable. The only exceptions are provided by those who do not actually do the work, use the tools or stick around long enough to see the effects of their advice. I am especially concerned for the well being of clients who are told that their approach is "doomed to certain failure" if a certain product is or is not used. Many of these "independent consultants" represent vendors in public marketing events, just as many research firms advise vendors on a subscription while providing similar services to end user clients. There is nothing wrong with these arrangements when customers are aware of them. Find out what relationship your independent consultant has with vendors and why they are so intent on a single solution or failure point based upon product alone.

I don't pretend to be "independent" even though I don't take money (or free trips) from vendors. I am biased by my experience with vendors and their products. Vendors that answer my questions directly, respond to customers honestly about their products and fix their own mistakes have earned my respect and admiration. I don't deal with the others, but don't limit your list of prospective suppliers based on my experience, give them a chance to impress or depress you themselves. My experiences over the past ten years with clients in banking, finance, energy, telecommunications and other areas is the basis for this document, your experience is always more important than mine - Good Hunting!

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