TDAN: The Data Administration Newsletter, Since 1997

THE DATA ADMINISTRATION NEWSLETTER – TDAN.com
ROBERT S. SEINER – PUBLISHER

Subscribe to TDAN

TDWI
Dataversity
Data Governance Winter
DGI Conference
Master Data Management

   > home
 Printer-friendly
 E-mail to friend

Data Model Resource Book, revised volume 1 & 2

Reviewed by Van Scott
Published: January 1, 2001

Author: Len Silverston
Publisher: John Wiley & Sons, 2001
Volume 1 - ISBN 0471380237
Volume 2 - ISBN 0471353485

Order Volume 1 through Amazon.com Today!
Order Volume 2 through Amazon.com Today!

Introduction

The notion of enterprise and data architecture has a lot more currency these days than in the past. Over the last several years resources for enterprise and data architecture have increased considerably, including conferences, web sites, seminars, and books. The Data Model Resource Book (Revised Edition, Volumes 1 and 2) is a major resource for individuals interested in an enterprise data architecture or data warehousing. The books provide a library of logical data models and sample data warehouse and data mart designs.

Volume 1 presents subject area models that apply to almost any industry. Volume 2 presents models for eight industries such as manufacturing, financial services, and health care. The books highlight the importance of subject-area modeling and show the relationship among high-level models, logical data models, warehouse designs, and data mart designs. They also provide a framework for the various levels of model and a methodology to transform logical data models into physical models and data designs.

The Subject Areas

The main part of Volume 1 presents eight logical data models that are common to most industries:

  • People and Organization
  • Product
  • Order
  • Shipment
  • Work Effort
  • Invoice
  • Accounting and Budgeting
  • Human Resources

The chapter on each subject area describes key features of the subject area, entities, attributes, and the subject area’s relationship to other subject areas. Narrative describes the subject area and business rules that apply. Instance tables (sample data) help bring the models to life.

The Industries

The main part of Volume 2 presents industry-specific models. Industries include:

  • Manufacturing
  • Telecommunications
  • Health Care
  • Travel
  • Insurance
  • Financial Services
  • Professional Services
  • Web-based Transactions

The common models in Volume 1 are fairly general. Volume 2 shows you how to customize these subject-area models for the specific industries, e.g., by adding sub types.

The Star Schemas

The books don’t merely provide logical data models. Volume 1 contains a number of star schemas, including Sales Analysis, Human Resources, Inventory, Purchase Order, as well as a data warehouse design. Volume 2 contains star schemas for Insurance Claims, Accounts, Account Transactions, Web Server Hits, Server Visits, and many more.

The Framework

The Resource Book contains one of the best frameworks I have seen for a data architecture. The framework shows the relationship among high-level models, mid-level models, and physical designs. Figure 1 shows major subject areas, while Figure 2 shows the relationship among the subject areas, mid-level models, and physical models.


Figure 1. High-Level Model

 


Figure 2. Relationship among models and designs

The Transformations

In order to insure that models are aligned, any multi-layered data architecture must be able to transform its models from one layer to the next. The Resource Book gives several examples of transforming models from one level to another. One section provides standard techniques for transforming a logical model to a physical model, e.g., by merging or splitting tables. A more interesting section shows how to transform logical models into data warehouse designs. Key transformations include ...

  • Removing purely operational data
  • Adding an element of time
  • Merging data from multiple tables into one table
  • Adding derived data
  • Creating data artifacts

Finally, the book also gives tips and techniques for making the general common models more industry specific, e.g., by adding appropriate subtypes.

Sample Enterprise Data Warehouse Model

Chapter Ten in Volume 1 applies the data warehouse transformation rules just mentioned to derive a sample data warehouse data model. Purely operational data (such as comments) are removed. Invoice and invoice detail tables are merged. A “load_date” column is added to several tables. These transformations result in three subject areas appropriate for business analysis: sales, budgeting and purchasing, and human resources. The enterprise data warehouse design is suitable for decision support or for a repository, whose data will eventually be distributed to a departmental data mart.

Conclusion

The Resource Book is the best book I’ve seen on data architecture. It does not merely address the top levels of a data architecture (Zachman Framework row one or two); it provides both common and industry-specific logical models as well as data designs that may be customized to meet your requirements. The end result is a is a rich framework whose models span the higher and lower levels of a data architecture, including high-level models, logical models, warehouse designs, star schemas, and SQL scripts. You can use the data models, designs, and scripts as templates or starting points for your own modeling, an introduction to subject areas you might not be familiar with, a reference to validate your existing models, and a help to building an enterprise data architecture. The book provides techniques to transform models from one level to another, as well as tips and techniques for getting the appropriate levels of abstraction in the models. Instance tables (sample data) help bring the models to life. I have customized and used the models on many projects in the last two years. The Resource Book is an invaluable resource to me.

The Data Model Resource Book is published by John Wiley & Sons. A CD-ROM of SQL scripts to create the models via a CASE tool is also available.

Go to Current Issue | Go to Issue Archive


Recent articles by Van Scott

Van Scott -

Van Scott is a creative enterprise architect with a very strong methodological bent. He is intrigued by the enterprise, enterprise architectures, and data warehousing.  Very project-focused.  Known for taking notes, listening, and reading. Published author in music, psychology, and data management.  Practicing musician and composer.  Principal consultant at Sonata Consulting, Inc.  You can reach Van at scottv1@airmail.net or 940.566.3503. Current project is building a large call detail data warehouse for a firm in the telecommunications industry.