This is the first time I am designing a data warehouse schema, I tried going through some of the blogs and chatgpt etc., but unfortuantely, I am more confused than ever. I want to design a data warehouse schema for the below fields. I was able to figure out some fact columns and some dimensions, but based on the data, I know that the designation, location, function, sub-function, pod, grade, squad, reporting manager info, and compensation will have historical information, to know where the employee was on a given point in time, or what compensation the employee was getting, who was the employees reporting manager in the past year, etc.,
I feel that adding all these fields into facts isn't the right way of doing it, and I can have those as a SCD Type 2 dimensions, but some of these are not dimensions but a fact.
So I am not sure how to take this further, any help would be great.
- Emp ID - String
- Name - String
- M/F (Gender) - String
- Status - String
- DOJ (Date of Joining) - Date - History needed
- Last Date - Date - History needed
- Designation - String - History needed
- Location - String - History needed
- Function - String - History needed
- Sub-function - String - History needed
- Pod - String - History needed
- Grade - String - History needed
- Squad - String - History needed
- Employee Email Id - String - History needed
- Reporting Manager E code - String - History needed
- Reporting Manager Name - String - History needed
- Reporting Manager 1 Email id - String - History needed
- Cost Center - Number - History needed
- Fixed (Salary Component) - Number - History needed
- Monthly CTC (Cost to Company) - Number - History needed
- Variable (Salary Component) - Number - History needed
- Retention Bonus - Number - History needed
- Total Annual CTC - Number - History needed
- Total Monthly CTC - Number - History needed
- Leave Encashment Provision - Number - History needed
- City of Origin - String
- State of Origin - String