POWER BI: МОДЕЛЮВАННЯ ДАНИХ

Power BI Desktop: організація інтерфейсу

Три основних інструментарії: 1) підключення та очищення; 2) моделювання; 3) візуалізація

Power Query:

• Огляд роботи у вікні Power Query
• Отримання даних про продажі з бази даних компанії
• Зв'язки з іншими таблицями (об'єкти Table і Value)
• Робота зі стовпцями: видалення, перейменування, формат даних і інші
• Умовні і обчислювані стовпці. Формули і конструкції мови "M"

DAX:

• Створення базових заходів - суми продажів в грошах і штуках. агрегатори

Visuals:

• Як працюють формули DAX у візуалізації

Оригінальний контекст фільтрів: координати - фільтрація - обчислення

Power Query:

• Підключення до таблиць клієнтів і географії. Текстові операції
• Таблиця "Календар" - назви днів тижня та інші
• Мова "M" - формули, функції, вирази. Функції перетворення типів
• Генерація стовпців сортування
• Підключення до файлу Excel, додавання в модель геоданих
• Об'єднання і злиття. Типи об'єднань

Visuals:

• Динаміка продажів. Вирішення питань з сортуванням

Таблиця «Календар»: вбудована і власна - переваги і недоліки

Power Query:

• Отримання таблиць продуктової "гілки", співробітників компанії
• Підключення до .csv - файлу - ціни закупівель
• Транспонування, заповнення, об'єднання стовпців, скасування згортання і ін.
• Стовпець [Index] і заповнення вгору/вниз
• Використання різних трюків для отримання потрібного результату

Model:

• Таблиці, ключі, зв'язку між таблицями і типи зв'язків
• Таблиці - дані (факти) і таблиці - вимірювання (довідники)
• Схема "зірка" і схема "сніжинка"

DAX:

• Функції - агрегатори. Аргументи. Як працюють
• COUNT і COUNTROWS - кількість клієнтів і кількість продуктів
• DISTINCTCOUNT - кількість відвантажених товарів і кількість відвантажених клієнтів

Visuals:

• Кількість відвантажених: продуктів по клієнтам і клієнтів по продуктам

Контекст фільтрів: передача між таблицями

Power Query:

• Підключення до зарплатних відомостей. Структура зарплат менеджерів з продажу
• Коли автоматично згенеровані кроки не підходять
• Підготовка стовпців для зв'язування з іншими таблицями в моделі
• Складні умови фільтрації
• Підключення до папки з файлами - системою планування відділу продажів
• Трюки з використанням функцій мови "M" для отримання контенту з робочих аркушів Excel

Model:

• Реорганізація таблиць. Створення зв'язків між таблицями

DAX:

• Створення заходів: зарплати менеджерів, оклад і бонусна частина
• Використання "швидких заходів": "за" і "проти"
• Середня, мінімальна і максимальна ціни закупівель. Агрегатори AVERAGE, MIN, MAX
• Часткові показники: % бонусної частини і інші. DIVIDE vs IFERROR, функція BLANK

Visuals:

• Тестування створених заходів: матриця, гістограма, зріз
• Розуміння "несподіваних" результатів на цьому етапі моделювання

Ще раз про зв'язки між таблицями і передачі контексту фільтрів

Power Query:

• Підключення до папки з файлами - прайс-листами компанії
• Реорганізація запитів по папках. Папка зі службовими запитами

Model:

• Організація таблиці цін. Її зв'язки

DAX:

• Контекст рядків і функції - ітератори
• Відсутність автоматичної передачі контексту рядків між таблицями
• Продажі з ПДВ, середня ціна відвантаження та інші: SUMX, AVERAGEX
• Менеджери, що залишилися без бонусу, з бонусом >30% в загальній ЗП: COUNTX, IF
• Мінімальний і максимальний бонус: MINX, MAXX
• Форми власності клієнтів у менеджерів: CONCATENATEX
• Табличні функції. Перше знайомство з VALUES і FILTER
• Візуалізація таблиці - результату роботи табличній функції
• Продажі з ціною відвантаження вище середньої

Visuals:

• Тестуємо створені заходи у візуалізації

Існування і взаємодія оригінального контексту фільтрів і контекстів рядків у формулі

Схожість результатів агрегаторів і ітераторів: коли використовувати ті чи інші?

Технічний і логічний погляд на модель даних

Денормалізація моделі: "за" і "проти"

DAX:

• Групи форм власності, типи днів тижня та інші: два варіанти SWITCH
• Використання текстових функцій і функцій дат: TRIM, DAY та інші
• Логічні функції OR, AND і логічні операції ||, &&
• Передача контексту рядків за допомогою RELATED і RELATEDTABLE. У чому між ними різниця?
• Заходи: максимальний і актуальний прайс-листи - RELATEDTABLE, FILTER, MAXX

Model:

• Створення нового макету "моделі даних". Денормалізована модель даних
• Приховати технічні і службові таблиці, поля в поданні звітів
• Використання і створення ієрархій: "Продукти", "Клієнти", "Географія"

Visuals:

• Тестування створених заходів в візуалізації
• Використання ієрархій для деталізації (drill-up, drill-down)

"Обчислювані стовпці" vs "заходи". Коли і як обчислюються

Питання оптимізації: обчислювані стовпці в моделі або в Power Query?

Модифікація оригінального контексту фільтрів. Функція CALCULATE. Два типи фільтрів

DAX:

• Перший тип фільтрів в CALCULATE: стовпець - операція порівняння
• CALCULATE на одній таблиці: к-ть клієнтів - ФОП, сума повернень,% повернень в обороті
• CALCULATE на декількох таблицях: % типів клієнтів в обороті, втрачені клієнти
• Другий тип фільтрів в CALCULATE: фізична таблиця або таблична функція
• % продукту в обороті,% корпорацій і інші: ALL, VALUES, FILTER в якості фільтрів
• % клієнтів, які купили бренд # 1: функція VALUES, її особливості та використання
• Особливий випадок VALUES: результат - одне значення. HASONEVALUE і SELECTEDVALUE
• Клієнти, що відвантажались тільки один раз і клієнти, які придбали тільки один продукт
• LOOKUPVALUE: випадок, коли вона знадобиться
• Особливий випадок CALCULATE: без аргументів - фільтрів. Неявна CALCULATE в заходах
• Використання CALCULATE в обчислюваних шпальтах і функціях - Ітератор

Visuals:

• Тестування створених заходів в візуалізації. Інтерпретація результатів

Перетворення контексту рядків в контекст фільтрів

DAX:

• Заходи - % від тотал (долі клієнтів, продуктів в загальному обороті): ALL з аргументом "таблиця"
• ALL з аргументом "стовпець"/"стовпці". ALL vs VALUES: схожість і відмінності
• Використання "швидких заходів" для створення заходів - % від тотал
• % форм власності клієнтів в загальному обороті. Відмінність результатів ALL ("таблиця") від ALL ("стовпці") або ALLEXCEPT ("таблиця", "стовпчики")
• Використання ISFILTERED і ISCROSSFILTERED для управління обчисленнями в мірах
• Створення заходів - % по батьківському елементу в ієрархії
• % в обороті тільки відібраних клієнтів: функція ALLSELECTED, особливості роботи

Visuals:

• Тестування створених заходів і інтерпретація результатів з ALL ("таблиця") і ALL ("стовпці")
• Поведінка ALLSELECTED при фільтрації на діаграмі, зрізі і панелі фільтрів

DAX:

• Про FILTER і її аргументах більш докладно
• Використання FILTER в CALCULATE і поза нею. Прості і складні умови без заходів
• Використання заходів в умові FILTER. Неявна і явна CALCULATE в умові FILTER
• Перетворення контексту рядків в контекст фільтрів всередині FILTER
• Продажі і кількість клієнтів з оборотом понад 1 млн.
• Кількість клієнтів з оборотом понад 1 млн. у менеджерів, які виконали план >90%
• Кількість і продажі продуктів з відвантаженнями <1 тис. з продаваних
• Декілька FILTER в CALCULATE vs одна FILTER зі складною умовою
• Кількість і % повернених накладних: VALUES як перший аргумент FILTER
• % клієнтів, які купували >70% асортименту продукції що відвантажується

Visuals:

• Тестуємо роботу створених заходів

Існування кількох контекстів фільтрів і рядків усередині формули. Їх взаємодія

Віртуальні таблиці та віртуальні зв'язки між таблицями, "проживаючі" всередині формули

• Використання функцій для аналітики змін в часі: SAMEPERIODLASTYEAR,
• TOTALYTD, TOTALMTD, TOTALQTD
• Використання функцій - фільтрів PREVIOUSMONTH, PREVIOUSQUARTER, PREVIOUSDAY
• Робота з фінансовим роком, створення і використання «нестандартних» календарів
• Написання власних функцій Time intelligence
• Створення «ковзаючого обороту» (ковзаюче середнє)

• Power Query: підключення до сайтів в Інтернеті (курси валют, сайт НБУ)
• Незв'язана таблиця з курсами валют для вибору валюти звіту
• Варіанти використання аналізу Що-Якщо
• Створення перемикача заходів на візуалізації (незв'язана таблиця і SWITCH)
• Кейси з використанням інтервалів значень показників

• Техніка покрокового створення обчислюваного стовпця (через проміжний)
• VAR ... RETURN - синтаксис використання змінних. Як спростити велику формулу, використовуючи змінні
• Кейси з використанням змінних
• Використання даних про продажі і плани, що мають різний рівень деталізації
• Техніка роботи з таблицями, що мають різний рівень деталізації

• Імплементація захисту на рівні рядків, створення і використання ролей
• Базовий і динамічний захисти

• Налаштування сторінок звіту
• Робота з візуалізацією - використання різних типів візуалізацій, особливості роботи в Power BI Desktop
• Конфігурація взаємодій зі звітами в Power BI Desktop
• Декорація звітів: додавання логотипу, малюнків
• Робота зі зрізами: зрізи для чисел, дат і текстових полів. Синхронізація зрізів
• Робота з фільтрами і панеллю фільтрів, налаштування типів фільтрів, прості і просунуті критерії фільтрації. Блокування та приховування фільтрів

• Ієрархії. Робота з переходами на різні рівні деталізації: на одній і декількох таблицях
• Сторінки деталізації: вбудована опція «показати записи» і настройка користувацької сторінки деталізації
• Конфігурація «підказок»: настройка базової підказки та створення користувацької сторінки «підказки»
• Створення та використання груп категорій в візуалізації
• Використання дискретизації (binning) числових значень і візуалізацій на її основі
• Робота з посиланнями (URL)
• Застосування умовного форматування: гістограми, набори значків і форматування кольором (шрифт і заливка)