# gokulrnair7/sankalp-industries-budget-vs-actual-variance-analysis architecture

> Architecture diagram of the GitHub repository gokulrnair7/sankalp-industries-budget-vs-actual-variance-analysis, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-07-31). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/gokulrnair7/sankalp-industries-budget-vs-actual-variance-analysis
- Repository: https://github.com/gokulrnair7/sankalp-industries-budget-vs-actual-variance-analysis

## Overview

#### Purpose and Entry Points

This repository is an end-to-end operating-expense budget-versus-actual variance analysis for a synthetic enterprise. Its pipeline generates a star-schema dataset, loads and cleans it in SQL Server, then presents the governed results in Excel and Power BI.

Primary execution order:
- `src/generate_dataset.py`
- `sql/01_create_and_load.sql`
- `sql/02_data_cleaning.sql`
- `sql/03_analysis.sql`

#### Synthetic Data Generation

`src/generate_dataset.py` is the upstream source of the analytical dataset. It produces:
- `data/fact_budget_actuals.csv`
- `data/dim_account.csv`
- `data/dim_department.csv`

The fact records monthly budget and actual spend; dimensions define accounts/cost categories and organizational attributes. Generation intentionally injects realistic ERP-like conditions: null actuals for open periods, duplicates, zero-budget spend, credit notes, renamed accounts, and isolated spikes. These are inputs to downstream data-governance and variance logic, not generation failures.

#### SQL Server Raw-Landing Boundary

`sql/01_create_and_load.sql` defines the SQL Server physical schema before CSV import. This is an important boundary: typed database tables protect date, text-code, nullable-actual, and numeric semantics from import-wizard inference.

The source CSVs are imported into raw tables after schema creation. Raw data is retained as the audit-preserving landing layer; downstream consumers should not depend on it for cleaned totals.

#### Cleaning and Governed Fact View

`sql/02_data_cleaning.sql` creates `vw_clean_fact`, the canonical analytical fact layer. It is a view, preserving raw records while centralizing transformations.

The view:
- removes exact business-record duplicates while excluding the surrogate key from duplicate matching;
- repairs consistently swapped date parts;
- marks incomplete/open reporting periods;
- retains null actuals, credit notes, and zero-budget actual spend as meaningful financial events.

`vw_clean_fact` is the main contract between data preparation and reporting. In particular, open-period filtering must be applied by every aggregate consumer to avoid treating missing/unclosed actuals as a completed-period variance.

#### Analysis Layer

`sql/03_analysis.sql` contains the management-analysis queries over the cleaned view and dimensions. Its CTE-based stages support independent inspection of intermediate logic.

The analytical flow calculates headline net variance and absolute variance, then slices results by department, business unit, and cost category. It additionally identifies:
- one-off spend by comparing a cost line to its own monthly baseline;
- material variances requiring both percentage and rupee thresholds;
- unbudgeted spend;
- cumulative year-to-date variance.

This separates persistent control or planning issues from exceptional events and prevents small high-percentage misses or large low-significance values from dominating review.

#### Reporting Consumers

`excel/Sankalp_Variance_Analysis.xlsx` is a formula-driven executive-analysis artifact. Its budget and actual inputs feed reconciled variance, bridge, and summary views from a single source area.

`powerbi/Sankalp_Variance_Dashboard.pbix` is the interactive reporting layer. It connects directly to SQL Server’s cleaned fact view and the two dimensions, rather than reimplementing cleaning in Power BI. The model uses one-to-many dimension-to-fact filtering and supports fiscal/calendar and organization slicers.

The Power BI file depends on a local SQL Server connection for refresh, although cached data is embedded.

#### Runtime and Infrastructure

Material runtimes are Python with pandas/numpy for generation and Microsoft SQL Server/SSMS for storage, import, cleaning, and querying. Excel and Power BI are downstream desktop BI clients.

The primary operational dependency is the SQL Server database connection: it hosts raw tables, `vw_clean_fact`, and the dimensional model consumed by Power BI. Data files under `data/` are reproducible seed artifacts, while the Excel workbook and `.pbix` are presentation endpoints.

Main entry points: [`sql/01_create_and_load.sql`](https://github.com/gokulrnair7/sankalp-industries-budget-vs-actual-variance-analysis/blob/main/sql/01_create_and_load.sql) (Schema and raw load), [`src/generate_dataset.py`](https://github.com/gokulrnair7/sankalp-industries-budget-vs-actual-variance-analysis/blob/main/src/generate_dataset.py) (Synthetic dataset generator), [`sql/03_analysis.sql`](https://github.com/gokulrnair7/sankalp-industries-budget-vs-actual-variance-analysis/blob/main/sql/03_analysis.sql) (Management analysis), [`data/fact_budget_actuals.csv`](https://github.com/gokulrnair7/sankalp-industries-budget-vs-actual-variance-analysis/blob/main/data/fact_budget_actuals.csv) (Budget actuals CSV)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_generation["Data Generation"]
  node_generator{{"Synthetic dataset generator<br/>Python / pandas"}}
  node_fact_csv["Budget actuals CSV<br/>seed fact file"]
  node_account_csv["Account CSV<br/>seed dimension file<br/>[dim_account.csv]"]
  node_department_csv["Department CSV<br/>seed dimension file<br/>[dim_department.csv]"]
end

subgraph group_sqlserver["SQL Server"]
  node_load_schema{{"Schema and raw load<br/>SQL Server import"}}
  node_raw_fact[("Raw fact table<br/>audit landing table")]
  node_raw_dimensions[("Raw dimension tables")]
  node_cleaning{{"Cleaning logic<br/>SQL transformation"}}
  node_clean_fact[("Clean fact view<br/>governed fact contract")]
  node_analysis{{"Management analysis<br/>SQL CTE queries<br/>[03_analysis.sql]"}}
  node_variance_outputs["Variance insights<br/>management analysis"]
end

subgraph group_reporting["Reporting"]
  node_excel["Executive workbook<br/>Excel analysis"]
  node_powerbi["Interactive dashboard<br/>Power BI model"]
  node_bi_model[("Star-schema BI model<br/>dimension-to-fact model")]
end

node_generator -->|"generates"| node_fact_csv
node_generator -->|"generates"| node_account_csv
node_generator -->|"generates"| node_department_csv
node_fact_csv -->|"imports"| node_load_schema
node_account_csv -->|"imports"| node_load_schema
node_department_csv -->|"imports"| node_load_schema
node_load_schema -->|"lands"| node_raw_fact
node_load_schema -->|"lands"| node_raw_dimensions
node_raw_fact -->|"source records"| node_cleaning
node_cleaning -->|"creates"| node_clean_fact
node_clean_fact -->|"governed facts"| node_analysis
node_raw_dimensions -->|"dimensions"| node_analysis
node_analysis -->|"produces"| node_variance_outputs
node_clean_fact -->|"fact source"| node_bi_model
node_raw_dimensions -->|"dimension sources"| node_bi_model
node_bi_model -->|"powers"| node_powerbi
node_excel -.->|"presents reconciled views"| node_variance_outputs

click node_generator "https://github.com/gokulrnair7/sankalp-industries-budget-vs-actual-variance-analysis/blob/main/src/generate_dataset.py"
click node_fact_csv "https://github.com/gokulrnair7/sankalp-industries-budget-vs-actual-variance-analysis/blob/main/data/fact_budget_actuals.csv"
click node_account_csv "https://github.com/gokulrnair7/sankalp-industries-budget-vs-actual-variance-analysis/blob/main/data/dim_account.csv"
click node_department_csv "https://github.com/gokulrnair7/sankalp-industries-budget-vs-actual-variance-analysis/blob/main/data/dim_department.csv"
click node_load_schema "https://github.com/gokulrnair7/sankalp-industries-budget-vs-actual-variance-analysis/blob/main/sql/01_create_and_load.sql"
click node_cleaning "https://github.com/gokulrnair7/sankalp-industries-budget-vs-actual-variance-analysis/blob/main/sql/02_data_cleaning.sql"
click node_analysis "https://github.com/gokulrnair7/sankalp-industries-budget-vs-actual-variance-analysis/blob/main/sql/03_analysis.sql"
click node_excel "https://github.com/gokulrnair7/sankalp-industries-budget-vs-actual-variance-analysis/blob/main/excel/Sankalp_Variance_Analysis.xlsx"
click node_powerbi "https://github.com/gokulrnair7/sankalp-industries-budget-vs-actual-variance-analysis/blob/main/powerbi/Sankalp_Variance_Dashboard.pbix"

classDef toneNeutral fill:#f8fafc,stroke:#334155,stroke-width:1.5px,color:#0f172a
classDef toneBlue fill:#dbeafe,stroke:#2563eb,stroke-width:1.5px,color:#172554
classDef toneAmber fill:#fef3c7,stroke:#d97706,stroke-width:1.5px,color:#78350f
classDef toneMint fill:#dcfce7,stroke:#16a34a,stroke-width:1.5px,color:#14532d
classDef toneRose fill:#ffe4e6,stroke:#e11d48,stroke-width:1.5px,color:#881337
classDef toneIndigo fill:#e0e7ff,stroke:#4f46e5,stroke-width:1.5px,color:#312e81
classDef toneTeal fill:#ccfbf1,stroke:#0f766e,stroke-width:1.5px,color:#134e4a
class node_generator,node_fact_csv,node_account_csv,node_department_csv toneBlue
class node_load_schema,node_raw_fact,node_raw_dimensions,node_cleaning,node_clean_fact,node_analysis,node_variance_outputs toneAmber
class node_excel,node_powerbi,node_bi_model toneMint
```

## Components

### Data Generation

- [Synthetic dataset generator](https://github.com/gokulrnair7/sankalp-industries-budget-vs-actual-variance-analysis/blob/main/src/generate_dataset.py): Python / pandas · Creates reproducible ERP-like budget and actual data conditions. · `src/generate_dataset.py`
- [Budget actuals CSV](https://github.com/gokulrnair7/sankalp-industries-budget-vs-actual-variance-analysis/blob/main/data/fact_budget_actuals.csv): seed fact file · `data/fact_budget_actuals.csv`
- [Account CSV](https://github.com/gokulrnair7/sankalp-industries-budget-vs-actual-variance-analysis/blob/main/data/dim_account.csv): seed dimension file · `data/dim_account.csv`
- [Department CSV](https://github.com/gokulrnair7/sankalp-industries-budget-vs-actual-variance-analysis/blob/main/data/dim_department.csv): seed dimension file · `data/dim_department.csv`

### SQL Server

- [Schema and raw load](https://github.com/gokulrnair7/sankalp-industries-budget-vs-actual-variance-analysis/blob/main/sql/01_create_and_load.sql): SQL Server import · Defines typed physical tables before CSV import. · `sql/01_create_and_load.sql`
- **Raw fact table**: audit landing table · Retains imported records before governed transformations.
- **Raw dimension tables**: dimension tables
- [Cleaning logic](https://github.com/gokulrnair7/sankalp-industries-budget-vs-actual-variance-analysis/blob/main/sql/02_data_cleaning.sql): SQL transformation · Centralizes duplicate, date, and reporting-period handling. · `sql/02_data_cleaning.sql`
- **Clean fact view**: governed fact contract · Aggregate consumers must filter open reporting periods.
- [Management analysis](https://github.com/gokulrnair7/sankalp-industries-budget-vs-actual-variance-analysis/blob/main/sql/03_analysis.sql): SQL CTE queries · Calculates variance, materiality, one-off, unbudgeted, and YTD views. · `sql/03_analysis.sql`
- **Variance insights**: management analysis

### Reporting

- [Executive workbook](https://github.com/gokulrnair7/sankalp-industries-budget-vs-actual-variance-analysis/blob/main/excel/Sankalp_Variance_Analysis.xlsx): Excel analysis · Formula-driven variance, bridge, and summary views. · `excel/Sankalp_Variance_Analysis.xlsx`
- [Interactive dashboard](https://github.com/gokulrnair7/sankalp-industries-budget-vs-actual-variance-analysis/blob/main/powerbi/Sankalp_Variance_Dashboard.pbix): Power BI model · Refreshes from the local SQL Server model; cached data is embedded. · `powerbi/Sankalp_Variance_Dashboard.pbix`
- **Star-schema BI model**: dimension-to-fact model · Uses one-to-many account and department filtering.

## Connections

- Synthetic dataset generator → Budget actuals CSV: generates
- Synthetic dataset generator → Account CSV: generates
- Synthetic dataset generator → Department CSV: generates
- Budget actuals CSV → Schema and raw load: imports
- Account CSV → Schema and raw load: imports
- Department CSV → Schema and raw load: imports
- Schema and raw load → Raw fact table: lands
- Schema and raw load → Raw dimension tables: lands
- Raw fact table → Cleaning logic: source records
- Cleaning logic → Clean fact view: creates
- Clean fact view → Management analysis: governed facts
- Raw dimension tables → Management analysis: dimensions
- Management analysis → Variance insights: produces
- Clean fact view → Star-schema BI model: fact source
- Raw dimension tables → Star-schema BI model: dimension sources
- Star-schema BI model → Interactive dashboard: powers
- Executive workbook → Variance insights: presents reconciled views