API-Siloed & Semi-Structured Data
LMS information was accessible only through OData feeds containing complex nested JSON arrays that could not be used directly for business reporting.
Enterprise On-Premises
Data Platform & ETL Automation
Developed a fully automated, cost-efficient enterprise LMS data platform using Python, SQL Server, optimized SQL views, Windows batch scripts, Task Scheduler, and Power BI—without relying on licensed cloud orchestration or enterprise ETL platforms.
On-Premises Platform
No Enterprise ETL Licensing Cost
2015–2026 Historical Data
Fully Automated Daily Refresh
95%+ Faster Reporting
1.
Business Challenge
The organization needed scalable, multi-year LMS reporting but lacked both a centralized warehouse and access to traditional enterprise ETL platforms.
Fragmented Multi-Year Reporting
Separate Power BI files were limited to two calendar years each, making consolidated 2015–2026 trend analysis impossible.
Enterprise ETL Tool Constraints
Organizational limits prevented use of SSIS, Azure Data Factory, Microsoft Fabric, or other standard orchestration platforms.
DirectQuery Performance Bottlenecks
Direct connections to unmodelled source data caused slow report loading, screen lag, and frequent Power BI crashes.
2.
Solution
A custom on-premises data warehouse and automated ETL framework was engineered using Python, SQL Server, SQL views, Windows batch scripts, Task Scheduler, and Power BI.
Custom Python ETL Engine
Used requests, pandas, pyodbc, and urllib3 to authenticate, extract, transform, and load enterprise LMS data.
OData Extraction & Pagination
Implemented $top, $skip, and $expand logic to retrieve large datasets, course ratings, and attendance sub-records safely.
JSON Normalization
Flattened multi-layered roles and transactional arrays into clean relational rows while preserving empty attributes.
Historical Batch Loading
Used pyodbc fast_executemany and year-by-year historical batches to process 2015–2026 data reliably.
30-Day Overlap Protection
Used the latest CreatedAt value, deleted overlapping records, and reloaded the current 30-day delta to avoid duplicate keys and missing changes.
Semantic SQL Views
Standardized strings, e-mail values, DATETIME fields, BIT fields, and reporting logic before Power BI consumption.
Power BI Star Schema
Connected Import-mode fact views for Enrollments and Attendances to conformed Users, Courses, and Course Sessions dimensions.
Windows Automation
Created executable batch scripts and scheduled the complete Python ETL workflow through Windows Task Scheduler.
3.
Business Impact
Faster Power BI Report Loading
DirectQuery reporting was replaced with a centralized SQL Server warehouse and optimized semantic layer.
Less Manual ETL Effort
Python-based extraction, transformation, batch processing, and scheduling automated routine data preparation.
Faster Daily Data Processing
Incremental loading eliminated repeated processing of the complete historical dataset.
Consolidated LMS History
Data from 2015–2026 replaced multiple year-specific reports with one enterprise reporting platform.
Centralized Single Source of Truth
A governed SQL Server warehouse improved reporting accuracy and consistency across enterprise LMS analytics.
Duplicate & Missing Records Eliminated
Overlap-window incremental loading and automated validation protected data completeness and business-key integrity.
Enterprise ETL Licensing Costs Avoided
Python, SQL Server, Windows Task Scheduler, and batch automation delivered enterprise-grade ETL without dedicated platform licensing.
Scalable Foundation Established
The reusable framework can support new data sources and increasing reporting demand without major architectural redesign.
4.
Solution Architecture
On-Premises Enterprise LMS Data Platform
LMS OData APINested JSON and Transactions
Python ETL EngineExtract, Flatten, Validate
Historical + Incremental2015–2026 and 30-Day Delta
↓↓↓
Python Processingrequests, pandas, pyodbc
→
SQL Server WarehouseCentral Enterprise Storage
→
Semantic SQL ViewsReporting-Ready Layer
↓
Power BI Import-Mode Star Schema
↓
Enterprise LMS Dashboards & Multi-Year Analytics
A local, low-cost architecture using core technologies and existing infrastructure instead of licensed cloud orchestration.
5.
Automated ETL Pipeline
The documented control flow extracts paginated OData data, flattens nested JSON, loads SQL Server production tables, applies semantic SQL views, and supplies a Power BI star schema through automated batch scheduling.
6.
Technology Stack
Pythonrequestspandaspyodbcurllib3SQL ServerSSMST-SQL ViewsOData APIJSON ParsingPower BI DesktopPower BI ServiceStar SchemaWindows Batch FilesWindows Task Scheduler
7.
Project Showcase
Live Power BI Dashboard
Replace this placeholder with the public Power BI embed URL after publishing the report.
Video Walkthrough
Replace this generic YouTube URL after publishing the walkthrough.
8.
GitHub Repository
Explore the Full Technical Implementation
The repository contains Python ETL scripts, SQL warehouse design, semantic views, Windows automation, architecture details, and project documentation.
Explore More Case Studies
Discover more enterprise data transformation and analytics projects.