# singhsrv/excel_sales_analysis architecture

> Architecture diagram of the GitHub repository singhsrv/excel_sales_analysis, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-08-17). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/singhsrv/excel_sales_analysis
- Repository: https://github.com/singhsrv/excel_sales_analysis

## Overview

##### Project type and purpose
- Type: A self-contained Excel-based BI solution (Profit & Loss dashboard)
- Purpose: Ingest raw sales data, transform it, build a data model, compute KPIs, and present interactive visuals.

##### File structure
- Gm subzone.pdf & P & L Year (Markets).pdf – exports of specific report pages
- README.md – describes ETL, modeling, and dashboard features
(No code directories; everything lives inside one .xlsx file.)

##### Extract architecture details from the README
- ETL using Power Query
- Data modeling in Power Pivot (relationship building)
- Calculations in DAX (measures & calculated columns)
- Dashboard UI with PivotTables/PivotCharts, slicers, conditional formatting, drill-downs
- Export to PDF (static snapshot of dashboard pages)

##### Main components & their interactions
a. Raw Data Sources
– CSV/Database/Flat files containing ~500K rows of sales
– External lookup tables (e.g., product lines, sub-zones)
b. ETL Layer (Power Query)
– Data connection(s)
– Query steps: filter, clean, transform, merge
– Custom date table generation
↓
c. Data Model (Power Pivot)
– Tables loaded from Power Query
– Relationships (primary/foreign keys)
– Star schema: FactSales at center, dimension tables for Date, Market, Product, SubZone
↓
d. Calculation Layer (DAX)
– Measures: GM%, target deltas, YTD sums, fiscal period tags
– Calculated columns (e.g., fiscal flags)
e. Presentation Layer (Excel Dashboard)
– PivotTables & PivotCharts bound to the data model
– Slicers (year, quarter, market, product) for interactive filtering
– Conditional formatting & drill-down buttons
– Export routines (to PDF files)

##### Architectural patterns & principles
- Layered BI architecture:
– Ingestion → Transformation → Modeling → Computation → Visualization
- Star schema in Power Pivot for performance
- Push vs. Pull: queries are “pulling” fresh data into PQ/PP on refresh
- Reusability: date table, common measures, template dashboard sheets

## Diagram (Mermaid)

```mermaid
graph TB
  subgraph "Documentation"
    Doc["README.md"]:::raw
  end

  subgraph "Raw Data Sources"
    DS1["Sales Data (CSV/DB)"]:::raw
    DS2["Lookup Tables"]:::raw
  end

  subgraph "ETL Layer (Power Query)"
    PQ["Power Query"]:::etl
    Clean["CleanSalesQuery"]:::etl
    DateGen["DateTableGen"]:::etl
  end

  subgraph "Data Model (Power Pivot)"
    PP["Power Pivot"]:::model
    subgraph "Star Schema"
      Fact["FactSales"]:::model
      DimDate["Date Dim"]:::model
      DimMarket["Market Dim"]:::model
      DimProduct["Product Dim"]:::model
      DimSub["SubZone Dim"]:::model
    end
  end

  subgraph "Calculation Layer (DAX)"
    DAX["DAX Measures & Calc Columns"]:::dax
  end

  subgraph "Presentation & Export"
    subgraph "Dashboard UI (Excel)"
      UI["PivotTables/Charts & Slicers"]:::ui
      Format["Conditional Formatting & Drill-down"]:::ui
    end
    subgraph "Exports (PDF)"
      GmSub["SubZone Dashboard Snapshot"]:::export
      PLYear["Profit & Loss by Market Snapshot"]:::export
    end
  end

  subgraph "User"
    Analyst["Analyst/Stakeholder"]:::persona
  end

  %% Data Flow
  Doc --> DS1
  Doc --> PQ
  DS1 --> PQ
  DS2 --> PQ
  PQ --> Clean
  Clean --> DateGen
  DateGen --> PP
  PP --> DAX
  DAX --> UI
  UI --> Format
  UI --> GmSub
  UI --> PLYear
  GmSub --> Analyst
  PLYear --> Analyst
  UI --> Analyst

  %% Click Events
  click Doc "https://github.com/singhsrv/excel_sales_analysis/blob/main/README.md"
  click GmSub "https://github.com/singhsrv/excel_sales_analysis/blob/main/Gm subzone.pdf"
  click PLYear "https://github.com/singhsrv/excel_sales_analysis/blob/main/P & L Year (Markets).pdf"

  %% Styles
  classDef raw fill:#CCCCCC,stroke:#333,stroke-width:1px
  classDef etl fill:#ADD8E6,stroke:#333,stroke-width:1px
  classDef model fill:#90EE90,stroke:#333,stroke-width:1px
  classDef dax fill:#FFB6C1,stroke:#333,stroke-width:1px
  classDef ui fill:#FFA500,stroke:#333,stroke-width:1px
  classDef export fill:#FFD700,stroke:#333,stroke-width:1px
  classDef persona fill:#E0E0E0,stroke:#333,stroke-width:1px
```
