460 likes | 614 Views
IOUG Live! 2004. Presentation # 402. Meta Data – The Final Frontier. Steve Jones, David Stanford Red Sky Data Inc. Welcome. A little about us A little about you Welcome to Toronto. Weather Forecast. Weather Forecast. Weather Forecast. Objectives.
E N D
IOUG Live! 2004 Presentation # 402 Meta Data – The Final Frontier Steve Jones, David Stanford Red Sky Data Inc.
Welcome • A little about us • A little about you • Welcome to Toronto
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
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
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
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
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
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
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
End to End Requirements Meta Data
Essential Capabilities Capture information from various sources Technical information (e.g. names, datatypes) Business information (e.g. definitions, rules) Transformation logic Data quality characteristics • Collection
Single repository for all meta data Third normal form Automated capture process Essential Capabilities Design Deployment • Available to all warehouse users • Web-based • Simple, intuitive interface
Look for occurrences of keywords Results by data warehouse area Essential Capabilities Search Security Restrict access to sensitive information User/group-based Table-based May require row-level security (rare)
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
Meeting End to End Requirements CWM Metamodel (shared meta data) CWM Meta Data Interchange Meta Data Repository
MDC releases OIM v1.0 1995 2000 2001 1996 1997 1998 1999 CWM Timeline *
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
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...
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)
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.) MIMB Company ProductB Company Product A (export bridge) To Import Export From (import bridge) Source: Meta Integration Technology, Inc.
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
Oracle’s Meta Data Solution Meta data shared between Oracle database components Two primary components: Oracle Warehouse Builder (OWB) Oracle RDBMS
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 3rd party design tools (e.g. ErWin, PowerDesigner, Embarcadero ER Studio)
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
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
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
Oracle RDBMS* • Meta data specific features included in 9i and 10g • DBMS_METADATA package • Retrieve meta data from repository as XML or DDL • Re-create objects using XML / DDL
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
Shortcomings In The Marketplace No one vendor fully supports a complete meta data solution Poor business meta data support
Inmon’s Evaluation (2001)* D= Direct Support, I= Indirect Support
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
Sample Model Logical divisions Business Meta Data Design Meta Data ETL Meta Data Reporting Tool Meta Data
Sample Model* Reporting Business ETL Design
Meta Data Load Stream Data Warehouse Stream Data Mart Source Extracts Data Warehouse End Users Staging Area Data Mart Cognos Report Listing Source System Document MS Access Verification & Input End User Interface Meta Data Repository Meta Data Data Mart ERwin Extract Meta Data Stream DB2 Catalog Informatica Repository Technical & Power Users
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
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
Thank You! Q & Q U E S T I O N S A N S W E R S David Stanford david.stanford@redskydata.com Steve Jones steve.jones@redskydata.com Session #402 A