### Zespół
Aleksander Błaszkiewicz s29711
Maciej Adryan s29725
### Ogólny opis projektu

Firma zajmuje się wypożyczaniem hulajnóg. Szefostwo zauważyło pewne trendy w wypożyczeniach. Hulajnogi wieczorem niechętnie wypożyczają kobiety i osoby starsze z uwagi na strach przed swoim bezpieczeństwem. Firma chce stworzyć akcje reklamowe i ulepszenia hulajnów, które mają to zmienić. W tym celu chce stworzyć hurtownie danych, która pomoże odpowiedzieć na te pytania.

### Wymagania biznesowe

~~- jak zmienia się średnia prędkość jazdy w zależności od płci w poszczególnych miesiącach,~~
~~- jak zmienia się średnia długość przejazdu w zależności od płci w poszczególnych miesiącach,~~
~~- jak zmienia się stosunek wypożyczeń kobieta/mężczyzna w zależności od pory dnia,~~
- jak zmienia się stosunek wypożyczeń grup wiekowych w zależności od pory dnia,
~~- jak zmienia się stosunek wypożyczeń kobieta/mężczyzna w poszczególnych miesiącach,~~
- jak zmienia się stosunek wypożyczeń grup wiekowych w poszczególnych miesiącach,
- jak wyglądała liczba wypożyczeń poszczególnych modeli hulajnogi,
- jaki wpływ ma bycie w obcym mieście na zachowania użytkowników.

### Opis źródeł

#### Baza OLTP
Zakładamy, że aktualne dane na temat wypożyczeń znajdują się w bazie OLTP. Zgodnie z tym, co Pani mówiłem są to dane generowane sztucznie, jako że robiliśmy bardzo podobny projekt na Politechnice Gdańskiej na przedmiocie o nazwie "Hurtownie Danych".

Dane ułożone są tak, jakby pochodziły z bazy relacyjnej, a dostępne są w plikach CSV.

Encje:
- hulajnoga,
- wypożyczenie,
- użytkownik,

Przykład, jak ułożone są dane:

#### Generowanie danych

Generator napisany jest tak, żeby dane były ciekawe. Oto przykład konfiguracji generatora dla miesiąca styczeń

const januaryDistribution: MonthSettingsDistribution = {
    user: {
        ageDistribution: {
            [AgeGroup.YOUNG]: 20,
            [AgeGroup.ADULT]: 60,
            [AgeGroup.OLD]: 20,
        },
        genderDistribution: {
            [Gender.MAN]: 80,
            [Gender.WOMAN]: 20,
        },
    },
    scooter: {
        duration: {
            short: 0.3,
            medium: 0.4,
            long: 0.3,
        },
        model: {
            A: 1,
            B: 0,
            C: 0,
            D: 0,
        },
    },
};

Dalsze miesiące dobrane są w taki sposób, żeby odpowiedzi na pytania zadane w sekcji biznesowej były ciekawe i żeby zachowania użytkowników rzeczywiście się zmieniały.
## Schemat danych
### Schemat bazy OLTP


Table Scooter {
  id integer [primary key]
  model varchar
}

Table Rental {
  id integer [primary key]
  scooterId varchar
  userId varchar
  startTime datetime
  endTime datetime
  city varchar
}

Table User {
  id integer [primary key]
  name varchar
  surname varchar
  dateOfBirth datetime
  gender varchar
  cityOfResidence varchar
}

Ref: Rental.scooterId - Scooter.id
Ref: Rental.userId - User.id

### Schemat hurtowni


Table Time {
  id number
  day number
  month number
  year number
  // morning, afternoon, evening. Computed from Rental.startDate and Rental.endDate
  timeOfDay varchar 
}

Table Rental {
  id number
  timeId number
  scooterId number
  userId number
  // true or false depending on whether city of rental is actual city of residence of the user. Computed from Rental.city and User.city
  local boolean 
}

Table Scooter {
  id number
  model varchar
}

Table User {
  id number
  // young, adult, old computed from User.birthOfDate
  ageGroup varchar 
}

Ref: Rental.timeId - Time.id 
Ref: Rental.scooterId - Scooter.id
Ref: Rental.userId - User.id

### Komentarz

Pola, które trzeba zmienić w trakcie przenoszenia danych z OLTP do hurtowni:
- data urodzenia - nastąpi przekształcenie z daty urodzenia na kategorie ["young", "adult", "old"] dla poprzeczek wiekowych [0-19, 20-49, 50-],
- pora dnia. Z czasu wypożyczenia nastąpi przekształcenie na kategorie ["morning", "afternoon", "evening"] dla zakresów [0-12,12-16,16-24],
- miasto - należy zaklasyfikować, czy wypożyczenie odbywa się w tym mieście, z którego pochodzi użytkownik. Będzie to pole typu boolean w fakcie "Rental".

### ETL

Zaczęliśmy od stworzenia `docker-compose.yml`, który włącza bazkę

version: "3.7"

services:
    mssql:
        image: mcr.microsoft.com/mssql/server:2019-latest
        container_name: mssql_server
        environment:
            SA_PASSWORD: "Password1!"
            ACCEPT_EULA: "Y"
            MSSQL_PID: "Express"
            MSSQL_DATABASE: "default"
        ports:
            - "1433:1433"
        volumes:
            - ./data:/data

Zamontowant jest volume `./data`, w którym znajdują się `rentals.csv`, `users.csv` oraz `scooters.csv` jak opisane wyżej w dokumencie.

Przygotowliśmy serię plików SQL, które trzeba odpalić, żeby stworzyć i uzupełnić całą bazę

`scaffold.sql` - plik tworzy strukturę bazy

CREATE TABLE Scooter (
    id VARCHAR(50) PRIMARY KEY,
    model VARCHAR(255)
);

CREATE TABLE MyUser (
    id VARCHAR(50) PRIMARY KEY,
    ageGroup VARCHAR(50), -- young (<18), adult (<60), old (>=60)
    city VARCHAR(50)
);

CREATE TABLE MyTime (
    id VARCHAR(50) PRIMARY KEY,
    day INT,
    month INT,
    year INT,
    timeOfDay VARCHAR(50)
);

CREATE TABLE Rental (
    id VARCHAR(50) PRIMARY KEY,
    scooterId VARCHAR(50),
    userId VARCHAR(50),
    timeId VARCHAR(50),
    isLocal BIT, -- true if city of rental is a city of user doing the rental
    FOREIGN KEY (scooterId) REFERENCES Scooter(id),
    FOREIGN KEY (userId) REFERENCES MyUser(id),
    FOREIGN KEY (timeId) REFERENCES MyTime(id),
);

`seed.sql` - plik seeduje encję `MyTime` zgodnie ze specyfikacją

WITH Dates AS (
    SELECT CAST('2024-02-01' AS DATE) AS date_column
    UNION ALL
    SELECT DATEADD(DAY, 1, date_column)
    FROM Dates
    WHERE MONTH(DATEADD(DAY, 1, date_column)) = 2
),
TimeSlots AS (
    SELECT 'morning' AS timeOfDay
    UNION ALL
    SELECT 'afternoon'
    UNION ALL
    SELECT 'evening'
)
INSERT INTO MyTime (id, day, month, year, timeOfDay)
SELECT
    NEWID(),
    DAY(d.date_column),
    MONTH(d.date_column),
    YEAR(d.date_column),
    t.timeOfDay
FROM
    Dates d
CROSS JOIN
    TimeSlots t
WHERE
    MONTH(d.date_column) IN (2, 3);
    

`load_scooters.sql` - tu wystarczy zwykły bulk load

BULK INSERT Scooter
FROM '/data/scooters.csv'  -- Provide the path to your scooters.csv file
WITH (
    FIELDTERMINATOR = ',', 
    ROWTERMINATOR = '\n', 
    FIRSTROW = 1
);

`load_users.sql` - tu bulk load + pomocnicza tabela, żeby przekształcić datę urodzenia usera na kategorię wiekową

CREATE TABLE TempUser (
    id VARCHAR(50) PRIMARY KEY,
    name VARCHAR(255),
    surname VARCHAR(255),
    birthDate DATE,
    gender VARCHAR(10),
    city VARCHAR(255)
);

BULK INSERT TempUser
FROM '/data/users.csv'
WITH (
    FIELDTERMINATOR = ',', 
    ROWTERMINATOR = '\n', 
    FIRSTROW = 1
);

INSERT INTO MyUser (id, ageGroup, city)
SELECT 
    id,
    CASE 
        WHEN DATEDIFF(YEAR, birthDate, GETDATE()) < 18 THEN 'young'
        WHEN DATEDIFF(YEAR, birthDate, GETDATE()) < 60 THEN 'adult'
        ELSE 'old'
    END AS ageGroup,
    city
FROM TempUser;

DROP TABLE TempUser;

`load_rentals.csv` - to było najbardziej problematyczne. Potrzebne były aż dwie pomocnicze tabele, żeby wszystko ze sobą spiąć

-- Create a temporary table to load data from CSV
CREATE TABLE TempRentalA (
    id VARCHAR(50) PRIMARY KEY,
    scooterId VARCHAR(50),
    userId VARCHAR(50),
    startDate DATETIME,
    endDate DATETIME,
    city VARCHAR(255)
);

CREATE TABLE TempRentalB (
    id VARCHAR(50) PRIMARY KEY,
    scooterId VARCHAR(50),
    userId VARCHAR(50),
    city VARCHAR(50),
    startDate DATE,
    timeOfDay VARCHAR(255)
);

-- Load data into temporary table
BULK INSERT TempRentalA
FROM '/data/rentals.csv'  -- Provide the path to your rentals.csv file
WITH (
    FIELDTERMINATOR = ',', 
    ROWTERMINATOR = '\n', 
    FIRSTROW = 2
);

-- Insert into Rental table with timeId and isLocal calculation
INSERT INTO TempRentalB (id, scooterId, userId, city, startDate, timeOfDay)
SELECT 
    tr.id,
    tr.scooterId,
    tr.userId,
    tr.city,
    tr.startDate,
    CASE
	    WHEN DATEPART(HOUR, tr.startDate) >= 8 AND DATEPART(HOUR, tr.startDate) < 12 THEN 'morning'
	    WHEN DATEPART(HOUR, tr.startDate) >= 12 AND DATEPART(HOUR, tr.startDate) < 16 THEN 'afternoon'
	    ELSE 'evening'
	END AS timeOfDay
FROM TempRentalA tr


INSERT INTO Rental (id, scooterId, userId, timeId, isLocal)
SELECT 
    trb.id,
    trb.scooterId,
    trb.userId,
    mt.id AS timeId,
    CASE WHEN trb.city = tu.city THEN 1 ELSE 0 END AS isLocal
FROM TempRentalB trb
JOIN MyUser tu ON trb.userId = tu.id
JOIN (
    SELECT 
        id,
        day,
        month,
        year,
        timeOfDay
    FROM MyTime
) mt ON 
    mt.year = YEAR(trb.startDate) AND
    mt.month = MONTH(trb.startDate) AND
    mt.day = DAY(trb.startDate) AND
    mt.timeOfDay = trb.timeOfDay
    
    
DROP TABLE TempRentalA;
DROP TABLE TempRentalB;

## Kostka OLAP

### Wymiary i Atrybuty:

#### **Wymiar Rental (Wynajem)**:
##### atrybuty:
(TotalRentals i RentalCount są w zasadzie zdublowane i oba to count of rows)
##### miary:
##### hierarchia: brak
#### **Wymiar MyUser (Użytkownik)**:
##### atrybuty:
##### miary:
##### hierarchia: brak

#### **Wymiar Scooter (Skuter)**:
##### atrybuty:
##### miary:
##### hierarchia: brak

#### **Wymiar MyTime (Czas)**:
##### atrybuty:
##### miary:
##### hierarchia:

### Inne screeny

#### widok Solution Explorer

#### Sprawdzenie czy Analysis Service działa (były problemy przy połączeniu po service account)

#### Przykładowe prawidłowe przeprocesowanie kostki:

#### Przykładowe dodawanie brakujących atrybutów:

### Utworzone miary kalkulowane:

I ich definicje:
##### Average_Speed
##### Total_Traveled_Km
##### AverageTraveledKm
##### Total_Length (w minutach)
##### Average_Rentals_Per_Day
##### AverageLength (w minutach)
##### Days_Count
##### Total_Rentals
##### Local_Travel (czy lokalne czy nie)
##### Foreign_Travel (analogicznie jak wyżej)
##### Impact_Of_Foreign_City (współczynnik przejechanych km przyjezdni/lokalsi)
##### Female_Rentals
##### Male_Rentals
##### Female_Male_Ratio (współczynnik wynajęć kobiet/mężczyzn)

### Kilka przykładowych query MDX

#### jak zmienia się średnia prędkość jazdy w zależności od płci w poszczególnych miesiącach:

#### jak zmienia się średnia długość przejazdu w zależności od płci w poszczególnych miesiącach,

#### jak zmienia się stosunek wypożyczeń kobieta/mężczyzna w zależności od pory dnia,

#### jak zmienia się stosunek wypożyczeń grup wiekowych w zależności od pory dnia,

#### jak zmienia się stosunek wypożyczeń kobieta/mężczyzna w poszczególnych miesiącach,

#### jak zmienia się stosunek wypożyczeń grup wiekowych w poszczególnych miesiącach,

#### jak wyglądała liczba wypożyczeń poszczególnych modeli hulajnogi

#### jaki wpływ ma bycie w obcym mieście na zachowania użytkowników

## KPI:

### Female_Male_Ratio_KPI
Ten KPI monitoruje stosunek wynajmów dokonanych przez kobiety do wynajmów dokonanych przez mężczyzn, dążąc do utrzymania równowagi między 0.9 a 1.1
Jak widać, cel nie został osiągnięty bo stosunek wynosił 0.57 (12 wynajęć przez kobiety, 21 przez mężczyzn)

### Average_Speed_KPI
Ten KPI mierzy średnią prędkość przejazdów, sprawdzając, czy mieści się w optymalnym zakresie od 2 km/h do 8 km/h.
Jak widać, mieści się w optymalnym zakresie, klienci nie są piratami drogowymi.

### Average_Rentals_Per_Day_KPI
Ten KPI śledzi średnią liczbę wynajmów na dzień, zapewniając, że jest wyższa niż 10.
Niestety, jak widać firma nie domaga i nie osiągają obecnie swojego zamierzonego celu.