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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

   datepart(hh, dbo. "Велике та нижче". [Дата й час]) as [Дата, час, години],

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

   datepart(ss;dbo. "Велике та нижче". [Дата й час]) as [Дата в секундах],

   datepart(ms, dbo. "Велике та нижче". [Дата й час]) as [Дата, час у мілісекундах]"

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

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

   Timefromparts(datepart(hh, dbo. "Велике та нижче". [Дата й час]), datepart(mm, dbo. "Велике та нижче". [Дата й час])) as [Дата й час ГодинаМінут]

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

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

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

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

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

"TotalSales:=sumx('Sales Table','Sales Table'[Unit Price]*'Sales Table'[Quantity])"

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