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

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

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

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

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

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

У цій статті

Коефіцієнти стиснення та обробник аналітики в пам'яті

Ніщо не може зрівнятися з неіснуючим стовпцем для використання в низькій пам'яті

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

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

Що робити, щоб фільтрувати тільки необхідні рядки?

Що робити, якщо потрібно, щоб стовпець; Чи можемо ми ще знизити вартість простору?

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

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

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

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

Висновки

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

Коефіцієнти стиснення та обробник аналітики в пам'яті

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

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

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

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

Ніщо не може зрівнятися з неіснуючим стовпцем для використання в низькій пам'яті

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

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

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

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

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

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

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

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

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

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

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

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

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

Що робити, щоб фільтрувати тільки необхідні рядки?

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

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

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

Що робити, якщо потрібно, щоб стовпець; Чи можемо ми ще знизити вартість простору?

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

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

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

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

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

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

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

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

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

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

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

На основі властивостей таблиці виберіть елемент редактор запитів.

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

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

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

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

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

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

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

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

    "SELECT ROUND ([Decimal_column_name]; 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 (гг, DBO. Bigtable. [Дата й час]) as [Дата й час];

    DatePart (Mi, DBO. Bigtable. [Дата й час]) as [Дата й час хвилин];

    DatePart (SS, DBO. Bigtable. [Дата й час]) як [Дата часу секунди];

    DatePart (MS, DBO. Bigtable. [Дата й час]) як [Дата часу мілісекунди] "

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

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

    Часові блоки (DatePart (гг, DBO). Bigtable. [Дата й час]), DatePart (mm, DBO. Bigtable. [Дата й час])) as [Дата й час HourMinute]

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

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

    Якщо ви використовуєте ключове слово MS замість СС, ви отримаєте тривалість у мілісекундах.

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

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

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

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

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

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

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

Висновки

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

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

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

  • Можливо, не потрібно, щоб усі рядки були в таблиці. Ви можете відфільтрувати рядки в майстрі імпорту таблиць.

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

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

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

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

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

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

Завантаження оптимізатора розмір книги

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

Потрібна додаткова довідка?

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

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

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

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

×