Функция Web.Contents() и её параметры Caching и ExcludedFromCacheKey в Power BI и Power Query

Текст представляет собой адаптированный перевод статьи Chris Webb (Крис Уэбб),
оригинал — Web.Contents(), Caching And The ExcludedFromCacheKey Option In Power BI And Power Query.

Крис Вебб (Chris Webb) — независимый эксперт, консультант по технологиям Analysis Services, MDX, Power Pivot, DAX, Power Query и Power BI. Его блог — это кладезь информации на тему перечисленных технологий. Вот уже более 10 лет он пишет про BI-решения от Microsoft. Количество его статей перевалило за 1000! Также Крис выступает на большом количестве различных конференций вроде SQLBits, PASS Summit, PASS BA Conference, SQL Saturdays и участвует в различных сообществах.
Крис любезно разрешил нам переводить его статьи на русский язык. И это одна из них.

Функция Web.Contents() и её параметры Caching и ExcludedFromCacheKey в Power BI и Power Query

При использовании функции Web.Contents() для вызова веб-сервиса из Power Query или Power BI нет необходимости получать HTTP запрос при каждом вызове функции. Происходит кэширование, и нет нужды вызывать запрос снова и снова для получения одних и тех же данных. В этой статье мы предоставим результаты тестов, показывающие как работает кэширование в Web.Contents() и какие факторы влияют на это.

Для тестов мы создали веб-сервис в Microsoft Flow, подобно тому, что мы описывали ранее. Он принимает HTTP POST запрос и вызывает процедуру сохранения в базе данных Azure SQL. Процедура сохранения обновляет таблицу базы, что позволяет, в свою очередь, подсчитать количество вызовов сервиса. Если процедура прошла удачно, он вернёт 0.

Этот сервис можно затем вызвать из Power Query или Power BI при помощи функции Web.Contents(). Примерно вот так (поскольку URL слишком длинный, он сохранён в переменной WebServiceURL):

Let
      Source = Web.Contents(
      WebServiceURL,
      [Content=Text.ToBinary("Hello")]
      ),
      #"Imported JSON" = Json.Document(Source,1252)
   in
      #"Imported JSON"

При запуске Power Query результат запроса выводится в таблицу Excel:

Основная важная вещь, которую стоит отметить, — это то, что в последних версиях Power Query (у нас Excel 2016 build 7571.2109) и Power BI (релиз 2.41.4581.361 – November 2016) при обновлении запроса веб-сервис вызывается один раз. Это кажется очевидным, но в предыдущих версиях вызовы происходили несколько раз…

Теперь посмотрим пример, где запрос вызывает веб-сервис несколько раз. Ниже приведен запрос, преобразованный в функцию fnCallWebService:

() =>
   let
      Source = Web.Contents(
      WebServiceURL,
      [Content=Text.ToBinary("Hello")]),
      #"Imported JSON" = Json.Document(Source,1252)
   in
      #"Imported JSON"

Вот запрос, который вызывает нашу функцию для каждой строки таблицы:

let
      Source = Excel.CurrentWorkbook(){[Name="MyTable"]}[Content],
      #"Changed Type" = Table.TransformColumnTypes(
      Source,
      {{"Row", Int64.Type}}),
      #"Invoked Custom Function" = Table.AddColumn(
      #"Changed Type",
      "fnCallWebService",
      each fnCallWebService())
   in
      #"Invoked Custom Function"

В приведённом выше запросе мы использовали кнопку Invoke Custom Function для вызова нашей функции на каждой строке таблицы источника, результат функции записали в новый столбец. Результат:

Хотя функция вызывается четыре раза, один раз для каждой строки таблицы, веб-сервис вызывается один раз. В нашем случае Power BI/Power Query определяет, что четыре раза происходит одинаковый запрос к веб-сервису. Поэтому обращение идёт один раз, а в трёх остальных случаях используется результат кэширования.

Один из способов избежать кэширования — это добавление заголовка HTTP к запросу веб-сервису и передача различных значений в этом заголовке при каждом вызове. Вот другая версия нашей функции fnCallWebServiceWithHeaders, она принимает число в качестве параметра и передаёт его на веб-сервис в заголовке MyHeader:

(RowNum as number) => let
      Source = Web.Contents(
      WebServiceURL,
      [Content=Text.ToBinary("Hello"),
      Headers=[MyHeader=Text.From(RowNum)]]),
      #"Imported JSON" = Json.Document(Source,1252)
   in
      #"Imported JSON"

Теперь при каждом вызове этой функции в неё передаётся значение столбца [Row]:

 let
      Source = Excel.CurrentWorkbook(){[Name="MyTable"]}[Content],
      #"Changed Type" = Table.TransformColumnTypes(
      Source,
      {{"Row", Int64.Type}}),
      #"Invoked Custom Function" = Table.AddColumn(
      #"Changed Type",
      "fnCallWebServiceWithHeaders",
      each fnCallWebServiceWithHeaders([Row]))
   in
      #"Invoked Custom Function"

…веб-сервис вызывается четыре раза. Наличие разных значений в заголовке MyHeader достаточно, чтобы остановить кэширование.

Однако можно заставить Power BI/Power Query игнорировать заголовки в отдельных случаях, если для них необходимо кэширование. Для этого применим параметр ExcludedFromCacheKey функции Web.Contents(). Ещё один вариант нашей функции, названной fnCallWebServiceWithHeadersExlCache:

 (RowNum as number) =>
   let
      Source = Web.Contents(
      WebServiceURL,
      [Content=Text.ToBinary("Hello"),
      Headers=[MyHeader=Text.From(RowNum)],
      ExcludedFromCacheKey={"MyHeader"}]),
      #"Imported JSON" = Json.Document(Source,1252)
   in
      #"Imported JSON"

Параметр ExcludedFromCacheKey принимает список текстовых значений, представляющих имена заголовков, которые должны игнорироваться при кэшировании запроса. В нашем случае присутствует только один заголовок – MyHeader. И если мы составим такой запрос:

 let
      Source = Excel.CurrentWorkbook(){[Name="MyTable"]}[Content],
      #"Changed Type" = Table.TransformColumnTypes(
      Source,
      {{"Row", Int64.Type}}),
      #"Invoked Custom Function" = Table.AddColumn(
      #"Changed Type",
      "fnCallWebServiceWithHeaders",
      each fnCallWebServiceWithHeadersExlCache([Row]))
   in
      #"Invoked Custom Function"

…то, несмотря на вызов функции для каждой строки и наличия разных значений в заголовке MyHeader, обращение к сервису происходит один раз, а три раза используется кэш.

Подведём итог. Если вы несколько раз вызываете в запросе веб-сервис, особенно с использованием параметра Headers функции Web.Contents(), важно понимать, как это работает. Потому что кэширование оказывает сильное влияние на быстродействие запроса.