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

W Excel 2013 lub nowszym można tworzyć modele danych zawierające miliony wierszy, a następnie wykonywać zaawansowaną analizę danych w stosunku do tych modeli. Modele danych można tworzyć za pomocą dodatku Power Pivot lub bez niego, aby obsługiwać dowolną liczbę tabel przestawnych, wykresów i Power View wizualizacji w tym samym skoroszycie.

Uwaga: Ten artykuł zawiera opis modeli danych w programie Excel 2013. Jednak taki sam sposób modelowania danych i funkcje dodatku Power Pivot wprowadzono w programie Excel 2013 i mają one również zastosowanie w programie Excel 2016. W praktyce nie ma niewielkiej różnicy między tymi wersjami Excel.

Chociaż w aplikacji Excel można łatwo tworzyć olbrzymie Excel danych, jest kilka powodów, dla których tego nie ma. Po pierwsze duże modele zawierające bardzo duże tabele i kolumny są uciążliwe podczas większości analiz, a ich wyniki są kłopotliwe. Po drugie duże modele używają cennej pamięci, co ma negatywny wpływ na inne aplikacje i raporty współużytkujące te same zasoby systemowe. Na koniec w aplikacji Microsoft 365 rozmiar pliku SharePoint Online i aplikacja Excel Web App ograniczają rozmiar pliku Excel do 10 MB. W przypadku modeli danych skoroszytu zawierających miliony wierszy limit 10 MB będzie dość szybko dobiegł do granicy 10 MB. Zobacz Specyfikacja i ograniczenia modelu danych.

W tym artykule dowiesz się, jak utworzyć model o mocno skonstruowanym modelu, w którym praca jest łatwiejsza i zużywa mniej pamięci. Wykorzystanie czasu na naukę najlepszych rozwiązań w zakresie wydajnego projektowania modeli będzie opłacać każdy model, który utworzysz i z niego korzystasz, niezależnie od tego, czy wyświetlasz go w programie Excel 2013, Microsoft 365 SharePoint Online, na serwerze Office Web Apps Server czy w programie SharePoint 2013.

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 Excel aparatu analizy pamięci do przechowywania danych w pamięci. Aparat implementuje zaawansowane techniki kompresji w celu zmniejszenia wymagań dotyczących magazynowania, zmniejszając zestaw wyników do ułamka pierwotnego rozmiaru.

Można oczekiwać, że model danych będzie od 7 do 10 razy mniejszy od tych samych danych 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 mieć rozmiar nie więcej niż 1 MB. Stopień kompresji w rzeczywistości uzyskany zależy przede wszystkim od liczby unikatowych wartości w każdej kolumnie. Im bardziej unikatowe 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, zależy przede wszystkim od maksymalizowania kompresji, a najprostszym sposobem jest pozbywanie się kolumn, które nie są naprawdę potrzebne, szczególnie wtedy, gdy te kolumny zawierają wiele unikatowych wartości.

Uwaga:  Różnice w wymaganiach dotyczących przestrzeni dyskowej dla poszczególnych kolumn mogą być duże. W niektórych przypadkach lepiej jest mieć wiele kolumn z niską liczbą unikatowych wartości zamiast jednej kolumny z dużą liczbą unikatowych wartości. W sekcji dotyczącej optymalizacji daty/czasu szczegółowo opisano tę technikę.

Nic nie jest przeoczysz nieistniejące kolumny, aby uzyskać niskie zużycie pamięci

Najbardziej wydajna w pamięci jest kolumna, która nigdy nie była importowana. Jeśli chcesz utworzyć skuteczny model, przyjrzyj się każdej kolumnie i zapytaj, czy ma ona wpływ na analizę, którą chcesz przeprowadzić. Jeśli tego nie ma lub nie masz pewności, pozostaw to. Zawsze możesz dodać nowe kolumny później, jeśli będą potrzebne.

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

Pierwszy przykład dotyczy danych pochodzących z magazynu danych. W magazynie danych typowe jest znajdowanie artefaktów 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 fakty 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 fakty nie uwzględniaj 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.

Uwaga:  W magazynach danych i wielowymiarowych bazach danych duże tabele składające się z głównie 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 zgodne z jednostkami organizacji, produktami, segmentami rynku, regionami geograficznymi itp. Model powinien zawierać wszystkie kolumny w tabeli faktów, które zawierają dane biznesowe lub których można użyć do odsyłania danych przechowywanych w innych tabelach w celu obsługi analizy danych. Kolumna, którą chcesz wykluczyć, to kolumna klucza podstawowego tabeli fakt, która składa się z unikatowych wartości istniejących tylko w tabeli fakt i nigdzie indziej. Tabele faktów są bardzo duże, dlatego niektóre z największych zysków w zakresie wydajności modelu są wynikające z wykluczania wierszy lub kolumn z tabel faktów.

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 za pomocą Kreatora importu tabeli w dodatku Power Pivot, a nie okna dialogowego "Importowanie danych" w programie Excel.

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

Kreator importu tabeli w dodatku PowerPivot

Dla każdej tabeli możesz kliknąć przycisk Podgląd & 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.

Okienko podglądu w Kreatorze importu tabeli

Co z filtrowaniem tylko potrzebnych wierszy?

Wiele tabel w firmowych bazach danych i magazynach danych zawiera dane historyczne skumulowane 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 danej analizy.

Kreator importu tabeli umożliwia filtrowanie danych historycznych lub niepowiązanych, dzięki czemu można zaoszczędzić sporo miejsca w modelu. Na poniższej ilustracji filtr daty służy do pobierania tylko wierszy zawierających dane z bieżącego roku, z wyjątkiem danych historycznych, które nie będą potrzebne.

Okienko filtrowania w Kreatorze importu tabel

Co zrobić, jeśli jest potrzebna kolumna; czy nadal możemy zmniejszyć koszt jego przestrzeni?

Istnieje kilka dodatkowych technik, które można zastosować, aby kolumna lepiej nadała się do kompresji. Pamiętaj, że jedynym cechy kolumny 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 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 poszczególnych tabel.

Wstążka w oknie programu PowerPivot z wyświetlonym poleceniem Właściwości tabeli

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

Otwieranie Edytora zapytań w oknie Właściwości tabeli

Edytor zapytań wyświetla zapytanie SQL służące do wypełniania tabeli. Jeśli podczas importowania odfiltrujesz dowolną kolumnę, zapytanie zawiera w pełni kwalifikowane nazwy kolumn:

Kwerenda SQL używana do pobierania danych

Natomiast jeśli zaimportowano tabelę do całości, bez konieczności odznaczania żadnej kolumny czy stosowania jakichkolwiek filtrów, zapytanie będzie mieć momencie "Wybierz * z ", co będzie trudniejsze do zmodyfikowania:

Kwerenda SQL w domyślnej, krótszej składni

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ąć separatory 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 zarówno części daty, jak i czasu, użyj wielu kolumn w zapytaniu SQL 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.

Używanie miar obliczeniowych języka DAX zamiast kolumn

Jeśli język wyrażeń języka DAX był już używany, być może wiesz już, że kolumny obliczeniowe są używane do zsumowania nowych kolumn na podstawie innej kolumny w modelu, podczas gdy miary obliczeniowe są definiowane raz w modelu, ale są obliczane tylko w przypadku, gdy są używane w tabeli przestawnej lub innym raporcie.

Jedną z technik oszczędzania pamięci jest zastąpienie zwykłych lub obliczeniowych kolumn miarami obliczonymi. Przykład klasyczny to Cena jednostkowa, Ilość i Suma. Jeśli masz wszystkie trzy rodzaje danych, możesz zaoszczędzić miejsce, utrzymywanie tylko dwóch i obliczanie trzeciego przy użyciu języka DAX.

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 tym modelu, w których obie zająć miejsce. Z kolei miary obliczane są obliczane na bieżąco i nie są w nich odstępy.

Wnioski

W tym artykule omówienia kilku metod, które mogą ułatwić tworzenie modelu wydajniejszego do pracy z pamięcią. Sposobem na zmniejszenie wymagań modelu danych dotyczących rozmiaru pliku i pamięci jest zmniejszenie ogólnej liczby kolumn i wierszy oraz liczby unikatowych wartości wyświetlanych w każdej kolumnie. Oto niektóre objęte przez nas techniki:

  • Usuwanie kolumn to oczywiście najlepszy sposób na zaoszczędź miejsce. Zdecyduj, które kolumny są naprawdę potrzebne.

  • Czasami można usunąć kolumnę i zamienić ją na miarę obliczeniową w tabeli.

  • Być może nie są potrzebne wszystkie wiersze w tabeli. W Kreatorze importu tabeli można filtrować wiersze.

  • Na ogół podział jednej kolumny na wiele różnych części jest dobrym sposobem na zmniejszenie liczby unikatowych wartości w kolumnie. Każda z części będzie mieć małą liczbę unikatowych wartości, a połączona suma będzie mniejsza niż oryginalna ujednolicona kolumna.

  • W wielu przypadkach jako fragmentatorów raportów potrzebne są również oddzielne części. W razie potrzeby można tworzyć hierarchie na przykład na przykład typu Godziny, Minuty i Sekundy.

  • Często kolumny zawierają więcej informacji, niż są potrzebne. Załóżmy na przykład, że w kolumnie są przechowywane miejsca dziesiętne, ale wszystkie miejsca dziesiętne zostały zastosowane w celu ukrycia wszystkich miejsc dziesiętnych. Zaokrąglenie może być bardzo efektywne w zmniejszaniu rozmiaru kolumny liczbowej.

Po wprowadzeniu wszystkich dostępnych danych w celu zmniejszenia rozmiaru skoroszytu warto rozważyć 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

Pobieranie optymalizatora rozmiaru skoroszytu

Dodatek Power Pivot: zaawansowane analizy i modelowanie danych w programie Excel

Potrzebna dalsza pomoc?

Rozwijaj umiejętności związane z pakietem Office
Poznaj szkolenia
Uzyskuj nowe funkcje w pierwszej kolejności
Dołącz do niejawnych testerów pakietu Office

Czy te informacje były pomocne?

×