04BI modernization

Modernizing the BI layer

I moved roughly 19 production dashboards away from workbook-level warehouse connections and onto governed published data sources. The migration also surfaced logic that had been quietly choosing the wrong duplicate record.

~19dashboards migrated

1governed source layer

2-stagededup approach

Trigger

A credential change broke scheduled reporting.

Production dashboards had grown around a mix of direct live connections, extracts, database views and embedded custom SQL. When credentials associated with those connections changed, scheduled reports failed because individual workbooks still depended on the old connection setup.

That exposed a maintainability problem: connection management was scattered across production workbooks.

Before

Workbook-level connections

Direct warehouse connections

Embedded custom SQL

Credentials managed across reports

After

Published data sources

Reusable governed sources

Custom SQL moved into views

Centralized connection layer

Migration

I moved the logic to the right layer before replacing the connection.

For dashboards already backed by appropriate tables or views, I published governed data sources and replaced the workbook connection. Where a workbook contained custom SQL, I moved that transformation into a database view first and published the view as the reusable source.

I also archived one-off or broken dashboards instead of carrying every legacy asset forward.

Discovery

The migration caught a deduplication bug.

While validating a daily applicant report, I found that multiple applications for the same person could be reduced to a record without enough logic to guarantee it was the correct one.

I reproduced examples where the existing approach failed and replaced it with deterministic ranking using business status, dates and an explicit record-level tie-breaker. A second check handled legitimate cases that could otherwise look like duplicates.

Deterministic ranking patternsanitized example

ROW_NUMBER() OVER (
  PARTITION BY applicant_identity, cohort
  ORDER BY
    relevant_date,
    business_status_priority,
    record_id DESC
)

Deduplication is not just getting one row. The rule has to explain why that row is the one the business should use.

Outcome

The reporting layer became easier to manage and easier to trust.

The primary dashboards moved onto governed published sources, reducing workbook-level connection dependency. The validation work also surfaced and corrected hidden data-quality issues instead of reproducing them in the new setup.