Параметры конфиденциальности данных в 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 в каждом возможном случае. Эту тему мы рассмотрим в следующей статье.

Предыдущая статья

Комментирование закрыто.

Идет набор на курс “Power BI для интернет-маркетинга”

Набор на новый поток курса по Power BI

Подпишитесь на нашу рассылку, чтобы не пропустить новые статьи!

Подписка на рассылку NeedForData

Бесплатная шпаргалка по визуализации
Плакат Как что визуализировать
Сертификаты студентов курсов NeedForData.ru

© 2018    NeedForData