Power BI – Composite models i relacje wiele do wielu

W ostatnim czasie Power BI otrzymał kolejną potężną aktualizację wprowadzającą do usługi szereg udogodnień i nowych funkcjonalności. Najbardziej przydatnymi funkcjonalnościami, które zostały wprowadzone są bez wątpienia możliwość tworzenia raportów w tzw. Mixed mode czyli w ramach pojedynczej aplikacji raportowej możemy łączyć tabele podłączone w trybie Direct Query wraz z tymi podłączonymi w trybie Import. Ponadto Composite models (bo taka jest ich oficjalna nazwa) pozwalają nam łączyć tabele z kilku źródeł w trybie Direct Query co wcześniej nie było możliwe. Drugą większą nowością jest możliwość tworzenia relacji wiele do wielu pomiędzy dwoma tabelami. Jak to wszystko wygląda w praktyce? Sprawdźmy to na konkretnych przykładach.

Na samym początku powiedzmy sobie parę słów w jakich trybach (ang. Storage mode) możemy zaimportować poszczególne tabele. W tym momencie mamy ich aż trzy( pomińmy specjalny przypadek jakim jest Analysis Services Live Connection ):

• Import – dane są cache’owane w modelu. Każde zapytanie z poziomu wizualizacji odpytuje dane zawarte w modelu, które mogą być odświeżane według określonego harmonogramu lub na żądanie. Podczas odpytywania z poziomu wizualizacji dane źródłowe nie są w ogóle “dotykane”.
• Direct Query – dane nie są cache’owane w modelu. Każde zapytanie z poziomu wizualizacji wysyła zapytanie w odpowiednim języku do źródła.
• Dual – nowy tryb działania w którym dane zachowują się tak jakby były w trybie Import lub Direct Query w zależności od kontekstu zapytania tj. w niektórych przypadkach zapytanie będzie realizowane z cache, a w innym przypadku zapytanie będzie wysłane do źródła.

Wygląda to dosyć interesująco dlatego przejdźmy do praktycznego przykładu.

Omawiana funkcjonalność na moment pisania niniejszego artykułu jest w fazie Preview tak więc musimy ją włączyć. Zrobimy to klikając File -> Options and Settings -> Options. W otwartym oknie musimy wybrać Preview Features i zaznaczyć Composite Models:

Po zrestartowaniu narzędzia spróbujmy pobrać dane w trybie Direct Query:

Jeśli chodzi o obiekty to pobrałem FactInternetSales oraz DimProduct z AdventureWorksDW2017. Informacja w dolnej części ekranu w Power BI Desktop pokazuje mi informację, że jestem w trybie Direct Query:

Następnie otworzyłem nowe połączenie (tym razem w trybie Import) i pobrałem DimCustomer:

Wcześniej nie byłem w stanie czegoś takiego dokonać, teraz natomiast nie ma z tym żadnego problemu. Zanim jeszcze mój model zostanie zbudowany dostanę ostrzeżenie o potencjalnym zagrożeniu związanym z tym, że dane z jednego źródła mogą być przesłane do drugiego źródła (np. w ramach filtracji):

W tym miejscu warto się zastanowić dłuższą chwilę o tym do jakich danych się łączymy i czy aby na pewno przesył danych pomiędzy źródłami jest możliwy i nie będzie dla nas problemem. Oczywiście problem ten istnieje w momencie gdy łączymy ze sobą kilka źródeł gdzie przynajmniej jedno z nich pozwala na połączenie Direct Query. Decyzja dotycząca przesyłu danych jest bliźniaczo podobna do tej, którą musimy podjąć w przypadku Privacy Levels.

Po podjęciu decyzji, zatwierdzeniu powyższego komunikatu i pobraniu danych w dolnej części ekranu dostępna jest informacja mówiąca o tym, że mam do czynienia z Mixed Mode:

To, że nasz model jest w trybie mieszanym powoduje kilka ciekawych właściwości jak np. to, że w widoku danych dostępne będą dla mnie tylko tabele zaimportowane do modelu. Po wybraniu tabeli w Direct Query dostaniemy stosowny komunikat:

Gdy przełączymy się do widoku diagramu i będziemy chcieli stworzyć relacje pomiędzy zaimportowaną tabelą, a tabelą w DQ to jedynie relacja typu Many-To-Many jest dla nas dostępna (na ten moment) bez względu na rzeczywistą liczebność kolumn uczestniczących w relacji:

Jest to spowodowane tym, że z technicznego punktu widzenia bardzo ciężko jest zweryfikować liczebność poszczególnych pól pomiędzy tabelami w różnych trybach lub z różnych źródeł. Co prawda mając odpowiednią wiedzę biznesową moglibyśmy wskazać liczebność relacji jednak Power BI nie jest tego w stanie potwierdzić i w przypadku błędnej konfiguracji możemy się domyślać, że skutkowałoby to błędami w działaniu całej aplikacji, a tego za wszelką cenę powinniśmy unikać. To, że wybierzemy typ relacji wiele-wiele powoduje, że niektóre funkcje języka DAX jak np. opierająca się o liczebność RELATED nie będą działać.

Po zdefiniowaniu relacji utworzyłem prosty raport składający się z wykresu kolumnowego przedstawiającego sumę pola FactInternetSales[SalesAmount] dla każdego DimProduct[Color] oraz slicera z polem DimCustomer[Gender]:

Przypomnę, że DimCustomer jest w trybie Import, a FactInternetSales oraz DimProduct są w trybie DirectQuery.Po wybraniu odpowiednich wartości na slicerze wygenerowane zostało następujące zapytanie do źródła (wersja skrócona):

SELECT 
TOP (1000001) [t0].[CustomerKey],[t1].[Color],SUM([t0].[SalesAmount])
 AS [a0]
FROM 
((select [$Table].[ProductKey] as [ProductKey],
...
from [dbo].[FactInternetSales] as [$Table]) AS [t0]

 left outer join 

(select [$Table].[ProductKey] as [ProductKey],
....
from [dbo].[DimProduct] as [$Table]) AS [t1] on 
(
[t0].[ProductKey] = [t1].[ProductKey]
)
)

WHERE 
(
1 = 1
)

GROUP BY [t0].[CustomerKey],[t1].[Color]

Z powyższego kodu możemy wywnioskować w jaki sposób działają Composite Models w przypadku łączenia danych z tabeli zaimportowanej i tej działającej w Direct Query. Wysyłanie w warunku WHERE wszystkich wartości klucza DimCustomer gdzie Gender=’M’ nie jest zbyt efektywne. Dlatego też wygenerowane zostało zapytanie, które grupuje wartości po kluczu złączenia z tabelą zaimportowaną tj. w tym konkretnym przypadku po CustomerKey. Samo złączenie i filtracja następują już po stronie Power BI.

Pamiętajmy jednakże, że tryb Direct Query, a co za tym idzie Composite models posiadają szereg ograniczeń, które należy mieć w tyle głowy w momencie podejmowania decyzji o implementacji tego czy też innego rozwiązania. Przede wszystkim zapytanie wygenerowane przez Direct Query nie może zwrócić więcej niż 1 milion wierszy co w niektórych przypadkach może stanowić problem. Jeśli używacie konstrukcji języka SQL takich jak procedury składowane itp. to zapomnijcie o trybie Direct Query. Ograniczenie to ma swoje uzasadnienie bo w jaki efektywny sposób Power BI miałby połączyć rezultat procedury z rezultatem standardowego zapytania SELECT? No właśnie.

W tym momencie Direct Query wspiera tylko niektóre źródła danych. Zanim zaczniemy rozważać użycie Direct Query warto zapoznać się z aktualną listą wspieranych źródeł danych, którą znajdziecie tutaj. Na moment pisania tego artykułu Composite Models nie wspierają połączeń do Analysis Services, zarówno Tabular jak i Multidimensional. O ile wsparcia dla tego pierwszego możemy się spodziewać o tyle kostki wielowymiarowe przez ich omijanie w nowych funkcjonalnościach powoli przechodzą do lamusa.

Poniższe źródła nie są wspierane przez Composite Models:

• SAP HANA
• SAP Business Warehouse
• SQL Server Analysis Services
• Power BI datasets

Kolejnym ograniczeniem jest to, że tabele które zostaną stworzone w modelu jako “Imported” nie mogą być przekonwertowane na “Direct Query”. Ten problem dosyć łatwo ominąć pobierając tabelę od nowa jednakże będzie wiązać się to z ponownym definiowaniem relacji itp. W drugą stronę tego problemu nie ma i tabela w trybie Direct Query bezproblemowo może zostać zamieniona na Import.

Przejdźmy do zilustrowania zachowania Power BI w przypadku posiadania w modelu kilku tabel w różnych trybach ale pochodzących z jednego źródła. Tym razem nasz zbiór testowy to FactInternetSales, DimProduct oraz FactResellerSales:

 

Zielonym kolorem oznaczyłem tabelę w trybie Import, żółty to tabela w trybie Dual, a czerwony to Direct Query. Power BI w zależności od wykorzystania danych postara się w najbardziej efektywny sposób rozwiązać połączenia między tabelami. W przypadku gdy na naszej wizualizacji wyświetlamy dane z DimProduct i FactInternetSales – żadne zapytanie nie zostanie wysłane do źródła. Dzieje się tak dlatego ze względu na fakt, że sama tabela faktów jest zaimportowana do modelu, a DimProduct jest o wiele mniejszą tabelą słownikową i łatwiej będzie rozwiązać to złączenie po stronie Power BI niż wysyłać cokolwiek do źródła. Ciekawiej robi się w przypadku gdy połączymy dane FactResellerSales i DimProduct. W tym przypadku sytuacja jest odwrotna i Power BI zdecyduje, że wykonanie zapytania po stronie źródła da lepsze rezultaty. Samo zapytanie wysłane do źródła wygląda w standardowy sposób (forma skrócona):

SELECT 
TOP (1000001) [t6].[Color],SUM([t5].[SalesAmount])
 AS [a0]
FROM 
((select [$Table].[ProductKey] as [ProductKey],
 .....
from [dbo].[FactResellerSales] as [$Table]) AS [t5]

 left outer join 

(select [$Table].[ProductKey] as [ProductKey],
......
from [dbo].[DimProduct] as [$Table]) AS [t6] on 
(
[t5].[ProductKey] = [t6].[ProductKey]
)
)GROUP BY [t6].[Color]

To samo zapytanie zostanie wysłane do źródła jeśli będziemy chcieli połączyć dane zaimportowane z danymi DirectQuery. Power BI wyśle zapytanie łączące fakt z wymiarem do źródła oraz wykona analogiczną operację lokalnie tylko pomiędzy drugą tabelą faktów i “kopią” danych wymiaru. NA tym właśnie polega cały tryb przechowywania Dual, który po prostu zachowuje się raz jak Import, a raz jak Direct Query w zależności od konkretnego kontekstu i tego co wyda się bardziej efektywne.

Wcześniej wspominałem o tym w jakim trybie mogą być tabele i zmiana z jakiego trybu na jaki jest możliwa. W przypadku gdy wszystkie dane pochodzą z jednego źródła (tak jak w powyższym przykładzie) tylko takie konfiguracje są możliwe:

Strona wiele relacji	Strona jeden relacji
Dual	Dual
Direct Query	Direct Query / Dual
Import	Import /Dual

 

W przypadku gdy będziemy chcieli skonfigurować tabele będącą po stronie jeden relacji w niekompatybilny sposób Power BI nam na to nie pozwoli wyświetlając nam stosowny komunikat:

Tak jak już wspomniałem powyższe rozważania związane z trybem Dual mają znaczenie tylko wtedy jeśli będziemy łączyć tabele z tego samego źródła bo tylko wtedy jest możliwość wykonania złączenia po stronie źródła danych. W przypadku łączenia danych z kilku źródeł zawsze dostaniemy relację wiele-wiele i dane będą łączone po stronie Power BI.

Zbliżając się ku końcowi warto wspomnieć, że tabele w trybie Dual podlegają wszystkim ograniczeniom trybu Direct Query ponadto muszą być dodatkowo odświeżane tak jak tabele w trybie Import. Czy powyższe zmiany znajdą zastosowanie w praktyce? Z całą pewnością tak! Sam osobiście znalazłem kilka możliwych scenariuszów, które będą mogły wykorzystać opisywany mechanizm. Warto podkreślić, że jest to dopiero pierwsza wersja będąca na moment pisania artykułu w fazie Preview, aczkolwiek możemy z całą pewnością powiedzieć, iż jest to kolejna rewolucja w świecie Power BI. Mam nadzieję, że z czasem dodane zostanie wsparcie dla Live Connection, którego obecnie brakuje i może część ograniczeń trybu Direct Query zostanie zniesiona? Czas pokaże, a nam nie pozostaje nic jak tylko się cieszyć z tego, że mamy coraz większy wachlarz możliwości.