Dimensional Attributes

[Arvind]

Hello,

I have a requirement where a dimension can have potentially hundreds of attributes that can change over a period of time. The end reporting requirement is that we should be able to run the report where we select a combination of attributes.

An example may help. In the Insurance company design, I have a Policy Dimension and then an Insured Item Dimension which are linked in a fact table. Now the Insured Item Dimension can have several attributes. For example, if the insured item is a house, a burglar alarm will be
an attribute.

My dimensions would typically look like this:

Insured_Item_Dimension (type 1 dimension)
Item_sk (surrogate key)
Item_address
Item_Built_Year
Item_
:
:
:

Insured_Item_Coverage (Type 2 dimension)
Item_sk
Eff_dt
Exp_dt
Burglar_Alarm
Sprinkler
Roof_Type
Construction_type
:
:
:


This design does not appeal to me for two reasons. First, the # of attributes continue to grow almost every month. Every time a new attibute is added, we have to add a column to this table and prefill for all the history with Nulls. Second reason, the source system maintains at a row level. Reading that row and determine if that attibute present or not for the location and update the column look little bit complicated to me and getting thousands of locations every day with their attributes, I am afraid this might slow down the ETL process.

The other option that I have, since it is represented as row level in source system, how about
I design it this way:

Insured_Item_Coverage
Item_sk
Attribute_SK (pointing to another dimension if it is burglar alarm, sprinkler etc)
Eff_Dt
Exp_Dt

This makes my ETL process a breeze. But in the BI application where users generally expect the attributes as an Yes or No column, this structure is somewhat complex.

I bet there is a technique out there that uses the best of both worlds. I just dont know what it is. Can someone help?

I appreciate your time and do ask me if the example is not clear.

Thanks
Arvind.

[Colin Davies]

I hate to say this , but you might need a bit more abstraction in your data model. Are you able to classify the types of attributes that keep getting added? If they are truly all over the place, then you may have to live with ugly monster dimensions. However, I suspect that the attributes can be categorized and grouped by type. For example, a burglar alarm might be lumped into a security "sub-dimension". Yes, we are drifting into snowflake territory here, but that can be managed as a 3NF-ish set of base tables, mercifully hidden behind a star schema of views. That's all I can infer from what you have posted, but it all looks very familiar.

For example, you have

Burglar_Alarm
Sprinkler
Roof_Type
Construction_type

Could these be classified as, say "rate modification characteristics"? Maybe a bridge table is called for?