Model for storing dimension attributes in multiple languages

We have a traditional dimensional model and are about to add multiple languages to our source systems and need the option of reporting certain dimensional attributes in either the local language or English. Has anyone developed a flexible model for this that would allow adding multiple language versions of attributes without adding columns to the dimension table directly?

2 options I can think of are:

1) An abstracted translation dimension that contains the attribute name, language, and the original and translated version of the attribute content. The granularity of this dimension is attribute name, language, and original value of the attribute. With this option any attribute that becomes multi-language in the source system after initial implmentation requires only a new row in this table.

2a) A translated version of the original dimension. This table would contain the dimension surrogate key, natural key, language, and translated version of the attributes requiring translation. With this option any attribute that becomes multi-language in the source system after initial implmentation requires a new column in the translated table.

2b) Similar to 2a except it contains all the columns of the original dimension.

Thanks for any ideas, and especially for examples of models that are in production and working well.

I would not go with option 1. While it is a nice, simple, flexible model to construct and maintain on the back-end, it is a horrible model for querying. Pretty much any commercial BI tool would have a very difficult time working with such a model. Queries would be very complex, requiring an alias of the table for every descriptive attribute, plus you have so somehow hard-code column name filters in the BI meta-layer so it gets the right row.

Some variation of option 2 is the way to go. The option you mentioned, keep the existing dimension as-is and add a sub-dimension table with the same key plus a language code to contain translated descriptive attributes, is probably the most flexible and least disruptive (current applications will continue to work). You can carry this futher by implementing language specific schema in the database, which contain views for a specific local language, and users would be directed to their perferred language based on their user ID. Or, most BI tools support user specific content filters allowing you to transparently present the peferred language based on user ID. Also, since you keep the original dimension as is, there is no need to store the English version in the sub-table.

From a maintenance standpoint, you might as well put all descriptive attributes in the sub-table. If there is no translation at this time, leave it blank or populate it with the value in the base dimension table.