Wyeth Data Warehousing Metadata REAL LIFE Journey

1
Wyeth Data Warehousing Metadata REAL LIFE
Journey

• Presented by
• Tom Rodgers, Consultant

2
Agenda

• The Company
• Meta Data Background
• Metadata
• What?
• Why?
• Who?
• Where?
• Issues/Barriers
• How did we proceed?
• Where did we end up?
• What happened next?
• Sample Solution
• Physical Data Model
• Application
• Future Initiatives
• Lessons Learned

3
Credits

• Credit goes to David Marco, Adrienne Tannenbaum,
  Robert S. Seiner, Warren Thornthwaite, Bill
  Inmon, Ralph Kimball, et al.

4
Company Information

• Wyeth is a global leader in pharmaceuticals,
  consumer health care products, and animal health
  care products.
• Wyeth has a long history of pioneering
  developments in pharmaceuticals and
  biotechnology, with leading products in the areas
  of womens health care, neuroscience,
  musculoskeletal disorders, cardiovascular
  therapy, vaccines and infectious disease,
  hemophilia, immunology, and oncology. Wyeth is
  also a leader in the development of nutritionals.
• Wyeth Consumer Healthcare is built on strong
  global brands, including some of the worlds most
  popular and best-known consumer health care
  products. Three of our well-established product
  lines Advil, Centrum, and Robitussin are
  among the top 12 non-prescription medicines in
  the world. Other key brands include Chap Stick,
  the Caltrate family of calcium supplements,
  Preparation H, Dimetapp, and Solgar vitamins
  and nutritional supplements.
• As a world leader in animal health, Fort Dodge
  Animal Health offers a wide range of biologicals
  and pharmaceuticals for the livestock, swine, and
  poultry industries, as well as for companion
  animals such as dogs, cats, and horses.
  Innovative Fort Dodge products include vaccines
  such as LymeVax, Duramune, Fel-O-Vax, and a
  West Nile virus vaccine.

5
Company Information

• Wyeth, headquartered in Madison, New Jersey, is
  one of the largest research-based pharmaceutical
  and health care products companies in the world.
  Wyeth is dedicated to solving the world's most
  critical health problems through research and
  development, spending approximately 2.1 billion
  in overall RD in 2003. With annual sales of
  nearly 15.8 billion in 2003 and products sold in
  more than 140 countries, Wyeth is truly a global
  health care leader.
• Wyeth Pharmaceuticals and Wyeth Research employ
  more than 40,000 people worldwide - each one
  committed to improving the health of people
  around the world.

6
My Background

• DBA/DA
• IBM IMS Data Dictionary
• Philadelphia Data Dictionary Users Group
• DAMA

7
Meta Data What is it?

• It is knowledge.
• Knowledge of our systems.
• Knowledge of our business.
• Knowledge of our marketplace.

8
Meta Data What is it?

• Types of Meta Data
• Business or front room meta data Assists or
  enhances the business clients access and use of
  the data in the data warehouse or data mart.
• Technical or back room meta data Source system,
  data staging, DBMS information.

9
Meta Data What is it?

• Business Meta Data
• Business Rules
• Business descriptions for columns, tables
• Report definitions
• End-user documentation and training aids
• Technical Meta Data
• Source system file layouts
• Source to Target mappings
• Fact and dimension definitions
• Aggregation information

10
Meta Data Why do we need it?

• Meta data supplies the necessary context that
  transforms the data warehouse or data mart data
  into useful information. It also translates the
  techno-babble into terms the client community can
  understand.
• Eg. Net Sales is
• Without meta data, net sales is open to
  interpretation. Some wrong, some right ones.
• With meta data, everyone knows the definition of
  what is meant by net sales.

11
Meta Data Why do we need it?

• Meta data builds confidence in the data in the
  data warehouse allows for better business
  decisions based on better understanding and
  confidence in the data.
• Employee Turnover When an employee leaves the
  company, company knowledge leaves as well. Meta
  data seeks to capture the knowledge - both
  technical and business - stored in employees
  heads.
• When a system changes it is extremely difficult
  to anticipate and manage the downstream effects.
  The benefits of meta data are long-lasting
  helping to reduce future release costs and the
  likelihood of errors.
• Reuse versus Rebuild Allows for the easy
  identification and, therefore, reuse of
  previously built data warehouse objects and data.
  

12
Meta Data Why do we need it?

• Data Warehouse creation steps
• Warehouse model
• Source Definitions
• Table Definitions
• Source-to-target maps
• Map and Transformation information
• Physical information (table spaces, etc.)

• Transformed data
• Load statistics
• Business descriptions
• Query descriptions
• The data itself
• Query statistics
• Extracted data

Only Extracted data, Transformed data, and
The data itself involved real data.
Everything else is meta data and the whole data
warehouse process relies on it. Warren
Thornthwaite, DCI DW Summit 12/8/1998
13
Who uses meta data?

• Business Users Sales Analysts, Marketing
  Analysts, Executive decision-makers
• Technical Users Developers, DBAs, Operations
• Data Warehousing Tools SAP/BW, Quality Stage,
  PowerDesigner, Informatica, MicroStrategy, Brio,
  ...

14
Meta Data Where do we store it?

• Meta Data Repositories
• Build
• Buy (Centralized/Tool-based)

15
Issues/Barriers

• Resources
• Time
• People
• Money
• Management Commitment
• Business Client Push
• Corporate Culture

16
How did we proceed?

• More Education
• Books
• Classes
• Consultants

17
How did we proceed?
Defined the existing architecture and developed a
metadata strategy
18
How did we proceed?
19
Where did we end up?
The distributed world Metadata remained in
tool-based repositories Metadata published on the
web through tool vendor APIs
20
What happened next?

• Identified the metadata Pain
• Developed a Questionnaire
• Interviewed metadata clients
• Performed a metadata Gap Analysis
• Proceeded to fill in the Gaps
• Homegrown solutions
• Publish tool metadata on the web

21
Sample Questionnaire
Meta Data Requirements Interview
Questionnaire   Interview Date
___________ Interviewee _________________________
Interviewer _________________________________ B
usiness or technical area _______________________
__   The intention of this questionnaire is to
assist the Meta Data Architecture team in
understanding the key information requirements
that will help meta data users perform their jobs
more efficiently. (Note Questions in bold face
are technical in nature and should not be asked
of business clients. Also note for instances
where people are getting metadata information,
have them explain how it is gotten. Are their
easier ways to get the information? Would they
like us to investigate making metadata more
easily accessible?)   1)      Is there anything
unclear about my current decision support system?
Are there any areas that would benefit by more
information or descriptions? _____________________
__________________________________________________
__________________________________________________
__________________________________________________
__________________________________________________
__________________________________________________
_____         This is an opportunity question
that begins to reveal the users pain. (Get
concrete examples and answers.)
22
Sample Requirements Priority Listing
23
Sample Solution

• Global Data Warehouse Field Definition
  Application
• Metadata Model
• Application Screens

24
Sample Solution
25
Sample Solution
26
Sample Solution
27
Sample Solution
28
Sample Solution
29
Sample Solution
30
Sample Solution
31
Future Initiatives

• Wyeth Information Council Data Management
  sub-committee
• Investigating integrated or catalogued view of
  metadata of both structured and non-structured
  information objects
• Initial focus is non-structured EDMS environment
• Dublin Core standards
• Corporate-wide effort between Wyeth
  Pharmaceuticals and Consumer Health
• Investigating the incorporation of a global
  metadata management program

32
Lessons Learned

• Identify barriers early
• Be optimistic
• Expose your product to the business
• Persistence