Згортання запитів і підключення до SQL Server

У цьому посібнику ми навчимося підключатися до SQL-сервера. Ми також обговоримо, як працює згортання запитів у Power Query та як ми можемо виконувати команди SQL у LuckyTemplates. Усередині Power Query ми можемо отримувати та маніпулювати даними з різних джерел. За допомогою згортання запитів ми можемо перевантажувати перетворення даних у джерело замість того, щоб робити їх у LuckyTemplates.

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

Зміст

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

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

Ми будемо виконувати цей посібник у редакторі Power Query. Спочатку клацніть SQL Server під New Source .

Якщо ви вже встановили сервер SQL і працюєте незалежно, ім’я сервера буде localhost . Однак, якщо ви працюєте в організації, вони нададуть вам ім’я сервера та доступ до своєї бази даних. Нам також потрібно вказати назву бази даних. У цьому прикладі я буду використовувати AdventureWorksDW2012 . Ви можете дізнатися, як завантажити цей зразок бази даних з цього підручника .

Для режиму зв’язку даних усі дані, які ми виберемо, будуть завантажені в модель, якщо ми виберемо Імпортувати . Але якщо ми виберемо DirectQuery , нічого не буде завантажено в модель даних, але все буде в базі даних. Щоразу, коли ми застосовуємо фільтр, запит буде надіслано назад на SQL Server.

Але це неефективно, оскільки для оновлення знадобиться більше часу. Тому ми виберемо Імпорт як режим підключення даних .

Тоді, якщо ми клацнемо « Додаткові параметри» , це надасть нам розділ, де ми зможемо написати оператор SQL. Пізніше ми навчимося це робити.

У цьому прикладі нам потрібна лише одна таблиця за раз, тому ми не будемо використовувати стовпці чи таблиці зв’язків. У такому випадку ми повинні зняти прапорець для цього параметра.

Нарешті натисніть OK .

Після цього ми зможемо побачити доступні таблиці, а звідти ми зможемо отримати певну інформацію. З цього прикладу нам потрібно отримати дані про факти інтернет-продажів. Отже, давайте здійснимо пошук і виберемо FactInternetSales , а потім клацнемо OK .

У результаті ми тепер матимемо дані в нашому редакторі Power Query.

Розуміння згортання запитів у Power Query

На панелі «Джерело» клацніть правою кнопкою миші «Навігація» , а потім виберіть «Переглянути власний запит» .

Після цього ми зможемо побачити команду, виконану цією машиною. Механізм Power Query створив цю команду для виконання в SQL Server. Давайте тепер натиснемо OK .

Як приклад, я навмання створив фільтр у цій таблиці, щоб показати вам, що коли ми створимо його, ми зможемо побачити його на панелі « ЗАСТОСУВАНІ КРОКИ» .

Коли ми клацнемо правою кнопкою миші один із фільтрів, ми побачимо, що опція «Переглянути власний запит» все ще доступна.

Цей SQL-запит із нашого фільтра було запущено на SQL Server . Якщо ми не застосовуємо фільтр, ми отримуємо 5 мільйонів рядків. Тепер, коли ми застосували фільтр, ми отримуємо лише 4 мільйони рядків.

Це означає, що LuckyTemplates тепер витягує з SQL Server 4 мільйони рядків замість 5 мільйонів. Завдяки цьому кількість рядків зменшилася, а також кількість завантажень із нашої мережі.

Майте на увазі, що якщо ми бачимо Native Query , це означає, що згортання запиту працює. Таким чином, уся обробка виконується у вихідній системі. Це найефективніший спосіб обробки даних, особливо якщо у вас є великий обсяг даних.

Ми додали ще один крок, де ми видалили один стовпець із нашої таблиці. Якщо ми клацнемо його правою кнопкою миші, ми побачимо опцію «Переглянути власний запит» , що означає, що він все ще працює.

Виявлення та виправлення несправного згортання запиту

З деякими перетвореннями, наприклад зміною типу даних стовпця, згортання запиту порушиться. Наприклад, ми змінимо тип даних стовпця TaxAmt на Whole Number .

Це додасть крок «Тип зміни» під «ЗАСТОСОВАНІ КРОКИ» . Якщо ми клацнемо на ньому правою кнопкою миші, ми побачимо, що View Native Query тепер вимкнено, що означає, що згортання запиту порушено.

Якщо згортання запиту порушено, будь-яке інше перетворення, яке ми зробимо, буде виконано в Power Query LuckyTemplates, але більше не у вихідній системі.

Наприклад, якщо ми отримуємо 3 мільйони рядків, усі вони потраплять у Power Query. Ми все ще можемо зменшити ці записи за допомогою фільтрації. Однак ці 3 мільйони рядків тепер потраплять у мережу, що не дуже ефективно.

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

Якщо ми переглянемо цей фільтр у APPLIED STEPS , параметр «Переглянути власний запит» не буде видно.

Знову ж таки, це тому, що згортання запиту було порушено через попереднє перетворення, яке ми створили. Що ми можемо зробити, так це перемістити всі кроки фільтрації, які ми будемо виконувати, вище кроку перетворення, який зламав Query Folding .

У цьому прикладі ми просто клацнемо правою кнопкою миші нещодавній крок фільтрації, який ми створили, і клацнемо « Перемістити перед » або просто перетягнемо його вгору трансформації «Змінити тип» .

Якщо ми знову клацнемо правою кнопкою миші на цьому фільтрі, ми побачимо, що опція « Переглянути власний запит» тепер є видимою, що означає, що згортання запиту знову працює.

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

Скажімо, наприклад, що ми хочемо відобразити дані із загальним обсягом продажів у форматі країни, як показано на зображенні.

У нашій таблиці FactInternetSales є стовпець SalesAmount , але в ньому немає інформації про країну.

Ми все ще можемо отримати інформацію про країну, оскільки у нас є стовпець SalesTerritoryKey .

Нам потрібно перенести сюди таблицю DimSales , щоб ми могли об’єднати її з нашим FactInternetSales . Потім нам потрібно перенести стовпець країни та згрупувати їх за стовпцем країни, що є дуже складним і може зайняти багато часу. Отже, замість того, щоб робити все це в Power Query , що неефективно, ми повинні робити це натомість.

Для цього просто натисніть «Нове джерело» > «SQL Server» .

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

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

Ми не хочемо включати стовпці зв’язків, тому знімемо прапорець тут. Потім натисніть кнопку OK , щоб запустити цю команду.

Після виконання команди ми побачимо це вікно попереднього перегляду, у якому показано загальний обсяг продажів за регіоном продажів .

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

Ще одна перевага полягає в тому, що ми можемо трансформувати всі наші дані в SQL і передавати лише необхідні або потрібні дані в нашу модель даних. Завдяки цьому ми можемо створити дуже ефективну модель даних відповідно до нашого плану без будь-яких труднощів чи проблем.

Висновок

У цьому посібнику ми дізналися, що таке згортання запитів , і відкрили його переваги. Ми також обговорили кроки підключення Power Query до SQL Server .

Крім того, ми говорили про переваги підключення до SQL Server і створення перетворень на SQL Server замість виконання їх у Power Query .

Сподіваємось, ви змогли побачити, як виконання всіх перетворень у SQL ефективніше та швидше порівняно з виконанням у Power Query .

Все найкраще,

Хафіз