Dimension with a link to another dimension

[Goodie666]

Hello,

I was wondering how people usually deal with dimension attributes that are really other dimensions. For instance, a product with a product group. I need my product group to be "translatable" to other languages so I wanted to treat it as a separate type 1 dimension. But then this means that users have to join from the fact to the product to the product group. And I don't want to go update the fact by putting the product group in each fact row neither... Is this something really wrong or do people end having to live with it? In Kimball's book I don't really see anything about it so I'm wondering how this is handled typically...

Thanks,

Greg

 

[MDXer]

use translation columns. If your dimension is in english and you need to present it in spanish then add the columns to show the attributes needed in spanish. MS SSAS allows a cube to show multiple languages by utilizing translation columns.

 

[Hans63]

That is one way. Another way is to store all descriptions in an other table with a composite key: (text_ID, language). All text fields are stored as an ID.
It creates a form of snowflake but you are very flexible in storing all different languages.

 

[Goodie666]

Hi Hans63,

Yes that is exactly what I am going for. While originally I did not want to have to go with a snowflake schema I think I might have to in some occasions. I can always use views to put a layer of 'abstraction' on top for users who will query it.

thanks,

greg