Paginated Report Migration

Context

The company used BusinessObjects (BOXI) for static PDFs and Power BI for dashboards. BOXI scheduling worked, but metric logic was inconsistent across reports and hard to audit.
Leadership asked to consolidate on Power BI. I took ownership of the paginated layer, reverse engineered BOXI universes, designed reusable SQL datasets, and matched figures exactly.

Goals

1. Deliver the same PDF and Excel outputs from Power BI Paginated.
2. Replace fragmented BOXI logic with a governed, reusable SQL layer.
3. Standardise templates and parameters for quicker change control.
4. Validate totals against BOXI and secure Finance sign off.

Challenges

• Duplicated and conflicting metric definitions in BOXI.
• Limited visibility into universe SQL and joins.
• Customer facing schedules required a zero defect cutover.
• Sparse documentation for some legacy variables.

Approach

1. Audit and prioritisation

I requested a monthly export of all BOXI schedules and recipients. From that I built two working indexes:

• Report index ranked by number of schedules and customer exposure.
  This identified the highest value reports to migrate first.

• Recipient index grouped by user and department.
  This showed dependency clusters and consolidation opportunities.

These indexes became the plan of record for migration and communication.

2. Rationalising logic

Each BOXI report was reviewed and categorised:

• Rebuild when logic was correct but needed standardisation.
• Fix when logic or joins were wrong.
• Retire when the rule was no longer valid.

I created a small canonical SQL layer using views and CTEs for shared business metrics. This replaced BOXI variables and removed one off definitions. I also moved away from hard coded variables such as warehouse_1, warehouse_2 by using dynamic groups in Report Builder, so new values appear automatically.

3. Clean SQL pattern

Aggregate the fact first by keys and date, then join dimensions for descriptions. This keeps queries readable and index friendly.

WITH fact_summary AS (
  SELECT
    s.dim_customer_key,
    s.dim_salesperson_key,
    d.year,
    d.month,
    SUM(s.cases_sold) AS cases_sold
  FROM fact_sales s
  JOIN dim_date d
    ON s.dim_order_date_key = d.dim_date_key
  WHERE d.year = @year
  GROUP BY
    s.dim_customer_key,
    s.dim_salesperson_key,
    d.year,
    d.month
)
SELECT
  c.customer_number,
  c.customer_name,
  u.full_name AS salesperson_name,
  f.year,
  f.month,
  f.cases_sold
FROM fact_summary f
JOIN dim_customer c
  ON f.dim_customer_key = c.dim_customer_key
JOIN dim_user u
  ON f.dim_salesperson_key = u.dim_user_key;

Why this works well:

• Short, predictable GROUP BY on keys and date.
• Adding a new descriptive field is just another column from a dimension.
• Execution plans benefit from key indexes on the fact.

4. Parameterised reports

Legacy reports were often fixed to a period such as this month versus last month.
Each paginated report was rebuilt with parameters so a user can pick any period. SQL calculates the comparison period and headers render with real month and year, for example Sales 2024 Jan and Sales 2023 Dec.
This reduced duplicate schedules and improved self service.

5. Validation with Python

I wrote a small Pandas utility to compare BOXI and PBI outputs by shape, columns, and totals. Finance signed off each report once the diff was empty.

from mylibrary import check_reports
import pandas as pd

pbi = pd.read_excel("pbi_report.xlsx", index=["customer_number","year","month"])
boxi = pd.read_excel("boxi_report.xlsx", index=["customer_number","year","month"])

result = check_reports(pbi, boxi)
result.summary()  # shows shape, column, and total differences

6. Dynamic scheduling

BOXI schedules were static and hard to maintain. In Power BI I introduced dynamic per recipient schedules.

• A small Users semantic model refreshed daily with user_id, name, and email.
• Each row drove a parameterised run that filtered to that person’s data and emailed the result.
• New staff appeared automatically once added in the ERP.
• Managers received fixed schedules where a dynamic approach was not suitable.

Results

• 100+ paginated reports rebuilt on shared datasets.
• Change requests completed in days instead of weeks.
• Critical data warehouse issues identified and fixed during rebuild.
• Dynamic schedules removed manual maintenance and improved auditability.
• Finance validated that totals and outputs match the prior estate.

Lessons learned

• A thin canonical SQL layer removes drift and speeds up changes.
• Aggregate first, then join dimensions keeps queries simple and fast.
• Parameterised templates reduce schedule sprawl and improve usability.
• Lightweight validation scripts build trust and shorten approvals.

Conclusion

The migration replaced a fragile BOXI estate with a governed, reusable reporting layer in Power BI.
By standardising the logic, validating results, and automating delivery, reporting moved from reactive maintenance to a predictable and auditable process that the business can trust.