Problem
Weak visibility of order ownership, status, deadlines, exceptions, and workload makes manual reporting and escalation difficult.
Portfolio case study · synthetic data
From fragmented order tracking to a testable analytics hand-off.
OOAMS is a self-directed Business Analysis, data analytics, and delivery package for a fictional B2B distributor. It connects requirements, SQLite, SQL, Python, Power BI design, Agile planning, testing, and UAT.
01 · Context
A clear operating problem needs a traceable solution.
The supplied scenario describes Meridian Industrial Supplies Pvt. Ltd. as a fictional wholesale distributor using spreadsheets, email, and messaging without one central operational view.
Objectives
- Define a consistent order lifecycle.
- Model operational data and audit history.
- Answer management questions with repeatable analytics.
Success definition
A reproducible, inspectable portfolio package that traces from BRD and FRD through SQL, Python, dashboard design, testing, and UAT.
02 · Operating model
Move from scattered updates to one analytical thread.
AS-IS scenario
- Order information spread across spreadsheets and messages.
- Ownership and status updates are difficult to reconcile.
- Exceptions are buried in correspondence.
- Management reporting is manually compiled.
TO-BE design
- Stable order references and controlled lifecycle statuses.
- Team and user assignment in the data model.
- Categorized issues, tasks, comments, and status history.
- SQL and Python outputs prepared for a manual Power BI build.
Architecture
Layered by responsibility, connected by traceability.
Requirements→SQLite schema + data→SQL analytics→Python pipeline→Excel hand-off→Power BI design
03 · Build
What the package demonstrates.
Requirements & process
BRD, process/gap analysis, FRD, business rules, acceptance criteria, and traceability.
Data foundation
11 operational tables, dataset metadata, 7 analytical views, and indexed filter paths in SQLite.
Analytics
Overdue, workload, bottleneck, issue, monthly trend, executive KPI, and SLA scorecard outputs.
Delivery planning
Agile epics, stories, sprint planning, and a Definition of Done in the supplied planning artefact.
Quality controls
Deterministic generation, explicit snapshot dates, safe output paths, SQL smoke checks, and pytest.
Presentation-ready
A dashboard design specification plus this static case study and a clear evidence trail.
04 · Synthetic baseline
Useful signals, carefully labelled.
The generator uses seed 42 and the fixed snapshot date 2025-09-12. These are portfolio diagnostics, not real business outcomes.
394
orders generated
91.9%
completion rate
6.4 d
average turnaround
121.9 h
NEW-stage average wait
| Signal | Synthetic result | Portfolio interpretation |
|---|---|---|
| Open-order overdue rate | 100.0% | All 28 open orders are past target at the fixed snapshot; this is intentionally diagnostic synthetic data. |
| Completion target | 91.9% | Above the illustrative >90% target in the supplied project narrative. |
| Turnaround target | 6.4 days | Above the illustrative <5.5-day target and therefore a clear improvement opportunity. |
| Primary bottleneck | NEW stage | Status history identifies the longest average wait; real operations would need validation. |
05 · Delivery evidence
Designed for review, not over-claiming.
Power BI
The workbook at 05-Python-Pipeline/OOAMS-Analytics-Output.xlsx is the intended data source. The supplied PDF specification defines pages, measures, and visuals.
Manual status: build and visual inspection in Power BI Desktop are still required. No PBIX is included.
Open Power BI specification →Jira / Agile
The supplied planning document contains six epics, user stories, sprint planning, and delivery definition. It is a portfolio planning artefact, not evidence of a live Jira project.
Open Agile artefact →Testing
Automated checks cover SQLite integrity, foreign keys, baseline counts, generator reproducibility, SQL view execution, and the 14-sheet workbook contract.
Open pytest suite →UAT
Business-user scenarios, simulated results, and an interviewer-facing case-study summary are recorded separately so automated validation is not confused with visual dashboard or stakeholder acceptance.
Open UAT scenarios → Open UAT results → Open UAT case study →06 · Reflection
What the project demonstrates.
Insights
The synthetic baseline shows a healthy completion percentage alongside poor speed and overdue exposure. That tension is useful: it encourages the analyst to separate throughput from timeliness and investigate the NEW-stage queue.
Challenges
Keeping time-based SQL and Python logic consistent, making scripts portable across working directories, preserving empty Excel sheets, and documenting Power BI as a manual step were the main quality decisions.
Learnings
A dashboard is only credible when definitions, data quality, reproducibility, requirements, and limitations agree. Synthetic results should be framed as hypotheses, not achievements.
Future improvements
Add a governed application/API layer, authentication, integrations, monitoring, backups, CI, interactive reporting periods, stakeholder usability testing, and production security controls.
07 · Technology
Small, inspectable, reproducible.
SQLite 3Python generatorSQL query packpandas + openpyxlPower BI design pytest + UATRelease traceability