Поделиться через

Поддержка собственных запросов в пользовательских соединителях Power Query

  • Статья

Примечание.

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

Дополнительные сведения о пользовательских соединителях Power Query см. в статье "Обзор пакета SDK Для Power Query".

В Power Query вы можете выполнять пользовательские собственные запросы к источнику данных, чтобы получить нужные данные. Вы также можете включить возможность сохранения свертывания запросов во время этого процесса и последующих процессов преобразования, выполненных внутри Power Query.

Цель этой статьи — продемонстрировать, как реализовать такую возможность для пользовательского соединителя.

Необходимые компоненты

В этой статье используется в качестве отправной точки пример , использующий драйвер ODBC SQL для своего источника данных. Реализация возможностей собственного запроса в настоящее время поддерживается только для соединителей ODBC, которые соответствуют стандарту SQL-92.

В примере соединителя используется драйвер SQL Server Native Client 11.0 . Убедитесь, что этот драйвер установлен вместе с этим руководством.

Вы также можете просмотреть завершенную версию примера соединителя из папки Finish в репозитории GitHub.

Изменение возможностей SQLCapabilities соединителя

SqlCapabilities В записи примера соединителя можно найти поле записи с именем Sql92Translation и значением PassThrough для него. Это новое поле необходимо для прохождения собственного запроса с помощью Power Query без каких-либо проверок.

SqlCapabilities = Diagnostics.LogValue("SqlCapabilities_Options", defaultConfig[SqlCapabilities] & [
    // Place custom overrides here
    // The values below are required for the SQL Native Client ODBC driver, but might
    // not be required for your data source.
        SupportsTop = false,
        SupportsDerivedTable = true,
        Sql92Conformance = 8 /* SQL_SC_SQL92_FULL */,
        GroupByCapabilities = 4 /* SQL_GB_NO_RELATION */,
        FractionalSecondsScale = 3,
        Sql92Translation = "PassThrough"
]),

Убедитесь, что это поле отображается в соединителе перед переходом вперед. Если нет, вы столкнетесь с предупреждениями и ошибками позже, когда дело доходит до использования возможности, которая не поддерживается, так как она не объявлена соединителем.

Создайте файл соединителя (как MEZ или PQX) и загрузите его в Power BI Desktop для ручного тестирования и определите целевой объект для собственного запроса.

Тестирование возможностей собственного запроса соединителя вручную

Примечание.

В этой статье мы будем использовать пример базы данных AdventureWorks2019. Но вы можете следовать вместе с любой базой данных SQL Server по вашему выбору и внести необходимые изменения, когда речь идет о специфике выбранной базы данных.

Способ реализации поддержки собственных запросов в этой статье заключается в том, что пользователю будет предложено ввести три значения:

  • Имя сервера
  • Имя базы данных
  • Собственный запрос на уровне базы данных

Теперь в Power BI Desktop перейдите к интерфейсу получения данных и найдите соединитель с именем SqlODBC Sample.

Screenshot of the connector found inside the get data experience of Power BI Desktop.

В диалоговом окне соединителя введите параметры для сервера и имени базы данных. Затем выберите OK.

Screenshot of connector dialog with server and database as parameters.

Откроется новое окно навигатора. В навигаторе можно просмотреть собственное поведение навигации из драйвера SQL, отображающего иерархическое представление сервера и баз данных в нем. Щелкните правой кнопкой мыши базу данных AdventureWorks2019 , а затем выберите " Преобразовать данные".

Screenshot of the transform data option from the contextual menu inside the Navigator window.

Этот выбор приводит к редактору Power Query и предварительной версии того, что эффективно предназначено для собственного запроса, так как все собственные запросы должны выполняться на уровне базы данных. Проверьте строку формул последнего шага, чтобы лучше понять, как соединитель должен перейти к целевому объекту собственных запросов перед их выполнением. В этом случае строка формул отображает следующие сведения:

= Source{[Name="AdventureWorks2019",Kind="Database"]}[Data]

Источник — это имя предыдущего шага, которое, в данном случае, является просто опубликованной функцией соединителя с переданными параметрами. Список и запись внутри нее просто помогают перемещать таблицу в определенную строку. Строка определяется критериями из записи, в которой имя поля должно быть равно AdventureWorks2019, а поле Kind должно быть равно Базе данных. После того как строка находится, [Data] вне списка {} power Query может получить доступ к значению в поле данных , которое в данном случае является таблицей. Вы можете вернуться к предыдущему шагу (источнику), чтобы лучше понять эту навигацию.

Screenshot of a table that shows the values and fields that were used for the navigation step.

Тестирование собственного запроса

После этого создайте настраиваемый шаг после шага навигации, выбрав значок fx в строке формул.

Screenshot of the fx button inside the formula that's used to create a custom step.

Замените формулу внутри строки формул на следующую формулу и нажмите клавишу ВВОД.

= Value.NativeQuery( AdventureWorks2019_Database, "SELECT TOP (1000) *
  FROM [Person].[Address]")

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

Screenshot of the permission is required to run this native database query warning message.

Выберите "Изменить разрешение". Откроется новое диалоговое окно " Запрос собственной базы данных", которое пытается предупредить вас о возможностях выполнения собственных запросов. В этом случае мы знаем, что эта инструкция SQL безопасна, поэтому нажмите кнопку "Выполнить ", чтобы выполнить команду.

Screenshot showing how to approve a native database query dialog.

После выполнения запроса в редакторе Power Query появится предварительная версия запроса. Эта предварительная версия проверяет, что соединитель может выполнять собственные запросы.

Screenshot of the native query executed in initial connector development and testing.

Реализация собственной логики запросов в соединителе

Используя сведения, собранные из предыдущих разделов, теперь цель заключается в переводе таких сведений в код для соединителя.

Способ выполнения этого перевода заключается в добавлении нового поля записи NativeQueryProperties в запись публикации соединителя, которая в данном случае является записьюSqlODBC.Publish. Запись NativeQueryProperties играет важную роль в определении взаимодействия соединителя с функцией Value.NativeQuery .

Новое поле записи состоит из двух полей:

  • NavigationSteps: это поле определяет, как навигация должна выполняться или обрабатываться соединителем. Он содержит список записей, которые описывают шаги для перехода к определенным данным, которые требуется запрашивать с помощью Value.NativeQuery функции. В каждой записи определяется, какие параметры необходимы или необходимы, чтобы такая навигация достигла требуемого целевого объекта.
  • DefaultOptions: это поле помогает определить, как должны быть включены или добавлены Value.NativeQuery в запись параметров определенные необязательные параметры. Он предоставляет набор параметров по умолчанию, которые можно использовать при запросе источника данных.

Шаги навигации можно разделить на две группы. Первый содержит те значения, которые вводятся конечным пользователем, например имя сервера или базы данных, в данном случае. Второй содержит эти значения, производные от конкретной реализации соединителя, например имя полей, которые не отображаются пользователю во время получения данных. Эти поля могут включать Name, KindDataи другие в зависимости от реализации соединителя.

В этом случае существовал только один шаг навигации, состоящий из двух полей:

  • Имя. Это поле — имя базы данных, переданной конечным пользователем. В этом случае это было AdventureWorks2019, но это поле всегда должно передаваться как из того, что пользователь ввел во время получения данных.
  • Тип: это поле содержит сведения, которые не отображаются для конечного пользователя и относятся к реализации соединителя или драйвера. В этом случае это значение определяет тип объекта. Для этой реализации это поле будет фиксированным значением, состоящим из строки Database.

Такие сведения будут переведены в следующий код. Этот код должен быть добавлен в качестве нового поля в SqlODBC.Publish запись.

NativeQueryProperties = [
    NavigationSteps = {
        [
            Indices = {
                [
                    FieldDisplayName = "database",
                    IndexName = "Name"
                ],
                [
                    ConstantValue = "Database",
                    IndexName = "Kind"
                ]
            },
            FieldAccess = "Data"
        ]
    }
]

Важно!

Имя полей учитывает регистр и должно использоваться, как показано в примере выше. Все данные, передаваемые полям, ConstantValueIndexNameлибо FieldDisplayName должны быть производными от кода M соединителя.

Для значений, которые будут переданы из введенных пользователем значений, можно использовать пару FieldDisplayName и IndexName. Для значений, которые являются фиксированными или предопределенными и не могут передаваться конечным пользователем, можно использовать пару ConstantValue и IndexName. В этом смысле запись NavigationSteps состоит из двух полей:

  • Индексы: определяет поля и значения, используемые для перехода к записи, содержащей целевой объект функции Value.NativeQuery .
  • FieldAccess: определяет, какое поле содержит целевой объект, который обычно является таблицей.

DefaultOptions

Поле DefaultOptions позволяет передавать необязательные параметры Value.NativeQuery функции при использовании собственной возможности запроса для соединителя.

Чтобы сохранить свертывание запросов после собственного запроса и предположить, что у соединителя есть возможности свертывания запросов, можно использовать следующий пример кода для EnableFolding = true.

NativeQueryProperties = [
    NavigationSteps = {
        [
            Indices = {
                [
                    FieldDisplayName = "database",
                    IndexName = "Name"
                ],
                [
                    ConstantValue = "Database",
                    IndexName = "Kind"
                ]
            },
            FieldAccess = "Data"
        ]
    },

    DefaultOptions = [
        EnableFolding = true
    ]
]

С помощью этих изменений создайте соединитель и загрузите его в Power BI Desktop для тестирования и проверки.

Тестирование и проверка соединителя

В Power BI Desktop с новым пользовательским соединителем запустите соединитель из интерфейса получения данных . При запуске соединителя вы заметите, что диалоговое окно теперь имеет длинное текстовое поле с именем Native query и в скобках имеет необходимые поля для работы. Введите те же значения для сервера, базы данных и инструкции SQL, которые вы ранее ввели при тестировании соединителя.

Screenshot of the connector dialog with the native query long text field shown.

После нажатия кнопки "ОК" в новом диалоговом окне отображается предварительная версия таблицы выполненного собственного запроса.

Screenshot of the dialog with a table preview of the executed native query.

Нажмите ОК. Теперь новый запрос загружается в редактор Power Query, где можно выполнить дальнейшее тестирование соединителя по мере необходимости.

Примечание.

Если соединитель имеет возможности свертывания запросов и явно определен EnableFolding=true как часть необязательной записиValue.NativeQuery, можно дополнительно протестировать соединитель в редакторе Power Query, проверка, если дальнейшие преобразования сворачиваться обратно в источник или нет.