Seperate Customer Dimension

[Narasig]

Hi
I am working for insurance client,
they had already Contract Dimension called as Policy Dimension. The contract Id (PK) is referenced in Customer Dimension.SInce there is need for more attributes in contract dimension from EDW stand point of view, we have to design a new dimension Contract.
my question is that we have to reference Customer details like (first name ,lastname and so on ) from the contract.
Do we have to reference customer key in Contract Dimension or any other ways to get the customer details from contract.
if any one has implemented like this, what are the advantages and issues pls. let me know.

Thanks in advance,
Narasimha

 

[RiverGuy]

It's hard to give advice without knowing more details -- does each Customer have at most one Contract, or may there be more? Furthermore, is a Contract able to be moved from one Customer to another? Is a Contract able to be attributed to more than one Customer or to just one?

 

[Narasig]

Thanks for your reply
contract to customer is at most one.
Furthermore, is a Contract able to be moved from one Customer to another? -- this is not moved.

Is a Contract able to be attributed to more than one Customer or to just one? . Its one customer only.

In this scenario, can we reference customerID in the contract table?..does it cause any issues going forward

Thanks
Narasimha.

 

[RiverGuy]

If these are 1:1, then they really could be part of the same dimension. However, Customer vs. Contract to me just seems like a separate entity. You could do a couple of things:

1. Make one dimension that has all of the Customer attributes and all of the Contract attributes in the same table.

-or-

2. Put a foreign key to Customer in the Contract table and store just the ContractKey in the Fact table. I wouldn't recommend also storing the CusotmerKey in the Fact table in this case.

 

[Hans63]

Some questions that come to mind are:
Is this a regular (frequent) situation? (using contract and customer data)?
Is there no fact you are reporting on this? And if you take factless fact into account?

 

[Narasig]

Thanks for your reply,
after looking at the data, I found that
Customer to contract relation is one to many. In this case,
my approach is that,
I have ContractMaster Dimension and ContractDetail dimension tables right now. Since for each contract we would like to see the individual information of the customer. If we reference customerkey in ContractMaster Dim table,is there any issues?
As I mentioned above, customer to contract is one to many,
for anyfield related to contract changes in the master table, there will be a record in Contract master and the corresponding key should be referenced in Contract Detail table.
by doing this way, lot of records are going to add in master table. Is this correct approach. Pls. suggest.

Thanks,
Narasimha.

 

[johnherman]

The solution on which you are converging (Customer Foreign Key in Contract Master), seems to be OK for both Type 1 and Type 2 customers - that's a decision you need to make based on business requirements.

You could perhaps get additional performance benefits by also placing the Customer FK in the Contract Details. This is a technique often used with Order and Invoice Details. The decision should be based on whether there are sufficient needs to access Contract Details when the only need to join to the Contract Master is to pick up the Customer FK. If the Contract Master is continually being used for other reasons (Expiration Date, etc), then there is no benefit to denormalizing the Customer FK into the Contract Detail.


As far as denormalizing some or all of the customer info in the contract, I could see where the denormalization would be beneficial for performance reasons. But you would also be duplicating data which results in more storage and more complicated ETL to maintain the duplicate items. Again, it would depend on the size (in rows) and usage of the Contract table(s).

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright