Model danych usługi Power BI z wieloma relacjami w tej samej tabeli (2023)

Przez:Harrisa Amjada|Zaktualizowano: 2023-05-19 |Uwagi| Powiązane: >Power BI

Problem

Czasami potrzeba wielu relacji między tymi samymi tabelami w dashboardzie Microsoft Power BI. Jednak podczas modelowania zobaczysz, że usługa Power BI sprawia, że ​​jedna z relacji jest nieaktywna podczas obliczeń lub wizualizacji. W tym samouczku omówiono funkcję języka DAX, która pomaga w uaktywnianiu nieaktywnych relacji zgodnie z potrzebami obliczeń lub wizualizacji. Ten samouczek będzie działał jako przewodnik krok po kroku dotyczący korzystania z języka DAX w celu ułatwienia tworzenia wielu relacji między tą samą tabelą w projektach analizy biznesowej usługi Power BI Desktop.

Rozwiązanie

Wraz z nadejściem rewolucji big data nasz świat stał się bardzo nasycony dużymi ilościami informacji, co stale zmienia nasze potrzeby w zakresie danych i związane z nimi technologie. Aby zapewnić efektywne wykorzystanie tych danych przez różne podmioty, korzystamy z komputerowego systemu przechowywania i wydobywania danych za pośrednictwem system zarządzania bazą danych (DBMS). Ogólnie rzecz biorąc, bazy danych składają się z różnych tabel, w których każdy wiersz może być jednoznacznie identyfikowany przez określoną kolumnę (pole) znane jako klucz podstawowy. Jednak prawdziwa moc i struktura relacyjnej bazy danych polega na ustanawianiu relacji między jej tabelami. Łącząc powiązane dane w tabelach, można tworzyć złożone i sensowne zapytania, które pozwalają wydobywać cenne spostrzeżenia do analizy danych. W tym samouczku ocenimy, jak utworzyć wiele relacji między tymi samymi tabelami w usłudze Power BI.

Na przykład na powyższym obrazku możemy zobaczyć model danych Power BI, który pozwala nam obserwować, jak dane są zorganizowane i powiązane ze sobą w bazie danych. Każdy blok reprezentuje tabelę wraz z odpowiednimi polami/kolumnami. Co ważniejsze, linie i strzałki między tabelami oznaczają relację liczności w obrębie schematu. Warto również zauważyć, że te linie są oznaczone „1” i „*”. Co to oznacza?

Te symbole odnoszą się do różnych typów relacji w bazie danych. Oni mogą być:

1. Jeden na jednego:Zależność ta istnieje, gdy pojedynczy rekord w jednej tabeli jest powiązany tylko z jednym rekordem w innej tabeli i odwrotnie. Załóżmy na przykład, że organizujesz informacje o swoich pracownikach i chcesz śledzić ich paszporty. Jest to klasyczny przykład relacji jeden do jednego, ponieważ każda osoba może mieć tylko jeden paszport, a każdy paszport jest wydawany tylko jednemu osoba. Takie relacje są jednak rzadkością, ponieważ możemy łatwo przechowywać odpowiednie informacje w tej samej tabeli. W powyższym przykładzie linia połączona przez dwa „1” po obu stronach będzie oznaczać ten rodzaj relacji.
2. Jeden za dużo: Ten związek istnieje, gdy pojedynczy rekord w jednej tabeli może być powiązany z wieloma rekordami w innej tabeli. Jednak każdy rekord w drugiej tabeli może być powiązany tylko z jednym rekordem w pierwszej tabeli. Przykładem takiej relacji mogą być klienci i ich zamówienia. Jeden klient może złożyć wiele zamówień; jednak każde zamówienie należy tylko do jednego klienta. Jest to najbardziej powszechny typ relacji z bazą danych, aw powyższym schemacie jest on reprezentowany przez „*” i „1” między odpowiednimi tabelami.
3. Wiele do wielu: Ta zależność istnieje, gdy wiele rekordów w jednej tabeli może być powiązanych z wieloma rekordami w innej tabeli. Na przykład w bazie danych firmy wydawniczej autorzy i książki mogą mieć relację wiele do wielu, ponieważ każda książka może być napisana przez kilku autorów, a każdy autor może również napisać kilka książek. Tabela połączeń jest używana w normalnych przypadkach, aby zapobiec duplikowaniu danych. Zwykle zawiera dwa klucze obce, które odwołują się do kluczy podstawowych oryginalnych tabel, wraz z wszelkimi dodatkowymi polami specyficznymi dla relacji między dwiema tabelami. Ten związek jest oznaczony przez dwa „*” po obu stronach linii na powyższym schemacie.

Biorąc pod uwagę dużą różnorodność przypadków użycia DBMS, możliwe jest, że wiele relacji typów, które omówiliśmy powyżej, może pochodzić z naszych tabel. Przedstawimy teraz praktyczną demonstrację, w ramach której utworzymy schemat w MySQL, a następnie będziemy obserwować i manipulować naszym modelem danych w Power BI.

Tworzenie schematu w MySQL

W tej demonstracji interesuje nas miesięczna sprzedaż w księgarni. Obecnie nasz schemat będzie się składał z pojedynczej tabeli z następującymi polami:

• Data zamówienia
• Data wysyłki
• Klucz produktu
• Identyfikator klienta
• Łączna sprzedaż

Aby rozpocząć, najpierw utworzymy naszą bazę danych, a następnie uzyskamy do niej dostęp za pomocą następujących poleceń:

UTWÓRZ BAZY DANYCH bookstore_sales; UŻYWAJ bookstore_sales;

Teraz utworzymy naszą tabelę, korzystając z następujących instrukcji, które będą zawierać pola wymienione powyżej:

UTWÓRZ TABELĘ Month_sales(order_date date, ship_date date, product_key varchar(6), customer_id varchar(8), sales int unsigned);

Teraz, gdy mamy szkieletową strukturę naszego stołu, możemy go wypełnić, wykonując następujące polecenia:

WSTAW DO WARTOŚCI_sprzedaży_miesięcznej("2023-02-01", "2023-02-02", "BK1234", "LHR78902", 20),("2023-02-02", "2023-02-02", " BK4568", "LHR34901", 50),("2023-02-04", "2023-02-04", "MV1890", "KHI17392", 30),("2023-02-04", "2023- 02-05", "BK0098", "LHR19028", 10),("2023-02-05", "2023-02-05", "MV2900", "RWP16370", 60),("2023-02- 06", "2023-02-07", "MG1902", "MUL29104", 30), ("2023-02-09", "2023-02-15", "MG1839", "SIA38291", 40), ("2023-02-10", "2023-02-15", "BK2891", "LHR00382", 80),("2023-02-14", "2023-02-15", "BK0032", " ISL10394", 30),("2023-02-15", "2023-02-15", "MV9001", "LHR01919", 20),("2023-02-18", "2023-02-20" , "MV8834", "MUL18301", 50),("2023-02-20", "2023-02-20", "MG0219", "KHI32413", 40),("22.02.2023", " 27.02.2023", "BK6002", "ISL90029", 80),("23.02.2023", "27.02.2023", "BK9921", "BWP82716", 60),("2023- 02-26", "2023-02-27", "MG0192", "LHR10284", 40);

Na koniec możemy sprawdzić naszą tabelę za pomocą instrukcji SELECT w MySQL:

WYBIERZ * Z bookstore_sales.monthly_sales;

Spowoduje to wyświetlenie naszej tabeli źródłowej, jak pokazano poniżej.

Wiele relacji w Power BI

Teraz, gdy mamy bazę danych, możemy ją zaimportować do usługi Power BI, aby obserwować wiele relacji pojawiających się między tymi samymi tabelami.

Krok 1

Zanim przejdziemy do sedna naszego tematu, musimy zaimportować naszą bazę danych z MySQL do Power BI. Aby to zrobić, kliknijOtrzymać danewDomwstążkę i kliknijWięcej…na dole wynikowej listy. Jak pokazano poniżej, możemy zaobserwować szereg typowych źródeł danych, z którymi możemy pracować w usłudze Power BI.

Krok 2

Pojawi się okno Pobierz dane. Pod polem wyszukiwania wybierzBaza danychkategorię i wybierzBaza danych MySQLopcję w prawo, jak pokazano poniżej. Następnie kliknijŁączyćna dole okna. Ten krok jest elastyczny w zależności od platformy, na której hostowana jest baza danych.

Krok 3

Pojawi się okno bazy danych MySQL. Wprowadź odpowiednie poświadczenia serwera i bazy danych w oknach dialogowych i kliknijOK.

Krok 4

Okno Nawigatora pojawi się, jeśli usługa Power BI pomyślnie połączy się z Twoją bazą danych. PoniżejOpcje wyświetlania, zaznacz pole wyboru obok tabeli „bookstore_sales.monthly_sales” i kliknijObciążeniena dole okna. Usługa Power BI umożliwia również użytkownikom obserwowanie ładowanych przez nich tabel, jak pokazano poniżej. Jeśli występują jakiekolwiek anomalie w zbiorze danych, możemy je również rozwiązać za pomocąPrzekształć daneopcja na dole okna, która przeniesie nas do okna edytora Power Query, w którym dostępne są narzędzia do manipulacji naszym zbiorem danych. Jednak nie musimy się w to zagłębiać, ponieważ nasza baza danych jest kompletna i czysta.

Krok 5

Po pomyślnym załadowaniu naszych wybranych tabel do Power BI jesteśmy gotowi przejść do sedna naszego głównego problemu. Obecnie nasza baza danych składa się tylko z jednej tabeli. Asit jest pojedynczą jednostką, nie ma możliwości tworzenia powiązań z bazami danych.

Dla naszych celów skupimy się na sprzedaży książek poprzez wysyłkę i zamówienia. Zanim będziemy mogli obliczyć te liczby, musimy utworzyć tabelę dat, która zakoduje wszystkie informacje dotyczące dat wymaganych w tym zbiorze danych. Będzie ona zawierać kolumnę dat obejmującąrok,kwartał,miesiąc, Idzieńobok oddzielnych kolumn dla „miesiąca”, „roku”, „indeksu miesiąca” i pola „miesiąc roku”, które przechwytuje połączone rok i miesiąc.

W głównym interfejsie Power BI, podModelowaniewstążka, kliknijNowy stół, jak pokazano niżej.

W polu formuły wprowadź następującą formułę języka DAX i kliknijWchodzić:

DateTable = GENERUJ ( KALENDARZ ( DATA ( 2023, 2, 1 ), DATA ( 2023, 2, 28 ) ), VAR bieżący dzień = [Data] VAR startYear = 2023 VAR miesiąc = MIESIĄC ( bieżący dzień ) VAR rok = ROK ( bieżący dzień ) RETURN ROW ("miesiąc", miesiąc, "rok", rok, "indeks miesiąca", INT (( rok - początekRok) * 12 + miesiąc), "RokMiesiąc", rok * 100 + miesiąc) )

Krok 6

WybierzTabelaikonę w panelu Wizualizacje, aby obserwować naszą nowo utworzoną tabelę, jak pokazano poniżej. To jest wstępnie zbudowana wizualizacja i możemy ją wypełnić, wybierając lub przeciągając wszystkie kolumny „DateTable” do pola „Columns”, jak pokazano poniżej.

Możemy teraz zobaczyć naszą tabelę w głównym obszarze roboczym Power BI, przedstawionym poniżej.

Krok 7

Teraz, gdy mamy w bazie dwie tabele, istnieje możliwość powiązań między nimi. KliknijWidok modeluikonę w prawo (patrz poniżej), aby zwizualizować nasz obecny schemat bazy danych.

Aby ustanowić relacje między tymi samymi tabelami, przeciągnij kolumnę „Date” z naszej „DateTable” i połącz ją z kolumną „order_date” drugiej tabeli. To stworzy nasz pierwszy związek. W przypadku drugiego przeciągnij ponownie kolumnę „Data” i połącz ją z kolumną „data_wysyłki” w naszej tabeli „sprzedaż_miesięczna”. Ten proces opisano poniżej:

Możemy zaobserwować, że tabele „DateTable” i „monthly_sales” mają dwie relacje jeden-do-wielu. Warto jednak zauważyć, że relacja między „Date” a „ship_date” jest nieaktywna (pokazana linią przerywaną), ponieważ usługa PowerBI zezwala tylko na jedno aktywne połączenie między tabelami.

Krok 8

Teraz musimy wymyślić metodę wykorzystania nieaktywnej relacji. Aby zobaczyć, co robią nasze dane, możemy wrócić do naszego panelu widoku raportu, gdzie na innej stronie będziemy tworzyć nową wizualizację tabeli. Po wybraniuTabelamożemy ponownie wypełnić nasze pole „kolumny”, wybierając kolumny „Data” i „Sprzedaż”, jak opisano poniżej.

Nasza wynikowa tabela wygląda następująco:

Widzimy, jak dane są kategoryzowane i jak sprzedaż sumuje się do 640 jednostek. A co jeśli chcemy rozróżnić sprzedaż według daty zamówienia i daty wysyłki?

Krok 9

Musimy obliczyć dwie osobne miary, które sumują sprzedaż według dat zamówień i dat wysyłki. W tym celu wDomwstążka, kliknijnowy środek,jak pokazano niżej:

Aby utworzyć naszą pierwszą miarę, która jawnie sumuje według dat zamówień, wprowadź następującą formułę języka DAX na pasku formuły:

OrderDateSales = OBLICZ (SUMA ('bookstore_sales Month_sales'[Sales] ), USERELATIONSHIP('DateTable'[Date], 'bookstore_sales Month_sales'[order_date] ) )

Aby utworzyć drugą miarę dla dat wysyłki według daty wysyłki, powtórz powyższy proces z następującą formułą języka DAX:

ShipDateSales = OBLICZ (SUMA ('bookstore_sales Month_sales'[Sales] ), USERELATIONSHIP('DateTable'[Date], 'bookstore_sales Month_sales'[ship_date] ) )

Krok 10

Teraz, gdy mamy już wyraźnie obliczone miary, utwórz kolejną wizualizację tabeli i wypełnij ją kolumnami „sprzedaż” i „Data”, tak jak poprzednio, ale dla nowych miar, jak pokazano poniżej.

Widzimy teraz (poniżej), że nasza sprzedaż książek została podzielona na kategorie według dat zamówień i dat wysyłki, a wszystko to aż do początkowej liczby 640 jednostek. Zapewnia to jasność, że nasze obliczenia działają poprawnie.

Krok 11

Możemy również wizualizować te dwie relacje za pomocą różnych elementów graficznych, takich jak wykres liniowy. WybierzWykres liniowyikona wWizualizacjei wypełnij pole „Oś X” kolumną „Data”, jak pokazano poniżej. Dla osi Y wybierz miary „DataZamówieniaSprzedaż” i „DataWysyłkiSprzedaż”.

Po kilku dostosowaniach poniżej znajduje się nasza nowa grafika:

Teraz wyraźnie widać, że przez większą część lutego księgarnia realizowała większy wolumen sprzedaży niż przyjmowała zamówienia. Innymi słowy, sklep konsekwentnie przyjmował zamówienia przez cały miesiąc, ale wolał wysyłać hurtowo, zmniejszając koszty wysyłki i zwiększając wydajność, optymalizując w ten sposób strategię łańcucha dostaw.

Wniosek

W tej wskazówce omówiliśmy niektóre podstawy baz danych, w tym różne typy relacji z bazami danych. Następnie rozszerzyliśmy tę koncepcję na możliwość wielu relacji między tymi samymi tabelami. Praktycznie zademonstrowaliśmy to, budując schemat w MySQL i manipulując nim w usłudze Microsoft Power BI.

Następne kroki

• Sprawdź wszystkieWskazówki dotyczące usługi Power BI w witrynie MSSQLTips.com

O autorze

Artykuł Ostatnia aktualizacja: 2023-05-19