Twórca modelu danych zapewniającego wydajną obsługę pamięci przy użyciu programu Excel i dodatku Power Pivot

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

Pierwszy przykład dotyczy danych pochodzących z magazynu danych. W magazynie danych często znajdują się artefakty procesów ETL, które ładują i odświeżają dane w magazynie. Kolumny, takie jak "create date", "update date" i "ETL run" są tworzone po załadowaniu danych. Żadna z tych kolumn nie jest potrzebna w modelu i należy usunąć zaznaczenie podczas importowania danych.

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

Wiele tabel, w tym tabel faktów, ma klucze podstawowe. W przypadku większości tabel, takich jak tabele zawierające dane klientów, pracowników lub sprzedaży, klucz podstawowy tabeli ma służyć do tworzenia relacji w modelu.

Tabele faktów są różne. W tabeli faktów klucz podstawowy służy do unikatowej identyfikacji każdego wiersza. Chociaż jest to konieczne do celów normalizacji, jest mniej przydatne w modelu danych, w którym mają być używane tylko te kolumny do analizy lub do ustanawiania relacji między tabelami. Z tego powodu podczas importowania z tabeli faktów nie dołączaj jej klucza podstawowego. Klucze podstawowe w tabeli faktów zajmują ogromne ilości miejsca w modelu, ale nie zapewniają żadnych korzyści, ponieważ nie mogą być używane do tworzenia relacji.

Jak wykluczyć niepotrzebne kolumny

Wydajne modele zawierają tylko te kolumny, których rzeczywiście potrzebujesz w skoroszycie. Jeśli chcesz określić, które kolumny są zawarte w modelu, musisz użyć Kreatora importu tabeli w dodatku Power Pivot, aby zaimportować dane , a nie za pomocą okna dialogowego Importowanie danych w programie Excel.

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

W przypadku każdej tabeli możesz kliknąć przycisk Podgląd & Filtr i wybrać potrzebne części tabeli. Zalecamy, aby najpierw usunąć zaznaczenie wszystkich kolumn, a następnie przejść do sprawdzania odpowiednich kolumn po rozważeniu, czy są one wymagane do analizy.

Modyfikowanie kolumn datetime

W wielu przypadkach kolumny datetime zajmują dużo miejsca. Na szczęście istnieje wiele sposobów, aby zmniejszyć wymagania dotyczące przestrzeni dyskowej dla tego typu danych. Techniki różnią się w zależności od sposobu używania kolumny i poziomu komfortu podczas tworzenia zapytań SQL.

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

• Czy potrzebuję części czasu?

• Czy potrzebuję części czasu na poziomie godzin? Minut? Sekund? Milisekund?

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

Sposób odpowiadania na każde z tych pytań określa opcje postępowania z kolumną Datetime.

Wszystkie te rozwiązania wymagają modyfikacji zapytania SQL. Aby ułatwić modyfikowanie zapytań, należy odfiltrować co najmniej jedną kolumnę w każdej tabeli. Odfiltrowanie kolumny powoduje zmianę konstrukcji zapytania ze skróconego formatu (SELECT *) na instrukcję SELECT zawierającą w pełni kwalifikowane nazwy kolumn, które są znacznie łatwiejsze do zmodyfikowania.

Przyjrzyjmy się utworzonym zapytaniam. 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 obszarze Właściwości tabeli wybierz pozycję Edytor Power Query.

W Edytor Power Query jest wyświetlane zapytanie SQL służące do wypełniania tabeli. Jeśli podczas importowania odfiltrowano dowolną kolumnę, zapytanie zawiera w pełni kwalifikowane nazwy kolumn:

Modyfikowanie zapytania SQL

Teraz, gdy już wiesz, jak znaleźć zapytanie, możesz je zmodyfikować w celu dalszego zmniejszenia rozmiaru modelu.

1. W przypadku kolumn zawierających dane walutowe lub dziesiętne, jeśli nie potrzebujesz miejsc dziesiętnych, użyj tej składni, aby pozbyć się miejsc dziesiętnych:

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

   Jeśli potrzebujesz centów, ale nie ułamków centów, wymień 0 na 2. Jeśli używasz liczb ujemnych, możesz zaokrąglić do jednostek, dziesiątek, setek itd.

2. Jeśli masz kolumnę Datetime o nazwie dbo. Bigtable. [Data godzina] i nie potrzebujesz części Godzina, użyj składni, aby pozbyć się godziny:

   "SELECT CAST (dbo. Bigtable. [Data godzina] jako data) AS [Data godzina]) "

3. Jeśli masz kolumnę Datetime o nazwie dbo. Bigtable. [Date Time] i potrzebujesz zarówno części daty, jak i godziny, użyj wielu kolumn w zapytaniu SQL zamiast pojedynczej kolumny Datetime:

   "SELECT CAST (dbo. Bigtable. [Data godzina] jako data ) AS [Data godzina],

   datepart(hh, dbo. Bigtable. [Data godziny]) jako [Godziny daty],

   datepart(mi, dbo. Bigtable. [Data godziny]) as [Date Time Minutes],

   datepart(ss, dbo. Bigtable. [Data godziny]) as [Date Time Seconds],

   datepart(ms, dbo. Bigtable. [Data godziny]) as [Date Time Milliseconds]"

   Użyj dowolnej liczby kolumn, aby przechowywać poszczególne części w osobnych kolumnach.

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

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

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

   "datediff(ss,[Data rozpoczęcia],[Data zakończenia]) as [Duration]"

   Jeśli użyjesz słowa kluczowego ms zamiast ss, otrzymasz czas trwania w milisekundach

Które 2 kolumny należy zachować?

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

"TotalSales:=sumx('Sales Table','Sales Table'[Unit Price]*'Sales Table'[Quantity])"

Kolumny obliczeniowe przypominają zwykłe kolumny, ponieważ zajmują miejsce w modelu. W przeciwieństwie do tego obliczone miary są obliczane na bieżąco i nie zajmują miejsca.