190 likes | 292 Views
Taming the Customer in Dimensional Modeling -------------- A BioTech Experience NoCOUG Conference Thursday, May 16, 2002 Wilma Van Dyk, Senior Consultant BASE Consulting Group Inc. INTRODUCTION. Wilma Van Dyk - Biography: Over 12 years of Industry Technical Experience 6 Years of Data Modeling
E N D
Taming the Customer in Dimensional Modeling--------------A BioTech ExperienceNoCOUG ConferenceThursday, May 16, 2002Wilma Van Dyk, Senior ConsultantBASE Consulting Group Inc.
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@baseconsulting.com • www.baseconsulting.com
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 we’ll 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.
AGENDA: 1) CRM DEFINED 3) MODELING THE CUSTOMER DIMENSION 4) CUSTOMERS IN BIO-TECH 5) RELATING THE BIO-TECH CUSTOMER TO CRM
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
CRM DEFINED Another Perspective:
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
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
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
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
CUSTOMER COMPLEXITIES - BIOTECH MODEL: • In the BioTech Industry Example, following complexities: • All of the previous 4plus: • 6) Customers hadmultiple types • 7) Customers hadmultiple parents • 8) Many customers were related toone fact
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
CUSTOMER COMPLEXITIES - BIOTECH MODEL: 6) Many Customers/Single Fact Record: - Must use a ‘joiner’ table to join the fact to the dimension
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?
DESIGN: Customer-Fact Relationship
DESIGN: Customer-Type Relationship:
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
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.