Clients with different attributes that are not all the same
2 posters
Page 1 of 1
Clients with different attributes that are not all the same
I have been given the project of building a master client database. I work in a financial environment where we client/product/account information. For some types of client they have specific information related just to them, unique identifier, name, but they differ in the other information that relates to them. For example, Client A may have a 4 character unique identifer with no product information, Client B may have a 9 digit unique identifier with the possibilty of product information based upon a platform. Finally Client C has a 4 character unique identifier with production information. The product information is based upon a System/Prin/Agent (4 digits each) Each client represents a different platform, of which a client can be on multiple platforms.
What would be the best method for this situation? One dimension table could have lots of null values for all of this information. The clients do have some attributes in common such as name, address, phone, etc. They just have different ways of identifying themselves. Should I split the clients out into their own client table with a common key to a main client table. Dimension with a snowflake?
.
What would be the best method for this situation? One dimension table could have lots of null values for all of this information. The clients do have some attributes in common such as name, address, phone, etc. They just have different ways of identifying themselves. Should I split the clients out into their own client table with a common key to a main client table. Dimension with a snowflake?
.
Brandon- Posts : 3
Join date : 2011-10-26
Re: Clients with different attributes that are not all the same
If you are trying to do this in a dimensional model, I would do the following:
Represent a client with a primary client table with the common attributes and secondary type specific tables for attributes coming from particular client systems... in ER terms: a sub-type cluster. All tables would use the same client PK and there would only be rows for clients that are of that type.
Note that this is NOT a snowflake. The main and sub-type tables have the same PK. The relationship is not between them but rather between the fact and the table. If you have a query that filters on an attribute unique to a particular sub-type, it will only look at measures for dimensions of that sub-type (unless you do an outer join from the fact to the sub-type table).
The client/product relationship could be implemented as a bridge between client associated with the fact and the product dimension. Or if this information has wider user, implement as its own fact table.
Represent a client with a primary client table with the common attributes and secondary type specific tables for attributes coming from particular client systems... in ER terms: a sub-type cluster. All tables would use the same client PK and there would only be rows for clients that are of that type.
Note that this is NOT a snowflake. The main and sub-type tables have the same PK. The relationship is not between them but rather between the fact and the table. If you have a query that filters on an attribute unique to a particular sub-type, it will only look at measures for dimensions of that sub-type (unless you do an outer join from the fact to the sub-type table).
The client/product relationship could be implemented as a bridge between client associated with the fact and the product dimension. Or if this information has wider user, implement as its own fact table.
Re: Clients with different attributes that are not all the same
Thank you. I may still have some questions if that is okay.
Brandon- Posts : 3
Join date : 2011-10-26
Similar topics
» Clients with different attributes that are not all the same.
» dimension table design question for around 100 attributes and higher level calculated attributes
» Clients, Roles and Rates
» Logical Data Model - Clients
» Dimension Attributes and Fact attributes storing same data in multiple data marts??
» dimension table design question for around 100 attributes and higher level calculated attributes
» Clients, Roles and Rates
» Logical Data Model - Clients
» Dimension Attributes and Fact attributes storing same data in multiple data marts??
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum