Represent Client Information Dimensionally

Hi All,

I am facing some problem in representing Client data dimensionally. The Client subject area in the source system has many associated data items and the relationship is as below:-

Client (One) --> Phone Details (Many) (Contains FK to Client Entity)
Client (One) --> Email Details (Many) (Contains FK to Client Entity)
Client (One) --> Empoyment Details (Many) (Contains FK to Client Entity)
Client (One) --> Financial Account (Many) (Contains FK to Client Entity)
Client (One) --> Company Details (Many) (Contains FK to Client Entity)

Each of the above is a separate entity (i.e. Phone Details , Email Details etc) and all are SCD2 in the source. When I say SCD2 I don't mean that the Source is a dimensional Data Model.

Now for me, Client is just one of the dimensions that define a transaction on a policy. Currently I can't think of any other option apart from Snowflaking the Client dimension for all the above relationships. Would that be right approach. Please let me know if there is a better approach. New to Dimensional Modelling.

Thanks & Regards
Rohan

rohanf wrote:Hi All,

I am facing some problem in representing Client data dimensionally. The Client subject area in the source system has many associated data items and the relationship is as below:-

Client (One) --> Phone Details (Many) (Contains FK to Client Entity)
Client (One) --> Email Details (Many) (Contains FK to Client Entity)
Client (One) --> Empoyment Details (Many) (Contains FK to Client Entity)
Client (One) --> Financial Account (Many) (Contains FK to Client Entity)
Client (One) --> Company Details (Many) (Contains FK to Client Entity)

Each of the above is a separate entity (i.e. Phone Details , Email Details etc) and all are SCD2 in the source. When I say SCD2 I don't mean that the Source is a dimensional Data Model.

Now for me, Client is just one of the dimensions that define a transaction on a policy. Currently I can't think of any other option apart from Snowflaking the Client dimension for all the above relationships. Would that be right approach. Please let me know if there is a better approach. New to Dimensional Modelling.

Thanks & Regards
Rohan

Right approach is to have a one client dim with above mentioned info as columns/attributes to client dim. You can have SCD2 on above columns if your business user wants to keep history on them. You are seeing many values of that info because among those many only 1 is current value... except for the financial account (client can have multiple open accounts).

If you want to load historical data into the dim and that dim is SDC2, then set your client dim start date and end date based on dates (actual dates or row insert/maintained date) available from the source system. OR you can ask your business user which relationship dates they value more and want to see as dim start and end date for historical data load.

Thanks for the reply. I was under the impression that client can have multiple contact details (Phone & Email) active at a time. Also under rare circumstance may have more than one employment. But I agree it only makes sense to have one email and a finite number or phone details (Work, Home, Mobile). I would post this question to the source system team for further clarification.

Just an associated question:-
Assume we have a change only in the client details (Assuming one client dim having phone, email data) and there is no change to the fact transaction or no new transaction happened corresponding to the change in personal information.
Would we still have to copy the last Transaction in the fact and associate that to this latest Client dim record?
I assume yes and with all other dim not changing at all, but would that not render an additive measure as non additive across time.

I might be missing a point here, would really appreciate your help!

Regards
Rohan

rohanf wrote:Thanks for the reply. I was under the impression that client can have multiple contact details (Phone & Email) active at a time. Also under rare circumstance may have more than one employment. But I agree it only makes sense to have one email and a finite number or phone details (Work, Home, Mobile). I would post this question to the source system team for further clarification.

Just an associated question:-
Assume we have a change only in the client details (Assuming one client dim having phone, email data) and there is no change to the fact transaction or no new transaction happened corresponding to the change in personal information.
Would we still have to copy the last Transaction in the fact and associate that to this latest Client dim record?
I assume yes and with all other dim not changing at all, but would that not render an additive measure as non additive across time.

I might be missing a point here, would really appreciate your help!

Regards
Rohan

De-Normalize your client dim to store contact info i-e have separate columns for Work, Home, and Mobile numbers. You can have one column for email where you store their primary email address. Also have another column to store prefer method to contact info. That column can have value as Work, Home, Mobile, or email.

As far as your second questions, it depends on the fact grain and what business users want to see (ask your business users what needs to happen). For example, If I have a credit card and I do not have any outstanding balance and no payments to make but if I change my mailing address, then business users want to see my current location regardless to any transaction. In that case, all of fact measures would be zero.

Thanks for your help! I am dealing with Insurance so the premium to be paid will never be Zero (Unless the policy is Lapsed/cancelled). Just want to know how will we handle client changes in this setup.

Is it fine if we generate a key that may behave as a Natural key (i.e. unique for a client across its history and same for all changes to the client). And have this key as Fk in the fact rather than the Dimension primary surrogate key?

Regards
Rohan