W poprzednim artykule (link) pobraliśmy sobie zestawienie filmów komediowych ze strony IMDB przy pomocy konektora Web, funkcjonalności Add table from examples oraz funkcji języka M. Wszystko działało zgodnie z oczekiwaniami jednakże problemy pojawiły się w momencie gdy wrzuciliśmy dane na portal gdzie przy próbie odświeżenia otrzymaliśmy niezbyt miło wyglądający komunikat błędu “Unable to refresh the model because it references an unsupported data source“:
Na szczęście istnieje rozwiązanie tego problemu, które chciałbym przedstawić w ramach niniejszego artykułu do lektury którego serdecznie zapraszam.
Odświeżanie danych w Power BI pochodzących ze stron www od bardzo dawna było problematyczne. Jeśli podejrzymy kod zapytania w języku M to zauważymy, że za pobranie danych odpowiada funkcja Web.BrowserContents lub Web.Contents i to właśnie jej odpowiednia parametryzacja powoduje, że serwis Power BI nie jest w stanie odświeżyć danych. Dzieje się tak dlatego, że serwis próbuje walidować URL podany jako parametr funkcji, a jeśli jest on dynamicznie budowany to niestety tego zrobić nie może. W celu sprawdzenia tej teorii możemy pobrać dane używając poniższego kodu, który jest zwykłym zapytaniem w języku M, który odnosi się do sztywnego URL bez żadnego parametru:
let
Source = Web.BrowserContents("https://www.imdb.com/list/ls072723591/?sort=list_order,asc&st_dt=&mode=detail&page=1"),
#"Extracted Table From Html" = Html.Table(Source, {{"Rank", ".lister-item-index"}, {"Title", ".lister-item-header > A"}, {"Year", ".lister-item-year"}, {"Rate", ".small.ipl-rating-star"}, {"Duration", ".runtime"}, {"Type", ".genre"}}, [RowSelector=".lister-item"]),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Rank", Int64.Type}, {"Rate", type number}}),
#"Inserted Kept Characters" = Table.AddColumn(#"Changed Type", "Kept Characters", each Text.Select([Year], {"0".."9"}), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Kept Characters",{"Year"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Kept Characters", "Year"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Rank", "Title", "Rate", "Year", "Duration", "Type"}),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Reordered Columns", "Duration [min]", each Text.BeforeDelimiter([Duration], " "), type text),
#"Removed Columns1" = Table.RemoveColumns(#"Inserted Text Before Delimiter",{"Duration"}),
#"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns1",{"Rank", "Title", "Rate", "Year", "Duration [min]", "Type"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Duration [min]", Int64.Type}})
in
#"Changed Type1"
Po publikacji pliku pbix z powyższym zapytaniem i po próbie odświeżenia na żądanie zobaczymy, że zakończyło się ono sukcesem. Stało się tak ze względu na to, że jak już wspomniałem URL podany jako parametr przeszedł walidację. Musimy zatem użyć takiej konstrukcji aby pozwoliła serwisowi zwalidować URL, a jednocześnie pozwoli nam go parametryzować. Coś takiego możemy uzyskać przy pomocy funkcji Web.Contents (niestety nie wspiera tego siostrzana Web.BrowserContents), która posiada opcjonalny parametr pozwalający na niejako złożenie odpowiedniego adresu gdzie serwis waliduje jedynie adres podany jako główny. W dokumentacji znajdziemy składnię tej właśnie funkcji i wygląda następująco:
Web.Contents(url as text, optional options as nullable record) as binary
Domyślnie przekazujemy oczywiście URL w postaci tekstu, jednakże pozostałe parametry widoczne wyżej jako “optional options“. Opcjonalnych parametrów jest całkiem sporo i nie będę omawiał ich wszystkich jednak chciałbym wspomnieć o dwóch, które będą nas interesować tj.:
- RelativePath – czyli dodatkowa część URL, która zostanie dołączona do adresu przekazanego jako pierwszy parametr funkcji Web.Contents np. jeśli jako pierwszy parametr przekażemy “https://www.seequality.net/”, a jako RelativePath “tworzenie-projektu-bazodanowego-sql-server-w-visual-studio/” to otrzymamy pełne odwołanie “https://pl.seequality.net/tworzenie-projektu-bazodanowego-sql-server-w-visual-studio/”
- Query – pozwala na dodanie do URL parametrów. Dodatkowo dzięki temu przełącznikowi nie musimy stosować żadnych znaków ucieczki np. cudzysłowów i tym podobnych.
W praktyce wywołanie Web.Contents będzie wyglądało następująco:
Web.Contents("https://www.imdb.com/",
[
RelativePath="list/ls072723591/?sort=list_order,asc&st_dt=&mode=detail",
Query=[page=pageNumber]
]
Jako URL podaliśmy stronę główną serwisu imdb, jako RelativePath ścieżkę do konkretnego podzbioru, a jako Query podaliśmy wymagany przez IMDB parametr page do którego przekazaliśmy parametr stworzonej przez nas funkcji o nazwie pageNumber. Cały kod funkcji wygląda następująco:
let
Source = (pageNumber) => let
Source = Web.Contents("https://www.imdb.com/",
[
RelativePath="list/ls072723591/?sort=list_order,asc&st_dt=&mode=detail",
Query=[page=pageNumber]
]),
#"Extracted Table From Html" = Html.Table(Source, {{"Rank", ".lister-item-index"}, {"Title", ".lister-item-header > A"}, {"Year", ".lister-item-year"}, {"Rate", ".small.ipl-rating-star"}, {"Duration", ".runtime"}, {"Type", ".genre"}}, [RowSelector=".lister-item"]),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Rank", Int64.Type}, {"Rate", type number}}),
#"Inserted Kept Characters" = Table.AddColumn(#"Changed Type", "Kept Characters", each Text.Select([Year], {"0".."9"}), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Kept Characters",{"Year"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Kept Characters", "Year"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Rank", "Title", "Rate", "Year", "Duration", "Type"}),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Reordered Columns", "Duration [min]", each Text.BeforeDelimiter([Duration], " "), type text),
#"Removed Columns1" = Table.RemoveColumns(#"Inserted Text Before Delimiter",{"Duration"}),
#"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns1",{"Rank", "Title", "Rate", "Year", "Duration [min]", "Type"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Duration [min]", Int64.Type}})
in
#"Changed Type1"
in
Source
Po tej właśnie modyfikacji warto przeładować dane w Power BI Desktop żeby sprawdzić czy wszystko przebiega bez problemu. Jeśli wszystko się powiodło należy opublikować raport na serwisie. Warto również zajrzeć we właściwości zestawu danych tak jak zostało to przedstawione na poniższym zrzucie ekranowym:
W sekcji Data source credentials możemy kliknąć Edit Credentials aby podejrzeć jakie mamy zdefiniowane ustawienia uwierzytelnienia w stosunku do źródła danych Web:
Dla strony www takiej jak IMDB wybieramy uwierzytelnienie anonimowe, poziom prywatności Public ze względu na fakt, iż jest to publiczne źródłowo danych. Dodatkowo mamy opcję Skip test connection, którą w tym wypadku warto zaznaczyć aby połączenie nie było testowane:
Niektórzy mogą zapytać czy to ustawienie nie rozwiązuje problemu i czy nie powinniśmy tego zrobić na samym początku. Odpowiedź brzmi nie gdyż jeśli główny adres URL nie może być zwalidowany to nie mamy w ogóle dostępu do ustawień źródła danych dla datasetu. Jeśli wszystko mamy skonfigurowane poprawnie to odświeżenie danych powinno odbyć się bez większych problemów:
To by było na tyle jeśli chodzi o ten konkretny problem związany z odświeżaniem danych www z poziomu Power BI. Z konektorem Web i funkcją Web.Content związane jest mnóstwo możliwości i problemów. Być może w najbliższym czasie uda mi się coś napisać na ten temat, na ten moment zainteresowanych odsyłam do bloga Chrisa Webba, który napisał mnóstwo artykułów poświęconych tej funkcjonalności. Pozdrawiam!
- Avoiding Issues: Monitoring Query Pushdowns in Databricks Federated Queries - October 27, 2024
- Microsoft Fabric: Using Workspace Identity for Authentication - September 25, 2024
- Executing SQL queries from Azure DevOps using Service Connection credentials - August 28, 2024
Last comments