Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Clients with different attributes that are not all the same

2 posters

Go down

Clients with different attributes that are not all the same Empty Clients with different attributes that are not all the same

Post  Brandon Wed Oct 26, 2011 6:34 pm

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?
.

Brandon

Posts : 3
Join date : 2011-10-26

Back to top Go down

Clients with different attributes that are not all the same Empty Re: Clients with different attributes that are not all the same

Post  ngalemmo Wed Oct 26, 2011 9:13 pm

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.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Clients with different attributes that are not all the same Empty Re: Clients with different attributes that are not all the same

Post  Brandon Thu Oct 27, 2011 11:07 am

Thank you. I may still have some questions if that is okay.

Brandon

Posts : 3
Join date : 2011-10-26

Back to top Go down

Clients with different attributes that are not all the same Empty Re: Clients with different attributes that are not all the same

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum