Jednou z mála využívaných, o to však užitočnejších vlastností dátového modelu v PowerPivote aj Power BI, sú neaktívne prepojenia medzi tabuľkami. Umožňujú vám zredukovať počet tabuliek v dátovom modeli, používajú sa hojne v pokročilej analytike aj reportingu, a vedia vám uľahčiť riešenie niektorých analytických problémov. Preto sa teraz pozrieme na to, ako fungujú, a ako sa dajú použiť.

Keď vytvárate prepojenia medzi tabuľkami v dátovom modeli, tak po chvíli narazíte na jedno obmedzenie – že medzi 2 tabuľkami môže byť iba jedno “normálne” prepojenie. Každé ďalšie prepojenie, ktoré medzi nimi vytvoríte, je akési “čiarkované”. A týchto čiarkovaných prepojení môžete vytvoriť koľko len chcete. Na prvý pohľad to však nemá žiaden efekt, keď to chcete potom niekde použiť.

Tieto “čiarkované” prepojenia sa po správnosti nazývajú neaktívne prepojenia. Neaktívne preto, lebo sa pri bežnej analytike tvária, ako keby tam neboli. A keď spájate či filtrujete dáta v tabuľkách, tak tie tabuľky sú z pohľadu výpočtov prepojené cez to “normálne”, nečiarkované prepojenie – po správnosti cez aktívne prepojenie. Aj preto môžete mať medzi dvoma tabuľkami iba jedno aktívne prepojenie. Inak by nebolo jasné, ako jednoznačne spojiť dáta v týchto dvoch tabuľkách, a ako sa potom má filtrovať jedna tabuľka podľa druhej.

Na čo sú ale tie neaktívne prepojenia?

Dôvodov je viacero. Technologický dôvod je taký, aby ste cez ne robili výpočet vtedy, keď potrebujete. Alebo aby ste vo východzej situácii zakázali filtrovanie jednej tabuľky podľa druhej tabuľky, ale neskôr to v konkrétnych výpočtoch povolili.

Typickým príkladom je analýza objednávok či iných transakcií s viacerými dátumovými stĺpcami. A opäť si to ukážeme na našom vzorovom súbore PowerPivotu, resp. Power BI.

V tabuľke Objednávky máme stĺpec SaleDateKey, ktorý obsahuje dátum objednania danej objednávky:

Tento stĺpec je prepojený na tabuľku Čas, na stĺpec DateKey, a vďaka tomu môžeme robiť analýzu objednávok podľa rokov, mesiacov, dní, a pod..

Pridáme si do tejto tabuľky ešte ďalší stĺpec, ktorý bude obsahovať dátum uhradenia objednávky, a bude natvrdo posunutý o 14 dní oproti dátumu objednania. Pridáme ho ako vypočítaný stĺpec s týmto vzorcom:

Dátum uhradenia = ‘Objednávky'[SaleDateKey] + 14

Potom si spravíme klasickú kontingenčku, kde:

• do oblasti riadkov dáme z tabuľky Čas stĺpec CalendarYear,
• do oblasti hodnôt dáme merítko Obrat z tabuľky Objednávky.

Výsledok bude vyzerať takto:

Keďže tabuľky Objednávky a Čas sú prepojené cez stĺpec SaleDateKey v Objednávkach, tak v skutočnosti táto kontingenčka zobrazuje obrat podľa dátumu objednania. A my by sme teraz chceli zobraziť vedľa neho obrat podľa dátumu uhradenia objednávok – čiže obrat podľa toho, kedy firme reálne prišli peniaze.

Klasické riešenie je také, že zduplikujeme tabuľku Čas, a potom túto druhú kópiu prepojíme s objednávkami na stĺpec s dátumom uhradenia. Čo je síce fajn, ale potom nebudeme vedieť v jednej kontingenčke zobraziť oba obraty. A zbytočne si kvôli tomu duplikujeme tabuľky v dátovom modeli, čo zrovna nie je inteligentné riešenie, ak máte v objednávkach nie dva, ale povedzme 10-20 dátumových stĺpcov. Preto na to musíme ísť inak.

Riešenie

Riešením je vytvorenie ďalšieho prepojenia medzi tabuľkami Čas a Objednávky, konkrétne z tabuľky Čas a stĺpca DateKey na tabuľku Objednávky a stĺpec “Dátum uhradenia”. Vytvoríme ho rovnakým spôsobom ako akékoľvek iné prepojenie. Po jeho vytvorení však uvidíte, že bude vytvorené “čiarkovane”, a teda ako neaktívne prepojenie:

Keď sa však potom pozriete do reportu, tak sa nikde nič nezmení. Pretože takéto prepojenie sa dá využiť iba cez jazyk DAX. A je na to iba 1 kontextová funkcia: USERELATIONSHIP. Nijak inak toto prepojenie v PowerPivote ani Power BI nevyužijete.

Funkcia USERELATIONSHIP je kontextová funkcia, ktorá počas výpočtu vzorca vykoná výpočet cez zadané prepojenie medzi tabuľkami, či už aktívne alebo neaktívne. Ak je prepojenie neaktívne, tak pre účely výpočtu sa bude tváriť ako aktívne. Podstatné však je, že prepojenie musí byť v dátovom modeli vytvorené. Podľa oficiálnej dokumentácie je syntax tejto funkcie je nasledovná:

USERELATIONSHIP(stĺpec1; stĺpec2)

…kde stĺpec1 a stĺpec2 sú koncové body prepojenia, ktoré chcete použiť. Je jedno, ktorý z nich zadáte prvý a ktorý druhý. Podstatné je, že toto prepojenie musí existovať v dátovom modeli.

Keď teda chceme vypočítať obrat podľa dátumu uhradenia cez naše neaktívne prepojenie, tak vytvoríme merítko s týmto vzorcom:

Obrat podľa dátumu uhradenia := CALCULATE([Obrat]; USERELATIONSHIP(‘Čas'[DateKey]; ‘Objednávky'[Dátum uhradenia]))

Funkcia USERELATIONSHIP je kontextová funkcia, a preto sme ju museli použiť spolu s funkciou CALCULATE na zmenu kontextu výpočtu. Výsledok po dosadení merítka do kontingenčky bude vyzerať takto:

Tam si všimnite 2 veci. Prvá je, že tento obrat podľa dátumu uhradenia je posunutý voči dátumu objednania o 14 dní. Preto je v prvom roku súčet obratov podľa dátumu uhradenia o niečo menší ako súčet klasického obratu (podľa dátumu objednania), pretože peniaze prišli vždy o 14 dní neskôr.

Druhá vec je ten riadok s prázdnym rokom, ktorý nám pribudol. To je objednávka z decembra 2004, ktorej vyšiel dátum uhradenia na január 2005. A keďže v číselníku Čas sú iba dátumy od roku 2001 po rok 2004, tak vznikol neplatný odkaz na túto časovú tabuľku. A PowerPivot sa s tým vysporiadal štandardným spôsobom – vytvorením neviditeľnej prázdnej položky v číselníku, tak ako som už o tom písal v tomto článku. Vyriešite ho rozšírením číselníka až po rok 2005, kde sa potom v reporte z prázdneho roka stane rok 2005.

A ako zmeniť neaktívne prepojenia na aktívne?

Veľmi ľahko, len musíte mať na pamäti, že v dátovom modeli môže byť medzi 2 tabuľkami iba 1 aktívne pripojenie. Takže ak tam už nejaké aktívne prepojenie máte, tak je potrebné najprv toto prepojenie deaktivovať. A potom to druhé, ktoré ste chceli mať aktívne, aktivovať. Spravíte to tak, že si v diagramovom zobrazení PowerPivotu rozkliknete dvojklikom dané prepojenie, a v okne, ktoré sa otvorí, zakliknete/odkliknete políčko nazvané “Aktívne”:

…a v Power BI Desktope to spravíte tiež rozkliknutím daného prepojenia medzi tabuľkami, a v okne, ktoré sa otvorí, zakliknete/odkliknete políčko “Aktivovať tento vzťah”:

Na záver

Takto sa teda používajú neaktívne prepojenia v PowerPivote, v Power BI, v SSAS Tabulare či Azure Analysis Services. Sú výborným nástrojom pri pokročilej analytike aj reportingu, a vďaka nim sa dá stvárať kopec mágie. Okrem zjednodušenia dátového modelu totižto vedia veľmi dobre pomôcť s komplexnejšími analytickými aj reportingovými scenármi. Preto je dobré o nich vedieť, a samozrejme, používať ich keď sú potrebné 🙂