It's good ideia join 2 tables in 1 dimension

Hi everybody,

i am from Brazil and my source model has one table of company and other of employee.

I am thinking on link this two tables in one dimension.

The company has: name, bank_account, CNPJ(Federal Tax Number in USA), address.
The employee has: name, bank_account, CPF(Social Security Number in USA), address, sex, age

The dimension will be: name, bank account, CPF/CNPJ, type(Company or Employee), sex, age, address

That's right?

Generally speaking, no. What business is this for? How is an employee like a company?

Is private social security, I just wanted to generalize the dimension to simplify the model.

What do you think?

If the actions you are trying to track can be applied to both an employee or a company, then it makes sense.

Ok i understood.

Can i make another question...if i have a table of employee and other of job title, can i link the tables on dim employee?

Just curious, but how is Employee and Company used?

We have a report of employees of some company, they are active on plans of private social security. Some plans has half contribution of company and half of employee. The report shows how much contribution of the company on month and the contribution of employee(some employees got out of plan and has the self contribution option). So i have to create company dim and employee dim.

RafaelR wrote:We have a report of employees of some company, they are active on plans of private social security. Some plans has half contribution of company and half of employee. The report shows how much contribution of the company on month and the contribution of employee(some employees got out of plan and has the self contribution option). So i have to create company dim and employee dim.

Ok, if that is the case, I would assume you have a company contribution amount and an employee contribution amount in the fact table. If that is the case you should also have a company FK and employee FK in the fact. In which case I would not recommend consolidating them into a single table. The end result is more flexibility in reporting and less confusion.

For self employed people, I would create a 'Self Employed' company row and reference that in such cases.

RafaelR wrote:Can i make another question...if i have a table of employee and other of job title, can i link the tables on dim employee?

Given the scenario you stated above, I would keep Job Title as a separate dimension and have a FK off the fact table.  I'm assuming the employee dimension is quite large, so the snowflake you describe would hamper performance significantly.  

From an ETL perspective, it may be useful to carry the job title FK in the employee dimension, but for the sole purpose of making it easier to populate the key into a fact table.  This saves you from having to lookup the job title key whenever you load facts for the employee.  The downside is you need to update the key on the employee whenever the employee's title changes, but this is a minor accommodation.

I am also assuming you are talking about standardized job titles. If it's really just whatever label the company has assigned to the position, it may be simpler to just include them as attributes in the employee dimension. But if these are standardized titles, keep it as a dimension.

If you want to track changes to an employee's title, that is best handled by a factless fact table that relates employee to job title over time.

Thx for help ngalemmo.