Rapidly Changing Dimension

Hello, I have a slowly changing dimension table with 105 attributes. About 85% of the attributes are type 1 and the remaining 15% are type 2. I have 4 fields that are type 2 that change a lot more frequently than the rest of the type 2 fields. On a side note, it would be helpful for the business users to be able to easily determine when one of these 4 fields do change.

I did a historical load of my dimension table going back to 2001 and if I take out the 4 fields that change rapidly, my dimension table has 6.5 million rows. With the 4 rapidly changing columns, my dimension table has about 13 million rows. I see a significant decrease in the performance on some of my test queries when the table increases from 6.5 million rows to 13 million rows so I'd like to come up with a solution that will keep the size of my dimension table to a minimum.

The 4 fields that rapidly change are not associated with facts so I'm not sure if a mini-dimension would be appropriate.

Here are some more specifics of my situation:

I have a data warehouse with Municipal bond data. I store information like name, state, maturity date, etc. about each bond in a table called DimMunicipalBondSecurity. Municipal bonds are rated by a few organizations. From time to time, these organizations upgrade or downgrade their rating for specific bonds. The business users would like to know when the ratings change and also be able to know what the rating of a bond was at any time.

I haven't found any good articles that I could apply to my situation. Some of the articles I found suggest separating the rapidly changing fields into a mini-dimension and then adding an additional foreign key to the fact table. I don't believe that solution helps me because the rating changes that occur are not associated with a fact.

Any help would be appreciated. Thanks!

One approach is to simply have a type 1 and a type 2 table for the same dimension. The type 1 would have all attributes while the type 2, with the same natural key, would have type 2 attributes, and possibly a few type 1 attributes to make reporting simpler. Fact tables would contain FKs to both.

A junk dimension (Ralph's original term, which I prefer over the more p.c. 'mini-dimension') will work as well, but you lose the association with the original dimension. The latter is only a problem if you need to provide dimension only reports. An advantage is the junk dimension would be much smaller that the type 2.

If you do go with junk, and since you need to do dimensional reporting (i.e. bond rating over time), you could implement a factless fact table associating the type 1 dimension with its bond rating (junk dimension) over time.

Thanks for the reply! I think I understand how I can use a junk dimension now. I believe that the query performance is OK with the type 1 and type 2 fields in the same dimension table as long as I don't include the 4 rapidly changing fields. I will use your suggestion and create a junk dimension out of these 4 fields. I'll also use your suggestion to create a factless fact table and add a row each time the bond rating changes.

Thank you very much for your help!! This is the second time you've saved me from a bad databases design!