Kurs Power BI: Model danych: star schema i relacje

W skrócieModel danych to fundament raportu Power BI: decyduje, czy miary DAX liczą się poprawnie i czy jedno kliknięcie filtruje cały pulpit. Na dziewięciu tabelach Nordvelli budujemy model gwiazdy, ustawiamy kardynalność i kierunek filtra relacji, a relację wiele do wielu rozwiązujemy tabelą pomostową.

Model danych to fundament, na którym opiera się każdy raport Nordvelli. To on decyduje, czy Twoje miary DAX policzą się poprawnie, czy kliknięcie w miasto Kraków przefiltruje cały pulpit i czy plik będzie ważył kilka megabajtów zamiast kilkuset. W tym rozdziale postawimy pełny model gwiazdy na dziewięciu tabelach Nordvelli: powiążemy 108 675 transakcji z tabelami wymiarów, ustawimy kardynalność i kierunek filtra w oknie relacji, rozwiążemy relację wiele do wielu za pomocą tabeli pomostowej i na koniec zobaczymy na własne oczy, jak pojedyncze kliknięcie zawęża cały raport.

Star schema: fakty kontra wymiary

Co chcemy osiągnąć. Raport Nordvelli ma odpowiadać na pytania o sprzedaż według miast, kategorii, pracowników i miesięcy, a potrzebne dane leżą w dziewięciu tabelach. Chcemy ułożyć je w schemat gwiazdy, dla którego silnik Power BI jest zaprojektowany.

Star schema, po polsku schemat gwiazdy, to sposób zorganizowania tabel, w którym jedna centralna tabela faktów łączy się z otaczającymi ją tabelami wymiarów. Nazwa bierze się z rysunku: gdy narysujesz fakty w środku, a wymiary dookoła, powstaje kształt gwiazdy. Ten wzorzec nie jest ozdobą, to natywny język silnika Power BI. Silnik VertiPaq, który działa pod maską, kompresuje i liczy najszybciej właśnie wtedy, gdy dane są ułożone w gwiazdę.

Rozdzielenie na dwa rodzaje tabel jest kluczowe, więc rozłóżmy je na czynniki pierwsze na danych Nordvelli.

Tabela faktów: co się wydarzyło

Tabela faktów przechowuje mierzalne zdarzenia biznesowe. Każdy wiersz to jedno zdarzenie, które da się policzyć albo zsumować. W Nordvelli tą tabelą jest FaktSprzedaz z 108 675 wierszami, gdzie jeden wiersz to jedna pozycja transakcji: sprzedano konkretny produkt, w konkretnym sklepie, danego dnia, przez konkretnego sprzedawcę. Kolumny liczbowe (Ilosc, WartoscNetto, WartoscBrutto, KosztWlasny, MarzaNetto) to właśnie fakty, które potem sumujesz w miarach. Klucze obce (DataID, ProduktID, SklepID, PracownikID) to kolumny łączące fakty z wymiarami.

Tabela faktów jest zwykle długa i wąska. Nordvella potwierdza tę regułę: 108 675 wierszy, ale niewiele kolumn, a większość z nich to klucze i liczby. Nie trzymamy w faktach nazwy produktu ani miasta sklepu, tylko ich identyfikatory. To celowe, bo powtarzanie tekstu w stu tysiącach wierszy niepotrzebnie rozdmuchałoby model.

Tabela wymiaru: kontekst zdarzenia

Tabela wymiaru opisuje kontekst faktów, czyli odpowiada na pytania kto, co, kiedy i gdzie. Wymiary są zwykle krótkie i szerokie, mają mało wierszy, ale dużo opisowych kolumn, po których będziesz filtrować i grupować raporty. W Nordvelli DimProdukty ma tylko 30 wierszy, ale każdy niesie Nazwa, Kategoria, Podkategoria, Marka, Model i MarzaProc. To właśnie po tych kolumnach zbudujesz wykresy w kolejnych rozdziałach.

Poniżej pełna mapa dziewięciu tabel Nordvelli. Zwróć uwagę na dysproporcję liczby wierszy: główna tabela faktów ma ponad sto tysięcy wierszy, a wymiary od czterech do niecałych dwóch tysięcy. Tak wygląda zdrowo zaprojektowany model.

Nordvella to nie klasyczna czysta gwiazda z jedną tabelą faktów, tylko konstelacja: te same tabele wymiarów obsługują kilka tabel faktów naraz. DimKalendarz i DimSklepy to wymiary współdzielone, bo filtrują zarówno sprzedaż, jak i budżet, satysfakcję czy zwroty. To normalne i pożądane. Jedna tabela dat i jedna tabela sklepów dla całego modelu oznacza, że fragmentator daty przefiltruje wszystkie fakty jednocześnie.

Dlaczego nie jedna wielka płaska tabela

Naturalna pokusa początkującego to sklejenie wszystkiego w jeden arkusz: do każdej z 108 675 transakcji dopisać nazwę produktu, miasto sklepu, imię sprzedawcy i pełny opis dnia. Taka płaska tabela wygląda znajomo, bo przypomina eksport z Excela, ale ma trzy poważne wady. Po pierwsze puchnie: napis Warszawa i Laptop Kelvani Vela 13 powtórzony sto tysięcy razy to marnotrawstwo, którego kompresja VertiPaq nie znosi tak dobrze jak powtarzalnego klucza liczbowego. Po drugie miary DAX robią się kruche, bo funkcje inteligencji czasu potrzebują osobnej, ciągłej tabeli dat. Po trzecie tracisz elastyczność, bo dodanie nowego atrybutu produktu wymaga aktualizacji stu tysięcy wierszy zamiast trzydziestu.

Relacje jeden do wielu w widoku Model

Co chcemy osiągnąć. Bez relacji wykres sprzedaży Nordvelli według kategorii pokazałby przy każdej kategorii tę samą sumę całej firmy. Chcemy połączyć tabelę transakcji z wymiarami relacjami w widoku Model, tak żeby każdy wykres dzielił sprzedaż poprawnie.

Relacja to zdefiniowane połączenie między kluczem w tabeli wymiaru a kluczem obcym w tabeli faktów. Dopiero gdy relacja istnieje, silnik wie, że wiersz DimProdukty o identyfikatorze ProduktID pasuje do tysięcy wierszy FaktSprzedaz z tym samym ProduktID. Bez relacji wykres słupkowy sprzedaży wg kategorii pokaże dla każdej kategorii tę samą, całkowitą sumę, bo Power BI nie ma jak przypisać transakcji do kategorii.

Zaczynasz od przejścia do widoku Model. W lewej krawędzi Power BI Desktop klikasz trzecią ikonę od góry, tę z trzema połączonymi prostokątami. Zobaczysz dziewięć kafelków z nazwami tabel i listą kolumn oraz linie łączące. Power BI często wykrywa relacje automatycznie po zgodności nazw kolumn, ale automatowi nigdy nie ufamy w ciemno, tylko wszystko sprawdzamy.

Trzy sposoby stworzenia relacji

Masz trzy drogi, wybierz najwygodniejszą do sytuacji:

1. Przeciągnięcie na diagramie. Chwytasz nazwę kolumny DataID w kafelku DimKalendarz i przeciągasz ją na kolumnę DataID w kafelku FaktSprzedaz. Puszczasz przycisk myszy, a Power BI rysuje linię i sam proponuje kardynalność.
2. Okno Zarządzanie relacjami. Na wstążce Modelowanie albo Narzędzia główne klikasz Zarządzanie relacjami, potem Nowa relacja. Wybierasz z list rozwijanych obie tabele i klikasz kolumnę klucza w podglądzie. To najbardziej kontrolowana metoda, bo wszystkie parametry widzisz na jednym ekranie.
3. Wykrywanie automatyczne. Przycisk Autowykrywanie w oknie Zarządzanie relacjami skanuje model i tworzy relacje po zgodnych nazwach. Wygodne na start, ale zawsze przeglądasz efekt ręcznie.

W tym oknie od razu wychwytujesz problemy. Jeśli któraś relacja jest w kolumnie Stan oznaczona jako nieaktywna, filtry nią nie przepłyną. Jeśli brakuje relacji między FaktSprzedaz a DimPracownicy, wykresy sprzedaży wg sprzedawcy będą kłamać. Przejrzenie tej listy zajmuje minutę, a chroni przed godzinami debugowania miar, które zwracają dziwne liczby.

Komplet relacji Nordvella

Poniżej trzynaście relacji, które ustawiasz dla pełnego modelu na tym etapie. Dwie kolejne, do tabeli pomostowej i nieaktywną po dacie zakupu, dołożysz w dalszej części rozdziału, razem będzie ich piętnaście. Wszystkie są jeden do wielu (albo równoważnie wiele do jednego, zależnie od strony, którą wskażesz jako pierwszą) i wszystkie mają kierunek filtra Pojedynczy, biegnący od wymiaru do faktów.

// Wymiary do FaktSprzedaz (strona 1 to wymiar, strona * to fakty)
DimKalendarz[DataID]      1 --> * FaktSprzedaz[DataID]
DimProdukty[ProduktID]   1 --> * FaktSprzedaz[ProduktID]
DimSklepy[SklepID]       1 --> * FaktSprzedaz[SklepID]
DimPracownicy[PracownikID] 1 --> * FaktSprzedaz[PracownikID]

// Wymiary współdzielone do pozostałych faktów
DimSklepy[SklepID]       1 --> * FaktBudzet[SklepID]
DimSklepy[SklepID]       1 --> * FaktSatysfakcja[SklepID]
DimKalendarz[DataID]      1 --> * FaktSatysfakcja[DataID]
DimKalendarz[DataID]      1 --> * FaktBudzet[DataID]
DimKategorie[KategoriaID] 1 --> * FaktBudzet[KategoriaID]
DimKategorie[KategoriaID] 1 --> * DimProdukty[KategoriaID]
DimKalendarz[DataID]      1 --> * FaktZwroty[DataZwrotu]
DimProdukty[ProduktID]   1 --> * FaktZwroty[ProduktID]
DimSklepy[SklepID]       1 --> * FaktZwroty[SklepID]

Dwie sprawy wymagają przygotowania. FaktBudzet nie ma w arkuszu kolumny DataID, tylko Rok i Miesiac, bo budżet planuje się na miesiące. Dołóż ją kolumną obliczeniową: w widoku Dane zaznacz FaktBudzet, na karcie Narzędzia tabel kliknij Nowa kolumna i wpisz DataID = FaktBudzet[Rok] * 10000 + FaktBudzet[Miesiac] * 100 + 1. Każdy miesiąc budżetu trafia wtedy na pierwszy dzień miesiąca w DimKalendarz. Nową kolumnę kliknij prawym przyciskiem w panelu Dane i wybierz Ukryj w widoku raportu: to klucz do łączenia tabel, a nie pole do wykresów. Druga sprawa to relacje, które Power BI wykrył sam przy wczytywaniu arkusza. Otwórz Zarządzanie relacjami i porównaj listę z kodem powyżej. Relację FaktZwroty (SprzedazID) do FaktSprzedaz (SprzedazID) oraz DimPracownicy (SklepID) do DimSklepy (SklepID), jeśli je tam zobaczysz, usuń przyciskiem Usuń, bo łączą tabele z pominięciem wymiarów i tworzą drugą ścieżkę filtra. Relację z listy, która jest oznaczona jako nieaktywna, otwórz przyciskiem Edytuj i zaznacz Aktywuj tę relację. Po tych zmianach z kodu powyżej brakuje jeszcze dwóch relacji: DimKalendarz[DataID] do FaktBudzet[DataID], bo tej kolumny w budżecie przed chwilą nie było, oraz DimKalendarz[DataID] do FaktZwroty[DataZwrotu], której Power BI nie wykrył, bo kolumny nazywają się inaczej. Utwórz obie przyciskiem Nowa relacja. Na liście będzie wtedy trzynaście relacji, wszystkie aktywne.

Zwróć uwagę, że kluczem daty jest DataID, a nie sama kolumna Data. Nordvella używa liczbowego identyfikatora dnia jako klucza łączącego, bo relacje na liczbach całkowitych są szybsze i mniej podatne na błędy niż na typie daty. Osobno, w widoku Dane, oznaczysz DimKalendarz jako tabelę dat wskazując kolumnę Data, ale to sygnał dla funkcji inteligencji czasu, a nie klucz relacji. Do tej różnicy wrócimy w rozdziale o inteligencji czasu.

Zobacz też: Szkolenie Power BI - Desktop, DAX i Online + Copilot

Kardynalność i kierunek filtrowania krzyżowego

Co chcemy osiągnąć. Źle ustawiona relacja potrafi pokazać w raporcie Nordvelli liczby, które wyglądają wiarygodnie, a są błędne. Chcemy ustawić kardynalność i kierunek filtrowania tak, żeby każda kwota w raporcie była policzona poprawnie.

Gdy w oknie Zarządzanie relacjami klikniesz Edytuj przy dowolnej relacji, otworzy się okno Edytowanie relacji. To tu ustawiasz dwa parametry, które decydują o poprawności całego modelu: Kardynalność i Kierunek filtrowania krzyżowego. Zatrzymajmy się na nich, bo błąd w tym miejscu jest najczęstszą przyczyną raportów pokazujących zawyżone albo zaniżone liczby.

Kardynalność: która strona jest jedna

Kardynalność, czyli liczność, mówi silnikowi, ile wierszy po jednej stronie odpowiada ilu wierszom po drugiej. Lista rozwijana daje cztery opcje, ale w zdrowej gwieździe używasz praktycznie jednej.

W praktyce, gdy przeciągasz DimProdukty na FaktSprzedaz, Power BI sam rozpozna, że ProduktID w DimProdukty jest unikalny (30 różnych produktów), a w FaktSprzedaz się powtarza (dziesiątki tysięcy transakcji na te same 30 produktów), i zaproponuje Wiele do jednego. To poprawnie: strona jeden zawsze siedzi po stronie wymiaru, bo tam klucz jest unikalny.

Kierunek filtra: Pojedynczy kontra Oba

Kierunek filtrowania krzyżowego mówi, w którą stronę filtr przepływa relacją. Masz dwie opcje. Pojedynczy oznacza, że filtr biegnie tylko od wymiaru do faktów. Wybierasz Kraków w DimSklepy, a filtr spływa do FaktSprzedaz i zawęża transakcje. Odwrotnie się nie da, bo nie ma takiej potrzeby. To ustawienie domyślne, przewidywalne i bezpieczne. Trzymaj się go w każdej relacji, chyba że masz naprawdę dobry powód.

Oba włącza filtrowanie obustronne, filtr biegnie w obie strony relacji. Brzmi wygodnie, ale w modelu z kilkoma tabelami faktów potrafi tworzyć niejednoznaczne ścieżki filtrowania i zwracać liczby, których nie sposób wytłumaczyć. W Nordvelli, gdzie DimSklepy karmi filtrami zarówno FaktSprzedaz, jak i FaktBudzet oraz FaktZwroty, ustawienie Oba na jednej z tych relacji może przenieść filtr z jednej tabeli faktów przez wspólny wymiar do drugiej i policzyć coś, czego nikt nie chciał.

// Zamiast włączać kierunek Oba na stałe, w pojedynczej mierze
// możesz jednorazowo wymusić obustronny przepływ funkcją CROSSFILTER.
// Przykład (ilustracja, nie musisz jej zakładać): policz produkty, które się sprzedały, filtrując od faktów do wymiaru.
Sprzedane produkty =
CALCULATE(
    DISTINCTCOUNT(DimProdukty[ProduktID]),
    CROSSFILTER(FaktSprzedaz[ProduktID], DimProdukty[ProduktID], BOTH)
)

To bezpieczniejsze podejście niż globalne Oba na relacji. Kierunek obustronny działa tylko wewnątrz tej jednej miary, przez czas jej liczenia, i nie zatruwa reszty modelu. W praktyce zawodowej stosujemy tę regułę: relacje ustawiamy na Pojedynczy, a gdy pojedyncza miara naprawdę potrzebuje przepływu w drugą stronę, sięgamy po CROSSFILTER punktowo.

Tabela pomostowa dla relacji wiele do wielu

Co chcemy osiągnąć. Nordvella chce wiedzieć, z jakich powodów klienci zwracają towar, a jeden zwrot może mieć kilka powodów naraz, i jeden powód dotyczy wielu zwrotów. Chcemy zbudować model, w którym analiza powodów zwrotów pokazuje poprawne liczby mimo takiej relacji.

Nie każda relacja układa się w czystą jedynkę i gwiazdkę. W Nordvelli naturalnym kandydatem na problem są zwroty. Wyobraź sobie, że chcesz analizować powody zwrotów, ale jeden zwrot może mieć kilka przyczyn naraz (produkt uszkodzony i pomyłka w zamówieniu), a jedna przyczyna dotyczy wielu zwrotów. To klasyczna relacja wiele do wielu, której nie chcesz modelować bezpośrednio, bo silnik gubi się w liczeniu i łatwo o zawyżone sumy.

Rozwiązaniem jest tabela pomostowa, po angielsku bridge table. To mała tabela techniczna, która staje między dwiema tabelami połączonymi wiele do wielu i rozbija tę relację na dwie relacje jeden do wielu. Zamiast łączyć zwroty z powodami wprost, wstawiasz między nie tabelę z unikalną listą powodów.

Jak zbudować pomost w Nordvelli

Krok po kroku. Najpierw tworzysz tabelę wymiaru z unikalnymi powodami. Przejdź do widoku Dane (druga ikona na lewym pasku), na wstążce kliknij kartę Narzędzia tabel, a w niej Nowa tabela. W pasku formuły pojawi się szablon Tabela = . Zaznacz go w całości i wpisz w to miejsce wyrażenie, które wyciąga z FaktZwroty listę różnych wartości kolumny Powod.

DimPowodyZwrotow = DISTINCT( FaktZwroty[Powod] )

Zatwierdź wyrażenie zielonym znaczkiem po lewej stronie paska formuły. Power BI przeliczy tabelę i od razu pokaże jej zawartość.

Funkcja DISTINCT gwarantuje, że każdy powód wystąpi tylko raz, więc ta kolumna jest unikalna i nadaje się na stronę jeden relacji. Teraz przejdź do widoku Model (trzecia ikona na lewym pasku). Na karcie Wszystkie tabele nowa tabela ginie wśród dziewięciu pozostałych, więc kliknij plus obok kart u dołu ekranu. Powstanie pusta karta Układ 1. Przeciągnij na nią z panelu Dane dwie tabele: FaktZwroty i DimPowodyZwrotow.

Chwyć kolumnę Powod w karcie FaktZwroty i upuść ją na kolumnie Powod w karcie DimPowodyZwrotow. Power BI otworzy okno Nowa relacja z wypełnionymi polami Z tabeli: FaktZwroty i Do tabeli: DimPowodyZwrotow. Sprawdź, że Kardynalność to Wiele do jednego (*:1), Kierunek filtrowania krzyżowego to Pojedyncza, a pole Aktywuj tę relację jest zaznaczone, i kliknij Zapisz. Kierunek przeciągania zmienia tylko opis: gdy przeciągniesz odwrotnie, z DimPowodyZwrotow na FaktZwroty, okno pokaże Jeden do wielu (1:*), czyli tę samą relację opisaną od drugiej strony.

Po zapisaniu na diagramie pojawi się linia z gwiazdką przy FaktZwroty i jedynką przy DimPowodyZwrotow. Kliknięcie linii pokazuje te same ustawienia w panelu Właściwości, a przycisk Otwórz edytor relacji otwiera je ponownie w oknie Edytowanie relacji. Tam wrócisz, gdy zechcesz coś zmienić.

Efekt jest taki, że możesz teraz zbudować wykres słupkowy z powodami zwrotu na osi (z DimPowodyZwrotow) i liczbą albo wartością zwrotów jako wysokością słupka (z FaktZwroty). Filtr płynie od czystej listy powodów do faktów, sumy nie dublują się, a fragmentator powodu przefiltruje resztę raportu. Ta sama technika ratuje każdą sytuację wiele do wielu: cennik obowiązujący w przedziałach dat, przypisanie sprzedawcy do kilku sklepów, produkt należący do kilku kampanii.

Liczby w kolumnie wysokości słupka wyliczy prosta miara na faktach zwrotów, którą pokażemy w rozdziałach o DAX. Ważne, że dzięki pomostowi ta miara policzy się jednoznacznie, bo silnik dostaje czystą relację jeden do wielu zamiast dwuznacznego wiele do wielu.

Efekt filtrowania krzyżowego: przed i po kliknięciu

Co chcemy osiągnąć. Menedżer, który klika na pulpicie jedno miasto, oczekuje, że pozostałe wykresy od razu pokażą dane tylko tego miasta. Chcemy zobaczyć na modelu Nordvelli, że zbudowane relacje dają dokładnie taki efekt.

Cała ta praca nad relacjami ma jeden namacalny cel: interaktywność raportu. Filtrowanie krzyżowe to mechanizm, w którym kliknięcie w jeden wizual automatycznie zawęża wszystkie pozostałe. Zobaczmy go w działaniu na modelu Nordvelli, który właśnie zbudowaliśmy. Pulpit z dwóch zrzutów poniżej pochodzi z naszego pliku roboczego. Miarę Łączna sprzedaż napiszesz w lekcji 07, a sam pulpit zbudujesz w lekcji 08, dlatego w pliku efektu tej lekcji go nie ma. Tutaj chodzi o mechanizm, który daje ten model.

Wyobraź sobie pulpit z czterema wizualami: wykresem słupkowym sprzedaży wg miast, kartą z całkowitą sumą sprzedaży, wykresem kołowym udziału kategorii i wykresem liniowym trendu w czasie. Przed jakimkolwiek kliknięciem każdy wizual pokazuje obraz całej firmy. Karta pokazuje łączną sprzedaż wszystkich sklepów, słupki idą od Warszawy z 222,8 mln złotych na szczycie do Białegostoku z 12,3 mln na dole, a trend rysuje sezonowość wszystkich miast razem.

Teraz klikasz w słupek Kraków na wykresie miast. W jednej chwili cały raport reaguje. Karta przelicza sumę i pokazuje już tylko sprzedaż krakowskich sklepów. Kołowy wykres kategorii pokazuje strukturę sprzedaży wyłącznie w Krakowie, więc udziały kategorii AGD/RTV, Akcesoria, Komputery i Telefony mogą się różnić od średniej firmowej. Wykres trendu rysuje krzywą sprzedaży tylko krakowskich transakcji. Pozostałe słupki miast wyszarzają się, żeby podkreślić Twój wybór. Drugie kliknięcie w ten sam słupek zdejmuje filtr i wszystko wraca do widoku całej firmy.

Mechanicznie stało się coś prostego: wybór Kraków zawęził tabelę DimSklepy do jednego wiersza, a ponieważ istnieje relacja DimSklepy do FaktSprzedaz z kierunkiem Pojedynczy, filtr spłynął do faktów. Każda miara policzona na FaktSprzedaz widzi teraz tylko krakowskie transakcje, więc każdy wizual korzystający z tych miar automatycznie pokazuje krakowski wycinek. Gdyby relacji nie było albo miała odznaczone pole Aktywuj tę relację, kliknięcie nie zrobiłoby nic, a to najczęstszy objaw zepsutego modelu, który zgłaszają początkujący.

Relacje nieaktywne i USERELATIONSHIP

Co chcemy osiągnąć. Nordvella chce analizować zwroty raz według daty zwrotu, a raz według daty pierwotnego zakupu, a między dwiema tabelami Power BI dopuszcza tylko jedną aktywną relację. Chcemy mieć w jednym raporcie oba spojrzenia, każde w osobnej mierze.

Zdarza się, że dwie tabele łączy więcej niż jedna sensowna relacja. Klasyczny przypadek w Nordvelli to zwroty. FaktZwroty ma kolumnę daty zwrotu, ale odnosi się też do daty pierwotnego zakupu przez powiązanie ze SprzedazID. Power BI pozwala mieć tylko jedną aktywną relację między parą tabel po tym samym łańcuchu, więc drugą trzyma jako nieaktywną, narysowaną na diagramie linią przerywaną.

Nieaktywna relacja domyślnie nie przepuszcza filtra, ale możesz ją włączyć punktowo wewnątrz miary funkcją USERELATIONSHIP. Dzięki temu jeden raport analizuje zwroty na dwa sposoby, w zależności od tego, którą miarę użyjesz na wizualu.

Zanim zbudujesz te dwie miary, potrzebujesz kolumny, po której pójdzie druga relacja. W arkuszu Nordvelli tabela FaktZwroty trzyma tylko datę zwrotu - daty zakupu w niej nie ma, bo ta siedzi w tabeli sprzedaży. Dociągasz ją kolumną obliczeniową: w widoku Dane zaznacz FaktZwroty w panelu Dane, na karcie Narzędzia tabel kliknij Nowa kolumna i w pasku formuły wpisz poniższą formułę. LOOKUPVALUE działa tu jak wyszukiwanie pionowe: dla każdego zwrotu odnajduje wiersz sprzedaży o tym samym SprzedazID i zwraca z niego klucz daty.

DataZakupu =
LOOKUPVALUE (
    FaktSprzedaz[DataID],
    FaktSprzedaz[SprzedazID],
    FaktZwroty[SprzedazID]
)

Zatwierdź formułę zielonym znaczkiem przy pasku formuły. Kolumna DataZakupu pojawi się w FaktZwroty z kluczem daty zakupu w każdym wierszu.

Dopiero teraz możesz poprowadzić z niej drugą relację do DimKalendarz[DataID]. W widoku Model przeciągnij na kartę Układ 1 tabelę DimKalendarz, a potem chwyć DataZakupu w FaktZwroty i upuść na DataID w DimKalendarz. W oknie Nowa relacja pole Aktywuj tę relację jest już odznaczone: aktywna ścieżka do kalendarza biegnie po dacie zwrotu, a Power BI pilnuje, żeby między dwiema tabelami była tylko jedna aktywna. Kliknij Zapisz.

Teraz dwie miary. Każdą zakładasz osobno: zaznacz FaktZwroty w panelu Dane, kliknij Nowa miara, wklej kod jednej miary i zatwierdź. Pierwsza liczy wartość zwrotów według daty zwrotu, czyli po relacji aktywnej:

Wartosc zwrotow wg daty zwrotu =
SUM( FaktZwroty[WartoscZwrotu] )

Druga liczy tę samą wartość według daty zakupu. USERELATIONSHIP włącza w niej punktowo relację nieaktywną:

Wartosc zwrotow wg daty zakupu =
CALCULATE(
    SUM( FaktZwroty[WartoscZwrotu] ),
    USERELATIONSHIP( FaktZwroty[DataZakupu], DimKalendarz[DataID] )
)

Umieszczasz obie miary na jednym wykresie liniowym z osią czasu z DimKalendarz. W widoku raportu, na pustej stronie Strona 1, kliknij w panelu Wizualizacje ikonę Wykres liniowy, w panelu Dane zaznacz w DimKalendarz pole RokMiesiac (trafi do obszaru Oś X), a w FaktZwroty obie nowe miary (trafią do Oś Y). Rozciągnij wizual za prawy dolny róg na całą stronę. Domyślnie Power BI układa miesiące według wartości miary, więc w menu Więcej opcji (trzy kropki w rogu wizualu) ustaw sortowanie po RokMiesiac rosnąco. Od razu widzisz dwie krzywe: jedna pokazuje, kiedy zwroty trafiły do sklepu, druga, z jakich zakupów pochodzą. Ten wzorzec daje każda gwiazda, w której druga data ma własną, nieaktywną relację do kalendarza. USERELATIONSHIP nie zmienia modelu na stałe, działa tylko w obrębie tej jednej miary, dokładnie jak CROSSFILTER dla kierunku.

Higiena modelu: co jeszcze warto ustawić

Co chcemy osiągnąć. Raport Nordvelli będą rozbudowywać także inne osoby, które na liście pól powinny widzieć tylko to, co nadaje się na wykres. Chcemy uporządkować model, między innymi ukryć kolumny techniczne i oznaczyć tabelę dat, zanim zaczniemy budować wizualizacje.

Poprawne relacje to serce modelu, ale kilka drobnych ustawień zamienia model dobry w model profesjonalny. Warto je zrobić od razu, zanim zaczniesz budować wizualizacje, bo później wracanie do modelu jest bardziej uciążliwe.

Ukryj klucze obce w faktach. Kolumny DataID, ProduktID, SklepID i PracownikID w FaktSprzedaz służą wyłącznie do łączenia tabel. Osoba budująca raport nie powinna przeciągać ich na wykresy, więc lepiej, żeby nie widziała ich na liście pól. W widoku Dane rozwiń FaktSprzedaz w panelu Dane, kliknij prawym przyciskiem DataID i wybierz Ukryj w widoku raportu. Powtórz to dla ProduktID, SklepID i PracownikID. Model zostaje czysty, a osoba budująca wizuale nie pomyli klucza z atrybutem.

Oznacz DimKalendarz jako tabelę dat. W widoku Dane kliknij DimKalendarz w panelu Dane, a na karcie Narzędzia tabel kliknij Oznacz jako tabelę dat. W oknie przestaw przełącznik Oznacz jako tabelę dat na Włączony, z listy Wybierz kolumnę daty wybierz Data, poczekaj na komunikat Walidacja zakończona pomyślnie i kliknij Zapisz. Bez tego funkcje inteligencji czasu, jak liczenie narastająco od początku roku czy porównanie rok do roku, mogą liczyć błędnie. To jedno okno, które oszczędza wiele późniejszych zagadek.

Sprawdź typy kolumn kluczy. Klucze łączące powinny być liczbami całkowitymi po obu stronach relacji. Jeśli w jednej tabeli ProduktID jest liczbą, a w drugiej tekstem, relacja albo się nie utworzy, albo będzie wolna. To częsta cicha usterka po imporcie z różnych źródeł.

Sieroty produktow =
// Szybki test spójności: ile transakcji nie znajduje produktu w wymiarze?
// Zero oznacza czystą gwiazdę. Wartość dodatnia to sieroty do naprawienia.
COUNTROWS(
    FILTER(
        FaktSprzedaz,
        ISBLANK( RELATED( DimProdukty[ProduktID] ) )
    )
) + 0

Ta miara diagnostyczna liczy transakcje, których ProduktID nie ma odpowiednika w DimProdukty. Jeśli zwraca coś więcej niż zero, masz w faktach klucze wskazujące na nieistniejące produkty, tak zwane sieroty. W poprawnie zbudowanym modelu Nordvelli wynik powinien być równy zero. Zakładasz ją jak poprzednie miary: w widoku Dane zaznacz FaktSprzedaz, kliknij Nowa miara, wklej kod i zatwierdź. W tym kodzie liczą się dwa szczegóły. Komentarze są pod linią z nazwą, bo wklejone nad nią Power BI dokleja do nazwy miary (sprawdziliśmy: miara dostaje wtedy nazwę zaczynającą się od //). Końcowe + 0 zamienia pusty wynik na zero, bo COUNTROWS z pustej tabeli nie zwraca 0, tylko wartość pustą.

Żeby zobaczyć wynik, przejdź do widoku raportu, otwórz pustą Stronę 2 na dole ekranu (jeśli w Twoim pliku jej nie ma, dodaj ją plusem obok kart stron), kliknij w panelu Wizualizacje ikonę Karta i zaznacz w panelu Dane miarę Sieroty produktow. Tak wygląda karta, gdy w kodzie zabraknie końcowego + 0:

Z dopiskiem + 0 ta sama karta pokazuje 0. Kod miary możesz w każdej chwili poprawić: kliknij miarę w panelu Dane, popraw formułę w pasku i zatwierdź.

Ten sam test zrób dla pozostałych wymiarów. Miary Sieroty sklepow i Sieroty dat różnią się od Sieroty produktow tylko kolumną w RELATED: DimSklepy[SklepID] i DimKalendarz[DataID]. Każdą wstaw na osobną kartę na tej samej stronie i dostaniesz stronę kontrolną modelu. Uruchamiaj ten test zawsze, gdy przejmujesz cudzy plik: trzy karty od razu mówią, ile jest wart jego model.

Gdy wynik jest większy od zera, sprawdź, których kluczy brakuje: w widoku Dane przefiltruj FaktSprzedaz po pustej kolumnie z wymiaru. Zaznacz FaktSprzedaz, kliknij Nowa kolumna i wpisz tymczasową kolumnę Nazwa produktu = RELATED( DimProdukty[Nazwa] ). Potem kliknij strzałkę w nagłówku nowej kolumny, odznacz (Zaznacz wszystko), zaznacz samo (Puste) i kliknij OK. Zostaną wiersze, których produktu nie ma w wymiarze.

Na koniec usuń tymczasową kolumnę: kliknij ją prawym przyciskiem w panelu Dane i wybierz Usuń z modelu. Przyczynę sierot naprawiasz w Power Query: dopisz brakujące pozycje do tabeli wymiaru albo popraw klucz w tabeli faktów, jeśli to literówka lub zły format.

Podsumowanie i co dalej

Zbudowaliśmy pełny model gwiazdy Nordvella: FaktSprzedaz w centrum, cztery wymiary dookoła i trzy dodatkowe tabele faktów podpięte pod współdzielone wymiary. Nauczyliśmy się tworzyć relacje trzema sposobami, świadomie ustawiać Kardynalność na Wiele do jednego i Kierunek filtrowania na Pojedynczy, rozwiązywać relacje wiele do wielu tabelą pomostową oraz sięgać po CROSSFILTER i USERELATIONSHIP punktowo, gdy pojedyncza miara naprawdę tego potrzebuje. Na koniec zobaczyliśmy, jak to wszystko owocuje interaktywnym raportem, w którym kliknięcie w Kraków przefiltrowuje cały pulpit.

Model jest gotowy i sprawdzony miarą sierot. W kolejnym rozdziale napiszemy na tym modelu pierwsze miary DAX, w tym Łączna sprzedaż, i zobaczysz, jak relacje decydują o kontekście, w którym się liczą. W lekcji 08 zbudujemy z tych miar pierwszy pulpit Nordvelli: wykres sprzedaży wg miast, kartę, wykres kołowy kategorii i liniowy trend w czasie. Dzięki poprawnym relacjom budowa wykresów sprowadza się wtedy do przeciągania pól, a filtrowanie krzyżowe działa bez dodatkowej pracy.

✕