Power BI – czy Assume referential integrity w Direct Query ma znaczenie?

Power BI posiada kilka trybów działania takich jak Import, Direct Query, Live Connection czy też Dual. Każdy z nich charakteryzuje się innymi cechami czy też ograniczeniami i przy wyborze należy przeanalizować wszystkie za, przeciw i wybrać ten tryb, który najlepiej sprawdzi się w naszym przypadku. Dziś powiemy sobie trochę o trybie Direct Query czyli sytuacji gdzie Power BI na bieżąco generuje i wysyła zapytania do źródłowej bazy danych, a całość obliczeń odbywa się właśnie po stronie bazy. Samego trybu nie będziemy jakoś szczególnie opisywać, a skupimy się na jednej właściwości o nazwie Assume referential integrity, która bardzo często jest pomijana.  Sprawdzimy czy ma ona jakiś wpływ na wydajność naszego rozwiązania i na działanie całego modelu.

Do testów posłuży nam oczywiście AdventureWorksDW gdzie wszystkie tabele zostały podłączone w trybie Direct Query:

Sam model przedstawia się następująco:

 

Opcja o której mowa w niniejszym artykule jest właściwością każdej relacji i znajdziemy ją klikając dwukrotnie właśnie w wybraną relację (będzie ona możliwa do ustawienia tylko w trybie Direct Query):

Za co ona tak naprawdę odpowiada? Przede wszystkim za to, że po jej zaznaczeniu Power BI założy iż tabele które połączone są relacją są konsystentne tj. gdy np. jeśli w FactInternetSales mamy ProductID=301 to mamy pewność, że w DimProduct istnieje produkt o identyfikatorze 301. Po co taka wiedza w Power BI? A no po to, że mając pewność co do integralności może on wysłać zapytanie wykorzystujące INNER JOIN zamiast OUTER JOIN.

Aby sprawdzić jak to zachowanie stworzyłem prostą tabelę w Power BI:

Po odświeżeniu raportu za pomocą Profilera zarejestrowałem następujące zapytanie (usunąłem zbędną sekcję SELECT aby zapytanie nie zajęło zbyt wiele miejsca):

SELECT 
TOP (1000001) [t7].[EnglishProductCategoryName],SUM([t0].[SalesAmount])
 AS [a0]
FROM 
((((
SELECT ...
 AS [SalesAmount]
FROM 
((select ...
from [dbo].[FactInternetSales] as [$Table])
)AS [t0]
)AS [t0]
 left outer join 
(select ...
from [dbo].[DimProduct] as [$Table]) AS [t4] on 
(
[t0].[ProductKey] = [t4].[ProductKey]
))
 left outer join 
(select ...
from [dbo].[DimProductSubcategory] as [$Table]) AS [t8] on 
(
[t4].[ProductSubcategoryKey] = [t8].[ProductSubcategoryKey]
))
left outer join 
(select ...
from [dbo].[DimProductCategory] as [$Table]) AS [t7] on 
(
[t8].[ProductCategoryKey] = [t7].[ProductCategoryKey]
))
GROUP BY [t7].[EnglishProductCategoryName]

To na co warto zwrócić uwagę to fakt, że pomiędzy poszczególnymi podzapytaniami występuje LEFT JOIN. W momencie gdy chciałem zaznaczyć wspomnianą opcję otrzymałem wszystko mówiący błąd:

Dzieje się tak dlatego, że opcja ta może być włączona tylko w przypadku gdy spełnione zostaną następujące czynniki:

• kolumna po której następuje złączenie po stronie jeden musi mieć wartość, która występuje w kolumnie po stronie wiele,
• kolumna po której następuje złączenie po stronie wiele nie może być pusta (nie może mieć wartości NULL).

Każda hurtownia danych powinna mieć zapewnioną integralność tego typu poprzez wykorzystanie tzw. Unknown members (wiersza o identyfikatorze zwyczajowo numerowanego jako 0 lub -1) jednakże w AdventureWorksDW te zasady są naruszane więc przygotowałem prosty skrypt, który naprawią zaistniałą sytuację.

SET IDENTITY_INSERT DimProductCategory ON

INSERT INTO DimProductCategory
([ProductCategoryKey], [ProductCategoryAlternateKey], [EnglishProductCategoryName], [SpanishProductCategoryName], [FrenchProductCategoryName])
VALUES
(
0,0,'#','#','#'
)
SET IDENTITY_INSERT DimProductCategory OFF


SET IDENTITY_INSERT DimProductSubcategory ON

INSERT INTO DimProductSubcategory
([ProductSubcategoryKey], [ProductSubcategoryAlternateKey], [EnglishProductSubcategoryName], [SpanishProductSubcategoryName], [FrenchProductSubcategoryName], [ProductCategoryKey]
)
VALUES
(
0,0,'#','#','#',0
)
SET IDENTITY_INSERT DimProductSubCategory OFF

UPDATE DimProduct
SET ProductSubcategoryKey=0
WHERE ProductSubcategoryKey IS NULL

Po porównaniu planu wykonania wykonanego bez zaznaczonej opcji (na górze) i z włączoną opcją (na dole) widzimy, że zapytanie z dolne jest nieznacznie mniej kosztowne:

Przy 10 milionach wierszy w tabeli faktów różnica w czasie wykonania była pomijalnie miła jednakże przy dużych modelach może być jak najbardziej odczuwalna. Czy oprócz innego sposobu wykonania omawiana właściwość może mieć wpływ na jakieś inne aspekty pracy? Oczywiście, że tak! Aby to zobrazować upewnijmy się, że opcja “Assume” jest włączona, a następnie “zepsujmy” nieco nasze dane – wykonamy zapytanie na źródle, które odłączy produkty od ich kategorii:

UPDATE DimProduct
SET ProductSubcategoryKey=NULL
WHERE ProductKey>400

Oczywiście uda nam się to wszystko wykonać ponieważ opcja była już włączona a walidacja wykonywana jest tylko w przypadku zmiany jej wartości (czyli podczas włączenia lub wyłączenia). Po naruszeniu integralności nie otrzymamy żadnego błędu, zamiast tego na raporcie zobaczymy coś takiego:

Karta widoczna w górnej części pokazuje podsumowanie wartości sprzedaży, a tabela poniżej sprzedaż w podziale na kategorie. Widzicie coś niepokojącego? Jeśli nie to zaraz się to wyjaśni.

Po wyłączeniu opcji raport prezentuje się trochę inaczej:

Myślę, że teraz różnicę widać bardzo wyraźnie. Jeśli opcja jest włączona, a w danych nie ma integralności to Power BI wykonując Inner Join eliminuje nie łączące się dane! Tak więc tabela na dolnym zrzucie pokazuje nieco mylącą sumę wartości – użytkownicy widząc 21 milionów w tabeli na podsumowaniu gdzie nie została żadna wartość na filtrach/slicerach może wysnuć całkowicie błędne wnioski!

Wnioskiem płynącym z tego artykułu powinno być to aby w trybie Direct Query zwrócić uwagę na to jak działa omawiana właściwość i unikać jak ognia braku konsystencji w danych gdy ta opcja jest włączona bo nie ma nic gorszego niż decyzja podjęta na podstawie błędnego raportu. Pozdrawiam.