W programie Excel można tworzyć modele danych zawierające miliony wierszy, a następnie wykonywać zaawansowane analizy danych na podstawie tych modeli. Modele danych można tworzyć z dodatkiem Power Pivot lub bez niego, aby obsługiwać dowolną liczbę tabel przestawnych, wykresów i wizualizacji programu Power View w jednym skoroszycie.
Mimo że w programie Excel można łatwo tworzyć modele ogromnych danych, istnieje kilka powodów, dla których warto tego nie robić. Po pierwsze, duże modele, które zawierają wiele tabel i kolumn, są przesadą w przypadku większości analiz i powodują kłopotliwą listę pól. Po drugie, duże modele zużywają cenną pamięć, co negatywnie wpływa na inne aplikacje i raporty, które współdzielą te same zasoby systemowe. Ponadto na platformie Microsoft 365 zarówno usługa SharePoint Online, jak i aplikacja Excel Web App ograniczają rozmiar pliku programu Excel do 10 MB. W przypadku modeli danych skoroszytów, które zawierają miliony wierszy, dość szybko osiągniesz limit 10 MB. Zobacz Specyfikacja i limity modelu danych.
W tym artykule dowiesz się, jak utworzyć ściśle skonstruowany model, który jest łatwiejszy w obsłudze i zużywa mniej pamięci. Poświęcenie czasu na zapoznanie się z najlepszymi rozwiązaniami w zakresie wydajnego projektowania modeli opłaci się w przyszłości w przypadku każdego modelu, który tworzysz i używasz, niezależnie od tego, czy wyświetlasz go w programie Excel, Microsoft 365 SharePoint Online, na serwerze Office Web Apps, czy w programie SharePoint.
Rozważ również możliwość uruchomienia optymalizatora rozmiaru skoroszytu. Umożliwia on przeanalizowanie skoroszytu programu Excel i, jeśli to możliwe, dalsze jego skompresowanie. Pobierz optymalizator rozmiaru skoroszytu.
W tym artykule
Nic nie przebije nieistniejącej kolumny pod względem niskiego użycia pamięci
A co, jeśli potrzebujemy kolumny; Czy nadal możemy zmniejszyć jego koszt przestrzeni?
Współczynniki kompresji i aparat analizy w pamięci
Modele danych w programie Excel używają aparatu analizy w pamięci do przechowywania danych w pamięci. Aparat implementuje zaawansowane techniki kompresji w celu zmniejszenia wymagań dotyczących pamięci masowej, zmniejszając zestaw wyników do momentu uzyskania ułamka oryginalnego rozmiaru.
Można oczekiwać, że model danych będzie średnio od 7 do 10 razy mniejszy niż te same dane w punkcie pochodzenia. Na przykład w przypadku importowania 7 MB danych z bazy danych programu SQL Server model danych w programie Excel może mieć rozmiar nie większy niż 1 MB. Faktycznie osiągnięty stopień kompresji zależy przede wszystkim od liczby unikatowych wartości w każdej kolumnie. Im więcej unikatowych wartości, tym więcej pamięci jest wymagane do ich przechowywania.
Dlaczego mówimy o kompresji i unikalnych wartościach? Ponieważ zbudowanie wydajnego modelu minimalizującego użycie pamięci polega na maksymalizacji kompresji, a najłatwiejszym sposobem na to jest pozbycie się wszystkich kolumn, które w rzeczywistości nie są potrzebne, zwłaszcza jeśli kolumny te zawierają dużą liczbę unikatowych wartości.
Uwaga
Różnice w wymaganiach dotyczących przechowywania poszczególnych kolumn mogą być ogromne. W niektórych przypadkach lepiej jest mieć wiele kolumn z małą liczbą unikatowych wartości niż jedną kolumnę z dużą liczbą unikatowych wartości. Sekcja dotycząca optymalizacji DateTime szczegółowo opisuje tę technikę.
Nic nie przebije nieistniejącej kolumny pod względem niskiego użycia pamięci
Najbardziej efektywna pod względem pamięci kolumna to ta, która nigdy nie została zaimportowana. Jeśli chcesz zbudować wydajny model, przyjrzyj się każdej kolumnie i zadaj sobie pytanie, czy przyczynia się ona do analizy, którą chcesz wykonać. Jeśli tak nie jest lub nie masz pewności, pomiń go. Zawsze możesz dodać nowe kolumny później, jeśli będą potrzebne.
Dwa przykłady kolumn, które powinny być zawsze wykluczane
Pierwszy przykład dotyczy danych pochodzących z hurtowni danych. W hurtowni danych często można znaleźć artefakty procesów ETL, które ładują i odświeżają dane w hurtowni. Kolumny takie jak "data utworzenia", "data aktualizacji" i "uruchomienie ETL" są tworzone podczas ładowania danych. Żadna z tych kolumn nie jest potrzebna w modelu i należy usunąć zaznaczenie podczas importowania danych.
W drugim przykładzie kolumna klucza podstawowego została pominięta podczas importowania tabeli faktów.
Wiele tabel, w tym tabele faktów, ma klucze podstawowe. W przypadku większości tabel, na przykład danych dotyczących klientów, pracowników lub sprzedaży, klucz podstawowy tabeli jest niezbędny do tworzenia relacji w modelu.
Tabele faktów są inne. W tabeli faktów klucz podstawowy służy do unikatowego identyfikowania każdego wiersza. Chociaż jest to konieczne na potrzeby normalizacji, jest mniej przydatne w modelu danych, w którym do analizy lub ustanawiania relacji między tabelami mają być używane tylko te kolumny. Z tego powodu podczas importowania z tabeli faktów nie podawaj jej klucza podstawowego. Klucze podstawowe w tabeli faktów zajmują ogromne ilości miejsca w modelu, ale nie przynoszą żadnych korzyści, ponieważ nie można ich używać do tworzenia relacji.
Uwaga
W hurtowniach danych i wielowymiarowych bazach danych duże tabele składające się głównie z danych liczbowych są często nazywane "tabelami faktów". Tabele faktów zazwyczaj zawierają dane dotyczące wydajności biznesowej lub transakcji, takie jak punkty danych sprzedaży i kosztów, które są agregowane i dopasowane do jednostek organizacyjnych, produktów, segmentów rynku, regionów geograficznych itp. Wszystkie kolumny tabeli faktów, które zawierają dane biznesowe lub które mogą być używane do tworzenia odsyłaczy do danych przechowywanych w innych tabelach, powinny zostać uwzględnione w modelu w celu obsługi analizy danych. Kolumna, którą chcesz wykluczyć, to kolumna klucza podstawowego tabeli faktów, która składa się z unikatowych wartości istniejących tylko w tabeli faktów i nigdzie indziej. Ponieważ tabele faktów są ogromne, niektóre z największych korzyści w zakresie wydajności modelu wynikają z wykluczenia wierszy lub kolumn z tabel faktów.
Jak wykluczyć niepotrzebne kolumny
Wydajne modele zawierają tylko te kolumny, które będą rzeczywiście potrzebne w skoroszycie. Jeśli chcesz określić, które kolumny są uwzględniane w modelu, musisz użyć do zaimportowania danych Kreatora importu tabel w dodatku Power Pivot, a nie okna dialogowego "Importowanie danych" jak w programie Excel.
Po uruchomieniu Kreatora importu tabeli wybierz tabele do zaimportowania.
W przypadku każdej tabeli możesz kliknąć przycisk Podgląd & filtru, a następnie wybrać te części tabeli, które są naprawdę potrzebne. Zalecamy, aby najpierw wyczyścić zaznaczenie wszystkich kolumn, a następnie przystąpić do sprawdzania kolumn, które są potrzebne do analizy, po określeniu, czy są one wymagane do analizy.
A co z filtrowaniem tylko potrzebnych wierszy?
Wiele tabel w firmowych bazach danych i hurtowniach danych zawiera dane historyczne gromadzone przez długi czas. Ponadto może się okazać, że interesujące Cię tabele zawierają informacje dotyczące obszarów działalności, które nie są wymagane do określonej analizy.
Za pomocą Kreatora importu tabeli można odfiltrować dane historyczne lub niepowiązane, oszczędzając w ten sposób dużo miejsca w modelu. Na poniższej ilustracji filtr daty służy do pobierania tylko tych wierszy, które zawierają dane dla bieżącego roku, z wyłączeniem danych historycznych, które nie będą potrzebne.
A co, jeśli potrzebujemy kolumny; Czy nadal możemy zmniejszyć jego koszt przestrzeni?
Istnieje kilka dodatkowych technik, które można zastosować, aby zwiększyć podatność kolumn na kompresję. Należy pamiętać, że jedyną cechą kolumny wpływającą na kompresję jest liczba unikatowych wartości. W tej sekcji dowiesz się, jak można modyfikować niektóre kolumny, aby zmniejszyć liczbę unikatowych wartości.
Modyfikowanie kolumn daty/godziny
W wielu przypadkach kolumny typu Data/godzina zajmują dużo miejsca. Na szczęście istnieje wiele sposobów zmniejszania wymagań dotyczących pamięci masowej dla tego typu danych. Stosowane techniki zależą od sposobu używania kolumny oraz poziomu komfortu tworzenia zapytań SQL.
Kolumny typu data/godzina zawierają część daty i godzinę. Gdy zadajesz sobie pytanie, czy potrzebujesz kolumny, zadaj to samo pytanie wiele razy dla kolumny Data/godzina:
- Czy potrzebuję części czasowej?
- Czy potrzebuję części czasowej na poziomie godzin? , minuty? , sekundy? , milisekundy?
- Czy mam wiele kolumn Data/godzina, ponieważ chcę obliczyć różnicę między nimi, czy po prostu zagregować dane według roku, miesiąca, kwartału i tak dalej.
Sposób udzielania odpowiedzi na każde z tych pytań określa opcje obsługi kolumny Data/godzina.
Wszystkie te rozwiązania wymagają modyfikacji zapytania SQL. Aby ułatwić modyfikowanie zapytań, należy odfiltrować co najmniej jedną kolumnę w każdej tabeli. Odfiltrowując kolumnę, można zmienić konstrukcję zapytania ze skróconego formatu (SELECT *) na instrukcję SELECT, która zawiera w pełni kwalifikowane nazwy kolumn, które są znacznie łatwiejsze do modyfikowania.
Przyjrzyjmy się kwerendom, które zostały utworzone dla Ciebie. W oknie dialogowym Właściwości tabeli można przejść do edytora Zapytania i wyświetlić bieżące zapytanie SQL dla każdej tabeli.
W oknie Właściwości tabeli wybierz pozycję Edytor Power Query.
Edytor Power Query wyświetla zapytanie SQL użyte do wypełnienia tabeli. Jeśli podczas importowania odfiltrowano którąkolwiek kolumnę, zapytanie uwzględni w pełni kwalifikowane nazwy kolumn:
Natomiast jeśli zaimportowano tabelę w całości, bez usuwania zaznaczenia żadnej kolumny ani stosowania żadnego filtru, zapytanie będzie miało postać "Wybierz * z", co będzie trudniejsze do zmodyfikowania:
|
|---|
Modyfikowanie kwerendy SQL
Znając już sposób znalezienia zapytania, można je zmodyfikować w celu dalszego zmniejszenia rozmiaru modelu.
- Jeśli separatory dziesiętne nie są potrzebne w przypadku kolumn zawierających walutę lub dane dziesiętne, użyj następującej składni, aby usunąć miejsca dziesiętne:
"SELECT ROUND([Decimal_column_name];0)... .”
Jeśli potrzebne są grosze, ale nie ułamki centów, należy zamienić cyfrę 0 na cyfrę 2. Liczby ujemne można zaokrąglać do jednostek, dziesiątek, setek itp. - Jeśli masz kolumnę typu Data/godzina o nazwie dbo. Duży stół. [Data i godzina] i nie potrzebujesz części Godzina, użyj składni, aby pozbyć się godziny:
"SELECT CAST (dbo. Duży stół. [Data godzina] as date) AS [Data godzina]) " - Jeśli masz kolumnę typu Data/godzina o nazwie dbo. Duży stół. [Data i godzina] i potrzebujesz zarówno części Date, jak i Time, użyj wielu kolumn w zapytaniu SQL zamiast pojedynczej kolumny Datetime:
"SELECT CAST (dbo. Duży stół. [Data/godzina] as data) AS [data/godzina],
DatePart(gg, dbo. Duży stół. [Data/godzina]) jako [Data/godzina, godziny],
datepart(mi, dbo. Duży stół. [Data/godzina]) jako [Data Godzina Minuty],
datepart(ss, dbo. Duży stół. [Data/godzina]) jako [Data, Godzina, Sekunda],
datepart(ms, dbo. Duży stół. [Data/godzina]) as [data, godzina, milisekundy]"
Każda część powinna być przechowywana w osobnych kolumnach w dowolnym miejscu. - Jeśli potrzebujesz godzin i minut, które wolisz mieć razem w jednej kolumnie czasu, możesz użyć składni:
Timefromparts(datepart(hh, dbo. Duży stół. [Data i godzina]), datepart(mm, dbo. Duży stół. [Data/godzina])) as [Data, Godzina, Godzina/minuta] - Jeśli masz dwie kolumny z datą i godziną, takie jak [Godzina rozpoczęcia] i [Godzina zakończenia], i tak naprawdę potrzebujesz różnicy czasu między nimi w sekundach w postaci kolumny o nazwie [Czas trwania], usuń obie kolumny z listy i dodaj:
"datediff(ss,[Data rozpoczęcia],[Data zakończenia]) as [Czas trwania]"
Jeśli użyjesz słowa kluczowego ms zamiast ss, uzyskasz czas trwania w milisekundach
Używanie miar obliczeniowych języka DAX zamiast kolumn
Jeśli masz już doświadczenie z językiem wyrażeń języka DAX, być może wiesz już, że kolumny obliczeniowe służą do tworzenia nowych kolumn na podstawie innej kolumny w modelu, podczas gdy miary obliczeniowe są definiowane raz w modelu, ale oceniane tylko wtedy, gdy są używane w tabeli przestawnej lub innym raporcie.
Jedną z metod oszczędzania pamięci jest zastąpienie zwykłych lub obliczeniowych kolumn miarami obliczeniowymi. Klasycznym przykładem są Cena jednostkowa, Ilość i Suma. Jeśli masz wszystkie trzy, możesz zaoszczędzić miejsce, zostawiając tylko dwa i obliczając trzecią przy użyciu języka DAX.
Które 2 kolumny należy zachować?
W powyższym przykładzie zachowaj ilości i cenę jednostkową. Te dwa mają mniej wartości niż wartość w kolumnie Suma. Aby obliczyć sumę, dodaj miarę obliczeniową, taką jak:
"TotalSales:=sumx('Sales Table','Sales Table'[Unit Price]*'Sales Table'[Quantity])"
Kolumny obliczeniowe przypominają zwykłe kolumny w tym sensie, że obie zajmują miejsce w modelu. W przeciwieństwie do tego, miary obliczeniowe są obliczane na bieżąco i nie zajmują miejsca.
Wnioski
W tym artykule omówiliśmy kilka podejść, które mogą pomóc w zbudowaniu modelu zapewniającego większą wydajność pamięci. Sposobem na zmniejszenie rozmiaru pliku i wymagań dotyczących pamięci w modelu danych jest zmniejszenie ogólnej liczby kolumn i wierszy oraz liczby unikatowych wartości wyświetlanych w każdej kolumnie. Oto kilka omówionych technik:
- Usunięcie kolumn to oczywiście najlepszy sposób na zaoszczędzenie miejsca. Zdecyduj, które kolumny są naprawdę potrzebne.
- Czasami można usunąć kolumnę i zastąpić ją miarą obliczeniową w tabeli.
- Być może nie wszystkie wiersze w tabeli będą potrzebne. Wiersze można odfiltrować w Kreatorze importu tabeli.
- Ogólnie rzecz biorąc, rozdzielenie jednej kolumny na wiele odrębnych części jest dobrym sposobem na zmniejszenie liczby unikatowych wartości w kolumnie. Każda z części będzie miała niewielką liczbę unikatowych wartości, a łączna suma będzie mniejsza niż oryginalna ujednolicona kolumna.
- W wielu przypadkach są również potrzebne wydzielone części do użycia jako fragmentatory w raportach. W razie potrzeby można utworzyć hierarchie na podstawie takich części jak godziny, minuty i sekundy.
- Często kolumny zawierają więcej informacji, niż jest potrzebnych. Załóżmy na przykład, że w kolumnie są przechowywane miejsca dziesiętne, ale zastosowano formatowanie w celu ukrycia wszystkich miejsc dziesiętnych. Zaokrąglanie może być bardzo skuteczne przy zmniejszaniu rozmiaru kolumny liczbowej.
Po zrobieniu wszystkich niezbędnych czynności w celu zmniejszenia rozmiaru skoroszytu rozważ również uruchomienie optymalizatora rozmiaru skoroszytu. Umożliwia on przeanalizowanie skoroszytu programu Excel i, jeśli to możliwe, dalsze jego skompresowanie. Pobierz optymalizator rozmiaru skoroszytu.
Linki pokrewne
Specyfikacja i limity modelu danych
Optymalizator rozmiaru skoroszytu
Dodatek Power Pivot: zaawansowane analizy i modelowanie danych w programie Excel