Modelowanie danych przy użyciu Microsoft Power BI - Markus Ehrenmueller-Jensen

Opinie o książce Modelowanie danych z użyciem Microsoft Power BI

Dzięki tej rewelacyjnej książce dowiesz się, dlaczego należy "tworzyć dla wszystkiego schemat gwiazdy". Wyjaśnia ona szczegółowo, dlaczego modelowanie danych jest istotne i podaje liczne przykłady. Lektura tej książki sprawi, że Twoje życie jako dewelopera Power BI będzie łatwiejsze.

- Koen Verbeeck, starszy architekt analizy biznesowej, miłośnik schematu gwiazdy, Microsoft Data Platform MVP

Książka Markusa Modelowanie danych przy użyciu Microsoft Power BI dostarcza bardzo dobre wprowadzenie do zasad modelowania danych dla efektywnego modelu danych w Power BI, a także dla modelu danych w programie Excel, Power Pivot.

Książka napisana w jasny sposób, z użyciem przejrzystego języka, który będzie zrozumiały zarówno dla nowicjuszy, jak i dla ekspertów. Jest ona bardzo przystępna i niezbędna dla wszystkich tych, którzy chcieliby dowiedzieć się czegoś więcej o modelowaniu danych.

Szczególnie podoba mi się dokonany przez Markusa podział tej książki na pewnego rodzaju macierz, w której każda z pięciu głównych części jest podzielona na cztery rozdziały dotyczące tych samych czterech podtematów: poznawanie modelu danych, budowanie modelu danych, rzeczywiste przykłady i optymalizacja wydajności. Ich złożoność stopniowo wzrasta, dzięki czemu zyskujemy coraz lepszy wgląd w poszczególne aspekty modelowania danych.

- Jorgen Koch, Innovate, entuzjasta Microsoft Power BI i Office,autor i certyfikowany trener Microsoft

Tworzenie wymyślnego raportu i majstrowanie przy kodzie języka DAX lub M w czasach sztucznej inteligencji nie jest trudne. Trudne jest za to tworzenie wysoce wydajnego modelu, który będzie działał. Markus jest "czarodziejem od modeli" i naprawił więcej mojej pracy, niż chciałbym przyznać. Ten praktyczny przewodnik pomoże Ci sprostać stojącym przed Tobą wyzwaniom związanym z budową modelu danych.

Choć platforma Power BI gwałtownie ewoluowała, wyzwanie związane z budową solidnego i wydajnego modelu nie zmieniło się. Markus widział mnóstwo środowisk i konfiguracji. Jestem świadkiem jego czarów. Pomógł mi naprawić popsute modele zwyczajnie rzucając na nie okiem. W swojej nowej książce dzieli się z nami wszystkimi swoją wiedzą, a za sprawą swojego praktycznego podejścia pomoże Ci samemu stać się czarodziejem od modeli danych.

- Joel Ruh, kierownik ds. transformacji cyfrowej w SkyFrame

Modelowanie danych przy użyciu Microsoft Power BI to obowiązkowa lektura dla każdego, kto chciałby opanować to potężne narzędzie. Markus Ehrenmueller-Jensen stworzył łatwy w czytaniu i zabawny przewodnik, który pomoże Ci odblokować pełny potencjał Power BI.

- Carola Seyr, kierownik zespołu ds. analizy biznesowej w Porsche Holding Salzburg

Modelowanie danych przy użyciu Microsoft Power BI

? 2025 APN PROMISE SA

Authorized translation of English edition ofData Modeling with Microsoft Power BIby Markus Ehrenmueller-JensenISBN 978-1-098-14855-3Copyright ? 2024 Savory Data GmbH. All rights reserved.

This translation is published and sold by permission of O'Reilly Media, Inc., which owns or controls of all rights to publish and sell the same.

APN PROMISE SA, ul. Domaniewska 44a, 02-672 Warszawatel. +48 22 35 51 600e-mail: [email protected]

Wszystkie prawa zastrzeżone. Żadna część niniejszej książki nie może być powielana ani rozpowszechniana w jakiejkolwiek formie i w jakikolwiek sposób (elektroniczny, mechaniczny), włącznie z fotokopiowaniem, nagrywaniem na taśmy lub przy użyciu innych systemów bez pisemnej zgody wydawcy.

Logo O'Reilly jest zarejestrowanym znakiem towarowym O'Reilly Media, Inc. Ilustracja z okładki i powiązane elementy są znakami towarowymi O'Reilly Media, Inc.

Wszystkie inne nazwy handlowe i towarowe występujące w niniejszej publikacji mogą być znakami towarowymi zastrzeżonymi lub nazwami zastrzeżonymi odpowiednich firm odnośnych właścicieli.

Przykłady firm, produktów, osób i wydarzeń opisane w niniejszej książce są fikcyjne i nie odnoszą się do żadnych konkretnych firm, produktów, osób i wydarzeń. Ewentualne podobieństwo do jakiejkolwiek rzeczywistej firmy, organizacji, produktu, nazwy domeny, adresu poczty elektronicznej, logo, osoby, miejsca lub zdarzenia jest przypadkowe i niezamierzone.

APN PROMISE SA dołożyła wszelkich starań, aby zapewnić najwyższą jakość tej publikacji. Jednakże nikomu nie udziela się rękojmi ani gwarancji. APN PROMISE SA nie jest w żadnym wypadku odpowiedzialna za jakiekolwiek szkody będące następstwem korzystania z informacji zawartych w niniejszej publikacji, nawet jeśli APN PROMISE została powiadomiona o możliwości wystąpienia szkód.

ISBN: 978-83-7541-568-1 (druk), 978-83-7541-569-8 (ebook)

Projekt okładki: Karen MontgomeryIlustracje: Rebecca DemarestIlustracja na okładce: Jose Marzan

Przekład: Krzysztof KapustkaKorekta: Ewa SwędrowskaSkład i łamanie: MAWart Marek Włodarz

Książkę chcę zadedykować najlepszej rzeczy,jaka kiedykolwiek przytrafiła mi się w życiu:moim ukochanym dzieciom, Clarze (Alex) i Victorowi.

Spis treści

Przedmowa

Wstęp

Część 1

Podstawy modelowania danych

Rozdział 1

Czym jest model danych?

Model danych

Podstawowe komponenty

Encja

Tabele

Relacje

Klucze główne

Klucze zastępcze

Klucze obce

Kardynalność

Łączenie tabel

Operatory zbiorowe

Złączenia

Problemy ze ścieżkami złączeń

Diagramy związków encji

Opcje modelowania danych

Rodzaje tabel

Jedna tabela do przechowywania wszystkiego

Postaci normalne

Modelowanie wymiarowe

Poziom szczegółowości

Wyodrębnianie, przekształcanie i ładowanie

Ralph Kimball i Bill Inmon

Data Vault i inne antywzorce

Kluczowe wnioski

Rozdział 2

Budowanie modelu danych

Normalizowanie

Denormalizowanie

Obliczenia

Flagi i wskaźniki

Data i godzina

Wymiary wielokrotnego stosowania

Wymiary wolnozmienne

Typ 0: Zachowaj oryginał

Typ 1: Nadpisz

Typ 2: Dodaj nowy wiersz

Typ 3: Dodaj nowe atrybuty

Typ 4: Dodaj miniwymiary

Typy 5, 6 i 7

Hierarchie

Kluczowe wnioski

Rozdział 3

Rzeczywiste przykłady

Binning

Dodawanie kolumny do tabeli faktów

Tworzenie tabeli wyszukiwania

Opisywanie zakresów przedziałów

Budżet

Identyfikowanie poziomu szczegółowości

Obsługiwanie tabel faktów o różnej liczności

Model wielojęzyczny

Tabele par klucz-wartość

Połączenie samoobsługi i analizy biznesowej przedsiębiorstwa

Kluczowe wnioski

Rozdział 4

Optymalizowanie wydajności

Kluczowe wnioski

Część II

Modelowanie danych w Power BI

Rozdział 5

Poznawanie modelu danych Power BI

Model danych

Podstawowe pojęcia

Tabele i kolumny

Relacje

Klucze główne

Klucze zastępcze

Klucze obce

Kardynalność

Łączenie tabel

Operatory zbiorowe

Złączenia

Problemy ze ścieżkami złączeń

Diagramy relacji encji

Opcje modelowania danych

Typy tabel

Jedna tabela do przechowywania wszystkiego

Postaci normalne

Modelowanie wymiarowe

Poziom szczegółowości

Wyodrębnianie, przekształcanie, ładowanie

Kluczowe wnioski

Rozdział 6

Budowanie modelu danych w Power BI

Normalizowanie i denormalizowanie

Obliczenia

Data i godzina

Wyłączanie automatycznej daty/godziny

Oznaczanie tabeli dat

Wymiary wielokrotnego stosowania

Wymiary wolnozmienne

Hierarchie

Kluczowe wnioski

Rozdział 7

Rzeczywiste przykłady z użyciem Power BI

Binning

Tabela wyszukiwania

Tabela zakresów

Budżet

Model wielojęzyczny

Tabela wymiarów dla dostępnych języków

Elementy wizualne

Zawartość tekstowa

Zawartość numeryczna

Metadane modelu danych

Interfejs użytkownika w programie Power BI Desktop (wersja tradycyjna)

Interfejs użytkownika w programie Power BI Desktop (wersja Windows Store)

Interfejs użytkownika w usłudze Power BI

Interfejs użytkownika w usłudze Power BI Report Server

Tabele par klucz-wartość

Łączenie samoobsługi i analizy biznesowej przedsiębiorstwa

Kluczowe wnioski

Rozdział 8

Optymalizacja wydajności w modelu danych Power BI

Tryb przechowywania

Partycjonowanie

Wstępne agregowanie

Modele złożone

Tryb Dual

Tabele hybrydowe

Kluczowe wnioski

Część III

Modelowanie danych dla Power BI z pomocą języka DAX

Rozdział 9

Poznawanie modelu danych z punktu widzenia języka DAX

Model danych

Podstawowe komponenty

Tabele

Relacje

Klucze główne

Łączenie zapytań

Operatory zbiorowe

Złączenia

Wyodrębnianie, przekształcanie, ładowanie

Kluczowe wnioski

Rozdział 10

Budowanie modelu danych za pomocą języka DAX

Normalizowanie

Denormalizowanie

Obliczenia

Proste agregacje dla obliczeń addytywnych

Obliczenia pół-addytywne

Ponowne tworzenie obliczeń w formie miary DAX

Obliczenia analizy czasowej

Flagi i wskaźniki

Funkcja IF

Funkcja SWITCH

Funkcja SWITCH TRUE

Tabela wyszukiwania

Traktowanie wartości pustych

Data i godzina

Wymiary wielokrotnego stosowania

Wymiary wolnozmienne

Hierarchie

Kluczowe wnioski

Rozdział 11

Rzeczywiste przykłady z użyciem języka DAX

Binning

Tabela wyszukiwania

Tabela zakresów

Budżet

Model wielojęzyczny

Tabele par klucz-wartość

Łączenie samoobsługi i analizy biznesowej przedsiębiorstwa

Kluczowe wnioski

Rozdział 12

Optymalizowanie wydajności za pomocą języka DAX

Tryb przechowywania

Wstępne agregowanie

Miary świadome agregacji

Kluczowe wnioski

Część IV

Modelowanie danych dla Power BI za pomocą Power Query

Rozdział 13

Poznawanie modelu danych z punktu widzenia Power Query

Model danych

Podstawowe komponenty

Tabele i zapytania

Relacje

Klucze główne

Klucze zastępcze

Łączenie zapytań

Operatory zbiorowe

Złączenia

Zależności zapytań

Rodzaje zapytań

Wyodrębnianie, przekształcanie, ładowanie

Kluczowe wnioski

Rozdział 14

Budowanie modelu danych za pomocą Power Query i M

Normalizowanie

Jakość kolumn

Rozkład kolumn

Profil kolumny

Identyfikowanie kolumn do znormalizowania

Tworzenie zapytania dla każdego wymiaru

Tworzenie jednego wspólnego zapytania wymiarów

Denormalizowanie

Obliczenia

Flagi i wskaźniki

Data i godzina

Wymiary wielokrotnego stosowania

Wymiary wolnozmienne

Hierarchie

Kluczowe wnioski

Rozdział 15

Rzeczywiste przykłady z użyciem Power Query i M

Binning

Ręczne tworzenie tabeli przedziałów

Tworzenie tabeli przedziałów na podstawie faktów

Tworzenie tabeli przedziałów w języku M

Tworzenie tabeli zakresów przedziałów w języku M

Budżet

Model wielojęzyczny

Tabele par klucz-wartość

Korzystanie z GUI

Korzystanie z kodu M

Pisanie funkcji w języku M

Łączenie samoobsługi i analizy biznesowej przedsiębiorstwa

Kluczowe wnioski

Rozdział 16

Optymalizacja wydajności modelu danych za pomocą Power Query

Tryb przechowywania

Partycjonowanie

Wstępne agregowanie

Kluczowe wnioski

Część V

Modelowanie danych dla Power BI za pomocą SQL

Rozdział 17

Poznawanie relacyjnego modelu danych

Model danych

Podstawowe komponenty

Tabele

Relacje

Klucze główne

Klucze zastępcze

Klucze obce

Łączenie zapytań

Operatory zbiorowe

Złączenia

Problemy ze ścieżkami złączeń

Diagramy relacji encji

Wyodrębnianie, przekształcanie, ładowanie

Kluczowe wnioski

Rozdział 18

Budowanie modelu danych przy użyciu języka SQL

Normalizowanie

Utrwalanie w postaci tabeli

Tworzenie widoku

Tworzenie funkcji

Tworzenie procedury

Tworzenie wymiaru filtru

Denormalizowanie

Obliczenia

Flagi i wskaźniki

Data i godzina

Wymiary wielokrotnego stosowania

Wymiary wolnozmienne

Typ 0: Zachowaj oryginał

Typ 1: Nadpisz

Typ 2: Dodawanie nowego wiersza

Hierarchie

Kluczowe wnioski

Rozdział 19

Rzeczywiste przykłady z użyciem SQL

Binning

Tworzenie tabeli wyszukiwania na podstawie faktów

Generowanie tabeli wyszukiwania

Tabela zakresów

Budżet

Model wielojęzyczny

Tabela par klucz-wartość

Łączenie samoobsługi i analizy biznesowej przedsiębiorstwa

Kluczowe wnioski

Rozdział 20

Optymalizacja wydajności modelu danych za pomocą SQL

Tryby przechowywania

Tabela

Indeks

Kompresja

Widok

Funkcja

Procedura składowana

Partycjonowanie

Wstępne agregowanie

Kluczowe wnioski

Epilog

Indeks

O autorze

Przedmowa

W branży związanej z danymi żaden temat nie jest przedmiotem większych dyskusji niż modelowanie danych. Jest on inspiracją dla memów, koszulek i niekończących się debat na konferencjach, a jego koniec przewiduje się od lat.

A jednak jesteśmy tu z nową książką o modelowaniu danych. I jest ona bardzo potrzebna. Modelowanie danych to podstawowa umiejętność, która ma wiele zastosowań. Ułatwia rozwiązywanie trudnych problemów, zapewnia łatwiejszą pracę z danymi i upraszcza pisanie wyrażeń w języku Data Analysis Expression (DAX). Poprawia wydajność i ostatecznie obniża koszty. Musimy być jednak gotowi wykonać stosowną pracę. O modelowaniu danych należy zacząć myśleć już na wczesnym etapie, niezależnie od tego, czy projektujemy hurtownię danych, magazyn danych lakehouse czy model semantyczny w Power BI.

Model danych jest kamieniem węgielnym naszego projektu. Nauczyłem się tego na przestrzeni lat, pracując z klientami nad wszystkimi odmianami usług analitycznych (od Power Pivot aż po SSAS i Power BI). Dzięki odpowiedniemu modelowaniu danych nie będziemy musieli uciekać się do tak wielu gimnastyk w języku DAX. Dobry model danych upraszcza nasze obliczenia.

Znam Markusa od wielu lat i zawsze z przyjemnością słucham jego wystąpień na konferencjach. Trudne tematy wyjaśnia on w prosty sposób, a ta książka nie jest od tego wyjątkiem.

W książce Modelowanie danych przy użyciu Microsoft Power BI Markus bada poszczególne aspekty i długą historię modelowania danych (kto nie ma na swojej półce książki Kimballa o hurtowniach danych?): w jaki sposób musimy myśleć o danych i jak możemy tłumaczyć wymagania na encje i atrybuty. Markus wykonuje świetną robotę, stosując te teoretyczne praktyki do prawdziwego życia.

W jaki sposób te praktyki modelowania danych pomagają nam w codziennych wyzwaniach związanych z Power BI i SQL? Markus wyjaśnia podstawy modelowania danych w Power BI, przyglądając się tabelom, relacjom i analizując poziom szczegółowości danych. Następnie pokazuje, jak przetłumaczyć typowe wymagania - takie jak wymiary wielokrotnego stosowania, wymiary wolnozmienne, binning i tłumaczenia - na język SQL, DAX lub M, bądź też w samym modelu, aby można ich było użyć na dowolnym etapie naszego projektu.

Lekcje zawarte w tej książce są bardzo cenne i pomagają nam uprościć naszą codzienną pracę na stanowisku inżyniera danych, a wszystko to zaczyna się od modelu.

- Kasper de Jonge, główny menedżer programu w Microsoft Fabric

Wstęp

Witamy w tej podróży po koncepcjach modelowania danych i praktycznych przykładach dla Power BI, wliczając w to język DAX, Power Query i T-SQL. Ta książka pomoże Ci w pełni zrozumieć kroki niezbędne do ułatwienia procesu tworzenia raportów za pomocą narzędzi Power BI Desktop i Power BI Report Builder, a także tworzenia miar w języku DAX.

Power BI obsługuje szeroką gamę źródeł danych (obejmującą bazy danych od różnych dostawców, takich jak Microsoft, Oracle czy Teradata, pliki płaskie, takie jak CSV, tekstowe lub pliki programu Excel, usługi internetowe, takie jak łącze https do strony internetowej itd.). Dane można załadować do Power BI wyłącznie poprzez Power Query. Najlepszą praktyką jest dodawanie obliczeń jako (jawnych) miar w języku DAX (zamiast kolumn obliczanych w DAX lub jako kolumn w Power Query lub źródle danych). Wyjątkiem powinno być tworzenie tabel obliczanych w DAX. W zależności od naszych umiejętności i preferencji, przekształcenia mające na celu ukształtowanie modelu danych będziemy implementować albo w Power Query (w interfejsie użytkownika lub pisząc kod w języku M), albo w źródle danych. Na przykład, w przypadku relacyjnej hurtowni danych zaimplementowanej przy użyciu silników relacyjnych baz danych firmy Microsoft możemy użyć w takiej hurtowni danych języka T-SQL, jak zostało to przedstawione na rysunku 0-1.

Pierwsza część tej książki, napisana w sposób agnostyczny, przedstawia niezbędne pojęcia w sposób ogólny: możemy je zastosować do dowolnego systemu analitycznego. Druga część książki objaśnia właściwości modelu danych w Power BI. Pozostałe rozdziały dotyczą zagadnień związanych z DAX, Power Query i SQL.

Ta książka została zaprojektowana dla Ciebie, czytelnika w taki sposób, aby zapewnić indywidualne doświadczenie oparte na Twojej własnej wiedzy. Możesz nie znać języka DAX, Power Query oraz SQL, ale być może zaznajomiłeś się z jednym lub dwoma z tych narzędzi. Możesz selektywnie wybierać i uzupełniać luki w swojej wiedzy. Być może potrzebujesz odświeżyć swoją wiedzę na temat kompozycji modelu danych. Pomoże Ci w tym część I tej książki. Być może nie radzisz sobie z plikami programu Excel, z których musisz tworzyć raporty? Tutaj dobrym punktem wyjścia będzie część poświęcona Power Query. A może Twoim zadaniem jest zbudowanie hurtowni danych, z którą inne osoby będą łączyć się za pomocą Power BI Desktop? Wtedy rozwiązania dla typowych problemów znajdziesz w części dotyczącej SQL.

Rysunek 0-1 Architektura kształtowania danych Power BI

Modelowanie danych jest zdecydowanie najbardziej niedocenianym zadaniem podczas pracy z programem Power BI Desktop. Jest kluczowym elementem wykonywanych przez nas kroków, od surowych danych po analizę i analitykę biznesową. Decyzje podjęte podczas modelowania danych będą mieć wpływ na to, jak wiele szczegółów będą w stanie ukazywać raporty, jak przyjazna dla użytkownika będzie baza danych lub model semantyczny w procesie tworzenia raportów i przeprowadzania analiz, a także jak łatwe będzie dodawanie nowych danych i implementowanie obliczeń na istniejących danych. Błędne decyzje podjęte na początku są później kosztowne w naprawie, ponieważ zmiany wprowadzane do modelu danych będą psuły istniejące raporty. Mówię to z własnego doświadczenia: nauczyłem się tego na własnych błędach, a w mojej pracy jako trener i konsultant niemal każdego dnia widzę, że inni ludzie również zmagają się z tymi konsekwencjami. Ta książka pokaże Ci, jak od samego początku modelować dane w prawidłowy sposób.

Dowiesz się, że mniej istotne jest to, jak skomplikowane są kroki wykonywane na "zapleczu" (DAX, Power Query lub T-SQL), o ile tylko wynikiem jest model danych, który jest łatwiejszy do zrozumienia i użycia przez użytkowników tworzących raporty i przeprowadzającego analizy w oparciu o ten model danych: autorów raportów, analityków biznesowych, analityków danych itd. Te kroki mogą być tak proste, jak zmiana nazw technicznych (np. CSTNM4711) na nazwy przyjazne dla użytkownika (np. Customer Name) lub tak skomplikowane, jak łączenie lub dzielenie tabel do postaci zupełnie nowej struktury. Dowiesz się, jak dodawać obliczenia do modelu danych i wzbogacać zwykłe dane o metadane (hierarchie, tłumaczenia itd.). Ta książka jest wypełniona praktycznymi przykładami wzorowanymi na problemach, przed jakimi stawałem pracując w tej branży przez ponad 25 lat. Należy pamiętać, że ostatecznym celem jest tutaj odciążenie twórcy raportu.

Typową analogią dla przekształcania danych jest restauracja. Będąc klientem restauracji oczekujemy, że serwowane nam dania będą atrakcyjnie ułożone na talerzach, abyśmy mogli cieszyć się posiłkiem jedynie przy pomocy powszechnie stosowanych narzędzi (łyżka, nóż, widelec, pałeczki, a nawet nasze palce). Aby było to możliwe, restauracja nie tylko rezerwuje znaczną przestrzeń i zasoby w kuchni, ale także dysponuje drogimi narzędziami (piec konwekcyjny, grill, cyrkulator do gotowania sous-vide, blender itd.) i zatrudnia formalnie przeszkolonych i wykwalifikowanych ludzi (kucharzy) do kontrolowania tych urządzeń i przetwarzania surowych składników na potrawy. O osobach wykorzystujących modele danych do tworzenia raportów i przeprowadzania analiz możemy myśleć jak o gościach restauracji: chcieliby oni, aby wszystkie informacje były prezentowane w łatwostrawnej formie (gra słów zamierzona), którą mogą spożytkować za pomocą powszechnych narzędzi (Power BI, Excela itd.). Wcielmy się w rolę kucharza, który wykorzystuje całe swoje doświadczenie do stworzenia modelu danych wywołującego apetyt na jego spożycie.

Czy jesteś kucharzem danych? Czytaj dalej!

Dla kogo jest ta książka?

Czy uzyskujesz dostęp do danych w Power BI Desktop i wchodzisz z nimi w interakcje za pomocą wizualizacji? Czy zebrałeś najważniejsze dane w pojedynczym pliku Power BI Desktop, aby inni mogli tworzyć na ich podstawie raporty? Czy zarządzasz hurtownią danych i chcesz mieć pewność, że model danych jest zoptymalizowany pod kątem użycia w Power BI Desktop? Jeśli na którekolwiek z tych pytań odpowiedziałeś "tak", ta książka jest właśnie dla Ciebie.

Główną grupę docelową stanowią entuzjastyczni twórcy raportów Power BI, którzy chcą zastosować w modelu danych najlepsze praktyki na potrzeby wydajnych raportów i łatwych obliczeń DAX. Drugorzędną grupą docelową są profesjonaliści IT, którzy chcą wspierać twórców raportów za pomocą źródeł danych typu "podłącz i używaj" (model danych utworzony w Power BI Desktop). Aby móc podążać za przykładami kodu zawartymi w tej książce, powinieneś umieć tworzyć raporty w Power BI Desktop i posiadać podstawową wiedzę co najmniej w zakresie DAX, Power Query/M lub SQL.

Czytając tę książkę, zapoznasz się nie tylko z opcjami modelowania danych w Power BI, ale także z opcjami modelowania w innych narzędziach, dzięki czemu będziesz mógł stworzyć model danych, który będzie optymalny dla Power BI. Omawiamy to w części V.

Dlaczego warto przeczytać (i napisać!) całą książkę o modelowaniu danych? Następny podrozdział dostarcza odpowiedź na to pytanie.

Czym jest modelowanie danych?

Wyzwania związane z przechowywaniem danych w różnych formatach logicznych są równie stare jak same dane. Przed wynalezieniem elektronicznych komputerów dane umieszczane były w różnych fizycznych plikach i porządkowane w fizycznych folderach, wypełniając w ten sposób półki dużych szafek, a nawet całe pokoje czy piwnice. Opcjonalne indeksy (ułożone alfabetycznie małe karteczki z ważnymi pojęciami i wskazaniami tego, na której półce oraz w którym folderze i pliku znajdują się te informacje) umożliwiały skanowanie danych nie tylko według ich kolejności fizycznej, ale również według różnych znaczników. Ta terminologia obowiązuje do dziś, ale dane są obecnie przechowywane w plikach i folderach na dyskach twardych, wraz z dodatkowymi indeksami przyspieszającymi dostęp do danych.

Na przestrzeni lat wymyślane i omawiane były różne podejścia do łatwego magazynowania (np. unikanie nadmiarowości i standaryzacja fizycznego przechowywania danych) i łatwego odczytywania (np. indeksowanie i ponowne wprowadzanie pewnej nadmiarowości w celu przyspieszenia dostępu do danych). Koncepcja relacyjnych baz danych (np. SQL Server i Azure SQL Database) sięga roku 1970. Modelowanie wymiarowe jest jeszcze starsze, a mimo to nadal jest bardzo przydatne.

Chcąc opanować Power BI, należy opanować modelowanie danych, ponieważ Power BI jest narzędziem analizy opartej na modelu (w przeciwieństwie do niektórych innych narzędzi dostępnych na rynku). Następny podrozdział wyjaśnia, z jakich części składa się Power BI i w którym miejscu definiujemy model danych.

Co to jest Power BI?

Power BI nie jest pojedynczym narzędziem, lecz całym zestawem narzędzi, który stał się powszechnie dostępny w 2015 r. Dla osób, które dopiero zaczynają korzystać z Power BI, zamieszczam krótki przegląd różnych narzędzi:

Power BI Desktop

Power BI Desktop to pełny klient, za pomocą którego możemy zrealizować wiele zadań (patrz rysunek 0-2). Łączymy się ze źródłami danych, oczyszczamy i przekształcamy dane (za pomocą Power Query), opracowujemy model danych (w widoku Model) i tworzymy raporty (w widoku Report). Jest to narzędzie, z którym spędzisz dużo czasu w przypadku odtwarzania przykładów z tej książki. Wszystkie przykłady i zrzuty ekranu w tej książce dotyczące modelowania danych Power BI, języka DAX i Power Query powstały z użyciem Power BI Desktop. Pliki utworzone za pomocą Power BI Desktop mają rozszerzenie .pbix lub .pbip. Power BI Desktop jest programem bezpłatnym - nie jest wymagana żadna licencja, a proces logowania można pominąć.

Rysunek 0-2 Ekran początkowy w Power BI Desktop

Power BI Service

Usługa o nazwie Power BI Service to miejsce, w którym możemy hostować pliki utworzone za pomocą Power BI Desktop, tak aby inne osoby mogły skorzystać z raportu lub utworzyć nowy raport na podstawie naszego modelu danych. Usługa Power BI oferuje dodatkowe funkcje (takie jak Metric, Dashboard, Power BI App, Analyze in Excel, Export to Excel itd.), które nie są dostępne w Power BI Desktop. Usługa Power BI jest hostowana w centrach danych chmury firmy Microsoft i jest dostępna za pośrednictwem dowolnie wybranej przeglądarki internetowej (zobacz rysunek 0-3). W usłudze Power BI możemy również bezpośrednio edytować raporty, a w firmie Microsoft ciężko pracują nad tym, aby umożliwić w niej również edycję modeli danych. W chwili pisania tego tekstu brakuje w niej ważnych funkcji (takich jak kontrola wersji i wspólne edytowanie), dlatego obecnie zalecam korzystanie z programu Power BI Desktop. Usługa Power BI jest dostarczana z różnymi licencjami.

Rysunek 0-3 Powitanie w usłudze Power BI

Power BI Report Server

Power BI Report Server (zobacz rysunek 0-4) jest alternatywą dla usługi Power BI service, którą możemy zainstalować w naszym środowisku lokalnym. Power BI Report Server oferuje ograniczony zestaw funkcji, a nowe wersje są wydawane (tylko) trzy razy do roku. Jeśli w usłudze Power BI Report Server zamierzamy opublikować raport przygotowany w Power BI Desktop, musimy użyć pasującej wersji programu Power BI Desktop (o nazwie Power BI Desktop for Report Server, która wyświetla miesiąc i rok w tytule aplikacji), ponieważ wydawana miesięcznie wersja Power BI Desktop może zawierać artefakty, które nie są (jeszcze) zgodne z używaną przez nas wersją Power BI Report Server.

Rysunek 0-4 Powitanie w Power BI Report Server

Power BI Report Builder

Jeżeli potrzebujemy tworzyć raporty pixel-perfect z listami danych obejmującymi kilka stron lub musimy dokonywać eksportu w formatach plików niedostępnych dla raportów tworzonych za pomocą Power BI Desktop, wówczas Power BI Report Builder będzie odpowiednim narzędziem (zobacz rysunek 0-5). Power BI Report Builder jest bezpłatny.

Jeśli raporty podzielone na strony zamierzasz publikować w usłudze Power BI Report Server (a nie w usłudze Power BI service), zamiast Power BI Report Builder musisz użyć SQL Server Report Builder.

Tworzenie modeli danych nie jest możliwe w żadnej wersji narzędzia Report Builder.

Rysunek 0-5 Tworzenie raportów podzielonych na strony przy użyciu Power BI Report Builder

Model tabelaryczny usług Analysis Services

Analysis Services Tabular (dostępny jako Azure Analysis Services i jako SQL Server Analysis Services Tabular) można porównać do programu Power BI Desktop pozbawionego funkcji tworzenia raportów i zredukowanego do widoku tabeli, widoku modelu i narzędzia Power Query. Korzysta on z tego samego silnika przechowywania danych (VertiPaq) i możliwości modelowania co Power BI Desktop, a do usług Analysis Services można podłączyć dowolne narzędzie raportowania (w tym Power BI Desktop i Power BI Report Builder) na potrzeby tworzenia raportów i analiz. Taką bazę danych opracowujemy i wdrażamy za pomocą Visual Studio (patrz rysunek 0-6). Niektórzy moi klienci do hostowania danych używają Azure Analysis Services zamiast usługi Power BI service, ponieważ mogą wtedy bardziej szczegółowo skalować koszty przechowywania danych (w porównaniu z kosztami licencjonowania Power BI). Inni korzystają z usług SQL Server Analysis Services, ponieważ nie mogą lub nie chcą hostować swoich danych w chmurze.

Wizją firmy Microsoft jest uczynienie usługi Power BI service nadzbiorem dla modelu tabelarycznego Analysis Services. Wraz z ogłoszeniem platformy Microsoft Fabric na konferencji Build w maju 2023 r. firma Microsoft poczyniła w tym kierunku duży krok. Fabric również umożliwi skalowanie kosztów na bardziej szczegółowym poziomie w porównaniu z licencjonowaniem Power BI.

Rysunek 0-6 Definicja tabelarycznej bazy danych usług Analysis Services w Visual Studio

Porozmawiajmy o tym, dlaczego model danych budowany przez nas w Power BI Desktop jest tak istotny.

Co jest takiego specjalnego w modelu danych Power BI?

Platforma Power BI jest bardzo wszechstronna, jeśli chodzi o kształty modeli danych, których możemy używać (sposób rozprowadzania informacji między tabelami lub łączenie wszystkiego w jedną tabelę). Ale nie wpadnijmy tutaj w pułapkę: silnik przechowywania danych w Power BI (o nazwie VertiPaq) i język do definiowania formuł dla miar (DAX) są zoptymalizowane pod kątem określonego kształtu nazywanego schematem gwiazdy. Ta książka pomoże Ci zrozumieć, czym jest schemat gwiazdy, dlaczego tak ważne jest przekształcanie tabel z naszego źródła danych na ten kształt oraz, co najważniejsze, jak faktycznie zaimplementować te kroki.

Krótko mówiąc, schemat gwiazdy to termin używany do określenia modelu danych, pośrodku którego znajduje się tabela faktów (stanowiąca centrum tej gwiazdy) otoczona tabelami wymiarów (rysunek 0-7). Wiem, że potrzeba dużo wyobraźni, aby móc zobaczyć taką gwiazdę - ale mimo wszystko ta koncepcja jest ważna i przydatna przy budowaniu systemów analitycznych, zwłaszcza w przypadku o Power BI.

Rysunek 0-7 Schemat gwiazdy

W idealnym świecie tabele w źródle danych od początku miałyby już kształt schematu gwiazdy. Jeżeli tak wyglądają wszystkie Twoje źródła danych, to jesteś prawdziwym szczęściarzem - możesz przestać czytać tę książkę i poprosić o zwrot pieniędzy. Zwykli śmiertelnicy, którzy nie mają tyle szczęścia, mają kilka opcji, które omawiamy w tej książce. Możemy zbudować warstwę hurtowni danych (np. w formie relacyjnej bazy danych lub magazynu data lakehouse). Użyjemy do tego języka SQL. Zmiany kształtu tabel można również dokonać za pomocą Power Query w Power BI. Trzecią opcją jest DAX, który omawiamy w następnym podrozdziale.

Czym jest DAX?

Data Analysis Expressions (DAX) to język wyrażeń formuł używany do tworzenia kolumn obliczanych, miar, tabel obliczanych i zabezpieczeń na poziomie wierszy oraz do pisania zapytań. Opanowanie języka DAX jest istotne, ponieważ niektóre rodzaje obliczeń można wykonać tylko w DAX (a nie w źródle danych Power BI czy w Power Query).

Aby przekształcić model danych na schemat gwiazdy, należy przenieść informacje z jednej tabeli do drugiej za pomocą kolumn obliczanych lub utworzyć nowe tabele jako tabele obliczane. Dowiesz się także, jak używać języka DAX w przypadku przekroczenia limitów możliwości modelowania danych w Power BI.

Ogólnie rzecz biorąc, DAX nie jest moim pierwszym wyborem jako narzędzie do kształtowania danych, jeśli tylko mogę rozwiązać jakiś problem za pomocą Power Query. Gdy to tylko możliwe, przenoszę przekształcenia do Power Query lub do warstwy hurtowni danych (jeśli jest dostępna), ponieważ najlepszą praktyką jest dokonywanie przekształceń jak najbliżej źródła danych, aby zwiększyć możliwość ich ponownego wykorzystania. Przekształcenie w Power Query można przenieść do przepływu danych Power BI, aby można go było używać w różnych modelach danych. Z kolei przekształcenia w DAX są ściśle stosowane do modelu danych, w którym się znajdują. Innym powodem jest to, że w Power Query i SQL mogę ukształtować dane przed ich faktycznym załadowaniem do Power BI, podczas gdy w DAX mogę tylko nałożyć kolumny obliczane i tabele na model - każda nieoptymalna część nadal zajmuje zasoby w moim modelu danych.

Jeśli nie czujesz się gotowy na Power Query (lub język M), bądź też nie masz pod ręką żadnej hurtowni danych, modelowanie danych w języku DAX wciąż będzie znacznie lepsze od braku modelowania danych. Pamiętajmy też, że niektóre problemy można rozwiązać jedynie za pomocą miary DAX.

Czym jest Power Query?

Jak wskazuje nazwa, Power Query to narzędzie do tworzenia zapytań przesyłanych do naszych źródeł danych. W chwili pisania tego tekstu Power Query oferuje łączniki dla ponad 120 źródeł danych, od plików płaskich, takich jak CSV, XLS lub JSON, po relacyjne (SQL Server, Oracle, DB2, Teradata itd.) i analityczne bazy danych (Analysis Services). Za pośrednictwem graficznego interfejsu użytkownika (GUI) możemy czyścić i przekształcać oryginalne tabele: zmieniać nazwy tabel i kolumn, aby były bardziej przyjazne dla użytkownika, usuwać niepotrzebne kolumny oraz dodawać nowe tabele i kolumny na podstawie istniejących informacji w celu ukształtowania lepszego modelu danych.

Wszystkie kroki stosowane przez nas w GUI są dodawane jako linie kodu do skryptu Power Query (nazywanego w skrócie M), podobnie jak ma to miejsce w przypadku rejestratora makr w programie Excel. Z tej książki dowiesz się, jak korzystać z GUI i kiedy edytować takie wynikowe zapytanie. Ten skrypt jest wykonywany przy każdym odświeżeniu tabeli, dzięki czemu wszystkie kroki przekształcenia są również stosowane do nowych danych. Dlatego proszę ludzi o przekazywanie mi "surowych" plików CSV lub plików programu Excel (w przypadkach, gdy preferowanym źródłem danych są pliki). Nie ma potrzeby wkładać wysiłku w ręczne kształtowanie zawartości za każdym razem, gdy wysyłają mi nowe dane, skoro mogę raz zaimplementować przekształcenia i stosować je ponownie podczas odświeżania modelu danych.

Narzędzie Power Query (a tym samym skrypty w jego języku M) jest dostępne nie tylko w programie Power BI Desktop, ale też w formie przepływów danych Power BI i zadań typu mashup w Azure Data Factory. Możliwe jednak, że będziemy chcieli przenieść przekształcenia bliżej źródła danych (moim zdaniem powinniśmy). Właśnie dlatego ta książka zawiera tak dużo treści na temat Azure SQL DB i T-SQL.

Czym jest SQL?

SQL to skrót od Structured Query Language. W kontekście firmy Microsoft "SQL" może również odnosić się do jej oferty silników relacyjnych baz danych. Azure SQL DB to oferta Microsoft w postaci relacyjnych baz danych w chmurze, dostępna do użytku lokalnego pod nazwą Microsoft SQL Server Database Engine.

W tej książce baza danych Azure SQL DB jest używana jako przykład dla sposobu modelowania danych poza Power BI. Nadal uważam to za najlepszą praktykę: modelujmy dane w bazie danych i modelujmy dane tak wcześnie, jak to tylko możliwe, a inaczej mówiąc w warstwie hurtowni danych. To, w jaki sposób zaimplementujemy tę warstwę, jest mniej istotne: jako fizycznie hostowaną relacyjną bazę danych, jako magazyn data lakehouse lub w jakimkolwiek innym magazynie danych, na przykład gdzieś w Microsoft Fabric.

Przeniesienie przekształceń jak najdalej w stronę źródła danych ułatwia ich ponowne wykorzystanie osobom znajdującym się niżej w strumieniu danych. Jeśli będziemy czekać do ostatniej chwili (tj. do określenia narzędzia używanego do wyświetlania naszych danych, np. Power BI Desktop, Power BI Report Builder, programu Excel lub innego narzędzia, którego użytkownicy końcowi mogą używać do uzyskania dostępu do danych i wykonywania swoich analiz), nagromadzimy wtedy dług techniczny. Użytkownicy końcowi będą wówczas odpowiedzialni za czyszczenie tych danych i nadanie im użytecznego kształtu. Z powodu braku odpowiednich umiejętności mogą temu nie podołać, co może prowadzić do nadmiernie skomplikowanych raportów i potencjalnie błędnych wyników (porozmawiamy o tym problemie w rozdziale 5.). Co więcej, cała ta praca jest następnie wykonywana ponownie lub kopiowana do następnego pliku, co prowadzi do powstania zduplikowanych wersji tych nadmiernie skomplikowanych raportów. Wkrótce możemy skończyć w tzw. piekle Excela, gdzie nikt nie ma pełnego obrazu różnych wersji logiki zastosowanej do tych danych. Z drugiej strony warstwa hurtowni danych gwarantuje pojedynczą wersję prawdy.

Część V koncentruje się na rozwiązaniach zbudowanych za pomocą instrukcji SELECT i rozszerzenia proceduralnego SQL o nazwie T-SQL. Używam dialektu SQL, który jest dostępny w Azure SQL DB i SQL Server Database Engine. Istnieje pewna szansa, że proste instrukcje SELECT przedstawione w tej książce będą również działać w innych relacyjnych bazach danych, a nawet w bazach danych NO-SQL. Jest niemal pewne, że rozszerzenia proceduralne (pętle, funkcje, procedury itd.) bez odpowiednich zmian nie będą działać w innych systemach zarządzania bazą danych. Jeśli ktoś nie korzysta z baz danych firmy Microsoft opartych na SQL, będzie musiał znaleźć jakiś sposób na przeniesienie tego kodu do używanego przez siebie systemu docelowego.

T-SQL jako język jest dość stabilny pod względem rzadkości tworzenia nowych rozszerzeń lub tego, które jego części uznawane są za przestarzałe. W przypadku Power BI jest inaczej - tutaj nowe wersje są wydawane co miesiąc.

Nowe wydanie co kilka tygodni

Zespół Microsoft rozwijający platformę Power BI z jej narzędziami i usługami jest bardzo zajęty dostarczaniem co miesiąc nowych wersji programu Power BI Desktop i cotygodniowym wprowadzaniem zmian w usługach opartych na chmurze. Dla wszystkich stanowi to pewne wyzwanie: interfejs użytkownika się zmienia, ikony są przeprojektowywane, a przyciski przenoszone w inne miejsca. Stanowi to również wyzwanie dla każdego projektu książki: w momencie czytania tej książki niektóre zrzuty ekranu mogą być już nieaktualne. Dlatego tam, gdzie jest to wystarczające, zamieszczam jedynie fragmenty ekranu. W wielu miejscach odsyłam również do oficjalnej dokumentacji Microsoft, którą firma aktualizuje wspólnie ze społecznością. Możesz się nią posiłkować, jeśli Twój program Power BI Desktop wygląda inaczej.

Ogólne koncepcje dotyczące tworzenia optymalnego modelu danych dla Power BI nie zmieniły się zbytnio w przeszłości, dlatego istnieje nadzieja, że ta wiedza pozostanie nadal aktualna i pomoże Ci także w przyszłości. Czytaj dalej, aby dowiedzieć się, w jaki sposób dzielę całą tę niezbędną wiedzę i umiejętności na strawne porcje.

Jak czytać tę książkę?

Książka została podzielona na pięć części - pięć książek w cenie jednej:

Podstawy modelowania danych (część I)

Pierwsza część stanowi łagodne wprowadzenie do teorii i pojęć, a także wyjaśnia, dlaczego modelowanie danych ma znaczenie. Cała treść omawiana jest w sposób praktyczny, ale niezależny od narzędzi. Można ją traktować jako "streszczoną" wersję książek The Data Warehouse Toolkit autorstwa Ralpha Kimballa i Margy Ross'a (Wiley, 2013) oraz Star Schema: The Complete Reference autorstwa Christophera Adamsona (McGraw Hill, 2010).

Modelowanie danych w Power BI (część II)

W drugiej części omawiane są funkcje modelowania danych w Power BI Desktop. Pokazujemy tu, jak zastosować całą tę teorię (z pierwszej części) w konkretnych przykładach. Omawiamy elementy dostępne w menu i na wstążce oraz wyjaśniamy właściwości modelu danych.

Modelowanie danych dla Power BI za pomocą DAX (część III)

W tej części pokazujemy, w jaki sposób informacjom ze źródeł danych możemy nadać odpowiedni kształt (omawiany w części II) za pomocą języka DAX.

Zaletą używania DAX jest to, że tak czy inaczej trzeba się go nauczyć, jeśli chce się dobrze opanować Power BI (pisanie miar obliczanych bez użycia DAX możliwe jest tylko w przypadku bardzo prostych modeli danych). Inną zaletą jest to, że zmiany dokonane w formule zostaną obliczone natychmiast po naciśnięciu przycisku OK, bez konieczności uzyskiwania dostępu do źródła danych. Z kolei wadą używania DAX do zmiany kształtu modelu danych jest to, że wyniki kolumn obliczanych i tabel obliczanych będą zajmować miejsce na dysku po zapisaniu pliku, a także w pamięci po otwarciu tego pliku. Kolejną wadą jest to, że model danych będzie zawierał zarówno oryginalny kształt, jak i wersję modelu danych po zmianie kształtu (zajmując w ten sposób niepotrzebną ilość miejsca).

Głównym celem DAX jest definiowanie miar, a nie kształtowanie danych, jednak niektóre z przykładów nadal zapewniają dodatkowy wgląd w to, co można osiągnąć za pomocą języka DAX. Poznanie pełnych możliwości języka DAX pomoże Ci także w zastosowaniu złożonych miar DAX, gdy z jakiegoś powodu nie będziesz w stanie rozwiązać problemu w samym modelu.

Modelowanie danych dla Power BI za pomocą Power Query (część IV)

W tej części pokazujemy, w jaki sposób informacjom ze źródeł danych możemy nadać odpowiedni kształt przy pomocy interfejsu użytkownika Power Query i języka M. Zobaczymy, że możemy osiągnąć naprawdę sporo przy użyciu samego interfejsu użytkownika, zanim zajmiemy się obsługą kodu w języku M.

Niestety, język M znacząco różni się od języka DAX. Przede wszystkim w M stosujemy przekształcenia bazując na wyniku z poprzedniego kroku. W języku DAX nakładamy zawartość na już istniejącą zawartość modelu danych. W przeciwieństwie do DAX, język M rozróżnia wielkość liter w przypadku słów kluczowych i zawartości kolumn. M jest podobny do języka F#, natomiast DAX jest podobny do formuł programu Excel. Te dwa języki trudno jest ze sobą porównać. Jak zobaczymy w tej części, M jako język znacznie lepiej nadaje się do zadań kształtowania danych niż DAX.

Kiedy ładowane przeze mnie dane nie mają jeszcze pożądanego kształtu, moimi ulubionymi narzędziami są Power Query i M. Do modelu danych Power BI ładowany jest tylko końcowy wynik przekształceń dokonanych w Power Query, co pozwala uniknąć niepotrzebnych tabel i kolumn. Podczas pracy z Power Query potrzebny jest aktywny dostęp do źródła danych.

Modelowanie danych dla Power BI za pomocą SQL (część V)

W tej części pokazujemy, w jaki sposób informacjom ze źródeł danych możemy nadać odpowiedni kształt za pomocą języka SQL i T-SQL. Pomimo trendów polegających na gromadzeniu danych w jeziora, wciąż jestem głęboko przekonany, że organizacja przedsiębiorstwa potrzebuje centralnego miejsca, w którym ktoś będzie zajmował się danymi - miejsca, gdzie naturalne (i brudne) dane będą czyszczone i będą zyskiwały odpowiedni kształt. To, czy takie miejsce będzie fizyczną bazą danych, czy "tylko" widokami na jakiś magazyn danych, nie jest tak istotne. Ważne jest jednak, aby mieć taką warstwę (hurtownię danych), zamiast obciążać tym użytkownika końcowego i używane przez niego narzędzia.

Jeśli ktoś chciałby dowiedzieć się więcej o relacyjnych bazach danych lub ułatwić życie użytkownikom Power BI w swojej organizacji, ta część książki będzie dla niego przydatna. W Microsoft Fabric możemy uzyskać dostęp do naszych danych znajdujących się w jeziorze danych.

Każda część składa się z czterech rozdziałów, które obejmują następujące zagadnienia:

Poznawanie modelu danych

Te rozdziały dotyczą podstawowych pojęć i koncepcji. Jeśli ktoś dopiero zaczyna przygodę z modelowaniem danych, powinien przeczytać i zrozumieć zawartą w nich treść. Ktoś, kto ma już pewne doświadczenie w modelowaniu danych, może szybko sprawdzić te rozdziały, aby odświeżyć pamięć. Warto przyjrzeć się kluczowym wnioskom zamieszczonym na końcu każdego rozdziału.

Budowanie modelu danych

Te rozdziały dotyczą najważniejszych zagadnień modelowania danych. Omawiają one problemy i prezentują rozwiązania dla typowych problemów. Jestem przekonany, że podczas przekształcania swoich danych na przydatne informacje napotkasz przynajmniej niektóre z nich.

Rzeczywiste przykłady

W tych rozdziałach omawiane są bardziej zaawansowane wyzwania. Omawiane tutaj problemy mogą być nieistotne w przypadku prostych modeli danych. Podczas pierwszego czytania tej książki można pominąć te rozdziały i powrócić do nich po napotkaniu jednego z tych problemów. W tych rozdziałach szczegółowo omawiane są zagadnienia modelowania danych, języka DAX, Power Query i SQL, a także rzadko stosowane funkcje, które mogą być potrzebne do rozwiązania określonego problemu. Są to rozdziały przeznaczone dla zaawansowanych kucharzy danych.

Optymalizacja wydajności

Każdą część zamyka rozdział poświęcony optymalizacji wydajności, co najczęściej jest ostatnim krokiem w całej podróży związanej z modelowaniem danych. Ktoś, kto od początku nauczy się stosować najlepsze praktyki omówione w tej książce, jeszcze przez długi czas nie będzie musiał się martwić wydajnością swoich raportów. Jednak w przypadku naprawdę dużych wdrożeń (mam tu na myśli miliardy wierszy danych) nawet przestrzeganie tych najlepszych praktyk będzie niewystarczające. Wtedy trzeba będzie zagłębić się w techniczną warstwę modelowania danych i nauczyć się optymalizować dostępne tryby przechowywania (Import, DirectQuery, połączenie na żywo i Direct Lake) pod kątem własnych wymagań.

Cała książka, a co za tym idzie także rozdziały dotyczące optymalizacji wydajności, skupia się na modelowaniu danych, a nie na tym, jak pisać wydajny kod w DAX, Power Query czy SQL.

Wszystkie te części i tematy zostały zebrane razem w 20 rozdziałach (patrz rysunek 0-8). Ich stopień złożoności i trudności ulega stopniowemu zwiększeniu, co pozwala przeczytać tę książkę od deski do deski. Jeśli ktoś do rozwiązywania problemów zamierza przykładowo używać Power Query, może też pominąć język DAX lub zignorować język SQL. Wyobraźmy sobie, że przechodzimy do rozdziału 10. i odkrywamy, że nie wiemy do końca, dlaczego powinniśmy utworzyć tabelę dat dla naszego modelu danych Power BI. Możemy przejść do rozdziału 6., aby się tego dowiedzieć.

Możemy także przejrzeć ogólną koncepcję tabeli dat w rozdziale 2. (i dowiedzieć się, dlaczego jest ona przydatna - tak w ogóle, jak i w systemach analitycznych zbudowanych przy użyciu innych narzędzi). A może zdecydujemy się nie budować tabeli dat w języku DAX i poszukamy innych rozwiązań. Te same implementacje wykonane w Power Query i SQL znajdziemy odpowiednio w rozdziałach 14. i 18. Rysunek 0-9 ilustruje tę podróż.

Rysunek 0-8 Przegląd rozdziałów

Rysunek 0-9 Przykład nawigacji

Gotów? Upewnij się, że masz zainstalowane niezbędne oprogramowanie.

Instalowanie niezbędnego oprogramowania

Do otwarcia dostarczonych plików demonstracyjnych i wykonania opisanych w tej książce ćwiczeń wymagane jest zainstalowanie następującego oprogramowania:

Power BI Desktop

W celu otwarcia plików .pbix należy albo zainstalować wersję Windows Store ze sklepu Power BI Desktop Store (https://aka.ms/pbidesktopstore) lub pobrać pliki instalatora programu Power BI Desktop (https://aka.ms/pbiSingleInstaller). Należy pamiętać o regularnym pobieraniu i instalowaniu pliku .msi lub zainstalować Power BI Desktop w wersji ze sklepu Microsoft Store, która będzie się aktualizować automatycznie.

Klient SQL

Na potrzeby ćwiczeń w języku SQL użyłem SQL Server Management Studio (SSMS), które można pobrać i zainstalować ze strony "Download SQL Server Management Studio (SSMS)" (https://oreil.ly/vKHDp). Alternatywnie można użyć narzędzia Azure Data Studio, dostępnego do pobrania na stronie "Download and install Azure Data studio" (https://oreil.ly/olspG). Można też skorzystać z innego preferowanego klienta SQL.

SQL Server lub Azure SQL DB

Do wykonania ćwiczeń w SQL potrzebny będzie dostęp do jednej z relacyjnych baz danych udostępnianych przez Microsoft: SQL Server (zainstalowany lokalnie) lub Azure SQL DB (oprogramowanie jako usługa na platformie chmury Microsoft). SQL Server można pobrać ze strony Microsoft (https://oreil.ly/t-HsT). Można użyć edycji Express lub Developer. Obie są bezpłatne i wystarczające do wykonania tych ćwiczeń.

Alternatywnie można zarejestrować się w Azure SQL DB (https://oreil.ly/_-BPe). Do wykonania tych ćwiczeń wystarczy bezpłatny dostęp próbny.

Dostęp z możliwością zapisu do bazy danych "AdventureWorksDW"

Większość przykładów opiera się na schemacie hurtowni danych fikcyjnej firmy, którą nazwano "Adventure Works". Jest to detaliczny sprzedawca sprzętu sportowego, który większość swoich przychodów uzyskuje ze sprzedaży rowerów na trzech kontynentach, realizowanej pośrednio przy udziale odsprzedawców lub bezpośrednio za pośrednictwem własnego sklepu internetowego. Aby utworzyć niezbędne obiekty bazy danych (tabele, widoki, procedury, funkcje, schematy), potrzebne są uprawnienia zapisu do bazy danych "AdventureWorksDW" (tej z przyrostkiem "DW" w nazwie, a nie "AdventureWorks", "AdventureWorksLT" czy "Adventure Works OLTP"). Plik kopii zapasowej i opis sposobu jego instalacji w SQL Server można znaleźć na stronie "AdventureWorks sample databases" (https://oreil.ly/yTjL8).

Aby zainstalować bazę danych "AdventureWorksDW" jako bazę danych Azure SQL DB, można użyć "aplikacji warstwy danych" (znanej również jako plik BACPAC) (https://oreil.ly/DYsLU). Ten plik można zainstalować za pomocą SQL Server Management Studio (SSMS). Kliknij prawym przyciskiem obiekt Databases (Bazy danych) i wybierz "Import Data-tier Application" (Importuj aplikację warstwy danych), jak to pokazano na rysunku 0-10.

Rysunek 0-10 Importowanie aplikacji warstwy danych w SQL Server Management Studio

Podaj lokalizację pliku, do której został on pobrany (rysunek 0-11).

Rysunek 0-11 Podawanie nazwy folderu i pliku

Najmniejsza/najtańsza edycja będzie w pełni wystarczająca (rysunek 0-12).

Rysunek 0-12 Wybieranie najmniejszego dostępnego ustawienia bazy danych

Uruchomienie bazy danych w formie bezserwerowej pozwoli na dodatkowe obniżenie kosztów, ponieważ taka baza danych (a wraz z nią wszystkie koszty) będzie przechodzić w stan hibernacji, jeśli nie będziemy jej używać przez godzinę. Przy kolejnej próbie uzyskania dostępu do bazy będzie ona wybudzana, co może skutkować przekroczeniem limitu czasu. Wtedy wystarczy połączyć się ponownie, a połączenie zostanie pomyślnie nawiązane.

Na koniec, po nawiązaniu połączenia z bazą danych AdventureWorksDW, należy uruchomić skrypt 001 Preparation.sql, aby utworzyć wszystkie artefakty dla środowiska demonstracyjnego.

Dodatkowe narzędzia

Społeczność pasjonatów danych skupiona wokół Power BI jest po prostu świetna. Naprawdę podziwiam tych mądrych ludzi, którzy łączą głęboką wiedzę na temat technologii stojącej za Power BI ze zrozumieniem potrzeb profesjonalnych deweloperów Power BI, a którzy mają również umiejętności pozwalające na tworzenie przydatnych narzędzi. Niektóre z tych osób rozwijają nawet swoje własne narzędzia open source i udostępniają je społeczności. W tej książce odwołuję się do dwóch takich narzędzi:

Tabular Editor V2, wersji open source narzędzia Tabular Editor V3 (https://tabulareditor.com) autorstwa Daniela Otykiera (dyrektora ds. technologii w Tabular Editor ApS) DAX Studio (https://daxstudio.org), narzędzia open source autorstwa Darrena Gosbella (starszego menedżera programu w Microsoft)

Pliki demonstracyjne

Dla każdego problemu udostępniam po jednym pliku Power BI Desktop (.pbix), który zawiera cały model danych oraz wszystkie rozwiązania w DAX i Power Query/M, jak również połączenie do tabel w Azure SQL. Umożliwia to łatwe porównywanie ze sobą różnych rozwiązań technicznych.

Większość przykładów ma charakter edukacyjny: np. w pliku Data multiple.pbix trzykrotnie występuje tabela Order Date o takiej samej zawartości. Order Date (DAX) to wersja w całości utworzona w języku DAX, Order Date (PQ) to wersja w całości utworzona w Power Query, a Order Date (SQL), jak można się domyślić, to wersja w całości utworzona w SQL i załadowana bezpośrednio do Power BI. Aby uniknąć nieporozumień: w praktycznym scenariuszu nie ma sensu tworzyć w modelu danych kilku tabel z taką samą zawartością. Tak samo nie ma sensu dokonywać niektórych przekształceń w języku DAX, a pozostałych w Power Query i/lub SQL. Warto trzymać się jednego narzędzia, aby łatwiej nam było znaleźć określone przekształcenie.

Wszystkie pliki dla tej książki można znaleźć w repozytorium GitHub (http://github.com/MEhrenmueller/DataModeling).

Teraz jesteś już gotowy na pierwszy rozdział, który pozwoli Ci zrozumieć, czym jest model danych i z czego się składa.

Konwencje użyte w tej książce

W książce zastosowano następujące konwencje typograficzne:

Kursywa

Wskazuje nowe terminy, adresy URL, adresy e-mail, nazwy i rozszerzenia plików.

Stała szerokość

Używana przy listingach programów, jak również w obrębie akapitów w celu odwołania się do elementów programów, takich jak nazwy zmiennych lub funkcji, bazy danych, typy danych, zmienne środowiskowe, instrukcje czy słowa kluczowe.

Pogrubienie o stałej szerokości

Używane dla poleceń lub innego tekstu, który użytkownik powinien wpisać dosłownie.

Kursywa o stałej szerokości

Używana dla tekstu, który należy zastąpić wartościami dostarczonymi przez użytkownika lub wartościami określonymi przez kontekst.

Dla całego kodu DAX (głównie w części III) obowiązują również następujące konwencje:

[Nazwa miary] :=

<definicja miary>

'Nazwa tabeli'[Nazwa kolumny] =

<definicja kolumny obliczanej>

[Nazwa tabeli] = /* tabela obliczana */

<definicja tabeli obliczanej>

Przykładowy kod

Materiały dodatkowe (przykładowy kod, ćwiczenia itd.) dostępne są do pobrania na stronie http://github.com/MEhrenmueller/DataModeling.

Jeżeli masz jakieś pytanie techniczne lub problem dotyczący przykładowego kodu, wyślij e-mail na adres [email protected].

Celem tej książki jest ułatwić Ci wykonywanie Twojej pracy. W ogólnym przypadku, jeśli książka dostarczana jest z przykładowym kodem, możesz używać go w swoich programach i dokumentacji. Nie musisz pytać nas o zgodę, chyba że wykorzystujesz znaczną część tego kodu. Na przykład pisanie programu, który wykorzystuje kilka fragmentów kodu z tej książki nie wymaga uzyskania od nas pozwolenia. Sprzedaż lub dystrybucja przykładów z książek O'Reilly wymaga odpowiedniego pozwolenia. Udzielanie odpowiedzi poprzez zacytowanie tej książki i przykładowego kodu nie wymaga pozwolenia. Zawieranie sporej ilości przykładowego kodu z tej książki w dokumentacji własnego produktu wymaga uzyskania naszej zgody.

Choć zwykle jej nie wymagamy, jesteśmy wdzięczni za atrybucję. Atrybucja zawiera przeważnie tytuł, autora, wydawcę oraz numer ISBN. Na przykład: "Data Modeling with Microsoft Power BI autorstwa Markusa Ehrenmueller-Jensena (O'Reilly). Copyright 2024 Savory Data Gmbh, 978-1-098-14855-3".

Jeśli wydaje Ci się, że sposób użycia przez Ciebie przykładowego kodu może wykraczać poza dozwolony użytek lub wspomniane powyżej zgody, skontaktuj się z nami pod adresem [email protected].

Jak się z nami skontaktować?

Wszelkie komentarze i pytania związane z tą książką proszę przesyłać do wydawcy:

O'Reilly Media, Inc.1005 Gravenstein Highway NorthSebastopol, CA 95472800-998-9938 (w Stanach Zjednoczonych lub Kanadzie)707-829-0515 (międzynarodowy lub lokalny)707-829-0104 (faks)[email protected]://www.oreilly.ccom/about/contact.html.

Mamy dedykowaną stronę dla tej książki, na której zamieściliśmy erratę, przykłady i wszelkie dodatkowe informacje. Znajdziesz ją pod adresem https://oreil.ly/DataModelingwithMicrosoftPowerBI.

Więcej informacji na temat naszych książek i kursów znajdziesz na stronie https://oreilly.com.

Znajdź nas na LinkedIn: https://linkedin.com/company/oreilly-media.

Oglądaj nas na YouTube: https://youtube.com/oreillymedia.

Podziękowania

Pisanie książki na początku wyglądało jak samotna praca: spisanie koncepcji i szkicu, przeglądanie własnych materiałów i przeprowadzanie badań, opracowywanie i ulepszanie przykładów, tworzenie zrzutów ekranu i diagramów, przekształcanie myśli i pomysłów w słowa itp. Ale szybko odkryłem, że jest to przedsięwzięcie zbiorowe. Miałem szczęście pracować z bardzo zaangażowanymi i zmotywowanymi ludźmi, których chcę tu sprowadzić przed kurtynę.

Michelle Smith była redaktorką merytoryczną, która zajmowała się moją propozycją książki i pomogła jej stać się użyteczną wersją roboczą na początek, a Shira Evans była doskonałą redaktorką wspomagającą powstawanie książki. Odbyłem niezliczone godziny owocnych dyskusji z Shirą, podczas których kwestionowała proponowaną strukturę, upewniała się, że jestem zgodny z wytycznymi wydawcy, zadawałem właściwe pytania, aby książka była bardziej czytelna, i przez cały czas motywowała mnie swoją wiedzą, doświadczeniem i wesołym nastrojem. Dziękuję także Katherine Tozer, Kate Dullea, Liz Wheeler, Alexis Browsh i wszystkim pracownikom WordCo Indexing Services za ich pracę nad końcową wersją produkcyjną.

Jestem również bardzo zadowolony, że przekonałem uznanych czołowych ekspertów Shabnama Watsona, Matta Allingtona i Nikolę Ilica do przyjęcia roli recenzentów technicznych tego projektu. Doceniam czas, jaki poświęcili tej książce. Dzięki ich komentarzom i odkryciom książka stała się o wiele lepsza.

Ponadto chcę podziękować wszystkim moim klientom, dzięki którym jestem zajęty projektami, a także uczestnikom moich seminariów, warsztatów i seminariów internetowych w ciągu ostatnich lat. Rozwiązywanie ich trudnych problemów, przygotowywanie materiałów do prezentacji i odpowiadanie na ich pytania doprowadziło w końcu do pomysłu zebrania wszystkiego w jedną całość na potrzeby tej książki.

Część 1

Podstawy modelowania danych

Pierwsza część tej książki ma na celu wyrównanie poziomu wiedzy czytelników w zakresie modelowania danych. Zawarte w tej części rozdziały mają charakter agnostyczny: treść, problemy i rozwiązania nie są specyficzne dla konkretnego typu systemu zarządzania bazą danych. Zdobytą w tej części wiedzę można zastosować do dowolnej relacyjnej lub analitycznej bazy danych. Obejmuje to oczywiście Power BI, Analysis Services Tabular i bazę danych Azure SQL DB, ale nie ogranicza się wyłącznie do nich, a wszystkie przedstawione stwierdzenia, informacje i wnioski można zastosować do systemów zarządzania bazą danych innych dostawców niż Microsoft - od klasycznych kostek, po magazyny data lakehouse itd.

Te koncepcje istnieją od dziesięcioleci i są tak dojrzałe, że z pewnością będą obowiązywać jeszcze przez kolejne dekady. Warto poznać je wszystkie, aby zrozumieć, dlaczego w dalszej części tej książki nalegam na zastosowanie takiego czy innego przekształcenia w kontekście modelowania danych w Power BI.

W rozdziale 1. wprowadzamy następujące podstawowe pojęcia i koncepcje:

Encje i tabele Relacje i ich kardynalność Klucze główne i klucze obce

Nauczymy się łączyć informacje rozproszone po różnych tabelach za pomocą operatorów zbiorowych i operatorów złączeń, a także poznamy potencjalne problemy, które mogą przykładowo skutkować brakiem danych lub ich powieleniem. Omawiamy tu trzy podstawowe możliwości modelowania danych (łączenie wszystkiego w jedną tabelę, dzielenie informacji w sposób pozwalający uniknąć powielenia danych w każdych okolicznościach, a także kompromis w postaci tzw. modelu wymiarowego). Nauczymy się decydować o tym, którego z tych wariantów należy użyć i w jakich warunkach.

Na podstawie informacji przedstawionych w rozdziale 1., w rozdziale 2. nauczymy się przekształcać model danych z naszego źródła danych na model danych o zamierzonym kształcie. Można tego dokonać przy użyciu kroków przekształcania, wliczając w to:

Normalizowanie i denormalizowanie tabel Dodawanie obliczeń Przekształcanie flag i wskaźników na zrozumiały tekst Dodawanie dedykowanej tabeli zawierającej wszystkie daty i/lub znaczniki czasu Modelowanie wymiarów, gdy mogą one odgrywać różne role (np. dana osoba może odgrywać rolę pracownika lub odgrywać rolę klienta) Modelowanie wymiarów, gdy musimy śledzić zmiany atrybutów na przestrzeni czasu Implementowanie hierarchii

Z mojego doświadczenia wynika, że wyzwania opisane w rozdziale 2. są powszechnie spotykane w większości modeli danych. Rozdział 3. dotyczy nieco rzadszych wyzwań, które nie występują w każdym modelu danych. Wszystkie te wyzwania to rzeczywiste problemy moich klientów, którymi musiałem się zająć. Aby je rozwiązać, będziemy musieli połączyć ze sobą różne techniki, a to sprawia, że są one bardziej zaawansowane.

W rozdziale 4., który jest poświęcony optymalizacji wydajności, rozmawiamy o tym, jaką rolę odgrywa model danych, jeśli chodzi o zapewnienie szybkiego działania raportów i zapytań opartych na modelu danych. Zasadniczo musimy zdecydować, czy chcemy utrwalić dane w kształcie, w jakim potrzebujemy ich do celów analitycznych, czy wolimy przechowywać jedynie kod dla zapytania, które zamiast tego będzie w locie przekształcać oryginalne dane i nadawać im wymagany kształt.

Rozdział 1

Czym jest model danych?

W tym rozdziale omówione zostaną podstawy modelowania danych, zaczynając od podstawowych pojęć, które pomogą uzasadnić, dlaczego modelowi danych należy poświęcić tak wiele uwagi. Model danych zoptymalizowany pod kątem tworzenia raportów i przeprowadzania analiz jest znacznie łatwiejszy w obsłudze niż model zoptymalizowany do innych celów (np. do przechowywania danych dla aplikacji lub danych zebranych w arkuszu kalkulacyjnym). Jeśli chodzi o analityczne bazy danych i hurtownie danych, to mamy więcej niż jedną opcję.

Celem w tej książce jest utworzenie modelu danych w formie schematu gwiazdy. Pod koniec tego rozdziału będziemy znali cechy schematu gwiazdy, które wyróżniają go na tle innych podejść do modelowania. Każdy z rozdziałów tej książki podkreśla, dlaczego schemat gwiazdy jest tak ważny w przypadku analitycznych baz danych, a w szczególności w przypadku Power BI i Analytics Services Tabular. Nauczymy się przekształcać dowolny model danych na schemat gwiazdy.

Przekształcanie informacji ze źródła danych na schemat gwiazdy zwykle nie jest łatwym zadaniem. Wręcz przeciwnie, może się to okazać trudne i wymagać kilku iteracji. Może to wymagać rozmów z osobami, dla których budujemy model danych, z użytkownikami raportów, a także z osobami odpowiedzialnymi za źródła danych. Możemy napotkać wątpliwości (ze strony innych osób, jak i nas samych), czy naprawdę warto wkładać w to cały ten wysiłek, zamiast po prostu pobierać dane w ich bieżącej postaci. W takim momencie warto wziąć głęboki oddech i ocenić, czy dane przekształcenie ułatwi życie twórcy raportu. Jeżeli tak, to warto podjąć ten wysiłek. Raz jeszcze powtórzmy wspólnie mantrę modelarza danych: uprościć życie twórcy raportów.

Zanim porozmawiamy o przekształceniach, wprowadźmy kilka podstawowych terminów i koncepcji:

Czym jest model danych? Czym jest encja? Co encja ma wspólnego z tabelą? Dlaczego należy zadbać o relacje? Dlaczego trzeba identyfikować różne klucze (główne, obce i zastępcze) i zrozumieć ogólne znaczenie kardynalności? Jak można łączyć tabele (operatory zbiorowe i złączenia)? Jakie są opcje modelowania danych?

Pierwszym przystankiem w naszej podróży w kierunku schematu gwiazdy jest zrozumienie tego, czym w rozumieniu ogólnym jest model danych.

Model danych

Model jest czymś, co reprezentuje świat rzeczywisty, ale go nie replikuje. Rozważmy przykładowo mapę. Mapa replikująca świat rzeczywisty w skali 1:1 byłaby niepraktyczna: obejmowałaby całą planetę. Zamiast tego mapy zmniejszają odległości. Mapy są tworzone do celów specjalnych. Mapa turystyczna zawiera (i pomija) inne rodzaje informacji niż przykładowo mapa drogowa. Z kolei mapa morska wygląda jeszcze inaczej. Są to wszystko mapy, ale o różnym przeznaczeniu.

To samo dotyczy modelu danych, który reprezentuje pewną logikę biznesową. Podobnie jak w przypadku mapy, model danych będzie wyglądał inaczej w różnych przypadkach użycia. Z tego względu modele dla różnych gałęzi przemysłu będą się od siebie różnić. Co więcej, nawet organizacje z tej samej branży będą potrzebować różnych modeli danych (nawet w przypadku zasadniczo identycznych procesów biznesowych), ponieważ będą się koncentrować na różnych wymaganiach. Problemy i rozwiązania opisane w tej książce pomogą nam pokonać przeszkody podczas tworzenia modelu danych dla naszej organizacji.

A teraz zła wiadomość: nie ma jednego modelu danych, który rządziłby wszystkimi. Ponadto nie da się stworzyć użytecznego modelu danych mając jedynie wiedzę techniczną, ale bez wiedzy z określonej dziedziny. Ale jest też dobra wiadomość: ta książka przeprowadzi nas przez kwestie techniczne niezbędne do skutecznego budowania modeli danych dla Power BI i/lub Analysis Services Tabular.

Pamiętajmy o tym, aby przed utworzeniem modelu danych zebrać lub zarejestrować wszystkie wymagania biznesowe. Oto kilka przykładów wymagań w języku naturalnym:

Sprzedajemy towary klientom i musimy znać dzień oraz numer jednostki magazynowej SKU sprzedanego przez nas produktu Musimy przeanalizować 12-miesięczną średnią kroczącą dla sprzedanej ilości każdego produktu Zespół projektowy składa się z maksymalnie 10 pracowników i musimy raportować godziny pracy dla każdego zadania projektowego

Te wymagania pomogą nam ustalić, jakie informacje należy przechowywać, w jakich kombinacjach i na jakim poziomie szczegółowości. Dla określonego przypadku użycia może istnieć więcej niż jedna opcja dla projektu modelu danych.

I, co bardzo ważne, już na samym początku musimy stworzyć prawidłowy model danych. Gdy na bazie modelu danych powstaną już pierwsze raporty, każda zmiana w tym modelu będzie niosła ze sobą ryzyko popsucia tych raportów. Im później odkryjemy niespójności i błędy w naszym modelu danych, tym droższa będzie ich naprawa. Ten koszt uderza w każdego, kto utworzył te raporty - nas samych, ale też innych użytkowników, którzy utworzyli raporty w oparciu o nasz model danych.

Projekt modelu danych ma ogromny wpływ na wydajność naszych raportów, które wysyłają zapytania o dane z modelu danych w celu wprowadzenia ich do wizualizacji. Dobrze zaprojektowany model danych zmniejsza potrzebę późniejszej optymalizacji zapytań. Ponadto dobrze zaprojektowany model danych będzie bardziej intuicyjny dla twórców raportów, pozwalając im oszczędzić czas i wysiłek (a także pieniądze naszej organizacji). Patrząc na to z innej perspektywy, problemy z wydajnością raportu, jak również twórcy raportu, którzy nie są pewni tego, z których tabel i kolumn powinni skorzystać do uzyskania określonych informacji, to pewna oznaka modelu danych, który można usprawnić za pomocą lepszego projektu.

W punkcie "Diagramy związków encji" na stronie 25 opisujemy graficzne sposoby dokumentowania kształtu modelu danych. Najpierw jednak porozmawiamy o encjach modelu danych.

Podstawowe komponenty

Zanim zagłębimy się w ten temat, musimy najpierw poznać kilka kluczowych komponentów modelu danych. W tym podrozdziale wyjaśniamy podstawowe części modelu danych. W podrozdziale "Łączenie tabel" na stronie 11 omówimy różne sposoby łączenia tabel za pomocą operatorów zbiorowych i złączeń, a także problemy, jakie możemy napotkać, wraz ze sposobem ich rozwiązania.

Pamiętajmy, że ta część książki nie dotyczy wyłącznie Power BI. Niektóre koncepcje mogą mieć zastosowanie tylko wtedy, gdy przygotujemy dane przed podłączeniem do nich Power Query (np. w przypadku pisania instrukcji SQL w relacyjnej bazie danych).

Encja

Encja (entity) to ktoś lub coś, co można indywidualnie zidentyfikować. W języku naturalnym encje są rzeczownikami. Może to być jakaś prawdziwa osoba (na przykład nasz ulubiony nauczyciel), jakiś niedawno zakupiony produkt (może lody?) lub jakieś pojęcie (np. encja).

Encje mogą być zarówno rzeczywiste, jak i fikcyjne. Większość z nich ma pewne atrybuty: nazwę, wartość, kategorię, czas utworzenia itd. Te atrybuty to informacje, które będą nas interesować w kontekście danych. Atrybuty są wyświetlane w raportach, aby pomóc czytelnikowi zapewnić kontekst, wyciągać wnioski i podejmować decyzje. Służą one również do filtrowania wyświetlanych informacji w celu zawężenia analizy.

W jaki sposób takie encje trafiają do modelu danych? Są one przechowywane w tabelach.

Tabele

Tabele stanowią podstawę modelu danych. Są one częścią modeli danych co najmniej od 1970 r., kiedy to Edgar F. Codd opracował relacyjny model danych dla swojego pracodawcy, IBM. Jednak informacje w postaci list lub tabel zbierano na długo przed wynalezieniem komputerów, co można zobaczyć przeglądając stare książki.

Tabele przechowują encje: każda encja jest reprezentowana przez wiersz w tabeli. Każdy atrybut encji jest reprezentowany przez kolumnę w tabeli. Kolumna w tabeli ma nazwę (np. dzień urodzin) i typ danych (np. data). Wszystkie wiersze tej samej kolumny muszą być zgodne z jej typem danych (przykładowo w żadnym wierszu w kolumnie "dzień urodzin" nie ma możliwości zapisania miejsca urodzenia). Jest to istotna różnica pomiędzy tabelą (bazy danych) a arkuszem kalkulacyjnym (np. w Excelu). Pojedyncza kolumna w pojedynczej tabeli zawiera treść. Przykład można zobaczyć w tabeli 1-1.

Tabela 1-1 Tabela zawierająca nazwiska lekarzy

Nazwisko lekarza

Data zatrudnienia

Smith

1993-06-03

Gry

2005-03-27

Young

2004-12-01

Stevens

2000-06-07

Karev

1998-09-19

O'Malley

2003-02-14

Encje nie istnieją wyłącznie samodzielnie, lecz są ze sobą powiązane.

Relacje

W większości przypadków relacje łączą ze sobą tylko dwie encje. W języku naturalnym relacja jest reprezentowana przez czasownik (np. kupił). Pomiędzy tymi samymi dwoma encjami może istnieć więcej niż jedna relacja. Na przykład, klient mógł najpierw zamówić określony produkt, który my później do niego wysłaliśmy. Jest to ten sam klient i ten sam produkt, ale różne relacje (zamówiony kontra wysłany).

Niektóre relacje mogą być samoodwołujące się. Oznacza to, że może istnieć relacja pomiędzy jedną encją (jednym wierszem w tej tabeli) a inną encją tego samego typu (innym wierszem w tej samej tabeli). Typowym przykładem są hierarchie organizacyjne. Każdy pracownik (z wyjątkiem może dyrektora generalnego) musi raportować do swojego przełożonego. Takie odwołanie do szefa jest zatem atrybutem. Jedna kolumna zawiera identyfikator pracownika (np. [Employee ID]), a druga zawiera identyfikator osoby, do której ten pracownik raportuje (np. [Manager ID]). Identyfikator [Manager ID] w jednym wierszu może być identyfikatorem [Employee ID] w innym wierszu w tej samej tabeli.

Oto kilka przykładów relacji wyrażonych w języku naturalnym:

Dr Smith leczy pana Jonesa. Michael odbył kurs modelowania danych. Pan Gates jest właścicielem firmy Microsoft. Pan Nadella jest dyrektorem generalnym.

Kiedy zaczynamy zbierać wymagania na potrzeby jakiegoś raportu (a tym samym dla naszego modelu danych), warto zapisać je w formie zdań w języku naturalnym, jak na powyższej liście. Jest to pierwszy krok. W punkcie "Diagramy związków encji" na stronie 25 nauczymy się rysować tabele i relacje między nimi w postaci diagramów związków encji.

Czasami już samo istnienie pewnych relacji jest wystarczającą informacją do zebrania i przeprowadzenia analizy. Jednak niektóre relacje mogą mieć atrybuty, które zbieramy na potrzeby bardziej szczegółowej analizy:

Dr Smith leczy pana Jonesa z powodu grypy. Michael ukończył kurs modelowania danych z oceną 5. Pan Gates jest właścicielem 50% firmy Microsoft. Pan Nadella jest dyrektorem generalnym od 4 lutego 2014 r.

Wiemy już, że encje są reprezentowane jako wiersze w tabelach. Nasuwa się jednak pytanie, w jaki sposób możemy połączyć ze sobą wiersze, aby móc reprezentować relacje. Pierwszym krokiem jest znalezienie (kombinacji) kolumn, które jednoznacznie identyfikują dany wiersz. Taki unikatowy identyfikator nazywany jest kluczem głównym (primary key).

Klucze główne

Zarówno w świecie rzeczywistym, jak i w modelu danych ważne jest to, aby można było jednoznacznie zidentyfikować konkretną encję (wiersz w tabeli). Przykładowo ludzie są identyfikowani w prawdziwym świecie na podstawie imion. Kiedy znamy dwie osoby o tym samym imieniu, możemy coś do tych imion dodać (np. nazwisko) lub wymyślić jakiś pseudonim (który jest zwykle krótszy od połączonego imienia i nazwiska), aby móc łatwo określić, do kogo się odwołujemy (nie tracąc na to zbyt wiele czasu). Jeśli ktoś nie słucha uważnie, może odwoływać się do jednej osoby, podczas gdy drugi rozmówca będzie mieć na myśli kogoś innego ("Ach, mówisz o tym drugim Johnie!").

Podobnie jest w przypadku tabeli: możemy oznaczyć jedną kolumnę (lub połączony zestaw kolumn, tj. klucz złożony) jako klucz główny tej tabeli. Jeśli tego nie zrobimy, możemy doprowadzić do powstania mylących raportów z powodu duplikatów (np. dla każdego Johna może być ukazywana łączna sprzedaż dla wszystkich Johnów).

Najlepszą praktyką jest używanie pojedynczej kolumny jako klucza głównego (w przeciwieństwie do klucza złożonego), z tego samego powodu, dla którego używamy pseudonimów dla ludzi: jest krótszy i dlatego łatwiejszy w użyciu. Możemy zdefiniować tylko jeden klucz główny.

Jawne zdefiniowanie klucza głównego w tabeli niesie ze sobą pewne konsekwencje. Nakłada to na taką kolumnę ograniczenie unikatowości (co gwarantuje, że żaden inny wiersz nie może mieć takiej samej wartości jak klucz główny i gwarantuje odrzucanie wstawień i aktualizacji danych, które spowodowałyby naruszenie tej reguły). Każdy znany mi system zarządzania relacyjną bazą danych umieszcza również indeks na kluczu głównym (aby przyspieszyć sprawdzanie, czy dane wstawienie lub aktualizacja danych naruszyłaby klucz główny). Wszystkie kolumny używane jako klucz główny muszą zawierać jakąś wartość (wartości null nie są obsługiwane). Moim zdaniem ograniczenie klucza głównego powinno się umieszczać w każdej tabeli, aby uniknąć zduplikowanych wierszy.

Aby upewnić się, że każdy wiersz w tabeli jest jednoznacznie identyfikowalny, możemy oznaczyć wiersz lub kombinację kilku wierszy jako klucz główny. W przykładzie z kolumnami [Employee ID] i [Manager ID] ważne jest, aby zawartość kolumny [Employee ID] była unikatowa dla całej tabeli.

Zazwyczaj w hurtowni danych (bazie danych zbudowanej wyłącznie w celu ułatwienia raportowania), zamiast używać jednej z kolumn systemu źródłowego jako klucza głównego (np. imienia i nazwiska lub numeru PESEL), wprowadza się nowy sztuczny identyfikator, który istnieje tylko w hurtowni danych: klucz zastępczy.

Klucze zastępcze

Klucz zastępczy (surrogate key) to pewna sztuczna wartość używana wyłącznie w hurtowni danych lub analitycznej bazie danych. Nie jest to ani klucz naturalny encji, ani klucz biznesowy systemu źródłowego, a już na pewno nie jest to klucz złożony, a tylko pojedyncza wartość. Jest on tworzony wyłącznie na potrzeby posiadania jednej kolumny klucza, która jest niezależna od jakiegokolwiek systemu źródłowego. Można o nim myśleć jak o (nieco dziwnym i tajnym) pseudonimie, który jednoznacznie identyfikuje encję i będzie używany wyłącznie do łączenia tabel, ale nigdy do filtrowania zawartości tabeli. Klucz zastępczy nigdy nie jest udostępniany odbiorcom raportów ani użytkownikom systemu analitycznego.

Zazwyczaj takie kolumny mają w swoich nazwach "Key", "ID" lub "SID" (np. ProductKey, Customer_ID lub SID). Powszechnie stosowane systemy zarządzania relacyjną bazą danych są w stanie automatycznie znaleźć za nas wartość dla takiej kolumny. Najlepszą praktyką jest użycie wartości całkowitej, rozpoczynając od 1. W zależności od liczby wierszy, jakiej spodziewamy się wewnątrz tabeli, powinniśmy znaleźć odpowiedni typ liczby całkowitej, która zwykle może obejmować od 1 bajtu (= 8 bitów = 28 = 256 wartości) do 8 bajtów (= 8 × 8 bitów = 64 bity = 264 = 18 446 744 073 709 551 616 wartości).

Czasem używane są identyfikatory unikatowe globalnie. Mają one swoje zastosowanie w scenariuszach skalowania w poziomie, gdzie procesy muszą działać niezależnie od siebie, ale nadal generują klucze zastępcze dla wspólnej tabeli. Wymagają one więcej miejsca do przechowywania (16 bajtów) w porównaniu do wartości całkowitej (maksymalnie 8 bajtów). Dlatego też używałbym ich tylko w przypadkach, gdy użycie wartości całkowitych nie wchodzi w grę.

Celem jest uniezależnienie funkcjonowania hurtowni danych od systemu źródłowego (np. gdy następuje zmiana systemu planowania przedsiębiorstwa [ERP], gdy system ERP ponownie wykorzystuje identyfikatory dla nowych encji lub gdy zmienia się typ danych klucza biznesowego ERP).

Klucze zastępcze są również niezbędne, gdy chcemy zaimplementować wymiar wolnozmienny typu 2, który omawiany jest w podrozdziale "Wymiary wolnozmienne" na stronie 52.

W kolejnym podrozdziale porozmawiamy o tym, jak reprezentować relacje między encjami. Rozwiązanie zagadki dotyczącej tworzenia relacji między encjami nie jest bardzo skomplikowane: po prostu klucz główny danej encji przechowujemy w formie kolumny w encji, która ma z nią powiązanie. W ten sposób odwołujemy się do innej encji. Odwołujemy się do klucza głównego obcej encji. Stąd też ta kolumna nazywana jest kluczem obcym w tabeli odwołującej się.

Klucze obce

Klucze obce (foreign key) po prostu odwołują się do klucza głównego innej encji. Klucz główny jest przechowywany w innej kolumnie, w tej samej lub w innej tabeli. Przykładowo tabela sprzedaży będzie zawierać kolumnę [Product ID] w celu identyfikacji produktów, które zostały sprzedane. Kolumna [Product ID] jest kluczem głównym w tabeli Product. Kolumna [Manager ID] w tabeli Employee odwołuje się do kolumny [Employee ID] w tej samej tabeli (Employee).

Kiedy jawnie zdefiniujemy ograniczenie klucza obcego w tabeli, system zarządzania bazą danych upewni się, że wartość kolumny klucza obcego dla każdego wiersza jest dostępna jako wartość w przywoływanym kluczu głównym. Zagwarantuje to, że żadne wstawienie lub aktualizacja danych w tabeli odwołującej się nie będzie w stanie zmienić wartości klucza obcego na coś nieprawidłowego. Zagwarantuje to również, że klucz główny, do którego istnieje odwołanie, nie będzie mógł zostać zaktualizowany na coś innego lub usunięty w tabeli, do której następuje odwołanie.

Najlepszą praktyką jest wyłączenie dopuszczalności wartości null dla kolumny klucza obcego. Jeśli wartość klucza obcego jest (jeszcze) nieznana lub jest pozbawiona sensu w bieżącym kontekście, należy zastosować wartość zastępczą (zwykle klucz zastępczy -1). Aby to działało, musimy jawnie dodać wiersz z wartością -1 jako klucz główny do tabeli, do której się odwołujemy. Daje nam to lepszą kontrolę nad tym, co wyświetlić w przypadku braku jakiejś wartości (zamiast pokazywać pustą wartość w raporcie). Umożliwia to także złączenia wewnętrzne, które są bardziej wydajne w porównaniu do złączeń zewnętrznych (które są niezbędne, gdy jakiś klucz obcy zawiera wartości null, aby te wiersze nie zostały utracone w wyniku. Więcej o złączeniach można dowiedzieć się w punkcie "Złączenia" na stronie 13).

Podczas gdy utworzenie ograniczenia klucza głównego spowoduje automatyczne umieszczenie indeksu na tym kluczu, utworzenie ograniczenia klucza obcego nie jest implementowane w ten sposób przykładowo w Azure SQL DB lub SQL Server. Aby przyspieszyć złączenie tabeli zawierającej klucz obcy z tabelą zawierającą klucz główny, zaleca się zindeksowanie kolumny klucza obcego.

W jaki sposób ustalić, w której z dwóch encji będących ze sobą w relacji przechować klucz obcy? Zanim odpowiemy na to pytanie, musimy omówić różne typy relacji.

Kardynalność

Termin kardynalność (cardinality) ma dwa znaczenia. Może to być liczba opisująca, ile odrębnych wartości w jednej kolumnie (lub kombinacji wartości w przypadku kilku kolumn) można znaleźć w tabeli. Jeśli przechowujemy w kolumnie wartość binarną, np. "tak" lub "nie", wówczas kardynalność takiej kolumny będzie wynosić dwa.

Kardynalność klucza głównego tabeli będzie zawsze taka sama jak liczba wierszy w tej tabeli, ponieważ każdy wiersz tabeli będzie miał inną wartość. W kolumnowej bazie danych (takiej jak Power BI lub Analysis Services Tabular) współczynnik kompresji zależy od kardynalności kolumny (im mniej różnych wartości, tym lepsza będzie kompresja).

W dalszej części tej książki odnosimy się głównie do tego drugiego znaczenia, opisującego kardynalność jako (maksymalną) liczbę wierszy, które można znaleźć w powiązanej tabeli dla danego wiersza z tabeli odwołującej się. Mając dane dwie tabele, możemy mieć do czynienia z następującymi rodzajami kardynalności:

Jeden do wielu (1:w, 1-*)

Przykładowo jeden klient może mieć wiele zamówień. Jedno zamówienie pochodzi od dokładnie jednego klienta.

Jeden do jednego (1:1, 1-1)

Na przykład ta osoba jest w związku małżeńskim z tą drugą osobą.

Wiele do wielu (w:w, *-*)

Na przykład jeden pracownik pracuje nad wieloma różnymi projektami. Jeden projekt może mieć wielu pracowników.

Kardynalność jest definiowana przez reguły biznesowe. Możliwe, że w naszej organizacji jedno zamówienie można przypisać dwóm klientom jednocześnie. Wtedy założenie "jeden do wielu" byłoby błędne i konieczne byłoby wymodelowanie tej relacji jako relacji "wiele do wielu". Znalezienie prawidłowych kardynalności jest kluczowym zadaniem podczas projektowania modelu danych. Upewnijmy się, że w pełni rozumiemy reguły biznesowe, aby uniknąć błędnych założeń.

Jeśli chcemy być bardziej szczegółowi, możemy również opisać, czy dana relacja może być warunkowa. Jako że wszystkie relacje po stronie "wiele" są warunkowe (np. konkretny klient mógł jeszcze nie złożyć zamówienia), zwykle nie jest to jawnie wspominane. Relacje po stronie "jeden" mogą mieć charakter warunkowy (np. nie każda osoba jest w związku małżeńskim). W takim przypadku możemy zmienić opis relacji z 1:1 na warunkowa:warunkowa (conditional:conditional, c:c) w dokumentacji.

Łączenie tabel

Jak dotąd dowiedzieliśmy się, że informacje (encje i ich relacje) są przechowywane w tabelach w modelu danych. Zanim przedstawimy reguły, takie jak kiedy dzielić informacje na różne tabele, a kiedy trzymać je razem w jednej tabeli, porozmawiamy o tym, jak łączyć informacje rozmieszczone w różnych tabelach.

Operatory zbiorowe

"Zbiór" możemy sobie wyobrazić jako wynik zapytania lub jako wiersze danych w formie tabelarycznej. Operatory zbiorowe umożliwiają połączenie dwóch (lub więcej) wyników zapytań poprzez dodanie do siebie lub usunięcie wierszy. Należy pamiętać, że liczba kolumn w tych zapytaniach musi być taka sama, a typy danych w kolumnach muszą być identyczne, bądź też reguły konwersji typów danych używanego przez nas systemu zarządzania bazą danych muszą umożliwiać (niejawną) konwersję na typ danych kolumny pierwszego zapytania.

Pierwsze zapytanie ustawia zarówno typy danych, jak i nazwy kolumn dla końcowego wyniku. Operator zbiorowy nie zmienia liczby ani typu kolumn, a jedynie liczbę wierszy. Ilustruje to rysunek 1-1.

Rysunek 1-1 Operatory zbiorowe

Union (suma zbiorów)

Dodaje wiersze z drugiego zbioru do wierszy pierwszego zbioru. W zależności od używanego systemu zarządzania bazą danych, w wyniku mogą pojawić się duplikaty (UNION ALL na rysunku obok), albo mogą one zostać usunięte przez operator. Możemy przykładowo potrzebować połączonej listy klientów i dostawców.

Intersect (część wspólna, iloczyn zbiorów)

Wyszukuje wiersze, które występują w obu zbiorach. Zachowywane są tylko wiersze występujące w obu zbiorach, zaś wszystkie pozostałe wiersze są pomijane. Chcemy przykładowo dowiedzieć się, kto w naszym systemie widnieje zarówno jako klient, jak i dostawca.

Except (minus, różnica zbiorów)

Wyszukuje wiersze występujące w obu zbiorach. Zwracane są tylko wiersze z pierwszego zbioru, które nie pojawiają się w drugim zbiorze. "Odejmujemy" wiersze drugiej tabeli od wierszy pierwszej tabeli (stąd też ten operator nazywany jest również minusem). Na przykład, chcemy uzyskać listę klientów ograniczoną do tych, którzy nie są również dostawcami.

Aby ustalić, czy jakiś wiersz jest identyczny, czy nie, oceniana i porównywana jest zawartość wszystkich kolumn tego wiersza zapytania. Należy tutaj uważać: chociaż klucze główne wylistowane w zbiorze wynikowym mogą być identyczne, nazwy lub opisy mogą się różnić. Wiersze z identycznymi kluczami, ale różnymi opisami nie zostaną uznane za identyczne przez operator zbiorowy.

Jak widać, operatory zbiorowe łączą tabele pionowo. Zasadniczo dołączają one zawartość jednej tabeli do zawartości innej tabeli. Operatory zbiorowe nie zmieniają liczby kolumn. Jeśli chcemy połączyć tabele poprzez dodanie kolumn z jednej tabeli do kolumn innej tabeli, musimy skorzystać z operatorów złączeń.

Złączenia

Złączenia przypominają operatory zbiorowe w tym sensie, że również łączą ze sobą dwa (lub więcej) zapytania (lub tabele). W zależności od rodzaju operatora złączenia możemy uzyskać odpowiednio taką samą liczbę wierszy jak w pierwszej tabeli, więcej wierszy lub mniej wierszy. W przypadku złączeń możemy także dodawać kolumny do zapytania (co nie jest możliwe w przypadku operatora zbiorowego).

Podczas gdy operatory zbiorowe porównują wszystkie kolumny, złączenia są wykonywane tylko na wybranym (pod)zbiorze kolumn, który należy określić (w ramach tzw. predykatu złączenia). Dla predykatu złączenia zwykle stosuje się porównanie równości pomiędzy kluczem głównym jednej tabeli a kluczem obcym w drugiej tabeli (złączenie równościowe, equi-join). Możemy przykładowo chcieć wyświetlić nazwę klienta dla konkretnego zamówienia (i określić porównanie równości pomiędzy kluczem obcym tabeli zamówień [Customer Key] a kluczem głównym tabeli klientów [Customer Key] w predykacie złączenia).

Tylko w szczególnych przypadkach porównujemy ze sobą inne (nie będące kluczem) kolumny w celu połączenia dwóch tabel. Przykłady takich złączeń zobaczymy w rozdziałach 7., 11., 15. i 19., gdzie demonstrujemy rozwiązania dla zaawansowanych problemów. Zobaczymy tam także przykłady złączeń nierównościowych (non-equi-join). Złączenia nierównościowe do złączenia wierszy dwóch tabel wykorzystują takie operatory, jak pomiędzy, większe lub równe, nierówne itd.

Jeden z przykładów dotyczy grupowania wartości w przedziały (binning). Binning polega na znalezieniu grupy, do której należy określona wartość poprzez złączenie tabeli zawierającej grupy z warunkiem proszącym o wartości większe lub równe dolnemu zakresowi przedziału i mniejsze od górnego zakresu przedziału. Podczas gdy zakres wartości tworzy złożony klucz główny tabeli zawierającej grupy, wartość szukana nie jest kluczem obcym: jest to dowolna wartość, potencjalnie nie istniejąca jako wartość w tabeli wyszukiwania, ponieważ tabela wyszukiwania zawiera tylko wartość początkową i końcową każdego przedziału, ale nie wszystkie wartości w tym przedziale.

Szczególnym przypadkiem złączeń równościowych są złączenia naturalne. W takim złączeniu nie określa się kolumn do porównania. Kolumny, które mają być używane w złączeniach równościowych, są wybierane automatycznie: używane są kolumny o tej samej nazwie w dwóch złączonych tabelach. Jak można się domyślić, zadziała to tylko wtedy, gdy będziemy się trzymać konwencji nazewnictwa (co samo w sobie jest dobrym pomysłem) w celu umożliwienia takich złączeń. Jeśli kolumny klucza głównego i klucza obcego mają różne nazwy, złączenie naturalne nie będzie działać prawidłowo (np. gdy kluczem głównym w tabeli klientów jest kolumna ID, a w tabeli zamówień klucz obcy ma nazwę CustomerID). To samo dotyczy sytuacji odwrotnej, gdy kolumny w obu tabelach mają taką samą nazwę, ale nie są ze sobą w relacji (np. zarówno tabela Product, jak i tabela Product Category mają kolumnę Name, która reprezentuje odpowiednio nazwę produktu i nazwę kategorii produktu, ale nie można ich w sensowny sposób wykorzystać na potrzeby złączenia równościowego).

Istotna różnica pomiędzy operatorami zbiorowymi a złączeniami polega na tym, że złączenia dodają do pierwszej tabeli kolumny, podczas gdy operatory zbiorowe dodają wiersze. Złączenia umożliwiają dodanie kolumny kategorii do produktów, które można znaleźć w tabeli wyszukiwania. Operatory zbiorowe umożliwiają przykładowo połączenie tabel zawierających sprzedaż z różnych źródeł danych w jedną ujednoliconą tabelę sprzedaży.

Operatory zbiorowe wyobrażam sobie jako kombinację tabel ułożonych pionowo (jedna tabela pod drugą), a operatory złączeń jako poziome połączenie tabel (ułożonych obok siebie). Ta metafora nie do końca odzwierciedla stan faktyczny (operatory zbiorowe INTERSECT i EXCECPT usuwają wiersze, a złączenia również dodają lub usuwają wiersze w zależności od kardynalności relacji lub typu złączenia), ale myślę, że jest to dobrym punktem wyjścia do rozróżnienia tych operatorów.

Wcześniej w tym rozdziale jako typowego przykładu użyliśmy tabeli Employee, w której klucz obcy ([Manager Key]) odwołuje się do wiersza w tej samej tabeli (za pośrednictwem klucza głównego [Employee Key]). Jeśli faktycznie dokonamy złączenia tabeli Employee z nią samą, aby przykładowo znaleźć nazwisko menedżera dla jakiegoś pracownika, zaimplementujemy wtedy samozłączenie (self-join).

Dwie tabele możemy złączyć w następujący sposób:

Złączenie wewnętrzne

Wyszukuje wiersze występujące w obu tabelach. Zachowywane są tylko wiersze występujące w obu tabelach, zaś wszystkie pozostałe są pomijane. Możemy przykładowo uzyskać listę klientów, dla których możemy znaleźć zamówienia. Graficzną reprezentację przedstawia rysunek 1-2.

Rysunek 1-2 Złączenie wewnętrzne

Działa to podobnie do operatora zbiorowego INTERSECT, jednak wynik może zawierać tyle samo, więcej lub mniej wierszy, niż znajduje się w pierwszej tabeli. Będzie on zawierał taką samą liczbę wierszy, jeśli dla każdego wiersza w pierwszej tabeli istnieje dokładnie jeden wiersz w drugiej tabeli (np. gdy każdy klient złożył dokładnie jedno zamówienie). Będzie zawierał więcej wierszy, jeśli w drugiej tabeli znajduje się więcej niż jeden pasujący wiersz (np. gdy każdy klient złożył co najmniej jedno zamówienie lub gdy niektórzy klienci mają tak wiele zamówień, że kompensują te braki klientom, którzy nie złożyli żadnego zamówienia). Będzie zawierał mniej wierszy, jeśli niektórych wierszy z pierwszej tabeli nie można dopasować do wierszy w drugiej tabeli (np. gdy nie wszyscy klienci złożyli zamówienia, a brakujące zamówienia nie są kompensowane przez innych klientów).