It's good ideia join 2 tables in 1 dimension

View previous topic View next topic Go down

It's good ideia join 2 tables in 1 dimension

Post  RafaelR on Wed Feb 19, 2014 1:08 pm

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?

RafaelR

Posts : 10
Join date : 2013-11-20

View user profile

Back to top Go down

Re: It's good ideia join 2 tables in 1 dimension

Post  ngalemmo on Wed Feb 19, 2014 1:29 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: It's good ideia join 2 tables in 1 dimension

Post  RafaelR on Wed Feb 19, 2014 2:23 pm

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

What do you think?

RafaelR

Posts : 10
Join date : 2013-11-20

View user profile

Back to top Go down

Re: It's good ideia join 2 tables in 1 dimension

Post  ngalemmo on Wed Feb 19, 2014 3:02 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: It's good ideia join 2 tables in 1 dimension

Post  RafaelR on Wed Feb 19, 2014 3:43 pm

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?

RafaelR

Posts : 10
Join date : 2013-11-20

View user profile

Back to top Go down

Re: It's good ideia join 2 tables in 1 dimension

Post  Jeff Smith on Wed Feb 19, 2014 4:51 pm

Just curious, but how is Employee and Company used?

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: It's good ideia join 2 tables in 1 dimension

Post  RafaelR on Wed Feb 19, 2014 5:15 pm

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

Posts : 10
Join date : 2013-11-20

View user profile

Back to top Go down

Re: It's good ideia join 2 tables in 1 dimension

Post  ngalemmo on Wed Feb 19, 2014 6:25 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: It's good ideia join 2 tables in 1 dimension

Post  ngalemmo on Wed Feb 19, 2014 6:34 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: It's good ideia join 2 tables in 1 dimension

Post  RafaelR on Tue Feb 25, 2014 8:04 am

Thx for help ngalemmo.

RafaelR

Posts : 10
Join date : 2013-11-20

View user profile

Back to top Go down

Re: It's good ideia join 2 tables in 1 dimension

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