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

Different Attributes for each Customer

3 posters

Go down

Different Attributes for each Customer Empty Different Attributes for each Customer

Post  adventr Mon Feb 06, 2012 11:47 am

Each customer in my staging database has the ability to have their own set of custom attributes for a student that they want to be able to report on and have aggregates for. Each customer has 10 - 20 custom attributes. From a data warehousing perspective, does it make more sense to have my student dimension have 20 generic fields that I put the values of the custom attributes or have a separate dimension for the custom attributes. I would think the first would perform much better but the label for each of the generic fields would have to be custom per customer (which I don't know how to do from a metadata perspective).

Any help would be appreciated.

Thanks.

adventr

Posts : 3
Join date : 2012-02-06

Back to top Go down

Different Attributes for each Customer Empty Re: Different Attributes for each Customer

Post  ykud Mon Feb 06, 2012 11:27 pm

I would go on to two tables:
1) Attribute description by customer — just telling you that ATTR_7 is Sex for Customer 28 and Family name for Customer 29
|CUSTOMER|ATTRIBUTE_COLUMN|ATTRIBUTE_DESCRIPTION|
2) Custom attributes for student
|Student_id|ATTR_1|ATTR_2|.....

You might have problems arising from having non-uniform datatypes you'll have to store as varchar (like 'Age' or 'Date of birth').
As for labeling — you can 'rewrite' column names per customer basis with some fancy SQL (for example a stored procedure that accepts customer_id as a parameter).
ykud
ykud

Posts : 12
Join date : 2012-01-16

http://ykud.com

Back to top Go down

Different Attributes for each Customer Empty Re: Different Attributes for each Customer

Post  ngalemmo Tue Feb 07, 2012 2:29 pm

There are a variety of strategies to handle storing such data in a generic form, however, none are particularly well suited for querying the data in an ad-hoc environment.

To create structures that can be queried in a SQL environment you need to eliminate the variable nature of the data. If you have a bunch of user definable fields, you need to put restrictions on how they can be used. For any given customer, they can only use a field one way. On the presentation end you would have to define a view (either in database or in the BI tool's metadata) for that customer that labels the columns appropriately so that they make sense to them. This presentation definition could also include column expressions to convert formats (char to date, for example). Underlying this is a dimension table with a bunch of character columns to hold the data.

An alternative is to store the data in a generic form (name/value pairs for example) and develop a front-end that understands the structure and allows users to query the data. This would not be a trivial undertaking depending on how flexible you want such an interface to be.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Different Attributes for each Customer Empty Re: Different Attributes for each Customer

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