Case Study
Global Enterprise · 2025–2026
NDA Notice — To comply with non-disclosure obligations and protect proprietary intellectual property, all corporate entities, internal ERP architectural schemas, and system identifiers have been generalised and anonymised. The logical methodologies, solution architectures, and performance outcomes remain fully representative of the actual engagement.
Business Analyst Intern · Global Fortune 500 Enterprise · Dec 2025 – Feb 2026
Order Reporting
Automation System
93%
Time reduction
500k+
Records migrated
30s
Per report
Knowledge locked inside people, not systems.
Generating a single customer order report required staff to manually navigate across multiple related Dataverse tables. Although foreign-key relationships existed in the system, users still had to open each record by hand, trace the relationships, and copy data into Excel one field at a time.
The process was slow, inconsistent, and entirely dependent on experienced staff who understood how the underlying data connected. When those staff were unavailable, reporting stopped. The challenge wasn't generating the report — it was making complex enterprise data understandable without requiring users to understand the database structure themselves.
Root causes identified
Fragmented data
Related records spread across 4 Dataverse tables — no unified view.
Manual FK resolution
Users had to mentally trace PK/FK links between the master sales table and three transactional sub-tables.
Undocumented logic
Business rules (coalesce fallbacks, field mappings, country codes) lived only in experienced staff.
Repetitive Excel work
Final report assembly done manually in Excel every single time.
As-Is → To-Be
As-Is · Manual
Search Dataverse manually
Open related record
Navigate another table
Copy field by field
Excel assembly
Customer report
⏱ Avg: 5+ minutes · Inconsistent output · Knowledge-dependent
To-Be · Automated
Order number input
Power Automate triggered
Dataverse PK/FK resolved
Business rules applied
Office Script formats
Customer report
⚡ Avg: ~30 seconds · Consistent output · No expertise needed
Abstracting database complexity from the user
Behind a single order number sat records spread across four Dataverse tables — an Enterprise Master Sales Table plus three Transactional Sub-tables (Shipping, Localisation, and Billing schemas) — connected by PK/FK relationships with multi-table coalesce fallback logic.
The coalesce resolution order: check the Shipping sub-table first → fall back to the Billing sub-table → fall back to the Master Sales Table. This handles cases where certain fields exist only in specific sub-tables.
Additional encoding: shipping destination (510→USA, 520→UK, etc.), foreign trade name truncation at " for ", and agency vs end-user name fallback.
Manual — before
↓ manual navigation for each table ↓
Automated — after
coalesce: Shipping → Billing → Master
↓
Solution Architecture
User Input
Single order number — the only thing the user ever touches.
Relationship Resolution
Power Automate queries Dataverse using existing PK/FK structure to fetch all related records automatically.
Business Rule Application
Coalesce logic, country code mapping, name truncation, and agency/end-user fallback all encoded in the flow.
Data Aggregation
Records from all four tables merged into a single unified data structure.
Template Instantiation
Master template copied into a new transaction-specific file — protecting the original.
Frozen Report Output
Office Script runs formatting rules, locks the output, and delivers a standalone customer-ready report.
Template isolation as a design principle
Rather than writing data directly into a shared master template — which risks corruption or accidental modification — the solution copies the master for each report run, injects data into the copy, runs the Office Script, and freezes the result as a standalone file.
Separating configuration from runtime data is a standard principle in maintainable enterprise systems. It ensures the master template can never be corrupted by an individual report run, and every output is independently auditable.
Template.xlsx
Master — never modified
↓ Copy
Template_[OrderID].xlsx
Transaction instance
↓ Inject Data + Run Script
Frozen_[OrderID].xlsx
Standalone read-only output
Delivered outcomes
93%
Reduction in report generation time
5 min → ~30 sec
500k+
Historical records across the dataset
8 global factories
~30s
Average report generation time
Down from 5+ minutes
1
User action required to generate a report
Single order number
0
Manual data queries needed
Fully automated lookup
∞
Company-wide availability
No expertise required
Deliverables
Business problem analysis
As-Is & To-Be mapping
Root cause analysis
Data relationship design
Solution architecture
Power Automate workflow
Governance design
Office Script + Excel output
Key takeaways
01
Analysis before automation
The right process came from understanding why the manual process existed and what it was trying to achieve — not from jumping to a technical solution.
02
Remove decisions, not just clicks
The best automation shifts cognitive burden from the user to the system. Users no longer needed to understand how the data connected.
03
Reliable data, reliable output
Correctly resolving PK/FK relationships across all four tables was the foundational design decision. Everything downstream depended on it.
04
Maintainability over complexity
Separating the template from runtime data was a deliberate governance choice that makes the system auditable, recoverable, and safe to iterate on.