Projekty

System BI dla Transportu Tramwajowego w Gdańsku

2025 · GitHub

O projekcie

Projekt grupowy realizowany w ramach przedmiotu Hurtownie Danych na Politechnice Gdańskiej. Celem było zaprojektowanie i wdrożenie kompletnego systemu Business Intelligence dla fikcyjnej organizacji transportu tramwajowego ZTM Gdańsk – od specyfikacji procesów biznesowych, przez projektowanie i implementację hurtowni danych, generowanie danych źródłowych, proces ETL, aż po zapytania analityczne MDX, dashboard Power BI i optymalizację zapytań.

Uwaga: Był to projekt grupowy. Etapy od specyfikacji procesów biznesowych po projekt i implementację hurtowni danych (etapy 1–5) były realizowane wspólnie. Proces ETL (etap 6) zrealizowałem samodzielnie w całości. Etapy końcowe – zapytania MDX, dashboard Power BI oraz optymalizacja zapytań – były realizowane przez pozostałych członków zespołu.

Opis organizacji

ZTM to fikcyjna firma odpowiedzialna za przewozy tramwajami na terenie Gdańska. Organizacja jest zainteresowana analizą opóźnień i awarii swoich tramwajów. Główne cele biznesowe to:

  • Miesięczna redukcja całkowitych opóźnień tramwajów o co najmniej 2%
  • Miesięczna redukcja całkowitej liczby awarii tramwajów o co najmniej 3%

Dane pochodzą z dwóch typów źródeł: relacyjnej bazy danych w formacie GTFS (General Transit Feed Specification – standardowy format opisujący rozkłady jazdy, trasy, przystanki i kursy transportu publicznego) oraz plików CSV zawierających dane o awariach i kierowcach. Procesy biznesowe obejmują operacyjne przejazdy tramwajów (monitorowanie czasu przyjazdu, rejestracja opóźnień) oraz obsługę serwisową (zgłaszanie i naprawa awarii).

Etapy projektu

Etap 1: Specyfikacja procesów biznesowych

Analiza organizacji ZTM i zdefiniowanie dwóch kluczowych procesów biznesowych: przejazdów tramwajów oraz obsługi serwisowej. Określenie celów mierzalnych – redukcja opóźnień o 2% i awarii o 3% miesięcznie. Sformułowanie przykładowych zapytań analitycznych dla każdego procesu.

Etap 2: Specyfikacja wymagań

Zaprojektowanie struktur dwóch źródeł danych – relacyjnej bazy danych GTFS (tabele: Agency, Routes, Calendar_Dates, Trips, Stops, Stop_Times) oraz plików CSV (malfunctions.csv, drivers.csv). Zdefiniowanie strategii integracji danych między źródłami. Sformułowanie 12 zapytań analitycznych, w tym zapytań wymagających danych z obu źródeł, zapytania wymagającego dodatkowego źródła (dane pogodowe) oraz zapytania wymagającego zmiany procesu biznesowego (dane o doświadczeniu motorniczych).

Etap 3: Generator danych

Implementacja generatorów danych w Python (pandas, numpy) do tworzenia realistycznych zbiorów danych. Generatory tworzą dane o kursach tramwajów, czasach przejazdu z opóźnieniami (uwzględniając godziny szczytu, warunki pogodowe, propagację opóźnień), awariach i kierowcach. Dane generowane w dwóch snapshotach czasowych (T1, T2) z możliwością skalowania do miliona rekordów. Ładowanie danych metodą BULK loading.

Etap 4: Projekt hurtowni danych

Zaprojektowanie hurtowni danych w schemacie gwiazdy. Centralna tabela faktów TRIP_OPERATIONS_FACT z miarami: opóźnienie, planowany czas przejazdu, opóźnienie spowodowane awarią, czas naprawy, oraz miary kalkulowane (procent punktualności, procent napraw na miejscu). Osiem tabel wymiarów:

  • DATE_DIM – wymiar hierarchiczny (Rok → Miesiąc → Dzień)
  • TIME_DIM – wymiar hierarchiczny (Kategoria pory dnia → Godzina)
  • ROUTE_DIM – linie tramwajowe
  • STOP_DIM – przystanki z lokalizacją i dzielnicą
  • DRIVER_DIM – motorniczy, SCD Type 2 (śledzenie zmian kategorii doświadczenia)
  • TRAM_DIM – pojazdy tramwajowe
  • TRIP_DIM – kursy tramwajów
  • MALFUNCTION_DIM – typy awarii (nullable)

Schemat hurtowni danych

Schemat gwiazdy hurtowni danych

Etap 5: Implementacja hurtowni danych

Implementacja schematu hurtowni w MS SQL Server – skrypty DDL tworzące tabelę faktów i tabele wymiarów, skrypty ładujące dane z bazy źródłowej i plików CSV do hurtowni z generowaniem kluczy zastępczych (surrogate keys). Weryfikacja poprawności danych i możliwości realizacji wszystkich 10 zapytań analitycznych.

Etap 6: Proces ETL (SSIS)

Implementacja procesu ETL w SQL Server Integration Services (SSIS) w Visual Studio 2022. Proces obsługuje ładowanie danych w dwóch momentach czasowych – pierwszy załadowanie pełnych danych, drugi dołączenie nowych i zaktualizowanych rekordów. Implementacja ładowania danych do wymiaru SCD Type 2 (DRIVER_DIM) z wersjonowaniem rekordów. Automatyczne przetwarzanie kostki OLAP po zakończeniu ETL.

Kostka OLAP w Visual Studio
Struktura kostki OLAP (TripOperationsCube) w Visual Studio – Data Source View z tabelą faktów i wymiarami
Proces ETL w SSIS
Control Flow procesu ETL w SSIS – sekwencja ładowania wymiarów statycznych, kierowców, awarii, tramwajów, przystanków, tras, kursów i tabeli faktów

Etap 7: Zapytania MDX i KPI

Sformułowanie i wykonanie zapytań MDX odpowiadających zapytaniom ze specyfikacji wymagań. Wykorzystanie: calculated members, klauzuli WHERE, funkcji operujących na hierarchii wymiarów (PrevMember, Children), funkcji TopCount/BottomCount, operacji numerycznych i filtrów. Implementacja KPI w Visual Studio Data Tools – m.in. KPI redukcji opóźnień porównujący bieżący miesiąc z poprzednim.

Etap 8: Dashboard Power BI

Implementacja interaktywnych raportów w Power BI – minimum 8 stron z prezentacją KPI dla różnych okresów czasowych, co najmniej 8 różnych typów wykresów, parametryzacja raportów (wybór roku, linii, przystanku). Wizualizacja trendów opóźnień, rozkładu awarii, porównań między liniami i analiz czasowych.

Etap 9: Optymalizacja zapytań

Porównanie wydajności różnych fizycznych modeli kostek OLAP (MOLAP, ROLAP, HOLAP) z agregacjami i bez. Testy obejmujące czasy wykonywania zapytań oraz czasy przetwarzania kostki. Analiza wyników i porównanie z założeniami teoretycznymi.

Stos technologiczny

  • MS SQL Server
  • SSIS (SQL Server Integration Services)
  • SSAS (SQL Server Analysis Services)
  • Visual Studio 2022
  • Power BI
  • Python (pandas, numpy)
  • MDX
  • T-SQL