Taming the Customer in Dimensional Modeling -------A Bio. Tech Experience No. COUG 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 • 3 Years of Dimensional Modeling, DW Architecture and Implementation • Industries include Manufacturing, Finance, Retail, Sales and Marketing, and Bio. Tech • Author of UC Berkeley Data Warehousing Certificate Program Data Modeling Course, TDWI Journal Articles wvandyk@baseconsulting. com www. baseconsulting. com 2
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 Bio. Tech Industry. 3
AGENDA: 1) CRM DEFINED 3) MODELING THE CUSTOMER DIMENSION 4) CUSTOMERS IN BIO-TECH 5) RELATING THE BIO-TECH CUSTOMER TO CRM 4
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 Pace. Setter 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 5
CRM DEFINED Another Perspective: 6
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 nonmatching sources 7
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 8
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 9
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 10
CUSTOMER COMPLEXITIES - BIOTECH MODEL: • In the Bio. Tech 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 11
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 12
CUSTOMER COMPLEXITIES - BIOTECH MODEL: 6)Many Customers/Single Fact Record: - Must use a ‘joiner’ table to join the fact to the dimension 13
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? 14
DESIGN: Customer-Fact Relationship 15
DESIGN: Customer-Type Relationship: 16
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 17
IS CRM POSSIBLE? In Bio. Tech: 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. 18
CONCLUSION/QUESTIONS 19
Скачать презентацию Taming the Customer in Dimensional Modeling ——-A Bio
73dfeac9aef2f7f1cf94f1f1e26ca9a7.ppt