Створення модель даних з ефективним використанням пам'яті за допомогою програми Excel і надбудови Power Pivot

Два приклади стовпців, які слід завжди виключати

Перший приклад стосується даних, які походять зі сховища даних. У сховищі даних часто зустрічаються артефакти процесів ETL, які завантажуються та оновлюють дані на складі. Стовпці, наприклад "дата створення", "дата оновлення" та "запуск ETL", створюються під час завантаження даних. Жоден із цих стовпців не потрібен у моделі, і його слід скасувати під час імпорту даних.

У другому прикладі потрібно пропустити стовпець первинного ключа під час імпорту таблиці фактів.

Багато таблиць, включно з таблицями фактів, мають первинні ключі. Для більшості таблиць, наприклад тих, які містять дані про клієнта, працівника або дані про збут, потрібно використовувати первинний ключ таблиці, щоб використовувати її для створення зв'язків у моделі.

Таблиці фактів різні. У таблиці фактів первинний ключ використовується для унікальної ідентифікації кожного рядка. Хоча це необхідно для нормалізації, вона менш корисна в моделі даних, де потрібно використовувати лише стовпці для аналізу або встановлення зв'язків між таблицями. Тому під час імпорту з таблиці фактів не включайте первинний ключ. Первинні ключі в таблиці фактів споживають величезну кількість простору в моделі, але не надають ніякої користі, оскільки їх не можна використовувати для створення зв'язків.

Виключення непотрібних стовпців

Ефективні моделі містять лише ті стовпці, які насправді знадобляться в книзі. Щоб керувати тим, які стовпці включено до моделі, потрібно скористатися майстром імпорту таблиць у надбудові Power Pivot, щоб імпортувати дані , а не діалогове вікно "Імпорт даних" у програмі Excel.

Коли ви запускаєте майстер імпорту таблиць, виберіть таблиці, які потрібно імпортувати.

Для кожної таблиці можна натиснути кнопку Попередній перегляд & Фільтр і вибрати потрібні частини таблиці. Радимо спочатку зняти прапорці для всіх стовпців, а потім продовжити перевірку потрібних стовпців, обміркуючи, чи потрібні вони для аналізу.

Змінення стовпців дати й часу

У багатьох випадках стовпці Datetime займають багато місця. На щастя, існує кілька способів зменшити вимоги до сховища для цього типу даних. Методи залежать від способу використання стовпця та рівня комфорту в побудові запитів SQL.

Стовпці дати й часу містять частину дати та час. Коли ви запитаєте себе, чи потрібен вам стовпець, поставте те саме запитання кілька разів для стовпця Datetime:

• Чи потрібна часова частина?

• Чи потрібна частина часу на рівні годин? Хвилин? Секунд? Мілісекундах?

• Чи є в мене кілька стовпців Дати й часу, тому що потрібно обчислити різницю між ними або просто агрегувати дані за роком, місяцем, кварталом тощо.

Спосіб відповіді на кожне з цих запитань визначає варіанти роботи зі стовпцем Datetime.

Усі ці рішення потребують змінення запиту SQL. Щоб спростити змінення запиту, слід відфільтрувати принаймні один стовпець у кожній таблиці. Відфільтрувавши стовпець, ви змінюєте побудову запитів із скороченого формату (SELECT *) на інструкцію SELECT, яка містить повні імена стовпців, які значно легше змінити.

Розгляньмо створені запити. У діалоговому вікні Властивості таблиці можна перейти до редактора запитів і переглянути поточний запит SQL для кожної таблиці.

У вікні Властивості таблиці виберіть Редактор Power Query.

На Редактор Power Query показано запит SQL, який використовується для заповнення таблиці. Якщо під час імпорту відфільтровано будь-який стовпець, запит міститиме повні імена стовпців:

Змінення запиту SQL

Тепер, коли ви знаєте, як знайти запит, ви можете змінити його, щоб додатково зменшити розмір моделі.

1. Якщо стовпці з грошовими або десятковими даними не потрібні, використовуйте цей синтаксис, щоб позбутися десяткових знаків:

   "SELECT ROUND([Decimal_column_name],0)... .”

   Якщо вам потрібні центи, але не дроби центів, замініть 0 на 2. Якщо використовуються від'ємні числа, можна округлити до одиниць, десятків, сотень тощо.

2. Якщо у вас є стовпець Datetime з іменем dbo. Великий стіл. [Дата й час] і вам не потрібна частина Time, використовуйте синтаксис, щоб позбутися часу:

   "SELECT CAST (dbo. Великий стіл. [Date time] as date) AS [Date time]) "

3. Якщо у вас є стовпець Datetime з іменем dbo. Великий стіл. [Дата й час] і вам потрібні частини Дати й Часу, використовуйте кілька стовпців у запиті SQL замість одного стовпця Datetime:

   "SELECT CAST (dbo. Великий стіл. [Дата й час] як дата ) AS [Дата й час],

   datepart(hh, dbo. Великий стіл. [Дата й час]) як [Дата й час],

   datepart(mi, dbo. Великий стіл. [Дата й час]) як [Дата й час у хвилинах],

   datepart(ss, dbo. Великий стіл. [Дата й час]) як [Дата й час у секундах],

   datepart(ms, dbo. Великий стіл. [Дата й час]) as [Date Time Milliseconds]"

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

4. Якщо вам потрібні години та хвилини, а ви віддаєте перевагу їм разом як одному стовпцю часу, можна використовувати синтаксис:

   Timefromparts(datepart(hh, dbo. Великий стіл. [Дата_часу]), datepart(mm; dbo. Великий стіл. [Дата й час])) as [Date Time HourMinute]

5. Якщо у вас є два стовпці дати й часу, наприклад [Час початку] та [Час завершення], і вам дійсно потрібна різниця в часі між ними в секундах як стовпець [Тривалість], видаліть обидва стовпці зі списку та додайте:

   "datediff(ss;[Дата початку],[Дата завершення]) як [Тривалість]"

   Якщо замість ss використовувати ключове слово мс, тривалість відображатиметься в мілісекундах.

Які 2 стовпці слід зберегти?

У наведеному вище прикладі залиште значення "Кількість" і "Ціна за одиницю". Ці два значення менші за загальний. Щоб обчислити підсумок, додайте обчислюваний показник, наприклад:

"TotalSales:=sumx('Sales Table','Sales Table'[Ціна за одиницю]*'Таблиця збуту'[Кількість])"

Обчислювані стовпці схожі на звичайні стовпці, які займають місце в моделі. На відміну від цього, обчислювані показники обчислюються на льоту та не займають місця.