Podatkovni model v Qliku: zvezdna shema, sintetični ključi in krožne reference

Qlik ne sprašuje, kako naj poveže tabele — poveže jih sam, prek polj z enakim imenom. Zato je podatkovni model v Qliku hkrati preprostejši in nevarnejši kot v relacijski bazi: ni ga treba »risati«, a vsako polje, ki se slučajno imenuje enako, postane povezava. Ta vodič predpostavlja, da poznate osnove nalaganja iz vodiča Nalaganje podatkov v Qlik Sense.

Dejstva in dimenzije

Vsaka tabela v modelu ima eno od dveh vlog:

• Tabela dejstev (fact) vsebuje dogodke, ki jih štejemo in seštevamo: vrstice računov, naročila, ure, skladiščne premike. Ima veliko vrstic in številska polja (znesek, količina).
• Tabele dimenzij opisujejo dogodke: kupec, izdelek, prodajalec, datum. Imajo manj vrstic in opisna polja (ime, skupina, regija).

Ko sta vlogi jasni, je jasna tudi oblika modela: dejstva v sredini, dimenzije okoli njih, vsaka povezana z enim ključem. To je zvezdna shema in v Qliku je skoraj vedno najboljša izbira — je hitra, razumljiva in v njej se številke ne podvajajo.

Če ima dimenzija še svojo poddimenzijo (izdelek → skupina izdelkov → oddelek), dobite snežinko. Qlik z njo nima težav, a za preglednost manjše poddimenzije raje pripnite k dimenziji z ApplyMap ali LEFT JOIN.

Zrnatost: ena vrstica = en dogodek

Zrnatost (grain) pove, kaj predstavlja ena vrstica tabele dejstev: ena vrstica računa? En račun? En dan na izdelek? Najpogostejša napaka v modelih je mešanje zrnatosti.

Primer: glava računa ima polje Postnina, vrstice računa pa Znesek. Če glavo z JOIN pripnete k vrsticam, se poštnina ponovi v vsaki vrstici — račun s petimi vrsticami ima petkratno poštnino in Sum(Postnina) je napačen.

Rešitve:

• vrednost iz glave razdelite na vrstice (npr. sorazmerno z zneskom vrstice),
• ali jo pripišete le eni vrstici računa (npr. prvi),
• ali glavo ohranite kot ločeno tabelo, povezano z RacunID, in poštnino seštevate tam.

Več tabel dejstev: prodaja in plan

Klasičen izziv: v isti aplikaciji želite prodajo (po dnevih, kupcih in izdelkih) in plan (po mesecih in izdelkih). Obe tabeli imata IzdelekID in datum — če ju naložite ločeno, dobite sintetični ključ. V Qliku sta dve uveljavljeni rešitvi.

1. Konkatenirana tabela dejstev (priporočeno)

Obe tabeli združite v eno, z dodatnim poljem, ki pove, od kod je vrstica. Polja, ki jih ena od tabel nima, ostanejo prazna:

Dejstva:
LOAD
    Datum,
    KupecID,
    IzdelekID,
    Znesek          as Prodaja,
    'Prodaja'       as TipZapisa
FROM [lib://Podatki/QVD/Prodaja.qvd] (qvd);

CONCATENATE (Dejstva)
LOAD
    MonthStart(Mesec) as Datum,     // plan je mesečen
    IzdelekID,
    Znesek          as Plan,
    'Plan'          as TipZapisa
FROM [lib://Podatki/plan.xlsx]
(ooxml, embedded labels, table is Plan);

Mera Sum(Plan) - Sum(Prodaja) zdaj deluje po izdelkih in mesecih. Po kupcih plan ni razdeljen, zato bo pri izboru kupca plan prazen — kar je pravilno.

2. Povezovalna tabela

Kadar imata tabeli dejstev zelo različna polja ali sta zelo veliki, ju lahko ohranite ločeni in povežete prek povezovalne tabele (link table): vsaka tabela dejstev dobi sestavljen ključ, povezovalna tabela pa vsebuje vse kombinacije ključa in skupnih dimenzij.

Prodaja:
LOAD
    IzdelekID & '|' & Num(MonthStart(Datum)) as %Kljuc,
    Datum         as DatumProdaje,
    KupecID,
    Znesek        as Prodaja
FROM [lib://Podatki/QVD/Prodaja.qvd] (qvd);

Plan:
LOAD
    IzdelekID & '|' & Num(MonthStart(Mesec)) as %Kljuc,
    Znesek        as Plan
FROM [lib://Podatki/plan.xlsx]
(ooxml, embedded labels, table is Plan);

// Povezovalna tabela: vsaka kombinacija ključa in skupnih dimenzij enkrat
Povezava:
LOAD DISTINCT
    %Kljuc,
    SubField(%Kljuc, '|', 1)                as IzdelekID,
    Date(Num#(SubField(%Kljuc, '|', 2)))    as Mesec
RESIDENT Prodaja;

CONCATENATE (Povezava)
LOAD DISTINCT
    %Kljuc,
    SubField(%Kljuc, '|', 1)                as IzdelekID,
    Date(Num#(SubField(%Kljuc, '|', 2)))    as Mesec
RESIDENT Plan;

Povezovalna tabela je prožna, a model postane bolj zapleten in počasnejši. Če lahko, izberite konkatenacijo.

Sintetični ključi

Ko imata dve tabeli dve ali več skupnih polj, Qlik ustvari sintetični ključ: dodatno tabelo $Syn 1 s kombinacijami teh polj. V pregledovalniku modela je jasno vidna, v dnevniku nalaganja pa se izpiše opozorilo.

Sintetični ključ ni vedno napaka — včasih je model res tak. Skoraj vedno pa pomeni, da se je povezalo nekaj, česar niste nameravali. Možne rešitve:

QUALIFY vsem poljem naslednjih tabel doda predpono z imenom tabele, UNQUALIFY pa izvzame polja, ki naj ostanejo povezovalna:

QUALIFY *;
UNQUALIFY KupecID, IzdelekID;

Kupci:
LOAD KupecID, Naziv, Status, DatumSpremembe
FROM [lib://Podatki/QVD/Kupci.qvd] (qvd);
// polja postanejo Kupci.Naziv, Kupci.Status, Kupci.DatumSpremembe

UNQUALIFY *;

QUALIFY * uporabljajte zmerno — imena, kot je Kupci.Naziv, niso prijazna za uporabnike. Pogosto je bolje polja preimenovati ročno.

Krožne reference

Krožna referenca nastane, ko se tabele povežejo v zanko. Tipičen primer: tabela Prodaja je povezana s Kupci in s Prodajalci, obe dimenziji pa imata polje Regija. Pot od prodaje do regije zdaj obstaja dvakrat — prek kupca in prek prodajalca.

Qlik zanko prekine tako, da eno tabelo označi kot ohlapno povezano (loosely coupled) in izpiše opozorilo. Rezultati izborov so potem odvisni od tega, katero povezavo je prekinil — to ni stanje, ki bi ga želeli v produkciji.

Rešitev je skoraj vedno v vprašanju: ali gre res za isto stvar? Regija kupca in regija prodajalca sta dva različna pojma, zato naj bosta dve polji:

Kupci:
LOAD KupecID, Naziv as Kupec, Regija as RegijaKupca
FROM [lib://Podatki/QVD/Kupci.qvd] (qvd);

Prodajalci:
LOAD ProdajalecID, Ime as Prodajalec, Regija as RegijaProdajalca
FROM [lib://Podatki/QVD/Prodajalci.qvd] (qvd);

Isto velja za datume: datum naročila, datum dobave in datum plačila so tri različna polja. Če potrebujete koledar za več datumov, ustvarite več koledarjev ali skupni koledar s tipom datuma.

JOIN, KEEP ali ApplyMap?

Ključi: poimenovanje in AutoNumber

• Ključna polja označite s predpono, npr. %, in jih skrijte pred uporabniki z nastavitvijo na začetku skripte: SET HidePrefix = '%';
• Dolgi besedilni ključi porabijo veliko pomnilnika. Pretvorite jih v zaporedna števila z AutoNumber():

AutoNumber(PodjetjeID & '|' & NarociloID, 'Narocilo') as %NarociloKljuc

Drugi argument je ime števca — pri vseh tabelah, kjer ključ nastopa, uporabite enakega, sicer se številke ne bodo ujemale. Uporabljajte ga le znotraj iste aplikacije, ne pri shranjevanju v QVD, ki ga bere druga aplikacija.

Kako preveriti model

1. Pregledovalnik modela: ni sintetičnih ključev, ni krožnih referenc, vsaka dimenzija je povezana z enim ključem.
2. Število vrstic: po vsakem JOIN primerjajte število vrstic pred in po njem.
3. Kontrolne vsote: skupna prodaja za izbran mesec se mora ujemati z izvornim sistemom (ERP, računovodstvo).
4. Osirotele vrednosti: ključi v dejstvih, ki jih ni v dimenzijah (kupec brez šifre). V tabeli z dimenzijo KupecID iz dejstev in poljem Kupec iz dimenzije jih takoj vidite kot prazne vrednosti.

Kontrolni seznam

• Ena tabela dejstev (ali konkatenirana) z jasno zrnatostjo.
• Vsaka dimenzija povezana z enim samim ključem.
• Brez $Syn tabel in brez opozoril o krožnih referencah.
• Polja z različnim pomenom imajo različna imena.
• Ključi so skriti in po potrebi pretvorjeni z AutoNumber.
• Kontrolne vsote se ujemajo z izvornim sistemom.