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

Параметры конфиденциальности данных в Power BI/Power Query, часть 3: ошибка Formula.Firewall

В первых двух частях данной серии (Часть 1, Часть 2) статей мы показали, как влияют настройки уровня конфиденциальности данных Power BI/Power Query/Excel Get & Transform на складывание запроса и на возможность его выполнения. В этой статье рассматривается ситуация, когда, независимо от применённых уровней конфиденциальности данных, запрос не выполняется и генерируется печально известная ошибка Formula.Firewall.

Признаемся, что абсолютного понимания заявленной темы нет (и вряд ли кто-то может похвастаться таким, кроме команды разработчиков Power Query). Поэтому в статье даётся объяснение, исходя из наших знаний, а также рассматриваются несколько сценариев возникновения ошибок и показывается, как с ними работать.

Используем два источника данных из предыдущих частей. Книгу Excel, содержащую название дня недели, и таблицу DimDate в базе данных SQL, которую можно отфильтровать по дню недели, взятому в Excel. Оба источника имеют уровень Public. Следующий запрос FilterDay загружает данные из книги и возвращает текстовое значение, содержащее название дня недели:
let
    Source = 
    Excel.Workbook(
        File.Contents("C:\FilterParameter.xlsx"), 
    null, true),
    FilterDay_Table = 
    Source{[Item="FilterDay",Kind="Table"]}[Data],
    ChangedType = 
    Table.TransformColumnTypes(
        FilterDay_Table,
        {{"Parameter", type text}}
    ),
    Output = 
    ChangedType{0}[#"Parameter"]
in
    Output
Давайте взглянем на следующий запрос:
let
    Source = 
    Sql.Database(
        "localhost", 
        "adventure works dw",
        [Query="select DateKey, EnglishDayNameOfWeek 
        from DimDate"]),
    FilteredRows = 
    Table.SelectRows(Source, 
        each ([EnglishDayNameOfWeek] = FilterDay)
    )
in
    FilteredRows
Он фильтрует содержимое таблицы DimDate, возвращая только те строки, в которых столбец EnglishDayNameOfWeek соответствует названию дня, полученному запросом FilterDay. Обратите внимание, что в запросе два шага: Source (который выполняет SQL запрос) и FilteredRows (который выполняет фильтрацию). На выходе получаем:
Как видно на скриншоте, запрос выполняется. На самом деле он будет работать при любых параметрах конфиденциальности. Стоит отметить, что при использовании в коде собственного SQL запроса, Query Folding на последующих шагах не происходит (как в нашем случае).

Теперь взглянем на другую версию запроса:
let
    Source = 
    Table.SelectRows(
        Sql.Database(
            "localhost", 
            "adventure works dw",
            [Query="select DateKey, 
                EnglishDayNameOfWeek 
                from DimDate"]
        ), 
        each ([EnglishDayNameOfWeek] = FilterDay)
    )
in
    Source
Важное отличие состоит в том, что теперь в запросе один шаг, а не два. Запрос и фильтрация происходят на одном шаге. Что более важно, независимо от параметров уровней конфиденциальности, запрос завершается ошибкой. Выдаётся предупреждение, что одношаговый запрос к DimDate не выполнен, т.к. он ссылается на другие запросы или шаги и не может напрямую получить доступ к источнику данных.
Проблема заключается в том, что движок Power Query не позволяет получить доступ к двум разным источникам данных, полученных от разных запросов на одном шаге. Мы думаем, что это происходит из-за проблем с определением допустимо ли соединение или нет, исходя из правил конфиденциальности.

На этом этапе можно подумать, что во избежание ошибки достаточно разбить запрос на несколько шагов, как в примере выше. Однако есть ситуации, когда обойти проблему не так просто. Для примера рассмотрим следующий запрос:
let
    Source = 
    Sql.Database(
        "localhost", 
        "adventure works dw",
        [Query="
         select DateKey, EnglishDayNameOfWeek 
         from DimDate 
         where 
         EnglishDayNameOfWeek='" & FilterDay & "'" 
        ]
    )
in
    Source
В этом примере мы динамически генерируем SQL запрос и передаем название дня недели для фильтра в условие WHERE. В двух предыдущих примерах в запросах не было WHERE и фильтрация осуществлялась внутри Power BI. В данном случае фильтрация происходит в запросе, и для генерации условия WHERE необходимо ссылаться на результат запроса FilterDay в том же шаге. Поэтому запрос возвращает ту же самую ошибку Formula.Firewall, что и в приведённом выше примере.

Так как обойти ошибку? Что ж, следующая версия запроса, который ссылается на FilterDay в отдельном шаге тоже не работает:
let
    DayAsStep = FilterDay,
    Source = 
    Sql.Database(
        "localhost", 
        "adventure works dw",
        [Query="
         select DateKey, EnglishDayNameOfWeek 
         from DimDate 
         where 
         EnglishDayNameOfWeek='" & DayAsStep & "'" 
        ]
    )
in
    Source
К счастью, проблему можно решить, применив функцию Value.NativeQuery(). В одной из предыдущих статей мы показали, как использовать эту функцию для передачи параметров в запросы SQL. Сгенерируем на отдельном шаге запись, содержащую параметры запроса (в коде это ParamRecord):
let
    Source = Sql.Database("localhost", "adventure works dw"),
    ParamRecord = [FilterParameter=FilterDay],
    Query = Value.NativeQuery(
                Source, 
                "select DateKey, EnglishDayNameOfWeek 
        from DimDate 
        where 
        EnglishDayNameOfWeek=@FilterParameter",
                ParamRecord)
in
    Query
Теперь запрос выполняется успешно.

Существует другой способ избежать ошибки. В предыдущих примерах использовались два запроса: в одном мы получали данные из Excel, в другом фильтровали данные сервера SQL. Если соединить два запроса в один, то можно обратиться к данным разных источников на одном шаге. В примере ниже, запрос не ссылается на результаты других запросов. Наименование дня недели получаем на шаге ExcelSource, затем запускаем динамический SQL запрос на шаге SQLSource. Весь запрос завершается успешно:
let
    ExcelSource = 
    Excel.Workbook(
        File.Contents("C:\FilterParameter.xlsx")
    , null, true),
    FilterDay_Table = 
    ExcelSource{[Item="FilterDay",Kind="Table"]}[Data],
    ChangedType = 
    Table.TransformColumnTypes(FilterDay_Table,
        {{"Parameter", type text}}),
    FilterDayStep = 
    ChangedType{0}[#"Parameter"],
    SQLSource = Sql.Database(
    "localhost", 
    "adventure works dw",
    [Query="
        select DateKey, EnglishDayNameOfWeek 
        from DimDate 
        where 
        EnglishDayNameOfWeek='" 
        & FilterDayStep & 
        "'" ])
in
    SQLSource
Очевидно, что движок M не испытывает проблем с доступом к данным разных источников на одном шаге, если эти источники созданы в одном запросе.

Если отключить проверку конфиденциальности в соответствующем диалоге, то мы уберём ошибку Formula.Firewall и получим Query Folding в каждом возможном случае. Эту тему мы рассмотрим в следующей статье.
Другие статьи цикла:
- часть 1
- часть 2
- часть 3
- часть 4
- часть 5