Different Attributes for each Customer

View previous topic View next topic Go down

Different Attributes for each Customer

Post  adventr on 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

View user profile

Back to top Go down

Re: Different Attributes for each Customer

Post  ykud on 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).
avatar
ykud

Posts : 12
Join date : 2012-01-16

View user profile http://ykud.com

Back to top Go down

Re: Different Attributes for each Customer

Post  ngalemmo on 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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Different Attributes for each Customer

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