Project Status: Completed (Capstone Project)
Role: Principal Data Engineer & Team Lead
An end-to-end Data Mart solution designed for the Quality Assurance and Learning Development Institute (LPMPP) at Institut Teknologi Sumatera (ITERA).
This project implements a Kimball Dimensional Modeling approach to analyze institutional performance, specifically piloting the Intellectual Property Management Center (PKKI) domain. It serves as a blueprint for integrating data across 7 different service centers within the university.
- Methodology: Kimball Dimensional Modeling (Star Schema)
- Database: SQL Server (hosted on Azure VM)
- ETL Orchestration: SQL Server Integration Services (SSIS)
- Visualization: Power BI Desktop
- Languages: T-SQL, Python
As the Team Lead and Lead Architect, I was responsible for the core engineering lifecycle:
- System Architecture: Designed the Star Schema and defined the grain for the Data Mart.
- ETL Development: Engineered complex SSIS packages to handle data extraction, transformation, and loading from raw sources into the Data Warehouse.
- Logic Implementation: Wrote the core T-SQL procedures for data staging and fact table population.
- Project Management: Led a team of 4, ensuring code quality and architectural integrity.
The system analyzes the Intellectual Property Management Center (PKKI) to track productivity and achievements.
Key Performance Indicators (KPIs):
- Intellectual Property Productivity: Tracking the volume and approval status of patents (Simple Patents vs. Patents) to evaluate and compare study programs based on the number of approved intellectual property outputs.
Scope of Data Model (Mission 1): Focused on a single Star Schema for Intellectual Property submission tracking:
- Fact Table:
Fact_PengajuanKI(Submission Transaction) - Dimensions:
Dim_Date(Temporal analysis)Dim_Inventor(Lecturer/Researcher details)Dim_JenisKI(IP Types: Patent, Copyright, etc.)Dim_Status(Workflow status: Granted, Pending, Rejected)
This repository contains the full source code for the Database, ETL pipelines, and Documentation.
├── docs/ # Business Requirements, ERD, & Mapping Docs
├── sql/ # T-SQL Scripts (DDL, Stored Procedures, Security)
├── etl/ # SSIS Packages (.dtsx) & Python Helper Scripts
├── dashboards/ # Power BI Files (.pbix)
└── presentations/ # Final Presentation Deck
For detailed technical specifications, please refer to:
This project was developed as a final assignment for the Data Warehouse Course (SD25-31007).
- Khairunnisa Maharani - Principal Engineer & Team Lead
- Ihsan M. Y. - Database Administrator
- Aprilia D. H. - Technical Writer & ETL Support
- Kharisa H. - QA Tester