search

CEDStandards / CEDS-Data-Warehouse

Modeled for longitudinal storage and reporting of P-20W data, the Common Education Data Standards (CEDS) Data Warehouse implements star schema data warehouse normalization techniques for improved query performance.
https://github.com/CEDStandards/CEDS-Data-Warehouse
Apache License 2.0
22 stars 3 forks source link

Enable Identification of Employer of Staff vs. Owner of Staff Work Location #93

Open gilbertcj6 opened 1 month ago

gilbertcj6 commented 1 month ago

This is for capturing needs not currently supported by the CEDS model. Please do not send or share actual data as examples in this issue or in attachments.

Author(s): CJ Gilbert; Kristy Webber

Authoring Organization(s): State of Michigan/CEPI

Email address: gilbertc6@michigan.gov

Use Case Title Enable Identification of Employer of Staff vs. Owner of Staff Work Location

Use Case Description

Element Name: Employer LEA Identifier Element Definition: Stores the LEA identifier of the employing entity

Element Name: Employer IEU Identifier Element Definition: Stores the ISD identifier of the employing entity

Use Case Background The entity that owns the school building is not always the same entity paying the salary of the staff member assigned to that building. Some of our reports call for aggregations based on where people have been assigned to work, and other reports call for aggregations based on who has employed those persons.

Right now, our state stores employment data and assignment data in two separate tables which allows us to differentiate between the district that owns the building and the district paying the salary.

In the CEDS Data Warehouse, there is only a single fact table that houses both employment and assignment data, and that table only has elements for linking to a single district or a single ISD. CEDS does not currently have a way to store separate districts so that we can aggregate numbers separately for these two types of relationships.

Adding these element would allow us to use the (already existing) fields of a) SchoolId, b) LeaId, and c) IeuId in the Data Warehouse to represent a) the school to which a staff member is assigned, b) the LEA which owns that building, and c) the IEU in which that building is located.

And then we’d could use EmployerLEAId and EmployerIEUId to point to the employing entities.

Location of Element in the Domain Entity Schema K12 -> K12 Staff -> Assignment  K12 -> K12 School -> Job K12 -> LEA -> Job

gilbertcj6 commented 1 month ago

Proposed solution to use case 924:

Add EmployerLEA and EmployerIEU Issue_924 Proposed Solution.docx