Job ID: OR-11126-25 (912990716)
Hybrid/Local Data Warehouse Architect/EA with Data Modeler, Dimensional modeling, Star schema, ETL, SQL, Visio/Erwin, data governance and healthcare/human services experience
Location: Salem OR (OR DHS / OHA)
Duration: 12 months
Skills:
Skills Database Data Analysis Advanced (7-9 Years) Yes
Skills Database Data Modeling Advanced (7-9 Years) Yes
Skills Database Data Warehousing Proficient (4-6 Years) Yes
Skills Languages SQL Advanced (7-9 Years) Yes
Skills Others Visio Proficient (4-6 Years) Yes
Job Description:
The Office of Information Services (OIS) is a shared service provider for the Oregon Health Authority (OHA) and Oregon Department of Human Services (ODHS). OIS provides the technology systems and services that support more than 18,000 OHA and ODHS agency staff at local offices and facilities around the state. These systems and services help the two agencies determine client service program eligibility; provide medical, housing, food, and job assistance; provide addiction, mental health, vocational and rehabilitative services; protect children, seniors and people with physical and/or developmental disabilities; process claims and benefits; manage provider licensing and state hospital facilities; and promote and protect the state public health.
The Office of Reporting, Research, Analytics and Implementation (ORRAI) seeks an experienced Enterprise Architect IV Conceptual Data Modeler with expertise in documenting and developing high-level conceptual models and translating them into dimensional models (star schemas) for complex human services systems. This position will support the Aging and People with Disabilities (APD) program’s Centralized Assessment and Monitoring (CAM) initiative, working collaboratively with ORRAI’s Enterprise Data Warehouse team, Database Engineers (DBEs), Database Administrators (DBAs), and existing CAM/CALMS contractors.
The contractor will be responsible for leading the development of a comprehensive conceptual data model for the CAM system, then using that model to design dimensional data structures (star schemas), establish mappings, and document data dictionaries to support integration into the ODHS Enterprise Data Warehouse. This work will ensure CAM data is structured and documented for long-term reporting, analytics, and data governance alignment.
Scope:
Contractor’s Key Person under this WOC will work with ORRAI’s data warehouse team and CAM/CALMS project contractors to lead data modeling efforts that bridge business requirements with data architecture implementation. This includes the development of a conceptual data model, translation into dimensional star schemas, creation of mapping documents, and delivery of a data dictionary consistent with ODHS standards. The contractor will ensure modeling work is accurate, well-documented, and aligned with data governance and warehouse integration needs.
Tasks:
Contractor’s Key Person shall:
Collaborate with CAM/CALMS contractors and ORRAI DBEs and DBAs to gather and clarify data requirements.
Develop a comprehensive conceptual data model that defines key business entities, relationships, and data domains for the CAM system.
Translate the conceptual model into a dimensional model (star schema) suitable for data warehouse integration.
Design and document fact and dimension tables in alignment with data warehousing best practices.
Develop mapping documentation between CAM system data and the designed star schema to support ETL planning and implementation.
Create and maintain a detailed data dictionary for all modeled entities, attributes, and business rules, following ODHS metadata standards.
Facilitate workshops and meetings to validate models with stakeholders and ensure shared understanding.
Use tools such as Microsoft Visio or Erwin to produce clear and maintainable diagrams and documentation.
Ensure all deliverables align with ORRAI architecture, data governance practices, and metadata frameworks.
Provide support and consultation as the data structures move through the logical and physical implementation phases in the ODHS data warehouse.
Qualifications:
Extensive experience in conceptual and dimensional data modeling, including star schema design for data warehouses.
Strong proficiency in data modeling tools such as Microsoft Visio and/or Erwin.
Demonstrated ability to create clear and accurate data documentation, including entity definitions, mappings, and data dictionaries.
Strong verbal and written communication skills, with the ability to facilitate discussions and document requirements across technical and non-technical audiences.
Experience working with data warehouse teams, including DBEs and DBAs, in enterprise environments.
Familiarity with human services programs or public sector data systems is preferred.
Knowledge of data governance, metadata, and ETL processes is a plus.