1
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Metadata Flowin a Multi-Vendor
Enterprise Toolset Focus Area
Session Code: AFM55SN
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
2
Session Abstract SlideMetadata is everywhere underneath all data and object modeling toolsas well as within the repositories of the ETL, DW, EAI, and BI development tools.Typical enterprise metadata flows will be presented and demonstrated around CA products: in particular the integration of ALL Fusion ERwinData Modeler with ETL tools like Informatica PowerCenter or Oracle Warehouse Builder, and BI tools like Business Objects, CognosReportNet.The metadata movement solutions are becoming increasingly powerful as they need to support multiple technologies (from proprietary API or file formats, to standards like CWM XMI), and they need to offer complex metamodel mappings and transformations (e.g. Relational to OLAP & BI).However such complexity also needs to be hidden behind fast, configurable, and easy to use “metadata movement components” that are well integrated (add-on or OEM embedded) within all the development tools.These metadata movement components must be bi-directional (forward and reverse engineering), and reconnect with each tool’s metadata management system (repository versioning, or embedded metadata compare & merge tools).
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
3
Customer Biography Slide
Christian H. BremeauMeta Integration Technology, Inc.Christian has 20 years of experience entirely devoted to metadata repository technologies.– He has been involved with virtually all metadata standards, more
recently with the OMG CWM.– He has also published numerous papers, and is a regular speaker at
DAMA and metadata related conferences.– Today, he is the CEO of Meta Integration which he founded 1997.– The company has established itself as the “Switzerland of Metadata”
per Lou Agosta (a Giga Information Group analyst), and has developed key partnerships as the “metadata component provider” to leading software vendors like Computer Associates, Microsoft, IBM, Oracle, Sybase, NCR, SAS, BO, Cognos & Informatica.
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
4
Agenda Slide
Metadata Movement TechnologiesMetadata Flow Use CasesMetadata Flow Demos
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
5
Multi-Vendor Development Tools
Metadata Flow (heart beat)
Design & Modeling
Data Movement &Data Integration
(ETL & EAI)
Business Intelligence (BI)
DM - DataModeling
OM - ObjectModeling Dimensional
ModelingReport
Authoring
Metadata insideOperational Data Stores & Data Warehouses
RDBMS Schema / DDL
Forward Engineering
(primary metadata flow)
OLAP metadata
Flat File Structures
RDBMS vendors’ new BI initiativepushing OLAP to the database level
XML Schema or DTD
Reverse Engineering
(initial creation or update)Legacy
Migration
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
6
So where is Metadata?
Metadata is right next to the data! (or at least the physical models)– Catalogs and Schemas of RDBMS(s) (DB2, Oracle, SQL Server),– Source code declarations of Java Classes, C/C++ Classes, or Cobol
structures,– DTD(s) and Schemas of XML Files.
Repositories of database design tools:– CA AllFusion ERwin Data Modeler, Oracle Designer,
Repositories of Software Development Environments:– CA Advantage Gen, Microsoft Visual Studio, IBM WebSphere/Eclipse,
Repositories of EAI, ETL, DW, & BI Development Tools.– Legacy Data Migration (LDM) e.g. ETI.– Data Extraction, Transformation, and Load (ETL) e.g. Informatica,– Enterprise Application Integration (EAI) e.g. IBM– Data Warehousing (DW) e.g. Oracle.– Data Mining, Online Analytical Processing (OLAP) e.g. Hyperion.– Business Intelligence (BI) e.g. BO and Cognos.
Sometimes the Repository is the standalone product:– CA Advantage Repository, ASG Viasoft Rochade
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
7
Metadata Exchange within the Design Tool World
Legacy metadata Migration: a design tool has been replaced by another one.
E.g. from CA GroundWorks/Terrainto AllFusion ERwin Data Modeler
Metadata Re-engineering: re-engineering a model from an IDEF1X based design tool to a UML based design tool for application development.
E.g. From CA AllFusion ERwin Data Modelerto IBM Rational Rose roundtrips
Metadata Integration: using the right tool at the right time. a conceptual model can be performed using a UML object-modeling tool,
E.g. IBM Rational Rosethe logical models may be performed using an IDEF1X data-modeling tool,
E.g. CA AllFusion ERwin Data Modelerfinally some of physical models can be performed in a specialized tool.
E.g. Oracle Designer
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
8
Using the Native File Format
• There are 3 major categories:Binary files of design tools or repositories,Text files,
possibly structured in a language like the Rational Rose MDL
Direct database access (underlying repository).
• Benefits & drawbacks:+ Reliable metadata when exporting.– Possibility of corrupting metadata when importing.– Proprietary solution specific to the tool vendor
(often requires high maintenance on new versions).
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
9
Using an Import/Export File Format
• There are 3 major categories:Specific text or binary formatsCSV text files dumping tables from repositories like the CA ERwin 3.x ERXXML files (standards like OMG UML or CWM XMI
or proprietary like CA AllFusion ERwin Data Modeler XML
• There are issues about import/export formats:– Sometimes incomplete or unreliable (try the export/re-import roundtrip test)– Sometimes multiple interpretations of the standards like CDIF and XMI
• Standards are making progress:From the CASE Data Interchange Format (CDIF), To the Metadata Coalition (MDC) XMLbased on the Open Information Model (OIM)(now merged with the OMG)The Object Management Group (OMG)’s XML metadata Interchange (XMI)based on the Meta Object Facility (MOF) as the meta-meta-modelsupporting the Unified Modeling Language (UML) metamodelsand all the Common Warehouse Metamodels (CWM).
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
10
Importing / Exporting Metadata with an API
• There are multiple issues to consider when looking at an API:
Availability for various programming languages (C++, Java) on various platforms (Windows, Unix)Performance when driven by some middleware (OMG CORBA, or MS DCOM/OLE2),Reliability of the Vendor’s External API for Integratorsvs. the Vendor’s Internal API used by their own tools.Complexity & Efficiency of a direct API vs. a generic/reflexive API like the OMG Meta Object Facilities (MOF)Cost as sometimes the API or SDK is not automatically delivered withthe tool (CA Advantage Gen)
• There are some new emerging standards in the Java community driven by the OMG UML CWM leaders
JSR #000040 Java metadata API JSR #000073 Data Mining APIJSR #000069 Java OLAP Interface (JOLAP)
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
11
Metadata Movement Complexity Levels
Level 1 Physical Metadata:– RDBMS schemas and catalogs and DDL (tables, columns, data types)– Flat file structures, XML schemas and DTD
Level 2: Logical (Business) Metadata– Logical models with entity, relationships, attributes, with descriptions, etc.
Level 3: Lineage Metadata:– Design: conceptual object model to logical data model,
and logical data models to their physical data models– ETL: source to target mapping– BI: relational to dimension mapping, and dimensions to cubes or reports
Level 4: Transformation Metadata– ETL: source to target transformations– BI: expressions from relational to dimensions, and dimensions to cubes
Other complex metadata:– graphical layouts, process models, etc.
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
12
Metadata Movement Challenges
Importing (Extracting) metadata from tool “A”:– Vendors offer access to partial/incomplete metadata.– API, vs. file format (native vs. export, XML based or not)– Extracting lineage and transformations often requires complex
parsing of proprietary transformation expressions which containssome syntax from virtually any language (SQL, VB, C/C++, Java)
Exporting (Creating) metadata to tool “B”:– Reliability of the API– Tolerance of the file format (e.g. the ability to export incomplete
mapping or transformations)– Built-in support for metadata version management (e.g. the ability to
compare and integrate with existing metadata)Complexity of metadata movement transformations:– Complex metamodel mapping (e.g. from Relational to OLAP),– Sometimes requires multiple selectable algorithms (bridge options),– Sophisticated transformations far beyond the capabilities of XSLT
scripts or ETL tools.
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
13
Meta Integration’s Metadata Movement SolutionsThe OMG’s Common Warehouse Metamodel (CWM)
{ Peter Frampton,123 Main Street,Mountain-View, CA, 94041 }
M0Data, Object, Instance,(also record, row)
A UML Object Model with a Class “Customer” and an operation “getAddress”A CWM Relational Model with a Table “CustomerAddress” and Columns: “Street”, “Zip”, etc.
M1
Model,Metadata,(also Schema)
The UML MetaModelwith Class, Operations, Attributes, Relationships, etc.The CWM Relational MetaModel with Table, columns, Primary Keys, etc.
M2
Metamodel, Meta-metadata
The MOF MetaMetaModelM3Meta-metamodel
OMGExamples
Meta-Levels
Unified Modeling Language(UML)
Inst
ance
of L
evel
…
Common Warehouse Metamodel(CWM)
WarehouseManagement
Resources
Analysis
Foundation
WarehouseProcess
WarehouseOperation
Transformation
XMLRecord-Oriented
MultiDimensionalRelational
BusinessInformation
SoftwareDeployment
Object Core
Object-Oriented
OLAP Data Mining
InformationVisualization
BusinessNomenclature
DataTypes Expressions Keys
IndexType
Mapping
CWMLevels
Meta Object Facility(MOF)
XML Metadata Interchange(XMI)
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
14
Metadata Conference / DAMA Symposium March 4-8 2001 – Hilton Anaheim California
ERwin
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
15
Metadata Conference / DAMA SymposiumApril 28 – May 2, 2002 / San Antonio, Texas.
AdaptiveRepository
HyperionApplication Builder
IBMDB2 UDB
Meta IntegrationModel Bridge
SASData Builder
CA ALLFusion ERwin DM
AdaptiveRepository
HyperionApplication Builder
IBMDB2 UDB
Meta IntegrationModel Bridge
SASData Builder
Rational Rose DMSybase PowerDesigner
Oracle DesignerDatabaseSchemaExtraction
Popular RDBMS:
DB2,Oracle,
SQL server,etc.
Common Warehouse Metamodel (CWM) XMI
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
16
Business Cases: Between Design Tools
Legacy metadata Migration Issues:– American Automobile Association (AAA) to phase out a particular legacy
design tool from COOL:Gen (TI Composer) to CA AllFusion ERwin DM– Sprint Corporation to standardize (corporate wide) on CA AllFusion ERwin DM
from all used data modeling tools including Oracle Designer 2000 1.3.2, Sybase PowerDesigner PDM 6.1, Silverrun RDM 2.4.4, Sterling COOL:Enterprise (ADW 2.7), COOL:DBA (Terrain for DB2 5.3.2)
A Point to Point metadata Integration Issue:– Pfizer and AIG for a bi-directional exchange
between CA AllFusion ERwin DM and Rational Rose.A Universal metadata Movement Issue:– Merrill Lynch from/to any available tools, repositories, and standards.
A Data Standardization Issue:– Defense Information Systems Agency (DISA) has been using CA AllFusion
ERwin DM for its integrated “DoD Data Model”, when some of the involved agencies were sometimes using other tools like:
COOL:Gen(TI Composer) at the Defense Logistics agency (DLA), andVisible (IE:Advantage) for the Military Health systems (MHS).
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
17
Business Cases: Integrated Conceptual Data Model
Building a corporate integrated conceptual modelfrom multiple sources and maintaining the mappings:
US National Imaging & Mapping Agency (NIMA)Classic data integration issues, Data is everywhere, security issues.
US Internal Revenue Services (IRS) ModernizationMultiple independent projects (with their own design tools and databases)
US Centers For Disease Control & Prevention (CDC)PHCDM (Public Health Conceptual Data Model) under UML,PHCPM (Public Health Conceptual Process Model) under UML.
US DoD Military Health Systems (MHS)FAM-D (Functional Area Modeling – Data) under IDEF1X,FAM-A (Functional Area Modeling – Activity) under IDEF0.
• Leads to interesting use of existing repository and ETL toolsfor solving problems they were not initially designed for.
• ETL Mapping used for Conceptual/Logical/Physical Mapping,• “Metadata Mining” on underlying repository using data reporting tools.
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
18
Forward Engineering from AllFusion ERwin DM to DB2 Cube Views
The model in CA All Fusion ERwin Data Modeler
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
19
Forward Engineering from AllFusion ERwin DM to DB2 Cube Views
The model in CA All Fusion ERwin Data Modeler
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
20
Forward Engineering from AllFusion ERwin DM to DB2 Cube Views
Generation of the DB2 Schema
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
21
Forward Engineering from CA ERwin DM to DB2 Cube Views
MIMB Import from CA AllFusion ERwin DM 4.x
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
22
Forward Engineering from AllFusion ERwin DM to DB2 Cube Views
MIMB Export to IBM DB2 Cube Views
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
23
Forward Engineering from AllFusion ERwin DM to DB2 Cube Views
MIMB Export to IBM DB2 Cube Views
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
24
Forward Engineering from AllFusion ERwin DM to DB2 Cube Views
Metadata import into Cube Views
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
25
Forward Engineering from AllFusion ERwin DM to DB2 Cube Views
Metadata import into Cube Views
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
26
Forward Engineering from AllFusion ERwin DM to DB2 Cube Views
Model Imported in Cube Views
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
27
Forward Engineering from AllFusion ERwin DM to DB2 Cube Views
Model Imported in Cube Views
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
28
Reverse Engineering from DB2 Cube Views to AllFusion ERwin DM
The Original Cube Model in OLAP Center
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
29
Reverse Engineering from DB2 Cube Views to AllFusion ERwin DM
Metadata Export from Cube Views
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
30
Reverse Engineering from DB2 Cube Views to AllFusion ERwin DM
MIMB Import from IBM DB2 Cube Views
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
31
Reverse Engineering from DB2 Cube Views to AllFusion ERwin DM
The Cube Model converted to ERwin 4 containsthe business names and descriptions
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
32
OO to ER: AllFusion ERwin DM / IBM Rational Rose
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
33
OO to ER: AllFusion ERwin DM / IBM Rational Rose
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
34
ETL: AllFusion ERwin DM / Informatica PowerCenter
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
35
ETL: AllFusion ERwin DM / Oracle Warehouse Builder
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
36
ETL: AllFusion ERwin DM / Oracle Warehouse Builder
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
37
ETL: AllFusion ERwin DM / Oracle Warehouse Builder
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
38
BI: AllFusion ERwin DM / Cognos ReportNet
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
39
BI: AllFusion ERwin DM / Cognos ReportNet
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
40
BI: AllFusion ERwin DM / Cognos ReportNet
41
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Questions & Answers
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
42
Related Sessions
“AllFusion Data Modeler: Dimensional Modeling Basics”– Session Code: AFM45ASNA– Speaker/Company: Whitney Owens /
Computer Associates – Date/Time: Wednesday, May 26, 2004: 1:45
p.m. - 2:45 p.m
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
43
Discover a world of training solutions– Learn how CA’s learning solutions can help you meet
your business objectives– Visit CA Technology Services, Education in the
Exhibition Center, at ca.com/education or call us at 1-800-237-9273
Learn how CA Technology Services can help your business– Visit CA Technology Services in the exhibition center
or on the web at ca.com/services
Enterprise Solutions for BusinessCA Technology Services
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
44
Exhibition Center
Related CA and Exhibitor TechnologyComputer Associates– Booth 216 – Life Cycle Management - Change
and Configuration Management
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
45
Session Evaluation Form
... please place it in the basket at the back of the room.
After completing your session evaluation form ...
© 2004 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
46
Notes