IIS Web Log Data Warehouse (Python + PostgreSQL + Tableau)

This repository contains the design and implementation of a Business Intelligence Data Warehouse for analyzing IIS web server logs. The project migrates legacy log analysis into a modern Star Schema architecture using a Hybrid ETL pipeline (Python & PostgreSQL).

Key Features:

• Star Schema Design: Optimized Fact/Dimension tables for OLAP performance.
• Hybrid ETL: Python (Pandas) for complex transformations, PostgreSQL for storage.
• Retroactive Crawler Detection: Algorithms to identify and flag bot traffic (based on robots.txt access patterns).
• Geolocation Enrichment: Offline IP-to-City mapping using MaxMind GeoLite2.
• Interactive Dashboards: Tableau visualizations for traffic, latency, and user agent analysis.

Project Structure

• docs/: Detailed design reports (Sun Model & Implementation).
• etl/: Python scripts for log parsing and data loading.
• sql/: DDL scripts for creating the PostgreSQL Star Schema.
• tableau/: The final dashboard file (.twb).

Setup & Installation

To respect data privacy and licensing, the raw log files and GeoLite2 database are not included in this repo. Follow these steps to reproduce the build:

1. Prerequisites

• Python 3.9+
• PostgreSQL (Local instance or Docker container)
• Tableau Desktop (or Public)

2. Install Dependencies

pip install pandas sqlalchemy psycopg2-binary user-agents geoip2

3. Database Setup

1. Create a local PostgreSQL database named warehouse_db.
2. Run the SQL script to create the schema:

psql -d warehouse_db -f sql/star_schema.sql

(Alternatively, the Python script handles table creation automatically via SQLAlchemy).

4. Required Assets (Not in Repo)

1. Raw Logs: Place your IIS log files (.log) in the etl/ directory.
2. Geolocation DB: Download the free GeoLite2 City database (.mmdb) from MaxMind and place it in the etl/ directory.

5. Run the ETL Pipeline

Update the DB_CONN string in etl_pipeline.py to match your local Postgres credentials, then run:

python etl/etl_pipeline.py

This will parse the logs, perform geo-lookups, detect crawlers, and load the data into PostgreSQL.

📊 Analytics & Visualizations

The Tableau workbook (tableau/WebAnalytics_Dashboard.twb) connects to the fact_web_visits table. It answers key business questions such as:

• Traffic Quality: Toggles to filter out "Crawler" vs "Human" traffic.
• Global Reach: Heatmaps of user activity by City and Country.
• Performance: Analysis of server response times (time-taken) during peak hours.

📝 Design Decisions

• Why Python? Chosen over SSIS for its superior handling of unstructured text (User Agents) and third-party library support for Geolocation.
• Why Star Schema? A standard dimensional model was chosen to ensure compatibility with BI tools and to optimize aggregation queries.
• Crawler Logic: Implemented a "Look-Ahead" set-based algorithm. If an IP requests robots.txt at any point, all sessions from that IP are retroactively flagged as is_crawler=True.

---