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.
