Параметры конфиденциальности данных
в Power BI/Power Query, часть 1:
Влияние на производительность
Текст представляет собой адаптированный перевод статьи Chris Webb (Крис Уэбб),
оригинал – Data Privacy Settings In Power BI/Power Query, Part 1: Performance Implications.
Крис Вебб (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, часть 1: влияние на производительность

Одной из наиболее запутанных, недостаточно документированных и плохо понимаемых функций Power BI и Power Query (оно же Get & Transform) являются параметры конфиденциальности данных. Автор неоднократно спотыкался на этом, поэтому было решено написать серию статей, демонстрирующих, как это работает и какой эффект производит в различных сценариях на примере запросов языка М.

Прежде чем продолжить, мы настоятельно рекомендуем ознакомиться с официальной документацией Майкрософт по этому вопросу.

Это даст хорошее понимание о разных уровнях конфиденциальности и где они настраиваются в Power BI UI. Эти же параметры доступны в Excel в зависимости от версии на вкладке Power Query или Get & Transform.

В первой части мы рассмотрим, какое влияние на производительность могут оказать разные уровни конфиденциальности. Допустим, у нас есть два источника данных. Первый – книга Excel с одной таблицей, содержащей название дня недели:
Второй – таблица DimDate в базе данных Adventure Works DW на SQL сервере:
Вот код запроса FilterDay, возвращающего название дня недели из таблицы книги Excel:
let
      Source = Excel.Workbook(
            File.Contents("C:\Filter.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
Это код запроса DimDate, возвращающего отфильтрованные данные из таблицы DimDate базы данных Adventure Works DW. Возвращаются только те строки, где ячейки в колонке EnglishDayNameOfWeek равны значению, полученному запросом FilterDay из примера выше.
Let
      Source = Sql.Databases("localhost"),
      DB = Source{[Name="Adventure Works DW"]}[Data],
      dbo_DimDate = DB{[Schema="dbo",Item="DimDate"]}[Data],
      RemovedColumns = Table.SelectColumns(dbo_DimDate,
            {"DateKey", "EnglishDayNameOfWeek"}),
      FilteredRows = Table.SelectRows(RemovedColumns,
            each ([EnglishDayNameOfWeek] = FilterDay))
in
      FilteredRows
При первом запуске второго запроса будут запрошены учётные данные для подключения к серверу SQL и затем (если не были выбраны параметры игнорирования уровня конфиденциальности) будет сообщение о необходимости соответствующей информации:
Нажимаем Continue и устанавливаем уровень конфиденциальности для сервера SQL и корневого диска, на котором находится книга Excel:
Выпадающие списки слева позволяют точнее определить, на что устанавливаются уровни конфиденциальности. В случае файла Excel, права доступа могут быть назначены на все папки по пути к книге и на саму книгу. В случае сервера SQL, уровень конфиденциальности можно назначить на базу данных, а не на сервер.

Назначьте любой из трех уровней (Public, Organizational или Private) для сервера SQL и файла Excel. Запрос будет работать:
Вот здесь все становится запутанным. На данный момент у книги уровень конфиденциальности None, который ставится по умолчанию для вновь созданных источников:
Однако этот параметр None означает, что он унаследован от параметра c:\ drive, установленного ранее, хотя это и не отражается на вкладке Data sources in current file. Будем надеяться, что на этот вопрос рассмотрят в следующем релизе. Текущий интерфейс несколько вводит в заблуждение.

В нашем случае параметры конфиденциальности данных сервера SQL не имеют значения, в отличие от книги Excel. Для определённости установите для сервера SQL уровень Private, а для книги Excel Public:
Если щелкнуть по шагу FilteredRows в запросе, возвращающем таблицу DimDate с сервера SQL, и выбрать пункт View Native Query, то получим текст самого запроса SQL:
Из кода видно, что происходит «Query Folding» запроса и добавляется фильтр по столбцу EnglishDayNameOfWeek:
select [_].[DateKey],
      [_].[EnglishDayNameOfWeek]
from
(
      select [DateKey],
            [EnglishDayNameOfWeek]
      from [dbo].[DimDate] as [$Table]
) as [_]
where [_].[EnglishDayNameOfWeek] = 'Friday'
Query Folding запроса почти всегда повышает его эффективность.

Теперь изменим уровень конфиденциальности следующим образом:
…несмотря на то, что запрос DimDate остаётся рабочим, Query Folding запроса для шага Filtered Rows не происходит. При щелчке ПКМ пункт View Native Query неактивен, а Profiler выдаёт следующий код SQL при обновлении запроса:
select [$Ordered].[DateKey],
      [$Ordered].[EnglishDayNameOfWeek]
from
(
      select [DateKey],
            [EnglishDayNameOfWeek]
      from [dbo].[DimDate] as [$Table]
) as [$Ordered]
order by [$Ordered].[DateKey]
Обратите внимание, в этом запросе нет условия WHERE и сервер SQL возвращает всю таблицу DimDate.

Данные от источника данных с уровнем конфиденциальности Private никогда не пересылаются другому источнику. А это как раз необходимо, чтобы произошел Query Folding запроса. Например, значение из книги Excel – текст Friday – должно быть вставлено в условие WHERE запроса SQL для отсылки на сервер, чтобы фильтрация произошла внутри базы данных. Риск использования Query Folding запросов заключён в том, что администратор базы данных может отслеживать запросы, приходящие на сервер. Просмотр условия WHERE позволит увидеть данные книги Excel. Это не представляет проблемы для названия дня недели, но может стать такой в случае конфиденциальных данных таких как имена и адреса клиентов. Поэтому при уровне книги Private для обеспечения конфиденциальности происходит скачивание всей таблицы DimDate и дальнейшая фильтрация данных идёт внутри Excel. Запрос остаётся работоспособным, но выполняется дольше чем при складывании запроса. Если же уровень конфиденциальности книги Excel установлен Public, то данные могут быть отосланы на сервер и происходит Query Folding запроса.

Подведём итог. В этой статье рассмотрено, как разные уровни конфиденциальности влияют на производительность запроса, определяя, происходит ли Query Folding запроса. Во второй части мы рассмотрим, как различные параметры конфиденциальности определяют, выполнится ли запрос вообще.
Это статья входит в цикл статей посвященных работе formula firewall:
- часть 1
- часть 2
- часть 3
- часть 4
- часть 5