Continue in 2 seconds

I have been told to identify all objects relating to a customer in a data warehouse and a data mart.

By
  • Larissa Moss, Clay Rehm, Les Barbusinski
Published
  • May 02 2003, 1:00am EDT
More in

Q:

I have been told to identify all objects relating to a customer in a data warehouse and a data mart. I would indeed be grateful to you if you can throw some light on this topic.

A:

Les Barbusinski’s Answer: The type of customer information stored in a data warehouse or data mart varies with the size and function of the DW and is limited by a) the kinds of products and/or services your company offers and b) the types of customers you have. For example, a financial services company may want to capture information about each customer’s accounts, assets, policies, etc. – while a pharmaceutical company may want to capture information about a customer’s orders, shipments, invoices, payments, etc. Furthermore, since the financial services company deals with individuals, it will probably want to trap demographic information about its customers (such age, education level and average annual income) – while the pharmaceutical company whose customers are clinics, hospitals and drug stores – may want to capture information about its customers’ tax-exempt status.

That said, here are some general categories of customer information you may want to capture in your data warehouse or data mart:

Dimensional Information

  • Type (e.g., individual, company, organization, government agency, etc.)
  • Name (e.g., first, middle, last, prefix/suffix, title, company name, aliases, etc.)
  • Addresses (e.g., home, mailing, business, billing, etc.)
  • Contact Information (e.g., phone/fax/pager numbers, e-mail addresses, etc.)
  • Demographics (e.g., age, income level, education level, ethnicity, marital status, retirement status, disabled, occupation, citizenship, etc.)
  • Characteristics (e.g., D&B rating, FICO score, political affiliation, industry group, market segment, company type, etc.)
  • Relationships/Householding Information

Transactional Information

  • Holdings (e.g., account balances, policy premiums, etc.)
  • Visits (e.g., office/store visits, off-site visits, etc.)
  • Sales Activity (e.g., purchases, orders, shipments, invoices, payments, rebates, returns, warranty claims, etc.)
  • Contact Activity (e.g., inbound/outbound calls, letters, e-mails, complaints, quotes and RFQs, RFIs, RFPs, etc.)
  • Campaign Activity (e.g., mailers, coupons/vouchers, loyalty card usage, etc.)
  • Clickstream Activity (e.g., Web site visits, page clicks, session durations, etc.)

Hope this helps.
Larissa Moss’ Answer: A one sentence requirement ("identify all objects relating to a customer") is not much to go by. My immediate reaction would be to model the customer as a data subject area. That would produce a normalized entity-relationship model to include the customer entity, customer-to-customer recursive relationships, customer parent-child hierarchies, customer-dependent entities (other objects directly associated with the customer) and possibly extend it to customer-product relationships (as in CRM), including product hierarchies and customer-organization relationships (organization being "your company"). On the other hand, maybe you are being asked to model a functional subject area, which would be the business activities (mainly monetary) around customer, such as order processing, order fulfillment, sale, shipment, billing, collections, etc. In that case a denormalized multidimensional model would be more appropriate. I'd be happy to discuss this with further, if I could find out more about the requirements. You can contact me at methodfocus@earthlink.net or (626) 355-8167.

Clay Rehm’s Answer: This depends on your industry, however I would consider the following to be the basics:

  • Demographic information (name, address, e-mail, phone, fax, etc)
  • How long they have been a customer
  • Buying patterns (what products or services have they bought since they have been a customer)
  • Preferred customer info (Frequent Flyer as an example)
  • Number of contracts/accounts/etc.
  • Revenue derived from each customer
  • Expenses incurred from supporting each customer
  • Which customers were lost and why

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access