Прокачай мой параметризованный SQL

Совет Power-BI Power

Power-BI позволяет использовать пользовательский SQL. Power-BI позволяет создавать параметры. Power-BI *не*предоставляет вам изящное решение для использования этих параметров в вашем пользовательском SQL. Возможно, вы встречали хитрые, полусырые решения, которые делают невозможным копирование кода между Power-BI и редактором SQL. Это не один из них.

Итак, у вас есть исходные данные, упакованные в аккуратный оператор SQL из 500 строк, готовый к загрузке в Power-BI. Это не идеальная ситуация, но я уверен, что у вас есть на то свои причины. Как и я, вы, вероятно, предпочтете выгрузить большую часть логики SQL в ETL и материализовать ее в базе данных, за исключением того, что ограничения проекта диктуют более гибкий подход без суеты. Как и я, вы, вероятно, любите вставлять эти 500 строк в представление или хранимую процедуру, где вы можете редактировать, отлаживать и контролировать версии этого лоха, за исключением (черт возьми) исходной системы. проприетарную базу данных и не позволит вам сохранять ваши собственные объекты.

Не имеет значения. Как я уже сказал, я уверен, что у вас есть свои причины.

Однако Power-BI — ваш друг. В ту же секунду, когда вы даже думаете о возможности получения данных из базы данных, она предлагает вам оператор SQL.

Кроме... Где параметры? Power-BI имеет параметры. Мой оператор SQL имеет параметры.

Как ввести параметры в мой SQL?

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

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

Параметры, приятель? Объединить!

Если вы гуглили «параметризованный SQL Power-BI» (я это сделал) и эта статья не была вашим первым хитом, вы наверняка видели решение конкатенации. Если вы ненавидите это так же сильно, как и я, переходите к следующему разделу.

Давайте посмотрим на пример. Чтобы не усложнять, вот мой SQL:

DECLARE @FromDate DateTime = '2021–01–01'
SELECT
  hashbytes('md5',[name]) AS myID
, [type] AS myDim
, object_ID as myMeasure
FROM sys.all_objects
WHERE create_date >= @FromDate

1. Создайте свой параметр в редакторе Power Query.

2. Создайте собственный источник данных SQL.

…дает нам некоторые данные…

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

3. Теперь, вводя внимательно, разделите это, объединив параметр непосредственно в M-код.

Идеально. Молодцы — у вас есть параметризованный SQL… КОТОРЫЙ ТЕПЕРЬ НЕ МОЖЕТЕ РЕДАКТИРОВАТЬ!!!

Да, верно — настройка М-кода отключает значок шестеренки. Приятно кататься вне трасс с нашими функциями М-кода с двойным черным ромбом и прочим, но что происходит, когда мы хотим вставить новую копию нашего SQL, который мы прототипировали в SQL Management Studio?

Не повезло, Чак. Ты должен начать с нуля.

Параметризируйте как босс

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

Если бы только в редакторе Power Query было удобное место, куда можно было бы копировать и вставлять большое количество…

Подождите минутку! Это похоже на аккуратное место в редакторе Power Query, где я могу копировать и вставлять большие объемы текста.

Что, если мы сохраним нашу инструкцию SQL в таблице Power-Query?

1. Создайте таблицу Power Query с помощью «Введите данные».

Дайте ему 2 столбца: Key и SQLText и дайте таблице очевидное имя, например SQLText.

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

2. Чтобы изменить таблицу SQLText, щелкните значок шестеренки в параметрах запроса.

3. Вставьте свой SQL из Management Studio и назначьте ему ключ

Но замените параметры строками-заполнителями, которые легко найти/заменить.

Отлично, теперь мы сохранили наш параметризованный исходный код. Нам просто нужно получить его, ввести параметры и выполнить.

4. Замените заполнители параметров фактическими значениями параметров.

Вы можете сделать это с помощью «Заменить значения» редактора Power Query…

… но он считает, что FromDate — это текст, а не параметр, поэтому вам все равно придется редактировать сгенерированный код из…

= Table.ReplaceValue(
  #"Changed Type",
  "#FromDate#",
  "FromDate",                          <= See, thinks it's text.
  Replacer.ReplaceText,{"SQLText"})

…to…

= Table.ReplaceValue(
  #"Changed Type",
  "#FromDate#",
  Date.ToText(FromDate, 'yyyy-MM-dd"),     <= That's better
  Replacer.ReplaceText,{"SQLText"})

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

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

5. Замените оператор SQL в источнике данных SQL Server ссылкой на нашу новую таблицу SQLtext.

Помните наш собственный источник данных SQL?

Что ж, теперь нам не нужен встроенный в кавычки SQL, потому что он есть в нашей текстовой таблице SQL, так что просто отредактируйте это...

= Sql.Database("<server>", "<database>", [Query="long SQL statement"])

…с этим…

= Sql.Database("<server>", "<database>", [Query=SQLText{[Key="Production"]}[SQLText] ])

…где «Производство» — это ключ, который мы использовали для входа в таблицу SQLText.

На данный момент мы видим возможности для того, чтобы сделать этот ключ параметром в Power-BI, чтобы мы могли автоматически развернуть новый SQL в рабочей среде и реализовать его с помощью простого изменения параметра.

6. Наконец, скройте таблицу SQLText от пользователей отчетов.

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

Конечный продукт

Давайте посмотрим на наш новый рабочий процесс для изменения SQL.

1. Прототип в Management Studio

2. Вставьте в нашу таблицу SQLText

3. Повторно проверьте свой запрос

4. Грейтесь в лучах славы успеха

Вот и все. Вы можете управлять своими параметризованными операторами SQL вне Power-BI и вставлять их в свою таблицу Power Query, не реконструируя неприятные конкатенации параметров.

Вывод

Что мы узнали?

Power BI позволяет нам использовать параметры, но не предоставляет простого способа заменить их в пользовательском SQL.
Ручное объединение параметров приводит к беспорядку.
Хранение текста наших SQL-запросов в виде данных в таблице Power Query означает, что мы можем вырезать и вставлять их в приятный пользовательский интерфейс. Это также означает, что мы можем выполнять замену параметров с помощью кода Power Query M.
Это открывает множество интересных возможностей для инструментовки и DevOps.

Прокачай мой параметризованный SQL