Multi Language hybrid approach

Hi,

I'm just embarking on moving my small DW model into a multi language environment and am trying to find the best approach for some of my aggregate tables...

For example, an aggregate might look like this -

dateid (fk to date dim)
customerid (fk to customer dim)
itemtype (effectively a degenerate dimension)
sales (measure)

in the base fact table itemtype is an attribute of an item dim which would be translatable for example 'book', 'laptop' ...etc
For my dimension tables i am creating alternate language versions i.e datedimFR, datedimDE ...etc.

but for the aggregate i see some options -

1. add additional fields to the aggregate (itemtypeFR, itemtypeDE ...etc) - manageable as i will only have 3, maybe 4 additional languages to consider, but inconsistent with my approach to multi language dimensions (i.e replicating per language)
2. create multiple versions of the aggregate table as per my dimension approach - although this is just going to unnecessarily duplicate potentially large tables
3. turn itemtype into a shrunken dimension and treat it as per my other multi language dimensions - seems like overkill for a single field.


I am hedging towards 1 as, although i dont really like mixing approaches it seems like the most workable solution.

Any advice or possible alternative approaches would be appreciated.



Os.

Option 1 is problematic. It bloats the fact table, requires schema changes to add languages, and it strays from the pattern you have already established. Use option 3.