Also, the default join operation (called an inner join) eliminates rows that do not match, which means that customers may inadvertently be left out of a data pull. The set of operations in SQL is not particularly rich, especially for text fields and dates. The result is that every database vendor extends standard SQL to include slightly different sets of functionality.

Database schema can also illuminate unusual findings in the data. For instance, we once worked with a file of call detail records in the United States that had city and state fields for the destination of every call. The file contained over two hundred state codes-that is a lot of states. What was happening? We learned that the city and state fields were never used by operational systems, so their contents were automatically suspicious-data that is not used is not likely to be correct. Instead of the city and state, all location information was derived from zip codes. These redundant fields were inaccurate because the state field was written first and the city field, with 14 characters, was written second. Longer city names overwrote the state field next to it. So, WEST PALM BEACH, FL ended up putting the H in the state field, becoming WEST PALM BEAC, HL, and COLORADO SPRINGS, CO became COLORADO SPRIN, GS. Understanding the data layout helped us figure out this interesting but admittedly uncommon problem.

Metadata

Metadata goes beyond the database schema to let business users know what types of information are stored in the database. This is, in essence, documentation about the system, including information such as:

The values legally allowed in each field

A description of the contents of each field (for instance, is the start date the date of the sale or the date of activation)

The date when the data was loaded

An indication of how recently the data has been updated (when after the billing cycle does the billing data land in this system?)

Mappings to other systems (the status code in table A is the status code field in table B in such-and-such source system)

When available, metadata provides an invaluable service. When not available, this type of information needs to be gleaned, usually from friendly database administrators and analysts-a perhaps inefficient use of everyones time. For a data warehouse, metadata provides discipline, since changes to the

warehouse must be reflected in the metadata to be communicated to users. Overall, a good metadata system helps ensure the success of a data warehouse by making users more aware of and comfortable with the contents. For data miners, metadata provides valuable assistance in tracking down and understanding data.

Business Rules

The highest level of abstraction is business rules. These describe why relationships exist and how they are applied. Some business rules are easy to capture, because they represent the history of the business-what marketing campaigns took place when, what products were available when, and so on. Other types of rules are more difficult to capture and often lie buried deep inside code fragments and old memos. No one may remember why the fraud detection system ignores claims under $500. Presumably there was a good business reason, but the reason, the business rule, is often lost once the rule is embedded in computer code.

Business rules have a close relationship to data mining. Some data mining techniques, such as market basket analysis and decision trees, produce explicit rules. Often, these rules may already be known. For instance, learning that conference calling is sold with call waiting may not be interesting, since this feature is only sold as part of a bundle. Or a direct mail model response model that ends up targeting only wealthy areas may reflect the fact that the historical data used to build the model was biased, because the model set only had responders in these areas.

Discovering business rules in the data is both a success and a failure. Finding these rules is a successful application of sophisticated algorithms. However, in data mining, we want actionable patterns and such patterns are not actionable.

A General Architecture for Data Warehousing

The multitiered approach to data warehousing recognizes that data needs come in many different forms. It provides a comprehensive system for managing data for decision support. The major components of this architecture (see Figure 15.3) are:

Source systems are where the data comes from.

Extraction, transformation, and load (ETL) move data between different data stores.

The central repository is the main store for the data warehouse.

The metadata repository describes what is available and where.

Data marts provide fast, specialized access for end users and applications.

Operational feedback integrates decision support back into the operational systems.

End users are the reason for developing the warehouse in the first place.

Departmental data w and metadata suppoi applications used by

The central data store is a relational database with a logical data model.

Extraction, transformation, and load tools move data between systems.

Operational systems are where the data comes from. These are usually mainframe or midrange systems.

Some data may be provided by external vendors.

Figure 15.3 The multitiered approach to data warehousing includes a central repository, data marts, end-user tools, and tools that connect all these pieces together.