Microsoft Power BI

sierpień 14,2020 | 2785 0

Transkrypt:

Czy zastanawialiście się kiedyś jak zaraportować ilość subskrybentów? Znaczy, jeżeli mamy usługę, do której ludzie się zapisują i wypisują to czasami chcemy zobaczyć czy liczba ta rośnie, czy może spada. Jeżeli chcecie zobaczyć jak to się robi w Power BI, zapraszam do mojego dzisiejszego nagrania.

W tym nagraniu pokazuję, jak liczyć w wybranym okresie czasu – to może być absolutnie dowolny okres czasu –jaka była wtedy ilość naszych subskrybentów, albo na przykład naszych pracowników. I to nie musi być sytuacja związana z ludźmi. Kiedyś miałem taki projekt, gdzie trzeba było policzyć, ile w danym momencie jest zarejestrowanych statków powietrznych. Tak, żeby policzyć wartość portfela rejestracji lotniczych. Ale dosyć tego gadania, zobaczmy jak to wygląda na moim komputerze.

Mój scenariusz dzisiejszy wygląda następująco – proszę zobaczyć – mam tabelkę pracowników, gdzie mam ich numer ubezpieczenia tutaj jest taka kolumna „EmployeeNationalIDAInternateKey”, mam też jego imię i nazwisko, mamy datę zatrudnienia, mamy datę urodzin i uznałem (bo akurat w zbiorze danych nie było czegoś takiego jak data opuszczenia zakładu, czy tam data zwolnienia się), że zrobimy to tak, że jeżeli nasz człowiek ma jakieś pełne urodziny typu 40-ste, 30-ste to w tym momencie, w tym dniu robi sobie prezent i przechodzi do nowej pracy. Czyli ten dzień urodzin, pełnych urodzin jest tym dniem, w którym kończymy zatrudnienie i te dane zaimportowałem akurat z pliku excelowego. Na koniec usunąłem datę urodzenia, no bo ona do samych obliczeń nie będzie mi potrzebna. I teraz tak – są dwa sposoby, żeby to policzyć. Stara szkoła, to jest – kimballowska szkoła. W jednej z książek Ralpha Kimballa możemy przeczytać, że są takie tabele faktów, które nazywamy migawkowymi, i które mówią nam o stanie jakichś rzeczy na dany dzień. I tutaj zwykle ma zastosowanie to w stanach magazynowych. To znaczy, że na każdy dzień możemy sobie zobaczyć ile sztuk danego produktu znajduje się u nas w magazynie. Ja tu też zrobię taką tabelkę. W tym celu utworzyłem sobie funkcję (tutaj akurat w Power Query), która będzie mi zwracała dni od jednej daty do drugiej daty. Dzięki niej zrobię dwie rzeczy. Jedno, zbuduję kalendarz. Kalendarz, który będzie chyba od 2006 do 2020 roku. I dzięki tej funkcji zbuduję też tabelę faktów, gdzie każdemu pracownikowi – proszę zobaczyć – tutaj sobie powtarzamy każdego pracownika. Na każdy dzień kiedy on jest u nas zatrudniony, tworzymy oddzielny rekord. Czyli kiedy chciałbym sprawdzić ilu pracowników mam w danym dniu, no to wtedy będę mógł wybrać sobie taki dzień. Proszę zobaczyć np. 18 października 2008, będę mógł go wybrać i zobaczyć jacy pracownicy wpadają mi w ten dzień i wtedy będę widział dokładnie ilu mam pracowników np. w naszym zakładzie pracy. Jak to zostało połączone w modelu. Oczywiście, model już załadowałem do siebie. Tutaj skorzystajmy z takich małych podwidoków i tutaj mam moją tabelę faktów, czyli ten każdy dzień, kiedy dany pracownik występuje tutaj jest jako rekord. Mam oczywiście mój kalendarz, czyli moją tabelę wymiaru kalendarzowego. Mam też liczbę moich pracowników. No i teraz spójrzmy sobie, jak może wyglądać taki raport, w którym będziemy w stanie zobaczyć przebieg ilości tych pracowników w dowolnie wybranym czasie. Idziemy sobie do naszej tabeli EmployeeFact. Tutaj korzystamy z naszej kolumny, która zawiera ten unikalny klucz – tak jakby… no w Polsce byłby to PESEL – a tam w tej tabelce jest to numer ubezpieczenia. On w tej chwili – ponieważ on jest traktowany raczej, jako atrybut – „wskakuje” na oś, ale my chcemy wyznaczyć ich liczność. Tutaj trzeba będzie zamienić i skorzystać z czegoś takiego jak liczba unikalnych wystąpień. Proszę zobaczyć: przez całą historię miałem około 300 pracowników.

Tutaj widać 290. A jak to w czasie będzie wyglądać? Powinienem wziąć sobie kalendarz i na przykład zobaczyć przebieg w czasie i nie interesują mnie indywidulane wartości (jak w wykresie kolumnowym), mogę się spokojnie przełączyć na wykres liniowy. O proszę bardzo, mam mój wykres liniowy i mogę sobie zobaczyć teraz. Jeszcze sobie „doformatuję” to, to znaczy usunę sobie oś Y i będę w każdym roku widział jak to się kształtuje. Dodatkowo mógłbym sobie wrzucić na przykład miesiąc i móc przeskakiwać na poszczególne lata. Żeby móc zobaczyć jak w poszczególnych latach to się rozkładało na przestrzeni wszystkich miesięcy. Zobaczmy może sobie tutaj, kiedy była liczba rosnąca. Możemy też zauważyć, że jeżeli ja teraz patrzę na rok 2009 to najwyższy szczyt był tutaj w końcówce tego roku. Miałem 247 pracowników a tutaj widać, że jest 261. A to tylko oznacza tyle, że byli jacyś pracownicy, którzy się zatrudnili w tym roku i też zwolnili się w tym roku. Oni też występują jakby (jeżeli chodzi o cały rok), ale w niektórych miesiącach nie będą występowali. I to jest tak, jakby starsze podejście. W takim sensie, że jest to podejście bardziej „hurtowniane”. To znaczy, że te dane wynikają tak naprawdę bezpośrednio z tabel. My tu nie musieliśmy pisać żadnej miary. Te dane wynikały z tej zbudowanej tabeli. Wadą tej tabeli jest to, że liczba rekordów, która się tam pojawia jest niemała. Proszę zobaczyć, prawie 600 000 rekordów, w przeciągu chyba 14-15 lat. I proszę zobaczyć, pracowników mamy tylko 300. Powoduje to, że nasza tabela faktów nie będzie mała. Jeżeli będziemy odświeżać te dane, ładować na nowo, i jeżeli tych pracowników mamy dużo, dużo więcej no to też trochę czasu Power BI będzie musiał spędzić, żeby odświeżyć tę tabelę,

Zobaczmy sobie teraz drugi sposób. Z czego wynika zastosowanie drugiego sposobu? Otóż my tu mamy silnik analityczny xVelocity, który jest wbudowany w Power BI, czy Analysis Services, czy też w Excelu. Ma on pewne funkcje, właściwości, które pozwalają na to – na samej tabeli tych pracowników, tylko i wyłącznie na podstawie tych dwóch dat i żadnych innych dodatkowych rekordów – aby skonstruować takie miary, żeby uzyskać też taką samą funkcjonalność. To znaczy, że ja będę miał możliwość zobaczenia dla każdego roku, zajrzenia w każdy miesiąc, mogę też w każdy tydzień zajrzeć – nie ma żadnego problemu. Zobaczmy jak to by wyglądało. Z powrotem może przejdźmy się na zakładkę modelowania i zobaczmy, jak tutaj będzie wyglądał układ tabel. Otóż wykorzystam też kalendarz. Tutaj co prawda jest to nazwane CalendarNew, ale CalendarNew jest dokładną kopią kalendarza normalnego. Tutaj nie ma żadnych innych cudów. Zobaczymy… To jest nasza tabelka identyczna, czyli dokładnie tabelka naszych pracowników. Z tym, że utworzymy sobie dwie relacje. Jedna jest na podstawie daty zatrudnienia. I tu jest pierwsza relacja skonstruowana. Tu jest też druga relacja – to jest data złożenia wypowiedzenia, czy też odejścia z pracy. Proszę zauważyć, że w momencie, kiedy utworzyliśmy drugą relację, to ta relacja jest taka wykropkowana. Jak zajrzymy w jej właściwości to zobaczymy, że ta relacja jest nieaktywna. To, że jest nieaktywna to wcale nie oznacza, że nie będziemy mogli jej użyć. To tyle tylko oznacza, że ona jest stworzona, ale będzie używana w specjalnych okolicznościach. No i teraz jak się zabrać do utworzenia takiego zestawienia. W miarę upływającego czasu mamy sytuację, kiedy ludzie się zatrudniają i to jest to nasze HireDate. Tutaj w tabelce Employee mamy HireDate i LeaveDate, czyli datę zatrudnienia i datę opuszczenia naszego miejsca pracy. Więc ja będę zliczał momenty, kiedy ktoś się pojawi nowy i ta liczba będzie rosła. Policzmy sobie, ile ludzi było zatrudnianych do tej pory. To znaczy, do momentu jak ja patrzę na przykład na rok 2012, to chciałbym zobaczyć ile od początku działalności mojej firmy do 31 grudnia 2012 roku było pracowników zatrudnionych. W tym celu tworzę sobie miarę. Proszę bardzo, nowa miara. I tak, ja będę chciał policzyć tę liczbę tych elementów w moim słowniku. Na początku nazwa HiredCount i skorzystam z funkcji COUNT() czyli będę liczył liczbę elementów. Elementy znajdują się w tabeli Employee. I teraz tak, gdybym ja to zostawił to widział bym liczbę pracowników, tych którzy się przyjęli (w tym konkretnym okresie), a ja chciałbym zobaczyć tych, którzy do tego momentu się przyjęli, czyli muszę zobaczyć też na lata wcześniejsze. Żeby zmienić kontekst „patrzenia dat” skorzystam z mojego nowego kalendarza (bo to jest moja nowa tabela w której wrzucę sobie też miesiąc) i zamienimy to na wykres liniowy. Proszę zobaczyć, tutaj mamy liczbę i zobaczymy, że w 2008 roku miałem największy przyrost pracowników – tutaj 45 osób – ale ja chciałbym zobaczyć też przyrostowo – tak trochę jak suma narastająco. Więc, jak robimy sumę narastającą, skorzystamy z CALCULATE() i oczywiście zmienimy nieco kontekst dat, na które patrzymy – będziemy się „wyfiltrowywać”. Wyfiltrujemy cały kalendarz. U mnie to jest CalendarNew w tym wypadku. Będziemy chcieli zrobić tak, żeby ta data była mniejsza lub równa od maksymalnej, którą w danym momencie widzimy. I tu też ‘CalendarNew’[Date]. Zamykamy nawiasy – proszę bardzo, wszystkie – i też uzyskam trochę inny kształt. Ten kształt będzie zawsze rosnący i on jest do końca mojego kalendarza. W drugim etapie chciałbym zobaczyć, jak ludzie się zwalniali, jak odchodzili z pracy. Więc zróbmy sobie następną miarę, która pozwoli nam policzyć jak wyglądało zwalnianie się. Tutaj w tym przypadku będę musiał popatrzeć na trochę inną datę, ale w zasadzie będę liczył to samo. Będę liczył liczbę pracowników, ale będę patrzył na datę, na tę datę opuszczenia. Ponieważ to będzie bardzo podobne, ja sobie pozwolę skopiować tę miarę i tutaj ustawić nową. Nazwiemy ją nieco inaczej, na przykład LeftCount. Zanim podziałam tutaj z filtrowaniem, skorzystam z tego, że CALCULATE() może przyjmować wiele argumentów filtrujących i tym następnym argumentem filtrującym dla mnie będzie coś takiego, co się nazywa USERELATIONSHIP(). Pamiętamy, że tam miałem tą nieaktywną relację pomiędzy LeaveDate a Date w kalendarzu nowym i ja będę teraz chciał z niej skorzystać. Wskazuję mu najpierw po jednej stronie, na kolumnę po jednej stronie, a potem mamy jeszcze w Employee coś takiego jak LeaveDate. To pozwoli mi na to, żeby zmienić nieco połączenie pomiędzy tymi dwoma tabelami. Jak już mam nową miarę – znaczy tę miarę, która liczy mi te osoby, które jakby odeszły – to muszę jeszcze jedną drobną modyfikację zrobić do tej miary. Bo ja teraz nie chcę zobaczyć osób, które mi odeszły do końca okresu, na który patrzę, tylko chcę zobaczyć, kto mi odszedł przed tym okresem, na który patrzę. Bo inaczej nie uwzględnię tych osób, które mam w danym okresie. Czyli na przykład, jeżeli mam rok 2011 i policzę to w ten sposób, to będę też liczył odejścia w tym 2011, a chcę te osoby jednak wliczyć w liczbę moich pracowników. Stąd tutaj ta drobna modyfikacja, to jest zamiana tej funkcji maximum na minimum. Zrobimy to jeszcze tak, żeby to było przed, a nie na przykład jeszcze z tym pierwszym stycznia.

Zobaczmy sobie jak to wygląda. Powielimy sobie ten wykres, podmienimy miarę w tym wykresie na LeftCount. O proszę bardzo – i to jest liczba moich odejść. Te odejścia zaczynały się jak widać w roku 2009, czyli ci pracownicy, którzy są od 2006 to jednak cały czas pracowali. Oczywiście tu się wszystko kończy na 2020. Żeby obliczyć liczbę pracowników, którzy są w danym momencie to trzeba tak naprawdę nałożyć te wykresy na siebie i odjąć jeden od drugiego, czyli liczbę zatrudnionych od liczby tych którzy odeszli i tu już będzie bardzo prosta miara – to będzie jedynie różnica. Napiszemy sobie, że to będzie Headcount i nasza różnica będzie HiredCount odjąć LeftCount. To wszystko. I znowu wykres sobie sporządzimy, żeby sprawdzić czy ta liczba jest chociaż jakoś zbliżona do tego co uzyskaliśmy we wcześniejszym sposobie. Może jeszcze tak, że porównajmy sobie te dwa wykresy i zobaczmy, czy będziemy widzieć te same liczby. No i proszę bardzo, mamy tutaj na przykład rok 2013, 201 pracowników było, 178 w następnych, 144 i tak dalej, i tak dalej. Tu oczywiście też można przechodzić z okresu na okres. Ja nawet jak przejdę, to nie będę filtrował tego, no bo tutaj inny wymiar filtruję.

Jak widać, mamy dwa sposoby na liczenie elementów w danym okresie czasu. Nie jest to jakaś super doskonała metoda, bo jeżeli weźmiemy pod uwagę to, że jakiś pracownik zatrudnił się w jakimś roku, popracował 3 miesiące, czy powinniśmy go liczyć jako całość czy np. ¼ – to już tak naprawdę zależy jakimi metodami będziemy liczyć. Jakie metody przyjmiemy tutaj do swojego raportowania i to już jest jakby inna bajka, na bardziej złożone wyliczenia. Ale dzięki tym dwóm metodom, które zobaczyliśmy, możemy jakby przyjąć to swoje właściwe podejście. Może być tak, że chcemy mieć mały model, zastosujemy drugą metodę, tą związaną z obliczeniami w silniku xVelocity, czyli tutaj te nowe miary DAX’owe. Może być też tak, że mamy Hurtownię, w której raportujemy też z tabelek relacyjnych. Czyli tabelka relacyjna będzie akurat i użyteczna tutaj w Power BI i na przykład w innym narzędziu raportowym, także podejście może być różne.

W każdym bądź razie, dziękuję za obejrzenie odcinka i zapraszam na następne.