A Metadata Architecture
Download
1 / 66

A Metadata Architecture For Enterprise-Wide Data Sharing - PowerPoint PPT Presentation


  • 142 Views
  • Uploaded on

A Metadata Architecture For Enterprise-Wide Data Sharing. Department of Defense (DoD) Data Interoperability Challenge. Same Data Requirements Different Functional Needs, Same Descriptions, Different Names. Logistics. Components/ Services. Transportation. Procurement. Personnel.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'A Metadata Architecture For Enterprise-Wide Data Sharing' - clover


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Slide1 l.jpg

A Metadata Architecture

For

Enterprise-Wide Data Sharing


Slide2 l.jpg

Department of Defense (DoD)

Data Interoperability Challenge

Same Data Requirements

Different Functional Needs, Same Descriptions, Different Names

Logistics

Components/

Services

Transportation

Procurement

Personnel

Command

& Control

Finance

Medical


Slide3 l.jpg

DoD TARGET DATA SHARING ENVIRONMENT

A Logistics Perspective

SAUDI ARABIA

AMMRL

QATAR

IMRL

KUWAIT

NAOMIS

UAE

ATAC

MEXICO

CAMS

DSS

MIMMS

NSIPS

JAPAN

SCS

ATLAS II

CC SS

NALDA II

FRANCE

ATAC

AWRDS APS

MC-TFS

U2

UK

MANPERS

SAILS

MAARS II

CRIM

CANADA

SBSS

SAMS

MPS (BIC)

CAIMS

ISRAEL

(personnel?)

CAS A

SARSS

FIMAR

OAS

SCS

LIF

ARAMIS

GO81

SAAS-MOD

CASEMIS

ASIAN

SASSY

AFEMS

ROAMS

CMIS

SEATO

DO35

TAPDB

NATO

SUPPLY MGMT

TAMMIS

SNAP/SUADPS/FIMAR

ATAV (LIDB)

FUELS - NEURS

WARS

CAIMS

CAIMS

MUFFIN

AMMIS

ARMS

SPS/SDW

PMIS

SAMMS

ATEMS

TAMMIS

MEDSUP

FLIS

SCCR

JECPO

TAMMIS

MEDASM

DVD Prime Vendors

URD

DOD CAV

DAAS LOTS

MEDSILS

UDR

VMI

Freight Links

NAV

NAC

MEDLOG

COP CSE

DISA

DBSS

GCCS - C2

IC3

JL ACTD

ALP

GDSS

WPS

IBS

CFM

TAPE

TAPE

TAPE

DITTS

RFT-E

TCAIMS II

RFT-K

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

JFCOM

Servers

UNCLAS

PACOM

Server

UNCLAS

USFK JTAV

Server

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

USFK

JTAV

CLAS

JFCOM

CLAS

EUCOM

CLAS

CENTCOM

CLAS

PACOM


Slide4 l.jpg

METADATA

REPOSITORY

Defense Data

Dictionary System

(DoD Standardized

Data Elements {SDE})

17000+ SDEs

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


Slide5 l.jpg

PART I:

The Problem


Slide6 l.jpg

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


Slide7 l.jpg

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:

    • NAMEandDEFINITIONare inseparable

    • NAMEis a unique identifier forDEFINITION

    • A NAMEcan be viewed as a kind of very shortDEFINITION

    • In order of precedence,DEFINITIONcreation should always

    • precedeNAMEcreation

    • Impossible tocorrectly NAMEa data elementwith precision without aDEFINITION

    • NAMEand DEFINITION are at the core of the data integration / sharing process

    • Many data sharing issues arise from “bad” data element NAMING and

    • DEFINITION practices


Slide8 l.jpg

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…….


Slide9 l.jpg

Design Layers of a Business Information System Database Architecture

  • 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

      vendor’s 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


Slide10 l.jpg

Design Layers for a Business Information System Database Architecture

“PERSONNEL” FUNCTIONAL AREA

DEPENDENT DATA MODEL TEMPLATES

“LOGISTICS” FUNCTIONAL AREA

DEPENDENT DATA MODEL TEMPLATES

“FINANCIAL” FUNCTIONAL AREA

DEPENDENT DATA MODEL TEMPLATES

“SPECIFIED” DATA MODEL LAYER

(DoD FDAd Domain)

ENTITY

ENTITY

ENTITY

ATTRIBUTE

ATTRIBUTE

ATTRIBUTE

SUBJECT

SUBJECT

SUBJECT

  • 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(P&R)

    • 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

  • or community of interest.


Slide11 l.jpg

Design Layers for a Business Information System Database Architecture

  • Implemented Technology Data Model Layer

FUNCTIONAL AREA / SUBJECT MATTER

DEPENDENT DATA MODEL TEMPLATES

“SPECIFIED” DATA MODEL LAYER

(DoD COI DAd Domain)

ENTITY

ATTRIBUTE

SUBJECT

COLUMN

COLUMN

COLUMN

SCHEMA

SCHEMA

SCHEMA

TABLE

TABLE

TABLE

TECHNOLGY DEPENDENT (e.g.,SQL,)

FULLY ATTRIBUTED, LOGICAL DATA MODEL

“IMPLEMENTED” DATA MODEL LAYER

(Data Architect / Modelers Domain)

TECHNOLGY DEPENDENT (e.g.,COBOL,)

FULLY ATTRIBUTED, LOGICAL DATA MODEL

TECHNOLGY DEPENDENT (e.g.,IDMS,)

FULLY ATTRIBUTED, LOGICAL DATA MODEL

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.


Slide12 l.jpg

Design Layers for a Business Information System Database Architecture

  • Operational Vendor DBMS Data Model Layer

FUNCTIONAL AREA / SUBJECT MATTER

DEPENDENT DATA MODEL TEMPLATES

“SPECIFIED” DATA MODEL LAYER

(DoD COI DAd Domain)

ENTITY

ATTRIBUTE

SUBJECT

COLUMN

SCHEMA

TABLE

TECHNOLGY DEPENDENT (SQL, COBOL, ETC),

FULLY ATTRIBUTED, LOGICAL DATA MODEL

“IMPLEMENTED” DATA MODEL LAYER

(Data Architect / Modelers Domain)

DBMS

TABLE

DBMS

TABLE

DBMS

TABLE

DBMS

COLUMN

DBMS

COLUMN

DBMS

COLUMN

DBMS

SCHEMA

DBMS

SCHEMA

DBMS

SCHEMA

DBMS DEPENDENT (e.g., Sybase)

DBMS DATA MODEL

DBMS DEPENDENT (e.g., DB2)

DBMS DATA MODEL

DBMS DEPENDENT (e.g., Oracle)

DBMS DATA MODEL

“OPERATIONAL” DATA MODEL LAYER

(Domain of Database Administrators (DBAs))

Layer 1

Layer 2

Layer 3

  • Roughly analogous to a physical data model

  • Vendor’s 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 vendor’s 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.


Slide13 l.jpg

Design Layers for a Business Information System Database Architecture

Business Application View Data Model Layer

BUSINESS

INFORMATION

SYSTEM

BUSINESS

INFORMATION

SYSTEM

BUSINESS

INFORMATION

SYSTEM

APPLICATION

VIEW TABLE

APPLICATION

VIEW TABLE

APPLICATION

VIEW TABLE

APPLICATION

VIEW COLUMN

APPLICATION

VIEW COLUMN

APPLICATION

VIEW COLUMN

Layer 1

Layer 2

FUNCTIONAL AREA / SUBJECT MATTER

DEPENDENT DATA MODEL TEMPLATES

“SPECIFIED” DATA DATA MODEL LAYER

(DoD COI DAd Domain)

ENTITY

ATTRIBUTE

SUBJECT

Layer 3

COLUMN

SCHEMA

TABLE

TECHNOLGY DEPENDENT (SQL, COBOL, ETC),

FULLY ATTRIBUTED, LOGICAL DATA MODEL

“IMPLEMENTED” DATA MODEL LAYER

(Data Architect / Modelers Domain)

BUSINESS APPLICATION SYSTEM

VIEW DATA MODEL (Command & Control)

DBMS

TABLE

DBMS

COLUMN

DBMS

SCHEMA

BUSINESS APPLICATION SYSTEM

“VIEW” DATA MODEL (Personnel App)

DBMS DEPENDENT (Oracle, DB2, Sybase, etc)

DBMS DATA MODEL

“OPERATIONAL” DATA MODEL LAYER

(Domain of Database Administrators (DBAs))

BUSINESS APPLICATION SYSTEM

“VIEW” DATA MODEL (Logistics App)

(Domain of Application System

Managers (SMs and/or PMs)

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.


Slide14 l.jpg

The Problem: Sourcing Enterprise “Context Independent” Architecture

Data Element Standards from Enterprise “Context Dependent”

Data and Information Systems and Databases

SDE “A”: Person Given Name

SDE “A”: Employee First Name

SDE “A”: Legal First Name

Standalone Database #1

Enterprise Common

Data Element Concept “A”

Standalone Database #2

Enterprise Common

Data Element Concept “A”

Layer 1

Person Given Name

Layer 1

Sailor Given Name

Layer 2

Salesman First Name

Sailor First Name

Layer 2

EFN

Layer 3

Sail_Frst_Nm

Layer 3

Layer 4

Name

Layer 4

First Name

SDE “A”: Sail_Frst_Nm

Standalone Database #4

Enterprise Common

Data Element Concept “A”

Standalone Database #3

Enterprise Common

Data Element Concept “A”

Layer 1

Employee Given Name

Layer 1

Legal Given Name

Employee First Name

Layer 2

Layer 2

Authoritative First Name

Emp_Gv_Nam

Layer 3

Layer 3 Lg_Auth_Frst_Nm

Given Name

Layer 4

Layer 4

Legal First Name

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.


Slide15 l.jpg

Design Layers for a Business Information System Database Architecture

METADATA

REPOSITORY

Defense Data

Dictionary System

BUSINESS

INFORMATION

SYSTEM

APPLICATION

VIEW TABLE

APPLICATION

VIEW COLUMN

(DoD Standardized

Data Elements)

17000+ SDEs

FUNCTIONAL AREA / SUBJECT MATTER

DEPENDENT DATA MODEL TEMPLATES

“SPECIFIED” DATA MODEL LAYER

(DoD COI DAd Domain)

ENTITY

ATTRIBUTE

SUBJECT

COLUMN

SCHEMA

TABLE

TECHNOLGY DEPENDENT (SQL, COBOL, ETC),

FULLY ATTRIBUTED, LOGICAL DATA MODEL

“IMPLEMENTED” DATA MODELLAYER

(Data Architect / Modelers Domain)

DBMS

TABLE

DBMS

COLUMN

DBMS

SCHEMA

DBMS DEPENDENT (Oracle, DB2, Sybase, etc)

DBMS DATA MODEL

“OPERATIONAL” DATA MODEL LAYER

(Domain of Database Administrators (DBAs))

BUSINESS APPLICATION SYSTEM

“VIEW” DATA MODEL

(Domain of Application System

Managers (SMs and/or PMs)

  • 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


Slide16 l.jpg

Design Layers for a Business Information System Database Architecture

BUSINESS

INFORMATION

SYSTEM

APPLICATION

VIEW TABLE

APPLICATION

VIEW COLUMN

FUNCTIONAL AREA / SUBJECT MATTER

DEPENDENT DATA MODEL TEMPLATES

“SPECIFIED” DATA MODEL LAYER

(DoD COI DAd Domain)

ENTITY

ATTRIBUTE

SUBJECT

COLUMN

SCHEMA

TABLE

TECHNOLGY DEPENDENT (SQL, COBOL, ETC),

FULLY ATTRIBUTED, LOGICAL DATA MODEL

“IMPLEMENTED” DATA MODEL LAYER

(Data Architect / Modelers Domain)

DBMS

TABLE

DBMS

COLUMN

DBMS

SCHEMA

DBMS DEPENDENT (Oracle, DB2, Sybase, etc)

DBMS DATA MODEL

“OPERATIONAL” DATA MODEL LAYER

(Domain of Database Administrators (DBAs))

BUSINESS APPLICATION SYSTEM

“VIEW” DATA MODEL

(Domain of Application System

Managers (SMs and/or PMs)

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


Slide17 l.jpg

….But, Where’s the Beef ?? Architecture

The

DDDS

“Big Bun”

.…the Enterprise Data Element “Beef” ??


Slide18 l.jpg

CONCEPTUAL Architecture

VALUE DOMAIN

STRUCTURE

CONCEPTUAL

VALUE DOMAIN

STRUCTURE TYPE

CONCEPT

STRUCTURE

TYPE

CONCEPT

STRUCTURE

CONCEPTUAL

VALUE

DOMAIN

CONCEPT

VALUE

DOMAIN

STRUCTURE

TYPE

VALUE

DOMAIN

STRUCTURE

DATA ELEMENT

CONCEPT

STRUCTURE TYPE

VALUE

DOMAIN

DATA

ELEMENT

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


Slide19 l.jpg

Design Layers for a Business Information System Data Architecture

CONCEPTUAL

VALUE DOMAIN

STRUCTURE

CONCEPTUAL

VALUE DOMAIN

STRUCTURE TYPE

CONCEPT

STRUCTURE

TYPE

CONCEPT

STRUCTURE

CONCEPT

BUSINESS

INFORMATION

SYSTEM

APPLICATION

VIEW TABLE

APPLICATION

VIEW COLUMN

VALUE

DOMAIN

STRUCTURE

TYPE

VALUE

DOMAIN

STRUCTURE

DATA ELEMENT

CONCEPT

STRUCTURE TYPE

VALUE

DOMAIN

FUNCTIONAL AREA / SUBJECT MATTER

DEPENDENT DATA MODEL TEMPLATES

“SPECIFIED” DATA MODEL LAYER

(DoD FDAd Domain)

DATA

ELEMENT

ENTITY

ATTRIBUTE

SUBJECT

COLUMN

SCHEMA

TABLE

TECHNOLGY DEPENDENT (SQL, COBOL, ETC),

FULLY ATTRIBUTED, LOGICAL DATA MODEL

“IMPLEMENTED” DATA MODEL LAYER

(Data Architects / Modelers Domain)

The DDDS:

DBMS

TABLE

DBMS

COLUMN

DBMS

SCHEMA

DBMS DEPENDENT (Oracle, DB2, Sybase, etc)

DBMS DATA MODEL

“OPERATIONAL” DATA MODEL LAYER

(Domain of Database Administrators (DBAs))

17000+ SDEs

BUSINESS APPLICATION SYSTEM

“VIEW” DATA MODEL LAYER

(Domain of Application System

Managers (SMs and/or PMs)

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


Slide20 l.jpg

DATA Architecture

ELEMENT

BUSINESS

INFORMATION

APPLICATION

SYSTEM

VIEW

COLUMN

STRUCTURE

TYPE

VIEW

VIEW

COLUMN

STRUCTURE

METADATA

REPOSITORY

Data sharing occurs at the “operational and

application” view layers. Made possible through

the relationships between all layers represented by

metadata in a repository that enables relating syntax,

structure, and semantics from any layer to a common

ISO 11179 standard representation.

VIEW COLUMN

ISO 11179

Specified Model

VIEW COLUMN

STRUCTURE

PROCESS

Implemented Model

Operational DBMS

Application View

META MODEL ARCHITECTURE SUPPORTING ENTERPRISE WIDE SHARED DATA

CONCEPT

STRUCTURE

TYPE

CONCEPTUAL

VALUE DOMAIN

STRUCTURE

CONCEPTUAL

VALUE DOMAIN

STRUCTURE TYPE

CONCEPTUAL

VALUE

DOMAIN

CONCEPT

STRUCTURE

CONCEPT

VALUE

DOMAIN

STRUCTURE

TYPE

VALUE

DOMAIN

STRUCTURE

DATA ELEMENT

CONCEPT

DATA ELEMENT

CONCEPT

STRUCTURE TYPE

DATA ELEMENT

CONCEPT

STRUCTURE

VALUE

DOMAIN

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)

ENTITY

ATTRIBUTE

SUBJECT

TECHNOLGY DEPENDENT &

DBMS INDEPENDENT MODEL / SCHEMA

COLUMN  INHERITS ATTRIBUTE

“IMPLEMENTED” DATA MODEL)

(Data Architects / Modelers Domain)

COLUMN

SCHEMA

TABLE

DBMS DEPENDENT &

APPLICATION VIEW INDEPENDENT

DBMS COLUMN (Oracle, DB2, etc) INHERITS  COLUMN

“OPERATIONAL” DATA MODEL

(Domain of Database Administrators (DBAs))

DBMS SCHEMA

DBMS TABLE

DBMS COLUMN


Slide21 l.jpg

Design Layers for a Business Information System Data Architecture

CONCEPTUAL

VALUE DOMAIN

STRUCTURE

CONCEPTUAL

VALUE DOMAIN

STRUCTURE TYPE

CONCEPT

STRUCTURE

TYPE

CONCEPT

STRUCTURE

CONCEPTUAL

VALUE

DOMAIN

CONCEPT

BUSINESS

INFORMATION

SYSTEM

APPLICATION

VIEW TABLE

APPLICATION

VIEW COLUMN

VALUE

DOMAIN

STRUCTURE

TYPE

VALUE

DOMAIN

STRUCTURE

DATA ELEMENT

CONCEPT

STRUCTURE TYPE

VALUE

DOMAIN

DoD CORE DATA

ELEMENT METADATA

REPOSITORY

ISO 11179 Model Layer

FUNCTIONAL AREA / SUBJECT MATTER

DEPENDENT DATA MODEL TEMPLATES

“SPECIFIED” DATA MODEL LAYER

(DoD FDAd Domain)

Specified Model Layer

DATA

ELEMENT

ENTITY

ATTRIBUTE

SUBJECT

Implemented Model Layer

Operational DBMS Layer

COLUMN

SCHEMA

TABLE

Application View Layer

TECHNOLGY DEPENDENT (SQL, COBOL, ETC),

FULLY ATTRIBUTED, LOGICAL DATA MODEL

“IMPLEMENTED” DATA MODEL LAYER

(Data Modelers Domain)

DBMS

TABLE

DBMS

COLUMN

DBMS DEPENDENT (Oracle, DB2, Sybase, etc)

DBMS DATA MODEL

“OPERATIONAL” DATA MODEL LAYER

(Domain of Database Administrators (DBAs))

DBMS

SCHEMA

BUSINESS APPLICATION SYSTEM

“VIEW” DATA MODEL LAYER

(Domain of Application System

Managers (SMs and/or PMs)

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


Slide22 l.jpg

DATA Architecture

ELEMENT

BUSINESS

INFORMATION

APPLICATION

SYSTEM

VIEW

COLUMN

STRUCTURE

TYPE

VIEW

VIEW

COLUMN

STRUCTURE

METADATA

REPOSITORY

Data sharing occurs at the “operational and

application” view layers. Made possible through

the relationships between all layers represented by

metadata in a repository that enables relating syntax,

structure, and semantics from any layer to a common

ISO 11179 standard representation.

VIEW COLUMN

ISO 11179

Specified Model

VIEW COLUMN

STRUCTURE

PROCESS

Implemented Model

Operational DBMS

Application View

META MODEL ARCHITECTURE SUPPORTING ENTERPRISE WIDE SHARED DATA

CONCEPT

STRUCTURE

TYPE

CONCEPTUAL

VALUE DOMAIN

STRUCTURE

CONCEPTUAL

VALUE DOMAIN

STRUCTURE TYPE

CONCEPTUAL

VALUE

DOMAIN

CONCEPT

STRUCTURE

CONCEPT

VALUE

DOMAIN

STRUCTURE

TYPE

VALUE

DOMAIN

STRUCTURE

DATA ELEMENT

CONCEPT

DATA ELEMENT

CONCEPT

STRUCTURE TYPE

DATA ELEMENT

CONCEPT

STRUCTURE

VALUE

DOMAIN

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)

ENTITY

ATTRIBUTE

SUBJECT

TECHNOLGY DEPENDENT &

DBMS INDEPENDENT MODEL / SCHEMA

COLUMN  INHERITS ATTRIBUTE

“IMPLEMENTED” DATA MODEL)

(Data Architects / Modelers Domain)

COLUMN

SCHEMA

TABLE

DBMS DEPENDENT &

APPLICATION VIEW INDEPENDENT

DBMS COLUMN (Oracle, DB2, etc) INHERITS  COLUMN

“OPERATIONAL” DATA MODEL

(Domain of Database Administrators (DBAs))

DBMS SCHEMA

DBMS TABLE

DBMS COLUMN


Slide23 l.jpg

METADATA Architecture

REPOSITORY

Defense Data

Dictionary System

(DoD Standardized

Data Elements)

17000+ SDEs

Supply Item

Resource

Quantity

Supply Item

Resource

Quantity

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

Materiel

Resource

Army Logistics

Management

Physical

Item

Balance

Supply Item

Resource

Quantity

Conceptual

Value Domain

Data Element

Supply Item

Resource

Quantity

SQL Column

Names

Physical

Measure

View Column

Names

Attribute

Names

DBMS Column

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.



Slide25 l.jpg

Example Logistics Application of an ISO 11179 Based Data Element Architecture

for Relating 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

Supply Unit

Quantity

Army SAMS (AIS)

Materiel Unit

Inventory

Quantity

“Oracle” DBMS

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

Data Element

Concept

Materiel

Resource

Army Logistics

Management

Mat_Inv_Qty

Materiel

Inventory

Quantity

Physical

Item

Balance

Conceptual

Value Domain

Data Element

Supply Item

Resource

Quantity

SQL Column

Names

Physical

Measure

View Column

Names

Attribute

Names

DBMS Column

Names

Materiel Item

Inventory

Quantity

Quantity

Mat_Itm_Inv_Qt

Navy Logistics

Management

Value

Domain

Stocked Materiel

Quantity

ANSI SQL

Ships Stores

Quantity

Data Element Definition:

The quantity of each type of

Federal Supply System materiel

item contained in an identifiable

inventory of materiel objects.

CORE METADATA

REPOSITORY

“Sybase” DBMS

Navy UADPS (AIS)

ISO 11179 Model

Specified Model

Additional Data Element

Structural Metadata:

Implemented Model

Operational DBMS

Data type characteristics,

local definition, enumerated

values ( if specific ), etc.

Application View


Slide26 l.jpg

Example Personnel Application of an ISO 11179 Based Data Element Architecture

for Relating 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

Squad Member

Rank Code

Army (AIS)

Unit Member

Rank Code

“Oracle” DBMS

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

Data Element

Concept

Human

Resource

Army Personnel

Management

Sold_Rnk_Cd

Soldier

Rank Code

Personnel

Classifi-

cation

Conceptual

Value Domain

Data Element

Personnel

Ranking

Measure

Person

Grade

Code

SQL Column

Names

View Column

Names

Attribute

Names

DBMS Column

Names

Sailor

Rating Code

Grade

Code

Sail_Rat_Cde

Navy Personnel

Management

Value

Domain

Crew Member

Rating Code

ANSI SQL

Data Element Definition:

Launch Team

Member

Rating Code

The code that represents the level of

authority and responsibility occupied

by Person in a hierarchy of levels

ranging from most superior to most subordinate in which each level is

subordinate to levels above and

superior to levels below.

CORE METADATA

REPOSITORY

“Sybase” DBMS

Navy (AIS)

ISO 11179 Model

Specified Model

Implemented Model

Operational DBMS

Additional Data Element

Structural Metadata:

Application View

Data type characteristics, etc.


Slide27 l.jpg

Part II: Element Architecture

Implementing the

Metadata Architecture

For Enterprise-wide Data

Sharing in a Legacy System

Environment


Slide28 l.jpg

Table of Contents Element Architecture

  • A Realistic and Practical Approach to Achieve the Ideal Solution

  • How Do We Get to the Ideal?

  • Find Our Metadata

  • Perform Smart Meta-Data Mining

  • Find the Right Starting Layer

  • Reverse Engineer to Build the Upper Layers

  • Overall Forward Engineering Process

  • Process Statistics

  • Lessons Learned


Slide29 l.jpg

DATA Element Architecture

ELEMENT

BUSINESS

INFORMATION

APPLICATION

SYSTEM

VIEW

COLUMN

STRUCTURE

TYPE

VIEW

VIEW

COLUMN

STRUCTURE

METADATA

REPOSITORY

Data sharing occurs at the “operational and

application” view layers. Made possible through

the relationships between all layers represented by

metadata in a repository that enables relating syntax,

structure, and semantics from any layer to a common

ISO 11179 standard representation.

VIEW COLUMN

ISO 11179

Specified Model

VIEW COLUMN

STRUCTURE

PROCESS

Implemented Model

Operational DBMS

Application View

1.0 META MODEL ARCHITECTURE SUPPORTING ENTERPRISE WIDE SHARED DATA

CONCEPT

STRUCTURE

TYPE

CONCEPTUAL

VALUE DOMAIN

STRUCTURE

CONCEPTUAL

VALUE DOMAIN

STRUCTURE TYPE

CONCEPTUAL

VALUE

DOMAIN

CONCEPT

STRUCTURE

CONCEPT

VALUE

DOMAIN

STRUCTURE

TYPE

VALUE

DOMAIN

STRUCTURE

DATA ELEMENT

CONCEPT

DATA ELEMENT

CONCEPT

STRUCTURE TYPE

DATA ELEMENT

CONCEPT

STRUCTURE

VALUE

DOMAIN

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)

ENTITY

ATTRIBUTE

SUBJECT

TECHNOLGY DEPENDENT &

DBMS INDEPENDENT MODEL / SCHEMA

COLUMN  INHERITS ATTRIBUTE

“IMPLEMENTED” DATA MODEL)

(Data Architects / Modelers Domain)

COLUMN

SCHEMA

TABLE

DBMS DEPENDENT &

APPLICATION VIEW INDEPENDENT

DBMS COLUMN (Oracle, DB2, etc) INHERITS  COLUMN

“OPERATIONAL” DATA MODEL

(Domain of Database Administrators (DBAs))

DBMS SCHEMA

DBMS TABLE

DBMS COLUMN


Slide30 l.jpg

1.1 Example Personnel Application of an ISO 11179 Based Data Element Architecture

for Relating 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

Squad Member

Rank Code

Army (AIS)

Unit Member

Rank Code

“Oracle” DBMS

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

Data Element

Concept

Human

Resource

Army Personnel

Management

Sold_Rnk_Cd

Soldier

Rank Code

Personnel

Classifi-

cation

Conceptual

Value Domain

Data Element

Personnel

Ranking

Measure

Person

Grade

Code

SQL Column

Names

View Column

Names

Attribute

Names

DBMS Column

Names

Sailor

Rating Code

Grade

Code

Sail_Rat_Cde

Navy Personnel

Management

Value

Domain

Crew Member

Rating Code

ANSI SQL

Data Element Definition:

Launch Team

Member

Rating Code

The code that represents the level of

authority and responsibility occupied

by Person in a hierarchy of levels

ranging from most superior to most subordinate in which each level is

subordinate to levels above and

superior to levels below.

CORE METADATA

REPOSITORY

“Sybase” DBMS

Navy (AIS)

ISO 11179 Model

Specified Model

Implemented Model

Operational DBMS

Additional Data Element

Structural Metadata:

Application View

Data type characteristics, etc.


Slide31 l.jpg

2. How Do We Get to the Ideal? Data Element Architecture

(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


Slide32 l.jpg

3. Find Our Metadata Data Element Architecture

  • Existing schemas within running applications as

  • that’s 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


Slide33 l.jpg

3.1 Where We Started Data Element Architecture

  • 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.


Slide34 l.jpg

4. Perform Smart Data Element ArchitectureMeta-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


Slide35 l.jpg

4.1 Where Used & Frequency Matrices Data Element Architecture

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


Slide36 l.jpg

SDM Data Element Architecture IDM Schemas

Subject Areas C-03 C3-12 ES-07 ES-08 LG-06 LG-23 LG-28 PR-22 PR-31

Environmental

Management X X X

Health

Management X X X X

Logistics

Management X X X X

Logistics

Operations X X X X X

Logistics

Planning X X X

Materiel

Maintenance X X

Materiel

Management X X X X X X X

Transportation

Operations X X X

Property

Management X X X

Personnel

Management X X X X X X

Management

Administration X X X

4.1 (cont) Subject Areas Use

Across IDM Schemas


Slide37 l.jpg

4.1 (cont) Where Used & Data Element Architecture

Frequency Matrices

Frequency of Use Matrix

IDM Schemas & Tables

SDM

Subject Area Entity C-03 C3-12 ES-07 ES-08 LG-06 LG-23 LG-28 PR-22 PR-31

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


Slide38 l.jpg

5. Find the Right Starting Layer Data Element Architecture

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


Slide39 l.jpg

6. Reverse Engineer to Build Data Element Architecture

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


Slide40 l.jpg

Importing SQL DDL Data Element Architecture

6.1 Import to Appropriate Layer

IDM


Slide41 l.jpg

6.1 Import to Appropriate Layer Data Element Architecture

IDM Tables

IDM


Slide42 l.jpg

6.2 Promote to Higher Data Data Element Architecture

Modeling Layer

Promote IDM to SDM


Slide43 l.jpg

Key Difference Between Subject-Entity-Attribute vs Schema-Table-Column

Model of a subject area. Intellectual boundaries, not

data processing boundaries. Not a conceptual version

of a logical database. It’s a subject based data model

template. Define once, use many times, differently in

IDM models.

Subject-Entity-Attribute

(SDM)

Model of a database schema that may involve attributes

from multiple entities in one table, or attributes of

entities across multiple tables. Intended to be

implemented within a DBMS as an operational database.

Schema-Table-Column

(IDM)

Not related. This would then mean Transformational

Relationship.

Subject-Schema

Not related. This would mean Transformational

Relationship

Entity-Table

Yes, Related. This allows define once, use many times

modeling.

Attribute-Column

6.2 Key Promotion Issues


Slide44 l.jpg

6.3 Re-engineer the Specified Schema-Table-Column

Data Model

  • Assign Entities to different Subjects

  • Reassign Entities to within Entities (sub-typing)

  • Reassign Attribute’s Semantics

  • Conform Attribute Names to Subject Area Scope

  • Reassign Attributes to different Entities

  • Reassign Attributes to different Data Elements

SDM

Reassign Entity to Subject


Slide45 l.jpg

6.3 Re-engineering the Specified Schema-Table-Column

Data Model

BASIC PROCESSES

SDM

Reassign Entity to Subject

Assign Attribute Meta Category Values

Reassign Attribute to Data Element

Reassign Attribute to Entity


Slide46 l.jpg

6.4 Reallocate Foreign Keys to Schema-Table-Column

Encapsulate Subject’s 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


Slide47 l.jpg

6.4 (cont) Reallocate Foreign Keys to Schema-Table-Column

Encapsulate Subject’s Entities

Validate/Create SDM Foreign Keys

SDM


Slide48 l.jpg

6.4 (cont) Reallocate Foreign Keys to Schema-Table-Column

Encapsulate Subject’s Entities

Modify SDM Foreign Keys

SDM


Slide49 l.jpg

6.5 Discover the Data Element Schema-Table-Column

Promote SDM Attributes to Data Elements


Slide50 l.jpg

6.6 Build Data Element Model Schema-Table-Column

Level Metadata


Slide51 l.jpg

Key Differences Among Data Element, Attribute, Column Schema-Table-Column

Characteristic Data Element Attribute Column

A characteristic of a

table that exists within

the context of a schema.

A characteristic of an

entity that exists within

the context of a subject.

Stand-alone independent

business fact template.

Context

Reason for

existence

Frequency of

use

Example

Source of value based

refinement of the

intent of the table. The

set of all columns fully

define an instance of

a table.

Source of semantics and

common general meaning

for classes of attributes

and columns.

Source of value based

refinement of the intent

of the entity. The set of

all attributes fully define

an instance of an entity.

Defined once within

the context of a table.

Defined once within the

context of an entity.

source of business facts

across one or more

columns within one or

more tables.

Define once, use many

times to provide semantics

to attributes or columns.

  • Invoice Line Item

  • Part Number

  • Salesman Identifier

  • Customer Identifier

Asset Identifier

Person Identifier

Customer Identifier

Identifier

6.7 Data Element, Attribute,

Column Differences


Slide52 l.jpg

7.0 Overall Forward Schema-Table-Column

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


Slide53 l.jpg

7.1 Import From Higher Level Schema-Table-Column

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!


Slide54 l.jpg

7.1 (cont) Import From Higher Level Schema-Table-Column

To Lower Level

Subject Area Data Model

to

Implemented Data Model

Import SDM Entities to IDM Tables


Slide55 l.jpg

7.1 (cont) Import From Higher Level Schema-Table-Column

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!


Slide56 l.jpg

7.1 (cont) Import From Higher Level Schema-Table-Column

To Lower Level

Implemented Data Model

to

Operational Data Model

Import IDM Schema Tables to ODM DBMS Tables


Slide57 l.jpg

7.2 Generate SQL Schema-Table-Column

Generate DBMS SQL DDL

ODM


Slide58 l.jpg

7.3 Generate Application Schema-Table-Column

ODM


Slide59 l.jpg

Task Schema-Table-Column

Name

Notes

Effort Metric

1

Find the Right Starting Point

With MS/Access based repository of data models. Close to about 100 models

2 days

2

Import to appropriate layer

Had to fix a number of data modeling errors in source CASE tool

40 hours for 10+ data models

3

Promote to Higher Data Modeling Layer

Required several cycles of distilling subjects

40 hours for 10+ models

4

Re-Engineer

Had to re-engineer Fkeys, rename entities and some attributes. Also had to reconnect new attributes to “old columns.”

240 hours for 10+ models

8. Process Statistics


Slide60 l.jpg

8. (cont) Process Statistics Schema-Table-Column

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


Slide61 l.jpg

9. Lessons Learned Schema-Table-Column

  • 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.

  • It’s not top down. It’s 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

  • incalculable.


Slide62 l.jpg

Michael M. Gorman Schema-Table-Column

Founder and President

2008 Althea Lane

Bowie, Maryland 20716-1518

Phone: +1.301.249.1142

Fax: +1.301.249.8955

Email: [email protected]

WWWeb: <http://www.wiscorp.com>

I

n

c

THANK YOU

Hank Lavender

Senior Information Engineer

1310 Braddock Place

Alexandria, Virginia 22314-1648

Phone: +1.703.836.5900

Fax: +1.703.836.8691

Email: [email protected]

WWWeb: <http://www.amerind.com>


Slide63 l.jpg

Back-ups Schema-Table-Column


Slide64 l.jpg

The Payoff Schema-Table-Column

Seamless & Transparent

Information Interoperability

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

The Future

DoD

Enterprise

Interoperability

Metadata Repository


Slide65 l.jpg

Supply Unit Schema-Table-Column

Quantity

Materiel Unit

Inventory

Quantity

Materiel

Inventory

Quantity

Materiel Item

Inventory

Quantity

Mat_Itm_Inv_Qt

Stocked Materiel

Quantity

Ships Stores

Quantity

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

Data Element Definition:

Mat_Inv_Qty

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

Attribute

Names

DBMS Column

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

Defense Data

Dictionary System

Navy UADPS (AIS)

(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.


Slide66 l.jpg

METABASE Schema-Table-Column

REPOSITORY

ISO 11179 Data Element

Templates

Specified Data Model (SDM)

Implemented Data Model (IDM)

Operational DBMS Model (ODM)

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


ad