dimensional data model data warehouse tutorial with examples
Цей посібник пояснює переваги та міфи розмірної моделі даних у сховищі даних. Також дізнайтеся про розмірні таблиці та таблиці фактів із прикладами:
Тестування сховища даних було пояснено в нашому попередньому підручнику, у цьому Навчальна серія сховища даних для всіх .
Величезні дані впорядковуються у сховищі даних (DW) за допомогою методів розмірного моделювання даних. Ці методи розмірного моделювання даних роблять роботу кінцевих користувачів дуже легкою для отримання інформації про бізнес-дані. Цей посібник пояснює все про розмірні моделі даних у DW.
Цільова аудиторія
- Розробники та тестувальники сховища даних / ETL.
- Фахівці з баз даних, що володіють базовими знаннями концепцій баз даних
- Адміністратори баз даних / експерти з великих даних, які хочуть зрозуміти концепції сховища даних / ETL.
- Випускники коледжів / курси підвищення кваліфікації, які шукають роботу зі сховищем даних.
Що ви дізнаєтесь:
Розмірні моделі даних
Розмірні моделі даних - це структури даних, доступні кінцевим споживачам у потоці ETL для запиту та аналізу даних. Процес ETL закінчується завантаженням даних у цільові розмірні моделі даних. Кожна мірна модель даних побудована з таблицею фактів, оточеною кількома таблицями вимірів.
Кроки, яких слід дотримуватися при розробці Розмірної моделі даних:
Переваги розмірного моделювання даних
Нижче наведено різні переваги розмірного моделювання даних.
- Вони захищені для використання постійно мінливих середовищ DW.
- Величезні дані можна легко створити за допомогою мірних моделей даних.
- Дані з мірних моделей даних легко зрозуміти та проаналізувати.
- Вони швидко доступні кінцевим користувачам для запитів з високою продуктивністю.
- Розмірні моделі даних дозволяють детально (або) згорнути дані в ієрархічному порядку.
Моделювання ER проти розмірного моделювання даних
- Моделювання ER підходить для операційних систем, тоді як розмірне моделювання підходить для сховища даних.
- Моделювання ER підтримує детальну інформацію про поточні транзакції, тоді як мірне моделювання забезпечує зведення поточних та історичних даних транзакцій.
- Моделювання ER має нормалізовані дані, тоді як мірне моделювання має денормалізовані дані.
- Моделювання ER використовує більше об’єднань під час отримання запиту, тоді як розмірне моделювання використовує меншу кількість об’єднань, отже, ефективність запитів швидша при вимірюванні розмірів.
Міфологічні міфи моделювання даних
Нижче наведено деякі існуючі міфи мірних даних, що моделюють дані.
- Розмірні моделі даних використовуються лише для представлення зведення даних.
- Вони специфічні для відділу в організації.
- Вони не підтримують масштабованість.
- Вони призначені для цілей звітів та запитів кінцевих користувачів.
- Ми не можемо інтегрувати розмірні моделі даних.
Таблиці розмірів
Таблиці розмірів відіграють ключову роль у системі DW, зберігаючи всі проаналізовані метричні значення. Ці значення зберігаються у легко обраних розмірних атрибутах (стовпцях) у таблиці. Якість системи DW здебільшого залежить від глибини розмірних атрибутів.
Отже, ми повинні спробувати надати багато атрибутів разом із відповідними значеннями в таблицях розмірностей.
Давайте дослідимо структуру таблиць розмірів !!
# 1) Ключ таблиці розмірів: Кожна таблиця розмірів матиме будь-який із своїх атрибутів розмірності як первинний ключ для унікальної ідентифікації кожного рядка. Отже, різні числові значення цього атрибуту можуть діяти як первинні ключі.
Якщо значення атрибута в будь-якому випадку не є унікальними, тоді ви можете розглядати послідовно генеровані системні номери як первинні ключі. Вони також називаються сурогатними ключами.
Розмірні моделі даних повинні мати обмеження цілісності посилань для кожного ключа між вимірами та фактами. Таким чином, таблиці фактів матимуть посилання на зовнішній ключ для кожного первинного / сурогатного ключа в таблиці вимірів для підтримки цілісності посилань.
Якщо це не вдалося, відповідні дані таблиці фактів не можуть бути отримані для цього вимірювального ключа.
# 2) Стіл широкий: Ми можемо сказати, що таблиці розмірів є широкими, оскільки ми можемо додати будь-яку кількість атрибутів до таблиці розмірів у будь-яку точку циклу DW. Архітектор DW попросить команду ETL додати відповідні нові атрибути до схеми.
У сценаріях реального часу ви можете побачити таблиці розмірів із 50 (або) більше атрибутами.
# 3) Текстові атрибути: Розмірні атрибути можуть бути будь-якого типу, бажано текстовими (або) числовими. Текстові атрибути матимуть справжні ділові слова, а не коди. Таблиці розмірів не призначені для розрахунків, отже числові значення рідко використовуються для розмірних атрибутів.
# 4) Атрибути можуть бути не пов’язані безпосередньо: Всі атрибути в таблиці розмірностей можуть не мати відношення один до одного.
# 5) Не нормалізовано: Нормалізація таблиці розмірів призводить до появи більшої кількості проміжних таблиць, що є неефективним. Таким чином таблиці розмірів не нормуються.
Розмірні атрибути можуть виступати джерелом обмежень у запитах, а також можуть відображатися у звітах як мітки. Запити будуть виконуватися ефективно, якщо ви безпосередньо виберете атрибут із таблиці розмірностей і перейдете безпосередньо до відповідної таблиці фактів, не торкаючись інших таблиць-посередників.
# 6) Буріння та згортання: Атрибути вимірювання мають можливість деталізувати (або) згортати дані, коли це потрібно.
# 7) Кілька ієрархій: Таблиця одного виміру, що має кілька ієрархій, є дуже поширеною. Таблиця розмірів матиме просту ієрархію, якщо існує лише один шлях від нижнього рівня до верхнього. Подібним чином він матиме кілька ієрархій, якщо існує кілька шляхів, які потрібно дістатися від нижнього рівня до верхнього.
# 8) Кілька записів: Таблиці вимірів матимуть менше записів (у сотнях), ніж таблиці фактів (у мільйонах). Хоча вони менші за факти, вони надають всі дані для таблиць фактів.
Ось приклад таблиці розмірів клієнта:
Розуміючи наведені вище концепції, ви можете вирішити, чи може поле даних діяти як атрибут виміру (або) не під час вилучення даних із самого джерела.
Основний план навантаження для розміру
Розміри можуть бути створені двома способами, тобто шляхом вилучення даних про розміри із зовнішніх систем джерела (або) Система ETL може будувати виміри з етапу без залучення будь-яких зовнішніх джерел. Однак система ETL без будь-якої зовнішньої обробки більше підходить для створення таблиць розмірів.
Нижче наведені кроки, задіяні в цьому процесі:
найкращий інструмент скріншоту для Windows 10
- Очищення даних: Дані очищаються, перевіряються та застосовуються ділові правила перед завантаженням у таблицю розмірів для підтримки узгодженості.
- Відповідність даних: Дані з інших частин сховища даних повинні бути належним чином агреговані як одне значення для кожного поля таблиці вимірів.
- Спільний доступ до тих самих доменів: Після підтвердження дані знову зберігаються у проміжних таблицях.
- Доставка даних: Нарешті, всі розмірні значення атрибута завантажуються призначеними первинними / сурогатними ключами.
Типи розмірів
Різні типи розмірів наведені нижче для довідки.
Давайте розпочнемо!!
# 1) Малі розміри
Невеликі розміри в сховищі даних виступають як таблиці пошуку з меншою кількістю рядків і стовпців. Дані невеликих розмірів можна легко завантажити з електронних таблиць. За необхідності малі розміри можна поєднати як супервимір.
# 2) Відповідний вимір
Відповідна розмірність - це розмірність, на яку можна посилатися однаково з кожною таблицею фактів, з якою вона пов’язана.
Вимірювання дати є найкращим прикладом відповідного виміру, оскільки атрибути вимірювання дати, такі як рік, місяць, тиждень, дні тощо, передають одні й ті самі дані однаково для будь-якої кількості фактів.
Приклад відповідного виміру.
# 3) Небажаний вимір
Мало атрибутів у таблиці фактів, таких як прапори та індикатори, можна перемістити в окрему таблицю розмірів небажаної інформації. Ці атрибути також не належать до будь-яких інших існуючих таблиць розмірів. Загалом, значення цих атрибутів - це просто «так / ні» (або) «true / false».
Створення нового виміру для кожного окремого атрибута прапора робить його складним, створюючи більшу кількість зовнішніх ключів до таблиці фактів. У той же час, зберігання всіх цих прапорів та інформації про показники у фактичних таблицях також збільшує обсяг даних, що зберігаються у фактах, що тим самим погіршує продуктивність.
Отже, найкращим рішенням для цього є створення єдиного непотрібного виміру, оскільки непотрібний вимір може містити будь-яку кількість показників 'так / ні' або 'істинно / помилково'. Однак у непотрібних вимірах зберігаються описові значення цих показників (так / ні (або) true / false), такі як активний і очікуваний тощо.
Виходячи зі складності таблиці фактів та її показників, таблиця фактів може мати один або кілька розмірів непотрібної інформації.
Приклад сміття.
# 4) Рольовий вимір
Єдиний вимір, на який можна посилатись з різними цілями у таблиці фактів, відомий як Рольовий вимір.
Найкращим прикладом для рольового виміру знову є таблиця вимірювання дати, оскільки той самий атрибут дати у вимірі може використовуватися для різних цілей, таких як дата замовлення, дата доставки, дата транзакції, дата скасування, тощо
За необхідності ви можете створити чотири різні подання у таблиці вимірів дати щодо чотирьох різних атрибутів дати таблиці фактів.
Приклад рольового виміру.
# 5) Вироджені розміри
Може бути мало атрибутів, які не можуть бути ані вимірами (метриками), ані фактами (мірами), але вони потрібні для аналізу. Усі такі атрибути можна перенести у вироджені виміри.
Наприклад, Ви можете розглядати номер замовлення, номер рахунку-фактури тощо як вироджені атрибути розмірів.
Приклад виродженого виміру.
# 6) Повільна зміна розмірів
Повільно мінливий вимір - це вид, коли дані можуть змінюватися повільно в будь-який час, а не через періодичні регулярні проміжки часу. Зі зміненими даними в таблицях розмірностей можна обробляти різні способи, як пояснено нижче.
Ви можете вибрати тип SCD, щоб індивідуально реагувати на зміну для кожного атрибута в мірній таблиці.
(i) SCD типу 1
- У типі 1, коли змінюються значення атрибутів розмірності, існуючі значення замінюються нещодавно зміненими значеннями, що є нічим іншим, як оновленням.
- Старі дані не зберігаються для історичної довідки.
- Минулі звіти неможливо відновити через відсутність старих даних.
- Легкий в обслуговуванні.
- Вплив на таблиці фактів більший.
Приклад SCD типу 1:
(Ii) Тип 2 SCD
- У типі 2, коли змінюються значення атрибутів розмірності, буде вставлений новий рядок із зміненими значеннями без зміни старих даних рядка.
- Якщо в будь-якій з таблиць фактів є посилання на зовнішній ключ, яке існує до старого запису, то старий сурогатний ключ автоматично оновлюється скрізь за допомогою нового сурогатного ключа.
- Вплив на зміни таблиці фактів стає набагато меншим із зазначеним вище кроком.
- Старі дані ніде не враховуються після змін.
- У типі 2 ми можемо відстежувати всі зміни, що відбуваються з розмірними атрибутами.
- Немає обмежень на зберігання історичних даних.
- У типі 2 додавання кількох атрибутів до кожного рядка, таких як змінена дата, дата фактичного часу, дата-час закінчення, причина зміни та поточний прапор є необов’язковим. Але це важливо, якщо бізнес хоче знати кількість змін, внесених протягом певного періоду часу.
Приклад SCD типу 2:
(Iii) Тип 3 SCD
- У типі 3, коли змінюються значення розмірних атрибутів, нові значення оновлюються, але старі значення все ще залишаються дійсними як другий варіант.
- Замість додавання нового рядка для кожної зміни буде додано новий стовпець, якщо він раніше не існував.
- Старі значення розміщуються у доданих вище атрибутах, а дані основного атрибута замінюються зміненим значенням, як у типі 1.
- Існує обмеження на зберігання історичних даних.
- Вплив на таблиці фактів більший.
Приклад SCD типу 3:
(iv) SCD типу 4
- У типі 4 поточні дані зберігаються в одній таблиці.
- Усі історичні дані містяться в іншій таблиці.
Приклад SCD типу 4:
(v) SCD типу 6
- Розмірна таблиця також може мати комбінацію всіх трьох типів SCD 1, 2 і 3, яка відома як гібридна тип 6 (або), що повільно змінюється.
Фактичні таблиці
Фактичні таблиці зберігають набір кількісно виміряних значень, які використовуються для розрахунків. Значення таблиці фактів відображаються у бізнес-звітах. На відміну від типів текстових даних таблиць розмір, тип даних таблиць фактів є суттєво числовим.
Фактичні таблиці глибокі, тоді як розмірні таблиці широкі, оскільки таблиці фактів матимуть більшу кількість рядків і меншу кількість стовпців. Первинний ключ, визначений у таблиці фактів, полягає насамперед у ідентифікації кожного рядка окремо. Первинний ключ також називається складеним ключем фактично таблиці.
Якщо у таблиці фактів відсутній складений ключ, і якщо будь-які два записи мають однакові дані, дуже важко розрізняти дані та посилати дані в таблиці розмірностей.
Отже, якщо відповідний унікальний ключ існує як складений ключ, тоді добре створити порядковий номер для кожного запису таблиці фактів. Іншою альтернативою є формування об’єднаного первинного ключа. Це буде генеровано шляхом об'єднання всіх згаданих первинних ключів таблиць розмірів по рядках.
Одиночну таблицю фактів можна оточувати таблицями кількох вимірів. За допомогою зовнішніх ключів, які існують фактично в таблицях, на відповідний контекст (детальні дані) виміряних значень можна посилатись у таблицях розмірностей. За допомогою запитів користувачі будуть ефективно виконувати деталізацію та згортання.
Найнижчий рівень даних, який можна зберегти в таблиці фактів, відомий як детальність. Кількість таблиць розмірностей, пов'язаних з таблицею фактів, обернено пропорційна деталізації даних цієї таблиці фактів. тобто для найменшого значення вимірювання потрібно посилати більше таблиць розмірів.
У мірній моделі таблиці фактів підтримують зв'язок 'багато-до-багатьох' із таблицями розмірностей.
Приклад таблиці фактів продажів:
Завантажити план для таблиць фактів
Ви можете ефективно завантажувати дані таблиці фактів, враховуючи такі вказівки:
# 1) Видалення та відновлення індексів
Індекси в таблицях фактично є хорошими прискорювачами продуктивності під час запиту даних, але вони знищують продуктивність під час завантаження даних. Отже, перед завантаженням будь-яких величезних даних у таблиці фактів, перш за все, скиньте всі індекси в цій таблиці, завантажте дані та відновіть індекси.
# 2) Окремі вставки від оновлень
Не об’єднуйте вставки та оновлення записів під час завантаження у таблицю фактів. Якщо кількість оновлень менше, обробляйте вставки та оновлення окремо. Якщо кількість оновлень більше, доцільно скоротити та перезавантажити таблицю фактів для швидких результатів.
# 3) Розбиття
Здійснюйте фізичне розділення таблиці фактів на міні-таблиці для кращої продуктивності запитів щодо масивних даних таблиці фактів. За винятком DBA та команди ETL, ніхто не буде знати про розділи на факти.
Як приклад , ви можете розділити таблицю щомісячно, квартально, річно і т. д. Під час запиту враховуються лише розділені дані, а не сканування всієї таблиці.
# 4) Паралельне навантаження
інструменти тестування безпеки для веб-додатків -
Зараз ми отримали уявлення про розділи на таблицях фактів. Розділи на факти також корисні при завантаженні величезних даних у факти. Для цього спочатку розбийте дані логічно на різні файли даних та запустіть завдання ETL, щоб паралельно завантажити всі ці логічні частини даних.
# 5) Утиліта масового навантаження
На відміну від інших систем RDBMS, система ETL не потребує явного ведення журналів відкоту для відмов у середині транзакції. Тут “масові навантаження” трапляються у факти, а не “вставки SQL”, щоб завантажити величезні дані. Якщо в разі виходу з ладу одного завантаження, цілі дані можна легко перезавантажити (або) вони можуть продовжуватися з того місця, де вони зупинені з масовим завантаженням.
# 6) Видалення запису про факти
Видалення запису таблиці фактів відбувається лише в тому випадку, якщо компанія прямо хоче. Якщо є будь-які дані таблиці фактів, які більше не існують у вихідних системах, тоді ці дані можна видалити фізично (або) логічно.
- Фізичне видалення: Небажані записи видаляються з таблиці фактів назавжди.
- Логічне видалення: До таблиці фактів буде додано новий стовпець, наприклад „видалено“ бітового (або) логічного типу. Це діє як прапор для представлення видалених записів. Ви повинні переконатися, що не вибираєте видалені записи під час запиту даних таблиці фактів.
# 7) Послідовність оновлення та видалення з таблиці фактів
Коли є будь-які дані, які слід оновити, таблиці розмірів повинні спочатку оновитись, а потім оновити сурогатні ключі в таблиці пошуку, якщо це необхідно, а потім оновити відповідну таблицю фактів. Видалення відбувається в зворотному порядку, оскільки видалення всіх небажаних даних з таблиць фактів дозволяє легко видалити пов’язані небажані дані з таблиць розмірів.
Ми повинні дотримуватися наведеної вище послідовності в обох випадках, оскільки таблиці розмірів і таблиці фактів постійно підтримують посилальну цілісність.
Типи фактів
На основі поведінки даних таблиць фактів вони класифікуються як таблиці фактів транзакцій, таблиці фактів моментальних знімків та накопичені таблиці фактів моментальних знімків. Всі ці три типи мають різні функції з різними стратегіями завантаження даних.
# 1) Таблиці фактів транзакцій
Оскільки назва вказує, таблиці фактів транзакцій зберігають дані рівня транзакції для кожної події, що трапляється. Такі дані легко аналізувати на рівні таблиці фактів. Але для подальшого аналізу ви також можете звернутися до відповідних вимірів.
Наприклад, кожна продажа (або) покупка, що відбувається на веб-сайті маркетингу, повинна завантажуватися в таблицю фактів транзакцій.
Приклад таблиці фактів транзакцій наведено нижче.
# 2) Періодичні таблиці фактичних знімків
Оскільки назва вказує, дані у таблиці фактів періодичних знімків зберігаються у вигляді знімків (зображень) з періодичними інтервалами, наприклад, на кожен день, тиждень, місяць, квартал тощо, залежно від потреб бізнесу.
Тож зрозуміло, що це постійне об’єднання даних. Отже, факти зйомки є більш складними порівняно з таблицями фактів транзакцій. Наприклад, будь-які дані звітів про доходи від ефективності можна зберігати у таблицях фактичних знімків для зручності.
Приклад періодичної таблиці фактичних знімків наведено нижче.
# 3) Накопичення таблиць фактичних моментів
Накопичувальні таблиці фактичних знімків дозволяють зберігати дані в таблицях протягом усього терміну служби продукту. Це діє як поєднання двох вищезазначених типів, де дані можуть бути вставлені будь-якою подією в будь-який час як знімок.
У цьому типі додаткові стовпці дати та дані для кожного рядка оновлюються з кожним етапом цього продукту.
Приклад накопичувальної таблиці фактичних знімків.
На додаток до вищезазначених трьох типів, ось ще кілька типів таблиць фактів:
# 4) Фактичні таблиці фактів: Факт - це сукупність заходів, тоді як факт менше фіксує лише події (або) умови, які не містять жодних заходів. Безфактова таблиця фактів в основному використовується для відстеження системи. Дані в цих таблицях можна аналізувати та використовувати для складання звітності.
Наприклад, ви можете шукати деталі працівника, який взяв відпустку, та тип відпустки за рік тощо. Включаючи всі ці неясні подробиці фактів у факт, таблиця точно збільшить розмір фактів.
Приклад таблиці фактичних фактів наведено нижче.
# 5) Відповідні таблиці фактів: Конформний факт - це факт, на який можна посилатися однаково з кожною інформацією, з якою він пов'язаний.
Технічні характеристики таблиці фактів
Нижче наведені характеристики таблиці фактів.
- Назва факту: Це рядок, який коротко описує функціональність таблиці фактів.
- Бізнес-процес: Розмови про бізнес повинні виконуватися за цією таблицею фактів.
- Запитання: Згадується перелік ділових питань, на які відповість ця таблиця фактів.
- Зерно: Позначає найнижчий рівень деталізації, пов'язаний з даними цієї таблиці фактів.
- Розміри: Перелічіть усі таблиці розмірностей, пов’язані з цією таблицею фактів.
- Заходи: Розраховані значення зберігаються в таблиці фактів.
- Частота навантаження Представляє інтервали часу для завантаження даних у таблицю фактів.
- Початкові рядки: Зверніться до вихідних даних, вперше заповнених у таблиці фактів.
Приклад розмірного моделювання даних
Ви можете отримати уявлення про те, як таблиці розмірів і таблиці фактів можуть бути розроблені для системи, переглянувши наведену нижче діаграму моделювання розмірних даних для продажів та замовлень.
Висновок
На сьогоднішній день ви мали б отримати прекрасні знання про методи розмірного моделювання даних, їх переваги, міфи, таблиці розмірів, таблиці фактів, а також їх типи та процеси.
Перегляньте наш майбутній підручник, щоб дізнатись більше про схеми сховища даних !!
=> Відвідайте тут, щоб навчитися зберігання даних з нуля.
Рекомендована література
- Підручник з тестування сховища даних із прикладами Посібник з тестування ETL
- Приклади інтелектуального аналізу даних: Найпоширеніші програми інтелектуального аналізу даних 2021
- Підручник із прикладами Python DateTime
- Основи зберігання даних: остаточний посібник із прикладами
- Підручник з об'ємного тестування: Приклади та інструменти об'ємного тестування
- Топ-10 популярних засобів зберігання даних та технології тестування
- Видобуток даних: процес, методи та основні проблеми аналізу даних
- Як виконувати тестування на основі даних у SoapUI Pro - Підручник SoapUI No14