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

Увага! : Цю статтю перекладено за допомогою служби машинного перекладу; див. застереження. Версію цієї статті англійською мовою див. тут для отримання довідки.

У програмі Excel 2013 або новішої версії, можна створювати моделі даних, що містить мільйони рядків та виконайте потужний аналіз проти ці моделі. Моделі даних, можна створити з або без Power Pivot надбудови для підтримки будь-яку кількість зведених таблиць, діаграм і графічних відображень Power View в тій самій книзі.

Примітка : У цій статті описано моделі даних у програмі Excel 2013. Проте, однакових даних моделювання та функції Power Pivot у програмі Excel 2013 також застосовуються до Excel 2016. Існує ефективно маленький різницю між цими версіях програми Excel.

Хоча ви можете легко побудувати моделей величезну даних у програмі Excel, є кілька причин не до. По-перше, великі моделі, які містять безліч таблиць і стовпців буде зайвим для більшості аналізу та зробити довгим список полів. Другий, великі моделі за допомогою цінний пам'ять, негативно впливає на інші програми та звіти, які спільно використовують ті самі системні ресурси. Нарешті, у службі Office 365, служби SharePoint Online і веб-застосунку Excel Web App обмеження розміру файлу Excel до 10 МБ. Для книги моделі даних, які містять мільйони рядків які виникнуть обмеження в 10 МБ досить швидко. Переглянути специфікація й обмеження моделі даних.

У цій статті йдеться про те, як створювати моделі зі стислою структурою, з якими легше працювати та які потребують менших затрат пам’яті. Не пошкодуйте часу, щоб навчитися проектувати ефективні моделі якнайкраще, і ви гарантовано підвищите їх якість та зручність використання незалежно від засобу перегляду (програми Excel 2013, служби SharePoint Online у пакеті Office 365 на сервері Office Web Apps чи служби SharePoint 2013).

Радимо також працює засіб оптимізації розміру книги. Він аналізує книги Excel і, якщо це можливо, стискає далі. Завантаження засобу оптимізації розміру книги.

У цій статті

Ступені стискання та ядро аналітики в пам'яті

Виключення стовпців – немає проблеми

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

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

Фільтрація лише потрібних рядків?

Що робити, якщо потрібно стовпця; чи ми зменшення розміру?

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

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

За допомогою мови DAX обчислюваних елементів замість стовпців

Які два стовпці слід залишити?

Висновок

Пов'язані посилання

Ступені стискання та ядро аналітики в пам’яті

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

У середньому за допомогою такого стискання модель даних набуває розміру в 7-10 разів меншого, ніж вихідні дані. Наприклад, під час імпорту 7 МБ даних із бази даних SQL Server модель даних у програмі Excel досягатиме заледве 1 МБ або менше. Ступінь досягнутого стискання залежить у значній мірі від унікальних значень у кожному стовпці. Чим більше унікальних значень, тим більше пам’яті необхідно для їх збереження.

Власне, чому ми піднімаємо питання про стискання та унікальні значення? Суть у тому, що побудова ефективної моделі, яка мінімізує використання пам’яті, полягає в досягненні максимального стискання, а найлегший спосіб зробити це – позбутися непотрібних стовпців, особливо тих, які містять велику кількість унікальних значень.

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

Виключення стовпців для ефективного використання пам’яті

Перш за все потрібно запам’ятати, що найкращий спосіб зберегти пам’ять під час імпорту – позбутися непотрібних стовпців. Якщо потрібно побудувати ефективну модель, огляньте кожен стовпець і подумайте, чи дійсно він вагомий для запланованого аналізу. Непотрібні стовпці та ті, щодо важливості яких ви сумніваєтеся, слід виключити. Якщо знадобиться, нові стовпці завжди можна додати згодом.

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

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

Другий тип – це стовпці первинного ключа, які слід пропускати, імпортуючи таблиці фактів.

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

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

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

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

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

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

Майстер імпорту таблиць надбудови PowerPivot

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

Область попереднього перегляду майстра імпорту таблиць

Фільтрація лише потрібних рядків

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

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

Область фільтра майстра імпорту таблиць

Зменшення розміру потрібного стовпця

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

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

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

Стовпці дати й часу складаються з двох частин (дати та часу). Коли ви вирішуєте, чи потрібен вам стовпець, задайтесь тим самим питанням щодо стовпця дати й часу. Подумайте:

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

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

  • У мене кілька стовпців дати й часу, тому що потрібно обчислити різницю між ними, чи необхідно лише звести дані за рік, місяць, квартал тощо?

Дії зі стовпцем дати й часу залежать від того, як ви відповісте на ці запитання.

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

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

стрічка у вікні надбудови powerpivot, де відображено команду ''властивості таблиці''

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

відкриття редактора запитів у діалоговому вікні ''редагування властивостей таблиці''

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

запит sql для завантаження даних

У свою чергу, якщо імпортувати таблицю цілісно, без зняття прапорців стовпців або використання фільтра, запит буде важче змінювати, оскільки він відображатиметься таким чином: "Select * from ".

запит sql за замовчуванням, який використовує стислий синтаксис

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

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

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

    “SELECT ROUND([Ім’я_стовпця_з_десятковими_дробами],0)… .”

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

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

    “SELECT CAST (dbo.Bigtable.[Дата й час] as date) AS [Дата й час]) “

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

    “SELECT CAST (dbo.Bigtable.[Дата й час] as date ) AS [Дата й час],

    datepart(hh, dbo.Bigtable.[Дата й час]) as [Дата й час у годинах],

    datepart(mi, dbo.Bigtable.[Дата й час]) as [Дата й час у хвилинах],

    datepart(ss, dbo.Bigtable.[Дата й час]) as [Дата й час у секундах],

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

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

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

    Timefromparts(datepart(hh, dbo.Bigtable.[Дата й час]), datepart(mm, dbo.Bigtable.[Дата й час])) as [Дата й час у годинах і хвилинах]

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

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

    У разі використання ключового слова "ms" замість "ss", отриманий результат відображатиметься в мілісекундах

Використання обчислюваних елементів DAX замість стовпців

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

Ще один метод збереження пам’яті полягає в заміні звичайних або обчислюваних стовпців обчислюваними елементами. Типовим прикладом можна вважати співвідношення даних стовпців "Вартість одиниці", "Кількість" та "Підсумок". Якщо наявні всі три, можна зберегти місце, використовуючи лише два та обчислюючи третій за допомогою елемента виразу DAX.

Які два стовпці слід залишити?

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

"TotalSales:=sumx(‘Sales Table’;’Sales Table’[Ціна одиниці]*’Sales Table’[Кількість])"

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

Висновки

У цій статті мова йшла про кілька способів, за допомогою яких можна побудувати моделі даних з ефективним використанням пам’яті. Єдиний спосіб зменшити розмір файлу та вимоги до пам’яті моделі даних – це зменшити загальну кількість стовпців, рядків і унікальних значень у кожному стовпці. Ось кілька методів це зробити:

  • Звичайно, накращий спосіб зберегти місце – видалити непотрібні стовпці. Вирішіть, які стовпці дійсно потрібні.

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

  • Не всі рядки таблиці потрібні. Можна відфільтрувати рядки за допомогою майстра імпорту таблиць.

  • Загалом розділяти стовпець на кілька окремих частин – це хороший спосіб зменшити кількість унікальних значень у стовпці. Кожна частина міститиме малу кількість унікальних значень, а об’єднане ціле займатиме менше місця ніж вихідний стовпець.

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

  • Часто стовпці містять більше відомостей, ніж потрібно. Наприклад, стовпець може містити десяткові дроби, а ви використали форматування, щоб їх приховати. Закруглення може виявитись ефективним способом зменшити розмір числових стовпців.

Тепер, коли ви зробили, ви можете зменшити розмір книги, радимо також працює засіб оптимізації розміру книги. Він аналізує книги Excel і, якщо це можливо, стискає далі. Завантаження засобу оптимізації розміру книги.

Пов’язані посилання

Специфікація й обмеження моделі даних

Завантаження Засобу оптимізації розміру книги

Power Pivot: Ефективний аналіз і моделювання даних у програмі Excel

Примітка : Застереження про машинний переклад: Цю статтю перекладено комп’ютерною системою без втручання людини. Корпорація Майкрософт пропонує таку послугу, щоб іншомовні користувачі могли дізнаватися про продукти, служби й технології Microsoft. Оскільки статтю перекладено за допомогою служби машинного перекладу, вона може містити смислові, синтаксичні або граматичні помилки.

Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×