Metadata Business Intelligence LOGO Erwin Moeyaert Overview What
Metadata Business Intelligence L/O/G/O Erwin Moeyaert
Overview • • What is metadata? Why is it needed? Types of metadata Metadata life cycle
What is Metadata? • Data ‘reporting’ – WHO created the data? – WHAT is the content of the data? – WHEN was it created? – WHERE is it geographically? – HOW was the data developed? – WHY was the data developed? 3
The Metadata • The name suggests some high-level technological concept, but it really is fairly simple. Metadata is “data about data”. • With the emergence of the data warehouse as a decision support structure, the metadata are considered as much a resource as the business data they describe. • Metadata are abstractions -- they are high level data that provide concise descriptions of lowerlevel data.
What is Metadata ? title supplemental information abstract time period author sources (file) size © 2005 CSC Brands, L. P. All Rights Reserved 5
What is Metadata? • entity • attributes © 2005 CSC Brands, L. P. All Rights Reserved 6
The Meta Data • Last component of DW environments. • It is information that is kept about the warehouse rather than information kept within the warehouse. • Legacy systems generally don’t keep a record of characteristics of the data (such as what pieces of data exist and where they are located).
• Better end user data access and analysis tools can help users figure out how to get information they need out of the warehouse • only good, easily accessible metadata can help them figure out what is available in the data warehouse and how to ask for it.
Metadata Repositories Metadata Users and Developers often need a way to find information on the data they use. Information can include: • Source System(s) of the Data, contact information • Related tables or subject areas • Programs or Processes which use the data • Population rules (Update or Insert and how often) • Status of the Data Warehouse’s processing and condition
General Metadata Issues Associated with Data Warehouse Use q What tables, attributes, and keys does the data warehouse contain? q Where did each set of data come from? q What transformation logic was applied in loading the data? q How has the metadata changed over time? q What aliases exist and how are they related to each other? q What are the cross-references between technical and business terms? q How often does the data get reloaded? q How much data is there? (assists in avoiding the submissions of unrealistic queries)
Typical Mapping Metadata q Identification of original source fields. q Simple attribute-to-attribute mapping. q Attribute conversions. q Physical characteristic conversions. q Encoding/reference table conversions. q Naming changes. q Key changes. q Defaults values. q Logic to choose from among multiple sources. q Algorithmic changes.
Data Warehouse Process Data Characteristics • Raw Detail • Integrated • History • No/Minimal History • Scrubbed • Summaries • Targeted • Specialized (OLAP) Source OLTP Systems Data Marts Data Warehouse • Design • Mapping • Extract • Scrub • Transform • Load • Index • Aggregation • Replication • Data Set Distribution Meta Data System Monitoring Copyright © 1997, Enterprise Group, Ltd. • Access & Analysis • Resource Scheduling & Distribution
Meta Data Description • Information about the data warehouse system – Content – Organizational – Structural – Management Information – Scheduling Information – Contact Information – Technical Information
Why Do You Need Meta Data? • Share resources – Users – Tools • Document system • Without metadata – Not Sustainable – Not able to fully utilize resource
Metadata Life Cycle • Collection - Identify metadata and capture into repository; automate • Maintenance - Put in place processes to synchronize metadata automatically with changing data architecture; automate • Deployment - Provide metadata to users in the right form and with the right tools; match metadata offered to specific needs of each audience
Metadata Collection • Right metadata at the right time • Variety of collection strategies • Sources – potential sources of data for DW – external data – data structures • Data Models - enterprise data model start point – import from CASE tool – correlate enterprise and warehouse models
Metadata Collection • Warehouse mappings – map operational data into warehouse data structure – Need record of logical connection used for mapping and transformation • Warehouse usage information – After roll out – What tables accessed, by whom and for what – What queries written – Capture nature of business problem or query
Maintaining Metadata • Up to date with reality • Capture incremental changes
Metadata Deployment • Warehouse developers need: – physical structure info for data sources – enterprise data model – warehouse data model – concerned with accuracy, completeness and flexibility of metadata – Need access to comprehensive impact analysis capabilities – Need to defend against accuracy & integrity questions
Meta Data • Types – Technical – Business / User • Levels – Core – Basic – Deluxe
Core Technical Meta Data • Source • Target • Algorithm
Basic Technical Meta Data • • • History of transformation changes Business rules Source program / system name Source program author / owner Extract program name & version Extract program author / owner Extract JCL / Script name Extract JCL / Script author / owner Load JCL / Script name
Basic Technical Meta Data (con’t) • • Load JCL / Script author / owner Load frequency Extract dependencies Transformation dependencies Load completion date / time stamp Load completion record count Load status
Deluxe Technical Meta Data • • • Source system platform Source system network address Source system support contact Source system support phone / beeper Target system platform Target system network address Target system support contact Target system support phone / beeper Etc.
Core Business Meta Data • Field / object description • Confidence level • Frequency of update
Basic Business Meta Data • Source system name • Valid entries (i. e. “There are three valid codes: A, B, C”) • Formats (i. e. Contract Date: 82/4/30) • Business rules used to calculate or derive the data • Changes in business rules over time
Deluxe Business Meta Data • • • Data owner contact information Typical uses Level of summarization Related fields / objects Existing queries / reports using this field / object • Estimated size (tables / objects)
Amount of Meta Data • How much Meta Data do I need? • As much as you can support!
Meta Data Functions Technical • • Maintenance Troubleshooting Documentation Logging / Metrics
Meta Data Location • DB Resident – Almost always relational – C/S predominantly – Normalized design – OODB is popular option for proprietary solutions
Repository • Specialized databases designed to maintain metadata, together with tools and interfaces that allow a company to collect and distribute its metadata • Repository Requirements – Logically Common – Open – Extensible
Meta Data Process • Integrated with entire process and data flow – Populated from beginning to end – Begin population at design phase of project – Dedicated resources throughout • Build • Maintain • Design • Mapping • Extract • Scrub • Transform • Load • Index • Aggregation • Replication • Data Set Distribution Meta Data System Monitoring Copyright © 1997, Enterprise Group, Ltd. • Access & Analysis • Resource Scheduling & Distribution
General Metadata Issues General metadata issues associated with Data Warehouse use: – What tables, attributes and keys does the DW contain? – Where did each set of data come from? – What transformations were applied with cleansing? – How have the metadata changed over time? – How often do the data get reloaded? – Are there so many data elements that you need to be careful what you ask for?
Components of the Metadata • Transformation maps – records that show what transformations were applied • Extraction & relationship history – records that show what data was analyzed • Algorithms for summarization – methods available for aggregating and summarizing • Data ownership – records that show origin • Patterns of access – records that show what data are accessed and how often
Typical Mapping Metadata Transformation mapping records include: – – – – – Identification of original source Attribute conversions Physical characteristic conversions Encoding/reference table conversions Naming changes Key changes Values of default attributes Logic to choose from multiple sources Algorithmic changes
Metadata • The structure of metadata will differ between each process, because the purpose is different. • This means that multiple copies of metadata describing the same data item are held within the data warehouse. • Most vendor tools for copy management and enduser data access use their own versions of metadata. 32
Metadata • Copy management tools use metadata to understand the mapping rules to apply in order to convert the source data into a common form. • End-user access tools use metadata to understand how to build a query. • The management of metadata within the data warehouse is a very complex task that should not be underestimated. 33
METADATA VIEWS • BUSINESS USER’S VIEW FROM A BUSINESS USER’S VIEW, METADATA SHOULD CONTAIN THE FOLLOWING SIX ELEMENTS: 1. TABLE OF CONTENTS 2. ORIGIN OF THE DATA FOR THE WAREHOUSE 3. TRANSFORMATION SEQUENCE 4. ACCESS LEVEL 5. TIMELINE OF THE JOURNEY 6. ACCESS ESTIMATES
METADATA VIEWS • DSS (DECISION SUPPORT SYSTEM) DEVELOPER’S VIEW 1. TRANSFORMATION AND BUSINESS RULES 2. DATA MODELS 3. AVAILABLE OPERATION DATA
METADATA VIEWS • CORPORATE VIEW METADATA IS A LOGICAL COLLECTION OF METADATA FROM VARIOUS SOURCES, INCLUDING THE FOLLOWING SIX PLACES:
METADATA VIEWS 1. LEGACY SYSTEM METADATA CONSISTING OF A DATA DICTIONARY CONTAINING INFORMATION ABOUT PROGRAM LIBRARIES, DATABASE CATALOGS AND FILE LAYOUTS. 2. OPERATIONAL CLIENT/SERVER SYSTEMS – CONSISTING OF DISTRIBUTED SOFTWARE COMPONENTS FROM A VARIETY OF VENDORS. 3. ENTERPRISE MODELS –THEY ARE THE FIRST STAGE IN THE ULTIMATE GOAL OF BUILDING CORPORATE METADATA.
- Slides: 41