Цей блог-пост є частиною серії “Моделювання даних”, створеної разом з моїм другом і відомим експертом з Power BI – Томом Мартенсом.
Зміст
* ПРЕЛЮДІЯ ДО МОДЕЛЮВАННЯ ДАНИХ З POWER BI
* МОДЕЛЬ ДАНИХ POWER BI
- Таблиці, стовпці та деякі пов’язані речі
- Концепція розширених таблиць
- Обчислювані стовпці та показники
- Небезпека однотабличного рішення
- Кардинальність (Cardinality) – ваша величність!
- Переваги міцних відносин
- Кільцеві залежності
- Ролеве моделювання вимірів
- Двосторонні відносини
* Батьківські-дочірні ієрархії
* Агрегування
* Нестандартне мислення
- Від’єднані таблиці
- Інший підхід до проблеми події, що триває
Як ви вже могли дізнатися з наших попередніх статей у цій серії, створення схеми “зірка” не означає, що ваші завдання з моделювання даних завершені. Є ще багато аспектів, на які варто звернути увагу, і, хоча ви можете уникнути налаштування кожної дрібниці, деякі з цих “дрібниць” безумовно важливі і потребують кращого розуміння. Просто тому, що наслідки можуть бути значними!
Одним з найочевидніших наслідків (не)правильних рішень у Power BI є продуктивність! Продуктивність, продуктивність, продуктивність… Ми завжди прагнемо досягти її (або принаймні МИ ПОВИННІ прагнути), але є багато речей, які потрібно врахувати при налаштуванні звітів у Power BI. Першим кроком у процесі оптимізації є переконання, що розмір вашої моделі даних знаходиться в оптимальному стані – це означає, зменшуйте розмір моделі даних в разі потреби! Це дозволить роботі механізму зберігання даних VertiPaq працювати більш ефективно при отриманні даних для вашого звіту.
Коли ви працюєте над оптимізацією розміру моделі даних, ви знаєте, хто є вашим “ворогом” номер 1?! Тип даних стовпця, чи не так? Текстові стовпці споживатимуть набагато більше пам’яті, ніж стовпець числового типу? Це лише наполовину правда!
Найбільшим супротивником є кардинальність (cardinality)!
Перш ніж пояснювати, що таке кардинальність і чому вона так важлива для зменшення розміру моделі даних, давайте спочатку обговоримо спосіб, яким VertiPaq зберігає дані, якщо ви встановили режим зберігання таблиці у режим «Імпорт» (режим зберігання DirectQuery виходить за межі цієї статті, тому з цього моменту все в цій статті про кардинальність стосується виключно режиму зберігання «Імпорт»).
Як тільки ви встановлюєте режим зберігання на Імпорт, VertiPaq сканує вибіркові рядки з колонки і, на основі даних у певній колонці (не забувайте, що VertiPaq – це колонкова база даних, що означає, що кожна колонка має свою власну структуру і фізично відокремлена від інших колонок), застосовує певний алгоритм стиснення даних.
Існує три різновиди кодування:
- Кодування значення – застосовується виключно до цілочисельних типів даних.
- Хеш-кодування – застосовується до всіх типів даних, що не є цілими числами, а також до цілочисельних типів даних у певних сценаріях.
- RLE (Run-Length-Encoding кодування за довжиною послідовності) – відбувається після хешування, як додатковий етап стиснення, у тих випадках, коли дані в стовпці відсортовані таким чином, що VertiPaq “думає”, що може досягти кращого ступеня стиснення, ніж при використанні лише хеш-алгоритму.
Як бачите, VertiPaq створює словник унікальних значень з стовпця, призначає бітовий індекс кожному з цих значень, а потім зберігає це індексне значення замість «реального» значення – просто кажучи, воно зберігає число 1 як вказівник на значення “Book”, число 2 для “Shirt” і так далі. Це схоже на створення віртуальної таблиці розміром у фоновому режимі!
Тепер уявіть, що замість двох унікальних значень у стовпці ми маємо щось на зразок цього:
Цей приклад ілюструє, як велика кількість унікальних значень у стовпці може призвести до збільшення розміру словника, що в свою чергу може вплинути на розмір моделі даних. Чим більше унікальних значень у стовпці, тим більше пам’яті потрібно для зберігання словника і, відповідно, для моделі даних в цілому. Таким чином, висока кардинальність (кількість унікальних значень) може вплинути на продуктивність та розмір моделі даних.
Кардинальність представляє кількість унікальних значень у стовпці.
Давайте швидко повторимо ключові висновки з ілюстрації вище. У таблиці Chats стовпець datetmStartUTC, який має тип даних Дата/Час і має другий рівень точності, має майже 9 мільйонів унікальних значень! Його розмір становить приблизно 455 МБ – ця цифра включає не лише розмір даних, який складає 26 МБ, але й розмір словника та ієрархії. Ви можете побачити, що VertiPaq використовує алгоритм Хеш для стиснення даних з цього стовпця, але найбільша частина обсягу пам’яті йде на розмір словника (майже 358 МБ).
Очевидно, це далеко не оптимальний стан для нашої моделі даних. Однак у мене є для вас хороші новини…
Існують кілька технік, які можуть покращити рівні кардинальності!
У одній з попередніх статей я пояснив, як ви можете зменшити кардинальність, застосовуючи більш складні підходи, такі як використання операцій ділення і взяття остачі, щоб розділити один числовий стовпчик з високою кардинальністю на два стовпчики з нижчою кардинальністю та заощадити кілька бітів на рядок. Я також показав вам, як розділити стовпчик Дата/Час на два окремі стовпчики – один, що містить лише частину дати, а інший – часові дані.
Проте ці техніки потребують додаткових зусиль на стороні звіту, оскільки всі показники потрібно переписати, щоб відображати структурні зміни в моделі даних та повертати правильні результати. Тому ці складні підходи – це не щось, що вам доведеться застосовувати в звичайній оптимізації моделі даних – вони більше підходять для “екстремальних” випадків, коли немає іншого способу зменшити розмір моделі даних – коротше кажучи, коли ви працюєте з дуже великими наборами даних!
Але це не означає, що вам не варто прагнути покращувати рівні кардинальності навіть для менших і простіших моделей даних. Навпаки, це повинна бути регулярна частина вашого процесу розробки Power BI. У цій статті я покажу вам два простих підходи, які значно зменшують кардинальність стовпчика, тим самим зменшуючи загальний розмір моделі даних.
Покращення рівнів кардинальності за допомогою зміни типу даних
Будьмо чесні – як часто вашим користувачам потрібно аналізувати дані на другому рівні? Наприклад, скільки продажів ми маємо о 09:35:36 або 11:22:48? Це не має сенсу, я б сказав. У 98% випадків запит бізнесу полягає в наявності даних на щоденному рівні. Можливо, у деяких випадках користувачам потрібно зрозуміти, яка частина дня є найбільш “продуктивною”: ранок, день або вечір … але все ж, давайте зосередимося на більшості випадків, де дані повинні бути розгорнуті на щоденному рівні.
Я змінив тип даних стовпця з Дата/Час на Дата, оскільки час не має значення для звітності. Давайте оновимо наші метрики в DAX Studio:
Ого, як приємно! Замість кардинальності близько 9 мільйонів, у нас зараз всього 1356 (це кількість унікальних днів у стовпці). Проте, що є важливішим, розмір стовпця зменшився з 455 МБ до 7 МБ! Це величезна зміна! Подивіться на розмір словника: замість необхідності побудови словника для 9 мільйонів значень, VertiPaq зараз опрацьовує лише 1356 унікальних значень, а розмір словника скоротився з 358 МБ до 85 КБ!
Іншим трюком, який ви можете застосувати, є робота з десятковими значеннями. Це не рідка ситуація, коли ви імпортуєте дані в Power BI “як є” – і я не раз бачив, як люди імпортували десяткові числа, що мають 5 значень після десяткової коми! Це означає, чи дійсно необхідно знати, що загальна сума продажів становить 27 586 398,56891, чи достатньо відображати 27 586 398,57?
Щоб було зрозуміло, форматування значень для відображення 2 десяткових знаків у Power BI не впливає на розмір моделі даних – це просто опція форматування для ваших візуальних елементів – насправді, дані зберігаються з 5 цифрами після десяткового розряду.
Зараз давайте перевіримо метри цієї таблиці в DAX Studio. Це досить простий розмір моделі даних, з всього 151 унікальними значеннями в ньому, але ви можете уявити різницю на таблиці з мільйонами рядків:
Зараз я перейду до редактора Power Query та зміню тип цього стовпця на Фіксоване десяткове число:
Зараз ми округлили значення до 2 десяткових знаків після десяткової коми, тому давайте знову перейдемо до DAX Studio та перевіримо числа:
Різниця очевидна, навіть на цьому надзвичайно малому наборі даних!
ОНОВЛЕННЯ 2022-11-13: Дякую Wyn Hopkins, який правильно зазначив, що важливо мати на увазі, що тип даних Decimal має 15 десяткових знаків точності, тоді як тип Fixed Decimal зберігає 4 десяткові знаки, як це документовано тут.
І якщо ви зараз думаєте: добре, це в порядку, якщо я округлю кожне окреме значення транзакції, але як це вплине на мої обчислення? Ну, давайте розглянемо досить поширену ситуацію обчислення відсотка від загальної суми для кожного рядка в таблиці:
Давайте зараз перевіримо, як працює це обчислення, коли ми зменшуємо кількість унікальних значень у стовпці і округляємо значення до двох десяткових знаків:
Теперішня справедлива питання буде: чи будуть бізнес користувачі цікавитися, якщо частка окремого рядка становить 0,66% чи 0,66225%… Якщо ці значення після другого десяткового знака є критичними для бізнесу, тоді – чорт, так, хто турбується про кількість кардинальних значень:)… Але, я б сказав, що в 99,99% випадків нікому не буде важливою різниця між 0,66 і 0,66225. У будь-якому випадку, навіть якщо хтось настоює на такій точності, спробуйте пояснити їм всі недоліки, які приносить висока кількість кардинальних значень (споживання пам’яті, повільні обчислення і так далі), особливо на великих таблицях фактів.
Покращення рівня кардинальності за допомогою узагальнення та групування
Інша техніка, яку я хотів вам показати, полягає в тому, як скористатися концепціями узагальнення та групування, щоб покращити рівень кардинальності та зробити вашу модель даних більш продуктивною.
Коли ви створюєте звіти, є ймовірність, що користувачі повинні розуміти різні метрики на вищому рівні деталізації, ніж окрема транзакція – наприклад, скільки продуктів було продано за певну дату, скільки клієнтів підписалися за певну дату, і так далі. Це означає, що більшість ваших аналітичних запитів не повинні бути спрямовані на одну окрему транзакцію, оскільки узагальнені дані будуть зовсім в порядку.
Давайте розглянемо різницю у пам’яті, якщо ми узагальнимо дані для нашої таблиці Чатів. Оригінальна таблиця, як ви можете пам’ятати з попередньої частини статті, займає 555 МБ:
Тепер, якщо я попередньо узагальню дані та групую їх за датою і/або продуктом, написавши наступний T-SQL:
SELECT CONVERT(DATE,datetmStartUTC) AS datetm
,productID
,COUNT(chatID) AS totalChats
FROM Chats
GROUP BY CONVERT(DATE,datetmStartUTC)
,productIDЯкщо найчастіші запити бізнесу полягають в аналізі кількості чатів за датою та/або продуктом, цей запит успішно задовольнить ці запити.
Давайте перевіримо розмір цієї узагальненої таблиці порівняно з оригінальною:
В той час як оригінальна таблиця, навіть з поліпшеним рівнем кардинальності для стовпця datetmStartUTC, займає 105 МБ, узагальнена таблиця займає лише 217 КБ! І ця таблиця може відповісти на більшість запитів бізнесу для аналізу. Навіть якщо потрібно включити додаткові атрибути, наприклад, дані про клієнта, це все ще буде більш оптимальним способом отримання даних.
Але це ще не все!
Навіть якщо вам не вдається створити узагальнені дані на стороні джерела даних, ви все ще можете отримати значний приріст продуктивності, використовуючи функцію агрегування в Power BI. Це одна з найпотужніших функцій в Power BI і заслуговує на окрему статтю, або навіть серію статей, подібних до цієї від Phil Seamark, на яку я завжди посилаюся, коли мені потрібен глибокий розуміння агрегацій та того, як вони працюють в моделі табличних даних.
Висновок
Побудова оптимальної моделі даних – це не легке завдання! Існує багато потенційних пасток, і іноді важко уникнути всіх пасток по дорозі до “ідеальної” моделі. Однак розуміння важливості зменшення загального розміру моделі даних – одна з ключових вимог при налаштуванні вашого рішення Power BI.
З цим на увазі, для досягнення оптимального розміру моделі даних потрібно засвоїти поняття кардинальності, як основного фактора, що визначає розмір стовпця. Розуміючи кардинальність належним чином, а також те, як VertiPaq зберігає та стискає дані, ви повинні мати можливість застосовувати деякі з технік, які ми щойно розглянули, щоб покращити рівні кардинальності стовпця, а отже, підвищити загальну продуктивність ваших звітів!
Дякую за увагу!
Останнє оновлення 13 листопада 2022 року за Nikola
Теги: Моделювання даних, Power BI
ОРИГІНАЛ СТАТТІ: Cardinality – Your Majesty!
АВТОР СТАТІ:Nikola
🚀Долучайтесь до нашої спільноти Telegram:
🚀Долучайтесь до нашої спільноти FaceBook: