Employee Dim - Multilanguage and Attributes (we need all possible value)

Hi,

I am triing to model an Employee Transaction Dimension that's look like the one in the Data warehouse toolkit, with 200-300 attributes.

I will try to explain the first problem :
Lets say we have an attribute Job in the Employee Transaction Dimension that can have 100 differents values. But in the dimension all the employees only use 60 of them. It's fine if we only want to see the job that are connected to our employees. But if we want to know, for each 100 job, how many employee have those job, we can't, because with this dimension, we can only see the 60 different job. So we cannot see the other 40 job with 0 employee.
One solution could be to put these attribute in an outrigger/mini-dimension. But we have like 80 or more attributes in the same situation (meaning we want to report on every possible value, not only the value that are connected to the employees).
So it could result in too many dimension (even if we gather some attributes in multiple mini-dimension).
Is that the only solution available for this kind of situation ?

Second problem:
With the same example: the attribute Job.
We want to support multi-language, meaning, that if an employee is a DBA, we want to show in the report :
"Database Administrator" if we are connected in the application in english
"Administrateur de base de donnée" if we are connected in the application in french
"Адміністратар баз даных" if we are connected in the application in bellarusion
and so on (lets say 10 différent languages)
We came up with two solution, but i hope there are better solution available :
- having 10 différents column for each attributes that support multi-language. So if we have 5 attributes in the employee dimension then it will results 50 columns for just five attributes ... not a very good solution imo because too-too many column; even for the reporting : you have to use 10 différents views
for each language.
- using table that contains 1 row per language/job. So we can join with the right language to have the value ?

those two problem concern about the same 80 attributes. And each attribute can have 5 to 1000 différents values.

Thanks.

For your second problem, use either multiple rows, with a language code as part of the PK, or multiple tables (with the same PK), one for each language. Creating multiple attributes by language would be a real mess in terms of querying.

For the first problem, could you not use an outer join to the fact?

Thanks for the reply

For your second problem, use either multiple rows, with a language code as part of the PK, or multiple tables (with the same PK), one for each language. Creating multiple attributes by language would be a real mess in terms of querying.

Having multiple table for each language or multiple attributes in one table : it seems equally in terms of querying:
SELECT JOB_NAME_EN FROM DIM_JOB
or
SELECT JOB_NAME FROM DIM_JOB_EN
None of those solutions (including multiple rows with a language code as part of the PK) seems perfect, but i guess i should pick one of them.

For the first problem, could you not use an outer join to the fact?

In the case we choose a dimension for each attribute (or minidimension), yes we would do an outer join.