Nalaganje podatkov v Qlik Sense

Vsaka Qlik aplikacija je dobra toliko, kolikor je dober njen podatkovni model. Grafe lahko popravite v minuti — napačno povezane tabele pa pomenijo napačne številke na vseh listih. Ta vodič pokaže, kako podatke naložite s skriptnim jezikom in se pri tem izognete najpogostejšim pastem.

Dva načina nalaganja

• Upravitelj podatkov (Data manager) — vizualno dodajanje podatkov z nekaj kliki. Primerno za hitre analize in manjše aplikacije.
• Urejevalnik nalaganja podatkov (Data load editor) — skripta, ki jo napišete sami. Daje popoln nadzor nad tem, kaj se naloži, kako se preoblikuje in kako se tabele povežejo.

Za vse, kar bo v produkciji dlje kot nekaj tednov, priporočam skripto. Je pregledna, ponovljiva in jo lahko komentirate.

Osnove skripte

Skripta se izvaja od zgoraj navzdol. Razdelite jo lahko na razdelke (zavihke na levi), npr. Nastavitve, Prodaja, Šifranti, Koledar. Vsak stavek se konča s podpičjem.

Osnovni stavek je LOAD. Pred njim zapišete ime tabele z dvopičjem:

// Excel datoteka iz podatkovne povezave "Podatki"
Prodaja:
LOAD
    RacunID,
    Datum,
    KupecID,
    IzdelekID,
    Kolicina,
    Znesek
FROM [lib://Podatki/prodaja.xlsx]
(ooxml, embedded labels, table is Prodaja);

lib://Podatki/ je podatkovna povezava (data connection), ki jo ustvarite v desnem panelu urejevalnika. V Qlik Cloud se datoteke, naložene v prostor, običajno nahajajo v povezavi DataFiles.

Preimenovanje in izračuni

Polja lahko že ob nalaganju preimenujete z as in jim dodate izračune:

Prodaja:
LOAD
    RacunID,
    Date(Datum)           as Datum,
    KupecID,
    IzdelekID,
    Kolicina,
    Znesek,
    Znesek * 0.22         as DDV,
    If(Znesek > 1000, 'Velik', 'Običajen') as VelikostRacuna
FROM [lib://Podatki/prodaja.xlsx]
(ooxml, embedded labels, table is Prodaja);

Podatki iz SQL baze

Pri bazah podatkov se najprej povežete, nato s SQL SELECT preberete podatke. Nad njim zapišite LOAD (t. i. preceding load), kjer podatke preoblikujete s Qlikovimi funkcijami:

LIB CONNECT TO 'ERP';

Kupci:
LOAD
    KupecID,
    Naziv                 as Kupec,
    Upper(Drzava)         as Drzava,
    PostnaSt;
SQL SELECT KupecID, Naziv, Drzava, PostnaSt
FROM dbo.Kupci
WHERE Aktiven = 1;

Kako Qlik poveže tabele

To je najpomembnejše poglavje: Qlik tabele poveže samodejno prek polj z enakim imenom. Nobenih JOIN stavkov ali relacij ne nastavljate posebej. Ker imata tabeli Prodaja in Kupci obe polje KupecID, sta povezani.

To pomeni dvoje:

• Polja, ki naj povezujejo tabele, morajo imeti točno enako ime (velike in male črke štejejo!).
• Polja, ki ne smejo povezovati, morajo imeti različna imena — tudi če v izvornih sistemih vse tabele vsebujejo npr. Opis, Status ali DatumSpremembe.

Sintetični ključi

Če imata dve tabeli več kot eno skupno polje, Qlik ustvari sintetični ključ — dodatno tabelo $Syn z vsemi kombinacijami. V pregledovalniku modela ga takoj opazite. Sintetični ključi upočasnijo aplikacijo in so skoraj vedno znak napake v modelu.

Rešitev: ohranite eno povezovalno polje in ostala preimenujte, ali pa iz več polj sestavite en ključ:

Narocila:
LOAD
    PodjetjeID & '|' & NarociloID   as %NarociloKljuc,
    PodjetjeID,
    NarociloID,
    Datum                           as DatumNarocila,
    Status                          as StatusNarocila
FROM [lib://Podatki/narocila.qvd] (qvd);

Pogosta konvencija je, da ključna polja začnete s %, da jih na prvi pogled ločite od poslovnih polj.

Krožne reference

Če se tabele povežejo v krog (A–B–C–A), Qlik eno povezavo prekine in v dnevniku nalaganja izpiše opozorilo. Rezultati so nepredvidljivi. Krog razrešite tako, da eno od polj preimenujete ali tabele združite.

Ciljna oblika: zvezdna shema

Za večino aplikacij je najboljša zvezdna shema: ena (ali nekaj) tabel z dejstvi v središču — npr. vrstice računov — in okoli nje šifranti: kupci, izdelki, koledar, prodajalci. Vsak šifrant je s tabelo dejstev povezan z enim samim ključem.

Takšen model je hiter, razumljiv in v njem se številke ne podvajajo. Podrobneje — vključno s prodajo in planom v istem modelu — v vodiču Podatkovni model v Qliku.

Preslikave z ApplyMap

Ko iz šifranta potrebujete le eno polje (npr. regijo glede na poštno številko), ga ne nalagajte kot ločene tabele — uporabite preslikavo. Preslikovalna tabela ima natanko dva stolpca in po koncu skripte samodejno izgine:

Map_Regija:
MAPPING LOAD
    PostnaSt,
    Regija
FROM [lib://Podatki/poste.xlsx]
(ooxml, embedded labels, table is Poste);

Kupci:
LOAD
    KupecID,
    Naziv                                           as Kupec,
    PostnaSt,
    ApplyMap('Map_Regija', PostnaSt, 'Neznana')     as Regija
FROM [lib://Podatki/kupci.qvd] (qvd);

Tretji argument ('Neznana') je privzeta vrednost, kadar poštne številke v preslikavi ni. Za razliko od JOIN preslikava nikoli ne podvoji vrstic.

Glavni koledar

Datum v tabeli dejstev je le en stolpec. Za analize po letih, četrtletjih in mesecih ustvarite ločeno tabelo — glavni koledar (master calendar), ki vsebuje vsak dan med najmanjšim in največjim datumom, tudi dneve brez prodaje:

// 1. Poiščemo razpon datumov
TmpRazpon:
LOAD
    Min(Datum) as MinDatum,
    Max(Datum) as MaxDatum
RESIDENT Prodaja;

LET vMin = Num(Peek('MinDatum', 0, 'TmpRazpon'));
LET vMax = Num(Peek('MaxDatum', 0, 'TmpRazpon'));
DROP TABLE TmpRazpon;

// 2. Ustvarimo vrstico za vsak dan
Koledar:
LOAD
    Datum,
    Year(Datum)                     as Leto,
    Month(Datum)                    as Mesec,
    'Q' & Ceil(Month(Datum) / 3)    as Cetrtletje,
    Date(MonthStart(Datum), 'MM.YYYY') as LetoMesec,
    Week(Datum)                     as Teden;
LOAD
    Date($(vMin) + IterNo() - 1)    as Datum
AUTOGENERATE 1
WHILE $(vMin) + IterNo() - 1 <= $(vMax);

Koledar se s prodajo poveže prek polja Datum. SET spremenljivki dodeli besedilo dobesedno, LET pa izraz najprej izračuna — zato tu uporabljamo LET. Razširjen koledar s polji za primerjave obdobij najdete v vodiču Glavni koledar in časovne primerjave.

Shranjevanje v QVD

QVD je Qlikov lasten format datotek, ki se naloži izjemno hitro. Uveljavljena praksa je nalaganje v slojih:

1. Ekstrakcija — ena aplikacija prebere podatke iz virov in jih nespremenjene shrani v QVD.
2. Transformacija — druga aplikacija iz teh QVD-jev pripravi očiščene tabele in jih spet shrani v QVD.
3. Prikaz — končne aplikacije za uporabnike le naložijo pripravljene QVD-je.

STORE Prodaja INTO [lib://Podatki/QVD/Prodaja.qvd] (qvd);
DROP TABLE Prodaja;

Tako baze ne obremenjujete z vsako aplikacijo posebej, poslovno logiko pa zapišete enkrat. Kako iz baze brati le nove in spremenjene vrstice, opisuje vodič Inkrementalno nalaganje in QVD.

Razhroščevanje

• Omejeno nalaganje — v razhroščevalniku (Debug) nastavite, da se iz vsakega vira naloži npr. le 100 vrstic. Napake najdete v sekundah.
• TRACE — izpis sporočila v dnevnik nalaganja: TRACE Prodaja naložena;
• Pregledovalnik modela — po vsakem večjem koraku preverite, ali so tabele povezane tako, kot pričakujete.
• Štetje vrstic — če se po JOIN število vrstic poveča, ste podvojili podatke in vsote bodo napačne.

Dobre prakse na kratko

• Skripto razdelite na razdelke z jasnimi imeni in jo komentirajte.
• Nalagajte samo polja, ki jih res potrebujete (LOAD * le izjemoma).
• Imena polj naj bodo poslovna in razumljiva: Kupec, ne KUNNR.
• Ključe označite (npr. s %) in jih v aplikaciji skrijte pred uporabniki.
• Po vsaki spremembi preverite model in ključne vsote.