Свертывание запросов и подключение к SQL Server

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

Свертывание запросов очень эффективно с большими базами данных, чтобы обеспечить повышение производительности ваших отчетов.

Оглавление

• Подключение к базе данных SQL Server для сворачивания запросов
• Общие сведения о свертывании запросов в Power Query
• Выявление и исправление неработающего свертывания запроса
• Преимущества подключения SQL Server и запуска языка SQL
• Заключение

Подключение к базе данных SQL Server для сворачивания запросов

Мы будем выполнять это руководство в редакторе Power Query. Сначала щелкните SQL Server в разделе «Новый источник» .

Если вы уже установили машину SQL-сервера и работаете независимо, имя сервера будет localhost . Однако, если вы работаете в организации, они предоставят вам имя сервера и доступ к своей базе данных. Нам также необходимо указать имя базы данных. В этом примере я буду использовать AdventureWorksDW2012 . Вы можете узнать, как загрузить этот пример базы данных из этого руководства .

Для Data Connectivity Mode все данные, которые мы выберем, будут загружены в модель, если мы выберем Import . Но если мы выберем DirectQuery , в модель данных ничего не будет загружено, но все будет в базе данных. Всякий раз, когда мы применяем фильтр, запрос будет отправлен обратно на SQL Server.

Но это неэффективно, потому что для обновления потребуется больше времени. Поэтому мы выберем Import в качестве режима подключения к данным .

Затем, если мы нажмем « Дополнительные параметры» , он предоставит нам раздел, в котором мы можем написать оператор SQL. Мы научимся это делать позже.

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

Наконец, нажмите ОК .

После этого мы сможем увидеть доступные таблицы и оттуда получить некоторую информацию. В этом примере нам нужно получить данные о продажах через Интернет. Следовательно, давайте найдем и выберем FactInternetSales , затем нажмем Ok .

В результате теперь у нас будут данные в нашем редакторе Power Query.

Общие сведения о свертывании запросов в Power Query

На панели «Источник» щелкните правой кнопкой мыши «Навигация» и выберите «Просмотреть собственный запрос» .

При этом мы сможем увидеть команду, вып��лненную этой машиной. Механизм Power Query создал эту команду для запуска в SQL Server. Теперь давайте нажмем Ок .

В качестве примера я случайным образом создал фильтр в этой таблице, чтобы показать вам, что как только мы их создадим, мы сможем увидеть их на панели ПРИМЕНЯЕМЫЕ ШАГИ .

Когда мы щелкнем правой кнопкой мыши один из фильтров, мы увидим, что параметр View Native Query все еще доступен.

Этот SQL-запрос из нашего фильтра был запущен в SQL Server . Когда мы не применили фильтр, мы получаем 5 миллионов строк. Теперь, когда мы применили фильтр, мы получаем только 4 миллиона строк.

Это означает, что теперь LuckyTemplates извлекает из SQL Server 4 миллиона строк вместо 5 миллионов. При этом количество строк уменьшилось, а также уменьшилось количество загрузок из нашей сети.

Имейте в виду, что пока мы видим Native Query , это означает, что свертывание запросов работает. Таким образом, вся обработка выполняется в исходной системе. Это наиболее эффективный способ обработки данных, особенно если у вас большой объем данных.

Мы добавили еще один шаг, на котором мы удалили один столбец из нашей таблицы. Если мы щелкнем по нему правой кнопкой мыши, мы увидим параметр View Native Query , что означает, что он все еще работает.

Выявление и исправление неработающего свертывания запроса

При некоторых преобразованиях, таких как изменение типа данных столбца, Query Folding сломается. Например, мы изменим тип данных столбца TaxAmt на Whole Number .

Это добавит шаг «Тип изменения» в разделе «ПРИМЕНЕННЫЕ ШАГИ» . Если мы щелкнем по нему правой кнопкой мыши, мы увидим, что View Native Query теперь отключен, что означает, что Query Folding не работает.

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

Например, если мы получим 3 миллиона строк, все они попадут в Power Query. Мы все еще можем уменьшить эти записи с помощью фильтрации. Однако эти 3 миллиона строк теперь будут проходить по сети, что не очень эффективно.

В качестве другого примера предположим, что мы хотим отфильтровать OrderDate , чтобы отображались только даты после 1 января 2012 года.

Если мы просмотрим этот фильтр в APPLIED STEPS , опция View Native Query не будет видна.

Опять же, это потому, что Query Folding был нарушен из-за предыдущего преобразования, которое мы создали. Что мы можем сделать, так это переместить все шаги фильтрации, которые мы будем выполнять, выше шага преобразования, который нарушил Query Folding .

В этом примере мы просто щелкнем правой кнопкой мыши последний созданный нами шаг фильтрации и выберем « Переместить до » или просто перетащим его в верхнюю часть преобразования «Изменить тип» .

Если мы снова щелкнем правой кнопкой мыши по этому фильтру, то увидим, что опция View Native Query теперь видна, что означает, что Query Folding снова работает.

Преимущества подключения SQL Server и запуска языка SQL

Предположим, например, что мы хотим отобразить данные с общим объемом продаж по странам в формате, как показано на рисунке.

В нашей таблице FactInternetSales есть столбец SalesAmount , но в нем нет информации о стране.

Мы по-прежнему можем получать информацию о стране, поскольку у нас есть столбец SalesTerritoryKey .

Что нам нужно сделать, так это принести сюда таблицу DimSales , чтобы мы могли объединить ее с нашей FactInternetSales . Затем нам нужно ввести столбец страны и сгруппировать их по столбцу страны, что очень сложно и может занять много времени. Итак, вместо того, чтобы делать все это в Power Query , что неэффективно, мы должны делать это на SQL .

Для этого просто нажмите «Новый источник» > «SQL Server» .

Давайте снова подключимся к нашему серверу с именем localhost и AdventureWorksDW2012 в качестве нашей базы данных.

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

Мы не хотим включать столбцы отношений, поэтому мы снимем этот флажок здесь. Затем нажмите кнопку OK , чтобы запустить эту команду.

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

Итак, мы смогли получить аналогичный вывод таблицы SalesByCountry , используя простую команду SQL вместо того, чтобы манипулировать различными таблицами и столбцами в нашем Power Query.

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

Загрузка и установка SQL Server
SQL для пользователей LuckyTemplates — новый курс LuckyTemplates
Запрос данных из нескольких источников данных

Заключение

В этом руководстве мы узнали, что такое Query Folding , и обнаружили его преимущества. Мы также обсудили шаги по подключению Power Query к SQL Server .

Более того, мы говорили о преимуществах подключения к SQL Server и создания преобразований на SQL Server вместо того, чтобы делать их на Power Query .

Надеюсь, вы смогли увидеть, насколько эффективнее и быстрее выполнение всех преобразований в SQL по сравнению с их выполнением в Power Query .

Всего наилучшего,

Хафиз