# OOAMS Portfolio Case Study

## View the static website

Open [`index.html`](index.html) directly in a browser, or serve the repository root locally:

```bash
python -m http.server 8000
```

Then visit `http://localhost:8000/10-Portfolio-Case-Study/`. The site uses only inline HTML/CSS, so no build step or package installation is required. The published case-study page is `https://ayushmaangangulywork-boop.github.io/OOAMS/10-Portfolio-Case-Study/`. The repository link in the page points to `https://github.com/ayushmaangangulywork-boop/OOAMS`. No PBIX is included.

> **Operations Order Management & Analytics System (OOAMS)**  
> A self-directed Business Analysis, data analytics, and delivery portfolio project for a fictional B2B distributor.

**Repository:** [OOAMS GitHub repository](../)  
**Data status:** synthetic and fictional  
**Delivery status:** portfolio prototype and design package; not deployed

## Hero

OOAMS turns a fragmented order-tracking problem into a traceable operating model: requirements are translated into a normalized SQLite data model, business questions are answered with SQL, KPIs are calculated in a reproducible Python pipeline, and a Power BI dashboard is specified for management use.

**Portfolio headline:** from business problem to testable analytics hand-off, without claiming production implementation.

## Problem

The fictional Meridian Industrial Supplies Pvt. Ltd. processes B2B orders across Procurement, Warehouse, Logistics, Customer Relations, and Operations Management. The supplied business scenario describes spreadsheets, email, and messaging being used without a central operational view. That creates weak ownership visibility, slow status review, inconsistent escalation, and manual reporting effort.

The dataset is a deliberately small synthetic representation of that scenario. It is not evidence about Meridian or any real company.

## Objectives

1. Define a consistent order lifecycle, ownership model, and requirement baseline.
2. Create a relational schema that supports orders, tasks, issues, comments, and immutable status history.
3. Answer management questions about overdue work, workload, bottlenecks, issues, and turnaround.
4. Produce a deterministic Excel hand-off suitable for a manually built Power BI report.
5. Demonstrate traceability from BRD and FRD through SQL, Python, dashboard design, and UAT.

## Solution

The solution is a layered portfolio package:

| Layer | Evidence |
| --- | --- |
| Requirements | `01-Requirements/` |
| Data model | `02-Database/OOAMS-DB-001-schema.sql` and `.db` |
| Synthetic data | `03-Data/OOAMS-Sample-Data-Generator.py` |
| Analytics SQL | `04-SQL/OOAMS-SQL-001-queries.sql` |
| Python reporting | `05-Python-Pipeline/OOAMS-Pipeline-001.py` |
| Dashboard design | `06-PowerBI/OOAMS-PBI-001.pdf` |
| Agile delivery | `07-Agile/OOAMS-APM-001.pdf` |
| Testing/UAT | `08-Testing/` and `tests/` |

## AS-IS vs TO-BE

| Dimension | AS-IS scenario | TO-BE design |
| --- | --- | --- |
| Order record | Multiple spreadsheets and messages | One order record with a stable order reference |
| Ownership | Difficult to see who owns open work | Team and user assignment in the order model |
| Status | Manual and inconsistent updates | Controlled lifecycle plus status history |
| Issues | Exceptions buried in correspondence | Categorized, prioritized, assigned issues |
| Reporting | Manual weekly compilation | Repeatable SQL and Python outputs |
| Management view | Limited bottleneck visibility | KPI, overdue, workload, issue, and trend outputs |
| Dashboard | Not available in the package | Power BI design specification; PBIX remains manual |

## Architecture

```text
Synthetic generator (--as-of + seed 42)
              |
              v
SQLite schema + database + analytical views
              |
       +------+------+
       |             |
       v             v
  SQL query pack   Python pipeline
                         |
                         v
              14-sheet Excel analytics output
                         |
                         v
            Manual Power BI Desktop build
```

The database stores `ooams_metadata.as_of_date` so SQL views and the Python pipeline use the same reproducible reporting snapshot.

## Features demonstrated

- Requirements decomposition, MoSCoW-style prioritisation, process analysis, and gap analysis.
- 11 operational tables plus dataset metadata, 7 analytical views, and indexed access paths.
- Order, task, issue, comment, and status-history modelling.
- SQL aggregations, joins, CTE/window-function examples, overdue analysis, workload analysis, and trend queries.
- Python validation, executive KPI calculation, bottleneck detection, issue analytics, workload analysis, and Excel export.
- Test strategy, lightweight automated checks, UAT acceptance criteria, limitations, and traceability.
- Agile epics, stories, sprint planning, and Definition of Done artefacts.

## Analytics

The reproducible baseline contains 394 orders, 316 completed orders, 50 cancelled orders, 28 open orders, 611 tasks, 120 issues, 1,655 status-history rows, and 305 comments. At the fixed snapshot date, the pipeline reports:

| KPI | Synthetic baseline | Interpretation |
| --- | ---: | --- |
| Completion rate | 91.9% | Exceeds the illustrative >90% target |
| Average turnaround | 6.4 days | Does not meet the illustrative <5.5-day target |
| Open-order overdue rate | 100.0% | Synthetic open orders are past their target date at this snapshot |
| Primary bottleneck | NEW stage, 121.9 average hours | Diagnostic result from synthetic status history |

These are generated portfolio findings, not real business outcomes or deployment results.

## Power BI

`06-PowerBI/OOAMS-PBI-001.pdf` defines the proposed pages, measures, visuals, and data source. The expected manual workflow is:

1. Run the generator and pipeline.
2. Open `05-Python-Pipeline/OOAMS-Analytics-Output.xlsx` in Power BI Desktop.
3. Create the model, relationships, measures, and visuals from the design specification.
4. Validate filters, labels, KPI definitions, and accessibility.
5. Save a local `.pbix` only if a reviewer wants to inspect the manual build.

No PBIX, deployment, or real dashboard usage is claimed here.

## Jira / Agile

`07-Agile/OOAMS-APM-001.pdf` contains the planned epics, user stories, and sprints. It is a portfolio planning artefact, not evidence of a live Jira project.

## Testing and UAT

Run:

```bash
pytest -q
```

The automated suite checks database integrity and foreign keys, baseline counts, generator reproducibility, SQL view execution, and the 14-sheet workbook contract. `08-Testing/OOAMS-TC-001.pdf` contains the broader manual test strategy. The business-user acceptance walkthrough is in [`08-Testing/UAT_SCENARIOS.md`](https://github.com/ayushmaangangulywork-boop/OOAMS/blob/main/08-Testing/UAT_SCENARIOS.md), with honest simulated statuses in [`08-Testing/UAT_RESULTS.md`](https://github.com/ayushmaangangulywork-boop/OOAMS/blob/main/08-Testing/UAT_RESULTS.md). `08-Testing/OOAMS-UAT-001.md` remains the concise integration record.

The concise interviewer-facing narrative is [`08-Testing/UAT_CASE_STUDY_SUMMARY.md`](https://github.com/ayushmaangangulywork-boop/OOAMS/blob/main/08-Testing/UAT_CASE_STUDY_SUMMARY.md).

## Insights

The synthetic data supports a useful management narrative:

- Completion is above the illustrative success threshold while speed and overdue exposure remain problematic.
- The NEW stage is the highest average waiting stage in the generated status history.
- Issue resolution is below the illustrative three-day threshold in the pipeline output.
- Workload and overdue views support escalation and resource-allocation discussion.

The correct project wording is “the synthetic baseline indicates…” rather than “the business achieved…”.

## Technology stack

Python 3, SQLite 3, pandas, NumPy, openpyxl, pytest, SQL, Markdown, Microsoft Word deliverables, and Power BI Desktop as a manual design/build target.

## Challenges and design decisions

- **Reproducibility:** fixed seed and snapshot date; no machine-specific absolute paths.
- **Date consistency:** the snapshot date is stored in `ooams_metadata` and used by SQL views.
- **Safe regeneration:** the generator protects non-default output databases unless `--force` is supplied.
- **Empty outputs:** the pipeline preserves empty-but-valid workbook sheets such as `AT RISK Orders`.
- **Scope control:** the package documents Power BI as a manual step rather than fabricating a PBIX.

## Learnings

The project reinforces that a polished analytics portfolio needs more than a dashboard mock-up: requirements, definitions, data quality, reproducibility, testing, and limitations must agree. It also demonstrates the importance of choosing one time basis for deadline KPIs and stating synthetic assumptions explicitly.

## Future improvements

- Add a real application/API layer with authentication and role-based access.
- Add CI for Python, SQL, workbook-contract, and link checks.
- Add a formal date-parameter interface for interactive reporting periods.
- Build and usability-test the Power BI report with representative stakeholders.
- Add production observability, data retention, backup, and security controls.
- Replace synthetic data with approved anonymized data only under a documented governance process.

## Limitations

This is not a deployed order-management system. It has no production users, live integrations, service-level commitments, security review, real customer data, or verified business impact. The database and analytics outputs are intentionally fictional and deterministic.

## GitHub presentation checklist

- Link the root README and this case study from the repository landing page.
- Keep `PBIX not included — manual Power BI Desktop step` visible.
- Do not convert illustrative KPI targets into claimed business results.
- Keep the repository link pointed at `https://github.com/ayushmaangangulywork-boop/OOAMS`.