Breaking down a Customer Dimension

Hi,

I am trying to create a Consultant dimension for a multi-level marketing company. Consultants are our customers.

The Consultant is the central entity in our business and everything revolves around them.

ConsultantID, Firstname, Lastname, 4 types of addresses(postal/billing/shipping/other), position title, sponsor and status are a few of a 100 attributes for this dimension.

I am supposed to maintain history for the following attributes:

1) Firstname
2) Lastname
3) 4 types of addresses
4) Sponsor
5) Position Title
6) Status


The first 4 attributes MAY change but it is highly infrequent.

Attributes 5 and 6 on the other hand change rapidly (between 0-4 times a month). Irrespective of how many times these 2 change the values of these two attributes at month end are what are important for reporting.

---------------

My initial idea is to create a main dimension for the consultant which has just the basic attributes which change very slowly and whose changes can be handled in either type 1 or type 2 fashions. I will attach a small look up table to this to find out the column on which the last change was made. This is just for record. Not much reporting is done on this and so the join will not be used regularly.

Then, create a new dimension which will have the following columns:

1)ConsultantID
2)Title
3)Title_Start_Date
4)Title_End_Date
5)Status
6)Status_Start_date
7)Status_End_date
8)Current_Flag

There many be around 100,000 consultants and around 10% many change their Position title and status regularly.

Is this a reasonable design ?

Please have a look at my dimension table in this picture and let me know your thoughts on this.

Feedback is much valued.

Thanks.

If you don't care about history for a subset of attributes, you don't need to track it. Simply treat the change as a type 1. When stuff changes that you do care about, treat as a type 2.

Thanks for the reply.

I get the difference between Type 1 and Type 2 responses to changes but I want to find out if it is a good idea to break up my consultant dimension to separate the hot attributes (of type 2) so that I do not have to touch the passive attributes when I make regular changes. Also, I thought that there will be less duplication in the main consultant table because of the additional rows created by type 2 response.

I can think of two following options:

Option1.
Since you are only interested in moth end picture of the consultant title and status, you may have a monthly periodic snapshot fact table to capture the historical correlations of those fast changing attributes. You then set those attributes as SCD 1 in the main dimension to minimise the SCD impact while still keeping the current values.

Option2.
Have a transaction dimension to timestamp the fast changing attributes, similar to your second table but with only one pair of effective dates. You will have its own SK and an FK to point to the consultant dimension, and it's reasonable snowflaking for transaction dimension.

Option1 is simple and capable of trend analysis on monthly base. Option 2 is more compact and good for point in time analysis, but relying on other fact table for trend analysis.

Thanks for your reply.

I like your Option 2 mainly because it supports like you put it :'point in time analysis'.

Also, this is closer to the design I had in mind but could not decide on.

But, why do you say that I should use only one pair of effective and expiration dates, when there are more than one rapidly changing columns to be tracked in that mini dimension?

I guess one pair of effective dates is kind of design pattern in dimensional modeling to track SCD changes, which allows reasonable data repetition on attributes to make point in time analysis easier.

Now you have extracted the dynamic attributes out of a wide main dimension and put them in another SCD dimension. Potentially you could end up with many attributes and you don't want to add effective date pairs for each attribute and repeat all the dates for one change. That's just not the way how SCD works, as more date range constraints make the query slow and untidy. So I would rather repeat the attribute values, or FKs pointing to the attributes if you want to normalise the transaction dimension.

Can you have 2 fact tables , one transaction and other snapshot factless fact table where you maintain the month end status of a consultant ??

SCD1 (main dimension ) ,SC2(hot attributes dimension), tansaction fact ,factless fact(SCD1 SKey, SC2 SKey,Time)

Yes, you can. You can have the scond fact table by snapshot on the transaction dimension monthly.

Vishy wrote:Can you have 2 fact tables , one transaction and other snapshot factless fact table where you maintain the month end status of a consultant ??

SCD1 (main dimension ) ,SC2(hot attributes dimension), tansaction fact ,factless fact(SCD1 SKey, SC2 SKey,Time)

But why have a factless fact table to maintain month end status of a consultant when you can do the same in the mini dimension?

In fact this is the point I wanted to clarify for myself. Can I maintain history of hot attributes in the mini dimension or should I be creating a fact table?

Can you please explain your design more?

Thanks.

Do you have some measure related with consultant's title change or status change ???

Vishy wrote:Do you have some measure related with consultant's title change or status change ???

I have "attributes" like :

Title_Code
Title_Description
Status_Code
Status_Description

You can choose to call them (Non-Additive) "measures" but for that you will have to place them in a fact table...which brings us back to the first question which you have not answered so far if I may point out.

The question is: Why use a fact table as you suggest to store history for title and status of consultants when you have a mini dimension doing the same job?

Hi 1dwbi ,

We understand that mini dimension has to be connected to the fact to bring out detail that you want.
So you must be connecting it with some fact, which is there altogether for different purpose. Correct me or provide some more details about the fact that you would use with mini dimension.

I am always a big follower of one sentance of Ralph Kimbal ... " Go and stand on the point where business is happening or a transaction is happening that you want to model in dimensional way"

If I go and stand where a consultants status is changing then, I don't see anything else there.This process will also help you incorporate any future requirments as you are modelling a business process.

Also think about reporting out of your design, it should not be complex.