Таблиці дат у надбудові Power Pivot мають важливе значення для перегляду й обчислення даних із часом. У цій статті детально описано таблиці дат і способи їх створення в надбудові Power Pivot. Зокрема, у цій статті описано:
-
Чому таблиця дат важлива для перегляду та обчислення даних за датами й часом.
-
Додавання таблиці дат до моделі даних за допомогою надбудови Power Pivot.
-
Дізнайтеся, як створити нові стовпці дат, як-от Рік, Місяць і Період у таблиці дат.
-
Створення зв'язків між таблицями дат і таблицями фактів.
-
Як працювати з часом.
Ця стаття призначена для користувачів, які не мають доступу до Надбудови Power Pivot. Однак важливо розуміти, як імпортувати дані, створювати зв'язки та створювати обчислювані стовпці й міри.
У цій статті не описано, як використовувати функції DAX Time-Intelligence у формулах вимірювання. Докладні відомості про те, як створювати міри за допомогою функцій часового аналізу DAX, див. в статті Часовий аналіз у надбудові Power Pivot у програмі Excel.
Примітка.: У надбудові Power Pivot імена "міра" та "обчислюване поле" є синонімами. Ми використовуємо міру імені в цій статті. Докладні відомості див. в статті Міри в надбудові Power Pivot.
Зміст
Докладні відомості про таблиці дат
Майже весь аналіз даних передбачає перегляд і порівняння даних за датами й часом. Наприклад, можна підсумувати обсяги збуту за минулий фінансовий квартал, а потім порівняти ці підсумки з іншими кварталами або обчислити залишок завершення місяця для облікового запису. У кожному з цих випадків дати використовуються як спосіб групування та агрегації операцій збуту або залишків за певний період часу.
Звіт Power View
Таблиця дат може містити багато різних представлень дати й часу. Наприклад, таблиця дат часто містить такі стовпці, як Фінансовий рік, Місяць, Квартал або Період, які можна вибрати як поля зі списку полів під час нарізки та фільтрування даних у зведених таблицях або звітах Power View.
Список полів Power View
Для стовпців дат, наприклад "Рік", "Місяць" і "Квартал", щоб включити всі дати у відповідному діапазоні, у таблиці дат має бути принаймні один стовпець із безперервним набором дат. Тобто цей стовпець має містити один рядок для кожного дня для кожного року, включеного в таблицю дат.
Наприклад, якщо дані, які потрібно переглянути, містять дати від 1 лютого 2010 року до 30 листопада 2012 року, а ви повідомляєте про календарний рік, вам знадобиться таблиця дат із принаймні діапазоном дат від 1 січня 2010 року до 31 грудня 2012 року. Щороку в таблиці дат мають міститися всі дні для кожного року. Якщо ви регулярно оновлюватимете дані з новими даними, можливо, потрібно буде запускати дату завершення на рік або два, щоб не оновлювати таблицю дат із часом.
Таблиця дат із суцільним набором дат
Якщо звітувати про фінансовий рік, можна створити таблицю дат із безперервним набором дат для кожного фінансового року. Наприклад, якщо фінансовий рік починається 1 березня, а у вас є дані про фінансові роки 2010 року до поточної дати (наприклад, у фр. 2013), можна створити таблицю дат, яка починається 01.03.2009 і включає принаймні кожен день у кожному фінансовому році до останньої дати у фінансовому році 2013.
Якщо звітуватиметься як за календарний рік, так і за фінансовий рік, не потрібно створювати окремі таблиці дат. Одна таблиця дат може містити стовпці для календарного року, фінансового року та навіть тринадцяти чотирьохтижневого календаря періоду. Важливо, щоб таблиця дат містила безперервний набір дат для всіх років.
Додавання таблиці дат до моделі даних
До моделі даних можна додати таблицю дат кількома способами:
-
Імпорт із реляційної бази даних або іншого джерела даних.
-
Створіть таблицю дат у програмі Excel, а потім скопіюйте або створіть посилання на нову таблицю в надбудові Power Pivot.
-
Імпорт із Ринку Microsoft Azure.
Давайте розглянемо кожен з них більш уважно.
Імпорт із реляційної бази даних
Якщо імпортувати деякі або всі дані зі сховища даних чи реляційної бази даних іншого типу, імовірно, між нею та рештою імпортованих даних уже існує таблиця дат і зв'язки. Дати та формат, ймовірно, відповідають датам у ваших даних про факт, і дати, ймовірно, починаються добре в минулому і йдуть далеко в майбутнє. Таблиця дат, яку потрібно імпортувати, може бути дуже великою та містити діапазон дат, які не потрібно включати до моделі даних. За допомогою розширених функцій фільтра майстра імпорту таблиць Power Pivot можна вибірково вибирати лише дати та певні стовпці. Це може значно зменшити розмір книги та підвищити продуктивність.
Майстер імпорту таблиць
У більшості випадків не потрібно створювати додаткові стовпці, наприклад Фінансовий рік, Тиждень, Назва місяця тощо, оскільки вони вже існують в імпортованій таблиці. Однак у деяких випадках, коли таблицю дат імпортовано до моделі даних, може знадобитися створити додаткові стовпці дат залежно від конкретної потреби у звітуванні. На щастя, це легко зробити за допомогою DAX. Дізнайтеся більше про створення полів таблиці дат пізніше. Кожне середовище відрізняється. Якщо ви не впевнені, чи є у ваших джерел даних пов'язані дати або таблиці календаря, зверніться до адміністратора бази даних.
Створення таблиці дат у програмі Excel
У програмі Excel можна створити таблицю дат, а потім скопіювати її до нової таблиці в моделі даних. Це дійсно досить легко зробити, і це дає вам велику гнучкість.
Створюючи таблицю дат у програмі Excel, ви починаєте з одного стовпця з суцільного діапазону дат. Потім можна створити додаткові стовпці, як-от Рік, Квартал, Місяць, Фінансовий рік, Період тощо на аркуші Excel за допомогою формул Excel або скопіювати таблицю до моделі даних, щоб створити їх як обчислювані стовпці. Додаткові стовпці дат у надбудові Power Pivot описано в розділі Додавання нових стовпців дати до таблиці дат далі в цій статті.
Інструкції: створення таблиці дат у програмі Excel і копіювання її до моделі даних
-
У програмі Excel на пустому аркуші в клітинці A1 введіть ім'я заголовка стовпця, щоб визначити діапазон дат. Зазвичай це може бутищось на кшталт Date, DateTime або DateKey.
-
У клітинці A2 введіть дату початку. Наприклад, 01.01.2010.
-
Клацніть маркер заповнення та перетягніть його вниз до номера рядка, який містить дату завершення. Наприклад, 31.12.2016.
-
Виділіть усі рядки в стовпці Дата (включно з іменем заголовка в клітинці A1).
-
У групі Стилі натисніть кнопку Форматувати як таблицю, а потім виберіть стиль.
-
У діалоговому вікні Формат таблиці натисніть кнопку OK.
-
Скопіюйте всі рядки, включно із заголовком.
-
У надбудові Power Pivot на вкладці Основне натисніть кнопку Вставити.
-
У полі Попередній перегляд >введіть ім'я таблиці , наприклад Дата або Календар. Залиште прапорець Використовувати перший рядок як заголовкистовпців і натисніть кнопку OK.
Нова таблиця дат (у цьому прикладі – Календар) у надбудові Power Pivot має такий вигляд:
Примітка.: Ви також можете створити зв'язану таблицю за допомогою команди Додати до моделі даних. Проте це робить книгу без необхідності великою, оскільки книга має дві версії таблиці дат; один у програмі Excel, а інший – у надбудові Power Pivot.
Примітка.: Дата імені – це ключове слово в надбудові Power Pivot. Якщо ви назвете таблицю, створену в надбудові Power Pivot Date, вам потрібно буде взяти ім'я таблиці в одинарні лапки в будь-яких формулах DAX, які посилаються на неї в аргументі. Усі приклади зображень і формул у цій статті наведено в таблиці дат, створеній у надбудові Power Pivot під назвою "Календар".
Тепер у моделі даних є таблиця дат. За допомогою DAX можна додати нові стовпці дат, наприклад Рік, Місяць тощо.
Додавання нових стовпців дати до таблиці дат
Таблиця дат з одним стовпцем дат, який містить один рядок для кожного дня для кожного року, важлива для визначення всіх дат у діапазоні дат. Це також необхідно для створення зв'язку між таблицею фактів і таблицею дат. Але цей стовпець дат з одним рядком для кожного дня не корисний, якщо аналізувати дані за датами у зведеній таблиці або звіті Power View. Потрібно, щоб таблиця дат включала стовпці, які допомагають агрегувати дані для діапазону або групи дат. Наприклад, можна підсумувати обсяги збуту за місяцем або кварталом або створити міру, яка обчислює зростання за рік. У кожному з цих випадків таблиці дат потрібні стовпці року, місяця або кварталу, які дають змогу агрегувати дані за цей період.
Якщо ви імпортували таблицю дат із реляційного джерела даних, можливо, вона вже містить потрібні стовпці дат різних типів. У деяких випадках може знадобитися змінити деякі з цих стовпців або створити додаткові стовпці дат. Це особливо стосується випадків, коли ви створюєте власну таблицю дат у програмі Excel і копіюєте її в модель даних. На щастя, створити нові стовпці дат у надбудові Power Pivot досить легко за допомогою функцій дати й часу в DAX.
Порада.: Якщо ви ще не працювали з DAX, радимо почати навчання за допомогою короткого посібника: опануйте основи DAX за 30 хвилин на Office.com.
Функції дати й часу DAX
Якщо ви коли-небудь працювали з функціями дати й часу у формулах Excel, імовірно, ви будете знайомі з функціями дати й часу. Хоча ці функції схожі на їхні аналоги в Excel, існують деякі важливі відмінності.
-
Функції DAX "Дата й час" використовують тип даних "Дата й час".
-
Вони можуть приймати значення зі стовпця як аргумент.
-
Вони можуть використовуватися для повернення та/або обробки значень дат.
Ці функції часто використовуються, коли створюють спеціальні стовпці дат у таблиці дат, тому їх важливо розуміти. Ми використовуватимемо ряд цих функцій, щоб створювати стовпці для року, кварталу, фінансового завдання тощо.
Примітка.: Функції дати й часу в DAX не збігаються з функціями часового аналізу. Дізнайтеся більше про часовий аналіз у надбудові Power Pivot у програмі Excel.
DaX містить такі функції дати й часу:
У формулах також можна використовувати багато інших функцій DAX. Наприклад, у багатьох описаних тут формулах використовуються математичні та тригонометричні функції , як-от MOD і TRUNC, логічні функції , такі як IF, і текстові функції , як-от FORMAT . Докладні відомості про інші функції DAX див. в розділі "Додаткові ресурси " далі в цій статті.
Приклади формул для календарних років
У наведених нижче прикладах описано формули, які використовуються для створення додаткових стовпців у таблиці дат "Календар". Один стовпець з іменем "Дата" вже існує та містить суцільний діапазон дат від 01.01.2010 до 31.12.2016.
Рік
=YEAR([дата])
У цій формулі функція YEAR повертає рік зі значення в стовпці Дата. Оскільки значення в стовпці "Дата" має тип даних datetime, функція YEAR знає, як повернути з нього рік.
Місяць
=MONTH([дата])
У цій формулі, подібно до функції YEAR, можна просто скористатися функцією MONTH , щоб повернути значення місяця зі стовпця Дата.
Квартал
=INT(([Місяць]+2)/3)
У цій формулі функція INT використовується для повернення значення дати як цілого числа. Аргумент, який ми вказуємо для функції INT, – це значення зі стовпця Місяць, додайте 2, а потім поділіть його на 3, щоб отримати наш квартал, 1 через 4.
Назва місяця
=FORMAT([дата];"mmmm")
У цій формулі, щоб отримати назву місяця, ми використовуємо функцію FORMAT , щоб перетворити числове значення зі стовпця "Дата" на текст. Ми вказуємо стовпець Date як перший аргумент, а потім формат; ми хочемо, щоб назва нашого місяця відображали всі символи, тому ми використовуємо "mmmm". Наш результат має такий вигляд:
Якщо потрібно повернути скорочене ім'я місяця трьом буквам, ми б в аргументі формату вводили слово "ммм".
День тижня
=FORMAT([дата];"ddd")
У цій формулі ми використовуємо функцію FORMAT, щоб отримати назву дня. Оскільки нам просто потрібна скорочена назва дня, у аргументі формату вказано "ddd".
Зразок зведеної таблиці
Якщо у вас є поля для таких дат, як Рік, Квартал, Місяць тощо, їх можна використовувати у зведеній таблиці або звіті. Наприклад, на наведеному нижче зображенні показано поле SalesAmount із таблиці "Збут факту" в таблиці VALUES, а також "Рік" і "Квартал" із таблиці вимірів "Календар" у поданні РЯДКИ. Обсяг salesAmount агрегується для контексту року та кварталу.
Приклади формул для фінансового року
Фінансовий рік
=IF([Місяць]<= 6;[Рік];[Рік]+1)
У цьому прикладі фінансовий рік починається 1 липня.
Немає функції, яка може видобути фінансовий рік зі значення дати, оскільки дати початку та завершення фінансового року часто відрізняються від дат календарного року. Щоб отримати фінансовий рік, спочатку скористайтеся функцією IF , щоб перевірити, чи значення місяця менше або дорівнює 6. У другому аргументі, якщо значення місяця менше або дорівнює 6, повертається значення зі стовпця Рік. Якщо ні, повертається значення з року та додається 1.
Ще один спосіб указати значення місяця завершення фінансового року – створити міру, яка просто визначає місяць. Наприклад, FYE:=6. Потім замість номера місяця можна посилатися на назву міри. Наприклад, =IF([Місяць]<=[FYE],[Рік],[Рік]+1). Це забезпечує більшу гнучкість під час посилання на місяць завершення фінансового року в кількох різних формулах.
Фінансовий місяць
=IF([Місяць]<= 6, 6+[Місяць], [Місяць]–6)
У цій формулі ми вказуємо, чи значення [Місяць] менше або дорівнює 6, потім приймаємо 6 і додаємо значення з місяця, в іншому випадку віднімайте 6 від значення [Місяць].
Фінансовий квартал
=INT(([FiscalMonth]+2)/3)
Формула, яку ми використовуємо для FiscalQuarter, така сама, як і для кварталу в нашому календарному році. Єдина різниця полягає в тому, що замість [Month] ми вказуємо [FiscalMonth].
Свята або особливі дати
Ви можете додати стовпець дат, у якому вказано, що певні дати – це свята або інша особлива дата. Наприклад, можна підсумувати загальний обсяг збуту за новий рік, додавши поле "Свято" до зведеної таблиці, роздільника або фільтра. В інших випадках можна виключити ці дати з інших стовпців дати або міри.
Включно зі святами або особливими днями досить просто. У програмі Excel можна створити таблицю з датами, які потрібно включити. Потім можна скопіювати або використати команду Додати до моделі даних, щоб додати її до моделі даних як зв'язану таблицю. У більшості випадків зв'язок між таблицею та таблицею "Календар" створювати не потрібно. Будь-які формули, що посилаються на неї, можуть використовувати функцію LOOKUPVALUE , щоб повертати значення.
Нижче наведено приклад таблиці, створеної в Excel, яка містить свята, які потрібно додати до таблиці дат:
|
Дата |
Свято |
|---|---|
|
1/1/2010 |
Нові роки |
|
11/25/2010 |
Подяки |
|
12/25/2010 |
Різдво |
|
01.01.2011 |
Нові роки |
|
11/24/2011 |
Подяки |
|
12/25/2011 |
Різдво |
|
01.01.2012 |
Нові роки |
|
22.11.2012 |
Подяки |
|
12/25/2012 |
Різдво |
|
1/1/2013 |
Нові роки |
|
11/28/2013 |
Подяки |
|
12/25/2013 |
Різдво |
|
11/27/2014 |
Подяки |
|
12/25/2014 |
Різдво |
|
01.01.2014 |
Нові роки |
|
11/27/2014 |
Подяки |
|
12/25/2014 |
Різдво |
|
1/1/2015 |
Нові роки |
|
11/26/2014 |
Подяки |
|
12/25/2015 |
Різдво |
|
01.01.2016 |
Нові роки |
|
11/24/2016 |
Подяки |
|
12/25/2016 |
Різдво |
У таблиці дат ми створюємо стовпець "Свята " та використовуємо таку формулу:
=LOOKUPVALUE(Свята[Свята],Свята[дата],Календар[дата])
Розгляньмо цю формулу ретельніше.
Ми використовуємо функцію LOOKUPVALUE, щоб отримувати значення зі стовпця "Свята" в таблиці "Свята". У першому аргументі ми вказуємо стовпець, де буде наше значення результату. У таблиці "Свята" вказано стовпець "Свята", тому що це значення, яке потрібно повернути.
=LOOKUPVALUE(Свята[Свята];Свята[дата],Календар[дата])
Потім ми вказуємо другий аргумент – стовпець пошуку з датами, які потрібно знайти. У таблиці "Свята" вказано стовпець "Дата", наприклад:
=LOOKUPVALUE(Свята[Свята],Свята[дата];Календар[дата])
Нарешті, ми вкажемо стовпець у таблиці Календар із датами, які потрібно знайти в таблиці "Свята ". Це, звичайно, стовпець "Дата " в таблиці "Календар ".
=LOOKUPVALUE(Свята[Свята],Свята[дата],Календар[дата])
Стовпець "Свята" поверне назву свята для кожного рядка, який має значення дати, яке відповідає даті в таблиці "Свята".
Настроюваний календар – тринадцять чотиритижневих періодів
Деякі організації, наприклад роздрібна торгівля або продовольчі служби, часто повідомляють про різні періоди, наприклад тринадцять чотиритижневих періодів. З тринадцяти чотиритижневим календарем періоду кожен період становить 28 днів; тому кожен період містить чотири понеділки, чотири вівторки, чотири середи тощо. Кожен період містить однакову кількість днів, і зазвичай свята припадають на один період щороку. Ви можете почати період у будь-який день тижня. Як і з датами в календарі або фінансовому році, за допомогою DAX можна створити додаткові стовпці з настроюваними датами.
У наведених нижче прикладах перший повний період починається в першу неділю фінансового року. У цьому випадку фінансовий рік починається з 07.07.12.
Тиждень
Це значення дає нам номер тижня, починаючи з першого повного тижня фінансового року. У цьому прикладі перший повний тиждень починається в неділю, тому перший повний тиждень першого фінансового року в таблиці "Календар" фактично починається 04.07.2010 і триває останній повний тиждень у таблиці "Календар". Хоча саме це значення не все, що корисно в аналізі, необхідно обчислити для використання в інших формулах періоду 28 днів.
=INT([дата]-40356)/7)
Розгляньмо цю формулу ретельніше.
Спочатку створюється формула, яка повертає значення зі стовпця "Дата" як ціле число, наприклад:
=INT([дата])
Потім ми хочемо шукати першу неділю в першому фінансовому році. Ми бачимо, що це 04.07.2010.
Тепер відніміть від цього значення 40356 (тобто ціле число за 27.06.2010, останню неділю від попереднього фінансового року), щоб отримати кількість днів із початку днів у таблиці "Календар", наприклад:
=INT([дата]-40356)
Потім поділіть результат на 7 (днів на тиждень), наприклад:
=INT(([дата]-40356)/7)
Результат матиме такий вигляд:
Period (крапка)
Період у цьому настроюваному календарі містить 28 днів, і він завжди починатиметься в неділю. Цей стовпець повертає число періоду, що починається з першої неділі першого фінансового року.
=INT(([Тиждень]+3)/4)
Розгляньмо цю формулу ретельніше.
Спочатку створюється формула, яка повертає значення зі стовпця "Тиждень" як ціле число, наприклад:
=INT([Тиждень])
Потім додайте 3 до цього значення, наприклад:
=INT([Тиждень]+3)
Потім поділіть результат на 4, наприклад:
=INT(([Тиждень]+3)/4)
Результат матиме такий вигляд:
Фінансовий рік періоду
Це значення повертає фінансовий рік для періоду.
=INT(([Період]+12)/13)+2008
Розгляньмо цю формулу ретельніше.
Спочатку ми створюємо формулу, яка повертає значення з періоду та додає 12:
= ([Період]+12)
Результат ділиться на 13, тому що у фінансовому році є тринадцять 28-денних періодів:
=(([Період]+12)/13)
Ми додаємо 2010, тому що це перший рік у таблиці:
=(([Період]+12)/13)+2010
Нарешті, ми використовуємо функцію INT, щоб видалити будь-яку частку результату, і повертаємо ціле число, якщо поділене на 13, наприклад:
=INT(([Період]+12)/13)+2010
Результат матиме такий вигляд:
Період у фінансовому році
Це значення повертає число періоду від 1 до 13, починаючи з першого повного періоду (починаючи з неділі) кожного фінансового року.
=IF(MOD([Період];13);MOD([Період];13);13)
Ця формула трохи складніша, тому спочатку її буде описати мовою, якою ми краще розуміємо. У цій формулі вказано, що значення ділиться з [Крапка] на 13, щоб отримати число періоду (1–13) у році. Якщо це число 0, повертається 13.
Спочатку ми створюємо формулу, яка повертає залишок значення від періоду до 13. Ми можемо використовувати MOD (математичні та тригонометричні функції), як описано нижче.
=MOD([Період],13)
Це, здебільшого, дає нам потрібний результат, за винятком випадків, коли значення періоду становить 0, оскільки ці дати не припадають на перший фінансовий рік, як у перші п'ять днів у нашому прикладі таблиця дат календаря. Ми можемо подбати про це за допомогою функції IF. У випадку, якщо наш результат 0, ми повертаємо 13, як це:
=IF(MOD([Період],13);MOD([Період];13);13)
Результат матиме такий вигляд:
Зразок зведеної таблиці
На зображенні нижче показано зведену таблицю з полем SalesAmount із таблиці "Збут фактів" у стовпцях VALUES і PeriodFiscalYear і PeriodInFiscalYear з таблиці виміру дати календаря в рядках. SalesAmount агрегується для контексту за фінансовим роком і 28-денним періодом у фінансовому році.
Зв’язки
Створивши таблицю дат у моделі даних, щоб почати переглядати дані у зведених таблицях і звітах, а також агрегувати дані на основі стовпців у таблиці вимірів дати, потрібно створити зв'язок між таблицею фактів із даними про транзакції та таблицею дат.
Оскільки потрібно створити зв'язок на основі дат, потрібно створити зв'язок між стовпцями, значення яких належать до типу даних datetime (Date).
Для кожного значення дати в таблиці фактів пов'язаний стовпець підстановки в таблиці дат має містити відповідні значення. Наприклад, рядок (запис транзакції) у таблиці "Збут факту" зі значенням 15.08.2012 у стовпці DateKey має мати відповідне значення у відповідному стовпці "Дата" в таблиці "Дата" ("Календар"). Це одна з найважливіших причин, чому стовпець дат у таблиці дат містить суцільний діапазон дат, який містить будь-яку можливу дату в таблиці фактів.
Примітка.: Хоча стовпець дат у кожній таблиці має мати однаковий тип даних (Дата), формат кожного стовпця не має значення.
Примітка.: Якщо надбудова Power Pivot не дає змоги створювати зв'язки між двома таблицями, поля дати можуть не зберігати дату й час до одного рівня точності. Залежно від форматування стовпців значення можуть виглядати однаково, але зберігатися по-різному. Дізнайтеся більше про роботу з часом.
Примітка.: Уникайте використання цілих сурогатних ключів у зв'язках. Під час імпорту даних із реляційного джерела даних часто стовпці дати й часу представляються сурогатним ключем – цілим числом, який використовується для представлення унікальної дати. У надбудові Power Pivot слід уникати створення зв'язків за допомогою цілих ключів дати й часу, а натомість використовувати стовпці з унікальними значеннями з типом даних дати. Хоча використання сурогатних ключів вважається найкращою практикою на традиційних сховищах даних, цілі ключі не потрібні в надбудові Power Pivot і ускладнюють групування значень у зведених таблицях за різними періодами дат.
Якщо під час спроби створити зв'язок виникає помилка невідповідності типу, імовірно, стовпець у таблиці фактів не належить до типу даних "Дата". Це може статися, якщо надбудові Power Pivot не вдається автоматично перетворити неочасне значення (зазвичай це текстовий тип даних) на тип даних дати. Ви все одно можете використовувати стовпець у таблиці фактів, але вам доведеться перетворити дані за допомогою формули DAX у новому обчислюваному стовпці. Див . статтю Перетворення дат типу даних тексту на тип даних дати пізніше в додатку.
Кілька зв'язків
У деяких випадках може знадобитися створити кілька зв'язків або створити кілька таблиць дат. Наприклад, якщо в таблиці "Факт збуту" є кілька полів дати, як-от DateKey, ShipDate і ReturnDate, усі вони можуть мати зв'язки з полем "Дата" в таблиці "Дата календаря", але лише один із них може бути активним зв'язком. У цьому випадку, оскільки DateKey представляє дату транзакції, і тому найважливіша дата, це найкраще слугуватиме активним зв'язком. Інші користувачі мають неактивні зв'язки.
Наведена нижче зведена таблиця обчислює загальний обсяг збуту за фінансовим роком і фінансовим кварталом. Міра "Загальний обсяг продажів" із формулою "Загальний обсяг продажів:=SUM([Обсяг продажів])" розміщується у значеннях VALUES, а поля FiscalYear і FiscalQuarter з таблиці "Дата календаря" розміщуються в рядках.
Ця пряма зведена таблиця працює правильно, тому що ми хочемо підсумувати загальний обсяг збуту за датою транзакції в DateKey. У нашому вимірі "Загальний обсяг продажів" використовуються дати в dateKey та підсумовуються за фінансовим роком і фінансовим кварталом, оскільки в таблиці "Збут" і стовпці "Дата" в таблиці "Календар" існує зв'язок між DateKey.
Неактивні зв'язки
Але що робити, якщо потрібно підсумувати загальний обсяг збуту не за датою транзакції, а за датою доставки? Нам потрібен зв'язок між стовпцем Дата_доставки в таблиці "Збут" і стовпцем "Дата" в таблиці "Календар". Якщо не створити цей зв'язок, агрегації завжди залежать від дати транзакції. Проте ми можемо мати кілька зв'язків, хоча лише один із них може бути активним, і оскільки дата транзакції є найважливішою, вона отримує активний зв'язок із таблицею "Календар".
У цьому випадку дата_доставки має неактивний зв'язок, тому будь-яка формула мір, створена для агрегації даних на основі дат доставки, має вказувати неактивний зв'язок за допомогою функції USERELATIONSHIP .
Наприклад, оскільки між стовпцем Дата_доставки в таблиці "Збут" і стовпцем "Дата" в таблиці "Календар" існує неактивний зв'язок, ми можемо створити міру, яка підсумовує загальний обсяг збуту за датою доставки. Формула використовується для визначення зв'язку для використання:
Загальний обсяг продажів за датою доставки:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[Дата_доставки],Календар[Дата]))
У цій формулі просто вказано: обчислити суму для salesAmount, але фільтрувати за допомогою зв'язку між стовпцем "Дата_доставки" в таблиці "Продажі" та стовпцем "Дата" в таблиці "Календар".
Тепер, якщо створити зведену таблицю та ставити значення "Загальний обсяг продажів за датою доставки" у значеннях "ЗНАЧЕННЯ", "Фінансовий рік" і "Фінансовий квартал" у розділах "РЯДКИ", відображається той самий загальний підсумок, але всі інші суми сум для фінансового року та фінансового кварталу відрізняються, оскільки вони базуються на даті доставки, а не на даті транзакції.
Використання неактивних зв'язків дає змогу використовувати лише одну таблицю дат, але для цього потрібно, щоб будь-які міри (наприклад, Загальний обсяг продажів за датою доставки) посилаєтеся на неактивний зв'язок у формулі. Існує ще одна альтернатива, тобто використовувати кілька таблиць дат.
Кілька таблиць дат
Ще один спосіб працювати з кількома стовпцями дат у таблиці фактів – створити кілька таблиць дат і створити окремі активні зв'язки між ними. Давайте знову розглянемо наш приклад таблиці "Продажі". У нас є три стовпці з датами, які ми можемо захотіти агрегувати дані на:
-
DateKey з датою продажу для кожної транзакції.
-
Дата доставки– з датою й часом доставки проданих товарів клієнту.
-
ReturnDate – з датою й часом отримання одного або кількох елементів.
Пам'ятайте, що поле DateKey з датою транзакції є найважливішим. Ми зробимо більшість агрегацій на основі цих дат, тому ми, безумовно, хочемо зв'язок між ним і стовпцем "Дата" в таблиці "Календар". Якщо не потрібно створювати неактивні зв'язки між "Дата_доставки" та "ReturnDate" і полем "Дата" в таблиці "Календар", тому ми можемо створити додаткові таблиці дат для дати доставки та дати повернення. Потім можна створити активні зв'язки між ними.
У цьому прикладі ми створили ще одну таблицю дат із назвою ShipCalendar. Це, звичайно, також означає створення додаткових стовпців дат, і оскільки ці стовпці дат містяться в іншій таблиці дат, ми хочемо назвати їх таким чином, щоб вони відрізняють їх від тих самих стовпців у таблиці Календар. Наприклад, ми створили стовпці "ShipYear", "ShipMonth", "ShipQuarter" тощо.
Якщо створити зведену таблицю та додати показник "Загальний обсяг продажів" у значеннях "ЗНАЧЕННЯ", "ShipFiscalYear" і "ShipFiscalQuarter" у rows, то відображаються ті самі результати, що й під час створення неактивного зв'язку та спеціального обчислюваного поля "Загальний обсяг продажів за датою доставки".
Кожен із цих підходів потребує ретельного розгляду. Якщо використовується кілька зв'язків з однією таблицею дат, можливо, доведеться створити спеціальні міри, які переносять неактивні зв'язки за допомогою функції USERELATIONSHIP. З іншого боку, створення кількох таблиць дат може заплутатися в списку полів, а оскільки в моделі даних у вас більше таблиць, для цього потрібно більше пам'яті. Поекспериментуйте з тим, що найкраще підходить вам.
Властивість «Таблиця дат»
Властивість Date Table встановлює метадані, необхідні для правильної роботи Time-Intelligence таких функцій, як TOTALYTD, PREVIOUSMONTH і DATESBETWEEN. Коли виконується обчислення за допомогою однієї з цих функцій, обробник формул Power Pivot знає, куди перейти, щоб отримати потрібні дати.
Попередження!: Якщо цю властивість не встановлено, міри, які використовують функції DAX Time-Intelligence, можуть не повертати правильні результати.
Якщо задати властивість Таблиця дат, у ній потрібно вказати таблицю дат і стовпець дат типу даних Date (datetime).
Інструкції: установлення властивості "Таблиця дат"
-
У вікні PowerPivot виберіть таблицю Календар .
-
На вкладці Конструктор натисніть кнопку Позначити як таблицю дат.
-
У діалоговому вікні Позначити як таблицю дат виберіть стовпець з унікальними значеннями та тип даних "Дата".
Робота з часом
Усі значення дат із типом даних "Дата" в Програмі Excel або SQL Server – це фактично число. До цього числа входять цифри, які посилаються на час. У багатьох випадках цей час для кожного рядка – північ. Наприклад, якщо поле DateTimeKey в таблиці фактів "Збут" має такі значення, як 19.10.2010 12:00:00 AM, це означає, що значення мають рівень точності дня. Якщо значення полів DateTimeKey містять час, наприклад 19.10.2010 8:44:00 AM, це означає, що значення мають рівень точності хвилини. Значення також можуть мати точність на рівні години або навіть рівень точності в секундах. Рівень точності значення часу суттєво вплине на створення таблиці дат і зв'язків між нею та таблицею фактів.
Потрібно визначити, чи буде агрегувати дані до рівня точності дня або до рівня точності часу. Іншими словами, в областях "Рядок", "Стовпець" або "Фільтр" зведеної таблиці можна використовувати стовпці в таблиці дат, як-от "Ранок", "День" або "Година" як "Година".
Примітка.: Дні – це найменша одиниця часу, з якою можуть працювати функції часового аналізу DAX. Якщо вам не потрібно працювати зі значеннями часу, слід зменшити точність даних, щоб використовувати дні як мінімальну одиницю вимірювання.
Якщо ви маєте намір агрегувати дані до рівня часу, у таблиці дат знадобиться стовпець дат із включеним часом. Насправді, він потребуватиме стовпець дат з одним рядком для кожної години або навіть кожної хвилини, кожного дня, для кожного року в діапазоні дат. Це відбувається тому, що, щоб створити зв'язок між стовпцем DateTimeKey в таблиці фактів і стовпцем дат у таблиці дат, потрібно мати відповідні значення. Як ви можете собі уявити, якщо ви включаєте багато років, це може зробити для дуже великої таблиці дат.
У більшості випадків дані потрібно агрегувати лише за день. Іншими словами, ви використовуватимете такі стовпці, як Рік, Місяць, Тиждень або День тижня, як поля в областях "Рядок", "Стовпець" або "Фільтр" зведеної таблиці. У цьому випадку стовпець дат у таблиці дат має містити лише один рядок для кожного дня в році, як описано вище.
Якщо стовпець дат містить рівень точності часу, але ви будете агрегувати лише до рівня дня, щоб створити зв'язок між таблицею фактів і таблицею дат, можливо, доведеться змінити таблицю фактів, створивши новий стовпець, який скорочує значення в стовпці дати до значення дня. Іншими словами, перетворіть значення, наприклад 19.10.2010, 8:44:00на19.10.2010, 12:00:00 AM. Потім можна створити зв'язок між цим новим стовпцем і стовпцем дат у таблиці дат, оскільки значення збігаються.
Розгляньмо приклад. На цьому зображенні показано стовпець DateTimeKey в таблиці "Факт збуту". Усі агрегації для даних у цій таблиці мають бути лише до рівня дня, використовуючи стовпці в таблиці дат календаря, наприклад Рік, Місяць, Квартал тощо. Час, включений до значення, не є актуальним, лише фактична дата.
Оскільки нам не потрібно аналізувати ці дані до рівня часу, нам не потрібен стовпець "Дата" в таблиці "Дата календаря", щоб включити один рядок для кожної години та кожної хвилини кожного дня в кожному році. Отже, стовпець Дата в нашій таблиці дат має такий вигляд:
Щоб створити зв'язок між стовпцем DateTimeKey в таблиці "Збут" і стовпцем "Дата" в таблиці "Календар", можна створити новий обчислюваний стовпець у таблиці "Збут" і скористатися функцією TRUNC , щоб скоротити значення дати й часу в стовпці DateTimeKey до значення дати, яке відповідає значенням у стовпці "Дата" в таблиці "Календар". Наша формула має такий вигляд:
=TRUNC([DateTimeKey],0)
Це дає нам новий стовпець (ми називаємо DateKey) з датою зі стовпця DateTimeKey та часом 12:00:00 для кожного рядка:
Тепер ми можемо створити зв'язок між цим новим стовпцем (DateKey) і стовпцем "Дата" в таблиці "Календар".
Аналогічно, ми можемо створити обчислюваний стовпець у таблиці "Збут", щоб зменшити точність часу в стовпці DateTimeKey до рівня точності години. У цьому випадку функція TRUNC не працюватиме, але ми все одно можемо використовувати інші функції DAX "Дата й час", щоб видобути й повторно об'єднати нове значення з годинним рівнем точності. Можна використовувати таку формулу:
= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)
Наш новий стовпець має такий вигляд:
За умови, що наш стовпець "Дата" в таблиці дат має значення до значення точності години, ми можемо створити зв'язок між ними.
Зробити дати більш придатними для використання
Багато стовпців дат, створених у таблиці дат, необхідні для інших полів, але насправді не все, що зручно в аналізі. Наприклад, поле DateKey в таблиці "Збут", на яке ми посилалися й які відображаються в цій статті, важливе, оскільки для кожної транзакції ця транзакція записується в певний день і час. Але з точки зору аналізу та звітування це не все, що зручно, тому що ми не можемо використовувати його як рядок, стовпець або поле фільтра у зведеній таблиці або звіті.
Аналогічно, у нашому прикладі стовпець "Дата" в таблиці "Календар" дуже корисний, критичний насправді, але його не можна використовувати як вимір у зведеній таблиці.
Щоб забезпечити максимально корисну роботу таблиць і стовпців, а також спростити навігацію у списках полів зведеної таблиці або звіту Power View, важливо приховати непотрібні стовпці в засобах клієнта. Крім того, можна приховати певні таблиці. У таблиці "Свята" вище наведено дати свят, важливі для певних стовпців таблиці "Календар", але не можна використовувати стовпці "Дата" та "Свята" в таблиці "Свята" як поля у зведеній таблиці. Щоб спростити навігацію списками полів, можна приховати всю таблицю "Свята".
Ще одним важливим аспектом роботи з датами є іменування конвенцій. У надбудові Power Pivot можна назвати таблиці та стовпці будь-яким потрібним чином. Але пам'ятайте, особливо якщо ви надаватимете спільний доступ до книги іншим користувачам, завдяки угоді про іменування простіше визначати таблиці й дати не лише в списках полів, але й у надбудові Power Pivot і формулах DAX.
Створивши таблицю дат у моделі даних, можна почати створювати міри, які допоможуть максимально ефективно використовувати дані. Деякі з них можуть бути простими, коли підсумовуються підсумки продажів за поточний рік, а інші можуть бути складніші, якщо потрібно відфільтрувати дані за певним діапазоном унікальних дат. Докладні відомості див. в статті Показники в надбудові Power Pivot і функціях часового аналізу.
Додаток
Перетворення текстових дат типу даних на тип даних дати
У деяких випадках таблиця фактів із даними транзакцій може містити дати текстових даних. Тобто дата, яка відображається як 2012-12-04T11:47:09, насправді не є датою взагалі, або, принаймні, не типом дати, яку надбудова Power Pivot може зрозуміти. Це дійсно просто текст, який читається як дата. Щоб створити зв'язок між стовпцем дат у таблиці фактів і стовпцем дат у таблиці дат, обидва стовпці мають належати до типу даних "Дата ".
Зазвичай, коли ви намагаєтеся змінити тип даних для стовпця дат текстового типу даних на тип даних дати, Надбудова Power Pivot може інтерпретувати дати та автоматично перетворити його на справжній тип даних. Якщо надбудові Power Pivot не вдалося перетворити тип даних, з'явиться повідомлення про невідповідність типів.
Однак дати можна й надалі перетворювати на істинний тип даних. Ви можете створити новий обчислюваний стовпець і використати формулу DAX, щоб проаналізувати рік, місяць, день, час тощо з текстових рядків, а потім об'єднати його знову, щоб надбудова Power Pivot прочитала його як справжню дату.
У цьому прикладі ми імпортували таблицю фактів "Збут" до надбудови Power Pivot. Він містить стовпець DateTime. Значення виглядають ось так:
Якщо переглянути вкладку "Основне" в групі "Тип даних у форматуванні", ми побачимо, що це текстовий тип даних.
Не вдалося створити зв'язок між стовпцем DateTime і стовпцем Date (Дата) у таблиці дат, тому що типи даних не збігаються. Якщо спробувати змінити тип даних на "Дата", з'являється повідомлення про невідповідність типу:
У такому разі надбудові Power Pivot не вдалося перетворити тип даних із тексту на дату. Ми все ще можемо використовувати цей стовпець, але для того, щоб перетворити його на справжній тип даних дати, потрібно створити новий стовпець, який аналізує текст і повторно створить його у значення, яке Power Pivot може створити тип даних "Дата".
Пам'ятайте, з розділу Робота з часом вище в цій статті; якщо не потрібно, щоб аналіз мав точність, слід перетворити дати в таблиці фактів на рівень точності дня. З цією метою ми хочемо, щоб значення в нашому новому стовпці були на рівні точності дня (за винятком часу). Ми можемо перетворити значення в стовпці DateTime на тип даних дати та видалити рівень точності часу за такою формулою:
=DATE(LEFT([Дата й час],4); MID([Дата й час],6,2), MID([Дата й час],9;2))
Це дасть нам новий стовпець (у цьому випадку називається Дата). Надбудова Power Pivot навіть виявляє значення дат і автоматично встановлює для типу даних значення Дата.
Якщо потрібно зберегти рівень точності часу, ми просто розширюємо формулу на години, хвилини та секунди.
=DATE(LEFT([Дата й час],4), MID([Дата й час],6,2), MID([Дата й час],9;2)) +
TIME(MID([Дата й час],12,2), MID([Дата й час],15,2), MID([Дата й час],18,2))
Тепер, коли у нас є стовпець "Дата" типу даних "Дата", можна створити зв'язок між ним і стовпцем дат у даті.
Додаткові ресурси
Обчислення в надбудові Power Pivot
Короткий посібник. Вивчення основ мови DAX за 30 хвилин
