Modeling the Customer Dimension

Question for the experts:

I am tasked with modeling a DW for a hospitality company that currently has 1 property with no immediate plans to add a new property. BUT I've been told to design it with acquisitions in mind.

So my question has to do with designing the Customer dimension and what to use for the PK: do I create a composite PK, using the Natural Key (AccountNumber) along with the Property Key (PropertyID) or create a Surrogate Key (CustomerID).

They would also like to track customer changes (address, membership level, etc) but need to query transactions using both historical values and current values. So, for instance, they may ask something like "Show me how much a customer currently classified as Premium has spent life-to-date?" or "Show me how much customers classified as Premium on 1/1/10 have spent life-to-date?".

All my past experience tells me to create a surrogate key but in this situation the client is using Teradata and since Teradata doesn't care about how many columns are used in a join, I'm thinking this might be a situation to use a natural key.

Any suggestions would be appreciated.

Dimension tables should always have a surrogate primary key.

Why on earth did they buy Teradata with only one property? Is the one property the burough of Manhatten?

Anyway, NO database 'cares' about how may columns are in a join. That is not why you use a surrogate key in the first place. It has to do with isolating the row relationships from business keys.

While I am normally for using surrogate keys, here's where my dilemma comes into play....wouldn't using a surrogate key in this situation make it more difficult to query "as of today", as in the example of "Show me all transactions for customers designated as Premium as of today?" because say a customer was at the Basic level for 2006-2009 and upgraded to Premium status from 2010-Present. The transactions for 2006-2009 would have a different surrogate key than the transactions from 2010-present. We'd be able to get the desired results but it seems to add another level of complexity.

Welcome to type 2 dimensions.

There are various strategies to make this simpler to do. But if the model is set, then a self-join will do the trick. But NEVER use natural keys as FKs from a fact. I don't care what platform you are using, it is always a bad idea.

Thanks for your responses, natural key is OUT!! Would you mind sharing some strategies for dealing with this, if type 2 is not the best way. The model isn't set yet so we have some flexibility is another method works better. We've also thought of using surrogate key with start/end dates.

Thanks again.

It has been discussed numerous times. One such thread is http://forum.kimballgroup.com/t1416-dimension-best-practice

OK, here's what we're leaning toward implementing. I would love any input (good or bad):

CUSTOMER dim
---------------
CustomerID
AccountNumber
Name
Gender
BirthDate
>> Note- Customer dimension is Type 1 SCD (we're not tracking changes to name, gender or birthdate)
>> Note- 1:1 relationship between CustomerID and AccountNumber

CUST_MEMBERSHIP
---------------
CustomerID
StartDate
EndDate
MemberLevel

CURRENT_CUST VIEW
---------------------
select * from customer cust
inner join cust_membership member
on cust.customerid = member.customerid
where enddate is null

TRANSACTIONS
---------------
CustomerID
TransactionDate
Amount

Reasoning:
A) It let us implement a surrogate key as opposed to using the natural key
B) We can use the CURRENT_CUST View for the current records for reporting "AS OF TODAY"
C) Allows reporting on historical data

Any thoughts?

Make member level its own dimension and hang it off the fact. Store current member level information on the customer dimension. This will give you point in time member level information as well as current member level information without snowflake or oddball dimensional joins.

If you need to report membership level history, you can do that with the cust_membership table you outlined.

As a side note, you are better off using some standard future date (such as 1/1/3000) instead of null for the expiration date of a current record. This allows you to use BETWEEN when searching for status as of a particular date.

Thanks NG, you've been a big help, much appreciated!