If you’re involved in data engineering, analytics, visualisation, or reporting, you know the work is never truly done. Requirements and designs evolve constantly, driven by continuous improvement and the pursuit of better insights.
“This Debtors dashboard is great, but wouldn’t it be even better if we could get contact history data from our CRM system too?”
“We’ve had a financial review and need to track new KPIs. Can you add them to our Finance report?”
These are typical requests: ingesting data from new systems, joining datasets, and defining new metrics. But some changes are harder to implement.
When Reporting Meets Reality
“The Sales Director wants to see analysis by Business Unit.”
Your ERP system isn’t set up to capture transactions by business unit. Now what?
If all you have is a hammer, everything looks like a nail. Data engineers and analysts often try to solve this in the transformation or reporting layer. If you can define mapping rules (e.g., departments A, B, and C map to Business Unit 1), you can categorise data and meet the request.
But this approach can create technical debt. What happens when new departments are added? Does your logic account for them? Over time, this can lead to a fragile web of undocumented transformations relying on tribal knowledge and eroding trust in reporting.
Roche’s Maxim advises performing data transformations as far upstream (close to the source) as possible, and only downstream (in the report) when necessary.
Start by assessing the quality of your source data:
- Is the field categorical or free-text?
- Is it mandatory or optional?
- Can it be reliably used for mapping or calculations?
For example, appraising the “department” field helps determine if it’s suitable for mapping to “business unit” both historically and going forward.
Learn how to transform fragmented reporting into real-time decision-making.
Can the Source System Be Changed?
Often overlooked, changing the source system can be the best solution. In environments like Microsoft Dynamics Business Central, Microsoft 365, and Azure, you have options and expertise.
If your company has restructured into Business Units, consider adjusting the ERP system to support this. Adding a new global or shortcut dimension with posting setups can deliver major benefits:
- Reliable Data Capture:
Enforced by user permissions and system controls.
- Historical Back population:
Categorise past data accurately.
- Simplified Reporting:
Easily load new dimensions into datasets and dashboards.
- Integration Ready:
Data is usable beyond reporting across ERP workflows.
- Trustworthy Insights:
Users can reconcile reports with source data.
Data Quality Is Everything
Your Power BI dashboard might look and function the same regardless of how it was built. But some approaches mask poor data quality or introduce complexity that grows over time.
The takeaway? Prioritise data quality at the source. Use reporting requests as opportunities to highlight risks and improve reliability because better data leads to better decisions.