Таблиці дат у надбудові Power Pivot важливі для перегляду й обчислення даних із часом. У цій статті наведено докладні відомості про таблиці дат і способи їх створення в надбудові Power Pivot. Зокрема, у цій статті описано:
-
Чому таблиця дат важлива для перегляду й обчислення даних за датами й часом.
-
Використання надбудови Power Pivot для додавання таблиці дат до моделі даних.
-
Створення нових стовпців дат, як-от "Рік", "Місяць" і "Період" у таблиці дат.
-
Створення зв'язків між таблицями дат і фактами.
-
Як працювати з часом.
Цю статтю призначено для користувачів у новій програмі Power Pivot. Однак важливо мати добре розуміння імпорту даних, створення зв'язків і створення обчислюваних стовпців і мір.
У цій статті не описано, як використовувати функції DAX Time-Intelligence у формулах для вимірювання. Докладні відомості про те, як створювати міри за допомогою функцій часового аналізу DAX, див. в статті Часовий аналіз у надбудові Power PivotExcel .
Примітка.: У надбудові Power Pivot імена "міра" та "обчислюване поле" – це синонім. У цій статті ми використовуємо міру назви. Докладні відомості див. в мірах у надбудові Power Pivot.
Зміст
Докладні відомості про таблиці дат
Практично весь аналіз даних передбачає перегляд і порівняння даних у датах і часі. Наприклад, можна підсумувати обсяги збуту за минулий фінансовий квартал і порівняти ці підсумки з іншими кварталами або обчислити суму закриття місяця для облікового запису. У кожному з цих випадків дати використовується як спосіб групування та агрегування операцій збуту або балансу за певний період часу.
Звіт Power View
Таблиця дат може містити багато різних представлень дат і часу. Наприклад, у таблиці дат часто містяться стовпці (наприклад, "Фінансовий рік", "Місяць", "Квартал" або "Період"), які можна вибрати як поля зі списку полів під час роздільника та фільтрування даних у зведених таблицях або звітах Power View.
Список полів Power View
Щоб включити всі дати у відповідному діапазоні, у стовпцях дат Рік, Місяць і Квартал, таблиця дат має містити принаймні один стовпець із несуміжним набором дат. Тобто цей стовпець має містити один рядок для кожного року, включений до таблиці дат.
Наприклад, якщо дані, які потрібно переглянути, мають дати від 1 лютого 2010 року до 30 листопада 2012 року, і вам потрібно створити таблицю дат, яка містить принаймні діапазон дат від 1 січня 2010 року до 31 грудня 2012 року. Кожен рік у таблиці дат має містити всі дні на кожен рік. Якщо ви регулярно оновлюватиме дані новішими даними, може знадобитись, щоб дата завершення діяла на один-два роки, тож вам не доведеться оновлювати таблицю дат у певний час.
Таблиця дат із несуміжним набором дат
Якщо звітувати про фінансовий рік, можна створити таблицю дат із несуміжним набором дат для кожного фінансового року. Наприклад, якщо фінансовий рік починається 1 березня, а у вас є дані для фінансових років 2010 року до поточної дати (наприклад, з FY 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 "Дата й час" використовують тип даних "Дата й час".
-
Вони можуть приймати значення зі стовпця як аргумент.
-
Їх можна використовувати для повернення та/або керування значеннями дат.
Ці функції часто використовуються, коли створюються спеціальні стовпці дат у таблиці дат, тому їх важливо розуміти. Ці функції використовуються для створення стовпців "Рік", "Квартал", "ФінансовийMonth" тощо.
Примітка.: Функції дати й часу в DAX – це не ті самі функції, що й функції часового аналетування. Дізнайтеся більше про часовий аналь у надбудові Power Pivot у Excel 2013.
DAX містить такі функції дати й часу:
У формулах також можна використовувати багато інших функцій DAX. Наприклад, у багатьох формулах, описаних тут, використовуються математичні та тригонометричні функції, як-от MOD і TRUNC, логічні функції, такі як IFі текстові функції, як-от FORMAT. Докладні відомості про інші функції DAX див. в розділі "Додаткові ресурси" далі в цій статті.
Приклади формул для календарного року
У прикладах нижче наведено формули, які використовуються, щоб створювати додаткові стовпці в таблиці дат під назвою Календар. Один стовпець з іменем Дата вже існує й містить несуміжні дати від 01.01.2010 до 31.12.2016.
Рік
=YEAR([дата])
У цій формулі функція YEAR повертає значення року зі значення в стовпці Дата. Оскільки значення в стовпці "Дата" має тип даних "Дата й час", функція YEAR знає, як повернути з нього рік.
Місяць
=MONTH([дата])
У цій формулі, подібно до функції YEAR, можна просто скористатися функцією MONTH, щоб повернути значення місяця зі стовпця "Дата".
Квартал
=INT(([Місяць]+2)/3)
У цій формулі функція INT використовується, щоб повернути значення дати як ціле число. Аргумент, указаний для функції INT, – це значення зі стовпця Місяць, додайте 2, а потім поділіть його на 3, щоб отримати наш квартал з 1 по 4.
Назва місяця
=FORMAT([дата];"mmmm")
У цій формулі, щоб отримати назву місяця, ми використовуємо функцію FORMAT, щоб перетворити числове значення зі стовпця "Дата" на текст. Першим аргументом є стовпець Дата, а потім – формат; ми хочемо, щоб назва місяця могло відображати всі символи, тому ми використовуємо "mmmm". Наш результат виглядає так:
Якщо потрібно повернути назву місяця, скорочену до трьох букв, для аргументу формату слід використовувати "mmm".
День тижня
=FORMAT([дата];"ddd")
У цій формулі для створення імені дня використовується функція FORMAT. Оскільки нам просто потрібна скорочена назва дня, у аргументі формату вказується "ддд".
Зразок зведеної таблиці
Якщо у вас є поля для таких дат, як Рік, Квартал, Місяць тощо, їх можна використовувати у зведеній таблиці або звіті. Наприклад, на наведеному нижче зображенні показано поле SalesAmount із таблиці фактів "Продажі" в таблиці "ЗНАЧЕННЯ" та "Рік" і "Квартал" із таблиці вимірів "Календар" у рядках. Обсяг salesAmount об'єкуються за контекстом року та кварталу.
Приклади формул фінансового року
Фінансовий рік
=IF([Місяць]<= 6,[Рік],[Рік]+1)
У цьому прикладі фінансовий рік починається 1 липня.
Немає функції, яка може видобути фінансовий рік зі значення дати, оскільки дати початку та завершення фінансового року часто відрізняються від дат календарного року. Щоб отримати фінансовий рік, спочатку ми використовуємо функцію IF для перевірки, чи значення параметра Month менше або дорівнює 6. У другому аргументі, якщо значення місяця менше або дорівнює 6, повертається значення зі стовпця Рік. Якщо ні, повертається значення з року та додайте 1.
Інший спосіб указати значення фінансового місяця завершення року – створити міру, яка просто вказує місяць. Наприклад, FYE:=6. Потім ви можете посилатися на назву міри замість номера місяця. Наприклад, =IF([Місяць]<=[FYE],[Рік],[Рік]+1). Це забезпечує більшу гнучкість під час посилання на фінансовий місяць завершення року в кількох різнихформулах.
Фінансовий місяць
=IF([Місяць]<= 6, 6+[Місяць], [Місяць]- 6)
У цій формулі ми вказуємо, чи значення [Місяць] менше або дорівнює 6, потім 6 і додайте значення з місяця, в іншому разі відніміть 6 від значення [Місяць].
Фінансовий квартал
=INT(([ФінансовийMonth]+2)/3)
Формула, що використовується для FiscalQuarter, значно збігалася з формулою за квартал у нашому календарного році. Єдина різниця полягає в тому, що ми вказали [FiscalMonth] замість [Month].
Свята або спеціальні дати
Ви можете додати стовпець дат, у якому вказано, що певні дати – свята, або іншу спеціальну дату. Наприклад, можна підсумувати підсумки збуту за новий рік, додавши поле "Свято" до зведеної таблиці як роздільник або фільтр. В інших випадках ви можете виключити ці дати з інших стовпців дат або в мірі.
Включно з святами або особливими днями досить просто. Ви можете створити таблицю 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.2016.
Тиждень
Це значення дає нам номер тижня, починаючи з першого повного тижня фінансового року. У цьому прикладі перший повний тиждень починається в неділю, тому перший повний тиждень першого фінансового року в таблиці Календар насправді починається 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-денним періодом фінансового року.
Зв’язки
Створивши таблицю дат у моделі даних, почніть переглядати дані у зведених таблицях і звітах, а також агрегувати дані на основі стовпців у таблиці вимірів дат, потрібно створити зв'язок між таблицею фактів із даними транзакцій і таблицею дат.
Оскільки потрібно створити зв'язок на основі дат, потрібно створити цей зв'язок між стовпцями, значення яких мають тип даних "Дата й час".
Для кожного значення дати в таблиці фактів пов'язаний стовпець підстановки в таблиці дат має містити відповідні значення. Наприклад, рядок (запис транзакції) у таблиці фактів "Продажі" зі значенням 15.08.2012 у стовпці "DateKey" має містити відповідне значення в пов'язаному стовпці "Дата" в таблиці дати (з іменем "Календар"). Це одна з найважливіших причин, через яку стовпець дат у таблиці дат має містити несуміжний діапазон дат, який може містити будь-яку можливу дату в таблиці фактів.
Примітка.: Хоча стовпець дат у кожній таблиці має мати однаковий тип даних (Date), формат кожного стовпця не має значення.
Примітка.: Якщо надбудова Power Pivot не дає змоги створювати зв'язки між двома таблицями, поля дати можуть не зберігати дату й час до того самого рівня точності. Залежно від форматування стовпця значення можуть виглядати однаково, але зберігатися інакше. Дізнайтеся більше про те, як працювати з часом.
Примітка.: Уникайте використання цілих ключів у зв'язках. Під час імпорту даних із реляційного джерела даних часто стовпці дати й часу представляються захватом ключа, який є цілим числом, що позначає унікальну дату. У надбудові Power Pivot не слід створювати зв'язки за допомогою клавіш із цілими значеннями дати й часу, а натомість використовуйте стовпці з унікальними значеннями з типом даних "Дата". Хоча використання захватних ключів вважається практичним порадою в традиційних сховищах даних, у надбудові Power Pivot не потрібні цілі ключі, що може ускладнити групування значень у зведених таблицях за різними періодами дат.
Якщо під час спроби створити зв'язок з'являється помилка Невідповідність типу, можливо, стовпець у таблиці фактів не є типом даних "Дата". Це може статися, якщо надбудова Power Pivot не може автоматично перетворити недатний тип даних (зазвичай це текстовий тип даних) на дату. Ви все одно можете використовувати стовпець у своїй таблиці фактів, але вам доведеться перетворити дані за допомогою формули DAX у новому обчислюваному стовпці. Відомості про те, як перетворити дати типу текстових даних на тип даних "Дата" далі в додачу.
Кілька зв'язків
У деяких випадках потрібно створити кілька зв'язків або створити кілька таблиць дат. Наприклад, якщо таблиця фактів "Збут" містить кілька полів дати, як-от DateKey, ShipDate і ReturnDate, усі вони можуть мати зв'язки з полем "Дата" в таблиці дат календаря, але лише одне з них може бути активним зв'язком. У цьому випадку, оскільки DateKey представляє дату транзакції, і тому найважливіша дата найкраще використовувати як активний зв'язок. Інші неактивні зв'язки.
Наведена нижче зведена таблиця обчислює загальний обсяг збуту за фінансовим роком і звітним кварталом. Міра "Загальний обсяг продажів" із формулою Загальний обсяг продажів:=SUM([Обсяг продажів])розміщено в полях VALUES, а поля FiscalYear і FiscalQuarter із таблиці дат календаря розміщуються в рядках.
Ця пряма зведена таблиця працює правильно, тому що ми хочемо підсумувати загальний обсяг збуту за дату транзакції в DateKey. Наша міра "Загальний обсяг продажів" використовує дати в dateKey та підсумовано за фінансовим роком і фінансовим кварталом, оскільки існує зв'язок між таблицею DateKey в таблиці "Збут" і стовпцем Дата в таблиці дат календаря.
Неактивні зв'язки
Але що робити, якщо потрібно підсумувати загальний обсяг збуту не за датою транзакції, а за датою відвантаження? Нам потрібен зв'язок між стовпцем "Дата доставки" в таблиці "Збут" і стовпцем "Дата" в таблиці "Календар". Якщо цей зв'язок не створено, агрегації завжди базуються на даті транзакції. Проте в нас може бути кілька зв'язків, хоча лише один може бути активним, а оскільки дата транзакції найважливіша, вона отримує активний зв'язок із таблицею Календар.
У цьому випадку функція "Дата доставки" має неактивний зв'язок, тому будь-яка формула міри, створена для агрегації даних на основі дат відвантаження, має визначати неактивний зв'язок за допомогою функції USERELATIONSHIP.
Наприклад, оскільки стовпець "Дата доставки" в таблиці "Збут" і "Дата" в таблиці "Календар" містить неактивний зв'язок, можна створити міру, щоб підсумувати загальний обсяг збуту за датою відвантаження. Для визначення зв'язку використовується така формула:
Total Sales by Ship Date:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))
У цій формулі просто вказується: обчислення суми для обсягу продажів, але фільтрування за допомогою зв'язку між стовпцем "Дата доставки" в таблиці "Збут" і стовпцем "Дата" в таблиці "Календар".
Тепер, якщо ми створимо зведену таблицю та ввели вимір "Загальний обсяг продажів за датою доставки" в значеннях "ЗНАЧЕННЯ" та "Фінансовий рік" і "Фінансовий квартал" у рядках, в рядках відображається однаковий загальний підсумок, а всі інші суми суми для фінансового року та фінансового кварталу відрізняються, оскільки вони базуються на даті відвантаження, а не даті транзакції.
Використовуючи неактивні зв'язки, можна використовувати лише одну таблицю дат, але для цього потрібні будь-які міри (наприклад, "Загальний обсяг продажів за датою відвантаження"), посилаються на неактивний зв'язок у формулі. Є ще одна альтернатива, тобто використовувати кілька таблиць дат.
Кілька таблиць дат
Інший спосіб працювати з кількома стовпцями дат у таблиці фактів – створити кілька таблиць дат і створити між ними окремі активні зв'язки. Розгляньмо наш приклад таблиці "Продажі". У нас є три стовпці з датами, можливо, ми захочемо об'єдувати дані:
-
DateKey з датою продажу для кожної транзакції.
-
"Дата доставки" – із датою й часом доставки проданих товарів клієнту.
-
Повернуто – дата й час отримання одного або кількох повернених елементів.
Пам'ятайте, що найважливіше поле DateKey з датою транзакції. Ми робитимемо більшість наших агрегацій на основі цих дат, тому, напевно, ми хочемо зв'язок між ним і стовпцем Дата в таблиці календаря. Якщо не потрібно створювати неактивні зв'язки між полями "Дата доставки" та "Дата" в таблиці "Календар", тому ми можемо створити додаткові таблиці дат для дати відвантаження та дати повернення. Потім ми можемо створити активні зв'язки між ними.
У цьому прикладі ми створили іншу таблицю дат під назвою "ShipCalendar". Це, звісно, означає створення додаткових стовпців дат і оскільки ці стовпці дат в іншій таблиці дат, потрібно назвати їх таким чином, щоб вони відрізняли їх від однакових стовпців у таблиці календаря. Наприклад, ми створили стовпці "ShipYear", "ShipMonth", "ShipQuarter" тощо.
Якщо ми створимо зведену таблицю та встановили для неї показник Загальний обсяг продажів у значеннях VALUES і ShipFiscalYear і ShipFiscalQuarter у рядках, ми побачимо такі самі результати, що й під час створення неактивного зв'язку та спеціального обчислюваного поля "Загальний обсяг продажів за датою доставки".
Кожен із цих підходів потребує ретельного розгляду. Якщо використовується кілька зв'язків з однією таблицею дат, можливо, доведеться створити спеціальні міри, які передаватимуться неактивні зв'язки за допомогою функції USERELATIONSHIP. З іншого боку, створення кількох таблиць дат у списку полів може бути незручним, а тому що в моделі даних є більше таблиць, знадобитися більше пам'яті. Поекспериментуйте з найкращим варіантом.
Властивість «Таблиця дат»
Властивість Date Table установлює метадані, необхідні Time-Intelligence такими функціями, як TOTALYTD, PREVIOUSMONTH і DATESBETWEEN, для правильного функціонування. Коли обчислення виконується за допомогою однієї з цих функцій, обробник формул Power Pivot знає, куди перейти, щоб отримати потрібні дати.
Попередження!: Якщо цю властивість не задано, міри, які Time-Intelligence функції DAX, можуть повертати неправильні результати.
Установивши властивість Таблиця дат, ви вказуєте в ній таблицю дат і стовпець дат типу даних Date (datetime).
Інструкції: установлення властивості "Таблиця дат"
-
У вікні PowerPivot виберіть таблицю Календар.
-
На вкладці Конструктор натисніть кнопку Позначити як таблицю дат.
-
У діалоговому вікні Позначити як таблицю дат виберіть стовпець з унікальними значеннями та тип даних "Дата".
Робота з часом
Усі значення дат із типом даних "Дата" в Excel SQL Server насправді є числом. До цього числа входять цифри, які посилаються на час. У багатьох випадках час для кожного рядка – північ. Наприклад, якщо поле DateTimeKey в таблиці фактів "Збут" має значення, наприклад 19.10.2010 12:00:00, це означає, що значення відносяться до рівня точності дня. Якщо значення поля DateTimeKey мають час, наприклад 19.10.2010 08:44:00, це означає, що значення дорівнює рівні точності хвилин. Значення також можуть бути до точності рівня годин або навіть секунд точності. Рівень точності значення часу суттєво вплине на спосіб створення таблиці дат і зв'язків між нею та таблицею фактів.
Потрібно визначити, чи агрегуватимуться дані до рівня точності дня або до рівня точності часу. Іншими словами, можливо, вам знадобиться використати стовпці в таблиці дат "Ранок", "Після обіду" або "Година" як поля дати часу в областях "Рядок", "Стовпець" або "Фільтр" зведеної таблиці.
Примітка.: Дні – це найменша одиниця часу, з якою можуть працювати функції часового інтелекту DAX. Якщо вам не потрібно працювати зі значеннями часу, слід зменшити точність даних, щоб використовувати дні як мінімальну одиницю.
Якщо ви хочете агрегувати дані на рівні часу, вам знадобиться стовпець дат із часом. Насправді, знадобиться стовпець дат з одним рядком на кожну годину або навіть кожну хвилину, щодня, на кожен рік у діапазоні дат. Це тому, що, щоб створити зв'язок між стовпцем DateTimeKey в таблиці фактів і стовпцем дати в таблиці дат, потрібно мати відповідні значення. Як ви можете собі уявити, якщо включити багато років, це може створити дуже велику таблицю дат.
У більшості випадків потрібно об'єднувати дані лише за день. Іншими словами, для полів у областях "Рядок", "Стовпець" і "Фільтр" використовуються такі стовпці, як "Рік", "Місяць", "Тиждень" або "День тижня". У цьому випадку стовпець дат у таблиці дат має містити лише один рядок для кожного дня року, як описано вище.
Якщо стовпець дат містить точність рівня часу, але ви агрегуватиметесь лише на рівні дня, щоб створити зв'язок між таблицею фактів і таблицею дат, може знадобитися змінити таблицю фактів, створивши стовпець, який скорочує значення в стовпці дати до значення дня. Іншими словами, перетворіть значення на зразок 19.10.2010 з 08:44:00до19.10.2010 12:00:00. Потім можна створити зв'язок між цим новим стовпцем і стовпцем дат у таблиці дат, оскільки значення збігаються.
Розгляньмо приклад. На цьому зображенні показано стовпець DateTimeKey в таблиці фактів "Збут". Усім агрегаціям даних у цій таблиці потрібно відстежувати лише рівень дня, використовуючи стовпці в таблиці дат календаря, наприклад Рік, Місяць, Квартал тощо. Час, включений у значення, не стосується лише фактичної дати.
Оскільки нам не потрібно аналізувати ці дані на рівні часу, стовпець Дата в таблиці дат календаря не потрібен, щоб включити один рядок на кожну годину та кожну хвилину кожного року. Таким чином, стовпець Дата в нашій таблиці дат має такий вигляд:
Щоб створити зв'язок між стовпцем DateTimeKey в таблиці "Збут" і стовпцем Дата в таблиці "Календар", можна створити новий обчислюваний стовпець у таблиці фактів "Збут" і скоротити значення дати й часу в стовпці DateTimeKey до значення дати, яке відповідає значенням у стовпці "Дата" в таблиці "Календар". Формула має такий вигляд:
=TRUNC([DateTimeKey];0)
Це дає нам новий стовпець (з іменем DateKey) із датою зі стовпця DateTimeKey та часом 00: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. Значення виглядати так:
Якщо в групі Форматування в групі Форматування відображається вкладка Основне надбудови Power Pivot, це текстовий тип даних.
Не вдалося створити зв'язок між стовпцем DateTime і стовпцем Date у нашій таблиці дат, тому що типи даних не збігаються. Якщо спробувати змінити тип даних на "Дата",з'являється повідомлення про помилку "Невідповідність типу"
У цьому випадку не вдалося перетворити тип даних Power Pivot із тексту на дату. Ми все одно можемо використовувати цей стовпець, але щоб отримати його в дійсний тип даних "Дата", потрібно створити новий стовпець, який аналізує текст і повторно створює його в значення, яке може бути типом даних Power Pivot.
Пам'ятайте, що в розділі Робота з часом вище в цій статті; Якщо аналіз не потрібен для точності на рівні дня, слід перетворити дати в таблиці фактів на рівень точності дня. Пам'ятайте, що значення в новому стовпці будуть на рівні точності дня (за винятком часу). Ми можемо перетворити значення в стовпці DateTime на тип даних дати та видалити рівень точності часу за допомогою такої формули:
=DATE(LEFT([Дата й час];4);MID([Дата й час];6;2);MID([Дата й час];9;2))
Це дає нам новий стовпець (у цьому випадку – Date). Надбудова Power Pivot навіть виявляє значення дати й автоматично встановлює для типу даних значення Дата.
Щоб зберегти точність часу, просто подовжте формулу, включаючи години, хвилини та секунди.
=DATE(LEFT([Дата й час];4);MID([Дата й час];6;2); MID([Дата й час];9;2)) +
TIME(MID([Дата й час];12;2); MID([DateTime];15;2); MID([Дата й час];18;2))
Тепер, коли в нас є стовпець "Дата" з типом даних "Дата", можна створити зв'язок між ним і стовпцем дат у даті.
Додаткові ресурси
Обчислення в надбудові Power Pivot
Короткий посібник. Вивчення основ мови DAX за 30 хвилин
