Approaches to coordinating data marts

Users are generally on their own when it comes to building the meta data foundation for data marts due to the lack of centralized repositories or standards for meta data content/formats. Here are a few options for building enterprise data marts.

QUICK AND DIRTY DATA MART. Planned at the business unit or workgroup level, this type of data mart is usually defined by specific reporting requirements. Vendors such as Cognos Inc., Burlington, Mass., actively promote data mart strategies that focus on getting the desired results.

Generally, these strategies dispense with the building of meta data repositories in favor of quick results. Instead, the best way to streamline the building of future data marts is to carefully define business objects up front. This helps organizations avoid reinventing the wheel each time a new mart is rolled out, and ensures that succeeding marts use consistent data. Lacking meta data repositories, designers must copy all of the relevant data extraction routines and keep them in-sync.

THE SINGLE-VENDOR DATA MART. Often dubbed the "data mart in a box," single-vendor approaches usually consist of a collection of tools that interface with each other. For instance, SAS Institute, Cary, N.C., has broadened its back-end and front-end tools, which had been used for populating SAS statistical data sets from mainframe or legacy sources. In this case, SAS has adapted its extraction tools to also feed relational databases, and it has bolted on a query and reporting environment. Information Builders Inc., New York City, has adopted a similar approach. Its SmartMart offering bundles the EDA/SQL family of middleware with the Focus reporting environment, the Fusion online analytical processing (OLAP) database and a series of tools to measure data mart usage. And Sagent, Palo Alto, Calif., is developing a Windows NT-based solution that combines back-end data extraction with front-end relational OLAP-like data views. In turn, Oracle Corp., Redwood Shores, Calif., has bundled several tools into a data mart suite, including data modeling for star schema capabilities from its Designer/2000 development tools, complete with database, along with a Sales Analyzer turnkey application.

These all-in-one tool collections offer well-integrated functionality that enables users to rapidly build standalone data marts while minimizing systems integration. However, some may lack repository capabilities, limiting their effectiveness for building "enterprise data marts."

PACKAGED ERP DATA MART. Because of SAP R/3's commanding position in the enterprise resource planning (ERP) market, a number of back-end middleware tool vendors, such as D2K, San Jose, Calif., and Information Builders have developed interfaces that read R/3 proprietary data tables. Others, such as Hewlett-Packard's Professional Services Organization, are developing packaged approaches to building subject-specific R/3 data marts that include middleware and the Cognos Impromptu managed query environment. So far, Hewlett-Packard has developed two generic marts covering sales analysis and customer profitability. Essbase, the OLAP multidimensional database from Arbor Software Corp., Sunnyvale, Calif., is already bundled as an option with PeopleSoft.

BEST OF BREED. Data warehouses and data marts require a variety of tools ranging from data extraction to database management, data warehouse management, OLAP and managed query environments. Most tool vendors therefore publish APIs to enable integration with other tools or to provide interfaces to import relevant meta data.

For instance, the Andyne query tool from Andyne Computing Ltd., Kingston, Ontario, can import not only relational database columns or table names, but higher level descriptions of business objects that might be maintained in a flat file or relational database. It also has APIs that read meta data files from back-end tools from Informatica, Menlo Park, Calif., and Prism Solutions Inc., Sunnyvale, Calif. Some vendors, such as Arbor Software go a step further and certify third-party tools with compatible interfaces.

Whether these APIs extend to meta data depends on the vendor; some tools store and manage meta data while others do not. Most back-end data extraction and transformation tool suppliers, such as Apertus/Carleton, D2K, Evolutionary Technologies, Informatica, Prism and Sagent, store their own meta data either through proprietary repositories or use of commercial relational or object-oriented databases. However, the same cannot necessarily be said of front-end tool vendors. For instance, Arbor has only recently added a meta data API for Essbase 5.

About the Author

Tony Baer is principal with onStrategies, a New York-based consulting firm, and editor of Computer Finance, a monthly journal on IT economics. He can be reached via e-mail at [email protected].