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

View previous topic View next topic Go down

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

Post  ATran on Thu Mar 25, 2010 11:37 pm

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.

ATran

Posts : 11
Join date : 2010-03-25

View user profile

Back to top Go down

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

Post  ngalemmo on Fri Mar 26, 2010 11:59 am

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?
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

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

Post  ATran on Fri Mar 26, 2010 1:54 pm

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.

ATran

Posts : 11
Join date : 2010-03-25

View user profile

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum