Tworzenie modelu danych zapewniającego wydajną obsługę pamięci za pomocą programu Excel i dodatek PowerPivot

Ważne : Ten artykuł został przetłumaczony maszynowo, zobacz zastrzeżenie. Angielskojęzyczną wersję tego artykułu można znaleźć tutaj .

W programie Excel 2013 lub nowszym tworzenie modeli danych zawierających miliony wierszy, a następnie wykonywanie zaawansowanych analiz danych przed tych modeli. Modele danych można utworzyć z lub bez Power Pivot dodatek do obsługi dowolną liczbę tabel przestawnych, wykresów i wizualizacji programu Power View w tym samym skoroszycie.

Uwaga : W tym artykule opisano modele danych w programie Excel 2013. Jednak tych samych danych modelowania i funkcje dodatku Power Pivot wprowadzone w programie Excel 2013 dotyczą również programu Excel 2016. Istnieje skuteczne mała różnica między tych wersjach programu Excel.

Mimo że umożliwia łatwe tworzenie modeli dużego danych w programie Excel, istnieje kilka powodów, dla których nie do. Pierwszy, dużych modeli, które zawierają multitudes tabel i kolumn są overkill w przypadku większości analiz i ułatwiają kłopotliwe listy pól. Drugi, dużych modeli za pomocą wartościowych pamięci, negatywnie wpływających na inne aplikacje i raporty, które współużytkują te same zasoby systemowe. Na koniec w usłudze Office 365 SharePoint Online i aplikacji Excel Web App ograniczyć rozmiar pliku programu Excel do 10 MB. W przypadku modeli danych skoroszytu, które zawierają miliony wierszy napotkasz limit 10 MB bardzo szybko. Zobacz Specyfikacja i limity modelu danych.

W tym artykule poznasz sposoby konstruowania zwartego modelu, który jest łatwiejszy w obsłudze i zużywa mniej pamięci. Warto poświęcić czas na zapoznanie się z najważniejszymi wskazówkami ułatwiającymi projektowanie wydajnego modelu — wysiłek szybko się zwróci w przypadku każdego tworzonego i używanego modelu, niezależnie od tego, czy będziesz go wyświetlać w programie Excel 2013, w usłudze Office 365 SharePoint Online, w programie Office Web Apps Server czy w programie SharePoint 2013.

Rozważ też uruchomienie Optymalizator rozmiaru skoroszytu. Analizuje skoroszytu programu Excel, a jeśli to możliwe, kompresuje dalej. Pobierz Optymalizator rozmiaru skoroszytu.

W tym artykule

Współczynniki kompresji i aparat analizy w pamięci

Najmniej kolumna nieistniejącego pamięci

Dwa przykłady kolumn, które należy zawsze wykluczać

Jak wykluczyć niepotrzebne kolumny

Co z filtrowaniem tylko potrzebnych wierszy?

Co zrobić, jeśli jest potrzebna kolumny; Czy firma Microsoft zajmowała mniej miejsca?

Modyfikowanie kolumn daty i godziny

Modyfikowanie kwerendy SQL

Używanie miar zamiast kolumn obliczeniowych DAX

2 kolumny, które należy zachować?

Wnioski

Łącza pokrewne

Współczynniki kompresji i aparat analizy w pamięci

W modelach danych w programie Excel dane są przechowywane w pamięci przy użyciu aparatu analizy w pamięci. Aparat ten korzysta z zaawansowanych technik kompresji do redukowania wymagań dotyczących przechowywania, zmniejszając dzięki temu zestaw wyników do rozmiaru stanowiącego ułamek rozmiaru oryginalnego.

Przeciętnie można się spodziewać, że rozmiar modelu danych zmniejszy się od 7 do 10 razy w stosunku do rozmiaru tych danych w miejscu ich pochodzenia. Jeśli na przykład importujesz 7 MB danych z bazy danych programu SQL Server, rozmiar modelu danych w programie Excel może bez problemu wynieść 1 MB lub mniej. Uzyskany stopień kompresji zależy przede wszystkim od liczby wartości unikatowych w poszczególnych kolumnach. Im więcej wartości unikatowych, tym więcej pamięci potrzeba do ich przechowywania.

Dlaczego mówimy o kompresji i wartościach unikatowych? Ponieważ konstruowanie wydajnego modelu minimalizującego zużycie pamięci polega właśnie na zmaksymalizowaniu kompresji, a najprościej to osiągnąć, usuwając niepotrzebne kolumny, zwłaszcza jeśli te kolumny zawierają dużą liczbę wartości unikatowych.

Uwaga : Różnice w wymaganiach dotyczących przechowywania dla poszczególnych kolumn mogą być spore. W niektórych przypadkach lepiej jest mieć wiele kolumn zawierających mało wartości unikatowych zamiast jednej kolumny z wieloma takimi wartościami. W sekcji dotyczącej optymalizacji kolumny daty i godziny znajdziesz szczegółowe informacje na temat odpowiedniej techniki.

Najmniej pamięci zajmuje niezaimportowana kolumna

Najbardziej wydajna pod względem zużycia pamięci kolumna to taka, której w ogóle nie zaimportowano. Jeśli chcesz skonstruować wydajny model, przyjrzyj się każdej kolumnie i oceń, czy będzie przydatna do planowanej analizy. Jeśli nie będzie lub nie masz pewności, nie importuj jej. W razie potrzeby nowe kolumny można zawsze dodać później.

Dwa przykłady kolumn, które należy zawsze wykluczać

Pierwszy przykład dotyczy danych pochodzących z magazynu danych. W magazynach danych często znajdują się artefakty procesów ETL ładujących i odświeżających dane. Po załadowaniu danych są tworzone kolumny typu „data utworzenia”, „data aktualizacji” i „przebieg ETL”. Żadna z tych kolumn nie jest potrzebna w modelu i podczas importowania danych należy anulować ich wybór.

Drugi przykład dotyczy pomijania kolumny klucza podstawowego podczas importowania tabeli faktów.

Wiele tabel, w tym również tabel faktów, zawiera klucze podstawowe. W przypadku większości tabel, takich jak tabele zawierające dane klientów, pracowników lub dotyczące sprzedaży, klucze podstawowe mogą być przydatne do tworzenia relacji w modelu.

Tabele faktów są inne. W tabeli faktów klucz podstawowy jest używany do unikatowego identyfikowania poszczególnych wierszy. Choć jest potrzebny do celów normalizacyjnych, w modelu danych jest jednak mniej przydatny, ponieważ tam potrzebne są tylko kolumny używane podczas analizy lub w celu ustanowienia relacji między tabelami. Z tej przyczyny podczas importowania danych z tabeli faktów nie trzeba uwzględniać jej klucza podstawowego. Klucze podstawowe w tabeli faktów zajmują w modelu bardzo dużo miejsca, nie zapewniając przy tym żadnych korzyści, ponieważ nie można ich użyć do tworzenia relacji.

Uwaga : W magazynach danych i wielowymiarowych bazach danych duże tabele składające się głównie z danych numerycznych są określane jako „tabele faktów”. Tabele faktów zawierają zwykle dane dotyczące wydajności biznesowej lub transakcji, takie jak punkty danych dotyczące sprzedaży i kosztów, sumowane i dopasowywane między innymi do jednostek organizacyjnych, produktów, segmentów rynku czy regionów geograficznych. W modelu obsługującym analizę danych należy uwzględnić wszystkie kolumny w tabeli faktów, które zawierają dane biznesowe lub których można użyć do tworzenia odsyłaczy do danych przechowywanych w innych tabelach. Kolumną, którą można wykluczyć, jest kolumna klucza podstawowego tabeli faktów, która składa się z wartości unikatowych istniejących tylko i wyłącznie w tej tabeli faktów. W związku z tym, że tabele faktów są tak ogromnych rozmiarów, największe zyski przy optymalizacji wydajności modelu wynikają z wykluczenia wierszy lub kolumn z tabel faktów.

Jak wykluczyć niepotrzebne kolumny

Wydajność modele zawierają tylko tych kolumn, które rzeczywiście potrzebne w skoroszycie. Jeśli chcesz kontrolować kolumny, które znajdują się w modelu, musisz użyć Kreatora importu tabeli w dodatku programu PowerPivot można importować dane , zamiast okna dialogowego "Importowania danych" w programie Excel.

Po uruchomieniu Kreatora importu tabeli wybierz tabele do zaimportowania.

Kreator importu tabeli w dodatku PowerPivot

Dla każdej tabeli możesz kliknąć przycisk Wyświetl podgląd i przefiltruj, a następnie wybrać te części tabeli, które są rzeczywiście potrzebne. Zalecamy anulowanie zaznaczenia wszystkich kolumn, a dopiero potem zaznaczanie odpowiednich kolumn — po rozważeniu, czy są wymagane do analizy.

Okienko podglądu w Kreatorze importu tabeli

Co z filtrowaniem tylko potrzebnych wierszy?

Wiele tabel w korporacyjnych bazach danych i magazynach danych zawiera dane historyczne gromadzone przez długi czas. Poza tym w interesujących Cię tabelach mogą się znajdować informacje dotyczące obszarów działalności, które nie są wymagane do Twojej analizy.

Przy użyciu Kreatora importu tabeli możesz odfiltrować dane historyczne lub dane niepowiązane i w ten sposób zaoszczędzić mnóstwo miejsca w modelu. Na poniższym obrazie filtr danych został użyty do pobrania tylko tych wierszy, które zawierają dane z bieżącego roku, bez niepotrzebnych danych historycznych.

Okienko filtrowania w Kreatorze importu tabel

Co w sytuacji, gdy kolumna jest potrzebna — co można zrobić, aby zajmowała mniej miejsca?

Aby kolumna była lepiej kompresowana, możesz zastosować kilka dodatkowych technik. Pamiętaj, że jedynym parametrem kolumny mającym wpływ na kompresję jest liczba wartości unikatowych. W tej sekcji dowiesz się, jak zmodyfikować kilka kolumn w taki sposób, aby zmniejszyć liczbę wartości unikatowych.

Modyfikowanie kolumn daty i godziny

W wielu przypadkach kolumny daty i godziny zajmują dużo miejsca. Na szczęście istnieje kilka sposobów zmniejszenia wymagań dotyczących przechowywania danych tego typu. Wybór techniki zależy od sposobu użycia kolumny i stopnia znajomości zasad konstruowania kwerend SQL.

Kolumny daty i godziny zawierają część oznaczającą datę i część oznaczającą godzinę. Na etapie pytania o przydatność kolumny daty i godziny zadaj sobie wielokrotnie to samo pytanie:

  • Czy potrzebuję części oznaczającej godzinę?

  • Czy potrzebuję części oznaczającej godzinę na poziomie godzin, minut, sekund, milisekund?

  • Czy mam wiele kolumn daty i godziny, ponieważ chcę obliczać różnice między nimi, czy chcę po prostu sumować dane według roku, miesiąca, kwartału i tak dalej?

Odpowiedź udzielona na każde z tych pytań wpływa na sposób postępowania z kolumną daty i godziny.

Wszystkie z podanych rozwiązań wymagają zmodyfikowania kwerendy SQL. Aby ułatwić sobie ten proces, należy odfiltrować co najmniej jedną kolumnę w każdej tabeli. Spowoduje to zmianę budowy kwerendy z formatu skróconego (SELECT *) na format instrukcji SELECT, która zawiera łatwiejsze do modyfikowania w pełni kwalifikowane nazwy kolumn.

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

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

W Edytorze zapytań jest wyświetlana kwerenda SQL używana do wypełniania tabeli. Jeśli podczas importowania jakaś kolumna została odfiltrowana, kwerenda będzie zawierać w pełni kwalifikowane nazwy kolumn:

Kwerenda SQL używana do pobierania danych

Z kolei jeśli tabela została zaimportowana w całości, bez anulowania zaznaczenia żadnej kolumny ani stosowania filtru, kwerenda będzie wyświetlana w formacie „Select * from ”, który jest trudniejszy do modyfikowania:

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

Modyfikowanie kwerendy SQL

Teraz gdy już wiesz, jak znaleźć kwerendę, możesz ją zmodyfikować, aby jeszcze bardziej zmniejszyć rozmiar modelu.

  1. Jeśli kolumny zawierają walutę lub dane dziesiętne, a dane dziesiętne nie są potrzebne, użyj tej składni, aby je usunąć:

    SELECT ROUND([Nazwa_kolumny_wartości_dziesiętnej],0)…

    Jeśli potrzebujesz groszy, ale nie potrzebujesz ułamków groszy, zastąp liczbę 0 liczbą 2. Jeśli użyjesz liczb ujemnych, będzie możliwe zaokrąglanie do jednostek, dziesiątek, setek itd.

  2. Jeśli w kolumnie daty i godziny o nazwie dbo.DużaTabela.[Data Godzina] nie potrzebujesz części oznaczającej godzinę, użyj następującej składni, aby usunąć tę część:

    SELECT CAST (dbo.DużaTabela.[Data Godzina] as date) AS [Data Godzina]

  3. Jeśli w kolumnie daty i godziny o nazwie dbo.DużaTabela.[Data Godzina] potrzebujesz zarówno części oznaczającej datę, jak i części oznaczającej godzinę, użyj w kwerendzie SQL wielu kolumn zamiast jednej kolumny daty i godziny:

    SELECT CAST (dbo.DużaTabela.[Data Godzina] as date) AS [Data Godzina],

    datepart(hh, dbo.DużaTabela.[Data Godzina]) as [Data Godzina Godziny],

    datepart(mi, dbo.DużaTabela.[Data Godzina]) as [Data Godzina Minuty],

    datepart(ss, dbo.DużaTabela.[Data Godzina]) as [Data Godzina Sekundy],

    datepart(ms, dbo.DużaTabela.[Data Godzina]) as [Data Godzina Milisekundy]

    Użyj tylu kolumn, ilu potrzebujesz, aby umieścić poszczególne części w osobnych kolumnach.

  4. Jeśli potrzebne są godziny i minuty, przy czym wolisz je umieścić razem w jednej kolumnie godziny, możesz użyć tej składni:

    Timefromparts(datepart(hh, dbo.DużaTabela.[Data Godzina]), datepart(mm, dbo.DużaTabela.[Data Godzina])) as [Data Godzina Godzina Minuta]

  5. W przypadku dwóch kolumn daty i godziny, na przykład [Godzina rozpoczęcia] i [Godzina zakończenia], jeśli naprawdę potrzebujesz różnicy czasu między nimi podanej w sekundach w kolumnie 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, uzyskany czas trwania będzie podany w milisekundach.

Używanie miar obliczeniowych DAX zamiast kolumn

Jeśli znasz już język wyrażeń DAX, być może wiesz, że kolumny obliczeniowe są używane do określania nowych kolumn na podstawie innej kolumny w modelu, a miary obliczeniowe są definiowane w modelu raz, ale szacowane tylko w przypadku użycia w tabeli przestawnej lub w innym raporcie.

Jedną z technik pozwalających zaoszczędzić pamięć jest zastąpienie kolumn zwykłych lub obliczeniowych miarami obliczeniowymi. Klasyczne przykłady to kolumny Cena jednostkowa, Ilość i Razem. Jeśli masz wszystkie trzy, możesz zaoszczędzić miejsce, zostawiając tylko dwie kolumny i obliczając trzecią za pomocą miary DAX.

Którą z dwóch kolumn należy zachować?

W powyższym przykładzie zachowaj kolumny Ilość i Cena jednostkowa. Zawierają one mniej wartości niż kolumna Razem. Aby obliczyć wartość kolumny Razem, dodaj miarę obliczeniową:

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

Kolumny obliczeniowe, podobnie jak zwykłe, zajmują miejsce w modelu, natomiast miary obliczeniowe są obliczane na bieżąco i nie zajmują miejsca.

Podsumowanie

W tym artykule omówiliśmy kilka metod ułatwiających konstruowanie modelu wydajniejszego pod względem zużycia pamięci. Sposobem zmniejszenia rozmiaru pliku i wymagań dotyczących pamięci modelu danych jest zmniejszenie ogólnej liczby kolumn i wierszy oraz liczby unikatowych wartości wyświetlanych w poszczególnych kolumnach. Oto kilka podanych technik:

  • Usunięcie kolumn jest oczywiście najlepszym sposobem zaoszczędzenia miejsca. Zdecyduj, których kolumn rzeczywiście potrzebujesz.

  • Czasami możesz usunąć kolumnę i zastąpić ją miarą obliczeniową w tabeli.

  • Być może nie wszystkie wiersze w tabeli są potrzebne. Odfiltrowywanie wierszy jest możliwe przy użyciu Kreatora importu tabeli.

  • Ogólnie rzecz biorąc, rozbicie pojedynczej kolumny na wiele osobnych części jest dobrym sposobem na zmniejszenie liczby wartości unikatowych w kolumnie. Każda z części będzie zawierała niewielką liczbę wartości unikatowych, a połączona wartość całkowita będzie mniejsza niż dla pierwotnej jednolitej kolumny.

  • W wielu przypadkach osobne części są przydatne jako fragmentatory w raportach. W odpowiednich przypadkach z takich części jak Godziny, Minuty i Sekundy można utworzyć hierarchie.

  • Kolumny często zawierają więcej informacji, niż to potrzebne. Załóżmy, że w kolumnie są przechowywane wartości dziesiętne, ale zastosowano formatowanie, aby ukryć wszystkie wartości tego typu. Zaokrąglenie może być bardzo skutecznym sposobem zmniejszenia rozmiaru kolumny numerycznej.

Teraz, gdy jest już gotowe, można zmniejszyć rozmiar skoroszytu, należy rozważyć, czy też uruchomiony Optymalizator rozmiaru skoroszytu. Analizuje skoroszytu programu Excel, a jeśli to możliwe, kompresuje dalej. Pobierz Optymalizator rozmiaru skoroszytu.

Linki pokrewne

Specyfikacja i limity modelu danych

Optymalizator rozmiaru skoroszytu plik do pobrania

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

Uwaga : Zrzeczenie dotyczące tłumaczenia maszynowego: Ten artykuł został przetłumaczony przez system komputerowy bez interwencji człowieka. Firma Microsoft udostępnia te tłumaczenia maszynowe, aby ułatwić użytkownikom, którzy nie znają języka angielskiego, korzystanie z zawartości dotyczącej produktów, usług i technologii firmy Microsoft. Ponieważ ten artykuł został przetłumaczony maszynowo, może zawierać błędy w słownictwie, składniowe lub gramatyczne.

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

Czy te informacje były pomocne?

Dziękujemy za opinię!

Dziękujemy za opinię! Wygląda na to, że połączenie Cię z jednym z naszych agentów pomocy technicznej pakietu Office może być pomocne.

×