Toto je objekt systému MBI.

MBI (Management Byznys Informatiky) je portál obsahující zobecněná řešení v řízení provozu a rozvoje IT, resp. podnikové informatiky.

Pokud máte zájem získat více informací o tomto objektu (vazby na další objekty, přílohy, apod.), ale i získat mnoho dalších užitečných materiálů, můžete tak učinit ZDE / (registrace je bezplatná).

Aplikace : DAX pro Power Pivot – principy, základní funkce
DAX pro Power Pivot – principy, základní funkce
Kód aplikace, nástroje

Standardní kód aplikace v MBI.

:
AQ003A
Autor

Jméno a příjmení autora

:
MBI tým
Tvůrce, řešitel, aplikace, resp. nástroje

Tvůrce, řešitel, aplikace, resp. nástroje

:
Microsoft
Popis, obsahové vymezení

Účel a základní obsahové vymezení aplikace, k jakým manažerským rozhodnutím aplikace slouží.

1. DAX pro Power Pivot – principy, základní funkce
  • Informace k jazyku DAX jsou také uloženy na následujících stránkách: :
    • DAX pro Power Pivot – KPI, time intelligence a další funkce (AQ003B ),
    • DAX pro Power BI (AQ003C ).
2. Principy DAX
  • DAX - jazyk pro práci s multidimenzionálně organizovanými a uloženými daty.
  • Formulace kalkulací a dalších operací v DAX je obdobná pravidlům Excel , na druhé straně ale má i výrazné odlišnosti .
2.1. Výpočetní předpis v DAX
  • Každý výpočetní předpis v DAX začíná rovnítkem „=“ nebo přiřazením „:=“,
  • Pro prvky předpisu se pro identifikaci používá název tabulky a název sloupce v tabulce.
  • Příklad : = FTQ_Prodej [Prodej_Skut_Ks] * FTQ_Prodej [Cena_Ks] ,*
  • kde FTQ_Prodej – je název tabulky a Prodej_Skut_Ks a Cena_Ks jsou názvy sloupců - musí být vždy uzavřeny v hranatých závorkách .
    • Pokud je název tabulky víceslovný, začíná-li číslicí nebo je shodný s rezervovaným slovem DAXu, je třeba ho uzavřít do apostrofů.
2.2. Datové typy a operátory
  • Standardní datové typy : Integer, Real, Currency, Date (datetime), TRUE/FALSE (Boolean), String, BLOB (Binary Large Object).
  • Výsledný typ ve výrazu vychází z datových typů jednotlivých částí výrazu, např. pokud je ve výrazu použit datový typ Date, pak výsledek bude rovněž mít datový typ Date.
  • Automaticky konvertuje řetězce na čísla a čísla na řetězce, jak to vyžaduje příslušný operátor (tzv. operator overloading ), např. pro výraz 7 & 2 bude výsledná hodnota 72 , ale pro „6“ + „3“ bude výsledek 9,
  • Standardní operátory : **aritmetické: +, -, *, /,
    • porovnávací: =, <>, >, >=, <, <=,
    • konkatenace: &,
    • logické: AND nebo &&, OR nebo ||, NOT nebo !..
3. Kontext vyhodnocování výpočetních předpisů
  • DAX musí respektovat uspořádání databáze Power Pivot nebo Power BI a jeho uložení dat, tj. kontext , kde se předpis bude vyhodnocovat ( evaluation context ).
  • Kontext - dán systémem ukazatelů a dimenzí - multidimenzionálním uspořádáním dat ,
  • Z pohledu kontingenčních tabulek je dán řádky a sloupci a jejich položkami, filtry a průřezy (slicers) , - ,
  • DAX rozlišuje dva kontexty :
    • řádkový kontext (row context) – operace probíhají v rámci 1 řádky,
    • filtr kontext (filter context) - vztahuje se k souhrnným hodnotám a aktuálně nastaveným filtrům tabulky.
  • Kontextu odpovídají 2 základní typy výpočtů , :
    • kalkulovaný sloupec (calculated column) - vypočítáván v kontextu jednotlivých řádektabulky (row context),
    • míra (measure , resp. počítané pole, calculated field) - počítá se na agregační úrovni dat, musí respektovat kontext každé buňky, včetně nastavených filtrů a průřezů.
4. Kalkulované sloupce a řádkový kontext
  • Kalkulovaný sloupecje vypočítáván v řádkovém kontextu - v kontextu jednotlivých řádektabulky a vytváří v tabulce nový kalkulovaný sloupec z hodnot položek a konstant dané řádky.
  • Výpočet se vždy provede právě na jedné řádce a postupně přechází z první na další řádky v rámci tabulky,
  • Výpočty probíhají v průběhu obnovení, resp. aktualizace tabulky,
  • Výpočet není závislý na aktivitách uživatele při práci s kontingenční tabulkou, není závislý na nastavení filtrů.
  • Vytvoření kalkulovaného sloupce - zápis výpočetního předpisu, vzorce, např. Trzby_Kc := FTQ_Prodej [Prodej_Skut_Ks] * FTQ_Prodej [Cena_Ks] - , nahoře,
  • přejmenování sloupce z původního Calculated Column resp. Počítaný sloupec - , dole,
  • všechny položky v řádcích - stejný výpočet , jinak využít funkci IF () ,
  • výsledky výpočtů se ukládají do databáze Power Pivot,
  • data v kalkulovaném sloupci jsou, oproti Míře, statická (nemění se s použitím filtrů).
5. Míry a filtr kontext
  • Míra, resp. measure , nebo počítané pole,calculated field, (pro Excel 2013) se počítá na agregační úrovni dat, tj. za tabulku nebo její podmnožinu na základě kontextu daného filtrem nebo filtry ( filter context ).
  • Míry se nemohou vztahovat k jednotlivým řádkům, ale může se vztahovat k 1 nebo více kalkulovaným sloupcům.
  • Je použitelná pro všechny kontingenční tabulky a reporty nad datovým modelem Power Pivotu,
  • Příklad : celková marže z prodeje zboží - výpočetní předpis je - :
    • =SUM (FTQ_Prodej [Prodej_Kc]) - SUM (FTQ_Prodej [Naklady])
  • Přehled o vytvořených mírách – Spravovat míry ,
  • Úpravy míry: Spravovat míry – funkce Upravit , úpravy míry se promítají do dalších měr, které mají na vstupu danou upravovanou míru.
6. Využití měr
  • Míry se zobrazí v kontingenční tabulce u tabulky faktů - , vpravo,
  • Tabulka obsahu souhrnné hodnoty prodeje v Kč (řádky) a souhrnné marže - , vlevo.
  • Míry lze centrálně formátovat – v editoru míry, s funkcí Kategorie , dole, v jiných kontingenčních tabulkách se přeformátování zajistí pomocí funkce Obnovit .
6.1. Příklady:
  • Výpočet počtu prodejních transakcí ,
  • Výpočet dnů, kdy byly zpracovány objednávky na zboží ,
  • Míry - přenosné výpočty , resp. portable formulas .
6.2. Vkládání měr do sebe
  • Míry lze postupně je vkládat do sebe,
  • Provedení změny do jedné míry se automaticky promítne do všech dalších.
7. Práce s více tabulkami
  • Pro tabulky a jejich vazby platí následující pravidla :
    • nedefinovat vazby mezi faktovými tabulkami navzájem, ale pouze prostřednictvím sdílených dimenzionálních tabulek,
    • nevytvářet „násilně“ kombinované faktové tabulky z několika základních (flatten tables),
    • kombinovat využití měr vázaných na různé faktové tabulky v jedné kontingenční tabulce..
7.1. Řádkový kontext s vazbami tabulek
  • Zpracování v řádkovém kontextu, které se vztahuje na 2 nebo více provázaných tabulek - parametry RELATED nebo RELATEDTABLE.
  • Kalkulovaný sloupec s hodnotou prodeje vypočítané ze skutečného množství prodaného zboží (Prodej_Skut_Ks) v tabulce faktů FTQ_Prodej a ceníkové ceny (Cena) v dimenzionální tabulce DI_Zbozi – - kardinalita a směr vazby: pro FTQ_Prodej : DI_Zbozi - kardinalita vazby M : 1.
  • Předpis pro kalkulovaný sloupec: =FTQ_Prodej [Prodej_Skut_Ks] * RELATED (DI_Zbozi [Cena]) – ,
  • Parametr RELATED funguje, protože řádkový kontext je na straně vazby many, „M“, tedy tabulky FTQ_Prodej.
  • Pokud by řádkový kontext byl na straně vazby „1“ - parametr RELATEDTABLE, např. je potřeba spočítat počty prodejů za jednotlivé druhy zboží, tedy řádkový kontext se vztahuje k tabulce DI_Zbozi, tedy na straně „1“.
  • RELATEDTABLE vrací postupně pro každý řádek zboží, tabulku odpovídajících prodejů v tabulce FTQ_Prodej. Počty prodejů vyjádří předpis: =COUNTROWS (RELATEDTABLE (FTQ_Prodej)), ,
  • RELATED i RELATEDTABLE mohou procházet celý řetěz provázaných tabulek, počet není limitován, ale vazby mezi tabulkami musí mít kardinalitu stejného typu a musí být definovány ve stejném směru.
7.2. Filtr kontext s vazbami tabulek
  • Řešení měr a filtr kontextu v prostředí několika tabulek s definovanými vazbami ,
  • Pravidlo uplatnění filtr kontextu: filtr aplikovaný na určitou tabulku se automaticky promítá do všech provázaných tabulek, které jsou na straně „M“ (many) ve vazbách 1 : M (one-to-many). Na druhé straně, pokud je tabulka na straně „1“ vazby M : 1, automatické promítnutí filtru ze strany „M“ do „1“ provázané tabulky neproběhne.
8. Parametrické nepropojené tabulky
  • Speciální tabulky, které obvykle slouží jako parametry pro přepočty hodnot ukazatelů podle aktuální situace.
  • Tyto tabulky nejsou propojené vazbou k žádné jiné tabulce datového modelu, protože není ani klíčová položka, na jejímž základě by bylo možné vazbu vytvořit.
  • Příklad: tabulka kursů korun za Euro pro přepočty cen zboží. Tabulka na obrázku představuje možné kursy Kč vzhledem k Euru, která bude dále sloužit jako parametrická pro přepočty. Jde pouze o příklad a lze do ní doplnit množství dalších hodnot kursu.
  • Tato tabulka bude pak při vytváření kontingenčních tabulek sloužit jako nabídka kursů v rámci sliceru uprostřed.
8.1. Vytvoření míry maximální hodnoty kursu
  • V dalším kroku vytvoříme míru maximální hodnoty kursu:
    • Korun_za_Euro := MAX (CZK_EUR [Kc_Euro]),
  • Tato míra bude poskytovat jednak maximální hodnotu na kursovním lístku, nebo bude sloužit jako hodnota pro přepočet ceny zboží.
8.2. Vytvoření míry průměrné ceny zboží podle zvoleného kursu
  • V dalším kroku vytvoříme míru pro přepočet průměrné ceny zboží podle zvoleného aktuální kursu Kč k Euru:
    • Cena_Euro := [Průměr Cena_Ks] / [Korun_za_Euro],
  • Míra Korun_za_Euro funguje v tomto případě tak, že pokud ve sliceru vybereme jednu hodnotu, v našem případě 26,09 , pak se do výpočtu doplní tato hodnota, pokud nevybereme žádnou hodnotu nahradí se maximální, tedy 26,31.
9. Funkce CALCULATE ()
  • Funkce CALCULATE() - nejdůležitější, nejužitečnější a nejkomplexnější funkce jazyka DAX,
  • Umožňuje modifikovat filtr kontext - nastavuje nový filtr kontext a na jeho základě vyhodnocuje výraz.

Funguje následujícím způsobem :

  • převezme aktuální filtr kontext a vytvoří z něj kopii do nového filtr kontextu,
  • vyhodnocuje každou podmínku v zadaných parametrech a pro každou z nich platí, že pokud je použit sloupec, který ještě nebyl předmětem filtru v původním kontextu, přidá tuto podmínku do nově vytvářeného filtr kontextu, pokud na druhé straně je použit sloupec, který už byl předmětem filtru v původním kontextu, pak nahradí existující filtr novou zadanou podmínkou,
  • všechny podmínky jsou v novém filtr kontextu vyhodnocovány s uplatněním logického součinu,
  • výraz míry je na základě filtru vyhodnocen.

CALCULATE() rozeznává 2 typy filtrů :

  • booleovské podmínky, jako např. DI_Zbozi [Cena] > 2000, tyto filtry se používají na jednotlivých sloupcích – s výslednou hodnotou Ano / Ne, resp. TRUE / FALSE,
  • seznamy hodnot presentované ve formě tabulky, představují seznamy hodnot, které mají být součástí nového filtr kontextu a všechny sloupce této tabulky jsou součástí filtru

Příklad: Vytvoření míry Prodej_2016 s využitím CALCULATE , nahoře, zařazení do kontingenční tabulky , dole

Příklad: Použití operátoru pro vyjádření logického součtu, tj. ||, nebo OR, - operátor || je možné použít pouze pro porovnání dvou stejných položek (sloupců).

9.1. CALCULATE() v řádkovém kontextu
  • Úlohou funkce CALCULATE() je rovněž transformace řádkového kontextu na filtr kontext.
  • Příklad: Potřebujeme vytvořit souhrn ceníkových cen a uložit ho do kalkulovaného sloupce (Suma_Cen) na základě zápisu: Souhrn_Cen := SUM ( DI_Zbozi [Cena] ),
  • V tomto případě se tedy v řádkovém kontextu vypočítá celkový souhrn ceníkových cen a ten se uloží do každého řádku kalkulovaného sloupce Souhrn_Cen – Souhrn_cen.
  • Pokud v rámci řádkového kontextu požadujeme souhrny ceníkových cen podle jednotlivých druhů zboží, pak použijeme v rámci řádkového kontextu funkci CALCULATE(), takto:
    • Souhrn_Cen_Calc := CALCULATE ( SUM ( DI_Zbozi [Cena] ) ),
  • Výsledkem je sloupec Souhrn_Cen_Calc, ,
  • V tomto případě neobsahuje zápis CALCULATE() žádné filtry a tedy nemění existující filtr kontext, ale na druhé straně transformuje existující řádkový kontext na filtr kontext. Uskutečňuje tak transformaci kontextu. Na základě tohoto zápisu výraz SUM ( DI_Zbozi [Cena] ) je vypočítáván v rámci filtr kontextu vždy pouze pro jednu řádku.
  • Tento princip se efektivně uplatňuje ve výpočtech s více provázanými tabulkami. Filtry se v případě řádkového kontextu nepromítají automaticky do dalších provázaných tabulek, zatímco pro filtr kontext platí, že se promítají automaticky ve směru vazby 1 ku M. To znamená, že transformace kontextu takové automatické promítání filtrů do vázaných tabulek zajistí, tedy např.
    • Souhrn_Prodeje_Calc := CALCULATE ( SUM ( FTQ_Prodej [Prodej_Skut_Ks] ) ),
  • To znamená, že filtry z tabulky zboží (DI_Zbozi) se automaticky promítnou do tabulky faktů (FTQ_Prodej) – jde o vazbu 1 : M - a dostaneme tak výsledky odpovídající těmto filtrům a podobně i v obdobných případech.
9.2. Parametr ALLSELECTED
  • Funkce CALCULATE() umožňuje měnit a tedy i zrušit všechny nastavené filtry na určité tabulce, a to s využitím parametru ALL,
  • Příklad: ALL (DI_Zbozi [Cena] )… – zruší všechny filtry na sloupci ceny zboží.
  • Pokud se mají filtry zrušit kromě těch, které jsme aktuálně nastavili v kontingenční tabulce, pak se pro to využije parametr ALLSELECTED, tedy: … ALLSELECTED (DI_Zbozi [Cena] )…
10. Využití iterací a iterátorů
  • Využití řádkového kontextu s iteracemi, resp. s použitím iterátorů.
10.1. Funkce „X“
  • Všechny funkce DAX končící na „X“ jsou považovány za iterátory.
  • Vyhodnocují výpočty v každé řádce a nakonec je agregují podle různých algoritmů.
  • Příklad: V každé řádce tabulky se položka náklady zvýší o 5 % a nakonec vrátí souhrn těchto přepočítaných hodnot . Funkce SUMX tak využije v tabulce.
10.2. Funkce FILTER
  • Funkce FILTER - v řádkovém kontextu prochází tabulku (řádku po řádce) a vrací novou tabulku s řádky odpovídajícími zadané podmínce,
  • Příklad: Funkce vrátí tabulku zboží, jejichž cena je vyšší než 3000, ,
  • V případě, že by při použití došlo ke konfliktu filtrů, takže by výše uvedená funkce nedávala žádné výsledky, používá se parametr ALL, který zajistí, že se aktuální filter kontext ignoruje.
11. Využití hierarchií prvků dimenzí
  • Hierarchie prvků představují předdefinované cesty přístupu k datům,
  • Příklad: vyjádření hierarchie v jedné tabulce je dimenze času ,
  • Vyjádření hierarchie v několika propojených tabulkách na bázi SNOWFLAKE.
11.1. Propojení hierarchie v několika propojených tabulkách do jedné
  • Možnost transformovat hierarchii dat několika propojených tabulek do jedné a jejich spojení s funkcí RELATED.
  • Předpokládejme hierarchii v dimenzi zboží založenou na vazbách tabulek kategorie zboží (DI_Zbozi_Kat) - skupiny zboží (DI_Zbozi_Skupina) - jednotlivé položky zboží (DI_Zbozi).
  • Pokud bude účelné promítnout některé údaje, jako např. název z vyšších úrovní hierarchie do nižší a současně i propojit tabulky v jedné, tedy v DI_Zbozi, pak lze použít následujícího zápisu pro vytvoření nového kalkulovaného sloupce:
    • DI_Zbozi [Skupina_Zbozi] := RELATED (DI_Zbozi_Skupina [Zbo_Nazev_Skupina] ) - , nahoře,
    • DI_Zbozi [Kategorie_Zbozi] := RELATED (DI_Zbozi_Kat [Zbo_Nazev_Kat ) - , dole.
11.2. Výpočty v hierarchiích prvků
  • Základní možnost - funkce Nastavení polí hodnot – vede ke změně standardních agregovaných hodnot ukazatele na průměry, počty apod.
  • Tuto funkci (vyvolanou pravým tlačítkem na jakékoli hodnotě sloupce) lze využít i k dalším typům výpočtů, kterými jsou obvykle výpočty procentních podílů hodnot dané buňky tabulky k celkovému součtu, k součtu řádku, k součtu sloupce apod., ,
  • Zobrazení hodnot v procentuálním vyjádření podle hierarchie prvků, .