Úvod

Pre svoj projekt som si vybral dataset Astrato Bike Sales, ktorý je dostupný na Snowflake Marketplace. Ide o syntetický dataset simulujúci reálne transakčné dáta maloobchodnej siete zameranej na predaj bicyklov a cyklistického vybavenia.

Dataset som zvolil kvôli jeho komplexnej relačnej štruktúre, ktorá je ideálna na demonštráciu ELT procesov. Dáta obsahujú jasné hierarchie (kategórie → subkategórie → produkty) a dostatočne podrobné transakcie na vytvorenie efektívnej Star Schemy.

Dáta podporujú biznis proces riadenia predaja. Umožňujú sledovať nákupné správanie zákazníkov, efektivitu produktového portfólia a geografickú distribúciu predajov. Analýza týchto dát je kľúčová pre optimalizáciu zásob a cielenie marketingových kampaní.

Dataset obsahuje kombináciu:

• Transakčných údajov: ceny, množstvá, zľavy a termíny objednávok
• Demografických údajov: informácie o zákazníkoch a ich segmentácii
• Produktových údajov: technické parametre, farebné vyhotovenia a hierarchická klasifikácia

Analýza je zameraná na:

1. Identifikáciu najziskovejších produktových kategórií
2. Sledovanie lojality zákazníkov a ich nákupných trendov v čase
3. Geografickú analýzu obratu

---

Staging tabuľky

Staging tabuľky predstavujú prvú vrstvu ELT procesu. Ich cieľom je načítať surové dáta zo zdrojového systému bez výrazných transformácií a pripraviť ich na ďalšie spracovanie.

Použité staging tabuľky:

• Sales – hlavná faktová tabuľka pôvodného modelu. Obsahuje záznamy o každej predanej položke, referenčné kľúče na zákazníkov a produkty, dátumy objednávok a finančné metriky.
• Products – obsahuje katalóg produktov so stĺpcami pre názov, farbu, veľkosť a nákupnú cenu. Slúži ako základ pre produktovú dimenziu.
• Customers – databáza zákazníkov obsahujúca demografické údaje ako meno, dátum narodenia, ročný príjem a geografické zaradenie.
• Geography – tabuľka definujúca lokality (mesto, štát, krajina, PSČ). Prepája zákazníkov s ich fyzickou adresou.
• Product_category – najvyššia úroveň hierarchie produktov, ktorá zjednocuje podkategórie do hlavných skupín (Bicykle, Komponenty, Doplnky).
• Product_subcategory – obsahuje podrobnejšie rozdelenie produktov (napr. Horské bicykle, Cestné bicykle, Helmy) a lokalizované názvy v španielčine a francúzštine.

---

Tabuľka faktov

FACTS_SALES uchováva kvantitatívne údaje o predajoch. Každý záznam predstavuje najnižšiu úroveň detailu – jednu položku objednávky.

Cudzie kľúče:

• CUSTOMERKEY – prepojenie na dimenziu zákazníkov
• PRODUCTKEY – prepojenie na dimenziu produktov
• ORDERDATEKEY – prepojenie na kalendárnu dimenziu
• TIMEKEY – prepojenie na časovú dimenziu (hodiny/minúty)

Hlavné metriky:

• ORDERQUANTITY – počet predaných kusov
• UNITPRICE – jednotková predajná cena
• SALESAMOUNT – hrubý výnos
• TOTALPRODUCTCOST – celkové náklady na produkt
• TAXAMT / FREIGHT – daňové zaťaženie a náklady na dopravu

Dopočítané metriky:

• NET_PROFIT – vypočítaný čistý zisk
• CUMULATIVE_CUSTOMER_SALES – kumulatívny obrat zákazníka
• DAILY_SALE_RANK – poradie predajov v rámci dňa podľa ich finančnej sily

---

Dimenzionálne tabuľky

• DIM_PRODUCTS – denormalizované údaje o produktoch, vrátane názvov modelov, farieb, veľkostí a kompletnej hierarchie (Subcategory a Category), vrátane jazykových mutácií názvov kategórií.
  Vzťah k faktom: 1:N
  Typ SCD: Typ 1 (prepísanie hodnôt pri zmene atribútu, história sa neeviduje)

• DIM_CUSTOMERS – profilové údaje o zákazníkoch (meno, vek, príjem, povolanie) integrované s geografickými údajmi (mesto, štát, krajina).
  Vzťah k faktom: 1:N
  Typ SCD: Typ 2 (pri zmene bydliska sa vytvára nový záznam s novým kľúčom)

• DIM_DATE – kalendárne atribúty odvodené z dátumu objednávky (deň, deň v týždni, mesiac, rok, kvartál, ročné obdobie).
  Vzťah k faktom: 1:N
  Typ SCD: Typ 0 (statické dáta)

• DIM_TIME – rozdelenie dňa na časové úseky (hodina, minúta, sekunda, AM/PM indikátor).
  Vzťah k faktom: 1:N
  Typ SCD: Typ 0 (fixná časová os)

---

ELT

• Databáza: BIKE_SALES__SAMPLE_DASHBOARD_SYNTHETIC_DATA
• Schéma: BIKES_SALES

Tvorba staging tabuliek a naplnenie

-- Nastavenie prostredia
USE WAREHOUSE CAMEL_WH;
USE DATABASE CAMEL_DB;
CREATE SCHEMA IF NOT EXISTS zaverecny_projekt;

-- Vytvorenie staging tabuliek
CREATE OR REPLACE TABLE products_staging AS
SELECT * FROM BIKE_SALES__SAMPLE_DASHBOARD_SYNTHETIC_DATA.BIKES_SALES."11_PRODUCTS";

CREATE OR REPLACE TABLE sales_staging AS
SELECT * FROM BIKE_SALES__SAMPLE_DASHBOARD_SYNTHETIC_DATA.BIKES_SALES."1_SALES";

CREATE OR REPLACE TABLE customers_staging AS
SELECT * FROM BIKE_SALES__SAMPLE_DASHBOARD_SYNTHETIC_DATA.BIKES_SALES."2_CUSTOMERS";

CREATE OR REPLACE TABLE geography_staging AS
SELECT * FROM BIKE_SALES__SAMPLE_DASHBOARD_SYNTHETIC_DATA.BIKES_SALES."3_GEOGRAPHY";

CREATE OR REPLACE TABLE subcategory_staging AS
SELECT * FROM BIKE_SALES__SAMPLE_DASHBOARD_SYNTHETIC_DATA.BIKES_SALES."5_PRODUCTSUBCATEGORY";

CREATE OR REPLACE TABLE category_staging AS
SELECT DISTINCT PRODUCTCATEGORYKEY,
    CASE
        WHEN PRODUCTCATEGORYKEY = 1 THEN 'Bikes'
        WHEN PRODUCTCATEGORYKEY = 2 THEN 'Components'
        WHEN PRODUCTCATEGORYKEY = 3 THEN 'Clothing'
        WHEN PRODUCTCATEGORYKEY = 4 THEN 'Accessories'
    END AS PRODUCT_CATEGORY
FROM BIKE_SALES__SAMPLE_DASHBOARD_SYNTHETIC_DATA.BIKES_SALES."5_PRODUCTSUBCATEGORY"
ORDER BY 1;

ALTER TABLE category_staging ADD COLUMN SP_PRODUCT_CATEGORY VARCHAR(30);
ALTER TABLE category_staging ADD COLUMN FR_PRODUCT_CATEGORY VARCHAR(30);

UPDATE category_staging
SET SP_PRODUCT_CATEGORY = CASE
        WHEN PRODUCTCATEGORYKEY = 1 THEN 'Bicicletas'
        WHEN PRODUCTCATEGORYKEY = 2 THEN 'Componentes'
        WHEN PRODUCTCATEGORYKEY = 3 THEN 'Ropa'
        WHEN PRODUCTCATEGORYKEY = 4 THEN 'Accesorios'
    END,
    FR_PRODUCT_CATEGORY = CASE
        WHEN PRODUCTCATEGORYKEY = 1 THEN 'Vélos'
        WHEN PRODUCTCATEGORYKEY = 2 THEN 'Composants'
        WHEN PRODUCTCATEGORYKEY = 3 THEN 'Vêtements'
        WHEN PRODUCTCATEGORYKEY = 4 THEN 'Accessoires'
    END;

Tvorba a naplnenie dim tabuliek

-- Dimenzionálna tabuľka času
CREATE OR REPLACE TABLE DIM_TIME AS
SELECT TIMEKEY,
       TIME_FROM_PARTS(HOUR, MINUTE, 0) AS TIME,
       MINUTE,
       HOUR,
       CASE WHEN HOUR < 12 THEN 'AM' ELSE 'PM' END AS AM_PM
FROM (
    SELECT DISTINCT CAST(TO_CHAR(ORDERTIMESTAMP, 'HH24MI') AS INT) AS TIMEKEY,
           MINUTE(ORDERTIMESTAMP) AS MINUTE,
           HOUR(ORDERTIMESTAMP) AS HOUR
    FROM sales_staging
);

-- Dimenzionálna tabuľka produktov
CREATE OR REPLACE TABLE DIM_PRODUCTS AS
SELECT p.PRODUCTKEY,
       p.PRODUCTNAME,
       p.MODELNAME,
       p.COLOR,
       p.SIZERANGE AS SIZE,
       p.WEIGHT,
       p.PRODUCTLINE,
       p.CLASS,
       (p.LISTPRICE - p.STANDARDCOST) AS PROFIT_PER_UNIT,
       s.PRODUCT_SUBCATEGORY AS SUBCATEGORYNAME,
       c.PRODUCT_CATEGORY AS CATEGORYNAME,
       c.SP_PRODUCT_CATEGORY AS CATEGORYNAME_SP,
       c.FR_PRODUCT_CATEGORY AS CATEGORYNAME_FR,
       IFNULL(p.STATUS, 'Not current') AS STATUS
FROM products_staging p
LEFT JOIN (
    SELECT DISTINCT PRODUCTSUBCATEGORYKEY, PRODUCTCATEGORYKEY, PRODUCT_SUBCATEGORY
    FROM subcategory_staging
) s ON p.PRODUCTSUBCATEGORYKEY = s.PRODUCTSUBCATEGORYKEY
LEFT JOIN category_staging c ON s.PRODUCTCATEGORYKEY = c.PRODUCTCATEGORYKEY;

-- Dimenzionálna tabuľka zákazníkov
CREATE OR REPLACE TABLE DIM_CUSTOMERS AS
SELECT c.CUSTOMERKEY,
       c.NAME,
       c.BIRTHDATE,
       c.AGE,
       c.GENDER,
       c.MARITALSTATUS,
       c.YEARLYINCOME,
       c.OCCUPATION,
       c.HOUSEOWNERFLAG,
       c.NUMBERCARSOWNED,
       CAST(c.DATEFIRSTPURCHASE AS DATE) AS DATEFIRSTPURCHASE,
       g.CITY,
       g.STATEPROVINCENAME,
       g.COUNTRY,
       g.POSTALCODE
FROM customers_staging c
LEFT JOIN (
    SELECT DISTINCT GEOGRAPHYKEY, CITY, STATEPROVINCENAME, CUSTOMER_COUNTRY AS COUNTRY, POSTALCODE
    FROM geography_staging
) g ON c.GEOGRAPHYKEY = g.GEOGRAPHYKEY;

-- Dimenzionálna tabuľka dátumu
CREATE OR REPLACE TABLE DIM_DATE AS
SELECT DISTINCT ORDERDATEKEY,
       CAST(ORDERDATE AS DATE) AS DATE,
       DAY(ORDERDATE) AS DAY,
       DAYOFWEEK(ORDERDATE) AS WEEKDAY,
       MONTH(ORDERDATE) AS MONTH,
       YEAR(ORDERDATE) AS YEAR,
       QUARTER(ORDERDATE) AS QUARTER,
       MONTHNAME(ORDERDATE) AS MONTHNAME,
       CASE WHEN DAYOFWEEK(ORDERDATE) IN (0,6) THEN 'Weekend' ELSE 'Workday' END AS DAYTYPE,
       CASE WHEN MONTH(ORDERDATE) IN (12,1,2) THEN 'Winter'
            WHEN MONTH(ORDERDATE) IN (3,4,5) THEN 'Spring'
            WHEN MONTH(ORDERDATE) IN (6,7,8) THEN 'Summer'
            ELSE 'Autumn'
       END AS SEASON
FROM sales_staging;

Tvorba a naplnenie tabuľky faktov

-- Faktová tabuľka predajov
CREATE OR REPLACE TABLE FACTS_SALES AS
SELECT RECORDKEY AS FACTS_KEY,
       SALESORDERNUMBER,
       SALESORDERLINENUMBER,
       ORDERQUANTITY,
       UNITPRICE,
       UNITPRICEDISCOUNTPCT,
       DISCOUNTAMOUNT,
       SALESAMOUNT,
       TOTALPRODUCTCOST,
       TAXAMT,
       FREIGHT,
       (SALESAMOUNT - TOTALPRODUCTCOST) AS NET_PROFIT,
       SUM(SALESAMOUNT) OVER (PARTITION BY CUSTOMERKEY ORDER BY ORDERDATE, RECORDKEY) AS CUMULATIVE_CUSTOMER_SALES,
       RANK() OVER (PARTITION BY CAST(ORDERDATE AS DATE) ORDER BY SALESAMOUNT DESC, RECORDKEY) AS DAILY_SALE_RANK,
       CUSTOMERKEY,
       ORDERDATEKEY,
       CAST(TO_CHAR(ORDERTIMESTAMP, 'HH24MI') AS INT) AS TIMEKEY,
       PRODUCTKEY
FROM (SELECT DISTINCT * FROM sales_staging) clean_sales;

Vytvorenie denormalizovanej vrstvy

-- Konečná denormalizovaná tabuľka
CREATE OR REPLACE TABLE ONE_BIG_TABLE AS
SELECT f.FACTS_KEY,
       f.SALESORDERNUMBER,
       f.SALESORDERLINENUMBER,
       f.ORDERQUANTITY,
       f.UNITPRICE,
       f.SALESAMOUNT,
       f.TOTALPRODUCTCOST,
       f.NET_PROFIT,
       f.CUMULATIVE_CUSTOMER_SALES,
       f.DAILY_SALE_RANK,
       p.PRODUCTNAME,
       p.MODELNAME,
       p.COLOR,
       p.SIZE,
       p.PRODUCTLINE,
       p.PROFIT_PER_UNIT,
       p.SUBCATEGORYNAME,
       p.CATEGORYNAME,
       p.STATUS AS PRODUCT_STATUS,
       c.NAME AS CUSTOMER_NAME,
       c.AGE,
       c.GENDER,
       c.CITY,
       c.COUNTRY,
       d.DATE AS CALENDAR_DATE,
       d.MONTHNAME,
       d.YEAR,
       d.QUARTER,
       d.DAYTYPE,
       d.SEASON,
       t.TIME AS SALE_TIME,
       t.HOUR,
       t.AM_PM
FROM FACTS_SALES f
INNER JOIN DIM_PRODUCTS p ON f.PRODUCTKEY = p.PRODUCTKEY
INNER JOIN DIM_CUSTOMERS c ON f.CUSTOMERKEY = c.CUSTOMERKEY
INNER JOIN DIM_DATE d ON f.ORDERDATEKEY = d.ORDERDATEKEY
INNER JOIN DIM_TIME t ON f.TIMEKEY = t.TIMEKEY;

---

Vizualizácia dát

Dashboard poskytuje vizuálny pohľad na kľúčové metriky predaja bicyklov. Pomocou piatich rôznych vizualizácií analyzujeme ziskovosť regiónov, demografiu zákazníkov, časovú efektivitu predajov a produktové trendy pre strategické rozhodovanie biznisu.

Graf 1: Čistý zisk podľa miest

Stĺpcový graf identifikuje geografické centrá zisku. Vizualizácia umožňuje manažmentu rýchlo určiť najvýnosnejšie pobočky alebo trhy (napr. Londýn, Paríž). Farebné rozlíšenie podľa krajín poskytuje ďalšiu informáciu o celkovej dominancii národných trhov.

-- Čistý zisk podla miest
SELECT 
    COUNTRY AS "Krajina", 
    CITY AS "Mesto", 
    ROUND(SUM(NET_PROFIT),2) as "Celkový zisk"
FROM ONE_BIG_TABLE
GROUP BY 1, 2
ORDER BY 3 DESC;

Graf 2: Počet objednávok podľa veku zákazníka

Bodový graf odhaľuje demografické zloženie zákazníckej základne. Najaktívnejšia skupina nakupujúcich sa nachádza v strednom veku (približne 30 – 50 rokov). Tento trend je kľúčový pre marketingové oddelenie pri nastavovaní cielenej reklamy a prispôsobovaní produktového portfólia.

-- Počet objednávok podľa veku zákazníka
SELECT
    AGE AS "Vek",
    COUNT(*) AS "Počet objednávok"
FROM ONE_BIG_TABLE
GROUP BY 1
ORDER BY 1;

Graf 3: Predaj podľa hodín a dní

Matica predajov poskytuje detailný pohľad na nákupné správanie v čase. Rozdelenie do časových pásiem (ráno, poobedie, noc) v kombinácii s dňami v týždni identifikuje "horúce miesta" s najvyššou intenzitou tržieb. Táto analýza pomáha optimalizovať personálne kapacity v skladoch alebo na zákazníckej podpore.

-- Predaj podľa hodín a dní
SELECT 
    CASE 
        WHEN HOUR BETWEEN 6 AND 11 THEN 'Ranné (6-11)'
        WHEN HOUR BETWEEN 12 AND 17 THEN 'Poobedné (12-17)'
        WHEN HOUR BETWEEN 18 AND 22 THEN 'Večerné (18-22)'
        ELSE 'Nočné (23-5)'
    END as "Časové pásmo",
    CASE 
        WHEN DAYOFWEEK(CALENDAR_DATE) = 1 THEN 'Pondelok'
        WHEN DAYOFWEEK(CALENDAR_DATE) = 2 THEN 'Utorok'
        WHEN DAYOFWEEK(CALENDAR_DATE) = 3 THEN 'Streda'
        WHEN DAYOFWEEK(CALENDAR_DATE) = 4 THEN 'Štvrtok'
        WHEN DAYOFWEEK(CALENDAR_DATE) = 5 THEN 'Piatok'
        WHEN DAYOFWEEK(CALENDAR_DATE) = 6 THEN 'Sobota'
        WHEN DAYOFWEEK(CALENDAR_DATE) = 0 THEN 'Nedeľa'
    END as "Deň",
    SUM(SALESAMOUNT) as "Predaj"
FROM ONE_BIG_TABLE 
GROUP BY 1,2,DAYOFWEEK(CALENDAR_DATE);

Graf 4: Top 10 produktov

Graf zobrazuje rebríček desiatich produktov s najvyššími tržbami. Dominancia modelov ako Mountain-200 naznačuje preferencie zákazníkov smerom k prémiovým bicyklom. Identifikácia týchto produktov pomáha pri riadení zásob a plánovaní promo akcií.

-- TOP 10 produktov
SELECT 
    PRODUCTNAME as "Produkt",
    SUM(SALESAMOUNT) as "Predaj"
FROM ONE_BIG_TABLE 
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;

Graf 5: Mesačný predaj po rokoch

Čiarový graf vizualizuje sezónnosť a medziročný rast tržieb (2018 – 2021). Sledovaním trendov môžeme identifikovať cyklické výkyvy, letné vrcholy predajov a prudké poklesy, ktoré môžu signalizovať externé faktory alebo dopredaj zásob. Tento pohľad je kritický pre dlhodobé plánovanie a predpovedanie finančných tokov.

-- Mesačný predaj po rokoch
SELECT 
    MONTH(CALENDAR_DATE) as "Mesiac",
    YEAR(CALENDAR_DATE) as "Rok",
    SUM(SALESAMOUNT) as "Predaj"
FROM ONE_BIG_TABLE 
GROUP BY 1, 2
ORDER BY 2, 1;

---

Autori: Matej Herich, Viktor Hutta