Customer Dimension

View previous topic View next topic Go down

Customer Dimension

Post  MSLBIDB on Tue Sep 30, 2014 2:09 pm

I am having discussions on creating a customer dimension table and would like some input.

I have 3 types of customers, wholesale, consumer direct and retail customers. There are multiple source systems that contain their respective data.

If I am trying to build out a dimensional model, would I create 1 customer dimension table with an attribute that describes the type of customer (wholesale, retailer, consumer)? Or would I create a separate customer dimension table specific to that type of customer?

MSLBIDB

Posts : 3
Join date : 2014-09-30

View user profile

Back to top Go down

Re: Customer Dimension

Post  ngalemmo on Tue Sep 30, 2014 3:56 pm

I would be wary of combining wholesale and retail customers (direct and in-store) in the same dimension.  You may have to depending on how you build your facts.

The primary problem with retail is that customer identification is an inexact science and usually requires additional structures (such as bridges) to support a changing picture of who the customer really is.  Providing such structures is critical, particularly if you plan to track retail customer activity across channels (direct, in-store).

In general it usually makes sense to keep the wholesale and retail sides separate.  They usually operate under different business models and the kinds of analysis that takes place is significantly different.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Customer Dimension

Post  MSLBIDB on Tue Sep 30, 2014 4:51 pm

Thank You Sir,
would I also create separate fact tables as well? If so, would I then just want to create two separate warehouses that contain the distinct customer types?


MSLBIDB

Posts : 3
Join date : 2014-09-30

View user profile

Back to top Go down

Re: Customer Dimension

Post  ngalemmo on Tue Sep 30, 2014 5:56 pm

I would have separate atomic level facts but not necessarily separate data warehouses.  There are still common integration points (dimensions) for the data and there are opportunities to aggregate the data (such as product sales and margins by channel) that are easiest to do with an integrated enterprise warehouse.

I would only consider physically separate data warehouses if there were specific implementation challenges (retail data volumes are significantly higher than wholesale, for example) that would impact availability and SLA's for the different businesses.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Customer Dimension

Post  MSLBIDB on Tue Sep 30, 2014 10:59 pm

Mr. Galemmo,
I appreciate your time in answering. I am a snoop and looked up your credentials. I see you have extensive experience with data warehouse design and understanding. This question I am going to ask is in no way doubting your opinion, but more to understand why you recommend creating two separate customer dim tables when Kimball design (from my understanding) talks about trying to create a uniform dimension that can take into account any and all attributes of a dimension.
If there are other channels would I need to create an additional customer dimension table to address those as well? I am a sponge and want to learn best practice and do what is best for the current and future growth of the design. I am definitely open to being taught, so please take this as asking advice and not scoffing.
Again, I appreciate your time and help.


MSLBIDB

Posts : 3
Join date : 2014-09-30

View user profile

Back to top Go down

Re: Customer Dimension

Post  ngalemmo on Wed Oct 01, 2014 2:34 pm

I am basing my opinion on past experience, not an analysis of your particular situation, so your milage may vary.

I don't know your business, what it sells, or how it interacts with customers.  I have developed systems for CPG manufacturers and, for the last four years, large retailers, and their view of a customer and how they interact with them are completely different.  On the wholesale side, you know exactly who your customer is, on the retail side you don't.

In retail you have anonymous customers (cash) as well as partially identified customers (loyalty cards, credit cards, etc…).  The same person may make multiple purchases over time using different identifications (persona) and other persons in the same household may make purchases using the same persona.  Even complete strangers may make purchases using someone else's loyalty card to get a discount.  It happens all the time in grocery stores.   Even an on-line transaction is not precise.  For example, I make on-line purchases for my kids on sites I would never use myself.  These are purchases they initiated, but since I am the one with a credit card, I have to set up the account and make the purchase.  Am I the customer or just a facilitator?

On the wholesale side you have different analysis requirements… you look at terms, discounting, pricing structures, marketing agreements and other accommodations and how specific customers perform.  On the retail side you look at demographics, segments, marketing campaign effectiveness against populations, all based on a probable understanding of who the customer may be.

So, in summary, a wholesale customer and a retail customer are not the same thing.  Kimball talks about a common dimension when they represent the same business entity.  For example, you may have three systems providing product information.  They are all products, all well identified, all well defined.  It would make sense to use one dimension.  That is not usually the case with wholesale and retail customers.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Customer Dimension

Post  BoxesAndLines on Thu Oct 02, 2014 8:54 am

This type of activity is normally supported by a data quality or MDM process. The dimensional model is fed from a Party data structure that captures all of the attributes for organizations, people, customers, etc. Most organization have different KPI's for retail vs. wholesale customers. Additionally, the attributes that define these two entities are also different. When you combine all of the different party types into one single dimension you end up with a small set of attribute applicable and the rest are set to null. This can cause problems come report time as it now becomes really easy to filter on attributes that are only particular to a certain type. You can manage this several ways, separate tables or separate views, are two options. If you go separate tables, you should have a common party key that will enable you to union all of the different types to present a holistic view of customer.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Customer Dimension

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum