Data Model with 6 fact tables

[csaint001]

Hi all,

I'm struggling with setting up a data model.  I have 6 fact tables with employee metrics and would need to filter/display by Employee attribute (dept, city, level etc.).  I have a current Employee Roster with lists all current employees (no dups) with all current attributes.  The issue is that the fact tables might have Employees that are NOT on the current roster or have changed levels, depts etc.  All of the fact tables have duplicate rows for all employees.

The only way I've been able to solve this is with 14 DIM tables listing out all of my attributes then connecting all of them to each of my fact tables.

Is there a more efficient way to set up is model?

[amitchandak · Accepted solution]

Unless looking at the model, I can not say. But If you are in a star schema. And you have separated out fact and dimension and making sure all common fields are in dimensions. Then it is a good design. I am sure you have taken care of SCD behavior of employee details.

[amitchandak · Accepted solution]

Unless looking at the model, I can not say. But If you are in a star schema. And you have separated out fact and dimension and making sure all common fields are in dimensions. Then it is a good design. I am sure you have taken care of SCD behavior of employee details.

[csaint001]

Hi,

@amitchandak 

Ok, thanks.  That's what I thought.  What you mean by "SCD behavior"?

[amitchandak · Accepted solution]

Slowly Changing Dimension.

Means employee's manager and department changes etc. You have also created a current employee fact. Means somewhere you are taking care of that too.