У програмі Excel можна створювати моделі даних, які містять мільйони рядків, а потім виконувати ефективний аналіз даних за цими моделями. Моделі даних можна створювати за допомогою надбудови Power Pivot або без неї, щоб підтримувати будь-яку кількість зведених таблиць, діаграм і візуалізацій Power View в одній книзі.
Хоча в Excel можна легко створити величезні моделі даних, є кілька причин цього не робити. По-перше, великі моделі, які містять безліч таблиць і стовпців, є надмірними для більшості аналізів і створюють громіздкий список полів. По-друге, великі моделі споживають цінну пам'ять, що негативно впливає на інші програми та звіти, які використовують ті ж системні ресурси. Нарешті, у Microsoft 365 і SharePoint Online, і Excel Web App обмежують розмір файлу Excel до 10 МБ. У моделях даних книг із мільйонами рядків можна доволі швидко досягти обмеження в 10 МБ. Див. специфікацію й обмеження моделі даних.
У цій статті ви дізнаєтеся, як створити модель із щільною структурою, з якою легше працювати та який потребує менше пам'яті. Якщо ви витратите час на вивчення передового досвіду в ефективному проектуванні моделей, це окупиться в майбутньому для будь-якої моделі, яку ви створюєте та використовуєте незалежно від того, чи переглядаєте ви її в Excel, Microsoft 365 SharePoint Online, на сервері Office Web Apps Server або в SharePoint.
Також рекомендуємо запустити засіб оптимізації розміру книги. Він проаналізує вашу книгу Excel і за можливості стисне її ще більше. Завантажте засіб оптимізації розміру книги.
У цій статті
Ніщо не зрівняється з неіснуючим стовпцем для низького обсягу використання пам'яті
Що робити, якщо нам потрібна колонка; Чи можемо ми все ж таки зменшити витрати на його простір?
Ступінь стиснення та механізм аналітики в пам'яті
Щоб зберігати дані, моделі даних в Excel використовують модуль аналітики в пам'яті. У двигуні реалізовані потужні методи стиснення, щоб зменшити вимоги до зберігання, зменшуючи набір результатів, поки він не стане часткою від початкового розміру.
У середньому модель даних може бути в 7–10 разів менша за ті самі дані в точці виходу. Наприклад, якщо ви імпортуєте 7 МБ даних із бази даних SQL Server, модель даних в Excel може легко мати розмір не більше 1 МБ. Досягнутий ступінь стискання залежить, перш за все, від кількості унікальних значень у кожному стовпці. Чим більше унікальних значень, тим більше пам'яті потрібно для їх зберігання.
Чому ми говоримо про стиснення і унікальні значення? Тому що побудувати ефективну модель, яка мінімізує використання пам'яті, потрібно максимізувати стискання, і найпростіший спосіб зробити це – позбутися тих стовпців, які насправді не потрібні, особливо якщо ці стовпці містять велику кількість унікальних значень.
Примітка.
Відмінності у вимогах до сховища для окремих стовпців можуть бути величезними. У деяких випадках краще мати кілька стовпців із невеликою кількістю унікальних значень, ніж один. У розділі про оптимізацію дати й часу докладніше описано цей метод.
Ніщо не зрівняється з неіснуючим стовпцем для низького обсягу використання пам'яті
Стовпець, який потребує найбільше пам'яті, – це той, який ви взагалі ніколи не імпортували. Якщо потрібно побудувати ефективну модель, погляньте на кожен стовпець і запитайте себе, чи сприяє він аналізу, який ви плануєте провести. Якщо це не так або ви не впевнені, пропустіть цей параметр. Ви завжди можете додати нові стовпці, якщо вони знадобляться.
Два приклади стовпців, які слід завжди виключати
Перший приклад стосується даних, які походять зі сховища даних. У сховищі даних зазвичай можна знайти артефакти процесів ETL, які завантажують і оновлюють дані в сховищі. Стовпці "дата створення", "дата оновлення" та "запуск ETL" створюються під час завантаження даних. Жоден із цих стовпців не потрібен у моделі, тому під час імпорту даних його потрібно скасувати.
У другому прикладі стовпець первинного ключа пропускається, коли імпортується таблиця фактів.
Багато таблиць, зокрема таблиці фактів, містять первинні ключі. Для більшості таблиць, наприклад тих, що містять дані про клієнтів, працівників або продажі, потрібен первинний ключ таблиці, за допомогою якого можна створювати зв'язки в моделі.
Таблиці фактів різні. У таблиці фактів первинний ключ використовується, щоб однозначно ідентифікувати кожен рядок. Хоча ця функція потрібна для цілей нормалізації, вона менш корисна в моделі даних, де для аналізу або встановлення зв'язків між таблицями потрібно використовувати лише ці стовпці. Тому, імпортуючи дані з таблиці фактів, не додавайте її первинний ключ. Первинні ключі в таблиці фактів займають величезну кількість місця в моделі, але не приносять ніякої користі, оскільки їх не можна використовувати для створення зв'язків.
Примітка.
У сховищах даних і багатовимірних базах даних великі таблиці, що складаються переважно з числових даних, часто називають таблицями фактів. Таблиці фактів зазвичай містять дані про ефективність бізнесу або трансакції, наприклад точки даних про збут і витрати, які агрегуються та вирівнюються за організаційними підрозділами, продуктами, сегментами ринку, географічними регіонами тощо. Щоб забезпечити аналіз даних, у модель мають бути включені всі стовпці таблиці фактів, які містять бізнес-дані або які можна використовувати для перехресних посилань на дані, що зберігаються в інших таблицях. Потрібно виключити стовпець первинного ключа в таблиці фактів, що складається з унікальних значень, наявних лише в таблиці фактів і ніде більше. Таблиці фактів дуже великі, тому найбільшого підвищення ефективності моделей можна досягти, виключивши з таблиць фактів рядки та стовпці.
Вилучення непотрібних стовпців
Ефективні моделі містять лише ті стовпці, які насправді знадобляться в книзі. Якщо ви хочете визначати, які стовпці включатимуться до моделі, вам доведеться скористатися майстром імпорту таблиць надбудови Power Pivot для імпорту даних , а не діалоговим вікном "Імпорт даних" в Excel.
Запускаючи майстер імпорту таблиць, можна вибрати таблиці, які потрібно імпортувати.
Для кожної таблиці можна натиснути кнопку Попередній перегляд & Фільтр і вибрати частини таблиці, які вам найбільше потрібні. Ми радимо спочатку зняти прапорці з усіх стовпців, а потім перейти до перевірки потрібних стовпців, вирішивши, чи потрібні вони для аналізу.
Як щодо фільтрування лише потрібних рядків?
Багато таблиць у корпоративних базах даних і сховищах даних містять історичні дані, накопичені за тривалий період часу. Крім того, може виявитися, що таблиці, які вас цікавлять, містять відомості про сфери бізнесу, які не потрібні для конкретного аналізу.
За допомогою майстра імпорту таблиць можна відфільтрувати історичні або непов'язані дані, заощадивши тим самим багато місця в моделі. На зображенні нижче фільтр дат використовується, щоб отримати лише рядки, які містять дані за поточний рік без урахування попередніх даних, які не потрібні.
Що робити, якщо нам потрібна колонка; Чи можемо ми все ж таки зменшити витрати на його простір?
Щоб зробити стовпець кращим кандидатом на стискання, можна застосувати кілька додаткових методів. Пам'ятайте, що єдина характеристика стовпця, яка впливає на стискання, – це кількість унікальних значень. У цьому розділі ви дізнаєтеся, як можна змінити деякі стовпці, щоб зменшити кількість унікальних значень.
Змінення стовпців дати й часу
У багатьох випадках стовпці дати й часу займають багато місця. На щастя, є кілька способів знизити вимоги до сховища для цього типу даних. Методи можуть різнитися залежно від способу використання стовпця та рівня зручності під час створення запитів SQL.
Стовпці дати й часу містять дату, частину й час. Запитуючи себе, чи потрібен стовпець, поставте те саме запитання кілька разів для стовпця DateTime.
- Чи потрібна часова частина?
- Чи потрібен відрізок часу на рівні годин? , хв.? , секунди? , мілісекунди?
- Потрібно мати кілька стовпців DateTime для обчислення різниці між ними, або просто для того, щоб об'єднати дані за роками, місяцями, кварталами тощо.
Від того, як ви відповісте на кожне з цих запитань, залежить, які функції ви матимете під час роботи зі стовпцем "Дата-час".
Усі ці рішення потребують змінення запиту SQL. Щоб полегшити змінення запиту, слід відфільтрувати принаймні один стовпець у кожній таблиці. Відфільтрувавши стовпець, ви змінюєте конструкцію запиту зі скороченого формату (SELECT *) на інструкцію SELECT, що містить повні імена стовпців, які значно простіше змінити.
Погляньмо на те, які запити створюються для вас. У діалоговому вікні "Властивості таблиці" можна перейти до редактора запитів і переглянути поточний запит SQL для кожної таблиці.
У властивостях таблиці виберіть Query EditorРедактор Power Query.
У Редактор Power Query відображається запит SQL, використаний для заповнення таблиці. Якщо під час імпорту ви відфільтрували будь-який стовпець, запит міститиме повні назви стовпців:
На відміну від цього, якщо імпортувати таблицю повністю, не знімаючи позначки з жодного стовпця та не застосовуючи фільтри, відображатиметься запит "Вибрати * з", що буде складніше змінити.
|
|---|
Змінення запиту SQL
Тепер, коли ви знаєте, як знайти запит, його можна змінити, щоб зменшити розмір моделі.
- У стовпцях, які містять грошові або десяткові знаки: якщо десяткові знаки не потрібні, використовуйте цей синтаксис, щоб позбутися десяткових дробів:
"SELECT ROUND([Decimal_column_name],0)... .”
Якщо вам потрібні копійки, але не частки центів, замініть 0 на 2. Якщо використовуються від'ємні числа, можна округлити до одиниць, десятків, сотень тощо. - Якщо у вас є стовпець дати й часу з іменем dbo. Великий. [Date Time] і вам не потрібна частина "Час", щоб позбутися часу, використовуйте синтаксис:
"SELECT CAST (dbo. Великий. [Date, time] as date) AS [Date, time]) " - Якщо у вас є стовпець дати й часу з іменем dbo. Великий. [Дата й час] Якщо потрібні обидві частини "Дата" й "Час", використовуйте в запиті SQL кілька стовпців замість одного стовпця "Дата_Час":
"SELECT CAST (dbo. Великий. [Date Time] as date ) AS [Date Time],
DatePart (hh;dbo. Великий. [Дата й час]) AS [Дата, час, години],
DatePart(mi;dbo. Великий. [Дата й час]) AS [Дата, час, хвилини],
DatePart(ss, dbo. Великий. [Дата й час]) AS [Дата, час, секунди],
DatePart (ms; dbo. Великий. [Дата й час]) As [Дата, час, мілісекунди]",
Використовуйте стільки стовпців, скільки потрібно для зберігання кожної частини в окремих стовпцях. - Якщо вам потрібні години й хвилини та ви хочете, щоб вони відображалися разом в одному стовпці часу, можна скористатися синтаксисом:
Timefromparts(datepart(hh; dbo. Великий. [Date Time]), datepart(mm; dbo. Великий. [Дата, час])) AS [Дата, час, година_хвилина] - Якщо у вас є два стовпці дати й часу, наприклад [Час початку] та [Час завершення], і вам дійсно потрібна різниця в часі між ними в секундах у вигляді стовпця [Тривалість], вилучіть обидва стовпці зі списку та додайте:
"datediff(ss,[Дата початку],[Дата завершення]) AS [Тривалість]"
Якщо замість ss використати ключове слово ms, тривалість обчислюється в мілісекундах
Використання обчислюваних мір DAX замість стовпців
Якщо ви раніше працювали з мовою виразів DAX, вам, можливо, уже відомо, що обчислювані стовпці використовуються, щоб отримувати нові стовпці на основі іншого стовпця в моделі, тоді як обчислювані міри визначаються в моделі один раз, але обчислюються лише тоді, коли використовуються у зведеній таблиці або іншому звіті.
Один із методів заощадження пам'яті – це заміна звичайних або обчислюваних стовпців обчислюваними показниками. Класичний приклад: "Ціна одиниці товару", "Кількість" і "Підсумок". Якщо у вас є всі три моделі, можна заощадити місце, залишивши лише два й обчисливши третє за допомогою мови DAX.
Які 2 стовпці потрібно залишити?
У наведеному вище прикладі залиште поля "Кількість" і "Ціна за одиницю". Ці два значення менші, ніж загальний підсумок. Щоб обчислити підсумок, додайте обчислювану міру, як-от:
"TotalSales:=sumx('Таблиця збуту','Таблиця продажів'[Ціна за одиницю]*'Таблиця продажів'[Кількість])"
Обчислювані стовпці схожі на звичайні стовпці, оскільки вони займають місце в моделі. На відміну від них, розраховані міри обчислюються на льоту і не займають місця.
Висновки
У цій статті ми розглянули кілька підходів, які можуть допомогти вам побудувати модель з ефективним використанням пам'яті. Щоб зменшити розмір файлу та вимоги до пам'яті моделі даних, зменште загальну кількість стовпців і рядків, а також кількість унікальних значень, що відображаються в кожному стовпці. Ось деякі методи, які ми розглянули:
- Звичайно, видалення стовпців – це найкращий спосіб заощадити простір. Вирішіть, які стовпці вам дійсно потрібні.
- Іноді можна видалити стовпець і замінити його на обчислюваний показник у таблиці.
- Можливо, не всі рядки в таблиці знадобляться. У майстрі імпорту таблиць рядки можна відфільтрувати.
- Загалом, щоб зменшити кількість унікальних значень у стовпці, можна розбити один стовпець на кілька окремих частин. Кожна частина матиме невелику кількість унікальних значень, а загальний підсумок буде меншим за вихідний об'єднаний стовпець.
- У багатьох випадках також потрібно, щоб окремі частини використовувалися як роздільники у звітах. За потреби можна створити ієрархії з таких частин, як "Години", "Хвилини" та "Секунди".
- Часто стовпці містять більше інформації, ніж потрібно. Наприклад, у стовпці зберігаються десяткові знаки, але застосовано форматування, щоб приховати всі десяткові знаки. Округлення дуже ефективно зменшує розмір числового стовпця.
Тепер, коли ви зробили все можливе, щоб зменшити розмір книги, радимо також запустити засіб оптимізації розміру книги. Він проаналізує вашу книгу Excel і за можливості стисне її ще більше. Завантажте засіб оптимізації розміру книги.
Пов’язані посилання
Специфікація й обмеження моделі даних
Засіб оптимізації розміру книги
Надбудова Power Pivot: ефективний аналіз і моделювання даних у програмі Excel