The Sooner Manufacturing Company Is Concerned With Training

1the Sooner Manufacturing Companyis Concerned With The Training Level

The Sooner Manufacturing Company is concerned with the training level of its employees. Legal and corporate directives require that each employee receive training in a number of areas (safety regulations, affirmative action, quality management, etc.) on a periodic basis. Employees have to repeat some courses each year. Managerial employees require different training than hourly employees.

Nancy Leader, the VP for Human Relations has requested a data warehouse to support this. One of the reports she would like looks like the one below: Department: xx Name Manager? Training-Received Date-Trained Hours-Trained xx Y/N xxxxx mmyy nnn. She indicated that she would like to be able to run the report by department; summarize it by month, quarter, or year; or restrict it to any training program. She also would like to be able to identify any employee that had not received necessary training and contact the employee and their supervisor by phone or e-mail.

Design a dimensional Data Mart for this system. Include attributes that you think are necessary.

a) What is the grain of this data mart ?

b) Provide a Data Schema (E-R diagram) including all attributes.

Paper For Above instruction

The goal of this data mart is to support comprehensive reporting on employee training across various dimensions such as time, department, employee characteristics, and training specifics. To achieve this, a well-defined star schema will be designed, capturing all relevant data points necessary for flexible querying, reporting, and contact purposes. This schema design begins with establishing the grain, identifying key entities, and their attributes, leading to an efficient and scalable data model.

1. Grain of the Data Mart

The grain of this data mart is defined at the level of each individual employee’s training record. Specifically, each row in the fact table will represent one instance of an employee receiving a specific training on a particular date, with associated details such as hours trained, whether the training was received, and whether the employee has completed the required training. This granular level ensures that reporting and analysis can be performed across multiple dimensions, including time, employee, department, and training type, without ambiguity or aggregation issues.

2. Dimensions and Facts

Fact Table: Employee_Training_Fact

  • Employee ID (PK, FK)
  • Training Program ID (PK, FK)
  • Date of Training (PK)
  • Hours Trained
  • Training Received (Y/N)

Dimension Tables:

  1. Employee Dimension
  • Employee ID (PK)
  • Name
  • Position (Managerial/Hourly)
  • Department ID (FK)
  • Supervisor ID (FK, nullable)
  • Contact Phone
  • Contact Email
  • Department Dimension
    • Department ID (PK)
    • Department Name
  • Training Program Dimension
    • Training Program ID (PK)
    • Training Name (e.g., Safety Regulations, Quality Management)
    • Description
  • Date Dimension
    • Date (PK)
    • Year
    • Quarter
    • Month
    • Day

    3. Schema Relationships and Attributes

    In the star schema, the Employee_Training_Fact table links to each dimension via foreign keys, representing the relationships between the training instances and their contextual data points. For example, querying by department or time period will involve joining the fact table with the respective dimension tables. The Employee dimension includes contact information for outreach, while the Supervisor ID links to another employee record, allowing contact with supervisors if necessary.

    4. Additional Capabilities

    This schema allows for flexible queries such as:

    • Filtering by department, training program, or employee type.
    • Aggregating training hours or completions by month, quarter, or year.
    • Identifying employees who have not received required training based on training records versus mandated training schedules.
    • Contacting employees and supervisors through contact details stored in the Employee Dimension.

    5. Conclusion

    By defining the grain at the employee-training session level and structuring the schema around key dimensions, this data mart provides a robust foundation for operational reporting and decision support, facilitating compliance monitoring, workforce development, and targeted communication strategies within Sooner Manufacturing.

    References

    Related Assignments