Таблиците за дата в Power Pivot са от съществено значение за преглеждането и изчисляването на данни с течение на времето. Тази статия предоставя изчерпателно разбиране на таблиците за дата и как можете да ги създавате в Power Pivot. По-специално тази статия описва:
-
Защо таблицата с дати е важна за преглеждането и изчисляването на данни по дати и часове.
-
Как да използвате Power Pivot, за да добавите таблица за дата към модела на данни.
-
Как да създадете нови колони за дата, като например "Година", "Месец" и "Период" в таблица с дати.
-
Как се създават релации между таблици за дата и таблици с факти.
-
Как да работите с времето.
Тази статия е предназначена за потребители, които са нови в Power Pivot. Важно е обаче вече да имате добро разбиране за импортирането на данни, създаването на релации и създаването на изчисляеми колони и мерки.
Тази статия не описва как да използвате DAX Time-Intelligence функции за измерване на формули. За повече информация как да създавате мерки с функциите dax Time Intelligence, вижте Времева интелигентност в Power Pivot в Excel.
Забележка: В Power Pivot имената "мярка" и "изчисляемо поле" са синоними. Използваме мярката за име в тази статия. За повече информация вижте Мерки в Power Pivot.
Съдържание
Разбиране на таблиците за дата
Почти всички анализи на данни включват преглеждане и сравняване на данни по дати и часове. Например може да искате да сумирате сумите на продажбите за последното финансово тримесечие и след това да сравните тези общи суми с други тримесечия или може да искате да изчислите баланс за приключване на месец за сметка. Във всеки от тези случаи използвате дати като начин да групирате и сумирате транзакциите или салдата по продажби за определен период от време.
Отчет на Power View
Таблицата за дата може да съдържа много различни представяния на дати и часове. Например таблицата с дати често ще има колони като "Финансова година", "Месец", "Тримесечие" или "Период", които можете да изберете като полета от списък с полета, когато набивате и филтрирате данните си в обобщени таблици или отчети на Power View.
Списък с полета на Power View
За дата колони като "Година", "Месец" и "Тримесечие", за да включат всички дати в съответния им диапазон, таблицата с дати трябва да има поне една колона с непрекъснат набор от дати. Т.е. тази колона трябва да има по един ред за всеки ден за всяка година, включена в таблицата с дати.
Ако например данните, които искате да прегледате, имат дати от 1 февруари 2010 г. до 30 ноември 2012 г. и докладвате за календарна година, тогава ще искате таблица с дати с поне диапазон от дати от 1 януари 2010 г. до 31 декември 2012 г. Всяка година в таблицата за дата трябва да съдържа всички дни за всяка година. Ако редовно обновявате данните си с по-нови данни, може да искате да изпълните крайната дата до година или две, така че не е необходимо да актуализирате таблицата си за дата с течение на времето.
Таблица "Дата" с непрекъснат набор от дати
Ако докладвате за финансова година, можете да създадете таблица с дати с непрекъснат набор от дати за всяка финансова година. Ако например вашата финансова година започва на 1 март и имате данни за финансови години 2010 нагоре през текущата дата (например във FY 2013), можете да създадете таблица с дати, която започва на 1.3.2009 г. и включва най-малко всеки ден през всяка финансова година до последната дата във финансовата година 2013 г.
Ако ще докладвате както за календарната година, така и за финансовата година, не е необходимо да създавате отделни таблици за дата. Една таблица с дати може да включва колони за календарна година, финансова година и дори календар за тринадесет четириседмичен период. Важното е, че таблицата с дати съдържа непрекъснат набор от дати за всички години.
Добавяне на таблица с дата към модела на данни
Има няколко начина, по които можете да добавите таблица с дати към вашия модел на данни:
-
Импортиране от релационни бази данни или друг източник на данни.
-
Създайте таблица с дати в Excel след което копирайте или свържете към нова таблица в Power Pivot.
-
Импортиране от Microsoft Azure пазара.
Нека разгледаме всяко от тези неща по-внимателно.
Импортиране от релационни бази данни
Ако импортирате някои или всички данни от склад за данни или друг тип релационни бази данни, има вероятност вече да има таблица с дати и релации между нея и останалата част от данните, които импортирате. Датите и форматът вероятно ще съответстват на датите във вашите данни от фактите, а датите вероятно започват добре в миналото и отиват далеч в бъдещето. Таблицата с дати, която искате да импортирате, може да е много голяма и да съдържа диапазон от дати след това, което ще трябва да включите във вашия модел на данни. Можете да използвате разширените функции за филтриране на съветника за импортиране на таблици на Power Pivot, за да изберете избирателно само датите и конкретните колони, от които наистина се нуждаете. Това може значително да намали размера на работната книга и да подобри производителността.
Съветник за импортиране на таблици
В повечето случаи няма нужда да създавате допълнителни колони, като например "Финансова година", "Седмица", "Име на месец" и т.н., тъй като те вече ще съществуват в импортираната таблица. В някои случаи обаче, след като таблицата за дата е импортирана във вашия модел на данни, може да се наложи да създадете допълнителни колони за дата в зависимост от конкретната нужда от отчитане. За щастие, това е лесно да се направи с помощта на DAX. По-късно ще научите повече за създаването на полета на таблица с дати. Всяка среда е различна. Ако не сте сигурни дали вашите източници на данни имат свързана таблица с дата или календар, говорете с администратора на вашата база данни.
Създаване на таблица за дата в Excel
Можете да създадете таблица с дати в Excel след което да я копирате в нова таблица в модела на данни. Това наистина е доста лесно за правене и ви дава много гъвкавост.
Когато създавате таблица с дати в Excel, започвате с една колона с непрекъснат диапазон от дати. След това можете да създадете допълнителни колони, като например "Година", "Тримесечие", "Месец", "Финансова година", "Период" и т.н. в работния лист на Excel, като използвате формули на Excel или, след като копирате таблицата в модела на данни, можете да ги създадете като изчисляеми колони. Създаването на допълнителни колони за дата в Power Pivot е описано в раздела Добавяне на нови колони за дата към таблицата за дата по-нататък в тази статия.
Как да: Създаване на таблица за дата в Excel и да я копирате в модела на данни
-
В Excel, в празен работен лист, в клетка A1въведете име на заглавка на колона, за да идентифицирате диапазон от дати. Обикновено това ще бъде нещо като Дата, Дата и час или DateKey.
-
В клетка A2въведете начална дата. Например 1.1.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, чудесно място за започване на обучението е с QuickStart: Научете основите на DAX в 30 минути на Office.com.
DAX функции за дата и час
Ако някога сте работили с функциите за дата и час във Excel формули, вероятно ще сте запознати с функциите за дата и час. Въпреки че тези функции са подобни на техните колеги в Excel, има някои важни разлики:
-
Функциите dax Date and Time използват тип данни за дата и час.
-
Те могат да приемат стойности от колона като аргумент.
-
Те могат да се използват за връщане и/или манипулиране на стойности за дата.
Тези функции често се използват при създаване на колони за дата по избор в таблица с дати, така че те са важни за разбиране. Ще използваме редица от тези функции, за да създадем колони за "Година", "Тримесечие", "Фискаленмонт" и т.н.
Забележка: Функциите за дата и час в DAX не са същите като функциите за интелигентно време. Научете повече за времевото разузнаване в Power Pivot в Excel 2013 г.
DAX включва следните функции за дата и час:
Има много други DAX функции, които можете да използвате и във формулите си. Например много от формулите, описани тук, използват математически и тригонометрични функции като MOD и TRUNC,Логически функции като IFи текстови функции като FORMAT За повече информация за други функции на DAX вж. раздела Допълнителни ресурси по-нататък в тази статия.
Примери за формули за календарна година
Следващите примери описват формулите, използвани за създаване на допълнителни колони в таблица с дати с име "Календар". Една колона с име Дата вече съществува и съдържа непрекъснат диапазон от дати от 1.1.2010 г. до 31.12.2016 г.
Година
=YEAR([дата])
В тази формула функцията YEAR връща годината от стойността в колоната Дата. Тъй като стойността в колоната Дата е от тип данни за дата и час, функцията YEAR знае как да върне годината от нея.
Месец
=MONTH([дата])
В тази формула, подобно на функцията YEAR, можем просто да използваме функцията MONTH, за да върнем стойност за месец от колоната Дата.
Тримесечие
=INT(([Месец]+2)/3)
В тази формула използваме функцията INT, за да върнем стойност на дата като цяло число. Аргументът, който задаваме за функцията INT, е стойността от колоната Месец, добавете 2 и след това я разделете на 3, за да получите нашето тримесечие, 1 през 4.
Име на месец
=FORMAT([дата]"мммм")
В тази формула, за да получите името на месеца, използваме функцията FORMAT, за да преобразуваме числова стойност от колоната Дата в текст. Задаваме колоната Дата като първи аргумент и след това формата; искаме името на месеца ни да показва всички знаци, така че да използваме "мммм". Нашият резултат изглежда така:
Ако искаме да върнем името на месеца, съкратено до три букви, бихме използвали "мммм" в аргумента формат.
Ден от седмицата
=FORMAT([date]"ddd")
В тази формула използваме функцията FORMAT, за да получите името на деня. Тъй като просто искаме съкратено име на ден, задаваме "ddd" в аргумента формат.
Примерна обобщена таблица
След като имате полета за дати, като например "Година", "Тримесечие", "Месец" и т.н., можете да ги използвате в обобщена таблица или отчет. Например изображението по-долу показва полето SalesAmount от таблицата Sales fact в VALUES и Year and Quarter от таблицата за размер на календара в ROWS. 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, е почти същата като за Тримесечие през нашата календарна година. Единствената разлика е, че задаваме [FiscalMonth] вместо [Месец].
Празници или специални дати
Може да искате да включите колона за дата, която показва, че определени дати са празници или друга специална дата. Например може да искате да сумирате общите суми за продажби за дните от нова година, като добавите поле "Празник" към обобщена таблица, като сегментатор или филтър. В други случаи може да искате да изключите тези дати от други колони за дата или мярка.
Включително празници или специални дни е съвсем проста. Можете да създадете таблица в Excel, която съдържа датите, които искате да включите. След това можете да копирате или използвате "Добавяне към модел на данни", за да го добавите към модела на данни като свързана таблица. В повечето случаи не е необходимо да създавате релация между таблицата и таблицата "Календар". Всички формули, препращащи към нея, могат да използват функцията 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(Празници[Празник],Празници[дата]Календар[дата])
Нека разгледаме тази формула по-внимателно.
Използваме функцията LOOKUPVALUE, за да получаваме стойности от колоната Празник в таблицата Празници. В първия аргумент задаваме колоната, където ще бъде стойността на резултата. Задаваме колоната Празник в таблицата Празници, защото това е стойността, която искаме да се върне.
=LOOKUPVALUE(Празници[Празник];Празници[дата]Календар[дата])
След това задаваме втория аргумент – колоната за търсене, която съдържа датите, които искаме да търсим. Задаваме колоната Дата в таблицата Празници, ето така:
=LOOKUPVALUE(Празници[Празник],Празници[дата];Календар[дата])
И накрая, задаваме колоната в нашата таблица "Календар", която съдържа датите, които искаме да търсим в таблицата Празник. Това, разбира се, е колоната "Дата" в таблицата "Календар".
=LOOKUPVALUE(Празници[Празник],Празници[дата],Календар[дата] )
Колоната Празник ще върне името на празника за всеки ред, който има стойност за дата, която съответства на дата в таблицата Празници.
Календар по избор – тринадесет четириседмичен период
Някои организации, като например търговски или хранителни услуги, често съобщават за различни периоди, като например тринадесет четириседмичен период. С тринадесет календара за четириседмичен период всеки период е 28 дни; Следователно всеки период съдържа четири понеделника, четири вторника, четири сряда и т.н. Всеки период съдържа същия брой дни и обикновено празниците ще попадат в рамките на един и същ период всяка година. Можете да изберете да започнете период на всеки ден от седмицата. Точно както при датите в календар или финансова година, можете да използвате DAX, за да създадете допълнителни колони с дати по избор.
В примерите по-долу първият пълен период започва в първата неделя на финансовата година. В този случай финансовата година започва на 1.7.2011 г.
Седмица
Тази стойност ни дава номера на седмицата, започвайки от първата пълна седмица във финансовата година. В този пример първата пълна седмица започва в неделя, така че първата пълна седмица през първата финансова година в таблицата "Календар" всъщност започва на 4.7.2010 г. и продължава през последната пълна седмица в таблицата "Календар". Макар че самата тази стойност не е толкова полезна при анализа, е необходимо да се изчисли за използване в други формули за период от 28 дни.
=INT([дата]-40356)/7)
Нека разгледаме тази формула по-внимателно.
Първо, създаваме формула, която връща стойности от колоната "Дата" като цяло число, ето така:
=INT([дата])
След това искаме да потърсим първата неделя в първата финансова година. Виждаме, че е 4.07.2010 г.
Сега извадете 40356 (което е цяло число за 27.6.2010 г., последната неделя от предишната финансова година) от тази стойност, за да получите броя на дните от началото на дните в нашата таблица "Календар", ето така:
=INT([дата]-40356)
След това разделете резултата на 7 (дни в седмицата), ето така:
=INT(([дата]-40356)/7)
Резултатът изглежда така:
Точка
Периодът в този календар по избор съдържа 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 полета от таблицата с размери на датата на календара в ROWS. SalesAmount се агрегира за контекста по финансова година и 28-дневен период през финансовата година.
Релации
След като сте създали таблица за дата във вашия модел на данни, за да започнете да преглеждате данните си в обобщени таблици и отчети и да агрегирате данни въз основа на колоните в таблицата с размери за дата, трябва да създадете релация между таблицата с факти с данните за транзакциите и таблицата за дата.
Тъй като трябва да създадете релация въз основа на дати, ще искате да сте сигурни, че създавате тази релация между колоните, чиито стойности са от типа данни datetime (Date).
За всяка стойност на дата в таблицата с факти свързаната справочна колона в таблицата за дата трябва да съдържа съвпадащи стойности. Например ред (запис на транзакция) в таблицата Sales fact със стойност 15.08.2012 12:00 ч. в колоната DateKey трябва да има съответстваща стойност в свързаната колона Дата в таблицата дата (наименувана календар). Това е една от най-важните причини, поради които искате колоната за дата в таблицата с дати да съдържа непрекъснат диапазон от дати, който включва всяка възможна дата във вашата фактова таблица.
Забележка: Докато колоната за дата във всяка таблица трябва да бъде от един и същ тип данни (Дата), форматът на всяка колона няма значение..
Забележка: Ако Power Pivot не ви позволява да създавате релации между двете таблици, полетата за дата може да не съхраняват датата и часа до едно и също ниво на точност. В зависимост от форматирането на колоните стойностите може да изглеждат по един и същ начин, но да се съхраняват по различен начин. Прочетете повече за работата с времето.
Забележка: Избягвайте използването на заместващи ключове с цяло число в релации. Когато импортирате данни от релационния източник на данни, често колоните за дата и час се представят от заместван ключ, който е цяло число, използвано за представяне на уникална дата. В Power Pivot трябва да избягвате създаването на релации с помощта на цели клавиши за дата/час и вместо това да използвате колони, които съдържат уникални стойности с тип данни за дата. Въпреки че използването на заместващи клавиши се счита за най-добра практика в традиционните складове за данни, не са необходими цели ключове в Power Pivot и може да затрудни групирането на стойности в обобщени таблици по различни периоди от дати.
Ако получите грешка при несъответствие на типа, когато се опитвате да създадете релация, това вероятно е така, защото колоната в таблицата с факти не е от тип данни "Дата". Това може да се случи, когато Power Pivot не може автоматично да преобразува нестоен (обикновено текстов тип данни) в тип данни за дата. Все още можете да използвате колоната в таблицата с факти, но ще трябва да конвертирате данните с ФОРМУЛА DAX в нова изчисляема колона. Вижте Конвертиране на дати за тип данни на текст в тип данни за дата по-късно в допълнението.
Множество релации
В някои случаи може да се наложи да създадете няколко релации или да създадете няколко таблици за дата. Ако например има няколко полета за дата в таблицата "Факти за продажби", като например DateKey, ShipDate и ReturnDate, всички те могат да имат релации към полето "Дата" в таблицата "Дата на календара", но само една от тях може да бъде активна релация. В този случай, тъй като DateKey представя датата на транзакцията и следователно най-важната дата, това най-добре ще служи като активна релация. Другите имат неактивни релации.
Следващата обобщена таблица изчислява общите продажби по финансова година и финансово тримесечие. Мярка, наречена Общи продажби, с формулата Общи продажби:=SUM([SalesAmount]), се поставя в VALUES и полетата FiscalYear и FiscalQuarter от таблицата "Дата на календара" се поставят в ROWS.
Тази обобщена таблица за директно препращане работи правилно, защото искаме да сумираме нашите общи продажби по датата на транзакциятав DateKey. Нашата мярка "Общи продажби" използва датите в DateKey и се сумира по финансова година и финансово тримесечие, защото има релация между DateKey в таблицата "Продажби" и колоната "Дата" в таблицата "Дата на календара".
Неактивни релации
Но какво ще стане, ако искаме да сумираме общите си продажби не по дата на транзакцията, а по дата на доставка? Нуждаем се от релация между колоната "Дата на експедиране" в таблицата "Продажби" и колоната "Дата" в таблицата "Календар". Ако не създадем тази релация, нашите агрегации винаги се базират на датата на транзакцията. Въпреки това можем да имаме няколко релации, въпреки че само един може да бъде активен, и тъй като датата на транзакцията е най-важната, тя получава активната релация с таблицата "Календар".
В този случай ShipDate има неактивна релация, така че всяка формула за мярка, създадена за обобщаване на данни на базата на дати на доставка, трябва да задава неактивната релация с помощта на функцията USERELATIONSHIP.
Например тъй като има неактивна релация между колоната "Дата на експедиране" в таблицата "Продажби" и колоната "Дата" в таблицата "Календар", можем да създадем мярка, която сумира общите продажби по дата на експедиране. Използваме формула като тази, за да зададете релацията, която да използвате:
Общо продажби по дата на експедиране:=CALCULATE(SUM(Продажби[SalesAmount]), USERELATIONSHIP(Продажби[Дата на експедиране], Календар[Дата]))
Тази формула просто гласи: Изчисляване на сума за SalesAmount, но филтриране с помощта на релацията между колоната "Дата на експедиране" в таблицата "Продажби" и колоната "Дата" в таблицата "Календар".
Сега, ако създадем обобщена таблица и поставим мярката "Обща сума на продажбите по дата на експедиране" в VALUES и "Финансова година" и "Финансово тримесечие" в ROWS, виждаме една и съща обща сума, но всички други суми за финансовата година и финансово тримесечие са различни, защото се базират на датата на доставка, а не на датата на транзакцията.
Използването на неактивни релации ви позволява да използвате само една таблица с дати, но изисква всички мерки (като например Общи продажби по дата на експедиране), да препращат към неактивната релация в нейната формула. Има и друга алтернатива, т.е. използвайте няколко таблици за дата.
Няколко таблици за дата
Друг начин да работите с няколко колони за дата във вашата таблица с факти е да създадете няколко таблици за дата и да създадете отделни активни релации между тях. Нека разгледаме отново нашия пример за таблица "Продажби". Имаме три колони с дати, на които може да искаме да съберем данни:
-
DateKey с датата на продажба за всяка транзакция.
-
Дата на доставка – с датата и часа, когато продадените артикули са експедирани до клиента.
-
Дата на връщане – с датата и часа, когато е получен един или повече върнати елементи.
Не забравяйте, че полето DateKey с датата на транзакцията е най-важно. Ще направим повечето от нашите агрегации на базата на тези дати, така че със сигурност ще искаме връзка между нея и колоната Дата в таблицата "Календар". Ако не искаме да създаваме неактивни релации между ShipDate и ReturnDate и полето Дата в таблицата Календар, като по този начин изискваме формули за специални мерки, можем да създадем допълнителни таблици за дата на експедиране и дата на връщане. След това можем да създадем активни релации между тях.
В този пример създадохме друга таблица с дати с име ShipCalendar. Това, разбира се, означава и създаване на допълнителни колони за дата и тъй като тези колони за дата са в друга таблица с дати, искаме да ги наименяме по начин, който ги различава от същите колони в таблицата "Календар". Например създадохме колони с име ShipYear, ShipMonth, ShipQuarter и т.н.
Ако създадем нашата обобщена таблица и поставим нашата мярка за общи продажби в VALUES и ShipFiscalYear и ShipFiscalQuarter в ROWS, виждаме същите резултати, които видяхме, когато създадохме неактивна релация и специално изчисляемо поле "Обща сума на продажбите по дата на експедиране".
Всеки от тези подходи изисква внимателно обмисляне. Когато използвате няколко релации с една таблица с дати, може да се наложи да създадете специални мерки, които транзитно неактивни релации с помощта на функцията USERELATIONSHIP. От друга страна създаването на няколко таблици за дата може да е объркващо в списък с полета и тъй като имате повече таблици в модела на данни, това ще изисква повече памет. Експериментирайте с това, което работи най-добре за вас.
Свойство "Таблица за дата"
Свойството Таблица за дата задава метаданните, необходими за Time-Intelligence функции като TOTALYTD, PREVIOUSMONTH и DATESBETWEEN, за да работят правилно. Когато се изпълнява изчисление с помощта на една от тези функции, ядрото за формули на Power Pivot знае къде да отиде, за да получи датите, от които се нуждае.
Предупреждение: Ако това свойство не е зададено, мерките, използващи DAX Time-Intelligence функции, може да не връщат правилни резултати.
Когато зададете свойството Таблица за дата, задавате таблица с дати и колона за дата от типа данни Date (datetime) в него.
Как да: Задаване на свойството Таблица за дата
-
В прозореца PowerPivot изберете таблицата Календар.
-
В раздела Проектиране щракнете върху Маркирай като таблица за дата.
-
В диалоговия прозорец Маркиране като таблица с дата изберете колона с уникални стойности и типа данни "Дата".
Работа с времето
Всички стойности за дата с тип данни "Дата" в Excel или SQL Server всъщност са число. В това число са включени цифри, които препращат към час. В много случаи този път за всеки ред е полунощ. Ако например поле DateTimeKey в таблица с факти за продажби има стойности като 10.10.2010 12:00:00 ч., това означава, че стойностите са до нивото на точност на деня. Ако стойностите на полетата DateTimeKey имат включено време, например 10.10.2010 г. 8:44:00 ч., това означава, че стойностите са до нивото на точност на минутата. Стойностите може също да са до нивото на часовете или дори нивото на точност на секундите. Нивото на точност във времевната стойност ще има значително влияние върху начина, по който създавате таблицата си за дата и релациите между нея и таблицата с факти.
Трябва да определите дали ще съберете данните си до ниво на точност за деня или до ниво на точност във времето. С други думи, може да искате да използвате колони в таблицата си за дата, като полета "Сутрин", "Следобед" или "Час като дата на час" в областите "Ред", "Колона" или "Филтър" на обобщена таблица.
Забележка: Дните са най-малката единица време, с която могат да работят функциите dax Time Intelligence. Ако не е необходимо да работите с времеви стойности, трябва да намалите точността на данните, за да използвате дните като минимална единица.
Ако възнамерявате да агрегират данните си до нивото на часа, тогава таблицата ви за дата ще се нуждае от колона за дата с включено време. Всъщност тя ще се нуждае от колона за дата с по един ред за всеки час или може би дори всяка минута на всеки ден, за всяка година в диапазона от дати. Това е така, защото за да създадете релация между колоната DateTimeKey в таблицата с факти и колоната за дата в таблицата за дата, трябва да имате съответстващи стойности. Както можете да си представите, ако включвате много години, това може да направи много голяма таблица с дати.
В повечето случаи обаче искате да сумите данните си само за деня. С други думи, ще използвате колони като "Година", "Месец", "Седмица" или "Ден от седмицата" като полета в областите "Ред", "Колона" или "Филтър" на обобщена таблица. В този случай колоната за дата в таблицата за дата трябва да съдържа само по един ред за всеки ден в годината, както описахме по-горе.
Ако колоната ви за дата включва ниво на точност по време, но ще агрегирате само до дневно ниво, за да създадете релация между таблицата с факти и таблицата с дати, може да се наложи да промените таблицата с факти, като създадете нова колона, която отрязва стойностите в колоната за дата до стойност за ден. С други думи, преобразувайте стойност като 10.10.2010 г. 8:44:00 ч. в 19.10.2010 г. 12:00:00 ч. След това можете да създадете релация между тази нова колона и колоната за дата в таблицата с дати, защото стойностите съвпадат.
Нека да разгледаме пример. Това изображение показва колона 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и Time Intelligence.
Приложение
Преобразуване на дати за тип данни на текст в тип данни за дата
В някои случаи таблицата с факти с данни за транзакции може да съдържа дати от текстов тип данни. Т.е. дата, която се показва като 2012-12-04T11:47:09, всъщност изобщо не е дата или поне не типът на датата, която Power Pivot може да разбере. Това е просто текст, който чете като дата. За да създадете релация между колона за дата в таблицата с факти и колона за дата в таблица за дата, и двете колони трябва да са от типа данни "Дата".
Обикновено, когато се опитате да промените типа данни за колона с дати, които са тип данни за текст, на тип данни за дата, Power Pivot може да интерпретира датите и да ги конвертира автоматично в тип данни за истинска дата. Ако Power Pivot не може да направи преобразуване на тип данни, ще получите грешка при несъответствие на типа.
Можете обаче все пак да конвертирате датите в тип данни за истинска дата. Можете да създадете нова изчисляема колона и да използвате формула на DAX, за да анализирате годината, месеца, деня, часа и т.н. от текстовите низове и след това да я свържете отново заедно по начин, който Power Pivot може да прочете като истинска дата.
В този пример импортирахме таблица с факти, наречена "Продажби" в Power Pivot. Тя съдържа колона с име DateTime. Стойностите се показват по този начин:
Ако разгледаме "Тип данни" в раздела "Начало" на групата "Форматиране" на Power Pivot, ще видим, че това е тип данни "Текст".
Не можем да създадем релация между колоната DateTime и колоната Дата в нашата таблица с дати, защото типовете данни не съвпадат. Ако се опитаме да променим типа на данните на Дата,получаваме грешка при несъответствие на типа:
В този случай Power Pivot не можа да преобразува типа на данните от текст в дата. Все още можем да използваме тази колона, но за да я превърнем в истински тип данни за дата, трябва да създадем нова колона, която анализира текста и я създава отново в стойност, която Power Pivot може да направи тип данни "Дата".
Не забравяйте, че от секцията Работа с време по-горе в тази статия; освен ако не е необходимо вашият анализ да бъде в ниво на точност по време на деня, трябва да преобразувате датите във вашата таблица с факти в ниво на точност за деня. Предвид това искаме стойностите в нашата нова колона да бъдат на нивото на точност на деня (с изключение на времето). И двете можем да преобразуваме стойностите в колоната DateTime в тип данни за дата и да премахнем нивото на точност на часа със следната формула:
=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 минути
