Power BI + Power Apps – wprowadzanie danych na raporcie

To o czym często się zapomina to fakt, że Power BI oprócz tego, że sam w sobie jest dosyć rozbudowaną platformą to również jest częścią jeszcze większej architektury zwanej Power Platform. Ten twór z kolei składa się z trzech podstawowych elementów, a mianowicie:

• Power BI,
• Power Apps,
• Microfosft Flow.

Jak powszechnie wiadomo komponenty te są ze sobą dosyć mocno zintegrowane i są świetnym przykładem efektu synergii gdzie osobno dają dosyć fajne możliwości ale ich prawdziwa moc objawia się po zintegrowaniu. Integracja o której tutaj mowa jest stosunkowo prosta w implementacji i nie powinna nikomu przysporzyć większych problemów. O Power BI pisałem już dosyć dużo w ramach niniejszego bloga więc zakładam, że czytelnicy dobrze zdają sobie sprawę jak to wszystko wygląda. W czym natomiast może nam czyli ludziom związanym z systemami analitycznymi pomóc Power Apps oraz Microsoft Flow? W bardzo wielu rzeczach! Zastosowanie tego pierwszego w środowisku Power BI postaram się przedstawić w niniejszym artykule.

Czyniąc długą historię krótką powiem lakonicznie, że Power Apps jest niczym innym jak narzędziem do budowania interaktywnych aplikacji webowych lub mobilnych bez konieczności posiadania umiejętności programistycznych. Narzędzie to posiada rozbudowany interfejs graficzny do tworzenia bogatych aplikacji, a “jedyne” o co musimy zadbać to odpowiednia konfiguracja wybranych przez nas formatek i “proste” ich oprogramowanie z wykorzystaniem formuł i funkcji będących częścią Power Apps. Dlaczego słowa “jedyne” i “proste” umieściłem w cudzysłowie? Ponieważ to, że nie musimy kodować nie oznacza, że użycie wspomnianych komponentów jest trywialne. W wielu momentach musimy po prostu wiedzieć co zrobić i kombinować aby nasza aplikacja działała wydajnie, zgodnie z tym czego od niej oczekujemy. Nie odnieście jednak mylnego wrażenia ponieważ jest to naprawdę świetny serwis, który znacząco upraszcza pracę i przede wszystkim rozszerza wachlarz dostępnych możliwości.

Jedną z takich możliwości jest opcja zapisu informacji z poziomu raportu Power BI do źródła danych. Brzmi znajomo? Pamiętacie opcję zapisu zwrotnego (ang. writeback) w starych, poczciwych kostkach wielowymiarowych Analysis Services? Wielokrotnie byłem pytany przez różne osoby czy jest możliwa implementacja czegoś analogicznego w modelach tabelarycznych, a co za tym idzie w Power BI i tutaj odpowiedź brzmi nie…. chyba, że użyjemy PowerApps!

Nie przedłużając przejdźmy do praktyki i pokażmy sobie prosty przykład zapisu do źródła przy pomocy aplikacji Power Apps osadzonej na raporcie. W tym konkretnym przypadku wykorzystam bazę danych Azure SQL Database do której wrzuciłem następujące tabele z wszystkim znanej bazy AdventureWorksDW:

• FactInternetSales
• DimProduct
• DimDate

Użycie chmurowej wersji SQLa nieco uproszcza sprawę bo nie musimy bawić się w dodatkowe konfiguracje Gatewaya. Przechodząc dalej możemy powiedzieć, że wspomniane powyżej dane będą również źródłem dla naszego raportu Power BI. Żeby całe ćwiczenie miało sens do bazy danych będziemy łączyć się z PBI trybem Direct Query czyli nie będziemy importować danych do serwisu, a jedynie wysyłać zapytania w locie do źródła. Po podłączeniu do źródła model przedstawia się w następujący sposób:

W dalszym etapie stworzymy sobie miarę, którą będziemy posługiwać się do testów:

Planning Sales Amount = SUMX(FactInternetSales;[OrderQuantity]*RELATED(DimProduct[ListPrice]))

Pomijając wszelkie aspekty wydajnościowe miara ta ma na celu wyliczenie wartości sprzedaży jako iloczynu ilości sprzedanych sztuk towaru i jego ceny katalogowej. Cenę katalogową będziemy mieli możliwość edycji z poziomu Power apps dzięki czemu będziemy w stanie podejrzeć zachowanie naszej miary w zależności od zmiany wartości ceny. Aby móc dostrzec obrazowo jak to wszystko wygląda stworzyłem następujące wizualizacje na panelu raportu:

Szczegóły na temat tych wizualizacji przedstawiają się następująco:

• górny slicer to Fiscal Year,
• wykres przedstawia stworzoną przez nas miarę Planning Sales Amout w podziale na Calendar Year i MonthNumberOfYear,
• Dolny wskaźnik to miara Planning Sales Amount,
• Slicer do wyboru produktu EnglishProductName,
• Tabela z EnglishDescription do wyświetlania opisów produktów.

Ktoś w tym miejscu może powiedzieć, że wyliczenie możemy zmodyfikować z pomocą parametrów i analizy What-If (o której już wspominałem w ramach tego artykułu) jednakże jest to zdecydowanie inna funkcjonalność gdyż analizy What-If tworzą globalny parametr do którego się odnosimy, a w przypadku naszego scenariusza testowego możemy ustalić cenę na poziomie pojedynczego produktu czyli na dużo niższym poziomie.

Mając gotową wizualizację dodajmy Power Apps. Aby to zrobić musimy dodać Custom Visual z Marketplace czyli klikamy w panelu wizualizacji  trzy kropeczki, a następnie Import from marketplace:

Następnie musimy wyszukać visual dedykowany dla Power Apps i kliknąć Add:

Po dodaniu na pole raportu visuala PowerApps musimy dodać do niego dane – w naszym przypadku są to dwa atrybuty pochodzące z DimProduct, a mianowicie ProductKey oraz EnglishProductName:

Po dodaniu danych na panelu wizualizacji możemy wybrać aplikację PowerApps, która nas interesuje lub stworzyć nową:

My stworzymy sobie nową, co właściwie jest najlepszym możliwym wyborem ponieważ wybierając tą opcję w PowerApps powstanie specjalna kolekcja z danymi, które przekazaliśmy do Visuala. Do takiej kolekcji będziemy mogli się odwoływać dzięki czemu powstanie pewnego rodzaju interakcja, ale o szczegółach za chwilę. Po kliknięciu Create new naszym oczom ukaże się następujące okno:

Przejście do PowerApps Studio oznacza przejście do dedykowanej strony internetowej gdzie mamy możliwość zbudowania naszej aplikacji. Próba otwarcia przeglądarki będzie skutkowała dodatkowym komunikatem:

 

Po zaakceptowaniu powyższego komunikatu powinniśmy zobaczyć ekran Power Apps Studio podobny do poniższego:

Interfejs tworzenia aplikacji przypomina nieprzypadkowo Office. Dostaliśmy szablon aplikacji w trybie mobile (dlatego też pole aplikacji to przypominający ekran urządzenia mobilnego prostokąt). Nasza aplikacja domyślnie składa się z dwóch elementów:

• Screen1 – domyślny i póki co jedyny ekran na którym możemy umieszczać nasze formatki
• PowerBIIntegration – kolekcja w której zawierają się dane, które przekazaliśmy w Power BI Desktop (dla przypomnienia były to ProductKey oraz EnglishProductName).

Pierwszym krokiem tworzenia aplikacji będzie połączenie do bazy danych. Możliwość zdefiniowania połączenia do źródła mamy wybierając opcje Data Sources dostępną w lewym górnym rogu. W naszym przykładzie będziemy łączyć się, jak już wspomniałem do Azure SQL Database więc wyszukujemy sobie konektor SQL Server, który mamy wybrać. Oczywiście nic nie stoi na przeszkodzie aby wybrać inne źródło jednakże na ten moment Azure SQL Database będzie w sam raz (warto zwrócić uwagę na “diamenciki” widoczne przy niektórych konektorach co oznacza, że musimy posiadać określoną licencję aby móc ich użyć):

Definiowanie połączenia jest stosunkowo proste i nie będę tego tłumaczył w szczegółach, a jedynie powiem że możemy łączyć się zarówno do bazy w chmurze Azure (Connect directly (cloud services) lub używając On-premise data gateway łącząc się przez zabezpieczony tunel do SQL Server znajdującego się w naszej sieci firmowej:

Po poprawnym nawiązaniu połączenia możemy wybrać tabele, które nas interesują – w naszym przypadku będzie do DimProduct:

Mając dane musimy dodać kontrolki, które będą pełnić rolę interfejsu wprowadzania danych. W naszym przypadku wybierzemy sobie galerię wertykalną tj. wybieramy na wstążce Insert -> Gallery -> Vertical:

Na polu aplikacji pojawi się galeria z przykładowymi danymi:

Do naszych potrzeb możemy zmodyfikować galerię tak aby zawierała jedynie tytuł. Zrobimy to wybierając odpowiednią opcję w menu Layout tak jak zostało to przedstawione poniżej, wtedy też zbędne elementy tak jak obrazek i podtytuł zostaną usunięte:

Oczywiście chcemy aby w galerii były wyświetlane dane z tabeli, która została przez nas podpięta, a nie dane przykładowe. Podpięcie danych będzie możliwe wybierając we  właściwości Data Source interesujący nas zestaw. W dalszym kroku możemy kliknąć przycisk Edit aby zmapować pola źródłowego zestawu danych do poszczególnych formatek w galerii:

Po poprawnej modyfikacji właściwości powinniśmy dostrzec efekt podobny do poniższego. Warto zwrócić uwagę na to, że każda komórka pokazuje kolejną wartość, można zatem powiedzieć, że galeria jest na tyle dynamiczna, że iteruje po wszystkich wierszach zestawu źródłowego. Z tego też powodu galeria wyświetla tyle komórek ile jest niezbędne do wyświetlenia wszystkich wierszy:

Oczywiście to jeszcze nie koniec naszych działań. Celem aplikacji będzie modyfikacja wybranych danych w bazie danych, dlatego też musimy dodać dodatkowe formatki umożliwiające tego typu działanie. W Power Apps bardzo ważna jest koncepcja pierwszej komórki, to znaczy, że modyfikując pierwszą komórkę galerii modyfikujemy tak naprawdę każdą z nich. Innymi słowy możemy powiedzieć, że pierwsza komórka jest dla nas szablonem i jeśli dla przykładu dodamy tam jakąś formatkę to w analogiczny sposób zostaną zmodyfikowane pozostałe komórki. W naszym przypadku zaznaczamy pierwszą komórę i ze wstążki wybieramy Insert -> Text -> Text Input:

Działanie to powtórzyłem dwukrotnie i dodatkowo dodałem etykiety (Insert -> Label). Całość ułożyłem w pierwszej komórce (reszta się dostosowała) otrzymując następujący efekt:

Przyglądając się temu co osiągnęliśmy można dojść do wniosku, że wyświetlany “Text input” w formatkach do wprowadzania danych nie jest do końca tym czego byśmy oczekiwali, raczej spodziewalibyśmy się, że wyświetlona zostanie tam aktualna wartość kolejno atrybutów List Price oraz Description. Dlatego też po zaznaczeniu formatki przeznaczonej do wprowadzania tekstu wyszukujemy sobie właściwość, którą chcemy modyfikować (w tym konkretnym przypadku jest to właściwość Default – wartość domyślna) i w polu formuły wpisujemy ThisItem.ListPrice. Pamiętacie, że powiedziałem iż galeria niejako “iteruje” po wierszach źródła danych? Słowo kluczowe pozwala się odnieść do “bieżącego” elementu czyli tego, który przypisany jest do danej komórki galerii:

Jest jeszcze jedna modyfikacja niezbędna do uzyskania pożądanego efektu, a jest nią ustawienie sortowania całej galerii. To tez osiągniemy przy pomocy kodu, a mianowicie zaznaczając całą galerię (najlepiej robiąc to w Tree View po lewej stronie):

Następnie we właściwości Items wprowadzamy kod sortowania czyli funkcję Sort przyjmującą następujące parametry:

• Zestaw danych, który chcemy sortować,
• wyrażenie po którym chcemy sortować,
• kierunek sortowania

 

Sort('[dbo].[DimProduct]',EnglishProductName,Ascending)

 

 

W międzyczasie polecam zapisywać aplikację PowerApps używając menu File żeby uniknąć przykrej niespodzianki z utratą tego co udało nam się do tego pory osiągnąć. No dobrze, mamy na ten moment przygotowaną galerię wyświetlającą interesujące nas atrybuty. Od strony Power BI nie pozostaje nic innego jak w Custom Visualu wybrać interesującą nas aplikację:

 

Niestety na ten moment nie ma żadnej interakcji między oboma narzędziami tj. jeśli zaznaczymy coś w Power BI to ten filtr nie jest przekazywany do Power Apps. Naprawmy ten problem i zróbmy aby nasza aplikacja była interaktywna! Wróćmy do PowerApps Studio i tam we wspomnianej już właściwości Items stworzonej galerii umieśćmy poniższą formułę:

Sort(Filter('[dbo].[DimProduct]',ProductKey in [@PowerBIIntegration].Data.ProductKey),EnglishProductName,Ascending)

Robi ona nic innego jak to, że filtruje galerię po atrybucie ProductKey przekazanym z Power BI. Jak widzicie powyżej odwołanie się do Power BI następuje przez zbiór danych PowerBIIntegration w której widoczne są atrybuty jakie przekazaliśmy do Custom Visuala Power Apps. Warto pamiętać, że odświeżenie Power Apps może chwile potrwać, tak więc zaraz po zmianach mimo zapisania możemy w Power BI widzieć starszą wersję aplikacji. Po krótkiej chwili powinniśmy zobaczyć pożądany efekt:

Teraz nie pozostaje nic innego jak zaprogramować naszą kontrolkę w taki sposób, że po zmianie wartości ma być wysyłany update do bazy z wprowadzoną przez nas wartością. Zrobimy to ustawiając wydarzenie OnChange czyli przy każdej zmianie wartości w naszej kontrolce ma być wykonany określony kod. W tym konkretnym przypadku chcemy zaktualizować wartość w bazie danych, a zrobimy to najprościej rzecz ujmując poprzez wywołanie funkcji PATCH:

Patch('[dbo].[DimProduct]', ThisItem, { ListPrice: Value(TextInput1.Text)})

Funkcja ta  w tym konkretnym przypadku działa analogicznie jak UPSERT czyli wstawia nowe wartości lub aktualizuje istniejące. Pierwszym argumentem jest wskazanie źródła danych, drugi argument wskazuje, że odnosimy się do bieżącego elementu, a trzeci to nic innego jak wskazanie kolumny, którą aktualizujemy i jaką wartością. Dodatkowo użyłem funkcji Value aby przekonwertować wartość tekstową na liczbową. W tym miejscu warto również zwrócić uwagę na to, że źródłowa tabela musi posiadać klucz główny aby w ogóle taka operacja mogła zajść. Jeśli nasza tabela nie ma klucza to będziemy mogli tylko i wyłącznie dane wyświetlać.

Analogiczną operację wykonałem dla pola Description, efekt końcowy prezentuje się w następujący sposób:

To na co możecie zwrócić uwagę to fakt, że po zmianie wartości w Power App odświeżyłem cały raport co sprowadza się do wysłania zapytań przez Power BI Desktop do źródłowej bazy. Odświeżenie jest wymagane bo Power BI w trybie Direct Query w żaden sposób się nie odświeża w zależności od zmian w źródle więc najlepiej jest odświeżyć raport.

Jako uzupełnienie zróbmy coś takiego, że nasza aplikacja działa tylko i wyłącznie wtedy gdy wybrany jest tylko jeden produkt. Dlatego też w aplikacji dodałem prostokąt (Insert -> Icons->Rectangle) o wielkości całego ekranu z napisem  “Please select one product” (Insert -> Text)  tak jak zostało to przedstawione poniżej:

Następnie kształt ten wraz z napisem ukryłem:

Aby ten napis pojawiał się w zależności od konkretnych warunków ( w naszym przypadku w sytuacji gdy wybrany jest więcej niż jeden produkt) musimy zdefiniować tak zwaną zasadę (ang. Rule) która działa analogicznie do znanych z Excela makr. Zdefiniujmy zatem tego typu strukturę wybierając okno Rules znajdujące się obok właściwości i klikając New Rule:

Następnie definiujemy warunek zwracający wartość True lub False, w przedstawianym przypadku będzie to nic innego jak sprawdzenie ile mamy wartości ProductKey przy pomocy funkcji count:

If(Count(PowerBIIntegration.Data.ProductKey)>1,true,false)

W dalszej kolejności definiujemy akcje (ang.Actions) czyli wykonujemy czynność analogiczną do nagrywania makra co myślę łatwiej zrozumieć na wizualizacji niż w słowie pisanym:

Efekt naszych działań powinien wyglądać następująco:

W samym Power BI wygląda to zgodnie z naszymi oczekiwaniami:

Jak możecie zauważyć to tylko jedna z możliwości wykorzystania Power Apps wewnątrz Power BI. Jeśli do tych dwóch narzędzi dorzucimy również Microsoft Flow to wiele rzeczy, które wydawały się nam nie do zrobienia staje się możliwe. Specyfika tworzenia aplikacji w Power Apps rządzi się swoimi prawami i w wielu przypadkach trzeba pamiętać o wydajności i odpowiednich strukturach źródłowych aby osiągnąć to czego oczekujemy. Z całą pewnością nie jest to ostatni wpis tego typu na blogu i postaram się pokazać wiele różnych możliwości jakie daje nam Power Platform. Na ten moment dziękuję za poświęcony czas i mam nadzieję, że przedstawione informacje okazały się ciekawe i przydatne.