Istnieje kilka sposobów na pracę w Excelu z większą liczbą rekordów niż 1 mln. Najnowsze z nich, wykorzystujące Power Query i Power Pivot, pozwalają na znaczną redukcję rozmiarów plików i przyspieszenie ich działania. Oto kilka przykładów z naszych eksperymentów przy realizacji usług związanych z automatyzacją raportowania oraz pobieraniem i transformacją danych z różnych źródeł.

Założenia eksperymentu

Eksperyment przeprowadziliśmy przy następujących założeniach:

1. Programy: Excel 365 Pro Plus 32-bitowy, Power BI 64-bitowy
2. Komputer: Procesor i7, pamięć RAM 8 GB, dysk SSD
3. Plik źródłowy: 15 kolumn, 1 048 575 rekordów, rozmiar 110 MB

Scenariusz 1: 1 mln rekordów z pliku Excel

Pierwszy scenariusz polegał na wczytaniu 1 pliku Excel poprzez mechanizm Power Query. Plik ważył 110 MB. Plik wyczytywałem na 3 sposoby:

1. PQ (Power Query > Tabela przestawna): czas pobierania 44 s, plik końcowy waży 78 MB
2. PP (Power Query > Power Pivot > Tabela przestawna): czas pobierania 60 s, plik końcowy waży 17 MB
3. PBI (Edytor zapytań > Model danych > Power BI): czas pobierania 44 s, plik końcowy waży 13 MB

Scenariusz 2: 10 mln rekordów z katalogu z 10 plikami Excel

10 plików bazowych waży łącznie ponad 1 GB (10 x 110 MB). Normalnie nie bylibyśmy w stanie pracować na takiej ilości danych w Excelu. Mechanizmy Power Query i modelu danych budowanego z użyciem Power Pivot nie mają z taką ilością większych problemów.

1. PQ: po 11 min przy 8,5 mln pobranych rekordów pojawił się komunikat: Wystąpiły problemy z pobraniem danych
2. PP: czas pobierania 6 min, plik końcowy waży 47 MB (przy 1 GB dla plików źródłowych!)
3. PBI: czas pobierania 5 min 40 s, plik końcowy waży 33 MB (przy 1 GB dla plików źródłowych!)

Oto zrzut liczby wierszy z Excela:

Wnioski końcowe

Kompresja danych, która odbywa się w modelu danych Excel i Power BI, potrafi wielokrotnie zmniejszyć rozmiar danych. Szczególnie widać w to w scenariuszu 2, gdzie z 1 GB danych uzyskaliśmy kilkadziesiąt MB. Co więcej, pliki były niesamowicie szybkie w pracy na modelu danych – wyniki w tabeli przestawnej pojawiały się od razu, nie było zawieszania, a ponieważ kalkulacje były napisane na modelu danych, również liczyły się znacznie szybciej. Wszystko za sprawą technologii in-memory, którą Microsoft nazwał xVelocity.

Jeśli Twoje raporty w programie Excel odświeżają się za długo, nie mieszczą się w arkuszu i nie pozwalają na szybką pracę z danymi, skorzystaj z tego, co w Excelu innowacyjne: ścieżki Power Query > Power Pivot > Tabela przestawna lub z aplikacji Power BI.