SQL Server 2016 – zmienne, komentowanie miar oraz tabele kalkulowane w Tabular

Kontynuujemy naszą serię opisującą nowości w SQL Server 2016 – dziś powiemy sobie czym są zmienne w języku DAX, które po Power BI i Excel 2016 zagościły w modelu tabelarycznym Analysis Services. Ponadto poruszymy temat komentowania w miarach oraz tabel kalkulowanych – zapraszam do lektury.

Zmienne to prawdopodobnie jedno z najpopularniejszych pojęć w szeroko pojętym IT. Każdy język programowania posiada mniej lub bardziej rozbudowane implementacje zmiennych. Nie inaczej było z językami platformy danych Microsoft – nikt z nas nie wyobraża sobie chyba pracy z językiem TSQL bez użycia zmiennych, które niejako nadawały sens użycia procedur składowanych. Istniały jednak pewne wyjątki od tej reguły, które nie posiadały konstruktów tego typu – jednym z nich był właśnie język związany z modelem tabelarycznym, Power BI i Power Pivot czyli DAX. Nie posiadał on zmiennych – ale czy były one niezbędne? Według mnie nie – przecież sobie jakoś radziliśmy bez nich prawda? Co nie znaczy, że ich pojawienie nie pozwoli nam na osiągnięcie nowych rzeczy. Ten kto napisał choć parę linijek w DAX ten wie, że jest to język dosyć specyficzny i może zadawać sobie pytanie gdzie tam miejsce na zmienne? Postaram się to przedstawić na prostym przykładzie właśnie z wykorzystaniem zmiennych.

Na samym wstępie chciałbym przedstawić podstawową składnię tworzenia zmiennych w DAX

VAR NazwaZmiennej =
    definicja
VAR NAzwaZmiennej2 =
    definicja2
RETURN
uzycie

Tak więc deklarację zmiennych zaczynamy słowem kluczowym VAR następnie podajemy jej nazwę i po znaku równości definicję. Jak widać nie musimy jawnie wskazywać typów danych, a samych zmiennych możemy mieć bardzo wiele. Po zakończeniu definiowania musimy wstawić słowo kluczowe RETURN po którym następuje właściwa treść miary (lub kolumny kalkulowanej). Stwórzmy sobie więc miarę na przykładowym projekcie modelu tabelarycznego opartego na AdventureWorksDW2014:

FreightByAmount :=
VAR vAmount =
    SUM ( 'FactInternetSales'[ExtendedAmount] )
VAR vFreight =
    SUM ( 'FactInternetSales'[Freight] )
RETURN
    DIVIDE ( vFreight, vAmount )

Mamy miarę FreightByAmount (nie przywiązujcie proszę wagi do biznesowego sensu powyższej miary – ma ona jedynie pokazać zastosowanie), która jest ilorazem sumy pola ExtendedAmount i sumy pola Freight. Oczywiście zmienne nie była tu konieczna – równie dobrze mogliśmy napisać sumy bezpośrednie w funkcji DIVIDE, jednakże zapis ze zmiennymi może być nieco czytelniejszy. Zdarza się, że miary bywają dużo bardziej skomplikowane – wtedy też użycie zmiennych może być zbawienne dla czytelności. Jeśli chodzi o nazewnictwo zmiennych to nie może ono zawierać słów kluczowych, słowa VAR, nazw obiektów oraz liczb jako pierwszych znaków nazwy. Standardowo możemy używać znaków alfanumerycznych.

Dodatkowo przy testowaniu tejże funkcjonalności zapewne zauważycie, że poprawiony nieco został Intellisense, który wykryje naszą zmienną i wyświetli gustowny znacznik graficzny 🙂

To co trzeba sobie powiedzieć w tym miejscu to fakt, iż zmienne zadeklarowane w taki sposób jak przedstawiony powyżej są zmiennymi lokalnymi tzn. są widoczne tylko w definicji danej miary. Co jednak gdy chcemy takiej konstrukcji użyć w innych miarach? Myślę, że do tego celu możemy zastosować podejście znane z poprzednich wersji usług analitycznych tj. stworzyć miarę “techniczną” niewidoczną dla narzędzi klienckich, która tak naprawdę może pełnić rolę zmiennej globalnej np. poniższa miara oblicza bieżącą datę:

vgToday:=FORMAT(NOW(),"yyyyMMdd")

bez problemu możemy ją użyć w innej mierze:

SalesOfCurrentDay :=
CALCULATE (
    SUM ( [SalesAmount] ),
    FILTER (
        'FactInternetSales',
        'FactInternetSales'[OrderDate] = VALUE ( [vgToday] )
    )
)

Możecie zadać sobie pytanie dlaczego użyłem przy nazwie vg – otóż jest to moja nomenklatura gdzie nazwy zmiennych poprzedzam przedrostkiem “v” natomiast miary pełniące rolę zmiennych globalnych poprzedzam właśnie przedrostkiem “vg”. Myślę, że nazywanie obiektów modelu w odpowiedni sposób jest kluczem do sukcesu w późniejszych staraniach w “połapaniu się” o co chodzi w modelu.

Chciałbym również powiedzieć o nowych możliwościach komentowania w definicji miary – od wersji 2016 (dopiero!) możemy dodawać komentarze w tym właśnie miejscu. Wcześniej musieliśmy to robić w osobnych komórkach – teraz wystarczy wpisać “//” aby mieć stworzony komentarz:

SumOfOrderQty:=SUM('FactInternetSales'[OrderQuantity])
//ta miara oblicza sume pola Order Quantity

To jedna z tych zmian, które aż się prosiły o implementację od pierwszej wersji narzędzia..

Ostatnią zmianą o której chciałbym powiedzieć są tabele kalkulowane (ang. Calculated Tables). Są to tabele wygenerowane przez wyrażenie DAX. Tabele w ten sposób wygenerowane występują tylko w modelu. Dla przykładu poniższy DAX wygeneruje przy pomocy funkcji CALENDAR (która również została dodana w najnowszej wersji) zestaw dat od 1 stycznia 2017 do 30 marca 2017.

=CALENDAR(DATE(2017,1,1),DATE(2017,3,30))

Definicja tabeli kalkulowanej może być stworzona przez każde wyrażenie, które zwraca tabelę – dlatego też w łatwy sposób możemy stworzyć kopię istniejącej tabeli bez potrzeby ładowania drugi raz tej samej tabeli ze źródła. Możemy również wybrać np. podzbiór danych z innej tabeli używając dla przykładu funkcji FILTER:

=FILTER('DimProduct','DimProduct'[Color]="Red")

Jest to bardzo przydatna funkcjonalność, która z całą pewnością znajdzie zastosowanie w praktyce. Oczywiście to nie wszystkie zmiany w modelu tabelarycznym Analysis Services 2016 – wcześniej opisaliśmy już DBCC dla Analysis Services oraz translacje i tabular model explorer – pojawiło się również wiele innych rzeczy jak np. nowe funkcje czy filtrowanie dwukierunkowe – ale o tym powiemy sobie w ramach innego artykułu.