1 / 19

INTRODUCTION

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

louie
Download Presentation

INTRODUCTION

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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@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 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.

  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 4plus: • 6) Customers hadmultiple types • 7) Customers hadmultiple parents • 8) Many customers were related toone 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

More Related