V tomto článku nájdete stručný návod na transformáciu dát v programe Microsoft Excel. Dáta pre projekt, môžeme mať z viacerých zdrojov alebo môžu byť vygenerované.
Opis dát
Naše ukážkové dáta pochádzajú z transakčných informačných systémov, kde záznamy reprezentujú nákupy zákazníkov v sieti predajní. Sieť predajní je zameraná na predaj drobného kovového materiálu, náradia a stavebného materiálu. Z transakčných systémov sme získali nasledujúce dáta.
| ID | PRODUKT | CENA | DATUM | SKUPINA | NAZOV_PREDAJNE | OKRES | KRAJ |
|---|---|---|---|---|---|---|---|
| 295 | podlozka M12 | 7,5 | 1.1.2004 | spotrebny material | MIBU zeleziarstvo | Banská Bystrica | Banskobystricky kraj |
| 296 | hmozdina M8 | 0,5 | 1.1.2004 | spotrebny material | MIBU zeleziarstvo | Banská Bystrica | Banskobystricky kraj |
| 297 | hmozdina M10 | 15,9 | 1.1.2004 | spotrebny material | MIBU zeleziarstvo | Banská Bystrica | Banskobystricky kraj |
| 298 | samorezna skrutka M10 | 4 | 1.1.2004 | spotrebny material | MIBU zeleziarstvo | Banská Bystrica | Banskobystricky kraj |
| 299 | samorezna skrutka M12 | 5,1 | 1.1.2004 | spotrebny material | Zeleziarstvo | Roznava | Kosicky kraj |
Dáta je možné stiahnuť tu. Dáta sú zložené z:
- hlavného identifikátora transakcie
- názvu produktu
- ceny produktu
- dátumu predaja
- kategórie produktu
- názvu predajne
- okresu, kde sa nachádza predajňa
- kraju, kde sa nachádza predajňa
1. Identifikácia dimenzií
Pred samotnou transformáciou dát na dimenzie a fakty potrebujeme identifikovať tieto dimenzie a ich úrovne.
Fakt - Pod pojmom fakt, rozumieme numerickú merateľnú jednotku. V našom prípade to bude cena predaného produktu. V tomto prípade máme predaje v rámci SR, ale ak by sme mali predaje napr. v rámci Európy a Ázie, tak by mali viacero mien, reprezentujúcich predaje. V takom prípade je potrebné komunikovať s manažmentom a stanoviť vhodnú menu napr. Euro.
Dimenzia - Dimenzie, sú popisy obchodnej činnosti, obsahujú logicky alebo hierarchicky usporiadané údaje.Medzi základné dimenzie podnikových MIS patria časové, priestorové a produktové dimenzie.
Náš dataset obsahuje dimenziu produktov, dimenziu predajní, dimenziu lokalít týchto predajní a dimenziu dátumov. Pri dimenzii produktov bude na najnižšej úrovni názov samotného produktu. Vyššie bude kategória, do ktorej produkt patrí. Dimenziu predajní budú tvoriť len názvy týchto obchodných reťazcov. Pri miestach, kde sa nachádzajú predajne, bude na najnižšom mieste okres, nad ním bude kraj. Mohli by sme pridať napr. úroveň Cele Slovensko. V dimenzií dátum, bude najnižšia úroveň samotný deň predaja. Ďalej vieme z dátumu vyextrahovať:
- pracovný deň alebo víkend
- týždeň v mesiaci
- mesiac
- kvartál
- polrok
- rok
- desaťročie
Dataset obsahuje menšie množstvo údajov. Postačia nám tieto úrovne deň, mesiac, kvartál a rok. Fakt máme len jeden a to cena predaného produktu. Pri zložitejších datasetoch sa stretneme aj s cenou prepravy, množstvom, zľavou a pod. Z týchto dát by sme potom vedeli vytvoriť viacero faktov ako profit.
2. Transformácia dát
Ak máme identifikované dimenzie a fakty, prejdeme k transformácií dát.
2.1 Dimenzia produktov
Hierarchia tejto dimenzie pozostáva zo samotného produktu a skupiny produktov. V programe Excel si vytvoríme nový hárok, pomenujeme ho produkty. Do bunky A1 si vložíme popis ID_PRODUKTU. Do stĺpca B vložíme najjemnejšiu úroveň hierarchie. V prípade tejto dimenzie to je samotný produkt. Skopírujeme teda stĺpec PRODUKT z hárka transakcie a vložíme ho do stĺpca B hárku produkty. Rovnako vložíme aj stĺpec SKUPINA. Medzivýsledok vidíme nižšie.
Začiatok dimenzie PRODUKTY
Odstránenie duplicity záznamov
Označíme stĺpec B, v hornom menu prejdeme do karty Údaje, kde nájdeme možnosť Odstrániť duplicity alebo vyhľadáme pomocou vyhľadávania.
Odstránenie duplikátov – krok 1
V novom okne, necháme možnosť Rozšíriť výber a klikneme na Odstrániť duplicity.
Odstránenie duplikátov – krok 2
V ďalšom kroku, môžeme zrušiť výber pre stĺpec ID_PRODUKTU a klikneme na OK. Zrušenie výberu je potrebné iba ak by boli v stĺpci hodnoty.
Odstránenie duplikátov – krok 3
Zoradenie záznamov
Po odstránení duplikátov, potrebujeme zoradiť tento zoznam. Označíme stĺpec B a v karte Údaje nájdeme tlačidlo Zoradiť od A po Z alebo vo vyhľadávači nájdeme Zoradiť vzostupne.
Zoradenie – krok 1
Otvorí sa nové okno, v ktorom len klikneme na Zoradiť.
Zoradenie – krok 2
Máme zoradený zoznam produktov a skupín do ktorých patria. Nakoniec len vygenerujeme identifikátory. Do bunky A2 vložíme číslo 1 a do bunky A3 vložíme číslo 2. Označíme bunky A2 a A3, kurzorom prejdeme do spodného pravého rohu, kurzor sa zmení na symbol +. Pomocou dvojkliku sa vygeneruje postupnosť čísel až po koniec hárka.
Vygenerovanie identifikátorov
Výsledok sú identifikátory, až po koniec hárku.
Výsledná dimenzia PRODUKTY
Takto upravená dimenzia je pripravená na vloženie do databázy. Tento postup opakujeme aj pre dimenziu predajní a miest.
2.2 Dimenzia predajní
Výsledná dimenzia je zobrazená na obrázku nižšie. Postup je rovnaký ako pri dimenzií produktov.
Postup je rovnaký ako pri dimenzií produktov.
Výsledná dimenzia PREDAJNE
2.3 Dimenzia miest
Znovu postupujeme ako pri dimenzií produktov. Najprv vkladáme najmenší celok. V tomto prípade je najmenší okres a potom nasleduje kraj. Výslednú dimenziu vidíme na obrázku nižšie.
Výsledná dimenzia MIEST
2.4 Časová dimenzia
V tejto dimenzií máme dostupný len dátum. Ale samotný dátum obsahuje množstvo informácií, ktoré vieme extrahovať. Začneme vložením stĺpca dátum do nového hárka. Vymažeme duplikáty, prípadne zoradíme. Vytvoríme si hlavičku pre väčšie časové celky. To znamená že nám pribudne MESIAC, KVARTAL a ROK. Tieto hodnoty vieme ručne vložiť alebo môžeme použiť funkcie Excelu.
Mesiace
Na tvorbu popisov existuje viacero spôsobov. Najjednoduchší spôsob je, že pomocou priradenia hodnoty bunke =B2, vložíme dátum do bunky a potom pravým tlačidlom myši otvoríme zoznam a vyberieme
položku Formátovať bunky. V zozname prejdeme do Vlastné a vložíme formát:
1
[$-sk-SK]mmmm-yyyy;@
Tento formát reprezentuje slovenský názov mesiaca a rok. Výsledná hodnota pre 1.1.2004 bude január-2004. Tieto texty vieme následne upravovať napr. pomocou funkcií ako UPPER alebo PROPER.
Kvartály
Aj pre kvartály máme viacero možností ako ich získať. Stačí napríklad vložiť tento formát:
1
="Q" &INT((MONTH(B2)+2)/3) & "_" & YEAR(B2)
Získame tak texty ako Q1_2004. Bunka B2 reprezentuje prvý výskyt dátumu, z ktorého chceme extrahovať tieto údaje.
& (Ampersand) predstavuje
ANDpri spájaní reťazcov
Roky
Pre roky môžeme použiť časť vyššie použitého výrazu =YEAR(B2) alebo môžeme si zvoliť vlastný formát cez Formátovanie buniek.
Tvorba úrovní v dimenzii DATUM
2.5 Tabuľka faktov
Vytvoríme nový hárok, napr. s názvom fakt. Stĺpec A bude reprezentovať identifikátory transakcií. Do stĺpca B vložíme merateľný fakt. V našom prípade to je stĺpec s názvom CENA. Ďalšie stĺpce pomenujeme rovnako ako stĺpce identifikátorov v našich dimenziách. Predpripravená tabuľka je obrázku nižšie.
Prázdna tabuľka faktov
Pre nájdenie cudzích kľúčov z dimenzií použijeme funkciu LOOKUP v tvare:
1
=LOOKUP(transakcie!G2;miesta!B:B;miesta!A:A)
Kde hodnoty oddeľujeme bodkočiarkou. Prvá hodnota je názov miesta, ktorého ID chceme nájsť, druhá hodnota je zoznam usporiadaných miest (celý stĺpec B hárku miesta) a tretia hodnota je ID, ktoré chceme vložiť to tabuľky faktov ak sa táto hodnota nájde (stĺpec A hárku miesta).
Prvá hodnota v transakciách v bunke G2 je Detva. Funkcia tento názov nájde v hárku miesta a priradí mu hodnotu 7 z vygenerovaných identifikátorov. Táto funkcia by mohla vrátiť aj samotný názov, ak by sme tretiu hodnotu zmenili z A na stĺpec B. Zvyšné hodnoty doplníme dvojklikom na symbol + označenej bunky.
Rovnako budeme postupovať aj pri iných dimenziách. Vyhľadávame najmenšiu úroveň v rámci hierarchie. Označovanie celých stĺpcov aj s hlavičkou nie je problém, keďže v transakciách nemáme, napr. produkt s názvom PRODUKT tak mu nebude priradená hodnota ID_PRODUKTU. Výsledná tabuľka faktu je zobrazená nižšie.
Výsledná tabuľka faktov
Záver
Takto upravené dáta vieme importovať do databázy. Niekedy je potrebné hárky rozdeliť do samostatných súborov a následne ich importovať do databázy. Pri kopírovaní dát, je potrebné používať vlastnosť Excelu kopírovať hodnoty buniek. Je to z toho dôvodu, že prednastavené kopírovanie kopíruje funkciu v danej bunke a to by vyvolalo nesprávne hodnoty alebo chyby (#ODKAZ!) v novom súbore.