Universal meta data models

David Marco

Michael Jennings

Universal Meta Data

Models

01 081779 FM.qxd 2/27/04 10:01 AM Page iii

01 081779 FM.qxd 2/27/04 10:01 AM Page ii

Universal Meta Data

Models

01 081779 FM.qxd 2/27/04 10:01 AM Page i

01 081779 FM.qxd 2/27/04 10:01 AM Page ii

David Marco

Michael Jennings

Universal Meta Data

Models

01 081779 FM.qxd 2/27/04 10:01 AM Page iii

Vice President and Executive Group Publisher: Richard Swadley

Vice President and Executive Publisher: Bob Ipsen

Vice President and Publisher: Joseph B. Wikert

Executive Editorial Director: Mary Bednarek

Executive Editor: Robert M. Elliott

Editorial Manager: Kathryn A. Malm

Senior Production Manager: Fred Bernardi

Senior Development Editor: Emilie Herman

Production Editor: Felicia Robinson

Media Development Specialist: Travis Silvers

Permissions Editor: Laura Moss

Text Design & Composition: Wiley Composition Services

Copyright  2004 by David Marco and Michael Jennings. All rights reserved.

Published by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form

or by any means, electronic, mechanical, photocopying, recording, scanning, or otherwise, except as

permitted under Section 107 or 108 of the 1976 United States Copyright Act, without either the prior

written permission of the Publisher, or authorization through payment of the appropriate per-copy fee

to the Copyright Clearance Center, Inc., 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax

(978) 646-8600. Requests to the Publisher for permission should be addressed to the Legal Department,

Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317)

572-4447, E-mail: [email protected].

Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or

warranties with respect to the accuracy or completeness of the contents of this work and specifically

disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No

warranty may be created or extended by sales or promotional materials. The advice and strategies con￾tained herein may not be suitable for every situation. This work is sold with the understanding that the

publisher is not engaged in rendering legal, accounting, or other professional services. If professional

assistance is required, the services of a competent professional person should be sought. Neither the

publisher not the author shall be liable for damages arising herefrom. The fact that an organization or

Website is referred to in this work as a citation and/or a potential source of further information does

not mean that the author or the publisher endorses the information the organization or Website may

provide or recommendations it may make. Further, readers should be aware that Internet Websites

listed in this work may have changed or disappeared between when this work was written and when

it is read.

For general information on our other products and services please contact our Customer Care Depart￾ment within the United States at (800) 762-2974, outside the United States at (317) 572-3993 or fax (317)

572-4002.

Trademarks: Wiley and the Wiley Publishing logo are trademarks or registered trademarks of John

Wiley & Sons, Inc. and/or its affiliates. All other trademarks are the property of their respective own￾ers. Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may

not be available in electronic books.

Library of Congress Cataloging-in-Publication Data: Available from Publisher.

ISBN: 0-471-08177-9

Printed in the United States of America

10 9 8 7 6 5 4 3 2 1

01 081779 FM.qxd 2/27/04 10:01 AM Page iv

I thank Melinda for being the best partner a man could ask for and

I thank God for blessing me in every way a person can be.

David Marco

With determination and focus, you can achieve anything; with

uncertainty and doubt, you will never begin.

Michael Jennings

01 081779 FM.qxd 2/27/04 10:01 AM Page v

01 081779 FM.qxd 2/27/04 10:01 AM Page vi

Acknowledgments xiii

About the Authors xv

Introduction xix

Part One Presenting the Managed Meta Data Environment 1

Chapter 1 Overview of the Managed Meta Data Environment 3

Evolution of the Managed Meta Data Environment 3

Typical Managed Meta Data Environment Objectives 5

Increase Revenue and/or Decrease Costs 5

Promote Public Health 7

Adhere to Regulations 8

Improve National Security 8

Provide Education 9

Ensure Public Safety 9

Facing Corporate Challenges with an MME 9

Provide IT Portfolio Management 11

Reduce IT Redundancy 13

Redundant Applications, Processes, and Data 15

Needless IT Rework 16

Redundant Hardware and Software 16

Prevent IT Applications Failure 17

Reduce IT Expenditures 18

Enable Knowledge Management 18

Adhere to Regulatory Requirements 18

Enable Enterprise Applications 19

Summary 22

Contents

vii

01 081779 FM.qxd 2/27/04 10:01 AM Page vii

Chapter 2 The Managed Meta Data Environment Architecture 23

MME Overview 24

Meta Data Sourcing Layer 25

Software Tools 29

End Users 30

Documents and Spreadsheets 31

Messaging and Transactions 33

Applications 34

Web Sites and E-Commerce 34

Third Parties 34

Meta Data Integration Layer 35

Meta Data Repository 36

Meta Data Management Layer 37

Archiving 38

Backup 38

Database Modifications 39

Database Tuning 39

Environment Management 39

Job Scheduling 40

Maintanence of Load Statistics 40

Purging 40

Maintenance of Query Statistics 40

Query and Report Generation 41

Recovery 41

Security Processes 41

Source Mapping and Movement 42

User Interface Management 42

Versioning 42

Meta Data Marts 43

Meta Data Delivery Layer 44

Applications 44

Data Warehouses and Data Marts 45

End Users 46

Messaging and Transactions 46

Meta Data Marts 47

Software Tools 47

Third Parties 48

Web Sites and E-Commerce 48

MME Data Stewardship 49

Understanding Data Stewardship 49

Types of Data Stewards 49

Executive Sponsor 50

Chief Steward 50

Business Steward 51

Technical Steward 51

Preparing for Data Stewardship 51

Form a Charter 52

Define and Prioritize Committee Activities 52

Create Committee Rules of Order 53

viii Contents

01 081779 FM.qxd 2/27/04 10:01 AM Page viii

Establish Roles for Committee Members 53

Design Standard Documents and Forms 53

Data Stewardship Activities 54

Define Data Domain Values 55

Establish Data Quality Rules, Validate Them, and

Resolve Them 55

Set Up Business Rules and Security Requirements 56

Create Business Meta Data Definitions 56

Create Technical Meta Data Definitions 57

Capability Maturity Model and the MME 57

What Is the Capability Maturity Model? 58

Why Is the SE-CMM Valuable? 59

Purpose of SE-CMM 59

SE-CMM Levels 60

Level 0: Not Performed 60

Level 1: Performed Informally 60

Level 2: Planned and Tracked 60

Level 3: Well-Defined 61

Level 4: Qualitatively Controlled 61

Level 5: Continuously Improving 61

Applying SE-CMM to Data Warehousing 61

Level 0: Not Performed 61

Level 1: Performed Informally 62

Level 2: Planned and Tracked 63

Level 3: Well-Defined 64

Level 4: Qualitatively Controlled 65

Level 5: Continuously Improving 65

MME and the Common Warehouse Metamodel 67

CWM Solution for Interchange Requirements 68

CWM Technology and Approach 69

Layered Architecture 69

Supporting Technology 70

The Future of the CWM 71

Summary 71

Chapter 3 Managed Meta Data Environment Applications 73

MME Application Examples 73

Banking and Finance Industry Example 74

Application Scenario 75

Healthcare Insurance Industry Example 79

Application Scenario 79

Manufacturing Industry Example 79

Application Scenario 81

National Defense Organization Industry Example 81

Application Scenario 81

Pharmaceutical Industry Example 85

Application Scenario 85

Retail Industry Example 85

Application Scenario 87

Telecommunications Industry Example 87

Application Scenario 88

Contents ix

01 081779 FM.qxd 2/27/04 10:01 AM Page ix

Case Studies: Two World-Class MME Initiatives 90

Allstate 90

Company Background 90

Challenges and Opportunities 91

MME Solution Overview 92

Data Stewardship 93

MME Meta Data Sourcing Layer 94

MME Meta Data Delivery Layer 96

Allstate’s Future MME Directions 98

MME Challenges 99

MME ROI 99

RBC Financial Group 100

Company Background 100

Challenges and Opportunities 101

MME Solution Overview 101

MME Meta Data Sourcing Layer 102

MME Meta Data Delivery Layer 105

MME Challenges 106

MME ROI 106

Summary 108

Part Two Universal Meta Models for the Meta Data

Repository Environment 109

Chapter 4 Universal Meta Model for Enterprise Systems 113

Purpose of the Enterprise Systems Meta Model 113

Enterprise Systems Model Assumptions 114

Enterprise Systems Subject Areas 115

Data Package 117

Data Relationships to Structure 128

Data Movement and Transformation 138

Expression Transformation 146

Transformation Operational History 156

Data Profiling 161

Reports from the Enterprise Systems Meta Model 166

Data Definition by System by Environment Report 166

Data Structure Hierarchy Report 166

System Data Quality Assessment Report 169

Data Profiling Exceptions Report 169

Data Impact Analysis Report 172

Data Lineage Analysis Report 172

Summary 175

Chapter 5 Universal Meta Model for XML, Messaging, and

Business Transactions 177

Purpose of the XML Meta Models 178

Model Assumptions 179

Introduction to XML 179

XML Schema Example 180

XML Categories 182

x Contents

01 081779 FM.qxd 2/27/04 10:01 AM Page x

Data-Centric XML: Data Structure vs. Process 183

XML Standards 184

XML, Messaging, and Business Transactions Subject Areas 189

XML Schemas and DTDs 189

XML Schema Structure 190

DTD Structure 193

XSLT: XML Transformation Language (XSLT) 208

Business Transactions 211

Classification Scheme 228

Reports from the XML, Messaging, and

Business Transactions Meta Model 231

XML Use Report: XML Top-Level Elements 232

XML Use Report: Schema Imports 233

XML Use Report: XML Process Send 235

XML Use Report: XML Process Impact 235

Process XML: Order Flow Views 238

Summary 242

Chapter 6 Universal Meta Model for IT Portfolio Management 243

Purpose of the IT Portfolio Management Meta Model 244

Assumptions in the IT Portfolio Management Meta Model 244

IT Portfolio Management Subject Areas 245

Service Management 247

Software Management 270

Hardware and Network Management 282

Project Portfolio Management 290

Data Quality Management 304

Reports from the IT Portfolio Management Meta Model 313

Software Module CRUD Report 313

Hardware Obsolescent Report 315

Data Storage Capacity Report 315

Installed Software Patch Report 316

Annual Vendor Software Fee Liability Report 317

System Trouble Report Volume Report 319

Unscheduled Maintenance by Component Report 319

IT Project Resource Projection Report 320

Project Success Rate Analysis Report 321

Data Quality Exception Summary Report 322

Summary 323

Chapter 7 Universal Meta Model for Business Rules, Business

Meta Data, and Data Stewardship 325

Purpose of the Business Rules, Business Meta Data,

and Data Stewardship Meta Model 326

Assumptions in the Business Rules, Business Meta Data,

and Data Stewardship Models 327

Business Rules, Business Meta Data, Data Stewardship

Subject Areas 327

Business Rules 328

Business Meta Data 343

Data Stewardship 351

Contents xi

01 081779 FM.qxd 2/27/04 10:01 AM Page xi

Reports from the Business Rules, Business Meta Data,

and Data Stewardship Meta Models 355

All Business Rules Report 355

Business Rules by Business Rule Category Report 357

Data Warehouse Customer Business Rules 359

Data Groups by Subject Area 359

Open and Pending Business Rule Issues 362

Person by Organization and Manager 364

Summary 366

Chapter 8 The Complete Universal Meta Model 367

A Complete High-Level Meta Model 368

Implementation Considerations 373

Levels of Data Models 374

Database Unique IDs 374

Code Tables 375

Translating LDM to PDM 376

Many-to-many Relationships 376

Entity Subtyping 377

Meta-Level Information Loss 378

Multiple Inheritance 380

Design Issues Revisited 381

Data Quality Meta Data Mart 382

OLAP Implementation of the Meta Data Mart 384

Summary 391

Conclusions 391

Appendix A Model Diagrams 393

Appendix B What’s on the CD-ROM? 411

System Requirements 411

Using the CD with Windows 412

What’s on the CD 412

Author-created Materials 412

Applications 414

Troubleshooting 415

Appendix C Glossary 417

Appendix D Recommended Reading 437

Index 439

End-User License Agreement 457

xii Contents

01 081779 FM.qxd 2/27/04 10:01 AM Page xii