Tworzenie modelu danych wydajnie korzystającego z Excel i dodatku Power Pivot

Dwa przykłady kolumn, które zawsze powinny być wykluczane

Pierwszy przykład dotyczy danych pochodzących z magazynów danych. W magazynie danych często odnajduje się artefakty procesów ETL, które załadują i odświeżają dane w magazynie. Kolumny takie jak "data utworzenia", "data aktualizacji" i "Czas wygaśnięcia" są tworzone podczas ładowania danych. W modelu nie są potrzebne żadne z tych kolumn i należy je wyebrać podczas importowania danych.

Drugi przykład obejmuje pominięcie kolumny klucza podstawowego podczas importowania tabeli faktów.

Wiele tabel, w tym tabele faktów, ma klucze podstawowe. W przypadku większości tabel, takich jak tabele zawierające dane klientów, pracowników lub sprzedaży, należy ustawić klucz podstawowy tabeli, aby można było za jego pomocą tworzyć relacje w modelu.

Tabele faktów różnią się. W tabeli fakt klucz podstawowy służy do unikatowego identyfikowania każdego wiersza. Jest to niezbędne do celów normalizowania, ale nie jest przydatne w modelu danych, w którym mają być ustanawiane tylko te kolumny, które są używane do analizy lub do ustanawiania relacji między tabelami. Dlatego podczas importowania z tabeli fakt nie należy uwzględniać jej klucza podstawowego. Klucze podstawowe w tabeli informacji zużywają olbrzymie ilości miejsca w modelu, ale nie zapewniają żadnych korzyści, ponieważ nie można ich używać do tworzenia relacji.

Jak wykluczyć niepotrzebne kolumny

Wydajne modele zawierają tylko te kolumny, które rzeczywiście będą potrzebne w skoroszycie. Jeśli chcesz kontrolować, które kolumny są uwzględnione w modelu, musisz zaimportować dane zamiast okna dialogowego "Importowanie danych" w dodatku Power Pivot za pomocą Kreatora importu Excel.

Po uruchomieniu Kreatora importu tabeli należy wybrać tabele do zaimportowania.

Dla każdej tabeli możesz kliknąć przycisk Podgląd & filtru i wybrać części tabeli, których naprawdę potrzebujesz. Zalecamy, aby najpierw usunąć zaznaczenie wszystkich kolumn, a następnie przejść do sprawdzenia wymaganych kolumn po rozważeniu, czy są one wymagane do analizy.

Modyfikowanie kolumn daty/dnia

W wielu przypadkach kolumny z datą mają dużo miejsca. Na szczęście istnieje wiele sposobów zmniejszenia wymagań dotyczących miejsca do magazynowania dla tego typu danych. Techniki będą się różnić w zależności od sposobu używania kolumny i Twojego poziomu komfortu w budowaniu SQL zapytaniach.

Kolumny z datą i godziną zawierają część daty i czas. Gdy zadajesz sobie pytanie, czy potrzebujesz kolumny, zadaj kilka razy to samo pytanie w kolumnie Datetime:

• Czy potrzebuję części czasu?

• Czy potrzebuję części czasu na poziomie godzin? , min? , Sekundy? , milisekundy?

• Czy mam wiele kolumn daty/czasu, ponieważ chcę obliczyć różnicę między nimi lub po prostu agregować dane według roku, miesiąca, kwartału i tak dalej.

Sposób odpowiadania na każde z tych pytań określa opcje dotyczące pracy z kolumną Datetime.

Wszystkie te rozwiązania wymagają modyfikacji zapytania SQL projektu. Aby ułatwić modyfikowanie zapytań, odfiltruj co najmniej jedną kolumnę w każdej tabeli. Filtrując kolumnę, można zmienić budowa zapytania z formatu skróconego (SELECT *) na instrukcji SELECT, która zawiera w pełni kwalifikowane nazwy kolumn, co jest znacznie łatwiejsze do zmodyfikowania.

Przyjrzyjmy się utworzonym zapytaniem. W oknie dialogowym Właściwości tabeli możesz przełączyć się do Edytora zapytań i wyświetlić bieżące zapytanie SQL dla każdej tabeli.

W oknie Właściwości tabeli wybierz pozycję Edytor zapytań.

W Edytorze zapytań jest SQL danych użyte do wypełnienia tabeli. Jeśli podczas importowania odfiltrujesz dowolną kolumnę, zapytanie zawiera w pełni kwalifikowane nazwy kolumn:

Modyfikowanie SQL danych

Teraz, gdy wiesz już, jak znaleźć zapytanie, możesz je zmodyfikować, aby dodatkowo zmniejszyć rozmiar modelu.

1. W przypadku kolumn zawierających dane walutowe lub dziesiętne, jeśli nie potrzebujesz separatorów dziesiętnych, użyj tej składni, aby usunąć miejsca dziesiętne:

   "SELECT ROUND([Decimal_column_name];0)... .”

   Jeśli potrzebujesz groszy, ale nie ułamków groszy, zamień 0 na 2. Liczby ujemne można zaokrąglać do jednostek, dziesiątek, setek itp.

2. Jeśli masz kolumnę Datetime o nazwie dbo. Bigtable. [Godzina daty] i część Godzina nie jest potrzebna, użyj składni, aby usunąć czas:

   "SELECT CAST (dbo. Bigtable. [Date time] as date) AS [Date time]) "

3. Jeśli masz kolumnę Datetime o nazwie dbo. Bigtable. [Data/godzina] i potrzebujesz części Data i godzina, użyj w zapytaniu daty SQL kolumny zamiast pojedynczej kolumny datetime:

   "SELECT CAST (dbo. Bigtable. [Date Time] as date ) AS [Date Time],

   datepart(hh; dbo. Bigtable. [Data i godzina]) jako [godziny daty],

   datepart(mi, dbo. Bigtable. [Data i godzina]) jako [Minuta daty],

   datepart(ss, dbo. Bigtable. [Data i godzina]) jako [Sekundy daty],

   datepart(ms, dbo. Bigtable. [Data i godzina]) jako [Czas daty (w milisekundach]"

   Należy użyć tylu kolumn, ile trzeba przechowywać w osobnych kolumnach.

4. Jeśli potrzebujesz godzin i minut i preferujesz je razem jako kolumnę czasową, możesz użyć składni:

   Timefromparts(datepart(hh, dbo. Bigtable. [Date Time]), datepart(mm, dbo. Bigtable. [Data i godzina])) as [Date Time HourMinute]

5. Jeśli masz dwie kolumny daty/godziny, takie jak [Godzina rozpoczęcia] i [Godzina zakończenia], a naprawdę potrzebujesz różnicy czasu (w sekundach) jako kolumny o nazwie [Czas trwania], usuń obie kolumny z listy i dodaj:

   "datediff(ss;[Start Date],[End Date]) as [Duration]"

   Jeśli użyjesz słowa kluczowego ms zamiast ss, czas trwania zostanie określony w milisekundach.

Które 2 kolumny należy zachować?

W powyższym przykładzie zachowaj wartości Ilość i Cena jednostkowa. Te dwie wartości są mniejsze niż suma. Aby obliczyć sumę, dodaj miarę obliczeniową, na przykład:

"SprzedażSprzedażSprzedaż:=sumx('Tabela sprzedaży';'Tabela sprzedaży'[Cena jednostkowa]*'Tabela sprzedaży'[Ilość])"

Kolumny obliczeniowe są jak zwykłe kolumny, w których obie zająć miejsce w modelu. Z kolei miary obliczane są obliczane na bieżąco i nie są w nich odstępy.