README.md

metadata

title: Chat With Data
emoji: 🏨
colorFrom: blue
colorTo: purple
sdk: streamlit
sdk_version: 1.39.0
app_file: frontend/dashboard.py
python_version: '3.11'
pinned: false

Chat with your data — Enterprise AI Analytics Platform

An enterprise-grade "Chat with Your Data" system that enables stakeholders to query hotel performance data in plain English, backed by a deterministic metrics engine, real-time KPI monitoring, and automated anomaly detection.

Live Demo • Features • Architecture • Quick Start • KPI Reference

---

🎯 Live Demo

🔗 Launch App on Streamlit Cloud)

Dashboard Preview

Executive Dashboard — KPI cards, revenue trends, and city performance

📸 More Screenshots

AI Chat Interface — natural language queries with formatted business answers

KPI Monitoring — anomaly detection alerts and property health scores

---

📌 Problem Statement

In large hospitality organizations, performance data is spread across multiple tables with complex relationships. Business users need answers to questions like:

• "What is our RevPAR for luxury hotels in Mumbai this month?"
• "Which booking platform has the highest cancellation rate?"
• "Compare week 25 vs week 29 occupancy across cities"

Traditionally, each question requires an analyst to manually write SQL, build a report, and deliver it — a process that takes hours per query and doesn't scale.

This platform solves that by enabling anyone to ask business questions in plain English and receive accurate, data-driven answers in seconds.

---

✨ Features

📊 Executive Dashboard

• 6 real-time KPI cards with live Week-over-Week deltas
• Multi-dimensional filtering — City, Category, Room Class, Month, Week
• Revenue by Category — Luxury vs Business donut chart
• Weekly Revenue Trend — Interactive line chart
• Weekend vs Weekday — Performance comparison table
• Realisation % & ADR by Platform — Dual-axis combo chart
• City Performance — Horizontal bar comparison
• Weekly Occupancy Trend — Area chart with time series
• Property Performance Table — All 13 KPIs per hotel

💬 Chat with Your Data (AI Agent)

• Natural language queries — Ask in plain English, get data-driven answers
• 24 built-in KPI metrics — Deterministic SQL generation (zero syntax errors)
• Complex query support — Rankings, comparisons, multi-step analysis
• Custom SQL fallback — LLM generates SQL for ad-hoc analytical questions
• Suggested starter questions — One-click query templates
• Formatted business answers — Currency, percentages, actionable insights

🔍 KPI Monitoring & Anomaly Detection

• 4-layer alert system:
  • Threshold-based alerts (configurable per KPI)
  • Week-over-Week drop detection
  • Consecutive decline detection (3+ week downtrends)
  • Statistical anomaly detection (z-score across properties)
• Property Health Scoring — 0-100 score per hotel based on weighted KPI performance
• Interactive trend analysis — Select any metric, view with threshold lines
• Configurable thresholds — Adjust warning/critical levels per session

---

🏗️ Architecture

System architecture — export as PNG from draw.io, Excalidraw, or Lucidchart and save as assets/images/architecture_diagram.png

View ASCII version

┌─────────────────────────────────────────────────────────────────────┐
│                     FRONTEND (Streamlit)                            │
│                                                                     │
│  ┌──────────────┐  ┌──────────────────┐  ┌───────────────────────┐ │
│  │  Dashboard    │  │  Chat with Data  │  │  KPI Monitoring       │ │
│  │  (KPI Cards,  │  │  (Natural Lang   │  │  (Anomaly Detection,  │ │
│  │   Charts,     │  │   Queries, AI    │  │   Health Scores,      │ │
│  │   Filters)    │  │   Agent)         │  │   Trend Analysis)     │ │
│  └──────┬───────┘  └────────┬─────────┘  └──────────┬────────────┘ │
│         │                   │                        │              │
└─────────┼───────────────────┼────────────────────────┼──────────────┘
          │                   │                        │
          ▼                   ▼                        ▼
┌─────────────────────────────────────────────────────────────────────┐
│                   METRICS ENGINE (Single Source of Truth)            │
│                   utils/metrics_engine.py                            │
│                                                                     │
│  get_core_metrics() │ get_wow_deltas() │ get_property_table()       │
│  get_trend_data()   │ get_city_comparison() │ get_platform_perf()   │
└─────────────────────────────┬───────────────────────────────────────┘
                              │
                              ▼
┌─────────────────────────────────────────────────────────────────────┐
│                   DETERMINISTIC SQL BUILDER                         │
│                   tools/tools.py                                    │
│                                                                     │
│  ┌──────────────────────────────────────────────────────────┐      │
│  │ _METRIC_CONFIG (24 metrics)                               │      │
│  │ _build_metric_sql() → Perfect SQL every time              │      │
│  │ _build_wow_sql() → Week-over-Week with window functions   │      │
│  │ _assemble_cross_table_query() → CTE-based RevPAR          │      │
│  └──────────────────────────────────────────────────────────┘      │
│                                                                     │
│  execute_metric_query()  → Deterministic (95% of queries)          │
│  execute_custom_sql()    → LLM-generated (complex ad-hoc)          │
│  get_database_context()  → Live schema introspection               │
└─────────────────────────────┬───────────────────────────────────────┘
                              │
          ┌───────────────────┼───────────────────┐
          ▼                   │                   ▼
┌──────────────────┐          │         ┌──────────────────┐
│   AI AGENT       │          │         │   SUPABASE       │
│   agents.py      │          │         │   PostgreSQL     │
│                  │          │         │                  │
│  LiteLLM +       │          │         │  dim_date        │
│  Native Function │──────────┘         │  dim_hotels      │
│  Calling         │                    │  dim_rooms       │
│                  │                    │  fact_bookings   │
│  3 Tools:        │───────────────────▶│  fact_aggregated │
│  calculate_metrics                    │  _bookings       │
│  run_custom_sql  │                    │                  │
│  search_metric   │                    │  ETL Pipeline    │
└──────────────────┘                    │  (CSV→Raw→Clean) │
                                        └──────────────────┘

Why This Architecture?

Design Decision	Rationale
Deterministic SQL Builder	LLMs can write bad SQL. Building SQL programmatically from metric configs eliminates syntax errors for known KPIs.
Two-path query strategy	95% of questions use the reliable builder. Only truly novel questions fall back to LLM-generated SQL.
Never direct-join fact tables	fact_bookings and fact_aggregated_bookings have different granularity. Direct joins cause row multiplication. CTEs handle cross-table metrics.
Single metrics engine	Dashboard, agent, and monitoring all use the same SQL builder. Numbers always match.
Native function calling	More reliable than text-based ReAct parsing. Structured JSON tool calls work with any LLM.
Separate ETL pipeline	Raw → Clean transformation with validation. Business rules (weekend = Fri+Sat) applied at ETL layer.

---

📁 Project Structure

atliq-hospitality/
│
├── agents/
│   ├── __init__.py
│   └── agents.py              # AI agent with native function calling
│
├── assets/
│   └── images/                # ← Drop your screenshots here
│       ├── dashboard_overview.png
│       ├── chat_with_data.png
│       ├── kpi_monitoring.png
│       ├── architecture_diagram.png
│       ├── db_schema_er_diagram.png
│       └── etl_pipeline_output.png
│
├── etl/
│   └── etl_pipeline.py        # CSV → Raw DB → Clean DB pipeline
│
├── frontend/
│   ├── dashboard.py            # Executive dashboard (main page)
│   └── pages/
│       ├── 02_Chat_with_Data.py    # Natural language query interface
│       └── 03_KPI_Monitoring.py    # Anomaly detection & health scores
│
├── prompts/
│   └── cot_prompts.py          # Chain-of-thought prompt templates
│
├── tools/
│   └── tools.py                # Deterministic SQL builder + execution
│
├── utils/
│   ├── config.py               # Configuration, schema map, metric library
│   └── metrics_engine.py       # Shared metrics API (single source of truth)
│
├── .streamlit/
│   └── config.toml             # Streamlit theme & server config
│
├── requirements.txt
├── .gitignore
└── README.md

---

🚀 Quick Start

Prerequisites

• Python 3.11+
• Supabase account (PostgreSQL database)
• OpenRouter API key (for LLM access)

1. Clone & Install

git clone https://github.com/YOUR_USERNAME/atliq-hospitality.git
cd atliq-hospitality
pip install -r requirements.txt

2. Configure Environment

Create .env in project root:

CLEAN_SUPABASE_DB_URI="postgresql://postgres:PASSWORD@db.XXXXX.supabase.co:5432/postgres"
OPENROUTER_API_KEY="sk-or-v1-XXXXXXXX"

3. Run ETL Pipeline (First Time Only)

python etl/etl_pipeline.py

This loads CSV data → Raw DB → transforms → Clean DB with validation.

Expected output:

Screenshot of a successful ETL run — save your terminal output as assets/images/etl_pipeline_output.png

4. Launch Application

streamlit run frontend/dashboard.py

Open http://localhost:8501 in your browser.

---

🗄️ Data Model

Entity Relationship Diagram

ER diagram of the Supabase PostgreSQL schema — generate from pgAdmin, DBeaver, or dbdiagram.io

Schema Overview

┌─────────────┐       ┌──────────────┐       ┌─────────────┐
│  dim_date   │       │  dim_hotels   │       │  dim_rooms  │
│─────────────│       │──────────────│       │─────────────│
│ date (PK)   │       │ property_id   │       │ room_id (PK)│
│ mmm_yy      │       │  (PK)        │       │ room_class  │
│ week_no     │       │ property_name │       └──────┬──────┘
│ day_type    │       │ category      │              │
└──────┬──────┘       │ city          │              │
       │              └──────┬───────┘              │
       │                     │                      │
       ▼                     ▼                      ▼
┌────────────────────────┐    ┌──────────────────────────────┐
│ fact_aggregated_       │    │      fact_bookings            │
│     bookings           │    │──────────────────────────────│
│────────────────────────│    │ booking_id (PK)              │
│ property_id (FK)       │    │ property_id (FK)             │
│ check_in_date (FK)     │    │ booking_date                 │
│ room_category (FK)     │    │ check_in_date (FK)           │
│ successful_bookings    │    │ checkout_date                │
│ capacity               │    │ room_category (FK)           │
│                        │    │ booking_platform             │
│ Grain: property +      │    │ booking_status               │
│   date + room_type     │    │ revenue_generated            │
│   (for occupancy)      │    │ revenue_realized             │
└────────────────────────┘    │ ratings_given, no_guests     │
                              │                              │
                              │ Grain: individual booking    │
                              │   (for revenue, ADR, etc.)   │
                              └──────────────────────────────┘

Key Business Rules

Rule	Detail
Weekend	Friday & Saturday (stakeholder-defined, non-standard)
Weekday	Sunday through Thursday
Revenue	Always use revenue_realized (net after cancellation adjustments)
Cancellation	Hotel keeps 40% of revenue_generated, refunds 60%
No Show	Full revenue_generated goes to hotel
Ratings	0 means "not rated" — excluded from averages
week_no	Stored as TEXT — always quote in SQL: '31' not 31
Fact table join	NEVER direct-join both fact tables — different granularity, use CTEs

Coverage

Dimension	Values
Date Range	May – July 2022 (92 days)
Cities	Delhi, Mumbai, Hyderabad, Bangalore
Hotel Categories	Luxury, Business
Room Classes	Standard, Elite, Premium, Presidential
Booking Platforms	MakeYourTrip, LogTrip, Tripster, Direct Online, Direct Offline, Journey, Others
Booking Status	Checked Out, Cancelled, No Show
Weeks	19 – 32

---

📊 KPI Reference

24 Built-in Metrics

Base Metrics

Metric	Formula	Source
Revenue	SUM(revenue_realized)	fact_bookings
Total Bookings	COUNT(booking_id)	fact_bookings
Total Capacity	SUM(capacity)	fact_aggregated_bookings
Total Successful Bookings	SUM(successful_bookings)	fact_aggregated_bookings
Average Rating	AVG(ratings_given) WHERE rating > 0	fact_bookings
No of Days	COUNT(DISTINCT date)	dim_date

Derived KPIs

Metric	Formula	Description
Occupancy %	Successful Bookings / Capacity × 100	Room utilization rate
ADR	Revenue / Total Bookings	Average revenue per booking
RevPAR	Revenue / Capacity	Revenue per available room (cross-table CTE)
Realisation %	1 − (Cancellation% + No Show%)	Booking-to-stay conversion
Cancellation %	Cancelled / Total Bookings × 100	Booking drop-off rate
No Show Rate	No Shows / Total Bookings × 100	Ghost booking rate
DBRN	Total Bookings / No of Days	Daily booked room nights
DSRN	Total Capacity / No of Days	Daily sellable room nights
DURN	Checked Out / No of Days	Daily utilized room nights

Week-over-Week (WoW) Metrics

Metric	Calculation
Revenue WoW	(Current Week / Previous Week) − 1
Occupancy WoW	Same pattern
ADR WoW	Same pattern
RevPAR WoW	Same pattern (cross-table)
Realisation WoW	Same pattern
DSRN WoW	Same pattern

Breakdown Metrics

Metric	Description
Booking % by Platform	Each platform's share of total bookings
Booking % by Room Class	Each room class's share of total bookings

---

🤖 AI Agent — How It Works

Tool Calling Flow

User: "What is the RevPAR for luxury hotels in Mumbai?"
  │
  ▼
LLM understands intent
  │
  ▼
LLM calls: calculate_metrics({
    metrics: ["revpar"],
    filters: { city: "Mumbai", category: "Luxury" }
  })
  │
  ▼
Python: _build_metric_sql("revpar", filters)
  → Generates CTE query (cross-table)
  → Executes against Supabase
  → Returns DataFrame
  │
  ▼
LLM receives data, formats business answer:
  "RevPAR for luxury hotels in Mumbai: ₹10,234
   This is 15% above the portfolio average..."

Three Tools

Tool	Purpose	Reliability
calculate_metrics	24 built-in KPIs with filters & grouping	✅ 100% (deterministic SQL)
run_custom_sql	Complex ad-hoc queries (rankings, correlations)	⚠️ 85-95% (LLM-generated SQL)
search_metric	Find correct metric name from business concept	✅ 100% (alias matching)

Example Queries the Agent Handles

Simple:     "What is the total revenue?"
Filtered:   "Occupancy rate for Delhi luxury hotels in week 27"
Grouped:    "Revenue breakdown by city"
Comparison: "Weekend vs weekday ADR"
WoW:        "How did RevPAR change week over week for week 31?"
Ranking:    "Top 5 hotels by revenue in Mumbai"
Complex:    "For each city, identify the hotel with lowest RevPAR
             in week 27, show the gap to city average"

---

🔍 Anomaly Detection System

4-Layer Alert Engine

Layer 1: THRESHOLD ALERTS
  └─ Each KPI checked against configurable warning/critical levels
     Example: Occupancy < 45% → 🔴 Critical

Layer 2: WEEK-OVER-WEEK ALERTS
  └─ Detects significant WoW drops
     Example: Revenue dropped -12% WoW → 🔴 Critical

Layer 3: TREND ALERTS
  └─ Detects consecutive declining weeks
     Example: ADR declined 4 weeks straight → 🔴 Critical

Layer 4: PROPERTY ANOMALY DETECTION (Z-Score)
  └─ Flags properties deviating from portfolio mean
     Example: Hotel X occupancy z-score = -2.3 → 🔴 Critical

Property Health Scoring

Score = Weighted average of normalized KPI performance

Weights:
  Occupancy %    → 25%
  RevPAR         → 25%
  Avg Rating     → 20%
  ADR            → 15%
  Realisation %  → 15%

Score ≥ 80 → 🟢 Healthy
Score 60-79 → 🟡 Concern
Score < 60  → 🔴 Critical

---

🛠️ Tech Stack

Component	Technology
Frontend	Streamlit 1.30+
Visualization	Plotly (interactive charts)
Database	Supabase (managed PostgreSQL)
ETL	Python + pandas + psycopg2
AI/LLM	LiteLLM + OpenRouter (model-agnostic)
LLM Model	Grok 4.1 Fast (swappable)
SQL Builder	Custom deterministic engine
Deployment	Streamlit Community Cloud
Version Control	Git + GitHub

---

🔒 Security

• Database credentials stored as environment secrets (never in code)
• Streamlit Cloud secrets encrypted at rest
• Read-only database user recommended for production
• SQL injection prevention: parameterized queries + SELECT/WITH-only enforcement
• Both fact tables direct-join blocked to prevent data corruption

---

📈 Performance

Metric	Value
KPI card load time	~2-3 seconds (6 metrics × individual queries)
Agent response time	3-8 seconds (depends on query complexity)
Dashboard full render	~5 seconds (with caching)
Cache TTL	60 seconds (metrics), 300 seconds (context)
Max agent iterations	5 tool calls per question
Supported concurrent users	Limited by Streamlit Cloud free tier

---

🧪 Testing & Validation

All KPI calculations verified against a Power BI dashboard built on the same dataset.

Test Category	Queries Tested	Pass Rate
Single KPI (no filter)	24	100%
Single KPI + filters	50+	100%
Multi-metric grouped	30+	100%
WoW calculations	12	100%
Cross-table (RevPAR)	15	100%
Complex rankings (LLM SQL)	10	~85%

---

🗺️ Roadmap

• Conversation memory for multi-turn chat
• SQL validation layer for LLM-generated queries
• Export query results as CSV/Excel
• Scheduled KPI monitoring with email alerts
• LLM fallback chain (try multiple models)
• Query logging and analytics
• Role-based access control
• Mobile-responsive dashboard

---

📄 License

This project is licensed under the MIT License — see the LICENSE file for details.

---

Built with ❤️ for data-driven hospitality management