---
# System prepended metadata

title: IDH Plan

---

### 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:
![image](https://hackmd.io/_uploads/rksqvopAT.png)
![image](https://hackmd.io/_uploads/rJlsPia0a.png)
![image](https://hackmd.io/_uploads/HkHjDjT06.png)

#### 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

![image](https://hackmd.io/_uploads/SJcVAjF1A.png)
```
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

![image](https://hackmd.io/_uploads/HkizjhY1R.png)
```
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: 
![image](https://hackmd.io/_uploads/Bkq8PTPNA.png)
(TotalRentals i RentalCount są w zasadzie zdublowane i oba to count of rows)
##### miary: 
![image](https://hackmd.io/_uploads/H1kjw6vVR.png)
##### hierarchia: brak
#### **Wymiar MyUser (Użytkownik)**:
##### atrybuty:
![image](https://hackmd.io/_uploads/B1qdPaPV0.png)
##### miary: 
![image](https://hackmd.io/_uploads/H1leuTwVA.png)
##### hierarchia: brak

#### **Wymiar Scooter (Skuter)**:
##### atrybuty:
![image](https://hackmd.io/_uploads/Skl_vaP40.png)
##### miary: 
![image](https://hackmd.io/_uploads/SJCe_6wV0.png)
##### hierarchia: brak


#### **Wymiar MyTime (Czas)**:
##### atrybuty:
![image](https://hackmd.io/_uploads/S1DYDpD4A.png)
##### miary:
![image](https://hackmd.io/_uploads/SJbMu6PNA.png)
##### hierarchia: 
![image](https://hackmd.io/_uploads/HkRYvpw4R.png)



### Inne screeny

#### widok Solution Explorer
![image](https://hackmd.io/_uploads/B1FTtpw4C.png)


#### Sprawdzenie czy Analysis Service działa (były problemy przy połączeniu po service account)
![image](https://hackmd.io/_uploads/Bk1XNAX4C.png)
![image](https://hackmd.io/_uploads/SJgj_TDVC.png)

#### Przykładowe prawidłowe przeprocesowanie kostki: 
![image](https://hackmd.io/_uploads/SJ9ZSR7EC.png)

#### Przykładowe dodawanie brakujących atrybutów:
![image](https://hackmd.io/_uploads/r11R5Rm4C.png)


### Utworzone miary kalkulowane:
![image](https://hackmd.io/_uploads/r12qQ6wVR.png)

I ich definicje: 
##### Average_Speed
![image](https://hackmd.io/_uploads/HkdFEpvEA.png)
##### Total_Traveled_Km
![image](https://hackmd.io/_uploads/SkzqVTwE0.png)
##### AverageTraveledKm
![image](https://hackmd.io/_uploads/HJ_5Nav4R.png)
##### Total_Length (w minutach)
![image](https://hackmd.io/_uploads/rJWo4aPEC.png)
#####  Average_Rentals_Per_Day
![image](https://hackmd.io/_uploads/HyPj4aPNC.png)
##### AverageLength (w minutach)
![image](https://hackmd.io/_uploads/SJRjE6PNC.png)
##### Days_Count
![image](https://hackmd.io/_uploads/HkNhV6w40.png)
##### Total_Rentals
![image](https://hackmd.io/_uploads/S1534Tv40.png)
##### Local_Travel (czy lokalne czy nie)
![image](https://hackmd.io/_uploads/B1ZpE6wNR.png)
##### Foreign_Travel (analogicznie jak wyżej)
![image](https://hackmd.io/_uploads/H1hgBaPVC.png)
##### Impact_Of_Foreign_City (współczynnik przejechanych km przyjezdni/lokalsi)
![image](https://hackmd.io/_uploads/ryzbr6D4R.png)
##### Female_Rentals
![image](https://hackmd.io/_uploads/HkdWraP4C.png)
##### Male_Rentals
![image](https://hackmd.io/_uploads/HJkGHTwEC.png)
##### Female_Male_Ratio (współczynnik wynajęć kobiet/mężczyzn)
![image](https://hackmd.io/_uploads/HJ3GHTD4R.png)



### Kilka przykładowych query MDX

#### jak zmienia się średnia prędkość jazdy w zależności od płci w poszczególnych miesiącach:
![image](https://hackmd.io/_uploads/SkYWxoP4C.png)

#### jak zmienia się średnia długość przejazdu w zależności od płci w poszczególnych miesiącach,
![image](https://hackmd.io/_uploads/HJi9_hv40.png)



#### jak zmienia się stosunek wypożyczeń kobieta/mężczyzna w zależności od pory dnia,
![image](https://hackmd.io/_uploads/ByzQF6v40.png)

#### jak zmienia się stosunek wypożyczeń grup wiekowych w zależności od pory dnia,
![image](https://hackmd.io/_uploads/HkrltTPVA.png)


#### jak zmienia się stosunek wypożyczeń kobieta/mężczyzna w poszczególnych miesiącach,
![image](https://hackmd.io/_uploads/ry6FLhvER.png)

#### jak zmienia się stosunek wypożyczeń grup wiekowych w poszczególnych miesiącach,
![image](https://hackmd.io/_uploads/B1zyt2PEC.png)

#### jak wyglądała liczba wypożyczeń poszczególnych modeli hulajnogi
![image](https://hackmd.io/_uploads/S1IIYhDER.png)
![image](https://hackmd.io/_uploads/BJzvY2DVR.png)


#### jaki wpływ ma bycie w obcym mieście na zachowania użytkowników
![image](https://hackmd.io/_uploads/BJFR5nPEC.png)
![image](https://hackmd.io/_uploads/Sku-inD4R.png)
![image](https://hackmd.io/_uploads/rklzinwNA.png)


## KPI: 
![image](https://hackmd.io/_uploads/H12rW6vEA.png)

### 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
![image](https://hackmd.io/_uploads/SyzSMaPER.png)
![image](https://hackmd.io/_uploads/rk6Bf6PEC.png)
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.
![image](https://hackmd.io/_uploads/rJN4ZavVC.png)
![image](https://hackmd.io/_uploads/rJudW6DER.png)
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.
![image](https://hackmd.io/_uploads/r1XBWaD4R.png)
![image](https://hackmd.io/_uploads/rJPwWpDEC.png)
Niestety, jak widać firma nie domaga i nie osiągają obecnie swojego zamierzonego celu. 



