Customer Dimension with Unlimited Metadata
2 posters
Page 1 of 1
Customer Dimension with Unlimited Metadata
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
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
Re: Customer Dimension with Unlimited Metadata
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.
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.
Re: Customer Dimension with Unlimited Metadata
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
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
Re: Customer Dimension with Unlimited Metadata
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.
Similar topics
» Merging customer data from disparate sources to create a master customer dimension
» De-normalizing Customer Information to create a Customer Dimension
» Customer Ship to Vs Customer Dimension
» How to handle a Type I or II dimension with a snowflaked customer sub dimension (kimball book page 337, 338)
» bridge table and junk dimension on customer dimension (bank/credit union)
» De-normalizing Customer Information to create a Customer Dimension
» Customer Ship to Vs Customer Dimension
» How to handle a Type I or II dimension with a snowflaked customer sub dimension (kimball book page 337, 338)
» bridge table and junk dimension on customer dimension (bank/credit union)
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|