W programie Excel można tworzyć modele danych zawierające miliony wierszy, a następnie wykonywać zaawansowane analizy danych na tych modelach. 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 tym samym skoroszycie.
Chociaż w programie Excel można łatwo tworzyć ogromne modele danych, istnieje kilka powodów, aby tego nie robić. Po pierwsze, duże modele zawierające wiele tabel i kolumn są przesadą dla większości analiz i sprawiają, że jest to uciążliwa lista pól. Po drugie, duże modele zużywają cenną pamięć, co negatywnie wpływa na inne aplikacje i raporty, które mają te same zasoby systemowe. Na koniec w 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 skoroszytu zawierających miliony wierszy dość szybko zostanie osiągnięty 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 pracy i zużywa mniej pamięci. Poświęcenie czasu na zapoznanie się z najlepszymi rozwiązaniami w zakresie efektywnego projektowania modeli opłaci się 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
Współczynniki kompresji i aparat analizy w pamięci
Modele danych w programie Excel używają aparatu analizy 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, aż osiągnie ułamek oryginalnego rozmiaru.
Można oczekiwać, że model danych będzie 7-10 razy mniejszy niż te same dane w punkcie pochodzenia. Jeśli na przykład importujesz 7 MB danych z bazy danych programu SQL Server, model danych w programie Excel może z łatwością mieć co najmniej 1 MB. Stopień kompresji rzeczywiście osiągnięte 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 unikatowych wartościach? Ponieważ tworzenie wydajnego modelu, który minimalizuje zużycie pamięci, polega na maksymalizacji kompresji, a najprostszym sposobem jest pozbycie się kolumn, których tak naprawdę nie potrzebujesz, zwłaszcza jeśli te kolumny zawierają dużą liczbę unikatowych wartości.
Uwaga: Różnice w wymaganiach dotyczących przestrzeni dyskowej dla poszczególnych kolumn mogą być ogromne. W niektórych przypadkach lepiej mieć wiele kolumn z małą liczbą unikatowych wartości, a nie jedną kolumnę z dużą liczbą unikatowych wartości. Sekcja optymalizacji datetime szczegółowo opisuje tę technikę.
W przypadku niskiego zużycia pamięci nic nie przebije nieistniejącej kolumny
Kolumna o największej wydajności pamięci to ta, która nigdy nie została zaimportowana. Jeśli chcesz utworzyć wydajny model, przyjrzyj się każdej kolumnie i zadaj sobie pytanie, czy przyczynia się on do analizy, którą chcesz wykonać. Jeśli nie jest lub nie masz pewności, pozostaw to na zewnątrz. Zawsze możesz dodać nowe kolumny później, jeśli są potrzebne.
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.
Uwaga: W magazynach 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 zwykle zawierają dane dotyczące wydajności biznesowej lub transakcji, takie jak punkty danych sprzedaży i kosztów, które są agregowane i wyrównane do jednostek organizacyjnych, produktów, segmentów rynku, regionów geograficznych itd. Wszystkie kolumny w tabeli faktów, które zawierają dane biznesowe lub które mogą służyć do odsyłania danych przechowywanych w innych tabelach, powinny zostać uwzględnione w modelu w celu obsługi analizy danych. Kolumna, którą chcesz wykluczyć, jest kolumną 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ą tak ogromne, niektóre z największych wzrost wydajności modelu pochodzą z wykluczenia wierszy lub kolumn z tabel faktów.
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.
A co z filtrowaniem tylko potrzebnych wierszy?
Wiele tabel w firmowych bazach danych i magazynach danych zawiera dane historyczne gromadzone przez długi czas. Ponadto może się okazać, że tabele, które Cię interesują, 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, a tym samym zaoszczędzić dużo miejsca w modelu. Na poniższej ilustracji filtr daty służy do pobierania tylko wierszy zawierających dane z bieżącego roku, z wyłączeniem danych historycznych, które nie będą potrzebne.
Co zrobić, jeśli potrzebujemy kolumny; czy nadal możemy obniżyć koszty przestrzeni kosmicznej?
Istnieje kilka dodatkowych technik, które można zastosować, aby kolumna była lepszym kandydatem do kompresji. Pamiętaj, że jedyną cechą kolumny, która wpływa na kompresję, jest liczba unikatowych wartości. W tej sekcji dowiesz się, jak można modyfikować niektóre kolumny w celu zmniejszenia liczby unikatowych wartości.
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? protokół? 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 zapytań.
W Edytorze zapytań 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:
|
Natomiast jeśli zaimportowano tabelę w całości, bez wyczyszczania zaznaczenia żadnej kolumny ani stosowania filtru, zapytanie będzie wyświetlane jako "Wybierz * z", co będzie trudniejsze do zmodyfikowania:
|
Modyfikowanie zapytania SQL
Teraz, gdy już wiesz, jak znaleźć zapytanie, możesz je zmodyfikować w celu dalszego zmniejszenia rozmiaru modelu.
-
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.
-
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]) "
-
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.
-
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]
-
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
Używanie miar obliczeniowych języka DAX zamiast kolumn
Jeśli język wyrażeń języka DAX był już wcześniej używany, być może wiesz już, że kolumny obliczeniowe są używane do tworzenia nowych kolumn na podstawie innej kolumny w modelu, podczas gdy miary obliczeniowe są definiowane raz w modelu, ale są obliczane tylko wtedy, gdy są używane w tabeli przestawnej lub innym raporcie.
Jedną z technik zapisywania pamięci jest zamiana kolumn regularnych lub obliczeniowych na miary obliczeniowe. Klasyczny przykład to Cena jednostkowa, Ilość i Suma. Jeśli masz wszystkie trzy, możesz zaoszczędzić miejsce, zachowując tylko dwa i obliczając trzeci za pomocą języka DAX.
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.
Wnioski
W tym artykule omówiliśmy kilka sposobów, które mogą pomóc w opracowaniu bardziej wydajnego modelu zapewniającego wydajną obsługę pamięci. Sposobem na zmniejszenie wymagań dotyczących rozmiaru pliku i pamięci modelu danych jest zmniejszenie ogólnej liczby kolumn i wierszy oraz liczby unikatowych wartości wyświetlanych w każdej kolumnie. Oto kilka technik, które omówiliśmy:
-
Usuwanie kolumn to oczywiście najlepszy sposób na zaoszczędzenie miejsca. Zdecyduj, które kolumny są naprawdę potrzebne.
-
Czasami można usunąć kolumnę i zamienić ją na miarę obliczeniową w tabeli.
-
Być może nie potrzebujesz wszystkich wierszy w tabeli. Wiersze można odfiltrować w Kreatorze importu tabeli.
-
Ogólnie rzecz biorąc, dzielenie jednej kolumny na wiele odrębnych części to dobry sposób na zmniejszenie liczby unikatowych wartości w kolumnie. Każda z tych części będzie miała niewielką liczbę unikatowych wartości, a łączna suma będzie mniejsza niż oryginalna ujednolicona kolumna.
-
W wielu przypadkach do użycia jako fragmentatorów w raportach są również potrzebne poszczególne części. W razie potrzeby można utworzyć hierarchie na podstawie części, takich jak Godziny, Minuty i Sekundy.
-
Wiele razy kolumny zawierają więcej informacji, niż jest to potrzebne. Załóżmy na przykład, że w kolumnie są przechowywane miejsca dziesiętne, ale zastosowano formatowanie, aby ukryć wszystkie miejsca dziesiętne. Zaokrąglanie może być bardzo skuteczne w zmniejszaniu rozmiaru kolumny liczbowej.
Po wykonaniu odpowiednich 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
