Job ID: UT-59756 (99990327)
COGNOS developer with Oracle/SQL Server/Postgres, data modeling, ETL and healthcare experience
Location: Salt Lake City, UT
Duration: 12 months
The Controlled Substance Database (CSD) hosted at the Utah Department of Health (UDOH) Center for Health Data and Informatics (CHDI) utilizes data downloaded from the Utah Department of Commerce’s (DOC) Division of Professional Licensing (DOPL) CSD on a quarterly basis. After being uploaded to MS SQL Server, numerous metrics are calculated to support the Bureau of Health Promotions (BHP) in their reporting to the Utah Legislature, Local Community Partners, and the US Centers for Disease Control (CDC). In addition, the metric algorithms are provided to DOPL for application to their CSD for several BI dashboards hosted by DOPL – Patient and Prescriber.
Scoring Criteria:
COGNOS Report Development and Framework Manager experience30%
Oracle/SQL Server/Postgres experience / SQL 15%
Dimensional (Star Schema) Modeling Database experience15%
ETL (Extract, Transform and Load) experience 10%
Cost 30%
TOTAL 100%
In support of the above, The Contractor will:
1.Download from DOPL to CHDI required data from DOPL CSD tables into flat files.
2.Create and upload flat files into CHDI SQL Server CSD database.
3.Apply calculations to – identify Veterinarians, MME’s, De-dupe patients, limit prescriptions to opioid painkillers as determined by staff, etc.
4.Run scripts to identify patients with – prescriptions greater than 90 MMEs, concurrent 90 MME prescriptions and a Benzo prescriptions, prescriptions with 5 or more providers and 5 or more pharmacies, etc.
5.Based on requirements, design and build a view to report provider specialties.
6.Based on requirements, design and populate a quarterly rolling six month Datamart to support a COGNOS BI Prescriber dashboard tool. Utilization and Community metrics to be defined.
7.Based on requirements, design and populate a monthly Datamart to support two COGNOS BI Community dashboard tools to provide UDOH community partners with timely access to aggregated data to determine opioid trends.