Power BI – wszystko co powinieneś wiedzieć o Incremental Refresh

Jedną z możliwości odświeżania danych w Power BI jest tzw. Incremental Refresh czyli odświeżanie przyrostowe. Funkcjonalność ta obecnie jest dostępna jedynie dla użytkowników Power BI Premium i polega na tym, że w przeciwieństwie do standardowych metod opiera się na tym, że odświeżane będą tylko dane, które albo się zmieniły albo pochodzą z wybranego okresu czasu. O tym jak podejść do tego zagadnienia, skonfigurować go i co tak naprawdę dzieje się pod spodem postaram się opowiedzieć w ramach niniejszego artykułu.

Aby rozpocząć pracę z opisywaną technologią wystarczy załadować w standardowy sposób wybrane tabele do naszego modelu. W celach demonstracyjnych stworzyłem nową bazę danych o nazwie PowerBIIncrementalRefresh, a wewnątrz niej tabelę Transactions opierając się na FactInternetSales z AdventureWorksDW2017. Jedyną zmianą której dokonałem to przesunąłem OrderDate o 6 lat i dodałem kolumnę ModificationDate, która ma symulować datę modyfikacji wiersza. Opisany skrypt tworzący te obiekty wygląda następująco:

CREATE DATABASE [PowerBIIncrementalRefresh]
GO

USE [PowerBIIncrementalRefresh]
GO

--prepare main table 
DROP TABLE IF EXISTS [dbo].[Transactions]
GO

SELECT
	 [ProductKey]
	,[CustomerKey]
	,[SalesOrderNumber]
	,[SalesOrderLineNumber]
	,[OrderQuantity]
	,[UnitPrice]
	,DATEADD(year,6,[OrderDate]) AS [OrderDate]
	,DATEADD(year,6,[OrderDate]) AS [ModificationDate]
INTO [dbo].[Transactions]
FROM [AdventureWorksDW2017].[dbo].[FactInternetSales]
GO

Gdy mój zestaw danych jest już gotowy to mogę pobrać go do Power BI gdzie wygląda on następująco:

W tym momencie jak odświeżymy dane to załadowana zostanie tabela w całości (albo przefiltrowana w taki sposób jak zdefiniowaliśmy to w Power Query). Aby móc zdefiniować ładowanie przyrostowe po pierwsze musimy włączyć tę opcję ponieważ na ten moment jest to funkcjonalność preview:

Po restarcie Power BI Desktop funkcjonalność powinna być dla nas dostępna. Przełączmy się zatem do Edytora Zapytań klikając Edit Queries:

W tym miejscu nasza praca sprowadza się do tego, że musimy stworzyć parametry typu DateTime o ustalonych nazwach, a mianowicie RangeStart oraz RangeEnd. Aby tego dokonać w Power Query klikamy Manage Parameters -> New Parameter:

Istotne jest to aby zdawać sobie sprawę, że parametry nie mogą nazywać się inaczej niż ustalone nazwy. Należy również nadać wartość domyślną (nieważne jaką i tak nie będzie używana przez mechanizm ładowania przyrostowego) :

Mając gotowe parametry musimy nimi przefiltrować kolumnę na której chcemy oprzeć ładowanie przyrostowe – w moim przypadku będzie to OrderDate. Dlatego też z menu wybrałem Date/Time Filters i tam Custom Filter:

Definicja filtra sprawdza się do tego, że wskazujemy iż nasza kolumna ma być większa bądź równa parametrowi Range Start oraz mniejsza niż RangeEnd. Istotne jest to aby w tym miejscu się nie pomylić bo inaczej cały mechanizm będzie działał w błędny sposób.

Po zdefiniowaniu zapytania dla naszego datasetu możemy go zamknąć i wrócić do głównego okna Power BI. W tym miejscu wystarczy kliknąć na wybraną tabelę prawym przyciskiem myszy i z menu kontekstowego wybrać Incremental Refresh:

W oknie konfiguracji mamy kilka opcji do ustawienia. Pierwsza z nich (Store rows where column OrderDate is in the last:) wskazuje za jaki okres interesują nas dane (na obrazku możecie zauważyć, że będą nas interesować dane za ostatnie 12 miesięcy). Druga z kolei wskazuje na okres podlegający zmianom, dla przykładu powiedzmy, że dane mogą się zmieniać na przestrzeni ostatnich trzech miesięcy:

Dodatkowo mamy możliwość wskazania technicznej kolumny, która zawiera datę i czas kiedy dany rekord został wstawiony lub zmodyfikowany (Detect data changes). Dzięki tej opcji Power BI będzie w stanie sprawdzić czy coś się zmieniło w stosunku do ostatniego ładowania i czy istnieje potrzeba przeładowania danych. Jeśli ktoś ma wymóg biznesowy polegający na tym, że trzeba odświeżać tylko zamknięte miesiące (Only refresh complete months) to również istnieje taka możliwość:

W tym miejscu warto wspomnieć, że ładowanie inkrementalne będzie działać tylko i wyłącznie w serwisie Power BI tak więc po zakończeniu konfiguracji należy opublikować raport na workspace ( na moment pisania tego artykułu możliwa jest publikacja jedynie do Workspace opartych o Dedicated Capacity).

Pierwsze uruchomienie odświeżania pozwoli na inicjalne ładowanie danych czyli wszystkie interesujące nas dane zostaną wrzucone do datasetu. Abyśmy mogli śledzić ten proces stworzyłem sesję Extended Events, która pozwoli przechwycić zapytania wysłane przez Power BI do mojej bazy danych, którą stworzyliśmy na początku artykułu. Sesję Extended Events możecie utworzyć za pomocą niniejszego skryptu (pominę tłumaczenie tej funkcjonalności gdyż nie to jest przedmiotem niniejszego artykułu, jednakże warto zwrócić uwagę na to, że identyfikacja zapytań polega na tym, że podglądamy użytkownika test, który jest używany do połączenia do bazy SQL Server):

IF EXISTS
	( 
		SELECT * 
		FROM sys.server_event_sessions
		WHERE [name]='IncrementalRefresh'
	)
	BEGIN
		DROP EVENT SESSION [IncrementalRefresh] ON SERVER 
	END
GO

CREATE EVENT SESSION [IncrementalRefresh] ON SERVER 
ADD EVENT sqlserver.sql_batch_starting(
    ACTION(
			 package0.event_sequence
			,sqlserver.session_id
			,sqlserver.sql_text
			,sqlserver.username
			)
    WHERE (
			[package0].[equal_boolean]([sqlserver].[is_system],(0)) 
			AND [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[username],N'test') 
			AND [sqlserver].[database_name]=N'PowerBIIncrementalRefresh'))
GO

ALTER EVENT SESSION [IncrementalRefresh] ON SERVER  
STATE = start;  
GO  

Wysyłane dane będziemy podglądać na żywo, aby to zrobić wystarczy kliknąć na stworzoną przez nas sesję i wybrać z menu kontekstowego Watch Live Data:

Dataset odświeżyłem na żądanie i dzięki temu możemy dostrzec, że do bazy danych trafił cały szereg różnych zapytań:

Jest ich dosyć dużo (a może być jeszcze więcej w zależności od tego jaki okres odświeżania wybraliśmy i jak szeroki jest zakres podlegający zmianom) ale ogólnie można je podzielić na dwa rodzaje.

Pierwsze z nich wygląda następująco:

execute sp_executesql N'select [_].[ProductKey],
    [_].[CustomerKey],
    [_].[SalesOrderNumber],
    [_].[SalesOrderLineNumber],
    [_].[OrderQuantity],
    [_].[UnitPrice],
    [_].[OrderDate],
    [_].[ModificationDate]
from [dbo].[Transactions] as [_]
where [_].[OrderDate] >= convert(datetime2, ''2019-11-01 00:00:00'') and [_].[OrderDate] < convert(datetime2, ''2019-12-01 00:00:00'')'

Jest to zwykłe zapytanie pobierające dane oparte o SELECT. Warto zwrócić uwagę na sekcję filtrującą czyli WHERE gdzie możemy zobaczyć, że filtracja następuje zgodnie z oczekiwaniami po kolumnie na której opiera się nasze ładowanie inkrementalne. Pozostałe zapytania ładujące wyglądają dokładnie tak samo z tą różnicą, że pobierają dane z innego zakresu czasu.

Drugi rodzaj zapytań wysłanych przez Power BI przedstawia się nieco inaczej i będziemy w stanie go dostrzec tylko i wyłącznie jeśli przy konfiguracji wskazaliśmy kolumnę techniczną zawierającą datę modyfikacji/wstawienia danego wiersza:

select max([rows].[ModificationDate]) as [ModificationDate]
from 
(
    select [_].[ModificationDate]
    from [dbo].[Transactions] as [_]
    where [_].[OrderDate] >= convert(datetime2, '2020-01-01 00:00:00') and [_].[OrderDate] < convert(datetime2, '2020-02-01 00:00:00')
) as [rows]

W tym przypadku widzimy, że pobierana jest maksymalna data modyfikacji wierszy w danym okresie czasu.

Powyższe zapytania powtarzają się wielokrotnie dla różnych okresów czasu, a to wskazuje jednoznacznie, że mamy tutaj do czynienia z partycjonowaniem datasetu. Jest to mniej więcej to samo partycjonowanie, które znamy z Analysis Services przy czym dodatkowo niejako z pudełka dostajemy dodatkowo sprawdzenie czy określona partycja ma zostać partycjonowana czy też nie. Pierwsze ładowanie może potrwać nieco dłużej bo wypełnione zostaną partycje dla całego okresu podlegającego raportowaniu.

Po zakończeniu ładowania inicjalnego spróbujmy odświeżyć dane jeszcze raz i spójrzmy jakie zapytania zostały wysłane do serwera SQL:

Jak możecie zauważyć na powyższym zrzucie ekranowym wysłane zostały tylko trzy zapytania sprawdzające ostatnią datę modyfikacji danych w określonych okresach. Dlaczego mamy tylko trzy zapytania? Odpowiedź znajdziemy w naszej konfiguracji gdzie zaznaczyliśmy, że chcemy przeładowywać dane z ostatnich trzech miesięcy. Dlatego też wysłane zostały trzy zapytania aby sprawdzić czy w ogóle coś się zmieniło od ostatniego ładowania.  Poszczególne zapytania wyglądają następująco:

--zapytanie 1
select max([rows].[ModificationDate]) as [ModificationDate]
from 
(
    select [_].[ModificationDate]
    from [dbo].[Transactions] as [_]
    where [_].[OrderDate] >= convert(datetime2, '2020-02-01 00:00:00') and [_].[OrderDate] < convert(datetime2, '2020-03-01 00:00:00')
) as [rows] 

--zapytanie 2
select max([rows].[ModificationDate]) as [ModificationDate]
from 
(
    select [_].[ModificationDate]
    from [dbo].[Transactions] as [_]
    where [_].[OrderDate] >= convert(datetime2, '2019-12-01 00:00:00') and [_].[OrderDate] < convert(datetime2, '2020-01-01 00:00:00')
) as [rows] 

--zapytanie 3
select max([rows].[ModificationDate]) as [ModificationDate]
from 
(
    select [_].[ModificationDate]
    from [dbo].[Transactions] as [_]
    where [_].[OrderDate] >= convert(datetime2, '2020-01-01 00:00:00') and [_].[OrderDate] < convert(datetime2, '2020-02-01 00:00:00')
) as [rows]

Zgodnie z oczekiwaniami możemy zauważyć, że każde zapytanie odnosi się do jednego z trzech ostatnich miesięcy. Po pobraniu ostatnich dat modyfikacji serwis porówna czy są one nowsze niż te z ostatniego ładowania i dzięki temu “wie” czy coś się zmieniło czy też nie. W naszym przypadku testowym nic się nie zmieniło więc nie dostrzegliśmy żadnego zapytania ładującego dane. Jednakże dobrze byłoby taki scenariusz zilustrować dlatego też uruchomimy sobie zapytanie aktualizujące datę modyfikacji dla danych za styczeń:

UPDATE [dbo].[Transactions]
SET ModificationDate=GETDATE()
WHERE OrderDate>='20200101' AND OrderDate <'20200201'

Następnie uruchomimy ładowanie raz jeszcze i wtedy też dostrzeżemy, że kolejny raz odświeżone zostały trzy ostatnie miesiące. Oprócz tego Power BI wykrył, że dane w jednej z partycji zostały zmienione i wysłał zapytanie odświeżające dane dla tego konkretnego zakresu danych:

Zapytanie pobiera dane za styczeń czyli dokładnie te, które zmodyfikowaliśmy:

execute sp_executesql N'select [_].[ProductKey],
    [_].[CustomerKey],
    [_].[SalesOrderNumber],
    [_].[SalesOrderLineNumber],
    [_].[OrderQuantity],
    [_].[UnitPrice],
    [_].[OrderDate],
    [_].[ModificationDate]
from [dbo].[Transactions] as [_]
where [_].[OrderDate] >= convert(datetime2, ''2020-01-01 00:00:00'') and [_].[OrderDate] < convert(datetime2, ''2020-02-01 00:00:00'')'

Wszystko działa sprawnie i przejrzyście szczególnie dla kogoś kto zajmował się w przeszłości podobnymi kwestiami w stosunku do modeli tabelarycznych. Cały czas używam słów odnoszących się do partycjonowania jednakże oprócz powyższych zapytań i intuicji nie mamy pewności co do tego, że dataset rzeczywiście jest partycjonowany. Możemy to jednak sprawdzić w bardzo łatwy sposób za pomocą XMLA Endpoint, które dosyć niedawno opisywałem w ramach artykułu, który znajdziecie tutaj. Nie będę się szczególnie rozwodził na ten temat szczególnie, że zrobiłem to w tamtym artykule jednakże sprawdźmy partycje łącząc się do datasetu z poziomu Management Studio i wybierając z menu kontekstowego określonej tabeli opcję Partitions:

Naszym oczom ukaże się lista partycji wraz z podstawowymi informacjami na ich temat:

Na ten moment możemy jedynie je odczytać jednakże zostało ogłoszone to, że XMLA Endpointy będą również do zapisu. Zobaczymy jaki poziom kontroli nad partycjami otrzymamy, jeśli będzie to swoboda taka jak w Analysis Services to z całą pewnością znacząco to poszerzy wachlarz dostępny opcji i ułatwi planowanie wdrożenia. Z tego miejsca warto również wspomnieć o tym, że ogłoszone zostały również duże datasety tzn. będziemy mogli tworzyć modele, które będą większe niż 10GB i będą mogły osiągnąć nawet do 400GB co jest już naprawdę bardzo dużym modelem.

Wszystko o czym mówię jest oczywiście związane z Premium czyli Dedicated Capacity. Jeśli zatem jest ktoś kto chciałby przetestować “Large Models” to już w tym momencie są one dostępne. Wystarczy zmienić właściwość Storage Mode dla danego datasetu z domyślnego ABF (Analysis Services Backup) na Premium Files. Zrobimy to wykorzystując Powershell i poniższy skrypt:

$WorkspaceId = (Get-PowerBIWorkspace |select Id,Name |where name -eq "PowerPlatformSaturday" | select id).id

$datasetId = (Get-PowerBIDataset -WorkspaceId $WorkspaceId |where name -Like "*Incremental*" |select id).id

(Get-PowerBIDataset -WorkspaceId $WorkspaceId -Id $datasetId -Include actualStorage).ActualStorage

Set-PowerBIDataset -Id $datasetId -TargetStorageMode PremiumFiles 

Widać jednakże, że pewien trend gdzie to Power BI przejmie całą gamę funkcjonalności związanych z modelami analitycznymi od Analysis Services i wszystkie opcje będziemy mieli wewnątrz jednego narzędzia bez potrzeby odwoływania się do innych usług wewnątrz chmury. Brzmi to naprawdę ciekawie – zobaczymy jak to wszystko będzie sprawdzać się w praktyce, a to okaże się już niebawem bo w pierwszej części tego roku.

Jeśli chodzi o ładowanie przyrostowe to oczywiście kilka kwestii pozostaje otwartych jak chociażby to w jaki sposób obsługiwać rekordy, które zostały usunięte lub jak sobie poradzić gdy nie mamy wyraźnie określonego okresu podlegającego zmianom, jak całość działa z mechanizmem Query folding – na te oraz kilka innych tematów postaram się odpowiedzieć w ramach osobnych artykułów.

Pozdrawiam!