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

User fields design issue

4 posters

Go down

User fields design issue Empty User fields design issue

Post Tue Apr 03, 2012 4:13 pm

We have a huge data in DWH with each member having around 50 attributes assigned to it. The number and the attributes change with each member participating in the type of program. So, the number of distinct attributes in the DWH reached 2500. How to model this data to be able to use retrieve and use all attributes as dimensions?

If I store this data in fact as memberid+attributeValue+attributedimid, it becomes a 3 billion records table.

examples of attributes are language, contact_during_day, transfernumber, doctor_line etc.

Posts : 3
Join date : 2012-04-03

Back to top Go down

User fields design issue Empty Re: User fields design issue

Post  ngalemmo Tue Apr 03, 2012 4:44 pm

I've dealt with this in the past few years working with retailers and the financial industries. While a name/value pair structure is the most flexible, querying it is a pain. You would need to implement a publication process that creates flattened structures of the desired attributes. It may work for you or it may not, and you still need to materialize a flat image (or a view, if your hardware can handle it) to allow direct user queries.

You need to consider use cases and where the same attributes exist in different situations. I would imagine a flat image of all attributes would be extremely sparse, besides being unusable (or exceed table limits of your DBMS). You most likely should consider a sub-type cluster, with common attributes in the main dimension and additional dimension tables for groups of attributes. These sub-type dimensions would carry the same PK as the main dimension so that they can be joined directly to the fact.

On the other hand, if they are truly user defined attributes, you can just store them as 50 varchar values in a row and develop views for each user group with appropriate column names for the attributes. Again, it depends on your use case.

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

Back to top Go down

User fields design issue Empty Re: User fields design issue

Post  BoxesAndLines Wed Apr 04, 2012 9:45 am

What ngalemmo calls a sub-type dimension is really just a mini dimension. Store the attributes that vary by type in a mini dimension. You will have a mini dimension for each type. The primary key of the mini dimension is the same as the primary key of the primary dimension. I would even look at creating junk mini dimensions if I have too many types.

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

User fields design issue Empty Re: User fields design issue

Post  Vishy Thu Apr 05, 2012 4:53 am

In other words the attributes which changes a lot throw them into a separate table as you will still need to connect this child and its parent dimension have same primary key column in both.



Posts : 75
Join date : 2012-02-28
Location : India

Back to top Go down

User fields design issue Empty Re: User fields design issue

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