Customer Dimension with Unlimited Metadata

View previous topic View next topic Go down

Customer Dimension with Unlimited Metadata

Post  khendricks on Tue Feb 01, 2011 2:04 pm

I’m currently attempting to define a customer dimension that has a fixed set of fields as well as an unlimited number of user-defined fields. The user-defined fields can be of various data types defined by the user. For example, a user may define a field called ‘Favorite Theme Park’ as a ‘string’ data type, or ‘Owns Time Share’ as a ‘boolean’. My first thought is to separate the fixed ones into their own dimension. However, I’m not sure what might be the best way to model the metadata representing the user-defined fields. Would this be the case where a snowflake may be the only resort? My thought would be:

SomeFactTable
customerKey
...fact attributes

CustomerDimension (for fixed fields)
customerKey
prefix
firstName
middleName
lastName
…other fixed attributes

CustomerUDFDimension (for user-defined fields)
customerUDFKey
customerKey
fieldname
dataType
booleanValue
stringValue
intValue
doubleValue
dateValue

Is there a more optimized way to model this without resorting to putting a fixed constraint on the number of fields? Thanks for any suggestions.

-Kirk

khendricks

Posts : 2
Join date : 2011-02-01

View user profile

Back to top Go down

Re: Customer Dimension with Unlimited Metadata

Post  ngalemmo on Tue Feb 01, 2011 3:02 pm

If you are truely dealing with no restrictions, the only viable approach is to store the data as attribute/value pairs in a generic model. This is outside dimensional modeling.

Such an approach requires significant interface work to make it useful to an end user. Also, on a traditional database platform, queries against such a structure do not perform very well. If you are dealing with large amounts of data in a structure like this, MPP type platforms tend to handle them better.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Customer Dimension with Unlimited Metadata

Post  khendricks on Tue Feb 01, 2011 3:49 pm

Yes, that was my thinking too...unfortunately. Being fairly new to dimensional modeling, I didn't want to overlook something. We may also look at actually defining a dimension for these user-defined fields as once they are set up, they are very rarely changed. Also, once they've populated data into them, they cannot change the metadata about that field or drop the field. So, what that would mean is we would only need to add to that dimension if we created a new field. That would be a bit of maintenance to spin through a lot of customer records to update the UDF dimension, but it would be very infrequent. And, I could then define a true dimension with their respective datatypes. So it would look something like this possibly:

SomeFactTable
customerKey
customerUDFKey
...fact attributes

CustomerDimension (for fixed fields)
customerKey
prefix
firstName
middleName
lastName
…other fixed attributes

CustomerUDFDimension (for user-defined fields)
customerUDFKey
favoriteThemePark
ownsTimeShare

If the user defined a new user-definable field for the Customer called timeShareCountry, I would need to spin through each CustomerUDFDimension and add the field so it would look like this:
CustomerUDFDimension (for user-defined fields)
customerUDFKey
favoriteThemePark
ownsTimeShare
timeShareCountry

Would that seem reasonable? Again, these may be sparsely populated, so keeping them out of the CustomerDimension would be desirable.

-Kirk

khendricks

Posts : 2
Join date : 2011-02-01

View user profile

Back to top Go down

Re: Customer Dimension with Unlimited Metadata

Post  ngalemmo on Tue Feb 01, 2011 4:57 pm

If you are willing to put some limitations, such as a workable maximum number of fields, treating everything as strings, etc... you can significantly simplify things. It then becomes a matter of users labeling the content of some fixed number of buckets in the dimension table. You could then define views for each user that labels the columns in a manner meaningful to them. Such views could be generated automatically.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Customer Dimension with Unlimited Metadata

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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