Constellation bills

Summary Database Structure and Connection Details

The discussion centers on the UVM production database's "Bills" view, which contains a JSON-formatted payload column. This column includes a ref value that directly corresponds to the bill ID in the DSS database, enabling cross-system linkage.

Key specifics:

payload column structure: Contains JSON data where the ref field stores the DSS bill ID, acting as a critical identifier for mapping records between UVM and DSS.

Data origin: All columns in the "Bills" view originate from the UVM database, except the payload content, which is sourced from DSS but stored within UVM.

ID mapping example: A UVM bill ID (e.g., 977347) links to a DSS bill ID (e.g., 6690769) via the ref value in the JSON payload.

Proposed Data Simplification

To streamline access, a new view will be created that extracts only essential columns from the existing "Bills" view. Additionally, the DSS bill ID (ref value) will be isolated into a dedicated column for clarity.

Integration Challenges

Directly connecting Power BI to the DSS database faces significant hurdles due to infrastructure constraints:

IP limitations: Whitelisting multiple IPs on the ABM side complicates direct access.

Data synchronization: Bills data updates frequently, necessitating a cron job to replicate DSS data into another system for reliable reporting. This replication effort is deemed resource-intensive.

Next Steps

Full technical details, including column mappings and access requirements, will be documented and shared via Slack post-meeting.