SMART Integrators Shop The DATAMART.

There are literally billions of dollars in exploitable customer information locked up in the operational databases of companies throughout the U.S. This data represents billions or trillions of transactions that reveal customer preferences, buying history, and other information that the businesses involved could use to gain a competitive advantage by establishing a closer relationship with their customers.

Little wonder that the Business Intelligence/Data Warehouse (BI/DW) market, which comprises the technologies used to extract and analyze information from operational databases, is booming. The Database Solutions III (DBS III) study recently completed by the BI/DW Program at Survey.com (Mountain View, CA) leads us to forecast total revenue for this market of $148 billion by 2003.

To most people in the IT industry, the phrase "data warehouse" suggests massive projects, cost overruns, delays, and a high potential for failure. Certainly, the attempt to gather every bit of data in an enterprise into one massive data warehouse is likely to tax to the utmost even the most accomplished IT department. That's why the more usual approach is increasingly what's called a datamart: basically a small data warehouse that supports one department or aspect of a business. Using an approach called a federated data warehouse, these datamarts can later be combined into a data warehouse.

There lies the opportunity for integrators of every size. Not only is a datamart fundamentally easier to implement, but it can also be built using very inexpensive hardware and software: Intel-based computers running Microsoft Windows NT (and soon, Windows 2000) and Microsoft SQL Server 7.0, which includes almost all of the specialized functionality needed to construct a datamart. Indeed, the DBS III study revealed statistics about Microsoft mind-share. This is not surprising, given the extensive data warehousing capabilities offered by Microsoft, as described briefly below. The only remaining obstacle lies in the specialized nature of the knowledge needed to construct a datamart: many integrators who are otherwise knowledgeable about databases may regard the additional expertise not worth acquiring, even given the availability of a low-cost Wintel platform. Yet a new company, Appsco Software Ltd. (Guildford, Surrey, UK; U.S. office Bellevue, WA; www.appsco.com) offers an almost push-button approach to datam art construction, a rapid application development tool called AppsMart, that brings this technology within the reach of almost any integrator with SQL Server database expertise. To illustrate the potential for new business that this tool provides, I'll take a brief look at how a datamart is constructed and then discuss how AppsMart short-circuits much of the process, enabling integrators to bring the benefits of BI/DW to businesses of virtually any size.

Making Sense Of Data

Most businesses run their day-to-day operations using some form of On-Line Transaction Processing (OLTP). For instance, every time you go through the checkout line at a supermarket or use your bank's ATM, you are interacting with an OLTP system. Data warehouse technology evolved because these systems, whose underlying datastore is called an operational database, are not designed to support analysis of the data they store. Their architecture is designed to support the day-to-day operations of the business. So it is practically impossible to query such a database for analytic purposes (e.g., how many customers in Store A bought the detergent promotion last week) without extensive custom programming. Even then, the operation is difficult, slow, and interferes with the real-time processing that is the raison d'etre of the system.

A data warehouse or datamart solves this problem by extracting the data from the operational database, cleaning it up adding reference data (called metadata) that allows analysis along various dimensions (such as time) that are only implicit in the operational store and makes sense of the data in a larger business context, and then presenting it, using a variety of analytical tools from a datastore that is separate from the OLTP system. Some of the transformations that might be applied are date and time format conversions, data type conversions (i.e., character to integer), and the like, as well as more complex changes. The latter may include changes such as those that are necessary to make data from a financial application that tracks data by quarter and fiscal year, match that from a sales applications, which tracks data by month and calendar year.

The technologies for extracting, cleaning, adding metadata, and then putting the transformed data into the data warehouse or datamart is often called Extraction Transformation and Loading (ETL) technologies, or, as we prefer to call them, Data-Enabling Technologies (DET). In Microsoft SQL Server, this functionality is supplied by Data Transformation Services (DTS). While not as extensive as some of the third-party DET solutions out there, DTS are free compared to a cost for third party tools that can range from $100,000 to upwards of $300,000!

One of the most common of these analytical tools is On-Line Analytic Processing (OLAP), which presents the data in the form of a multidimensional cube (an OLAP cube) instead of a vast table of discrete entries representing each transaction. Thus, rather than a huge agglomeration of entries consisting of customer name, customer address, product bought, time bought, shipping address, etc., an OLAP cube presents a set of dimensions (e.g., customer, product, region, and time) that enable the data to be "sliced, diced, and drilled through" along any arbitrary division. This makes it possible for Line Of Business (LOB) managers to ask questions, like "What percentage of customers who used the Memorial Day coupon for steak in Region 2 also bought barbecue charcoal?"

Again, Microsoft offers this functionality as part of SQL Server 7.0: the Microsoft OLAP Server, formerly code-named Plato, which also works with a number of third-party databases, as well. The front end to OLAP Services can be a third party OLAP tool or it can be Excel, using a feature called Pivot Tables. The benefit of this approach, especially important to integrators for small and midsize companies, is that most managers likely already have Excel on their desktop--there's no new application to learn.

Microsoft has also created the Microsoft Repository, a database that stores descriptive information about data warehouse information such as database schema, data transformations, and On-Line Analytical Processing (OLAP), which permits many datamarts to be integrated over time into a federated data warehouse, as well as extensive integration of third-party solutions and many other data warehouse services and features.

The Art Of The Datamart

Yet even given the extensive data warehousing framework supplied by Microsoft and its low cost, another obstacle remains to integrator exploitation of this technology for small and midsize companies (or departmentally in large companies). That is the expertise needed to design and implement a data warehouse or datamart. The data in the datamart has to be split into two categories: factual information and reference information. Factual information is basically the individual transactions or events handled by the OLTP system. The reference information is data that is used to analyze the factual data and it is organized into dimensions to permit OLAP queries and the like. So, for instance, a piece of factual data might be a store transaction such as in the example above while the reference information might be a product hierarchy, customer, time periods often used for analysis, location of various business entities (warehouse, store, distribution center, etc.), and the like.

Factual data doesn't change: a transaction is a historical event. Reference data can and often does change as LOB managers and others demand new ways of looking at the data in response to competitive challenges. For this reason, and others, the data in a data warehouse is usually represented in a star schema with the factual information at the center of the star, surrounded by the various types of reference data. Fig 1 illustrates a typical star schema as presented by the AppsMart user interface.

Although there are many decisions involved in data warehouse or datamart implementation, the design of the star schema is perhaps the most critical. The wrong decisions can render a datamart useless, unable to deliver the information managers need and unable to adapt to change. Designing the right schema requires a domain expert, someone who knows the business and can describe the essential processes and entities involved, and come up with what is called a business information model that matches the requirements of the managers who will use the datamart. This domain expert not only has to know the business, but also he or she must understand datamart design--and this expertise is neither common nor inexpensive.

That expertise bottleneck is precisely what AppsMart addresses. AppsMart makes it possible for a domain expert to design a template that describes the star schema for a class of business--something as broad as a "value chain" (resellers of finished products). This template can be applied to a specific business by a consultant who does not possess the high-level knowledge of the domain expert, yet has an understanding of business processes and how people need to analyze them. This expertise is far more common and less expensive. That is why I call AppsMart the "datamarts for the masses" application.

As delivered, AppsMart is actually available in two versions. The AppsMart Domain Expert Bundle (US $15,000) enables a domain expert to create a template for a given class of business; the End-User Bundle (a confusing name I hope they will change) (US $8,000) enables a consultant to apply existing templates to a specific customer's needs. Both versions come with a variety of pre-built templates for a broad range of businesses that can be adapted to specific industries and specific customer needs.

Fig 1 shows a star schema for a campaign effectiveness application, part of the value chain template supplied with the application. At the center is the factual data: consumers, responses, cost of campaign, and the time the campaign happened. Surrounding it are the various types of reference data by which the campaign can be analyzed: product, marketing channel, marketing campaign, demographics, and so forth. This star schema permits a manager to ask questions like: what was the cost per response for this campaign among women age 18-24 with household incomes between $30,000-$40,000 in Region 2?

Using the AppsMart Scratch Pad application, the consultant can modify the template created by a domain expert to match specific needs. This process includes modeling any additional business processes specific to the customer, defining new measures and dimensions, and matching sample data to the customer's organization so that AppsMart can populate the datamart with appropriate test data. At this point, a click of the mouse delivers a detailed specifications document for customer review and signoff (or for further modification). From this point, another click of the mouse begins the construction of the datamart. (Fig 2)

AppsMart generates the DTS packages needed to bring operational data into the datamart, the star schema along with sample data for review, the OLAP cubes needed to analyze the data, and the Excel Pivot Tables as front ends to OLAP Services. At this point, the customer can take the datamart for a test drive. If something is missing, it's a simple, iterative process to define new business processes, measures, and dimensions and then regenerate the datamart with the new features.

Reportedly, the World Wrestling Federation developed and deployed a datamart encompassing 3 years of WWF home video sales data, 12 different views of sales order information, 40 standard reports, and the ability to run special queries and create custom reports--all in less than three weeks. Similar results were reported by Prandium, the operator of El Torito, ChiChi's, Koo Koo Roo, and Hamburger Hamlet.

While AppsMart is not a panacea, still requiring some expertise above and beyond the usual database design expertise that many integrators in this field already possess, it so greatly eases and accelerates the design and implementation of a datamart that it should be seriously considered by any integrator who wants to cash in on this burgeoning market. To help integrators exploit this powerful tool, Appsco offers raining courses and is partnering with companies such as Arthur Andersen and others who will be able to create templates for vertical business applications that can be applied by integrators at all levels.