Таблиците с дати в 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 г.), можете да създадете таблица с дати, която започва на 1.3.2009 г. и включва най-малко всеки ден от всяка финансова година до последната дата на финансовата 2013 година.
Ако ще отчитате и календарната година, и по финансовата година, няма нужда да създавате отделни таблици с дати. Таблицата с дати може да съдържа колони за календарна година, финансова година и дори календар за период от тринадесет тринадцяти седмици. Важното е, че вашата таблица с дати съдържа последователен набор от дати за всички включени години.
Добавяне на таблица с дати към модела на данни
Има няколко начина, по които можете да добавите таблица с дати към вашия модел на данни:
- Импортирайте от релационна база данни или друг източник на данни.
- Създайте таблица с дати в Excel и след това копирайте или свържете към нова таблица в Power Pivot.
- Импортиране от пазара на Microsoft Azure.
Нека разгледаме всяка от тях по-подробно.
Импортиране от релационна база данни
Ако импортирате някои или всички данни от склад за данни или друг тип релационна база данни, много вероятно вече има таблица с дати и релации между нея и останалата част от данните, които импортирате. Датите и форматът вероятно ще съответстват на датите в фактите и датите вероятно започват много в миналото и отиват далеч в бъдещето. Таблицата с дати, която искате да импортирате, може да бъде много голяма и да съдържа диапазон от дати извън това, което ще трябва да включите във вашия модел на данни. Можете да използвате разширените функции за филтриране на съветника за импортиране на таблици в Power Pivot, за да изберете избирателно само датите и конкретните колони, които наистина ви трябват. Това може значително да намали размера на работната книга и да подобри производителността.
Съветник за импортиране на таблици
В повечето случаи няма нужда да създавате допълнителни колони, като например "Финансова година", "Седмица", "Име на месец" и т.н., тъй като те вече ще съществуват в импортираната таблица. В някои случаи обаче, след като сте импортирали таблицата с дати в модела на данни, може да се наложи да създадете допълнителни колони за дати в зависимост от конкретна нужда от отчитане. За щастие това е лесно да се направи с помощта на DAX. По-късно ще научите повече за създаването на полета в таблицата с дати. Всяка среда е различна. Ако не сте сигурни дали вашите източници на данни имат свързана дата или таблица на календара, се обърнете към администратора на базата данни.
Създаване на таблица с дати в Excel
Можете да създадете таблица с дати в Excel и след това да я копирате в нова таблица в модела на данни. Това е наистина доста лесно за изпълнение и ви дава голяма гъвкавост.
Когато създавате таблица с дати в Excel, започвате от една колона с непрекъснат диапазон от дати. След това можете да създадете допълнителни колони, като например година, тримесечие, месец, финансова година, период и т.н. в работния лист на Excel, като използвате формули на Excel, или, след като копирате таблицата в модела на данни, можете да ги създадете като изчисляеми колони. Създаването на допълнителни колони за дати в Power Pivot е описано в раздела "Добавяне на нови колони за дати към таблицата с дати " по-нататък в тази статия.
Как да: Създаване на таблица с дати в Excel и копирането й в модела на данни
В Excel, в празен работен лист, в клетка A1 въведете име на заглавката на колона, за да идентифицирате диапазон от дати. Обикновено това ще бъде нещо като "Дата", "ДатаЧас" или "Ключ за дата".
В клетка A2 въведете начална дата. Например 1.1.2010 г.
Щракнете върху манипулатора за попълване и го плъзнете надолу до номер на ред, който включва крайната дата. Например 31.12.2016 г.
Изберете всички редове в колоната " Дата " (включително името на заглавката в клетка A1).
В групата "Стилове " щракнете върху "Форматиране като таблица" и след това изберете стил.
В диалоговия прозорец "Форматиране като таблица" щракнете върху OK.
Копирайте всички редове, включително заглавния.
В Power Pivot, в раздела "Начало" щракнете върху "Постави".
В "Paste Preview>Table Name" въведете име, например Date или Calendar. Оставете отметнато "Използвай първия ред като заглавки на колони"и след това щракнете върху OK.
Новата таблица с дати (наречена в този пример Calendar) в Power Pivot изглежда така:
Забележка
Можете също да създадете свързана таблица с помощта на "Добавяне към модела на данни". Това обаче прави вашата работна книга ненужно голяма, тъй като работната книга има две версии на таблицата с дати; един в Excel и един в Power Pivot.
Забележка
Име , дата е ключова дума в Power Pivot. Ако дадете име на таблицата, която създавате, в Power Pivot "Дата", тогава ще трябва да оградите името на таблицата с единични кавички във всички DAX формули, които препращат към нея в аргумент. Всички примерни, изображения и формули в тази статия се отнасят до таблица с дати, създадена в Power Pivot с име Calendar.
Сега имате таблица с дати в модела на данните. Можете да добавите нови колони за дата, като година, месец и др. с помощта на DAX.
Добавяне на нови колони за дата в таблицата с дати
Таблица с дати с една колона за дата, която има по един ред за всеки ден за всяка година, е важна за определяне на всички дати в диапазон от дати. Тя е необходима и за създаване на релация между таблицата с факти и таблицата с дати. Но тази колона с една дата с един ред за всеки ден не е полезна, когато анализирате по дати в обобщена таблица или в отчет на Power View. Искате вашата таблица с дати да включва колони, които ви помагат да агрегирате данните за диапазон или група от дати. Например може да искате да сумирате сумите на продажбите по месеци или тримесечия или да създадете мярка, която изчислява годишния ръст. Във всеки от тези случаи таблицата с дати се нуждае от колони за година, месец или тримесечие, които ви позволяват да агрегирате данните си за този период.
Ако сте импортирали таблицата с дати от релационен източник на данни, тя вече може да включва различните типове колони с дати, които искате. В някои случаи може да искате да промените някои от тези колони или да създадете допълнителни колони за дата. Това важи особено ако създадете собствена таблица с дати в Excel и я копирате в модела на данни. За щастие, създаването на нови колони за дата в Power Pivot е доста лесно с функциите за дата и час в DAX.
Съвет
Ако все още не сте работили с DAX, чудесно място да започнете да учите е с "Бърз старт": Научете основите на DAX за 30 минути в Office.com.
Функции за дата и час на DAX
Ако някога сте работили с функции за дата и час във формули на Excel, тогава вероятно сте запознати с функциите за дата и час. Въпреки че тези функции са подобни на аналогичните си функции в Excel, има някои важни разлики:
- Функциите за дата и час на DAX използват тип данни за дата/час.
- Те могат да вземат стойности от дадена колона като аргумент.
- Те могат да бъдат използвани за връщане и/или манипулиране на стойности на дати.
Тези функции често се използват при създаване на колони с дата по избор в таблица с дати, така че е важно да ги разберете. Ще използваме редица от тези функции, за да създадем колони за Year, Quarter, FiscalMonth и т.н.
Забележка
Функциите за дата и час в DAX не са същите като функциите за интелигентно време. Научете повече за интелигентното време в Power Pivot в Excel.
DAX включва следните функции за дата и час:
- ДАТА
- DATEVALUE
- НА СЛЕДВАЩИЯ ДЕН
- EDATE
- EOMONTH
- HOUR
- MINUTE
- MONTH
- NOW
- SECOND
- TIME
- TIMEVALUE
- ДНЕС
- WEEKDAY
- WEEKNUM
- YEAR
- YEARFRAC
Има много други функции на DAX, които можете също да използвате във вашите формули. Например много от формулите, описани тук, използват математически и тригонометрични функции , като например MOD и TRUNC, логически функции , като например IF, и текстови функции , като FORMAT За повече информация за други функции на DAX вж. раздела "Допълнителни ресурси " по-нататък в тази статия.
Примери на формули за календарна година
Следващите примери описват формули, използвани за създаване на допълнителни колони в таблица с дати с име Calendar. Една колона, наречена "Дата", вече съществува и съдържа последователен диапазон от дати от 1.1.2010 г. до 31.12.2016 г.
Година
=YEAR([дата])
В тази формула функцията YEAR връща годината от стойността в колоната "Дата". Тъй като стойността в колоната "Дата" е от тип данни за дата и час, функцията YEAR знае как да върне годината от нея.
Месец
=MONTH([дата])
В тази формула, подобно на функцията YEAR, можем просто да използваме функцията MONTH , за да върнем стойност за месец от колоната "Дата".
Тримесечие
=INT(([месец]+2)/3)
В тази формула използваме функцията INT , за да върнем стойност на дата като цяло число. Аргументът, който задаваме за функцията INT, е стойността от колоната "Месец", добавете 2 и след това разделете това на 3, за да получим нашето тримесечие от 1 до 4.
Име на месец
=FORMAT([дата];"мммм")
В тази формула, за да получим името на месеца, използваме функцията FORMAT , за да конвертираме числова стойност от колоната "Дата" в текст. Задаваме колоната "Дата" като първи аргумент и след това формата; Искаме името на месеца да показва всички знаци, затова използваме "мммм". Резултатът ни изглежда така:
Ако искаме да върнем името на месеца, съкратено до три букви, бихме могли да използваме "ммм" в аргумента за форматиране.
Ден от седмицата
=FORMAT([дата];"ддд")
В тази формула използваме функцията FORMAT, за да получим името на деня. Тъй като просто искаме съкратено име на деня, задаваме "ддд" в аргумента за форматиране.
Примерна обобщена таблица
След като имате полета за дати като "Година", "Тримесечие", "Месец" и т. н., можете да ги използвате в обобщена таблица или отчет. Например следващото изображение показва полето "Сума на продажбите" от таблицата "Факти за продажбите" в VALUES и "Година" и "Тримесечие" от таблицата с измерения Calendar в ROWS. SalesAmount е агрегиран за контекста за година и тримесечие.
Примери на формули за финансова година
Финансова година
=IF([Месец]<= 6;[Година];[Година]+1)
В този пример финансовата година започва на 1 юли.
Няма функция, която да извлече финансова година от стойност на дата, защото началната и крайната дата на финансовата година често са различни от тези на календарната година. За да получим финансовата година, първо използваме функция IF , за да проверим дали стойността за "месец" е по-малка или равна на 6. Във втория аргумент, ако стойността за "месец" е по-малка или равна на 6, връща се стойността от колоната "Година". Ако не, върнете стойността от "Година" и добавете 1.
Друг начин да зададете стойност за края на месеца за финансовата година е да създадете мярка, която просто задава месеца. Например FYE:=6. След това можете да препращате към името на мярката вместо номера на месеца. Например =IF([Месец]<=[ГОДИНА];[Година];[Година]+1). Това предоставя по-голяма гъвкавост при препращане към края на месеца на финансовата година в няколко различни формули.
Финансов месец
=IF([Месец]<= 6; 6+[Месец]; [Месец]- 6)
В тази формула задаваме, ако стойността за [Месец] е по-малка или равна на 6, тогава вземете 6 и добавете стойността от "Месец", в противен случай извадете 6 от стойността от [Месец].
Финансово тримесечие
=INT(([FiscalMonth]+2)/3)
Формулата, която използваме за FiscalQuarter, е почти същата като за Quarter през нашата календарна година. Единствената разлика е, че указваме [FiscalMonth] вместо [Month].
Празници или специални дати
Може да искате да включите колона с дати, която посочва, че определени дати са празници или друга специална дата. Например може да искате да сумирате общите суми на продажбите за Нова година, като добавите поле за празник в обобщена таблица, като сегментатор или филтър. В други случаи може да искате да изключите тези дати от други колони с дати или в мярка.
Включването на празници или специални дни е съвсем просто. Можете да създадете таблица в Excel, която съдържа датите, които искате да включите. След това можете да копирате или използвате "Добавяне към модела на данни", за да го добавите към модела на данни като свързана таблица. В повечето случаи не е необходимо да създавате релация между таблицата и таблицата в Calendar. Всички формули, които се отнасят към него, могат да използват функцията LOOKUPVALUE , за да върнат стойности.
По-долу е даден пример за таблица, създадена в Excel, която включва празниците, които трябва да се добавят към таблицата с дати:
| Дата | Празник |
|---|---|
| 1/1/2010 | Нова година |
| 11/25/2010 | Ден на благодарността |
| 12/25/2010 | Коледа |
| 1.01.2011 | Нова година |
| 11/24/2011 | Ден на благодарността |
| 12/25/2011 | Коледа |
| 1.01.2012 | Нова година |
| 22.11.2012 | Ден на благодарността |
| 12/25/2012 | Коледа |
| 1/1/2013 | Нова година |
| 11/28/2013 | Ден на благодарността |
| 12/25/2013 | Коледа |
| 11/27/2014 | Ден на благодарността |
| 12/25/2014 | Коледа |
| 1.1.2014 г. | Нова година |
| 11/27/2014 | Ден на благодарността |
| 12/25/2014 | Коледа |
| 1/1/2015 | Нова година |
| 11/26/2014 | Ден на благодарността |
| 12/25/2015 | Коледа |
| 1.01.2016 | Нова година |
| 11/24/2016 | Ден на благодарността |
| 12/25/2016 | Коледа |
В таблицата с дати създаваме колона, наречена "Празник ", и използваме формула, подобна на тази:
=LOOKUPVALUE(Holidays[Holiday];Holidays[date];Calendar[date])
Нека разгледаме тази формула по-внимателно.
Използваме функцията LOOKUPVALUE, за да получим стойности от колоната "Празници" в таблицата "Празници". В първия аргумент задаваме колоната, където ще бъде стойността на резултата. Задаваме колоната "Празници " в таблицата "Празници ", защото това е стойността, която искаме да върне.
=LOOKUPVALUE(Holidays[Holiday];Holidays[date];Calendar[date])
След това задаваме втория аргумент – колоната за търсене, която съдържа датите, които искаме да търсим. Задаваме колоната " Дата " в таблицата " Празници " така:
=LOOKUPVALUE(Holidays[Holiday];Holidays[date];Calendar[date])
Накрая задаваме колоната в нашата таблица в Calendar, която съдържа датите, които искаме да търсим в таблицата с празници. Това, разбира се, е колоната "Дата" в таблицата на Calendar.
=LOOKUPVALUE(Holidays[Holiday];Holidays[date];Calendar[date])
Колоната "Празници" ще върне името на празника за всеки ред, който има стойност на дата, която съответства на дата в таблицата "Празници".
Календар по избор - тринадесет периода и четири седмици
Някои организации, като например търговията на дребно или ресторантьорството, често отчитат различни периоди, като например тринадесет периода от четири седмици. При календар от тринадесет и четири седмици всеки период е 28 дни; следователно всеки период съдържа четири понеделника, четири вторника, четири сряди и т.н. Всеки период съдържа един и същ брой дни и обикновено празниците попадат в един и същ период всяка година. Можете да изберете да започнете период във всеки ден от седмицата. Точно както при датите в календарна или финансова година, можете да използвате DAX, за да създадете допълнителни колони с дати по избор.
В примерите по-долу първият пълен период започва през първата неделя на финансовата година. В този случай финансовата година започва на 1.7.
Седмица
Тази стойност ни дава номера на седмицата, започвайки от първата пълна седмица на финансовата година. В този пример първата пълна седмица започва в неделя, т.е. първата пълна седмица на първата финансова година в таблицата Calendar започва на 4.7.2010 г. и продължава до последната пълна седмица в таблицата Calendar. Макар че тази стойност сама по себе си не е толкова полезна за анализа, тя е необходимо да се изчисли, за да се използва в други формули за период от 28 дни.
=INT([дата]-40356)/7)
Нека разгледаме тази формула по-внимателно.
Първо създаваме формула, която връща стойности от колоната "Дата" като цяло число, ето така:
=INT([дата])
След това искаме да търсим първата неделя от първата финансова година. Виждаме, че е 04.07.2010 г.
Сега извадете 40356 (което е цялото число за 27.06.2010, последната неделя от предишната финансова година) от тази стойност, за да получите броя на дните от началото на дните в нашата таблица в Calendar, ето така:
=INT([дата]-40356)
След това разделете резултата на 7 (дни в седмицата), ето така:
=INT(([дата]-40356)/7)
Резултатът изглежда така:
Точка
Периодът в този календар по избор съдържа 28 дни и винаги започва в неделя. Тази колона ще върне номера на периода, започващ с първата неделя на първата финансова година.
=INT(([Седмица]+3)/4)
Нека разгледаме тази формула по-внимателно.
Първо създаваме формула, която връща стойност от колоната "Седмица" като цяло число, ето така:
= INT([Седмица])
След това добавете 3 към тази стойност, ето така:
=INT([Седмица]+3)
След това разделете резултата на 4, ето така:
=INT(([Седмица]+3)/4)
Резултатът изглежда така:
Период Финансова година
Тази стойност връща финансовата година за зададен период.
=INT(([Точка]+12)/13)+2008
Нека разгледаме тази формула по-внимателно.
Първо създаваме формула, която връща стойност от Period и събира 12:
=([Точка]+12)
Делим резултата на 13, защото във финансовата година има 13 периода от 28 дни:
=(([Точка]+12)/13)
Добавяме 2010, защото това е първата година в таблицата:
=(([Точка]+12)/13)+2010
И накрая, използваме функцията INT, за да премахнем която и да е част от резултата и да върнем цяло число, когато се раздели на 13, ето така:
= INT(([Точка]+12)/13)+2010
Резултатът изглежда така:
Период във FiscalYear
Тази стойност връща номера на периода 1 – 13, започвайки с първия пълен период (започващ в неделя) на всяка финансова година.
=IF(MOD([Период];13); MOD([Период];13);13)
Тази формула е малко по-сложна, така че първо ще я опишем на език, който разбираме по-добре. Тази формула гласи, разделете стойността от [Period] на 13, за да получите номера на период (1-13) в годината. Ако това число е 0, връща се 13.
Първо създаваме формула, която връща остатъка от стойността от "Период" по 13. Можем да използваме MOD (математически и тригонометрични функции) по следния начин:
= MOD([Точка];13)
Това в по-голямата си част ни дава желания от нас резултат, с изключение на случаите, когато стойността за "Период" е 0, защото тези дати не попадат в първата финансова година, както в първите пет дни в нашата примерна таблица с дати в Calendar. Можем да се погрижим за това с функция IF. В случай, че нашият резултат е 0, връщаме 13, ето така:
= IF(MOD([Период];13);MOD([Период];13);13)
Резултатът изглежда така:
Примерна обобщена таблица
Изображението по-долу показва обобщена таблица с полето SalesAmount от таблицата Sales fact в VALUES и полета PeriodFiscalYear и PeriodInFiscalYear от таблицата с измерения Calendar date в ROWS. SalesAmount е агрегиран за контекста по финансова година и 28-дневен период във финансовата година.
Релации
След като създадете таблица с дати в модела на данните, за да започнете да преглеждате данните в обобщени таблици и отчети и да агрегирате данните въз основа на колоните в таблицата с измерение за дати, трябва да създадете релация между таблицата с факти с данните за транзакциите и таблицата с дати.
Тъй като трябва да създадете релация, базирана на дати, уверете се, че сте създали тази релация между колони, чиито стойности са от тип данни за дата и час (дата).
За всяка стойност на дата в таблицата с факти свързаната колона за справка в таблицата с дати трябва да съдържа съответстващи стойности. Например ред (запис на транзакция) в таблицата с факти за продажбите със стойност 8/15/2012 12:00 AM в колоната "Ключ на дата" трябва да има съответстваща стойност в свързаната колона "Дата" в таблицата с дати (с име Calendar). Това е една от най-важните причини да искате колоната с дати в таблицата с дати да съдържа непрекъснат диапазон от дати, включващ всяка възможна дата в таблицата с факти.
Забележка
Докато колоната с дати във всяка таблица трябва да бъде от един и същ тип данни (Date), форматът на всяка колона няма значение.
Забележка
Ако Power Pivot не ви позволява да създадете релации между двете таблици, полетата за дата може да не съхраняват датата и часа до същото ниво на точност. В зависимост от форматирането на колоните стойностите може да изглеждат еднакво, но да се съхраняват по различен начин. Прочетете повече за работата с времето.
Забележка
Избягвайте използването на целочислени заместващи ключове в релации. Когато импортирате данни от релационен източник на данни, често колоните с дата и час се представят със заместващ ключ, който представлява цяла колона, използвана за представяне на уникална дата. В Power Pivot трябва да избягвате създаването на релации с помощта на цели ключове за дата/час и вместо това да използвате колони, съдържащи уникални стойности с данни от тип дата. Въпреки че използването на заместващи ключове се счита за най-добра практика в традиционните хранилища за данни, целочислените ключове не са необходими в Power Pivot и могат да затруднят групирането на стойности в обобщени таблици по различни периоди на дати.
Ако получите грешка за несъответствие на типове, когато се опитате да създадете релация, това вероятно се дължи на факта, че колоната в таблицата с факти не е от тип данни за дата. Това може да се случи, когато Power Pivot не може автоматично да преобразува данни, които не са дата (обикновено текстови тип данни) в данни от тип дата. Все още можете да използвате колоната във вашата таблица с факти, но ще трябва да преобразувате данните с формула DAX в нова изчисляема колона. Вижте "Конвертиране на дати от текстови типове данни в данни от тип дата " по-нататък в приложението.
Множество релации
В някои случаи може да се наложи да създадете няколко релации или няколко таблици с дати. Ако например в таблицата "Факти за продажбите" има няколко полета за дата, като например DateKey, ShipDate и ReturnDate, всички те могат да имат релации с полето "Дата" в таблицата с дати на Calendar, но само едно от тях може да бъде активна релация. В този случай, тъй като DateKey представлява датата на транзакцията и следователно най-важната дата, това би послужило най-добре като активна релация. Останалите имат неактивни връзки.
Следващата обобщена таблица изчислява общите продажби по финансова година и финансово тримесечие. Мярка, наречена Total Sales, с формулата Total Sales:=SUM([SalesAmount])), се поставя в VALUES, а полетата FiscalYear и FiscalQuarter от таблицата с дати в Calendar се поставят в ROWS.
Тази проста обобщена таблица работи правилно, защото искаме да сумираме общите ни продажби по датата на транзакцията в DateKey. Нашата мярка за общи продажби използва датите в DateKey и се сумира по финансова година и финансово тримесечие, тъй като има релация между DateKey в таблицата Sales и колоната Date в таблицата с дати в Calendar.
Неактивни релации
Но какво ще стане, ако искаме да обобщим нашите продажби не по дата на транзакцията, а по дата на доставката? Нуждаем се от релация между колоната ShipDate в таблицата Sales и колоната Date в таблицата Calendar. Ако не създадем тази релация, нашите агрегирания винаги се основават на датата на транзакцията. Можем обаче да имаме няколко релации, въпреки че само една може да бъде активна, и тъй като датата на транзакцията е най-важната, тя получава активната релация с таблицата Calendar.
В този случай ShipDate има неактивна връзка, така че всяка формула за мярка, създадена за агрегиране на данни въз основа на датите на доставка, трябва да указва неактивната релация с помощта на функцията USERELATIONSHIP .
Например тъй като има неактивна релация между колоната "Дата на експедиране" в таблицата "Продажби" и колоната "Дата" в таблицата "Calendar", можем да създадем мярка, която сумира общите продажби по дата на доставка. Използваме формула, подобна на тази, за да определим релацията, която да се използва:
Общо продажби по дата на експедиране:=CALCULATE(SUM(Продажби[СумаПродажби]), USERELATIONSHIP(Продажби[ДатаЕкспедиране]; Calendar[Дата]))
Тази формула просто гласи: Изчисляване на сума за SalesAmount, но филтриране с помощта на релацията между колоната ShipDate в таблицата Sales и колоната Date в таблицата Calendar.
Сега, ако създадем обобщена таблица и поставим мярката за общи продажби по дата на експедиране в VALUES, а финансова година и финансово тримесечие в РЕДОВЕ, ще видим същата крайна обща сума, но всички други суми за финансовата година и финансово тримесечие са различни, защото се базират на датата на доставката, а не на датата на транзакцията.
Използването на неактивни релации ви позволява да използвате само една таблица с дати, но изисква всички мерки (като "Общи продажби по дата на изпращане") да препращат към неактивната релация във формулата си. Има и друга алтернатива – използване на множество таблици с дати.
Множество таблици с дати
Друг начин за работа с няколко колони с дати в таблица с факти е да създадете няколко таблици с дати и да създадете отделни активни релации между тях. Нека разгледаме отново нашия пример за таблицата с продажби. Имаме три колони с дати, за които може да искаме да агрегираме данните:
- Ключ за дата на дата на продажба за всяка транзакция.
- Дата на експедиране – с датата и часа, когато продадените артикули са изпратени на клиента.
- Дата на връщане – с датата и часа, когато е получен един или повече върнати елементи.
Не забравяйте, че полето DateKey с датата на транзакцията е най-важно. Ще направим повечето от нашите агрегирания въз основа на тези дати, така че със сигурност ще искаме релация между нея и колоната Date в таблицата Calendar. Ако не искаме да създаваме неактивни релации между ShipDate и ReturnDate и полето Date в таблицата Calendar, като по този начин изискваме формули за специални мерки, можем да създадем допълнителни таблици с дати за дата на доставка и дата на връщане. Тогава можем да създадем активни взаимоотношения между тях.
В този пример създадохме друга таблица с дати с име ShipCalendar. Това, разбира се, означава също създаване на допълнителни колони за дата и тъй като тези колони с дати са в различна таблица с дати, искаме да ги наименуваме така, че да ги отличава от същите колони в таблицата Calendar. Например създадохме колони с имена ShipYear, ShipMonth, ShipQuarter и т.н.
Ако създадем обобщената таблица и поставим нашата мярка за общи продажби в VALUES, а ShipFiscalYear и ShipFiscalQuarter в РЕДОВЕ, ще видим същите резултати, които видяхме, когато създадохме неактивна релация и специално изчисляемо поле за общи продажби по дата на доставка.
Всеки от тези подходи изисква внимателно обмисляне. Когато използвате няколко релации с една таблица с дати, може да се наложи да създадете специални мерки за прехвърляне на неактивни релации с помощта на функцията USERELATIONSHIP. От друга страна, създаването на няколко таблици с дати може да е объркващо в списъка на полетата и тъй като имате повече таблици в модела на данни, това ще изисква повече памет. Експериментирайте с това, което е най-добро за вас.
Свойство "Таблица с дати"
Свойството "Таблица с дати" задава метаданните, необходими за правилната работа на Time-Intelligence функции, като например TOTALYTD, PREVIOUSMONTH и DATESBETBET. Когато се извършва изчисление с помощта на една от тези функции, системата за формули на Power Pivot знае къде да отиде за получаване на датите, от които се нуждае.
Предупреждение
Ако това свойство не е зададено, мерките, използващи функции на DAX Time-Intelligence, може да не върнат правилните резултати.
Когато зададете свойството "Таблица с дати", задавате в нея таблица с дати и колона за дати с тип данни "Дата" (дата и час).
Как да: Задаване на свойството "Таблица с дати"
- В прозореца на PowerPivot изберете таблицата на Calendar.
- В раздела " Проектиране " щракнете върху "Маркирай като таблица с дати".
- В диалоговия прозорец "Маркиране като таблица с дати" изберете колона с уникални стойности и тип данни "Дата".
Работа с времето
Всички стойности за дата с тип данни "Дата" в Excel или SQL Server всъщност са числа. В това число има цифри, които препращат към час. В много случаи това време за всеки ред е полунощ. Например ако поле "Ключ на дата/час" в таблица с факти за продажбите има стойности като 19.10.2010 12:00:00 AM, това означава, че стойностите са с точност до нивото на ден. Ако стойностите на полето "DateTimeKey" съдържат включен час, например 19.10.2010 8:44:00 AM, това означава, че стойностите са с минимално ниво на точност. Стойностите може също да са с точност до ниво час или дори до ниво на секунди. Нивото на точност на стойността за час ще окаже значително влияние върху начина, по който създавате вашата таблица с дати, и върху релациите между нея и таблицата с факти.
Трябва да определите дали ще агрегирате вашите данни до ниво на точност за ден, или до ниво прецизност във времето. С други думи, може да искате да използвате колони в таблицата за дати, като например "Сутрин", "Следобед" или "Час", като полета за дата в областите за ред, колона или филтриране на обобщена таблица.
Забележка
Дните са най-малката единица време, с която могат да работят функциите за интелигентно време на DAX. Ако не трябва да работите със стойности за време, трябва да намалите точността на вашите данни, за да използвате дни като единица за минимум.
Ако възнамерявате да агрегирате данните си до ниво време, тогава вашата таблица с дати ще се нуждае от колона с дата с включен час. Всъщност тя ще има нужда от колона с по един ред за всеки час или може би дори от всяка минута от всеки ден, за всяка година в диапазона от дати. Това е така, защото за да създадете отношение между колоната "Ключ на дата/час" в таблицата с факти и колоната с дата в таблицата с дати трябва да имате съответстващи стойности. Както можете да си представите, ако включите много години, това може да направи таблицата с дати много голяма.
В повечето случаи обаче искате да агрегирате данните само за деня. С други думи, ще използвате колони като "Година", "Месец", "Седмица" или "Ден от седмицата" като полета в областите за ред, колона или филтриране на обобщена таблица. В този случай колоната с дати в таблицата с дати трябва да съдържа само по един ред за всеки ден от годината, както описахме по-рано.
Ако вашата колона с дати включва ниво на точност във времето, но ще агрегирате само до ниво на ден, за да създадете релация между таблицата с факти и таблицата с дати, може да се наложи да промените таблицата с факти чрез създаване на нова колона, която съкращава стойностите в колоната с дати до стойност на ден. С други думи, преобразувайте стойност като 19.10.2010 8:44:00 ч. в 19.10.2010 12:00:00 ч. След това можете да създадете релацията между тази нова колона и колоната с дати в таблицата с дати, защото стойностите съвпадат.
Да разгледаме пример. Това изображение показва колона "Ключ на дата/час" в таблицата "Факти за продажбите". Всички агрегирания за данните в тази таблица трябва да са само на ниво ден, като се използват колони в таблицата с дати в Calendar, като година, месец, тримесечие и т.н. Часът, включен в стойността, няма значение, а само действителната дата.
Тъй като не ни е нужно да анализираме тези данни на ниво време, не ни е нужно колоната "Дата" в таблицата с дати на Calendar да включва по един ред за всеки час и всяка минута от всеки ден от всяка година. Колоната "Дата" в нашата таблица с дати изглежда така:
За да създадем релация между колоната "Ключ на дата" в таблицата "Продажби" и колоната "Дата" в таблицата "Calendar", можем да създадем нова изчисляема колона в таблицата "Факти за продажбите" и да използваме функцията TRUNC, за да съкратим стойността на датата и часа в колоната "Ключ на дата" до стойност за дата, която съответства на стойностите в колоната "Дата" в таблицата "Calendar". Нашата формула изглежда така:
=TRUNC([DateTimeKey];0)
Това ни дава нова колона (нарекохме DateKey) с датата от колоната DateTimeKey и час 12:00:00 преди обяд за всеки ред:
Сега можем да създадем релация между тази нова колона (DateKey) и колоната Date в таблицата Calendar.
По същия начин можем да създадем изчисляема колона в таблицата "Продажби", която намалява точността на времето в колоната "Ключ на дата/час" до часовото ниво на точност. В този случай функцията TRUNC няма да работи, но все още можем да използваме други функции за дата и час на DAX, за да извлечем и да конкатенираме нова стойност до ниво на точност от един час. Можем да използваме формула тази:
= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)
Новата ни колона изглежда така:
Ако нашата колона "Дата" в таблицата с дати съдържа стойности с точност до часове, можем да създадем релация между тях.
Правене на датите по-полезни
Много от колоните с дати, които създавате в таблицата с дати, са необходими за други полета, но всъщност не са толкова полезни за анализа. Например полето "Ключ на дата" в таблицата "Продажби", която споменахме и която показахме в тази статия, е важно, защото за всяка транзакция тази транзакция се записва като извършена на определена дата и час. Но от гледна точка на анализа и отчитането това не е чак толкова полезно, защото не можем да го използваме като поле за ред, колона или филтър в обобщена таблица или отчет.
По подобен начин в нашия пример колоната "Дата" в таблицата на Calendar е много полезна, всъщност критична, но не можете да я използвате като измерение в обобщена таблица.
За да бъдат възможно най-полезни таблиците и колоните в тях и да направите навигирането в списъците с полета в обобщена таблица или отчет на Power View по-лесно, е важно да скриете ненужните колони от клиентските инструменти. Можете също да скриете някои таблици. Таблицата "Празници", показана по-горе, съдържа празнични дати, които са важни за определени колони в таблицата Calendar, но не можете да използвате колоните с празници и дати в самите таблици "Празници" като полета в обобщена таблица. Тук отново, за да улесните навигирането в списъците с полета, можете да скриете цялата таблица "Празници".
Друг важен аспект на работата с дати са конвенциите за именуване. Можете да давате имена на таблици и колони в Power Pivot колкото пожелаете. Но имайте предвид, особено ако ще споделяте работната книга с други потребители, добрата конвенция за именуване улеснява идентифицирането на таблици и дати не само в списъците с полета, но също и в Power Pivot и във формулите на DAX.
След като имате таблица с дати в модела на данни, можете да започнете да създавате мерки, които ще ви помогнат да се възползвате максимално от данните. Някои могат да са прости като сумиране на общите суми на продажбите за текущата година, а други могат да бъдат по-сложни, при което трябва да филтрирате по определен диапазон от уникални дати. Научете повече в "Мерки в Power Pivot " и "Функции за интелигентно време".
Приложение
Преобразуване на текстови типове данни дати в данни от тип дата
В някои случаи таблица с факти с данни за транзакции може да съдържа дати от текстов тип. Това означава, че дата, която изглежда като 2012-12-04T11:47:09, в действителност не е дата или поне не е типът датата, която Power Pivot може да разбере. Това всъщност е просто текст, който се чете като дата. За да създадете връзка между колона за дата в таблицата с факти и колона за дата в таблица с дати, и двете колони трябва да са от тип данни " Дата ".
Обикновено когато се опитате да промените типа данни за колона с дати, които са от текстов тип данни, на данни от тип дата, Power Pivot може да интерпретира датите и автоматично да ги преобразува в данни от тип "истинска дата". Ако Power Pivot не може да направи преобразуване на типа данни, ще получите грешка за несъответствие на типове.
Можете обаче да конвертирате датите в истински тип данни за дати. Можете да създадете нова изчисляема колона и да използвате DAX формула, за да анализирате годината, месеца, деня, часа и т.н. от текстовите низове и след това да ги съедините отново по начин, който Power Pivot може да разчете като истинска дата.
В този пример импортирахме таблица с факти, озаглавена "Продажби" в Power Pivot. Съдържа колона, наречена "ДатаЧас". Стойностите изглеждат по следния начин:
Ако погледнем в "Тип на данните" в групата "Форматиране" в раздела "Начало" на Power Pivot, ще видим, че това е текстов тип данни.
Не можем да създадем релация между колоната "ДатаЧас" и колоната "Дата" в нашата таблица с дати, защото типовете данни не съвпадат. Ако се опитаме да променим типа на данните на Date, получаваме грешка за несъответствие на тип:
В този случай Power Pivot не успя да конвертира типа данни от текст в дата. Все още можем да използваме тази колона, но за да я получим в истински тип данни за дата, трябва да създадем нова колона, която анализира текста и го пресъздава в стойност Power Pivot може да направи данни от тип "Дата".
Не забравяйте от раздела "Работа с време" по-горе в тази статия; Освен ако не е необходимо вашият анализ да е с точност по време от деня, трябва да преобразувате датите в таблицата с факти в ниво на точност за ден. Имайки това наум, искаме стойностите в нашата нова колона да са с точност до деня (без часа). Можем както да преобразуваме стойностите в колоната за дата и час в данни от тип дата, така и да премахнем нивото на точност за дата с помощта на следната формула:
=DATE(LEFT([DateTime];4), MID([DateTime];6;2), MID([DateTime];9;2))
Това ни дава нова колона (в този случай с име "Дата"). Power Pivot дори засича стойностите като дати и автоматично задава типа на данните на "Дата".
Ако искаме да запазим нивото на точност за времето, просто разширяваме формулата, така че да включва часовете, минутите и секундите.
=DATE(LEFT([DateTime];4), MID([DateTime];6;2), MID([DateTime];9;2)) +
TIME(MID([DateTime];12;2), MID([DateTime];15;2), MID([DateTime];18;2))
Сега, когато имаме колона "Дата" от типа данни "Дата", можем да създадем релация между нея и колоната за дата в дата.
Допълнителни ресурси
QuickStart: Научете основите на DAX за 30 минути