A Metadata Architecture

1
A Metadata Architecture For Enterprise-Wide Data
Sharing
2
(No Transcript)
3
DoD TARGET DATA SHARING ENVIRONMENT A Logistics
Perspective
Legend
Essential AIS
ATAC
MRP II
Contributing AIS
USMC
USN
USAF
COALITION
USA
MILSEALIFT
GSA
USCG
DoD Joint Applications GCCS GCSS (IDEs)
Commercial
DLA
JMAR
TRANSCOM GTN
DARPA
MTMC
AMS
GATES
CMOS
UNCLAS CENTCOM (SOUTHCOM SOCOM) Server
TCACCIS
UNCLAS PENTAGON
UNCLAS PACOM Server
UNCLAS USFK JTAV Server
UNCLAS JFCOM Servers
UNCLAS EUCOM Server
G081
GCCS Global Command Control
System GCSS Global Combat
Support System
BROKER
GOPAX
ADANS
ISSE GUARD
ISSE GUARD
JALIS
CLAS PENTAGON
CLAS PACOM
CLAS USFK JTAV
CLAS JFCOM
CLAS EUCOM
CLAS CENTCOM
4
The DDDS The Current DoD Repository of DoD
Standardized Data Elements
Intended to be the DoD Repository of Data
Elements to Support DoD Enterprise-wide
Interoperable Data Sharing
5
PART I The Problem
6
Current Problems
1. Incorrect data architecture abstraction
level for representing Enterprise Level
Data Elements for interoperable data sharing 2.
Numerous redundant representations of
Standardized Data Elements (SDEs)
(DIFFERENT NAMES SAME DEFINITION) 3.
Incomplete, non-existent and/or, non-current SDE
metadata 4. Inadequate categories of SDE
metadata 5. Inadequate support / enforcement
of data administration processes for data
management
7
A Data Element Name and Data Element Definition
Refresher Two Data Element Metadata Attributes

• Data Element Name is a label given to establish
  Data Element identity
• Data Element Definition is a description
  providing complete,
• unambiguous meaning represented by a Data
  Element
• Name and Definition together provide the
  semantic context for the
• data item values represented by a Data Element
  
• Some key concepts/principles/facts about Data
  Element Naming and Definition
• NAME and DEFINITION are inseparable
• NAME is a unique identifier for DEFINITION
• A NAME can be viewed as a kind of very short
  DEFINITION
• In order of precedence, DEFINITION creation
  should always
• precede NAME creation
• Impossible to correctly NAME a data element
  with precision without a DEFINITION
• NAME and DEFINITION are at the core of the data
  integration / sharing process
• Many data sharing issues arise from bad data
  element NAMING and
• DEFINITION practices

8
A Proposed Metadata Architecture for Shared
Enterprise Data Elements

• Focuses on solutions for
• Problem 1 Incorrect data architecture
  abstraction level
• Problem 2 Differently named data elements for
  the same
• data element concept
• Problem 4 - Inadequate categories of
  standardized data element
• metadata
• Not a solution for every kind of impediment to
• interoperable data sharing
• Problems 3 and 5 require quality improvements
  in
• process execution and in data management
  governance
• and will be addressed in Part II.
• Will begin by looking more closely at the
• fundamental metadata architecture levels.

9

• Specified Context Data Model Layer
• Roughly analogous to high level conceptual
  Entity-Relationship (E-R) data
• models of functional area/business domain, or
  Community of Interest (COI)
• data depicting the structure and
  relationships of entities and their attributes.
• Implemented Technology Data Model Layer
• Database schemas represented in a particular
  technology (SQL, COBOL, etc)
• based on fully attributed 3rd normal form
  logical data models
• Operational (Vendor) DBMS Data Model Layer
• Roughly analogous to a physical data model
  representing a particular
• vendors version of a technology based
  schema, i.e., Oracle SQL DBMS vs
• IBM DB2 SQL DBMS vs Sybase SQL DBMS, etc,
  etc
• Business Application View Data Model Layer
• Represents the application system access
  interface to a DBMS which
• preserves the separation and integrity of
  the database data from systems
• that operate on and manipulate the data

10
Specified Context (Community of Interest)
Data Model Layer
Layer 1

• May be represented in one, a combination of,
  or all of the following views
• Entity w/attributes no key designations
  un-normalized unresolved many to many
  relationships
• Key based entities un-normalized un-resolved
  many to many relationships
• Fully attributed un-normalized resolved or
  un-resolved many to many relationships
• Fully attributed 3rd normal form developed
  sub-typing resolved many to manys
• Current DDDS / DDA functional / subject area
  domains map to domains represented by
• designated DoD Functional Data Administrators
  (FDAds)
• Logistics DUSD(L)
• Personnel USD(PR)
• Comptroller USD(C)
• Health Affairs ASD(HA)
• Etc, etc
• Each DoD Subject Area DAd should have a
  specified data model that represents the data
  element
• structures and relationships of functional
  area data element requirements for their
  respective functional area

11
Layer 1
Layer 2

• 3RD Normal form ERD logical data model
• Represented in a technology dependent data
  architecture schema
• Technology driven / constrained data element
  naming
• Subject area entity and attribute templates are
  deployed into schema tables and
• columns that must conform to a particular
  chosen technology such as COBOL or SQL.
• Layer 1 attribute metadata is inherited by
  Layer 2 columns
• The relationship between Layer 1 and Layer 2 is
  one to many. That is to say that
• any attribute from Layer 1 may be deployed as
  a column into many Layer 2 schemas.

12
Layer 1
Layer 2
Layer 3

• Roughly analogous to a physical data model
• Vendors versions of particular technology
  based schema such as SQL,
• i.e., Oracle SQL DBMS vs Informix SQL DBMS,
  vs Sybase SQL DBMS, etc, etc.
• Data element naming is bound by vendors
  implemented DBMS business rules
• for a particular technology based schema.
• Again, the relationship between Layer 2 and
  Layer 3 is one to many. That is to say that
• any column from a Layer 2 schema may be
  deployed as a DBMS column in many Layer 3 DBMSs.
  

13
Layer 4

• Data element naming in conformance with
  functional area common business language terms
• Finally, with respect to a single DBMS, the
  relationship between Layers 3 and 4 is also one
  to many
• from 3 to 4. That is, a DBMS column may be
  deployed as view columns in many applications
• that may interface with a particular DBMS.

14
The Problem Sourcing Enterprise Context
Independent Data Element Standards from
Enterprise Context Dependent Data and
Information Systems and Databases
Enterprise Registry of Standardized Shared Data
Elements The DDDS
Enterprise Registry of Standardized Data
Elements (SDE) To Represent Common Enterprise
Data Element Concepts
Enterprise Data Element Concept A
Effective Result Four differently named
versions, or, representations of the
Enterprise common Data Element Concept, A,
that will exist in the Registry as Standardized
Data Elements. Redundancy and ambiguity is the
consequence.
15

• Database System Architecture Design Steps
• Specified Context Data Model Layer
• Implemented Technology Data Model Layer
• Operational Vendor DBMS Data Model Layer
• Business Application View Data Model Layer

The DDDS Repository Intended to represent DoD
globally shared enterprise standard data
elements. Thus, the repository should contain
only one named data element standard for each
unique enterprise level data element concept.
Layer 1
Layer 2
Layer 3
Layer 4
16
The DDDS Repository Intended to represent
DoD globally shared enterprise standard data
elements. Thus, the repository should contain
only one named data element standard for each
unique enterprise level data element concept. In
reality, the repository contains many cases of
differently named data elements that represent
the same data element concept. The result is
uncontrolled redundancy and ambiguity incapable
of supporting seamless and interoperable data
sharing.

• Database System Architecture Design Steps
• Specified Context Data Model Layer
• Implemented Technology Data Model Layer
• Operational Vendor DBMS Data Model Layer
• Application View Data Model Layer

Layer 1
A source for DDDS SDEs
Layer 2
METADATA REPOSITORY
A source for DDDS SDEs
Defense Data Dictionary System
Layer 3
One GIGANTIC semantic mess
A source for DDDS SDEs
17000 SDEs
Layer 4
A source for DDDS SDEs
17
.But, Wheres the Beef ??
The DDDS Big Bun
.the Enterprise Data Element Beef ??
18
Design Layers for a Business Information System
Data Architecture
DATA ELEMENT CONCEPT STRUCTURE
DATA ELEMENT CONCEPT
Layer 0
ISO 11179 BUSINESS CONTEXT INDEPENDENT DATA
ELEMENT REPRESENTATION
Functionally Independent Business Fact Semantic
Templates (Globally Shared Data Elements) (Domain
of DoD Data Administration)

• The Enterprise Data Element Layer
• ISO 11179 Naming and Definition
• Context Independent Data Elements
• Uniform Naming
• Uniform Semantics
• Uniform Value Domains

19
Design Layers for a Business Information System
Data Architecture
CONCEPTUAL VALUE DOMAIN
Layer 0
DATA ELEMENT CONCEPT STRUCTURE
DATA ELEMENT CONCEPT
ISO 11179 BUSINESS CONTEXT INDEPENDENT DATA
ELEMENT REPRESENTATION
Functionally Independent Business Fact Semantic
Templates (Globally Shared Data Elements) (Domain
of DoD Data Administration)
Layer 1
DDDS Source
Layer 2
DDDS Source
One gigantic semantic mess- redundancies
ambiguities
DDDS Source
Layer 3
DDDS Source
Layer 4
20
META MODEL ARCHITECTURE SUPPORTING ENTERPRISE
WIDE SHARED DATA
CONCEPT STRUCTURE TYPE
CONCEPTUAL VALUE DOMAIN
CONCEPTUAL VALUE DOMAIN STRUCTURE
CONCEPTUAL VALUE DOMAIN STRUCTURE TYPE
CONCEPT STRUCTURE
CONCEPT
VALUE DOMAIN STRUCTURE TYPE
VALUE DOMAIN STRUCTURE
VALUE DOMAIN
DATA ELEMENT CONCEPT STRUCTURE TYPE
DATA ELEMENT CONCEPT STRUCTURE
DATA ELEMENT CONCEPT
ISO 11179 BUSINESS CONTEXT INDEPENDENT DATA
ELEMENT REPRESENTATION
Functionally Independent Business Fact Semantic
Templates (Globally Shared Data Elements) (Domain
of DoD Data Administration)
FUNCTIONALLY DEPENDENT TECHNOLOGY INDEPENDENT
DATA MODEL TEMPLATES ATTRIBUTE ? INHERITS DATA
ELEMENT SPECIFIED DATA MODEL (DoD FDAd
Domain)
APPLICATION VIEWS OF DBMS TABLES
COLUMNS VIEW DATA MODEL (Domain of Application
System Managers (SMs and/or PMs)
ATTRIBUTE
ENTITY
SUBJECT
TECHNOLGY DEPENDENT DBMS INDEPENDENT MODEL /
SCHEMA COLUMN ? INHERITS ATTRIBUTE IMPLEMENTED
DATA MODEL) (Data Architects / Modelers Domain)
COLUMN
TABLE
SCHEMA
DBMS DEPENDENT APPLICATION VIEW
INDEPENDENT DBMS COLUMN (Oracle, DB2, etc)
INHERITS ? COLUMN OPERATIONAL DATA MODEL
(Domain of Database Administrators (DBAs))
DBMS COLUMN
DBMS TABLE
DBMS SCHEMA
21
Design Layers for a Business Information System
Data Architecture
Layer 0
DATA ELEMENT CONCEPT STRUCTURE
DATA ELEMENT CONCEPT
ISO 11179 BUSINESS CONTEXT INDEPENDENT DATA
ELEMENT REPRESENTATION
Functionally Independent Business Fact Semantic
Templates (Globally Shared Data Elements) (Domain
of DoD Data Administration)
Layer 1
Layer 2
Layer 3
Layer 4
22
META MODEL ARCHITECTURE SUPPORTING ENTERPRISE
WIDE SHARED DATA
CONCEPT STRUCTURE TYPE
CONCEPTUAL VALUE DOMAIN
CONCEPTUAL VALUE DOMAIN STRUCTURE
CONCEPTUAL VALUE DOMAIN STRUCTURE TYPE
CONCEPT STRUCTURE
CONCEPT
VALUE DOMAIN STRUCTURE TYPE
VALUE DOMAIN STRUCTURE
VALUE DOMAIN
DATA ELEMENT CONCEPT STRUCTURE TYPE
DATA ELEMENT CONCEPT STRUCTURE
DATA ELEMENT CONCEPT
ISO 11179 BUSINESS CONTEXT INDEPENDENT DATA
ELEMENT REPRESENTATION
Functionally Independent Business Fact Semantic
Templates (Globally Shared Data Elements) (Domain
of DoD Data Administration)
APPLICATION VIEWS OF DBMS TABLES
COLUMNS VIEW DATA MODEL (Domain of Application
System Managers (SMs and/or PMs)
FUNCTIONALLY DEPENDENT TECHNOLOGY INDEPENDENT
DATA MODEL TEMPLATES ATTRIBUTE ? INHERITS DATA
ELEMENT SPECIFIED DATA MODEL (DoD FDAd
Domain)
ATTRIBUTE
ENTITY
SUBJECT
TECHNOLGY DEPENDENT DBMS INDEPENDENT MODEL /
SCHEMA COLUMN ? INHERITS ATTRIBUTE IMPLEMENTED
DATA MODEL) (Data Architects / Modelers Domain)
COLUMN
TABLE
SCHEMA
DBMS DEPENDENT APPLICATION VIEW
INDEPENDENT DBMS COLUMN (Oracle, DB2, etc)
INHERITS ? COLUMN OPERATIONAL DATA MODEL
(Domain of Database Administrators (DBAs))
DBMS COLUMN
DBMS TABLE
DBMS SCHEMA
23
An Optimal Application of an ISO 11179 Based
Data Element Architecture for Resolving
Disparate Representations of Shared Enterprise
Data Elements
Business Application Information System
(AIS) View Model
Metadata Repository Architecture of
Related Representations of DoD Enterprise Shared
Data Elements in Support of Data and Information
Sharing
Vendor Dependent SQL DBMS Operational Model
Army SAMS (AIS)
Oracle DBMS
Supply Item Resource Quantity
ISO 11179 Context Inde pendent Data Element
Representation Meta Model
Technology Dependent Implemented Model
Functional/Organizational Context Dependent
Specified Model
ANSI SQL
Concepts
Business Fact Semantic Template Name
Supply Item Resource Quantity
Data Element Concept
Army Logistics Management
Materiel Resource
Physical Item Balance
Supply Item Resource Quantity
Conceptual Value Domain
Data Element
Supply Item Resource Quantity
SQL Column Names
View Column Names
Physical Measure
DBMS Column Names
Attribute Names
Quantity
Supply Item Resource Quantity
Navy Logistics Management
Value Domain
ANSI SQL
Supply Item Resource Quantity
Data Element Definition
Supply Item Resource Quantity
The quantity of each type of Federal Supply
System materiel item contained in an
identifiable inventory of materiel objects.
Sybase DBMS
Navy UADPS (AIS)
Additional Data Element Structural Metadata
Data type characteristics, local definition,
enumerated values ( if specific ), etc.
24
(No Transcript)
25
(No Transcript)
26
(No Transcript)
27
Part II Implementing the Metadata
Architecture For Enterprise-wide Data Sharing in
a Legacy System Environment
28
Table of Contents

1. A Realistic and Practical Approach to Achieve the
   Ideal Solution
2. How Do We Get to the Ideal?
3. Find Our Metadata
4. Perform Smart Meta-Data Mining
5. Find the Right Starting Layer
6. Reverse Engineer to Build the Upper Layers
7. Overall Forward Engineering Process
8. Process Statistics
9. Lessons Learned

29
1.0 META MODEL ARCHITECTURE SUPPORTING
ENTERPRISE WIDE SHARED DATA
CONCEPT STRUCTURE TYPE
CONCEPTUAL VALUE DOMAIN
CONCEPTUAL VALUE DOMAIN STRUCTURE
CONCEPTUAL VALUE DOMAIN STRUCTURE TYPE
CONCEPT STRUCTURE
CONCEPT
VALUE DOMAIN STRUCTURE TYPE
VALUE DOMAIN STRUCTURE
VALUE DOMAIN
DATA ELEMENT CONCEPT STRUCTURE TYPE
DATA ELEMENT CONCEPT STRUCTURE
DATA ELEMENT CONCEPT
ISO 11179 BUSINESS CONTEXT INDEPENDENT DATA
ELEMENT REPRESENTATION
Functionally Independent Business Fact Semantic
Templates (Globally Shared Data Elements) (Domain
of DoD Data Administration)
APPLICATION VIEWS OF DBMS TABLES
COLUMNS VIEW DATA MODEL (Domain of Application
System Managers (SMs and/or PMs)
FUNCTIONALLY DEPENDENT TECHNOLOGY INDEPENDENT
DATA MODEL TEMPLATES ATTRIBUTE ? INHERITS DATA
ELEMENT SPECIFIED DATA MODEL (DoD FDAd
Domain)
ATTRIBUTE
ENTITY
SUBJECT
TECHNOLGY DEPENDENT DBMS INDEPENDENT MODEL /
SCHEMA COLUMN ? INHERITS ATTRIBUTE IMPLEMENTED
DATA MODEL) (Data Architects / Modelers Domain)
COLUMN
TABLE
SCHEMA
DBMS DEPENDENT APPLICATION VIEW
INDEPENDENT DBMS COLUMN (Oracle, DB2, etc)
INHERITS ? COLUMN OPERATIONAL DATA MODEL
(Domain of Database Administrators (DBAs))
DBMS COLUMN
DBMS TABLE
DBMS SCHEMA
30
(No Transcript)
31
2. How Do We Get to the Ideal? (or the least
un-ideal)

• Find our metadata
• Perform smart metadata mining
• Pick the right starting layer
• Reverse engineer to build the upper layers
• Forward engineer to build standard-data
• based applications

32
3. Find Our Metadata

• Existing schemas within running applications as
• thats the only place where data-truth
  resides
• Extract Cobol FDs within running applications
• for the same truth reason
• Finally, research metadata libraries like ERwin
  models

33
3.1 Where We Started

• DoD had 493 (Erwin) data models that were
• developed in the 1990s. There were 5709
  tables
• and 16921 columns in these tables.
• We did not inventory each DoD Agency, but the
  key
• investigator (Hank Lavender) is very much
  aware of
• what, where, and how much all the schemas
  overlapped.
• This effort was to prove the process. We
  will soon
• start real Enterprise-wide data sharing
  projects.

34
4. Perform Smart Meta-Data Mining

• Pick backbone and rib-cage (HR, Finance,
  Inventory
• Customer Management, Sales) Applications
• Pick the most commonly used schemas across the
• enterprise that support the backbone and
  rib-cage
• applications
• Pick the subset of schemas that have the most
• commonly used tables
• (note commonly used is different from
  exactly the same as)
• Make Where-Used and Frequency-Used Matrices

35
4.1 Where Used Frequency Matrices
Basic Types and Populations
IDM Data Model
Counts Data Model
Description Tables
Columns Relationships C-03
Budgets Currency 56
178 53 C3-12 Command
Control 28 276
65 ES-07 Environmental
Hazards 41 464
46 ES-08 Environmental Projects
28 185 40
LG-06 Transportation Operations 19
91 26 LG-23
Materiel Documentation 36
272 51 LG-28
Materiel Characteristics 45
225 48 PR-22 Training
Instruction 20 135
23 PR-31 Person
Characteristics 36 118
41
Totals 309
1944 393
542 Unique Data Element Concepts
36
4.1 (cont) Subject Areas Use Across IDM Schemas
37
4.1 (cont) Where Used Frequency Matrices
Frequency of Use Matrix
SDM Subject Area Entity
C-03 C3-12 ES-07 ES-08 LG-06 LG-23
LG-28 PR-22 PR-31
IDM Schemas Tables
Logistics Management
Organization X X X X
X X
X Person X
X X X
X X Country X X
X X X Location
X X Task
X

X Facility
X X Plan
X
X Guidance
X
X Geolocation X
X X
Personnel Management
Logistics Operations
Logistics Operations
Management Administration
Property Management
Logistics Planning
Environmental Management
Property Management
38
5. Find the Right Starting Layer
Data Modeling Layer Description
Start Here ?
Context independent business fact semantic
templates
NO, these are not database models and have no
context
Data Elements
NO, as these are just templates and not
database models
Technology independent data model templates
Specified Data Model
OK- if you have Erwin like data models that
can be researched, tabulated, and extracted via
Excel or SQL DDL
DBMS independent database data models and hosts
for database object classes
Implemented Data Model
YES! This is the best as it matches the reality
of operating databases and applications
Operational Data Model
DBMS dependent and Operating System specific
Database application specific SQL views
No, as this is too application-use specific, and
not data model centric.
View Data Model
39
6. Reverse Engineer to Build The Upper Layers

• Import to appropriate layer
• Promote to higher data modeling layer
• Re-engineer the Specified Data Model layer
• Analyze to discover the Data Elements
• Build Data Element Model Metadata layer

40
6.1 Import to Appropriate Layer
IDM
41
6.1 Import to Appropriate Layer
IDM Tables
IDM
42
6.2 Promote to Higher Data Modeling Layer
Promote IDM to SDM
43
6.2 Key Promotion Issues
44
6.3 Re-engineer the Specified Data Model

• Assign Entities to different Subjects
• Reassign Entities to within Entities
  (sub-typing)
• Reassign Attributes Semantics
• Conform Attribute Names to Subject Area Scope
• Reassign Attributes to different Entities
• Reassign Attributes to different Data Elements

SDM
Reassign Entity to Subject
45
6.3 Re-engineering the Specified Data Model
BASIC PROCESSES
SDM
Reassign Entity to Subject
Assign Attribute Meta Category Values
Reassign Attribute to Data Element
Reassign Attribute to Entity
46
6.4 Reallocate Foreign Keys to Encapsulate
Subjects Entities
SDM

• For Each Subject Area
• Make a List of Entities
• Make a Subject Area Based E-R Model Diagram
• Delete Unnecessary Foreign Keys from Existing
  Entities
• Make New Foreign Keys Where Needed
• Export to E-R Diagrammer to Verify Result
• Recycle if Necessary

47
6.4 (cont) Reallocate Foreign Keys
to Encapsulate Subjects Entities
Validate/Create SDM Foreign Keys
SDM
48
6.4 (cont) Reallocate Foreign Keys
to Encapsulate Subjects Entities
Modify SDM Foreign Keys
SDM
49
6.5 Discover the Data Element
Promote SDM Attributes to Data Elements
50
6.6 Build Data Element Model Level Metadata
51
6.7 Data Element, Attribute, Column Differences
52
7.0 Overall Forward Engineering Process

• Import from higher level to lower level
• Map IDM to ODM legacy schemas to
• preserve existing systems environment
• and/or
• Generate new ODM schemas to replace
• legacy systems
• SQL Views can support legacy names or
• new names
• Generate Application

53
7.1 Import From Higher Level To Lower Level
Subject Area Data Model to
Implemented Data Model

• Start Metabase IDM
• Make the Target Schema
• Pick an SDM Subject
• Select the Root Entity
• Create the Data Model Entity Tree
• Perform Import (from SDM to IDM)
• Prune Schema-Table Set to Just Those Needed
• Prune Table-Column Set to Just Those Needed
• Move Columns Among Tables as Needed
• Import Next SDM Model and Perform Pruning
  Steps
• Mapping to New IDM from SDM Preserved-----Of
  Course!

54
7.1 (cont) Import From Higher Level To Lower
Level
Subject Area Data Model to
Implemented Data Model
Import SDM Entities to IDM Tables
55
7.1 (cont) Import From Higher Level To Lower
Level
Implemented Data Model to
Operational Data Model

• Start Metabase ODM
• Make the Target DBMS Schema
• Pick an IDM Schema
• Select the Root Table
• Create the Data Model Table Tree
• Perform Import (from IDM to ODM)
• Prune DBMS Schema-DBMS Table Set to Just
  Those Needed
• Prune DBMS Table-DBMS Column Set to Just
  Those Needed
• Move DBMS Columns Among DBMS Tables as Needed
• Import Next IDM Model and Perform Pruning
  Steps
• Mapping to New ODM from IDM Preserved-----Of
  Course!

56
7.1 (cont) Import From Higher Level To Lower
Level
Implemented Data Model to
Operational Data Model
Import IDM Schema Tables to ODM DBMS Tables
57
7.2 Generate SQL
Generate DBMS SQL DDL
ODM
58
7.3 Generate Application
ODM
59
   
8. Process Statistics
60
8. (cont) Process Statistics
Task
Name
Notes
Effort Metric
5
Abstract to Data Element
Required review of each attribute, and creation
of MCVs, etc.
0.25 hours per attribute for 1000 attributes. So,
250 hours.
6
Build Data Element Model Level Metadata
Required generation of higher level concepts,
value domains, etc.
80 hours

Import from higher level to lower level
Required design of new data models for new
databases from data model templates, and/or just
re-mapping to existing models
8 hours per model for 10 existing models and for
2 new models. Thus, 100 hours
7
Required specification of data types and lengths
8
20 columns per hour for 80 hours.
Generate SQL
1 hour to export, 1 hour to generate 1st cut
application
Input to and then Generate Application
9
Export of one Model from IDM
61
9. Lessons Learned

• It can be done. However, it is not a walk in
  the park!
• It requires clear understanding of separation
  of Data Models. Data Element
• from Specified DMs, from Implemented DMs, and
  from Operational DMs.
• These are NOT transformations (conceptual to
  logical to physical). These
• are different data models.
• Subject Matter Experts are Essential, Critical,
  and Absolutely Necessary.
• Its not top down. Its bottom-up. But once
  built, use it top-down.
• You must have a metadata repository and data
  modeling tool that works
• at the enterprise level, and not just at the
  database or data model level.
• We made changes to the metadata repository
  system along the way. So,
• being able to change the meta model, entry
  and update and reports, is essential.
• Given that Entity reuse for just these ODS
  models was about 4x, the value
• for the data model template reuse in data
  warehouses and data marts is

62
THANK YOU
Hank Lavender Senior Information Engineer 1310
Braddock Place Alexandria, Virginia
22314-1648 Phone [phone] Fax
1.703.836.8691 Email hlavender_at_amerind.com WWWe
b lthttp//www.amerind.comgt
63
Back-ups
64
Proposed DoD Metadata Repository
Complete Representations of Data Element Metadata
DoD CORE DATA ELEMENT METADATA REPOSITORY
Data Element Metadata Relationships to Multiple
Categories of Metadata
Today
ISO 11179 Model Layer
Application Metadata
Specified Model Layer
Core DoD Enterprise Data Element Metadata
Repository
Business Rule Metadata
Implemented Model Layer
Data Movement Metadata
Data Management Metadata
Operational DBMS Layer
Application View Layer
DoD Enterprise Interoperability Metadata
Repository
The Future
65
The Current DoD Architecture for Defining
Standard Data Element Representations of
Shared Enterprise Data Elements
Business Application Information System
(AIS) View Model
Vendor Dependent SQL DBMS Operational Model
Army SAMS (AIS)
Oracle DBMS
Technology Dependent Implemented Model
Functional/Organizational Context Dependent
Specified Model
ANSI SQL
Army Logistics Management
Mat_Inv_Qty
Data Element Definition
The quantity of each type of Federal Supply
System materiel item contained in an
identifiable inventory of materiel objects.
SQL Column Names
View Column Names
DBMS Column Names
Attribute Names
Additional Data Element Structural Metadata
Data type characteristics, local definition,
numerated values ( if specific), etc.
Navy Logistics Management
ANSI SQL
METADATA REPOSITORY
Sybase DBMS
Navy UADPS (AIS)
Defense Data Dictionary System
(SDE Access Name)
(DoD Standardized Data Elements) 16000
SDEs
Business Rule Only one named representation is
permitted to exist in the repository as an
Enterprise SDE.
66
SQL DBMS
DoD Global Information Grid (GIG)
ODM LAYER
Feedback
OUTPUT ODM LAYER
Output XML Wrapped Metadata
Output Schema Information
XML Schema Info Tables
HUMAN RESOURCES DATABASE