Power BI – parametry, szablony i słów kilka o połączeniach bezpośrednich

Z miesiąca na miesiąc widzimy jak Power BI staje się coraz lepszym narzędziem pozwalającym na budowanie różnego rodzaju wizualizacji oraz zestawień. Ze względu na moje poprzednie doświadczenia związane z raportowaniem na potrzeby przeróżnych organizacji mogę stwierdzić, że jednym z pierwszych wymogów wobec narzędzia tego typu jest parametryzacja zapytań do źródła danych. Mam tutaj na myśli możliwość przefiltrowania danych zanim jeszcze trafią do narzędzia raportującego. Czy Power BI posiada taką możliwość? Z całą pewnością tak! Dziś powiemy sobie parę słów na ten temat – zapraszam do lektury.

Dynamiczne przetwarzanie zapytań po stronie źródłowej bazy danych (bo na tego typu źródle się skupimy w ramach niniejszego artykułu) jest możliwe na kilka sposobów. Pierwszym z nich jest niewątpliwie tryb zapytań Direct Query (lub Live connection jeśli łączymy się do modelu analitycznego Analysis Services- oba mechanizmy fundamentalnie się od siebie różnią ale na potrzeby tego artykułu możemy uprościć rzeczywistość i powiedzieć, że działają w analogiczny sposób/). Tryb ten polega na tym, że Power BI służy jedynie do wyświetlania i wizualizacji danych, a całość obliczeń wykonywana jest po stronie źródła. Aby sprawdzić działanie tego mechanizmu połączmy się do serwera źródłowego używając tego trybu. Sprowadza się to do wybrania ze wstążki Get Data, a następnie rodzaju bazy danych do której chcemy się podłączyć (w moim przypadku będzie to oczywiście SQL Server). Po potwierdzeniu wyboru powinniśmy dostrzec okno podobne do przedstawionego poniżej. Wszystko co musimy tutaj ustawić to oprócz adresu serwera musimy w sekcji Data Connectivity mode zaznaczyć Direct Query (jeśli połączymy się do Analysis Services poszczególne wartości będą pozwalały na dokonanie wyboru pomiędzy Import oraz Live Connection):

Po wyborze interesujących nas obiektów w konkretnej bazie danych (w przypadku Direct Query musimy wybrać obiekty, a nie łączymy się do całej bazy danych tak jak mogło się wydawać na pierwszy rzut oka) stworzone zostanie połączenie umożliwiające odpytywanie ich “w locie”:

Każda operacja będzie skutkowała wygenerowaniem zapytania w języku SQL i przesłaniem go do bazy danych. Dla przykładu poniższa wizualizacja spowodowała wygenerowanie dosyć pokaźnego rozmiarowo zapytania TSQL:

SELECT 
TOP (1000001) [t3].[Fiscal Year],SUM([t0].[Tax Amount])
 AS [a0]
FROM 
(
((select [$Table].[Sale Key] as [Sale Key],
    [$Table].[City Key] as [City Key],
    [$Table].[Customer Key] as [Customer Key],
    [$Table].[Bill To Customer Key] as [Bill To Customer Key],
    [$Table].[Stock Item Key] as [Stock Item Key],
    [$Table].[Invoice Date Key] as [Invoice Date Key],
    [$Table].[Delivery Date Key] as [Delivery Date Key],
    [$Table].[Salesperson Key] as [Salesperson Key],
    [$Table].[WWI Invoice ID] as [WWI Invoice ID],
    [$Table].[Description] as [Description],
    [$Table].[Package] as [Package],
    [$Table].[Quantity] as [Quantity],
    [$Table].[Unit Price] as [Unit Price],
    [$Table].[Tax Rate] as [Tax Rate],
    [$Table].[Total Excluding Tax] as [Total Excluding Tax],
    [$Table].[Tax Amount] as [Tax Amount],
    [$Table].[Profit] as [Profit],
    [$Table].[Total Including Tax] as [Total Including Tax],
    [$Table].[Total Dry Items] as [Total Dry Items],
    [$Table].[Total Chiller Items] as [Total Chiller Items],
    [$Table].[Lineage Key] as [Lineage Key]
from [Fact].[Sale] as [$Table]) AS [t0]

 left outer join 

(select [$Table].[Date] as [Date],
    [$Table].[Day Number] as [Day Number],
    [$Table].[Day] as [Day],
    [$Table].[Month] as [Month],
    [$Table].[Short Month] as [Short Month],
    [$Table].[Calendar Month Number] as [Calendar Month Number],
    [$Table].[Calendar Month Label] as [Calendar Month Label],
    [$Table].[Calendar Year] as [Calendar Year],
    [$Table].[Calendar Year Label] as [Calendar Year Label],
    [$Table].[Fiscal Month Number] as [Fiscal Month Number],
    [$Table].[Fiscal Month Label] as [Fiscal Month Label],
    [$Table].[Fiscal Year] as [Fiscal Year],
    [$Table].[Fiscal Year Label] as [Fiscal Year Label],
    [$Table].[ISO Week Number] as [ISO Week Number]
from [Dimension].[Date] as [$Table]) AS [t3] on 
(
[t0].[Delivery Date Key] = [t3].[Date]
)
)
 left outer join 

(select [$Table].[Stock Item Key] as [Stock Item Key],
    [$Table].[WWI Stock Item ID] as [WWI Stock Item ID],
    [$Table].[Stock Item] as [Stock Item],
    [$Table].[Color] as [Color],
    [$Table].[Selling Package] as [Selling Package],
    [$Table].[Buying Package] as [Buying Package],
    [$Table].[Brand] as [Brand],
    [$Table].[Size] as [Size],
    [$Table].[Lead Time Days] as [Lead Time Days],
    [$Table].[Quantity Per Outer] as [Quantity Per Outer],
    [$Table].[Is Chiller Stock] as [Is Chiller Stock],
    [$Table].[Barcode] as [Barcode],
    [$Table].[Tax Rate] as [Tax Rate],
    [$Table].[Unit Price] as [Unit Price],
    [$Table].[Recommended Retail Price] as [Recommended Retail Price],
    [$Table].[Typical Weight Per Unit] as [Typical Weight Per Unit],
    [$Table].[Photo] as [Photo],
    [$Table].[Valid From] as [Valid From],
    [$Table].[Valid To] as [Valid To],
    [$Table].[Lineage Key] as [Lineage Key]
from [Dimension].[Stock Item] as [$Table]) AS [t5] on 
(
[t0].[Stock Item Key] = [t5].[Stock Item Key]
)
)

WHERE 
(
([t5].[Size] IN (N'200L',N'1/50 scale',N'100L',N'18mm'))
)

GROUP BY [t3].[Fiscal Year]

Zapytanie mimo, że długie to w gruncie rzeczy nie jest skomplikowane i nie powinno przysporzyć problemów. Oczywiście w bardziej zaawansowanych raportach możemy otrzymać dużo większe zapytania z wyższym stopniem zaawansowania. Taki urok trybów generujących kod automatycznie – w przypadku gdy odpowiednio dobierzemy indeksy to i tak działanie takich zapytań może być bardzo dobre. Nie wgłębiając się w szczegóły działania trybu Direct Query możemy zadać sobie pytanie czy mamy możliwość wpłynięcia na kod generowany przez Power BI?

Załóżmy, że mamy gotowe zapytanie lub procedurę składowaną, której chcemy użyć w Power BI:

SELECT 
	DSI.Size
	,DD.[Fiscal Year]
	,DD.[Fiscal Month Number]
	,DD.Date
	,SUM(F.[Tax Amount]) AS [Tax Amount] 
FROM 
	[Fact].[Sale] AS F
JOIN 
	[Dimension].[Stock Item] AS DSI
ON 
	DSI.[Stock Item Key]=F.[Stock Item Key]
JOIN 
	[Dimension].[Date] AS DD
ON
	DD.Date=F.[Invoice Date Key]
GROUP BY
	DSI.Size
	,DD.[Fiscal Year]
	,DD.[Fiscal Month Number]
	,DD.Date

Mamy odpowiednie miejsce gdzie możemy go użyć, jest nim mianowicie okno SQL statement w sekcji Advanced options:

Zapytanie to zostanie potraktowane jako podzapytanie wysłane do źródłowej bazy danych:

SELECT 
TOP (1000001) [t6].[Fiscal Year],SUM([t6].[Tax Amount])
 AS [a0]
FROM 
(
(SELECT 
	DSI.Size
	,DD.[Fiscal Year]
	,DD.[Fiscal Month Number]
	,DD.Date
	,SUM(F.[Tax Amount]) AS [Tax Amount] 
FROM 
	[Fact].[Sale] AS F
JOIN 
	[Dimension].[Stock Item] AS DSI
ON 
	DSI.[Stock Item Key]=F.[Stock Item Key]
JOIN 
	[Dimension].[Date] AS DD
ON
	DD.Date=F.[Invoice Date Key]
GROUP BY
	DSI.Size
	,DD.[Fiscal Year]
	,DD.[Fiscal Month Number]
	,DD.Date)
)
 AS [t6]
WHERE 
(
([t6].[Size] IN (N'18mm',N'100L',N'10m',N'200L'))
)

GROUP BY [t6].[Fiscal Year]

Można więc powiedzieć, że mamy pewną kontrolę nad tym co się dzieje po stronie bazy danych. Jednocześnie musimy zdać sobie sprawę z faktu, że nie wszystko możemy wstawić w sekcję SQL Statement jeśli używamy jednocześnie trybu Direct Query. Logicznie rzecz biorąc możemy tam umieścić wszystko co może stać się podzapytaniem. W momencie gdy spróbujemy użyć niedozwolonego konstruktu dostaniemy komunikat błędu np. w przypadku użycia CTE:

Alternatywą do Direct Query jest tryb Importu, który przechowuje dane wewnątrz Power BI. Dane przechowywane w modelu są niczym innym jak lokalnym modelem Analysis Services Tabular. Połączenie ze źródłem będzie miało miejsce tylko przy odświeżeniu danych, bieżące działania użytkowników będą wykonywane na wspomnianym modelu tabelarycznym. W tym miejscu mamy jednak możliwość sterowania “tym” co zostanie pobrane do modelu. Możemy dla przykładu użyć tzw. Query Parameters.

Zobrazujmy sobie to na przykładzie. Tym razem wybieramy tryb Import i w zależności od potrzeb możemy wstawić gotowe zapytanie lub dokonać transformacji w edytorze zapytań. Warto mieć tutaj na uwadze, że jeśli nie mamy gotowego zapytania, które zwraca ostateczną wersję danych, to nie warto wpisywać tego zapytania gdyż działanie to ogranicza użycie mechanizmu Query Folding. Mechanizm ten stara się w zależności od wybranych transformacji i kodu M, jak najwięcej przerzucić na źródło danych. Najważniejszą techniką optymalizacyjną transformacji w Power BI jest zadbanie aby jak najwięcej działo się w źródle, czyli innymi słowy dbanie o to aby Query Folding działał z jak największą efektywnością.  W przykładzie który chciałbym Wam zaprezentować użyjemy gotowego zapytania – jest ono dokładnie takie jak wskazałem wyżej oprócz tego, że dodatkowo filtrujemy tutaj pole Fiscal Year:

Po kliknięciu OK mamy możliwość przejścia do okna transformacji i to właśnie uczyńmy klikając Edit:

W edytorze zapytań naszym celem będzie parametryzacja wpisanego wcześniej Fiscal Year. Chcemy dać możliwość użytkownikom aby to oni sterowali tym jaki rok fiskalny będzie zaciągany. Po kliknięciu przycisku Manage Parameters możemy z listy wybrać New Parameter:

Pojawi się okno definiowania parametrów. W tym miejscu oprócz nazwy i opisu możemy wybrać typ danych oraz co będzie źródłem wartości.  W przypadku wyboru wartości dla parametru mamy trzy możliwości:

• wartość może być wpisana wprost do parametru (Any Value),
• wartość może mieć postać wielu wartości (List of Values),
• wartość może pochodzić z wyniku innego zapytania (Query).

Na ten moment ustawimy sobie wartość “na sztywno” na rok 2015 – będzie to niejako wartość domyślna parametru.

Jeżeli źródłem wartości dla naszego parametru będzie np. lista to będziemy mieli możliwość ustawienia tzw. “Default Value”. Tutaj warto zaznaczyć czym jest wartość domyślna, a czym wartość podana w “Current Value”. Wartość domyślna pozwala zdefiniować to co jako pierwsze zobaczy użytkownik chcący ustawić parametr. Z kolei Current Value to wartość jaka będzie używana przez parametr dopóki ktoś tego nie zmieni. Warto od razu uzmysłowić sobie te różnice, które mogą być początkowo trochę niezrozumiałe (szczególnie dla użytkowników Report0ing Services gdzie wartość domyślna pełniła to co w Power BI robi “Current Value” i ” Default Value” w tym samym czasie).

Po zatwierdzeniu definicji naszego parametru przyciskiem OK musimy go jakoś podpiąć pod nasze główne zapytanie. Możemy to zrobić w bardzo prosty sposób z interfejsu graficznego lub ręcznie modyfikując kod. Póki co wybierzmy to drugie rozwiązanie. Tak więc musimy przejść do edycji zapytania, które chcemy parametryzować klikając na pierwszy i jedyny dostępny krok o nazwie Source prawym przyciskiem myszy i wybierając Edit Settings:

Teraz jedyne co musimy zrobić to po prostu zamiast sztywnej wartości podstawić w formule zapis podobny do poniższego. Parametr musiałem przekonwertować na tekst, aby móc go dołączyć do pozostałego tekstu stanowiącego zapytanie:

Na powyższym zrzucie można zauważyć dużo “białych znaków”, które związane są z wcięciami w naszym kodzie co nieco zakłóca odbiór całości. Warto pomyśleć o użyciu np. procedury składowanej w przypadku bardziej skomplikowanych zapytań. Mimo, że edycja kodu nie jest zbyt trudna to z GUI sprawa jest jeszcze prostsza – jedyne co musimy zrobić to przejść do standardowej filtracji:

Tam mamy możliwość wyboru parametru:

 

Gdy już mamy gotowe zapytanie z parametrem powinniśmy w podglądzie zobaczyć dane dla roku 2015 czyli zgodne z wartością ustawioną jako Current Value. Jeśli nie chcemy aby Power BI za każdym razem po zmianie parametru żądał od nas potwierdzenia poświadczeń możemy wejść w ustawienia File -> Options -> Security i tam odznaczyć opcję Require user approval for new native database queries:

Teraz każda zmiana wartości parametru spowoduje, że pobrane zostaną nowe, dostosowane dane:

Wartość parametru można ustawić również z poziomu samego raportu wybierając opcję Edit queries -> Edit Parameters:

Niestety na ten moment (Marzec 2018) nie jest możliwa bezpośrednia zmiana wartości parametrów z poziomu portalu Power BI. Jest to jedna z funkcjonalności na którą najbardziej czekam i na portalu ideas.powerbi.com można znaleźć jedną z propozycji użytkowników aby coś takiego dodać – jeśli chcecie zawsze możecie na nią zagłosować (link).

Oczywiście parametry nie są jedyną opcją na filtrację danych – możemy osiągnąć podobną funkcjonalność odpytując np. plik Excel. Wystarczy, że w tym właśnie pliku w określoną komórkę wprowadzimy wartość, która będzie następnie używana przy odświeżeniu raportu. Stworzyłem taki Excel i przedstawia się on następująco:

Następnie stworzyłem połączenie do tego Excela – w tej demonstracji umieściłem ten plik na moim lokalnym komputerze, ale równie dobrze może to być udział sieciowy lub plik może znajdować się np. na onedrive.

Oczywiście aby portal Power BI miał dostęp do źródeł on-premise musimy skonfigurować tzw. Gateway czyli bramę umożliwiającą komunikację chmury z zasobami lokalnymi (na ten moment uprośćmy to zagadnienie do tego prostego sformułowania). Gdy już mamy naszą wartość z Excela zaciągniętą możemy ją podpiąć pod nasze główne zapytanie. Aby to zrobić wystarczy wpisać nazwę zapytania zawierającego naszą wartość z Excela:

Gdy mamy wszystko skonfigurowane przy odświeżeniu dane zostaną pobrane zgodnie z tym co znajduje się w Excelu:

Oba podejścia mają wady i zalety. Niewątpliwą zaletą Query Parameters jest możliwość ich użycia  z szablonami czyli Power BI Templates. Szablon to nic innego jak definicja raportu nie zawierająca żadnych danych. W momencie jeśli mamy zdefiniowany parametr w raporcie i zapiszemy go jako szablon, to osoba, która otworzy ten szablon zobaczy monit z prośbą o podanie wartości parametru. Tworzenie szablonu jest bardzo proste i sprowadza się do wybrania odpowiedniej opcji z menu przy zapisie:

Tak jak wspomniałem osoba otwierająca plik szablonu (rozszerzenie pbit) zobaczy monit:

Parametry ani tym bardziej wartości pobierane z innego źródła do filtracji nie mogą być traktowane jako mechanizm bezpieczeństwa, bo jeżeli ktoś tylko potrafi używać klawiatury i myszki ten z całą pewnością będzie potrafił to ograniczenie obejść. Myślę, że sposobów filtracji w Power BI znalazło by się jeszcze kilka ale na ten moment mam nadzieję, że przedstawione treści okażą się dla Was przydatne.