Originally appeared in MSI Magazine
October 1, 1998

Some Assembly Required

You’re breaking out the champaign. Somehow, your organization just weathered a massive ERP reengineering project. Order entry, general ledger, manufacturing, and distribution transactions are now managed under a single integrated, client/server umbrella. Presto! You can finally run your business the way you were supposed to. All the data is in sync, while month-end closings are no longer traumatic.

So what do you do with all this newly synchronized, consistent data that can now be accessed via documented APIs rather than homegrown, maintenance-intensive batch extraction programs? The answer that's becoming increasingly obvious is to build decision support systems allowing your organization to answer questions like which customers are the most profitable or which processes have the fewest defects.

But, as we all know, ERP and other transactional systems are ill-suited for analytical work. It’s time consuming to write all those ABAP IV routines to add new reporting capabilities to R/3, even assuming your organization can locate or afford an ABAP programmer.

Not surprisingly, all this has provided a major opening for data warehousing—a market which Dataquest estimates is growing at a rough 30% annual clip.

However, just building a data warehouse—any warehouse—does not necessarily guarantee success.

Step one—identifying user needs—can be a daunting challenge. Compared to transaction systems, which promise to automate or consolidate known activities, data warehouse projects provide decision support capabilities that are often totally new to users. As a data warehouse project leader from one of the nation’s largest banks bemoaned, users either ask for on-line versions of the same Monday morning reports that they’ve been receiving for years, or they demand a laundry list because they are afraid if they don't ask now, they’ll never get the features they might need later.

Step two is dealing with best-of-breed integration. Although some vendors claim to offer “data marts in box,” the reality is that data warehouses and marts usually require a mix of databases, middleware, and front end development tools or point applications, often from multiple vendors.

Yet, all of these hurdles may be blessings in disguise. The trial by ordeal nature of building data warehouses and marts theoretically separates the dedicated from the dilettantes. Maintaining the warehouse should theoretically be a cakewalk by comparison. But, as we’ve mentioned in past columns, often there is little to prepare you for success.

Usage patterns are difficult to predict, because, unlike transaction systems, which have known patterns and volume levels, data warehouses and marts are used strictly on a voluntary basis. Users rely on them if they are useful, otherwise the system may log only sporadic hits. Installing database logs and network sniffers may be critical for protecting your investment.

Then, as we’ve previously noted, is the question of data volume. How much data do users really need to detect trends and predict the future? While transaction systems are usually purged of data over three or six months old, the amount of data—even if summarized—can snowball as users ask for historical data back to creation. Better plan ahead. For instance, the operational data store of a major chemical manufacturer, which receives transaction data from SAP and legacy systems for feeding to about two dozen data marts, is being designed to hold from three to ten years of transaction history.

Upcoming vendor product developments threaten to make data warehouse design and implementation appear deceptively simple. With SQL Server 7.0, which is expected to be released next year, Microsoft is bundling “Plato,” its code name for OLAP extensions at no extra cost. Although Plato delivers only the data structures and a rudimentary Excel spreadsheet front end, OLAP tools players such as Arbor (now Hyperion Solutions, after its recent merger) are recasting themselves as OLAP applications companies.

In the ERP space, data mart vendors such as Cognos and Information Builders are offering specialized data extract capabilities tuned for R/3, while SAP itself is offering bolt-on “Business Warehouse” marts which, as SAP’s web site claims, offers “an end to end solution with no hidden extras.”

The end result is that the “data mart in a box” is becoming just as real as “shrink-wrapped” ERP. OLAP tools may be maturing into applications, but as the timing becomes more auspicious to build that first or second data mart, recall a valued lesson from your ERP days: Assembly is still required.