Switch Data Sources of OKR Metrics Dashboard

[jsutantio]

User Story As a product manager and ReportStream team member, I would like my dashboard's data source to be the most accurate and reliable so that the resultant calculations and dashboards are less prone to error.

Description As of April 25th, 2024, the FY24 OKR Metrics Dashboard leverages the Onboarding Tracker SmartSheet, which is updated manually by the engagement team, to obtain COVID and Universal Pipeline connection status and organization codes. Due to the tracker being reliant on manual entry, which can cause human error, and may not always be consistently updated, the team has decided to use the PostgreSQL database to obtain the same data.

Acceptance Criteria

• [ ] Establish connection between Power BI and the PG DB staging
• [ ] Establish connection between Power BI and the PG DB production
• [ ] Import PG DB into Power BI and swap out formulas from Onboarding Tracker to PG DB
• [ ] Create and verify that PG DB query yields accurate results (e.g., double check COVID connection status and testing vs active vs inactive)
• [x] Document PG DB query in SharePoint
• [ ] Publish updated OKR Metrics Dashboard

[jsutantio]

@jillian-hammer for awareness

[jsutantio]

Steps taken so far, related to establish connection: -Worked with CDC IT to get a dedicated virtual machine so that there is a persistent IP address associated with the Power BI desktop. (The normal VMs will have IPs that change after closing/ restarting each session, which is forced after a period of time elapses). -Worked with CDC IT to whitelist the IP of the dedicated developer desktop (VM) to bypass the firewall and grant access to connect to Azure

Waiting on: -Put in ticket with ReportStream DevOps to whitelist the IP to connect to specific Azure databases (specifically the replica versions of both staging and prod PG DB)