Taming the Customer in Dimensional Modeling A BioTech Experience NoCOUG Conference Thursday, May 16,

1
Taming the Customer in Dimensional
Modeling--------------A BioTech
ExperienceNoCOUG ConferenceThursday, May 16,
2002Wilma Van Dyk, Senior ConsultantBASE
Consulting Group Inc.
2
INTRODUCTION

• Wilma Van Dyk - Biography
• Over 12 years of Industry Technical Experience
• 6 Years of Data Modeling
• 3 Years of Dimensional Modeling, DW Architecture
  and Implementation
• Industries include Manufacturing, Finance,
  Retail, Sales and Marketing, and BioTech
• Author of UC Berkeley Data Warehousing
  Certificate Program Data Modeling Course, TDWI
  Journal Articles
• wvandyk_at_baseconsulting.com
• www.baseconsulting.com

3
WHY THE CUSTOMER DILEMMA?

• Customer Relationship Management (CRM) is the
  guiding principle of analytics today
• But Technical Systems Still Lack the Ability to
  Manage CRM
• In This Presentation well discuss
• The Goals of CRM and how they differ from the
  Reality of Customer data
• An Example of the Customer Dilemma in the BioTech
  Industry.

4
AGENDA
1) CRM DEFINED 3) MODELING THE CUSTOMER
DIMENSION 4) CUSTOMERS IN BIO-TECH 5) RELATING
THE BIO-TECH CUSTOMER TO CRM
5
CRM DEFINED

• As many definitions as stars in the sky
• The idea is that every contact with a customer
  through every channel is stored in the CRM system
  and allows the company to truly understand
  customer actions. - Thomas Hannigan, Chatham
  Systems Group
• Customer Relationship Management (CRM) is the
  strategic application of people, processes, and
  technology in an organization-wide focus on
  improving the profitability of customer
  relationships - DM Martin and AM Peel, The
  PaceSetter Group, 2001
• The infrastructure that enables the delineation
  of and increase in customer value, and the
  correct means to motivate valuable customers to
  remain loyal, .. to buy back again. - Jill Dyche,
  The CRM Handbook, 2000

6
CRM DEFINED

• Another Perspective

7
CUSTOMER DEFINED

• In Dimensional Modeling
• The Customer Dimension is a Conformed
  Dimension
• It is Used in almost every fact table star or
  join and across multiple data marts
• It Contains a lot of information - eg. Multiple
  hierarchies, types, lots of names
• It Includes a lot of records, often from
  disparate and non-matching sources

8
CUSTOMER COMPLEXITIES

• The Customer Dimension often contains design
  complexities. For example
• 1) Some customers have multiple records
• 2) Some customers have multiple sources
• 3) Some customers have multiple children
• 4) Some customer have multiple addresses
• 5) Some customers have multiple contacts

9
CUSTOMER COMPLEXITIES

• 1) Multiple records/Multiple children
• May have to clean up in source system. If
  parent exists, use this for analysis
• 2) Multiple sources
• Find a common number. If does not exist, will
  have duplicate records

10
CUSTOMER COMPLEXITIES

• 3) Multiple Addresses
• Usually naturally fall into multiple records.
  Tie the appropriate one to the fact table,
  depending on the role of the customer
• 4) Multiple Contacts
• Have join through fact or joiner table

11
CUSTOMER COMPLEXITIES - BIOTECH MODEL

• In the BioTech Industry Example, following
  complexities
• All of the previous 4 plus
• 6) Customers had multiple types
• 7) Customers had multiple parents
• 8) Many customers were related to one fact

12
CUSTOMER COMPLEXITIES - BIOTECH MODEL

• 6) Multiple Types
• - Discuss with business - may clean up in source.
  If not, training issue
• 7) Multiple Parents
• - Must split into separate join - through
  separate fact table

13
CUSTOMER COMPLEXITIES - BIOTECH MODEL

• 6) Many Customers/Single Fact Record
• - Must use a joiner table to join the fact to
  the dimension

14
OTHER COMPLEXITIES

• Future phases will include different customers
• Distributors Wholesale Customers (current)
• Hospitals (next)
• Physicians (next)
• Consumers (next)
• With current design can track
• Which distributors purchased drugs
• Which hospitals purchased drug from wholesalers
• Which physicians purchased drugs from wholesalers
• Which physicians are associated with hospitals
• Which consumers purchased drugs from physicians
• How?

15
DESIGN

• Customer-Fact Relationship

16
DESIGN

• Customer-Type Relationship

17
BIOTECH CUSTOMER - RELATE TO CRM?

• Bad News
• - Still too many duplicate customers. Can get a
  top 10 customer report but there are gaps
• Still too many different customer types. Future
  analysis by demographics is incomplete and will
  need major rework of the data
• Good News
• The company is working towards a clean customer
  master using a third party system. All or most
  duplicates will be removed.
• Future demographics are possible through third
  party data.
• Continuous clean up is being done on types

18
IS CRM POSSIBLE?

• In BioTech
• As more and better customer data is added,
  analysis and resulting behavior will improve
• In General
• New trend is for companies to devote serious
  time, money, and effort to maximize customer
  analysis. Key point in data warehousing projects
  in the future.

19
CONCLUSION/QUESTIONS