Tabele dat w dodatku Power Pivot są niezbędne do przeglądania i obliczania danych w czasie. Ten artykuł zawiera dokładny opis tabel dat i sposobu ich tworzenia w dodatku Power Pivot. W szczególności w tym artykule opisano:
-
W przypadku przeglądania i obliczania danych według dat i godzin tabela dat jest ważna.
-
Jak za pomocą dodatku Power Pivot dodać tabelę dat do modelu danych.
-
Sposób tworzenia nowych kolumn dat, takich jak Rok, Miesiąc i Okres w tabeli dat.
-
Tworzenie relacji między tabelami dat a tabelami faktów.
-
Jak pracować z czasem.
Ten artykuł jest przeznaczony dla użytkowników nowych użytkowników dodatku Power Pivot. Jednak ważne jest, aby już mieć dobrą wiedzę na temat importowania danych, tworzenia relacji oraz tworzenia kolumn obliczeniowych i miar.
W tym artykule nie opisano używania języka DAX w Time-Intelligence miar formuł. Aby uzyskać więcej informacji na temat tworzenia miar za pomocą funkcji analizy czasowej języka DAX, zobacz Analiza czasowa w dodatku Power Pivot w programie Excel.
Uwaga: W dodatku Power Pivot nazwy "miara" i "pole obliczeniowe" są synonimami. W tym artykule używamy miary nazw. Aby uzyskać więcej informacji, zobacz Miary w dodatku Power Pivot.
Spis treści
Opis tabel dat
Niemal każda analiza danych polega na przeglądaniu i porównywaniu danych w danych z datami i godzinami. Możesz na przykład zechcieć zsumować kwoty sprzedaży za przeszły kwartał obrachunkowy, a następnie porównać te sumy z innymi kwartałami lub obliczyć miesięczne saldo końcowe dla konta. W każdym z tych przypadków daty są służące do grupowania i agregowania transakcji sprzedaży lub sald dla określonego okresu w czasie.
Power View raportu
Tabela dat może zawierać wiele różnych reprezentacji dat i godzin. Na przykład tabela dat często będzie zawierała kolumny, takie jak Rok obrachunkowy, Miesiąc, Kwartał lub Okres, które można wybrać jako pola z listy pól podczas pracy z tabelami przestawnym i filtrowaniem ich w Power View przestawnej.
Power View lista pól
Aby kolumny dat, takie jak Rok, Miesiąc i Kwartał, zawierały wszystkie daty w odpowiednim zakresie, tabela dat musi zawierać co najmniej jedną kolumnę z ciągłym zestawem dat. Oznacza to, że ta kolumna musi zawierać jeden wiersz na każdy dzień każdego roku w tabeli dat.
Jeśli na przykład dane, które chcesz przeglądać, mają daty od 1 lutego 2010 do 30 listopada 2012 i raport używasz roku kalendarzowego, potrzebujesz tabeli dat z zakresem dat od 1 stycznia 2010 do 31 grudnia 2012. Każdy rok w tabeli dat musi zawierać wszystkie dni dla każdego roku. Jeśli dane będą regularnie odświeżane przy użyciu nowych danych, warto zdać datę końcową o rok lub dwa, więc nie musisz aktualizować tabeli dat wraz z czasem.
Tabela dat z ciągłym zestawem dat
Raportując rok obrachunkowy, można utworzyć tabelę dat z ciągłym zestawem dat dla każdego roku obrachunkowego. Jeśli na przykład rok obrachunkowy rozpoczyna się 1 marca i masz dane za lata obrachunkowe 2010 za okres od bieżącej daty (na przykład ROK 2013), możesz utworzyć tabelę dat, która zaczyna się 2009-03-01 i zawiera co najmniej codziennie każdy rok obrachunkowy do ostatniej daty roku obrachunkowego 2013.
Jeśli raport będzie raportować zarówno rok kalendarzowy, jak i rok obrachunkowy, nie trzeba tworzyć oddzielnych tabel dat. Jedna tabela dat może zawierać kolumny dla roku kalendarzowego, roku obrachunkowego, a nawet trzynaście kalendarzy okresów czterotygodniowych. Ważne jest, aby tabela dat zawierała ciągły zestaw dat dla wszystkich uwzględnionych lat.
Dodawanie tabeli dat do modelu danych
Istnieje kilka sposobów dodawania tabeli dat do modelu danych:
-
Zaimportuj dane z relacyjnej bazy danych lub innego źródła danych.
-
Utwórz tabelę dat w programie Excel, a następnie skopiuj lub utwórz link do nowej tabeli w dodatku Power Pivot.
-
Zaimportuj z Microsoft Azure Marketplace.
Przyjrzyjmy się bliżej każdemu z nich.
Importowanie z relacyjnej bazy danych
W przypadku importowania części lub wszystkich danych z magazynu danych lub innego typu relacyjnej bazy danych istnieje prawdopodobieństwo, że istnieje już tabela dat i relacje między nimi a resztą importowanych danych. Daty i format będą prawdopodobnie zgodne z datami w danych faktycznie, a daty te prawdopodobnie zaczynają się dobrze w przeszłości i wychodzą daleko w przyszłość. Tabela dat, którą chcesz zaimportować, może być bardzo duża i zawierać zakres dat wykraczających poza zakres, który należy uwzględnić w modelu danych. Za pomocą zaawansowanych funkcji filtrowania kreatora importu tabeli dodatku Power Pivot można wybrać selektywnie tylko daty i konkretne kolumny, których potrzebujesz. Może to znacząco zmniejszyć rozmiar skoroszytu i zwiększyć wydajność.
Kreator importu tabeli
W większości przypadków nie trzeba tworzyć żadnych dodatkowych kolumn, takich jak Rok obrachunkowy, Tydzień, Nazwa miesiąca itp., ponieważ będą one już istnieć w importowanej tabeli. Jednak w niektórych przypadkach po zaimportowaniu tabeli dat do modelu danych może być konieczne utworzenie dodatkowych kolumn dat, w zależności od konkretnych potrzeb w zakresie raportowania. Na szczęście można tego łatwo zrobić przy użyciu języka DAX. Dowiedz się więcej o tworzeniu pól tabeli dat w późniejszym czasie. Każde środowisko jest inne. Jeśli nie masz pewności, czy źródła danych mają powiązaną datę lub tabelę kalendarza, porozmawiaj z administratorem bazy danych.
Tworzenie tabeli dat w programie Excel
Możesz utworzyć tabelę dat w programie Excel a następnie skopiować ją do nowej tabeli w modelu danych. Jest to naprawdę dosyć łatwe do zdysymowania i zapewnia dużą elastyczność.
Tworząc tabelę dat w programie Excel, zaczynasz od jednej kolumny z ciągłym zakresem dat. Następnie można utworzyć dodatkowe kolumny, takie jak Rok, Kwartał, Miesiąc, Rok obrachunkowy, Okres itp., w arkuszu programu Excel za pomocą formuł programu Excel lub po skopiowaniu tabeli do modelu danych można utworzyć je jako kolumny obliczeniowe. Tworzenie dodatkowych kolumn dat w dodatku Power Pivot opisano w sekcji Dodawanie nowych kolumn dat do tabeli dat w dalszej części tego artykułu.
Jak to zrobić: tworzenie tabeli dat w programie Excel skopiowanie jej do modelu danych
-
W Excel pustym arkuszu w komórce A1wpisz nazwę nagłówka kolumny, aby zidentyfikować zakres dat. Zazwyczaj może to być typ Date, DateTime lub DateKey.
-
W komórce A2wpisz datę rozpoczęcia. Na przykład 2010-01-01.
-
Kliknij uchwyt wypełniania i przeciągnij go w dół do numeru wiersza, który zawiera datę końcową. Na przykład 2016-12-31.
-
Zaznacz wszystkie wiersze w kolumnie Data (łącznie z nazwą nagłówka w komórce A1).
-
W grupie Style kliknij pozycję Formatuj jako tabelę, a następnie wybierz styl.
-
W oknie dialogowym Formatowanie jako tabeli kliknij przycisk OK.
-
Skopiuj wszystkie wiersze, łącznie z nagłówkiem.
-
W dodatku Power Pivot na karcie Narzędzia główne kliknij pozycję Wklej.
-
W oknie podglądu wklejania > nazwa tabeli wpisz nazwę, taką jak Data lub Kalendarz. Pozostaw zaznaczone pole użyj pierwszego wiersza jako nagłówków kolumn,a następnie kliknij przycisk OK.
Nowa tabela dat (w tym przykładzie o nazwie Kalendarz) w dodatku Power Pivot wygląda następująco:
Uwaga: Możesz również utworzyć tabelę połączona za pomocą funkcji Dodaj do modelu danych. Powoduje to jednak, że skoroszyt jest niepotrzebnie duży, ponieważ skoroszyt ma dwie wersje tabeli dat. jeden w Excel i jeden w dodatku Power Pivot.
Uwaga: Data to słowo kluczowe w dodatku Power Pivot. Jeśli nadasz nazwę tabeli tworzyć w dodatku Power Pivot, należy ją ująć w pojedyncze cudzysłowy w dowolnych formułach języka DAX odwołujących się do tej tabeli w argumentie. Wszystkie przykładowe obrazy i formuły w tym artykule dotyczą tabeli dat utworzonej w dodatku Power Pivot o nazwie Kalendarz.
W modelu danych jest teraz tabela dat. Za pomocą języka DAX możesz dodać nowe kolumny dat, takie jak Rok, Miesiąc itp.
Dodawanie nowych kolumn dat do tabeli dat
Tabela dat z jedną kolumną dat, która ma jeden wiersz na każdy dzień każdego roku, jest ważna podczas definiowania wszystkich dat w zakresie dat. Jest również konieczne w celu utworzenia relacji między tabelą fakt a tabelą dat. Jednak ta pojedyncza kolumna z jednym wierszem na dzień nie jest przydatna podczas analizowania według dat w tabeli przestawnej lub raporcie Power View przestawnej. Chcesz, aby tabela dat zawierała kolumny, które ułatwiają agregowanie danych dla zakresu lub grupy dat. Możesz na przykład zechcieć zsumować kwoty sprzedaży według miesiąca lub kwartału albo utworzyć miarę obliczaną wzrost w ciągu roku. W każdym z tych przypadków tabela dat wymaga kolumn rok, miesiąc lub kwartał, które umożliwiają agregowanie danych dla tego okresu.
Jeśli tabela dat została zaimportowana z relowanego źródła danych, może już zawierać różne typy kolumn dat, które chcesz. W niektórych przypadkach może być trzeba zmodyfikować niektóre z tych kolumn lub utworzyć dodatkowe kolumny dat. Jest to szczególnie ważne, jeśli utworzysz własną tabelę dat w programie Excel i skopiujemy ją do modelu danych. Na szczęście tworzenie nowych kolumn dat w dodatku Power Pivot jest dość łatwe dzięki funkcji daty i czasu w języku DAX.
Porada: Jeśli język DAX nie został jeszcze przez Ciebie pracował, warto rozpocząć naukę za pomocą przewodnika Szybki start: nauka podstaw języka DAX w 30 minut na Office.com.
Funkcje daty i czasu języka DAX
Jeśli pracujesz z funkcjami daty i czasu w Excel, prawdopodobnie zaznajomisz się z funkcjami daty i czasu. Te funkcje są podobne do ich odpowiedników w programach Excel, jednak istnieje kilka istotnych różnic:
-
Funkcje daty i czasu języka DAX używają typu danych data/godzina.
-
Jako argument mogą też zostać podane wartości z kolumny.
-
Za ich pomocą można zwracać wartości dat i (lub) manipulować nimi.
Te funkcje są często używane podczas tworzenia niestandardowych kolumn dat w tabeli dat, więc należy je zrozumieć. Za pomocą kilku tych funkcji utworzymy kolumny dla kolumn Rok, Kwartał, Miesiąc Obrachunkowy i tak dalej.
Uwaga: Funkcje daty i czasu w języku DAX nie są takie same jak funkcje analizy czasowej. Dowiedz się więcej o analizie czasowej w dodatku Power Pivot Excel 2013.
Język DAX zawiera następujące funkcje daty i czasu:
Istnieje również wiele innych funkcji języka DAX, których można używać w formułach. Na przykład wiele opisanych tutaj formuł używa funkcji matematycznych i trygonometrycznych, takich jak MOD i TRUNC,Funkcje logiczne, takie jak JEŻELIi Funkcje tekstowe, takie jak FORMAT. Aby uzyskać więcej informacji o innych funkcjach języka DAX, zobacz sekcję Dodatkowe zasoby w dalszej części tego artykułu.
Przykłady formuł dla roku kalendarzowego
W poniższych przykładach opisano formuły służące do tworzenia dodatkowych kolumn w tabeli dat o nazwie Kalendarz. Jedna kolumna o nazwie Date już istnieje i zawiera ciągły zakres dat od 2010-01-01 do 2016-12-31.
Rok
=ROK([data])
W tej formule funkcja ROK zwraca rok z wartości w kolumnie Data. Ponieważ wartość w kolumnie Data jest typu danych data/dzień, funkcja ROK wie, jak zwrócić z tej kolumny rok.
Miesiąc
=MIESIĄC([data])
W tej formule, podobnie jak w przypadku funkcji ROK, można po prostu użyć funkcji MIESIĄC w celu zwrócenia wartości miesiąca z kolumny Data.
Kwartał
=ZAO INT(([Miesiąc]+2)/3)
W tej formule używamy funkcji ZAO INT, aby zwrócić wartość daty jako liczbę całkowitą. Argumentem funkcji ZAOPT jest wartość z kolumny Miesiąc, dodaj liczbę 2, a następnie podziel ją przez 3, aby uzyskać nasz kwartał, 1 przez 4.
Nazwa miesiąca
=FORMAT([data];"mmmm")
W tej formule w celu uzyskania nazwy miesiąca używamy funkcji FORMAT, aby przekonwertować wartość liczbową z kolumny Data na tekst. Jako pierwszy argument określamy kolumnę Data, a następnie format. Chcemy, aby w nazwie miesiąca wyświetlane są wszystkie znaki, więc użyjemy ciągu "mmmm". Wynik wygląda następująco:
Jeśli chcemy zwrócić nazwę miesiąca skróconą o trzy litery, w argumentie formatu użyjemy ciągu "mmm".
Dzień tygodnia
=FORMAT([data];"ddd")
W tej formule do uzyskania nazwy dnia użyjemy funkcji FORMAT. Ponieważ potrzebujemy tylko skróconej nazwy dnia, w argumentie formatu jest określony tekst "ddd".
Przykładowa tabela przestawna
Gdy masz pola dla dat, takich jak Rok, Kwartał, Miesiąc itp., możesz używać ich w tabeli przestawnej lub raporcie. Na przykład na poniższej ilustracji przedstawiono pole WartośćSprzedazy z tabeli faktów Sprzedaż w polach WARTOŚCI oraz Rok i Kwartał z tabeli wymiarowej Kalendarza w wierszu WIERSZE. WartośćSprzedażyła jest agregowana w kontekście roku i kwartału.
Przykłady formuł dla roku obrachunkowego
Rok obrachunkowy
=JEŻELI([Miesiąc]<= 6;[Rok];[Rok]+1)
W tym przykładzie rok obrachunkowy rozpoczyna się 1 lipca.
Nie ma funkcji, która umożliwia wyodrębnianie roku obrachunkowego z wartości daty, ponieważ daty rozpoczęcia i zakończenia roku obrachunkowego często różnią się od tych w roku kalendarzowym. Aby uzyskać rok obrachunkowy, najpierw użyjemy funkcji JEŻELI w celu przetestowania, czy wartość w miesiącu jest mniejsza niż lub równa 6. W drugim argumentie, jeśli wartość argumentu Miesiąc jest mniejsza niż lub równa 6, zwróć wartość z kolumny Rok. Jeśli nie, zwróć wartość z roku i dodaj 1.
Innym sposobem określenia wartości miesiąca końcowego roku obrachunkowego jest utworzenie miary określającej tylko miesiąc. Na przykład: FYE:=6. Następnie można odwołać się do nazwy miary w miejscu numeru miesiąca. Na przykład =JEŻELI([Miesiąc]<=[ROK];[Rok];[Rok]+1). Zapewnia to większą elastyczność w przypadku odwoływania się do miesiąca końcowego roku obrachunkowego w kilku różnychformułach.
Miesiąc obrachunkowy
=JEŻELI([Miesiąc]<= 6; 6+[Miesiąc];[Miesiąc]-6)
W tej formule określamy, czy wartość [Miesiąc] jest mniejsza niż lub równa 6, to należy przyjąć liczbę 6 i dodać wartość z miesiąca, a w przeciwnym razie odjąć 6 od wartości [Miesiąc].
Kwartał obrachunkowy
=ZAO INT(([Miesiąc Obrachunkowy]+2)/3)
Formuła, która jest dla kwartału obrachunkowego, jest znacznie taka sama jak dla kwartałów w roku kalendarzowym. Jedyna różnica polega na określeniu wartości [Miesiąc Obrachunkowy] zamiast [Miesiąc].
Święta lub specjalne daty
Możesz chcieć dołączyć kolumnę daty, która wskazuje, że niektóre daty to święta lub inne daty specjalne. Na przykład możesz zechcieć zsumować sumy sprzedaży dla nowego dnia lat, dodając pole Święta do tabeli przestawnej jako fragmentator lub filtr. W innych przypadkach możesz chcieć wykluczyć te daty z innych kolumn dat lub ze miary.
Uwzględnienie świąt lub dni specjalnych jest dość proste. Możesz utworzyć tabelę w Excel która zawiera daty, które chcesz uwzględnić. Następnie możesz skopiować lub użyć funkcji Dodaj do modelu danych, aby dodać ją do modelu danych jako tabelę połączona. W większości przypadków nie trzeba tworzyć relacji między tabelą a tabelą Kalendarz. Każda formuła odwołująca się do niego może używać funkcji SPRAWDŹ.WARTOŚĆ w celu zwrócenia wartości.
Poniżej znajduje się przykład tabeli utworzonej w aplikacji Excel która zawiera święta, które mają zostać dodane do tabeli dat:
|
Data |
Święto |
|---|---|
|
1/1/2010 |
Nowe lata |
|
11/25/2010 |
Dziękujemy |
|
12/25/2010 |
Bożonarodzeniowe |
|
2011-01-01 |
Nowe lata |
|
11/24/2011 |
Dziękujemy |
|
12/25/2011 |
Bożonarodzeniowe |
|
2012-01-01 |
Nowe lata |
|
2012-11-22 |
Dziękujemy |
|
12/25/2012 |
Bożonarodzeniowe |
|
1/1/2013 |
Nowe lata |
|
11/28/2013 |
Dziękujemy |
|
12/25/2013 |
Bożonarodzeniowe |
|
11/27/2014 |
Dziękujemy |
|
12/25/2014 |
Bożonarodzeniowe |
|
2014-01-01 |
Nowe lata |
|
11/27/2014 |
Dziękujemy |
|
12/25/2014 |
Bożonarodzeniowe |
|
1/1/2015 |
Nowe lata |
|
11/26/2014 |
Dziękujemy |
|
12/25/2015 |
Bożonarodzeniowe |
|
2016-01-01 |
Nowe lata |
|
11/24/2016 |
Dziękujemy |
|
12/25/2016 |
Bożonarodzeniowe |
W tabeli dat utworzymy kolumnę o nazwie Święta i użyjemy formuły w ten sposób:
=SPRAWDŹ.WARTOŚĆ(Święta[Święta];Święta[data];Kalendarz[data])
Dokładniej przyjrzyjmy się tej formule.
Używamy funkcji SPRAWDŹ.WARTOŚĆ do uzyskania wartości z kolumny Święta w tabeli Święta. W pierwszym argumentie określamy kolumnę, w której będzie nasza wartość wyniku. Określamy kolumnę Święta w tabeli Święta, ponieważ jest to wartość, która ma zostać zwrócona.
=ODNOŚNIK.WARTOŚĆ(Święta[Święta],Święta[data];Kalendarz[data])
Następnie określamy drugi argument, czyli kolumnę wyszukiwania zawierającą daty, które chcemy wyszukać. W tabeli Święta określamy kolumnę Data w ten sposób:
=SPRAWDŹ.WARTOŚĆ(Święta[Święta];Święta[data];Kalendarz[data])
Na koniec w tabeli Kalendarz określamy kolumnę zawierającą daty, które chcemy wyszukać w tabeli Święta. Jest to oczywiście kolumna Data w tabeli Kalendarz.
=SPRAWDŹ.WARTOŚĆ(Święta[Święta];Święta[data];Kalendarz[data])
W kolumnie Święta dla każdego wiersza z wartością daty, która jest taka sama jak data w tabeli Święta, zostanie zwrócona nazwa świąt.
Kalendarz niestandardowy — trzynaście 4-tygodniowych okresów
Niektóre organizacje, takie jak sprzedaż detaliczna lub usługi spożywcze, często raportują informacje dotyczące różnych okresów, takich jak trzynaście 4-tygodniowych okresów. W przypadku trzynastu czterech dni po każdym okresie jest to 28 dni. dlatego każdy okres zawiera cztery poniedziałki, cztery wtorki, cztery środy i tak dalej. Każdy okres zawiera tę samą liczbę dni i zwykle dni wolne będą wypadać w tym samym okresie każdego roku. Możesz zacząć okres od dowolnego dnia tygodnia. Za pomocą języka DAX, podobnie jak w przypadku dat w kalendarzu lub roku obrachunkowym, można tworzyć dodatkowe kolumny z datami niestandardowymi.
W poniższych przykładach pierwszy pełny okres zaczyna się w pierwszą niedzielę roku obrachunkowego. W tym przypadku rok obrachunkowy rozpoczyna się w dniu 2011-07-01.
Tydzień
Ta wartość daje nam numer tygodnia, rozpoczynając od pierwszego pełnego tygodnia roku obrachunkowego. W tym przykładzie pierwszy pełny tydzień rozpoczyna się w niedzielę, więc pierwszy pełny tydzień w pierwszym roku obrachunkowym w tabeli kalendarza w rzeczywistości zaczyna się 2010-07-04 i trwa do ostatniego pełnego tygodnia w tabeli Kalendarza. Chociaż sama wartość nie jest tak przydatna w analizie, konieczne jest obliczenie do użycia w innych formułach z 28-dniowym okresem.
=ZAOST([data]-40356)/7)
Dokładniej przyjrzyjmy się tej formule.
Najpierw tworzymy formułę, która zwraca wartości z kolumny Data jako liczbę całkowitą, w następujący sposób:
=ZAO INT([data])
Następnie chcemy poszukać pierwszej niedzieli w pierwszym roku obrachunkowym. Widzimy, że jest to 2010-07-04.
Teraz odejmij od wartości 40356 (czyli liczbę całkowitą dla daty 2010-06-27, ostatnią niedzielę od poprzedniego roku obrachunkowego), aby uzyskać liczbę dni od początku dnia w tabeli Kalendarz, tak jak poniżej:
=ZAO INT([data]-40356)
Następnie podziel wynik przez 7 (dni w tygodniu), w ten sposób:
=ZAO INT(([data]-40356)/7)
Wynik wygląda następująco:
Period
Okres w tym kalendarzu niestandardowym zawiera 28 dni i zawsze zaczyna się w niedzielę. Ta kolumna zwróci numer okresu rozpoczynającego się od pierwszej niedzieli pierwszego roku obrachunkowego.
=ZAO INT(([Tydzień]+3)/4)
Dokładniej przyjrzyjmy się tej formule.
Najpierw tworzymy formułę, która zwraca wartość z kolumny Tydzień jako liczbę całkowitą, na przykład w następujący sposób:
=ZAO INT([Tydzień])
Następnie dodaj 3 do tej wartości, w ten sposób:
=ZAO INT([Tydzień]+3)
Następnie podziel wynik przez 4, w ten sposób:
=ZAO INT(([Tydzień]+3)/4)
Wynik wygląda następująco:
Rok obrachunkowy okresu
Ta wartość zwraca rok obrachunkowy dla okresu.
=ZAO INT(([Okres]+12)/13)+2008
Dokładniej przyjrzyjmy się tej formule.
Najpierw tworzymy formułę, która zwraca wartość z okresu i dodaje liczbę 12:
= ([Okres]+12)
Dzielimy wynik przez 13, ponieważ w roku obrachunkowym istnieje trzynaście 28-dniowych okresów:
=(([Okres]+12)/13)
Dodaję rok 2010, ponieważ jest to pierwszy rok w tabeli:
=(([Okres]+12)/13)+2010
Na koniec używamy funkcji ZAOPT do usunięcia dowolnego ułamka wyniku i zwrócenia całej liczby, podzielonej przez 13, w ten sposób:
=ZAO INT(([Okres]+12)/13)+2010
Wynik wygląda następująco:
Okres w roku obrachunkowym
Ta wartość zwraca numer okresu od 1 do 13, rozpoczynając od pierwszego pełnego okresu (rozpoczynającego się od niedzieli) w każdym roku obrachunkowym.
=JEŻELI(MOD([okres];13);MOD([okres];13);13)
Ta formuła jest nieco bardziej złożona, więc opiszemy ją jako pierwszą w języku, który lepiej rozumiemy. Ta formuła podaje wartość dzielenia z [Okres] przez 13, aby uzyskać liczbę okresów (1-13) w roku. Jeśli ta liczba wynosi 0, zwróć liczbę 13.
Najpierw tworzymy formułę, która zwraca resztę wartości z okresu o 13. Można użyć funkcji MOD (funkcji matematycznych i trygonometrycznych) w ten sposób:
=MOD([okres];13)
W takiej sytuacji w większości przypadków daje nam on określony wynik, z wyjątkiem sytuacji, gdy wartość pozycji Okres wynosi 0, ponieważ te daty nie należą do pierwszego roku obrachunkowego, tak jak w pierwszych pięciu dniach naszej przykładowej tabeli dat kalendarza. Możemy zająć się tym za pomocą funkcji JEŻELI. W przypadku, gdy wynikiem jest 0, zwracamy 13, w ten sposób:
=JEŻELI(MOD([okres];13);MOD([okres];13);13)
Wynik wygląda następująco:
Przykładowa tabela przestawna
Na poniższej ilustracji przedstawiono tabelę przestawną z polem WartośćSprzedaż z tabeli fakt. Sprzedaż w polach WARTOŚCI oraz OkresFiscalYear i PeriodInFiscalYear z tabeli wymiarowej daty kalendarza w wierszu WIERSZE. WartośćSprzedażyła jest agregowana dla kontekstu przez rok obrachunkowy i 28-dniowy okres w roku obrachunkowym.
Relacje
Po utworzeniu tabeli dat w modelu danych, aby rozpocząć przeglądanie danych w tabelach przestawnych i raportach, a aby agregować dane na podstawie kolumn w tabeli wymiarów daty, musisz utworzyć relację między tabelą fakt a danymi transakcji a tabelą dat.
Ponieważ konieczne jest utworzenie relacji na podstawie dat, należy utworzyć relację między kolumnami, których wartości są typu danych data/data (Data).
Dla każdej wartości daty w tabeli informacji powiązana kolumna odnośnika w tabeli dat musi zawierać zgodne wartości. Na przykład wiersz (rekord transakcji) w tabeli informacji Sprzedaż z wartością 2012-08-15 12:00 w kolumnie KluczDajDa musi mieć odpowiadającą jej wartość w powiązanej kolumnie Data w tabeli daty (o nazwie Kalendarz). Jest to jeden z najważniejszych powodów, dla których kolumna daty w tabeli dat powinna zawierać ciągły zakres dat, który zawiera każdą możliwą datę w tabeli fakt.
Uwaga: Chociaż kolumna z datami musi mieć ten sam typ danych (Data), format każdej kolumny nie ma znaczenia.
Uwaga: Jeśli program Power Pivot nie pozwoli na utworzenie relacji między dwiema tabelami, pola daty mogą nie zawierać daty i godziny z jednakową dokładnością. W zależności od formatowania kolumny wartości mogą wyglądać tak samo, ale mogą być przechowywane inaczej. Dowiedz się więcej o pracy z czasem.
Uwaga: Unikaj używania całkowitych kluczy zastępczych w relacjach. Podczas importowania danych z relowanego źródła danych często kolumny daty i czasu są reprezentowane przez klucz zastępczy, czyli kolumnę typu liczba całkowita reprezentującą unikatową datę. W dodatku Power Pivot należy unikać tworzenia relacji przy użyciu całkowitych kluczy daty/czasu, a zamiast tego używać kolumn zawierających unikatowe wartości z typem danych daty. Używanie kluczy zastępczych jest uznawane za najlepsze rozwiązanie w przypadku tradycyjnych magazynów danych, jednak klucze całkowite nie są potrzebne w dodatku Power Pivot i mogą utrudniać grupowanie wartości w tabelach przestawnych według różnych okresów dat.
Błąd niezgodności typu wyświetlany podczas próby utworzenia relacji prawdopodobnie dlatego, że kolumna w tabeli fakt nie ma typu danych Data. Może się tak zdarzyć, gdy program Power Pivot nie może automatycznie przekonwertować daty (zwykle typu danych tekstowych) na typ danych daty. Nadal można używać kolumny w tabeli informacji, ale będzie trzeba przekonwertować dane za pomocą formuły języka DAX w nowej kolumnie obliczeniowej. Zobacz Konwertowanie danych typu tekstowego na typ danych daty w dodatku w dalszej części dodatku.
Wiele relacji
W niektórych przypadkach może być konieczne utworzenie wielu relacji lub utworzenie wielu tabel dat. Jeśli na przykład w tabeli fakt sprzedaży znajduje się wiele pól daty, takich jak DateKey, ShipDate i ReturnDate, wszystkie mogą mieć relacje z polem Data w tabeli daty kalendarza, ale tylko jedno z nich może być aktywną relacją. W tym przypadku dataKey oznacza datę transakcji, a zatem najważniejszą datę, która najlepiej będzie pełnić rolę aktywnej relacji. Pozostałe relacje są nieaktywne.
W poniższej tabeli przestawnej obliczana jest łączna sprzedaż według roku obrachunkowego i kwartału obrachunkowego. Miara o nazwie Łączna sprzedaż z formułą Łączna sprzedaż:=SUMA([KwotaSprzedazy]), zostanie umieszczona w polu WARTOŚCI, a pola Rok Obrachunkowy i Rok Obrachunkowy z tabeli dat kalendarza zostaną umieszczone w wierszach WIERSZE.
Ta tabela przestawna z prostą przód działa poprawnie, ponieważ chcemy zsumować całkowitą sprzedaż do daty transakcji w kluczu DateKey. W ramach naszej miary Total Sales są używane daty z kolumny KluczDatu i są sumowane według roku obrachunkowego i kwartału obrachunkowego, ponieważ istnieje relacja między kolumną KluczDaj w tabeli Sprzedaż a kolumną Data w tabeli Daty kalendarza.
Nieaktywne relacje
Ale co zrobić, jeśli chcemy zsumować sumę sprzedaży nie według daty transakcji, ale według daty wysyłki? Potrzebna jest relacja między kolumną Data Wysyłki w tabeli Sprzedaż a kolumną Data w tabeli Kalendarz. Jeśli nie utworzymy tej relacji, agregacje zawsze będą oparte na dacie transakcji. Jednak możemy mieć wiele relacji, nawet jeśli tylko jedna z nich może być aktywna i ponieważ data transakcji jest najważniejsza, staje się ona aktywną relacją z tabelą Kalendarz.
W tym przypadku data Wysyłki ma nieaktywną relację, więc każda formuła miary utworzona w celu agregowania danych na podstawie dat wysyłki musi określać nieaktywną relację przy użyciu funkcji USERELATIONSHIP.
Na przykład ponieważ istnieje nieaktywna relacja między kolumną Data Wysyłki w tabeli Sprzedaż a kolumną Data w tabeli Kalendarz, możemy utworzyć miarę sumujących całkowitą sprzedaż według daty wysyłki. W celu określenia relacji do użycia użyjemy takiej formuły:
Total Sales by Ship Date:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))
Ta formuła zawiera po prostu states: Calculate a sum for SalesAmount, but filter by using the relationship between the ShipDate in the Sales table and the Date column in the Calendar table.
Teraz, jeśli utworzymy tabelę przestawną i umieścimy miarę Łączna sprzedaż według daty wysyłki w wartościACH, a kolumny Rok obrachunkowy i Kwartał obrachunkowy w funkcji ILE.WIERSZY, zobaczymy tę samą sumę całkowitą, ale wszystkie inne kwoty dla roku obrachunkowego i kwartału obrachunkowego różnią się, ponieważ są one oparte na dacie wysyłki, a nie na dacie transakcji.
Użycie relacji nieaktywnych pozwala używać tylko jednej tabeli dat, ale wymaga, aby wszelkie miary (takie jak Total Sales by Ship Date) odwoływiły się do nieaktywnej relacji w jej formule. Istnieje jeszcze jedna alternatywa, czyli użycie wielu tabel dat.
Wiele tabel dat
Innym sposobem pracy z wieloma kolumnami dat w tabeli faktów jest utworzenie wielu tabel dat i utworzenie osobnych aktywnych relacji między nimi. Przyjrzyjmy się ponownie przykładowi tabeli Sprzedaż. Mamy trzy kolumny z datami, na których możemy zechcieć agregować dane:
-
KluczDazy z datą sprzedaży dla każdej transakcji.
-
Data Wysyłki — z datą i godziną wysłania sprzedanych elementów do klienta.
-
Data Zwracana — z datą i godziną, kiedy co najmniej jeden zwrócony element został zwrócony.
Pamiętaj, że najważniejsze jest pole DateKey (KluczDazy) z datą transakcji. Większość agregacji wykonajemy na podstawie tych dat, więc na pewno będziemy chcieli relacji między nimi a kolumną Data w tabeli Kalendarz. Jeśli nie chcemy tworzyć nieaktywnych relacji między polami Data Wysyłki i Data Zwracana oraz polem Data w tabeli Kalendarz, w związku z tym wymagając specjalnych formuł miar, możemy utworzyć dodatkowe tabele dat dla daty wysyłki i daty zwrotu. Następnie można tworzyć aktywne relacje między nimi.
W tym przykładzie utworzono inną tabelę dat o nazwie ShipCalendar .... Oczywiście oznacza to również utworzenie dodatkowych kolumn dat, a ponieważ te kolumny dat znajdują się w innej tabeli dat, chcemy je nazwać tak, aby odróżniały je od tych samych kolumn w tabeli Kalendarz. Na przykład utworzono kolumny o nazwach Czas Wysyłki, MiesiącPocztowy, KwadratPocztowy i tak dalej.
Jeśli utworzymy tabelę przestawną i umieścimy miarę Total Sales w polach VALUES i ShipFiscalYear i ShipFiscalQuarter w wierszach ILE.WIERSZY, zobaczymy te same wyniki, które zostały wprowadzone podczas tworzenia nieaktywnej relacji i specjalnego pola obliczeniowego Total Sales by Ship Date.
Każde z tych metod wymaga starannego rozważenia. Podczas używania wielu relacji z jedną tabelą dat może być konieczne utworzenie specjalnych miar, które przesyłują nieaktywne relacje przy użyciu funkcji USERELATIONSHIP. Z drugiej strony tworzenie wielu tabel dat może być mylące w przypadku listy pól, a ponieważ w modelu danych jest ich więcej, wymaga to większej ilości pamięci. Poeksperymentuj z tym, co najbardziej Ci się najlepiej sprawdza.
Właściwość Tabela dat
Właściwość Tabela dat ustawia metadane niezbędne do poprawnego Time-Intelligence funkcji, takich jak TOTALYTD, PREVIOUSMONTH i DATESBETWEEN. Gdy obliczenia są uruchamiane przy użyciu jednej z tych funkcji, aparat formuł dodatku Power Pivot wie, gdzie należy uzyskać potrzebne daty.
Ostrzeżenie: Jeśli ta właściwość nie jest ustawiona, miary używające języka DAX Time-Intelligence funkcji mogą nie zwracać poprawnych wyników.
Ustawiasz właściwość Date Table (Tabela dat) i określasz w nim kolumnę daty (datetime) typu danych Date (datetime).
Jak to zrobić: Ustawianie właściwości Tabela dat
-
W PowerPivot wybierz tabelę Kalendarz.
-
Na karcie Projektowanie kliknij pozycję Oznacz jako tabelę dat.
-
W oknie dialogowym Oznaczanie jako tabeli dat wybierz kolumnę z unikatowymi wartościami i typ danych Data.
Praca z czasem
Wszystkie wartości dat z typem danych Data w Excel lub SQL Server są w rzeczywistości liczbami. W tej liczbie są to cyfry odwołujące się do czasu. W wielu przypadkach jest to czas dla każdego wiersza o północy. Jeśli na przykład pole DateTimeKey w tabeli fakty Sprzedaż ma wartości, takie jak 2010-10-19 12:00:00, oznacza to, że wartości są do poziomu dokładności do dnia. Jeśli wartości pól DateTimeKey (KluczDagodziny) mają czas uwzględniony, na przykład 2010-10-19 8:44:00, oznacza to, że wartości są precyzyjniej podane w minutach. Wartości mogą być również do dokładności na poziomie godziny lub nawet do poziomu sekund precyzji. Poziom dokładności wartości czasu będzie miał znaczący wpływ na sposób tworzenia tabeli dat i relacji między tabelą a tabelą informacji.
Należy określić, czy dane będą agregowane do poziomu precyzji dnia, czy do poziomu dokładności czasu. Innymi słowy możesz chcieć użyć kolumn w tabeli dat, takich jak Rano, Po południu lub Godzina, jako pól daty godziny w obszarach Wiersz, Kolumna lub Filtr tabeli przestawnej.
Uwaga: Dni to najmniejsza jednostka czasu, z pomocą których mogą pracować funkcje analizy czasowej języka DAX. Jeśli praca z wartościami czasu nie jest potrzebna, należy ograniczyć dokładność danych, aby używać dni jako jednostki minimalnej.
Jeśli zamierzasz agregować dane do poziomu czasu, tabela dat będzie potrzebować kolumny daty z uwzględnioną godziną. W rzeczywistości będzie potrzebna kolumna z datą zawierającą jeden wiersz na każdą godzinę, a może nawet co minutę, każdego roku w zakresie dat. Jest tak dlatego, że aby utworzyć relację między kolumną DateTimeKey (KluczDagodziny) w tabeli fakt a kolumną daty w tabeli dat, muszą być zgodne wartości. Wyobraź sobie, że jeśli uwzględnisz wiele lat, może to sprawić, że tabela z datami będzie bardzo duża.
Jednak w większości przypadków chcesz agregować dane tylko do dnia. Innymi słowy, kolumny takie jak Rok, Miesiąc, Tydzień lub Dzień tygodnia będą stanowić pola w obszarach Wiersz, Kolumna lub Filtr tabeli przestawnej. W tym przypadku kolumna daty w tabeli dat musi zawierać tylko jeden wiersz na każdy dzień w roku, jak opisano wcześniej.
Jeśli kolumna daty zawiera dokładność na poziomie czasu, ale agreguje się tylko do poziomu dnia, w celu utworzenia relacji między tabelą fakt a tabelą dat może być konieczne zmodyfikowanie tabeli faktów przez utworzenie nowej kolumny obcinanej do wartości dnia wartości w kolumnie daty. Innymi słowy, przekonwertuj wartość, na przykład 2010-10-19 8:44:00 na 2010-10-19 12:00:00. Następnie możesz utworzyć relację między tą nową kolumną a kolumną daty w tabeli dat, ponieważ wartości są zgodne.
Przyjrzyjmy się przykładowi. Na tym obrazie przedstawiono kolumnę DateTimeKey (KluczDagodziny) w tabeli informacji Sprzedaż. Wszystkie agregacje danych w tej tabeli muszą być tylko na poziomie dnia, przy użyciu kolumn w tabeli dat kalendarza, takich jak Rok, Miesiąc, Kwartał itp. Czas zawarty w wartości nie jest istotny, a jedynie rzeczywista data.
Ponieważ nie trzeba analizować tych danych do poziomu czasu, kolumna Data w tabeli daty kalendarza nie musi zawierać jednego wiersza dla każdej godziny i każdej minuty każdego roku. Kolumna Data w tabeli dat wygląda następująco:
Aby utworzyć relację między kolumną DateTimeKey w tabeli Sprzedaż a kolumną Data w tabeli Kalendarz, można utworzyć nową kolumnę obliczeniową w tabeli fakt. Sprzedaż i użyć funkcji TRUNC w celu obcinania wartości daty i czasu z kolumny KluczDajgodziny do wartości daty, która odpowiada wartościom w kolumnie Data w tabeli Kalendarz. Nasza formuła wygląda następująco:
=LICZBA.NC([KluczDagodziny];0)
W ten sposób nadamy nam nową kolumnę (o nazwie DateKey) zawierającą datę z kolumny DateTimeKey i czas 12:00:00 dla każdego wiersza:
Teraz możemy utworzyć relację między tą nową kolumną (KluczDazy), a kolumną Data w tabeli Kalendarz.
Podobnie możemy utworzyć kolumnę obliczeniową w tabeli Sprzedaż, która zmniejsza dokładność czasu w kolumnie KluczDatyGodziny do poziomu dokładności godziny. W tym przypadku funkcja TRUNC nie będzie działać, ale nadal możemy używać innych funkcji daty i godziny języka DAX w celu wyodrębnienia i ponownego zesumowania nowej wartości do poziomu dokładności godzin. Możemy użyć formuły w ten sposób:
= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)
Nasza nowa kolumna wygląda następująco:
Pod warunkiem, że nasza kolumna Data w tabeli dat zawiera wartości do poziomu dokładności godzin, możemy następnie utworzyć relację między nimi.
Tworzenie bardziej użytecznych dat
Wiele kolumn dat tworzyć w tabeli dat jest niezbędnych dla innych pól, ale w naprawdę nie są one przydatne w analizie. Na przykład pole DateKey (KluczDa) z tabeli Sales, do której się odnosiliśmy i które przedstawiono w tym artykule, jest ważne, ponieważ dla każdej transakcji ta transakcja jest rejestrowana jako występująca o określonej dacie i godzinie. Jednak z punktu widzenia analizy i raportowania nie jest to aż tak przydatne, ponieważ nie można go użyć jako wiersza, kolumny ani pola filtru w tabeli przestawnej lub raporcie.
Podobnie w naszym przykładzie kolumna Data w tabeli Kalendarz jest bardzo przydatna, krytyczna w rzeczywistości, ale nie można jej użyć jako wymiaru w tabeli przestawnej.
Aby tabele i kolumny w nich zachować jak najbardziej przydatne oraz ułatwić nawigowanie po listach pól w tabeli przestawnej lub raporcie programu Power View, należy ukryć niepotrzebne kolumny przed narzędziami klienta. Możesz również ukryć niektóre tabele. W pokazanej wcześniej tabeli Święta znajdują się daty świąteczne, które są ważne dla niektórych kolumn w tabeli kalendarzy, ale kolumny Data i Święta z tabeli Święta nie mogą być wyświetlane jako pola tabeli przestawnej. W tym miejscu również możesz ukryć całą tabelę Świąt, aby ułatwić nawigowanie po listach pól.
Innym ważnym aspektem pracy z datami są konwencje nazewnictwa. W dodatku Power Pivot możesz nazwać tabele i kolumny tak, jak chcesz. Należy jednak pamiętać o tym, zwłaszcza w przypadku udostępniania skoroszytu innym użytkownikom, dobra konwencja nazewnictwa ułatwia identyfikowanie tabel i dat nie tylko na listach pól, ale również w dodatku Power Pivot i w formułach języka DAX.
Po utworzeniu tabeli dat w modelu danych możesz rozpocząć tworzenie miar, które pomogą ci w najlepiej wyeksłować dane. Niektóre mogą być tak proste, jak sumowanie sum sprzedaży w bieżącym roku, a inne mogą być bardziej złożone, gdzie trzeba filtrować według określonego zakresu unikatowych dat. Aby uzyskać więcej informacji, zobacz Miary w dodatku Power Pivoti funkcjach analizy czasowej.
Dodatek
Konwertowanie danych typu tekstowego na typ danych daty
W niektórych przypadkach tabela informacji z danymi transakcji może zawierać daty o typie danych tekstowych. Oznacza to, że data wyświetlana jako 2012-12-04T11:47:09 w rzeczywistości nie jest datą lub przynajmniej nie jest typem daty zrozumiałej dla dodatku Power Pivot. To tak naprawdę tekst, który odczytuje jak datę. Aby można było utworzyć relację między kolumną daty w tabeli fakt a kolumną daty w tabeli dat, obie kolumny muszą mieć typ danych Data.
Zazwyczaj podczas próby zmiany typu danych kolumny dat, które są danymi typu tekstowego, na typ danych daty, program Power Pivot może zinterpretować daty i automatycznie przekonwertować je na typ danych Data prawdziwa. Jeśli konwersja typu danych nie jest w dodatku Power Pivot, wystąpi błąd niezgodności typu.
Można jednak przekonwertować daty na typ danych Data prawdziwa. Możesz utworzyć nową kolumnę obliczeniową i użyć formuły języka DAX do analizowania roku, miesiąca, dnia, czasu itp. na podstawie ciągów tekstowych, a następnie ponownie je ze sobą związywać, tak aby program Power Pivot odczytał ją jako prawdziwą datę.
W tym przykładzie zaimportowano tabelę faktów o nazwie Sprzedaż do dodatku Power Pivot. Zawiera kolumnę o nazwie DateTime. Wartości są wyświetlane w ten sposób:
Gdy przyjrzymy się typowi danych na karcie Narzędzia główne dodatku Power Pivot w grupie Formatowanie, zobaczymy, że jest to typ danych Tekst.
Nie można utworzyć relacji między kolumną DateTime i kolumną Date w tabeli dat, ponieważ typy danych nie są zgodne. Jeśli spróbujemy zmienić typ danych na Data,zostanie błąd niezgodności typu:
W tym przypadku dodatku Power Pivot nie można było przekonwertować typu danych z tekstu na datę. Nadal możemy używać tej kolumny, ale aby przetworzyć ją do prawdziwego typu danych daty, musimy utworzyć nową kolumnę, która analizuje tekst i ponownie tworzy go w wartości, które mogą zostać dodane do dodatku Power Pivot jako typ danych Data.
Pamiętaj, że w sekcji Praca z czasem we wcześniejszej części tego artykułu; jeśli nie jest to konieczne, podczas analizy należy precyzyjniej przekonwertować daty w tabeli faktów na dokładność dnia. W związku z tym chcemy, aby wartości w nowej kolumnie oznaczały poziom dokładności do dnia (z wyjątkiem czasu). Możemy zarówno przekonwertować wartości w kolumnie DateTime na typ danych daty, jak i usunąć poziom dokładności czasu za pomocą następującej formuły:
=DATA(LEWY([DateTime],4), MID([DateTime],6;2), MID([DateTime],9;2))
W ten sposób zostanie nam nadana nowa kolumna (w tym przypadku o nazwie Data). Program Power Pivot wykrywa nawet wartości jako daty i automatycznie ustawia typ danych na Data.
Aby zachować dokładność poziomu czasu, wystarczy rozszerzyć formułę tak, aby zawierała godziny, minuty i sekundy.
=DATA(LEWY([DateTime],4); MID([DateTime],6;2); MID([DateTime],9;2)) +
CZAS(MID([DateTime];12;2);MID([DateTime];15;2);MID([DateTime];18;2))
Teraz, gdy mamy kolumnę Data o typie danych Data, możemy utworzyć relację między kolumną daty a kolumną daty w dacie.
Dodatkowe zasoby
Obliczenia w dodatku Power Pivot
Szybki start: nauka podstaw języka DAX w 30 minut
