Metadata - The Final Frontier - Copperstone Connect

gruesomebugscuffleSoftware and s/w Development

Nov 25, 2013 (3 years and 10 months ago)

166 views

Red Sky Data Inc.

1

Presentation # 402


Steve Jones,

David Stanford

Red Sky Data Inc.

Meta Data


The Final Frontier

IOUG Live! 2004

Red Sky Data Inc.

2

Welcome

A little about us

A little about you

Welcome to Toronto

Red Sky Data Inc.

3

Weather Forecast

Red Sky Data Inc.

4

Weather Forecast

Red Sky Data Inc.

5

Weather Forecast

Red Sky Data Inc.

6

Objectives

Gain an in
-
depth understanding of meta data
issues and requirements (in the context of data
warehousing)

Gain an appreciation for Oracle’s solution to
meta data

Provide a real world, implemented solution (a
starting point) for your meta data solution

Red Sky Data Inc.

7

Agenda

Meta data


What Is It?

Overall Requirements

Common Warehouse Metamodel (CWM)

Vendor Tools Today

Oracle’s Meta Data Solution

Shortcomings in the Marketplace

Meta Data Design


A Sample Model

Security in Meta Data

Wrap Up

Red Sky Data Inc.

8

Meta Data


What is it?

More than “data about data”

Collection of information describing the contents
of the data warehouse environment

Used to maximize the value from the data
warehouse


both technical and business

Red Sky Data Inc.

9

Benefits of Meta Data

Improved data consistency

Reduced data redundancy

Easier re
-
use of data and applications that use
it

Enhanced self
-
service for a broader range of
enterprise users

Red Sky Data Inc.

10

Benefits of Meta Data

Lower cost development, implementation, and
maintenance of new applications and
processes

Personalized information

Faster, smoother implementation of third
-
party
applications and tools

Red Sky Data Inc.

11

Categories of Meta Data


Data load statistics


Source and target table/column information


Transformation logic


Obtained from database catalogs, tool repositories,
etc.

Technical Meta Data

Red Sky Data Inc.

12

Categories of Meta Data


Business rules

Source system names and descriptions

Contact names

Entity/attribute names and descriptions

Report names and descriptions

Obtained from business users

Business Meta Data

Red Sky Data Inc.

13

End to End Requirements


Data
Warehouse
Data Marts




TransformTables
Error Correction
Audit



Source
Systems
Staging Area




Meta Data

Red Sky Data Inc.

14

Essential Capabilities

Collection


Capture information from various sources

Technical information (e.g. names, datatypes)

Business information (e.g. definitions, rules)

Transformation logic

Data quality characteristics

Red Sky Data Inc.

15


Available to all warehouse users

Web
-
based

Simple, intuitive interface


Single repository for all meta data

Third normal form

Automated capture process

Essential Capabilities

Design

Deployment

Red Sky Data Inc.

16


Restrict access to sensitive information

User/group
-
based

Table
-
based

May require row
-
level security (rare)


Look for occurrences of keywords

Results by data warehouse area

Essential Capabilities

Security

Search

Red Sky Data Inc.

17

Common Warehouse Metamodel
(CWM)



Specification that describes meta data
interchange among data warehousing,
business intelligence, knowledge
management and portal technologies

Product vendors build adaptors that facilitate
the interchange

Standard interfaces, protocols, language

XML
-
based

Red Sky Data Inc.

18

Meeting End to End Requirements

CWM Metamodel

(shared meta data)

CWM Meta Data
Interchange


Meta Data
Repository

Red Sky Data Inc.

19

CWM Timeline *

199
5

199
6

1997

199
8

1999

200
0

200
1

MDC

releases

OIM v1.0

Red Sky Data Inc.

20

Tool Marketplace

Remains immature

Many tools are vendor
-
specific

Lacks standards, although progress is being
made

Tools good at technical meta data, not so good
at business meta data

Red Sky Data Inc.

21

Selected Vendor Solutions

Major players include:

Computer Associates


Advantage Repository

Data Advantage Group


MetaCenter

IBM


Information Catalog Manager

Informatica


Informatica SuperGlue

Microsoft


Meta Data Services

Oracle


Oracle Repository

Many others...

Red Sky Data Inc.

22

Data Advantage Group

Primary focus is meta data

Flagship product is MetaCenter

Analytical data management platform

Capture, analyze and manage meta data

Tailorable to business needs

Seemless integration with development and deployment tools
(ETL, BI, Case)

Red Sky Data Inc.

23

Meta Integration Model Bridge

(MIMB)

Standalone tool from Meta Integration
Technology Inc.

Allows meta data to be exchanged, or “bridged”
between products (design tools, meta data
repositories, databases, etc.)

Company

Product A

Company

ProductB

MIMB

Export

From (import
bridge)

Import

(export bridge) To

Source: Meta Integration Technology, Inc.

Red Sky Data Inc.

24

MIMB Features

Implements/integrates IDEF1X Data Modelling
and UML Object Modelling standards

Meta data import from:

Relational databases

Data and object modelling design tools

ETL, DW, BI environments

OMG CWM/XMI compliant modelling tools

Meta data repositories

Red Sky Data Inc.

25

Oracle’s Meta Data Solution

Meta data shared between Oracle database
components

Two primary components:

Oracle Warehouse Builder (OWB)

Oracle RDBMS

Red Sky Data Inc.

26

Oracle Warehouse Builder (OWB)

Meta Data Change Management

Backup and history management

Versioned meta data objects

Applies to any individual object (e.g. table) or objects within
objects (e.g. tables within a module)

Support for MITI Bridges

Import designs from over 40 3
rd

party design tools (e.g. ErWin,
PowerDesigner, Embarcadero ER Studio)

Red Sky Data Inc.

27

OWB


Other Features

Information about all ETL components is stored
in the Oracle
-
based repository

Documented public views of repository

API available

Adheres to OMG
-
CWM

Version support

Multi
-
user environment

Red Sky Data Inc.

28

OWB


Other Features

Object
-
level security policies

Meta data accessible from OWB client
application or web
-
based Design Browser

Data lineage and impact analysis viewable from
Design Browser

Descriptive information can be added to
components

Red Sky Data Inc.

29

OWB


Other Features

OWB meta data model is extendible, allowing
addition of user
-
defined properties


user
interfaces automatically extend

Export bridges available:

Oracle Discoverer and Oracle OLAP Server

CWM format

MITI

Red Sky Data Inc.

30

Oracle RDBMS*

Meta data specific features included in 9i
and 10
g

DBMS_METADATA package

Retrieve meta data from repository as XML or DDL

Re
-
create objects using XML / DDL

Red Sky Data Inc.

31

DBMS_METADATA Subprograms

Subprogram

Description

ADD_TRANSFORM Function

Specifies a transform that
FETCH_xxx
applies to the XML representation of the
retri
eved objects

CLOSE Procedure

Invalidates the handle returned by OPEN and cleans up the associated state

CONVERT Function

Converts an XML document to DDL.

FETCH_xxx Functions

Returns metadata for objects meeting t
he criteria established by
OPEN
,
SET_FILTER
,
SET_COUNT
,
ADD_TRANSFORM
, and so on

The GET_xxx Functions

Fetches the metadata for a specified object as XML or DDL, using only a single
call

GET_QUERY Procedure

Returns the text of the queries that are used by
FETCH_xxx

OPEN Procedure

Specifies
the type of object to be retrieved, the version of its metadata, and the
object model


Red Sky Data Inc.

32

DBMS_METADATA Subprograms

Subprogram

Description

OPENW Procedure

Opens a write context

PUT Function

Submits an XML document to the database

SET_COUNT Procedure

Specifies the maximum number of objects to be retrieved in a single
FETCH_xxx

call

SET_FILTER Procedure

Specifies restrictions on the objects to be retrieved, for example, the object name
or sc
hema

SET_PARSE_ITEM Procedure

Enables output parsing by specifying an object attribute to be parsed
and returned

SET_TRANSFORM_PARAM and
SET_REMAP_PARAM Procedures

Specifies parameters to the XSLT
stylesheet identified by
transform_handle


Red Sky Data Inc.

33

DBMS_METADATA Tips

To improve client performance, increase
number of objects returned by FETCH_xxx
functions by using SET_COUNT procedure

Use SET_FILTER procedure to filter meta data
objects

Red Sky Data Inc.

34

Shortcomings In The Marketplace



No one vendor fully supports a complete
meta data solution


Poor business meta data support

Red Sky Data Inc.

35

Inmon’s Evaluation (2001)*

Meta Data

CA

Com
-

paq

HP

IBM

Infor
-

mix

Micro

soft

NCR

Oracle

People

Soft

SAP

SAS

Sun

Central
Repository

D

I

I

I

D

D


D

D

D

D

I

Tools
Meta data

D

I

I

D

D

D


D

D

D

D

I

Distributed
Meta data

D

I

I


D








Impact
Analysis

D

I

I


D






D


Source /
Target
Tracking

D

I

I


D

D





D


Technical
Meta data

D

I

I

D

D

D


D

D

D

D

I

Business
Meta data











D


DBMS
Sensitivity

D

I

I


D






D


Base
Platform
Re
quired

D

I

I


D


D







D= Direct Support, I= Indirect Support

Red Sky Data Inc.

36

A Sample Solution

CWM is best method, but custom solution may
be required to address gaps

Following sample based on environment using:

Informatica

Cognos

DB2

Red Sky Data Inc.

37

Sample Model

Logical divisions

Business Meta Data

Design Meta Data

ETL Meta Data

Reporting Tool Meta Data

Red Sky Data Inc.

38

Sample Model*

Ontario Ministry of Education
ESDW Metadata Repository
Exclusive A
Exclusive A
Exclusive A
fk_rpcolsrt_rptcol
fk_rptcolty_rptcol
fk_rprtcolty_rprt
fk_fldtblty_fldtbl
fk_catdbtype_cat
fk_branch_sys
fk_reslang_sys
fk_dbtype_srcsys
fk_column_fldrcol
fk_domain_column
fk_table_column
fk_table_fldrtable
fk_cntry_contact
fk_prorst_contact
fk_strdir_contact
fk_strtype_contact
fk_minstry_contact
fk_catalog_report
fk_catalog_folder
fk_folder_fldtable
fk_fldcolty_fldcol
fk_report_prompt
fk_report_filter
fk_rprt_rprtcolumn
fk_systype_sys
fk_bam_rule_type
fk_column_rule
fk_role_type_role
fk_contact_role
fk_cat_role
fk_sys_role
fk_rep_role
Source_Column
Target_column
fk_map_etl_stat
fk_map_transform
fk_sys_xtab
fk_tab_xsys
fk_map_systab
fk_updfrq_tabxsys
fk_extrct_xtabsys
fk_rel_xtabsys
fk_tabletype_xtabs
fk_fldrcol_rptcol
fk_fldrtab_fldrcol
fk_sys_cat
Zs_Report_Col_Sort
sort_cd: VARCHAR(5) NOT NULL
sort_type_desc: VARCHAR(10)
sort_type_desc_f: VARCHAR(15)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
Zs_Report_Col_Type
rpt_col_type_cd: VARCHAR(5) NOT NULL
rpt_col_desc: VARCHAR(50)
rpt_col_desc_f: VARCHAR(50)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
Zs_Report_Type
report_type_cd: VARCHAR(5) NOT NULL
report_type_desc: VARCHAR(30)
report_type_desc_f: VARCHAR(40)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
Zs_Fldr_Tab_Type
fldr_tab_type_cd: VARCHAR(5) NOT NULL
fldr_tab_desc: VARCHAR(20)
fldr_tab_desc_f: VARCHAR(25)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
Zs_Catalog_db_Type
cat_db_type_cd: VARCHAR(5) NOT NULL
cat_db_type_desc: VARCHAR(50)
cat_db_type_desc_f: VARCHAR(50)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
ZS_SYSTEM
system_name: VARCHAR(50) NOT NULL
branch_cd: VARCHAR(5) (FK)
resource_lang_cd: VARCHAR(5) (FK)
database_type_cd: VARCHAR(5) (FK)
system_type_cd: VARCHAR(5) (FK)
branch_contact: VARCHAR(20)
internal_users: INTEGER
external_users: INTEGER
system_desc: VARCHAR(2000)
buss_cycle: VARCHAR(2000)
system_location: VARCHAR(100)
hardware: VARCHAR(50)
opsys: VARCHAR(50)
appl_software: VARCHAR(50)
num_data_tables: INTEGER
num_ref_tables: INTEGER
sla_ident: VARCHAR(255)
data_share_ref: VARCHAR(2000)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
ZS_COLUMN
table_name: VARCHAR(50) NOT NULL (FK)
column_name: VARCHAR(50) NOT NULL
domain_name: VARCHAR(75) (FK)
column_key_type: VARCHAR(50)
data_type: VARCHAR(40)
data_precision: INTEGER
data_scale: INTEGER
nullable_ind: CHAR
buss_cycle: VARCHAR(2000)
buss_cycle_defn: VARCHAR(2000)
buss_cycle_content: VARCHAR(2000)
buss_cycle_exp_dt: DATE
buss_name: VARCHAR(75)
buss_name_f: VARCHAR(75)
buss_defn: VARCHAR(2000)
buss_defn_f: VARCHAR(2000)
det_buss_defn: VARCHAR(2000)
det_buss_defn_f: VARCHAR(2000)
url_link: VARCHAR(255)
data_field_layout: VARCHAR(50)
requirement: VARCHAR(50)
requirement_rules: VARCHAR(255)
code_field_ind: CHAR
code_reference_tab: VARCHAR(50)
user_alert_ind: CHAR
user_alert_comment: VARCHAR(2000)
multi_yr_defn_ind: CHAR
validation: VARCHAR(2000)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
ZS_ETL_PROC_STATS
system_name: VARCHAR(50) NOT NULL (FK)
table_name: VARCHAR(50) NOT NULL (FK)
mapping_name: VARCHAR(80) NOT NULL (FK)
start_time_ts: TIMESTAMP NOT NULL
failed_rows: NUMERIC
last_error_desc: VARCHAR(255)
last_error_code: NUMERIC
session_log_file: VARCHAR(255)
session_ts: TIMESTAMP
successful_rows: DOUBLE
version_id: NUMERIC
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
ZS_TABLE
table_name: VARCHAR(50) NOT NULL
buss_name: VARCHAR(75)
buss_name_f: VARCHAR(75)
buss_defn: VARCHAR(2000)
buss_defn_f: VARCHAR(2000)
det_buss_defn: VARCHAR(2000)
det_buss_defn_f: VARCHAR(2000)
url_link: VARCHAR(255)
buss_cycle: VARCHAR(2000)
buss_cycle_defn: VARCHAR(2000)
buss_cycle_content: VARCHAR(2000)
buss_cycle_exp_dt: DATE
multi_yr_defn_ind: CHAR
initial_data_point: VARCHAR(255)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
ZS_CONTACT_INFO
contact_id: INTEGER NOT NULL
country_cd: VARCHAR(5) (FK)
prov_state_cd: VARCHAR(2) (FK)
direction_cd: VARCHAR(2) (FK)
street_type_cd: VARCHAR(5) (FK)
minstry_cd: VARCHAR(5) (FK)
branch: VARCHAR(100)
last_name: VARCHAR(50)
first_name: VARCHAR(30)
position: VARCHAR(50)
street_number: VARCHAR(6)
street_name: VARCHAR(30)
unit_designation: VARCHAR(6)
unit_number: VARCHAR(5)
city: VARCHAR(10)
postal_code: VARCHAR(10)
area_code: VARCHAR(3)
phone: VARCHAR(10)
phone_extension: VARCHAR(5)
email: VARCHAR(255)
fax_number: CHAR(10)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
ZS_DOMAIN
domain_name: VARCHAR(75) NOT NULL
business_name: VARCHAR(75)
business_name_f: VARCHAR(75)
buss_defn: VARCHAR(2000)
buss_defn_f: VARCHAR(2000)
det_buss_defn: VARCHAR(2000)
det_buss_defn_f: VARCHAR(2000)
data_field_layout: VARCHAR(50)
requirement: VARCHAR(50)
requirement_rules: VARCHAR(255)
multi_yr_defn_ind: CHAR
validation: VARCHAR(2000)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
Zs_Database_Type
database_type_cd: VARCHAR(5) NOT NULL
db_type_desc: VARCHAR(50)
db_type_desc_f: VARCHAR(50)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
Zs_Resource_Lang
resource_lang_cd: VARCHAR(5) NOT NULL
resrce_lang_desc: VARCHAR(50)
resrce_lang_desc_f: VARCHAR(50)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
Zs_Branch
branch_cd: VARCHAR(5) NOT NULL
branch_name: VARCHAR(50)
branch_name_f: VARCHAR(50)
branch_desc: VARCHAR(255)
branch_desc_f: VARCHAR(255)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
Zs_Role_Type
role_type_cd: VARCHAR(5) NOT NULL
role_type_desc: VARCHAR(50)
role_type_desc_f: VARCHAR(50)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
Zs_Street_Type
street_type_cd: VARCHAR(5) NOT NULL
street_type_desc: VARCHAR(20)
street_type_desc_f: VARCHAR(40)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
Zs_St_Direction
st_direction_cd: VARCHAR(2) NOT NULL
st_directn_desc: VARCHAR(20)
st_directn_desc_f: VARCHAR(20)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
Zs_Ministry
minstry_cd: VARCHAR(5) NOT NULL
ministry_name: VARCHAR(100)
ministry_name_f: VARCHAR(100)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
Zs_Province_State
prov_state_cd: VARCHAR(2) NOT NULL
prov_state_name: VARCHAR(50)
prov_state_name_f: VARCHAR(50)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
Zs_Country
country_cd: VARCHAR(5) NOT NULL
country_name: VARCHAR(50)
country_name_f: VARCHAR(50)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
Zs_Table_type
table_type_cd: VARCHAR(5) NOT NULL
table_type: VARCHAR(120)
table_type_f: VARCHAR(150)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
Zs_Update_Freq
update_freq_cd: VARCHAR(5) NOT NULL
update_frequency: VARCHAR(20)
update_frequency_f: VARCHAR(40)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
ZS_CATALOG
cat_name: VARCHAR(50) NOT NULL
cat_db_type_cd: VARCHAR(5) (FK)
cat_desc: VARCHAR(255)
cat_db_log_name: VARCHAR(50)
cat_db_phy_name: VARCHAR(50)
metaschema: VARCHAR(30)
system_name: VARCHAR(50) NOT NULL (FK)
create_dt: DATE
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
ZS_FOLDER
cat_name: VARCHAR(50) NOT NULL (FK)
fldr_name: VARCHAR(50) NOT NULL
folder_desc: VARCHAR(255)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
ZS_FOLDER_TABLE
cat_name: VARCHAR(50) NOT NULL (FK)
fldr_name: VARCHAR(50) NOT NULL (FK)
table_name: VARCHAR(50) NOT NULL (FK)
fldr_tab_type_cd: VARCHAR(5) (FK)
fldr_table_name: VARCHAR(75)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
ZS_FOLDER_COLUMN
cat_name: VARCHAR(50) NOT NULL (FK)
fldr_name: VARCHAR(50) NOT NULL (FK)
table_name: VARCHAR(50) NOT NULL (FK)
column_name: VARCHAR(50) NOT NULL (FK)
fldr_col_type_cd: VARCHAR(5) (FK)
fldr_column_name: VARCHAR(50)
fldr_col_derivtn: VARCHAR(255)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
ZS_REPORT
report_name: VARCHAR(50) NOT NULL
report_name_f: VARCHAR(80)
report_type_cd: VARCHAR(5) (FK)
cat_name: VARCHAR(50) (FK)
report_desc: VARCHAR(2000)
report_desc_f: VARCHAR(2000)
instr_gen: VARCHAR(2000)
instr_gen_f: VARCHAR(2000)
instr_oper: VARCHAR(2000)
instr_oper_f: VARCHAR(2000)
report_defn: VARCHAR(2000)
report_defn_f: VARCHAR(2000)
buss_use_defn: VARCHAR(2000)
buss_use_defn_f: VARCHAR(2000)
create_ts: TIMESTAMP
create_status: VARCHAR(20)
refresh_ts: TIMESTAMP
refresh_status: VARCHAR(20)
production_ts: TIMESTAMP
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
ZS_REPORT_COLUMN
report_name: VARCHAR(50) NOT NULL (FK)
report_column_name: VARCHAR(50) NOT NULL
sort_cd: VARCHAR(5) (FK)
rpt_col_type_cd: VARCHAR(5) (FK)
cat_name: VARCHAR(50) (FK)
fldr_name: VARCHAR(50) (FK)
table_name: VARCHAR(50) (FK)
column_name: VARCHAR(50) (FK)
report_col_derivtn: VARCHAR(255)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
Zs_Fldr_Col_Type
fldr_col_type_cd: VARCHAR(5) NOT NULL
fldr_col_desc: VARCHAR(10)
fldr_col_desc_f: VARCHAR(15)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
Zs_Filter
report_name: VARCHAR(50) NOT NULL (FK)
filter_name: VARCHAR(50) NOT NULL
filter_desc: VARCHAR(255)
create_dt: DATE
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
Zs_Prompt
report_name: VARCHAR(50) NOT NULL (FK)
prompt_name: VARCHAR(50) NOT NULL
prompt_desc: VARCHAR(255)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
Zs_System_Type
system_type_cd: VARCHAR(5) NOT NULL
system_type_desc: VARCHAR(50)
system_type_desc_f: VARCHAR(50)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
ZS_BAM_RULE
table_name: VARCHAR(50) NOT NULL (FK)
column_name: VARCHAR(50) NOT NULL (FK)
BAM_type_code: VARCHAR(3) NOT NULL (FK)
BAM_value: INTEGER
BAM_desc: VARCHAR(75)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
Zs_Bam_Type
BAM_type_code: VARCHAR(3) NOT NULL
BAM_type_desc: VARCHAR(2000)
BAM_type_desc_f: VARCHAR(2000)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
ZS_ROLE
contact_id: INTEGER NOT NULL (FK)
object_name: VARCHAR(50) NOT NULL (FK)
role_type_cd: VARCHAR(5) NOT NULL (FK)
object_type_flg: CHAR(3) NOT NULL
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
ZS_TRANSFORM
source_name: VARCHAR(50) NOT NULL (FK)
source_col_name: VARCHAR(50) NOT NULL (FK)
target_name: VARCHAR(50) NOT NULL (FK)
target_col_name: VARCHAR(50) NOT NULL (FK)
version_id: INTEGER NOT NULL
mapping_name: VARCHAR(80) NOT NULL (FK)
source_col_busname: VARCHAR(240)
source_busname: VARCHAR(240)
target_col_busname: VARCHAR(240)
target_busname: VARCHAR(240)
subject_area: VARCHAR(240)
trans_expression: VARCHAR(2000)
exprss_desc: VARCHAR(2000)
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
ZS_MAPPING
mapping_name: VARCHAR(80) NOT NULL
mapping_comment: VARCHAR(2000)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
ZS_TAB_X_SYSTEM
system_name: VARCHAR(50) NOT NULL (FK)
table_name: VARCHAR(50) NOT NULL (FK)
program: VARCHAR(50)
program_desc: VARCHAR(2000)
extract_dt: DATE
production_date: DATE
number_of_records: INTEGER
physical_size: INTEGER
schema_file_name: VARCHAR(80)
user_alert_ind: CHAR
user_alert_comment: VARCHAR(2000)
update_freq_cd: VARCHAR(5) (FK)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
extract_type_cd: VARCHAR(5) (FK)
release_type_cd: VARCHAR(5) (FK)
table_type_cd: VARCHAR(5) (FK)
Zs_Extract_Type
extract_type_cd: VARCHAR(5) NOT NULL
extract_desc: VARCHAR(75)
extract_desc_f: VARCHAR(75)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
Zs_Release_Type
release_type_cd: VARCHAR(5) NOT NULL
release_desc: VARCHAR(75)
release_desc_f: VARCHAR(75)
metadata_review_ts: TIMESTAMP
last_update_ts: TIMESTAMP
expiry_ts: TIMESTAMP
Red Sky Data Inc.

39

Sample Model


Business*

Red Sky Data Inc.

40

Sample Model


Design*

Red Sky Data Inc.

41

Sample Model


ETL*

Red Sky Data Inc.

42

Sample Model


Reporting*

Red Sky Data Inc.

43

Meta Data Load Stream





Source

Extracts

End Users

Data
Warehouse

Staging

Area

Data

Mart

Data

Mart

Data
Warehouse
Stream

ERwin

Extract


DB2

Catalog


Informatica

Repository

Cognos

Report

Listing

MS Access

Verification

& Input


Meta Data
Repository


Meta Data
Data Mart

Source

System

Document

End User

Interface

Technical &
Power Users

Meta Data
Stream

Red Sky Data Inc.

44

Security in Meta Data

End user access should be read
-
only

Treat meta data components as any other
database component:

Create users and groups

Grant access to groups

Use Oracle's row
-
level security (or equivalent) if needed

Red Sky Data Inc.

45

In Summary

Meta data completes the architecture of the data
warehouse environment

It includes both technical and business information

Vendor tools have improved, but still some work to
do

CWM is the standard protocol for meta data sharing

Take advantage of Oracle's built
-
in meta data
support

Red Sky Data Inc.

46

A

Q

&

Q U E S T I O N S

A N S W E R S

David Stanford

david.stanford@redskydata.com

Thank You!

Steve Jones

steve.jones@redskydata.com



Session #402