Máte-li odvahu pokračovat – prosím :)
Teoretický základ
Možná si někteří z Vás kladou otázku: Co to znamená semi-aditivní měřítko. Funkce dělíme podle aditivity jednoduše na tři skupiny.
Neaditivní – s těmito měřítky nelze počítat, dávají smysl jen na detailu. Nemůžeme je sčítat přes žádné navázané dimenze. Příklad: Jednotková cena. Jednotkovou cenu nemůžu sčítat ani přes produkt, ani přes zákazníka, ani v čase. Dává smysl jen na detailní úrovni.
Aditivní – jsou taková měřítka, která počítám stejným mechanismem přes všechny dimenze. Například Prodej - částka v Kč. Můžu sčítat funkcí suma přes produktové kategorie, sčítat sumu za regiony a prodeje vesele sčítat v čase.
Semi-Aditivní – přes některou dimenzi se počítají jinak. Nejčastěji se nedají semiaditivní měřítka sčítat v čase a jsou v DW realizované jako snapshotové tabulky místo tabulek transakčních. Tzn, že tabulka ukládá k datumu raději stav, než pohyb. Příklad: stav účtu – přes dimenzi účtu můžu sčítat, v čase mě zajímá buď počáteční stav, či konečný stav. Často bychom v designu mohli použít tabulku transakční (pohyby na účtu), i jako tabulku stavovou (ukládat stav ke dni).
| Den | Pohyb | Stav |
| 1 |
10
|
10
|
| 2 |
-2
|
8
|
| 3 |
3
|
11
|
| 4 |
-1
|
10
|
| ... |
10
| |
| 31 |
6
|
16
|
| Výsledek |
16
|
16
|
Proč se stavové tabulky vůbec vytváří, když by šlo spočítat výsledek z pohybů? Jedním slovem "výkon". Počítat zůstatek na účtu k 31. dni v měsíci z pohybů při spuštění reportu od začátku do konce za 10 letou historii, výrazně zpomalí report. Mohlo by být vhodnější tento časově náročný výpočet předpočítat ve fázi ETL procesu a reportem sáhnout už na hotový výsledek. Proto se v tabulce v některých případech vyplatí ukládat stavy místo transakcí (můžeme mít i tabulky 2, jednu transakční, jednu stavovou). Typickými představitely stavových tabulek jsou: stavy účtů, stavy skladů, kurzovní lístky, ceny komodit, ceny akcií, a další.
Rozhodli jsme se uložit v datovém skladu tabulku jako stav, jak s ní ale naložit v OLAP kostce, aby data zobrazila správně?
SSAS podporují následující semi-aditivní funkce, které fungují "jinak v čase": First, Last, FirstNonEmpty, LastNonEmpty, AverageOfChildren (přes zavádějící název se průměr počítá pouze přes časovou osu, přes ostatní dimenze suma)
Všechny výše vyjmenované funkce počítají sumu přes všechny dimenze, kromě času. Zůstatek na účtu s funkcí Last počítá sumu přes dimenzi účet (máme 3 účty v různých bankách, chceme posčítat zůstatky přes dim účet), ale stavy v čase sčítat nemůžeme proto funkce
Last – zobrazí poslední stav v období. V roce 2015 zobrazí stav z 31.1.2015 pokud k danému dni existuje stav, vrátí tento stav. Pokud neexistuje ke dni stav, vrátí prázdnou buňku.
LastNonEmpty – vrátí poslední stav v daném období, pro který existuje hodnota. Jedná se o výrazně použitelnější variantu funkce last. LastNonEmpty počítá i s otevřeným obdobím. Tedy pokud je dnes 28.4.2015 a zůstatek byl 10. Při řezu kostky na úrovni roku, by funkce Last pro rok 2015 vrátila prázdnou buňku, funkce LastNonEmpty by vrátila 10.
Semi-aditivní chování se v SSAS je podporováno, což je super a jedná se o jednoduhý přepínač. To je super. Stačí označit časovou dimenzi, aby fungovala jako "Time Dimension" (add business intelligence wizard na dimenzi – mimo rozsah tohoto článku) a vybrat funkci ze seznamu.
Narazíte ale na určitá omezení
- Semi-aditivní měřítka nejsou podporovány ve standardní edici SQL Serveru (kromě Last – a ta je upřímně na ... / omezeně použitelná – pokud jste doplnili u třech teček slovo "Nic", doplnili jste správně)
- Sice máte vyšší edici SQL Serveru, která Semi-Aditivní funkce podporuje (BI/Enterprise), ale nevyberete si ze seznamu, protože funkce nedělá to, co potřebujete. (např kombinace DistinctCount přes zákazníka a LastNoneEmtpy v čase (problém A) – přes dimenze počítat počet unikátních výskytů, v čase zobrazovat poslední stav. Další aplikace "pohledávky". U pohledávek potřebujete pro uzavřené období funkci last, nechcete zobrazovat pohledávku pokud byla zaplacena. Pro otevřené období ale chcete vidět last non empty, pokud pohledávka v otevřeném období je, chcete zobrazit stav. Problém B)
Demo data/demo projekt
Ukázkový projekt s SSAS databází je nasdílen zde: https://drive.google.com/file/d/0B9ZohZ1CALKZblNFT28zQkhLNEE/view?usp=sharing a je postaven nad ukázkovou databází Adevnture Works Data Warehouse 2012 (ke stažení zde: http://msftdbprodsamples.codeplex.com/downloads/get/165405). Projekt je vlastní tvorba, budu ho používat i pro další čánky (v plánu MDX tutorial), a proto bude aktualizován dle potřeby.
Problém řešení
Počáteční stav – následující dotaz vnám vrátí tabulku, kde End Of Day Rate vrací poslední kurz pro australský dolar v daném období (pro kontrolu). Budu postupně upravovat člen x tak, aby vracel na konci cesty správný výsledek
WITH MEMBER x AS
1
SELECT
{[Measures].[End Of Day Rate]
,[Measures].[EOD]
,x}
ON COLUMNS,
NON EMPTY
[Dim Date].[Year].[Year]*
[Dim Date].[YQMD].[Month]
ON ROWS
FROM [MDX Tutorial]
WHERE [Dim Currency].[Currency].&[AUD]
Sloupec EOD simuluje situaci, že máme edici SQL Serveru standard a chceme spočítat End Of Day Rate, představuje vstup (používá funkci suma, takže sčítá stavy z jednotlivých dní. Např: July 2005 je Suma stavů ze dnů v 6.tém měsíci roku 2005).
Closing period
Funkci closing period nebudu používat, protože by mne navedla na funkci last... Tuto většinou nepotřebuji, protože je nevhodná pro otevřené období (aktuální rok, měsíc, atd.), takže jí v tomto článku nebudu věnovat příliš mnoho času, pokud vůbec nějaký.
Základní pattern krokově (jak spočítám semiaditivní měřítko na standardní edici)
Logický postup
Nezávisle na úrovni na které se v hierarchii nacházím, potřebuji se odnavigovat na dny v daném období. Z těchto dní vybrat poslední, pro který existuje stav a vrátit pro něj číslo.
Řešení
1 Definice člena x mne odnaviguje z aktuálního člena v hierarchii (currentmember funkce) na úraveň datum ve víceúrovňové hierarchii YQMD. Funkce settostr slouží pouze k výpisu na obrazovku pro ladící účely.
WITH MEMBER x AS
settostr(
descendants(/*1 - vsechny dny v aktualnim obdobi zacatek*/
[Dim Date].[YQMD].currentmember/*1 - identifikace aktualniho obdobi*/
,[Dim Date].[YQMD].[Date]/*1 level pro navigaci*/
)/*1 - konec navigace na dny*/
)
2 Odfiltrování pryč dnů, které nemají stav
settostr(
nonempty(/*2 - vyplneny kurz*/
descendants(/*1 - vsechny dny v aktualnim obdobi zacatek*/
[Dim Date].[YQMD].currentmember/*1 - identifikace aktualniho obdobi*/
,[Dim Date].[YQMD].[Date]/*1 - level pro navigaci*/
)
,[Measures].[EOD]
/*2 konec filtrace na vyplnene kurzy*/
)/*1 - konec navigace na dny*/
)
3 Funkce Tail vrátí n posledních několika prvků z množiny. Pokud nevyplníme počet n, vrátí poslední datum z období jako jednoprvkovou množinu.
WITH MEMBER x AS
settostr(
tail(/*3 poslednni v odobi*/
nonempty(/*2 - vyplneny kurz*/
descendants(/*1 - vsechny dny v aktualnim obdobi zacatek*/
[Dim Date].[YQMD].currentmember/*1 - identifikace aktualniho obdobi*/
,[Dim Date].[YQMD].[Date]/*1 - level pro navigaci*/
)
,[Measures].[EOD]
/*2 konec filtrace na vyplnene kurzy*/
)/*1 - konec navigace na dny*/
)/*3 posledni v obdobi*/
)
4 množina, byť jednoprvková nejde použít jako souřadnice v tuplu, proto je potřeba jednoprvkovou množinu převést na membera pomocí funkce item, funkce .name slouží pouze pro vypsání posledního datumu v období.
tail(/*3 poslednni v odobi*/
nonempty(/*2 - vyplneny kurz*/
descendants(/*1 - vsechny dny v aktualnim obdobi zacatek*/
[Dim Date].[YQMD].currentmember/*1 - identifikace aktualniho obdobi*/
,[Dim Date].[YQMD].[Date]/*1 - level pro navigaci*/
)
,[Measures].[EOD]
/*2 konec filtrace na vyplnene kurzy*/
)/*1 - konec navigace na dny*/
)/*3 posledni v obdobi*/
.item(0)/*prevod na membera*/
.name /*výpis jména*/
5 pokud jsme se byli schopni odkázat na poslední datum v období, můžeme vrátit poslední stav dokončením tuplu. 1 souřadnice je měřítko, 2 je časový údaj
(/*5 dokonceni tuplu*/
tail(/*3 poslednni v odobi*/
nonempty(/*2 - vyplneny kurz*/
descendants(/*1 - vsechny dny v aktualnim obdobi zacatek*/
[Dim Date].[YQMD].currentmember/*1 - identifikace aktualniho obdobi*/
,[Dim Date].[YQMD].[Date]/*1 - level pro navigaci*/
)
,[Measures].[EOD]
/*2 konec filtrace na vyplnene kurzy*/
)/*1 - konec navigace na dny*/
)/*3 posledni v obdobi*/
.item(0)/*4 prevod na membera*/
,[Measures].[EOD]
)/*5 konece dokonceni tuplu*/
Finální dotaz
WITH MEMBER x AS
(/*5 dokonceni tuplu*/
tail(/*3 poslednni v odobi*/
nonempty(/*2 - vyplneny kurz*/
descendants(/*1 - vsechny dny v aktualnim obdobi zacatek*/
[Dim Date].[YQMD].currentmember/*1 - identifikace aktualniho obdobi*/
,[Dim Date].[YQMD].[Date]/*1 - level pro navigaci*/
)
,[Measures].[EOD]
/*2 konec filtrace na vyplnene kurzy*/
)/*1 - konec navigace na dny*/
)/*3 posledni v obdobi*/
.item(0)/*4 prevod na membera*/
,[Measures].[EOD]
)/*5 konece dokonceni tuplu*/
SELECT
{[Measures].[End Of Day Rate]
,[Measures].[EOD]
,x}
ON COLUMNS,
NON EMPTY
[Dim Date].[Year].[Year]*
[Dim Date].[YQMD].[Month]
ON ROWS
FROM [MDX Tutorial]
WHERE [Dim Currency].[Currency].&[AUD]
Výsledek finálního dotazu
Sloupec x vrací stejný výsledek jako kostkou spočítaný sloupec End Of Date Rate, takže – funguje to :) Tento přístup by fungoval i pro měřítko s problémem A, stačilo by pro zdrojové měřítko (v našem případě EOD) nastavit agregační funkci DistinctCount.
Pohledávky (problém B)
Pro pohledávky nejde použít základní semiaditvní pattern. Náhled na tabulku představuje stavy. V roce 2014 bysme rádi viděli jako konečnou bilanci prázdnou hodnotu. Klient pohledávku splatil a nic dále nedluží (funkce last). Jinak je tomu pro období otevřené. Článek píši 28.4.2015. Stav ke konci 4.tého měsíce je 88 funkce last na měřítku Pohledavka by vrátila prázdou buňku. Zde potřebuji raději funkci "LastNonEmpty"
Stačí rozšířit náš kalkulovaný člen: Slovně řečeno: Pro neuzavřené období chci poslední vyplněný stav. Pro období uzavřená poslední stav
| Rok | Mesic | Pohledavka | |
| 2014 |
1
|
69
| |
| 2014 |
2
|
37
| |
| 2014 |
3
|
14
| |
| 2014 |
4
|
19
| |
| 2014 |
5
|
74
| |
| 2014 |
6
|
22
| |
| 2014 |
7
|
75
| |
| 2014 |
8
|
89
| |
| 2014 |
9
|
97
| |
| 2014 |
10
| ||
| 2014 |
11
| ||
| 2014 |
12
| ||
| 2015 |
1
|
89
| |
| 2015 |
2
|
79
| |
| 2015 |
3
|
48
| |
| 2015 |
4
|
88
|
Pro tento případ potřebuji identifikovat, jestli je období otevřené, nebo ne. Tohle není úplně jednoduché a záleželo by to dost na designu Vašeho DW a kostky. Představme si, že Měna = Klient a Kurz = Pohledávka. Pokud bych situaci zjednodušil a počítal, že v otevřeném období alespoň jeden klient bude mít pohledávku, můžu rozšířit funkci NonEmpty aby dohledala poslední neprázdné datum přes všechny klienty (Měny)
Počítaný člen by mohl vypadat následovně
(
tail(
nonempty(
descendants(
[Dim Date].[YQMD].currentmember
,[Dim Date].[YQMD].[Date]
)
,([Measures].[EOD], [Dim Currency].[Currency].[All])/*Změna*/
)
)
.item(0)
,[Measures].[EOD]
)
Skript může vyžadovat modifikaci podle Vaší aktuální situace v závislosti na datech a designu
Závěr
Cílem článku bylo vysvětlit problematiku a nastínit řešení. Pokud řešíte se semi-adititou nějaký problém, který nejste schopni vyřešit, napište a uvidíme co se s tím dá dělat.
Tento komentář byl odstraněn administrátorem blogu.
OdpovědětVymazat