10 способів покращити продуктивність Power Pivot

Публікуючи звіти Power Pivot в Інтернеті, ви маєте намір надати своїй аудиторії найкращий досвід. Значна частина цього досвіду полягає в тому, щоб забезпечити хорошу продуктивність. Слово продуктивність (якщо воно стосується програм і звітів) зазвичай є синонімом швидкості — або того, наскільки швидко програма виконує певні дії, такі як відкриття у браузері, виконання запитів або фільтрація.

1 Обмежте кількість рядків і стовпців у таблицях моделі даних.

Один величезний вплив на продуктивність Power Pivot — кількість стовпців, які ви вносите або імпортуєте в модель даних. Кожен імпортований стовпець — це ще один вимір, який Power Pivot має обробити під час завантаження книги. Не імпортуйте додаткові стовпці «про всяк випадок» — якщо ви не впевнені, що будете використовувати певні стовпці, просто не вводьте їх. Ці стовпці досить легко додати пізніше, якщо ви виявите, що вони вам потрібні.

Більше рядків означає більше даних для завантаження, більше даних для фільтрації та більше даних для обчислення. Уникайте вибору всієї таблиці, якщо вам цього не потрібно. Використовуйте запит або подання у вихідній базі даних, щоб відфільтрувати лише ті рядки, які потрібно імпортувати. Зрештою, навіщо імпортувати 400 000 рядків даних, якщо ви можете використовувати просту фразу WHERE і імпортувати лише 100 000?

2 Використовуйте перегляди замість таблиць.

Говорячи про перегляди, для найкращої практики використовуйте представлення, коли це можливо.

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

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

3 Уникайте багаторівневих відносин.

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

Уникайте створення моделей, де таблиці розмірів пов’язані з іншими таблицями розмірів.

4 Дозвольте серверам баз даних зробити хрускіт.

Більшість аналітиків Excel, які тільки почали працювати з Power Pivot, зазвичай витягують необроблені дані безпосередньо з таблиць на своїх зовнішніх серверах баз даних. Після того, як вихідні дані перебувають у Power Pivot, вони створюють обчислювані стовпці та заходи для перетворення й укрупнення даних за потреби. Наприклад, користувачі зазвичай отримують дані про доходи та витрати, а потім створюють обчислюваний стовпець у Power Pivot для обчислення прибутку.

Тож навіщо змушувати Power Pivot робити цей розрахунок, якщо серверний сервер міг би впоратися з цим? Реальність така, що серверні системи баз даних, такі як SQL Server, мають здатність формувати, агрегувати, очищати та трансформувати дані набагато ефективніше, ніж Power Pivot. Чому б не використати їхні потужні можливості для обробки та формування даних, перш ніж імпортувати їх у Power Pivot?

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

5 Остерігайтеся стовпців з нерозрізненими значеннями.

Стовпці, які мають велику кількість унікальних значень, особливо впливають на продуктивність Power Pivot. Такі стовпці, як ідентифікатор транзакції, ідентифікатор замовлення та номер рахунка-фактури, часто не потрібні у високорівневих звітах і інформаційних панелях Power Pivot. Тому, якщо вони не потрібні для встановлення зв’язків з іншими таблицями, не включайте їх у свою модель.

6 Обмежте кількість слайсерів у звіті.

Слайсер є однією з найкращих нових функцій бізнес-аналітики (BI) Excel за останні роки. Використовуючи слайсери, ви можете надати своїй аудиторії інтуїтивно зрозумілий інтерфейс, який дозволяє інтерактивно фільтрувати ваші звіти та інформаційні панелі Excel.

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

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

7 Створюйте слайсери лише для полів розмірів.

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

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

8 Вимкніть перехресний фільтр для певних слайсерів.

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

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

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

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

10Оновіть до 64-розрядної версії Excel.

Якщо ви продовжуєте стикатися з проблемами продуктивності зі звітами Power Pivot, ви завжди можете придбати кращий ПК — у цьому випадку оновивши до 64-розрядного ПК із встановленим 64-розрядним Excel.

Power Pivot завантажує всю модель даних в оперативну пам’ять щоразу, коли ви з нею працюєте. Чим більше оперативної пам’яті на вашому комп’ютері, тим менше проблем з продуктивністю ви бачите. 64-розрядна версія Excel може отримати доступ до більшої кількості оперативної пам’яті вашого ПК, гарантуючи наявність системних ресурсів, необхідних для роботи з більшими моделями даних. Насправді, Microsoft рекомендує 64-розрядний Excel для тих, хто працює з моделями, що складаються з мільйонів рядків.

Але перш ніж поспішно розпочати інсталяцію 64-розрядного Excel, вам потрібно відповісти на ці запитання:

У вас уже встановлено 64-розрядний Excel?

Чи достатньо великі ваші моделі даних?

Чи встановлено на вашому ПК 64-розрядну операційну систему?

Чи перестануть працювати інші ваші надбудови?