User Defined Dimension Attributes

I have a business requirement that is somewhat stretching my modelling skills. The application is a highly sensitive one so I’ll describe the problem using a Sales Datamart template.

There are 7 plus fact tables, 1 is updated monthly, the rest quarterly. Revised monthly data can be supplied several times in a month and added to the warehouse as a separate revision.

There are around 15 dimensions in total.

The 2 key dimensions:
Product - 3.5M rows – 20 attributes – SCD
Customer – 350K rows – 10 attributes – SCD

The problematic business requirement is that the client wishes to have the ability to add their own user-defined attributes for use in analysis. They wish to have the flexibility to be able to add new attributes at will, ideally with no limit on how many they can have.
This would be manageable if the user-defined attributes were to be simply added to the existing dimensions (mainly the two mentioned above). However, the requirement is that user-defined attribute values need to vary across time and also across revisions. For example:

Month Product User Attribute 1 User Attribute 2
-------- ------------ ---------------------- --------------------
Jan Widgets Red Approved
Feb Widgets Green Under review

Not all products would need to be flagged in this way and it would appear that a fact-less fact table would model this, but the implications of an ever-growing list of attributes makes this a maintenance nightmare. Another option is to make the attributes degenerate dimensions but this would lead to an extremely fat fact table leading to performance problems.
The situation is complicated further by the fact that the data is to be loaded into OLAP cubes (SSAS) for ad-hoc user analysis; cubes normally need to be re-built when there are structural changes in the dimensions and this is undesirable when the fact tables are large.

If anyone has encountered this type of problem before I’d be interested to learn how it was overcome.

Many thanks

Yes, I have dealt with it in the past... usually with a bit more structure than you describe.

Techically, the data structures to support this type of capability are fairly simple: a FK to the dimension(s), a FK to a list of user attribute name and an attribute value. The user can assign a value to any user attribute related to any dimension. Problems begin when you try to implement it...

Who, and how is this going to be maintained? And, as you point out, a cube is not exactly geared toward this type of data. Even simple reporting becomes complicated when you attempt to include more than one of these attributes in a query.

Your best bet would be to go back to the business and help define what they really want to do. Listen carefully and begin to build a structure around it.