SSAS – Analiza plików bazy analitycznej

Wstęp

Kolejnym przykładem analizy, który pozwoli nam lepiej zrozumieć projekt bazy wielowymiarowej jest analiza plików, które są nieodłączonym jej elementem. Jak już wspomniano w poście http://pl.seequality.net/monitorowanie-optymalizacja-ssas/ Analysis Services wszystkie dane przechowuje w ogromnej ilości plików. Dla każdego atrybutu, hierarchii, agregacji itp. Wygenerowany zostanie jeden lub kilka plików, które mają za zadanie w efektywny sposób zwrócić dane potrzebne do wygenerowania rezultatu zapytania MDX. W tym artykule pobierzemy listę wszystkich plików bazy wielowymiarowej za pomocą skryptu PowerShell, a następnie przygotujemy raport w Power BI, który pozwoli lepiej zrozumieć rozmiar naszej bazy. Wszystkie pliki dostępne są na GitHub: https://github.com/seequality/seequality_ssas

Analiza taka powinna być pomocna w sytuacji gdy:

  • wydaje się, że kostka zajmuje za dużo miejsca
  • na serwerze jest problem z ilością wolnego miejsca dyskowego
  • wydaje się, że procesowanie kostki jest wolne, a wszystkie inne dobre praktyki zostały już wdrożone
  • Do bazy wielowymiarowej dodane zostały wszystkie dane dostępne w hurtowni bez wstępnej analizy i chcielibyśmy zredukować jej rozmiar, a nie mamy wiedzy na temat tego co zajmuje najwięcej przestrzeni dyskowej (Na pytanie co możemy usunąć i z czego użytkownicy nie korzystają odpowiemy sobie w ramach innego artykułu).

Jak można zauważyć analiza tego typu powinna dostarczyć wielu cennych informacji.

Gromadzenie danych do analizy

Rozpoczniemy od pobrania listy plików oraz ich rozmiarów – metod jest oczywiście wiele. Aby tego dokonać możemy podłączyć się bezpośrednio z PowerBI do folderu i pobrać listę plików, możemy skorzystać z komend konsoli Windows, skorzystać z PowerShell lub wykorzystać gotowe narzędzia czy skrypty dostępne w sieci. Przygotowywanie raportu w oparciu o PowerQuery i bezpośrednie połączenie z PowerBI nie jest według mnie najlepszym rozwiązaniem, ponieważ bardzo rzadko będziemy mieli możliwość połączenia się do serwera produkcyjnego z maszyny lokalnej, a instalacja Power BI na “produkcji” lub udostępnianie folderów z “produkcji” nie wydaje się najlepszym rozwiązaniem. Najlepszym i równie prostym rozwiązaniem może być skrypt PowerShell, chociaż nawet tutaj kryje się drobna pułapka. Z racji struktury plików SSAS oraz nazw obiektów, które możemy zdefiniować w projekcie nazwy plików mogą przekroczyć 260 znaków.
Z tego powodu nie możemy skorzystać z domyślnego Get-ChildItem, ponieważ nazwy, które przekroczą tę liczbę zostaną ucięte. Możemy jednak skorzystać z funkcji dostępnej pod adresem: https://gallery.technet.microsoft.com/scriptcenter/Get-Deeply-Nested-Files-a2148fd7, czyli Get-FolderItem. Skrypt PowerShell będzie wyglądał wówczas następująco:

Po uruchomieniu skryptu wszystkie informacji o plikach zostaną zapisane w pliku tekstowym – wykorzystując ten plik jako źródło danych możemy przygotować raport w Power BI. W dalszej części tego postu pokażę przykładowy raport i analizę. Zanim przejdziemy jednak do raportu warto zwrócić na moment uwagę na pliki SSAS oraz ich podział i strukturę co powinno ułatwić dalsze korzystanie z raportu. Odpowiada ono dokładnie architekturze Analysis Services. Przeglądając folder z danymi znajdziemy:

  • Data (folder z danymi)
    • Folder bazy danych <xxx>.db
      • Metadane dla kostek, źródeł danych, widoków danych, wymiarów oraz grup miar {dsv.xml, ds.xml, cub.xml, dim.xml, dms.xml, CryptKey.bin, }
      • Folder źródła danych <xxx>.ds
        • Ewentualne pliki źródła danych
      • Folder wymiaru <xxx>.dim
        • Pliki wymiaru i plik metadanych {.kstore, .ksstore, .khstore, .astore, .asstore, .ahstore, .hstore, .data, .data.hdr, .map, .map.hdr , .sstore, .lstore, .ostore, .dstore, .bstore, .bsstore, info.xml}
      • Folder kostki <xxx>.cub
        • Folder grupy miar <xxx>.det
          • Folder partycji <xxx>.prt
            • Pliki partycji oraz plik metadanych {fact.data, fact.data.hdr, info.xml}
          • Metadane dla partycji {prt.xml}
        • Pliki kostki, skrypty MDX, perspektywy {det.xml, perps.xml, scr.xml}
      • Folder struktury Data Mining <xxx>.dms
        • Folder modelu Data Mining <xxx>.dmm
          • Pliki modelu Data Mining {.cnt.bin, .dt, .dtavl, .dtstr, .mrg.ccmap, .mrg.ccstat}
        • Metadane dla modeli Data Mining {.dmm.xml}
    • Folder dla Assemblies <xxx>.asm
      • Ewentualne pliki dla assemblies
    • Metadane dla assemblies, baz danych, role i pliki serwera {.asm.xml, master.vmp, CrypKey.bin, db.xml, role.xml}

Odnośnie samych rozszerzeń oraz ich zawartości nie ma co prawda zbyt wiele informacji, natomiast na pewno możemy się odwołać do książek “Pro SQL Server 2008 Analysis Services By Philo Janus, Guy Fouche”, “Microsoft SQL Server 2008 Analysis Services Unleashed by Irina Gorbach, Alexander Berger, Edward Melomed”. Warto również zobaczyć prezentację “Internal Storage and IO Optimization” dostępną pod adresem  http://www.vconferenceonline.com/vconference/materials/slides/Module%203%20-%20Internal%20Storage%20Structures.pdf oraz post https://blog.gbrueckl.at/ssas/, gdzie Gerhard Brueckl pokazywał w jaki sposób analizować pliki SSAS w Excelu. Moja lista rozszerzeń i plików bazuje na tych źródłach, aczkolwiek została trochę zmieniona.

file extension file extension description file extension type
.kstore key store key store
.ksstore key string store key string store
.khstore key hash table key hash table
.astore property store property store
.asstore property string store property string store
.ahstore name hash table name hash table
.hstore hole store hole store
.data mapdata store mapdata store
.data.hdr mapdata store header mapdata store header
.map bitmap indexes bitmap indexes
.map.hdr bitmap indexes bitmap indexes
.sstore set store set store
.lstore structure store structure store
.ostore order store order store
.dstore decoding store decoding store
.bstore member-value (blob) store member-value (blob) store
.bsstore member-value string store member-value string store
.ustore unary store unary store
.dmdimstore data mining dimension store data mining
.dmdimhstore data mining diminesion hole store data mining
.cnt.bin data mining files data mining
.dt data mining files data mining
.dtstr data mining files data mining
.dtavl data mining files data mining
.ccmap data mining files data mining
.ccstat data mining files data mining
asm.xml assembly assembly
.vmp system file system file
CryptKey.bin system file system file
.db.xml database metadata metadata
.role.xml role role
.dim.xml dimension metadata metadata
.dms.xml data minig structure metadata metadata
.dsv.xml data spurce view metadata metadata
.ds.xml data source metadata metadata
.cub.xml cube metadata metadata
.det.xml measure group metadata metadata
.persp.xml perspective metadata metadata
.scr.xml mdx script mdx script
.prt.xml measure group partition metadata metadata
.agg.xml measure group aggregation metadata metadata
info.*.xml metadata metadata
.dmm.xml data mining model metadata metadata

 

Lista ta została również zaimportowana do Power BI oraz posłuży do przyporządkowania odpowiednich typów rozszerzeń dla plików.

Raport i analiza

Sam raport jest stosunkowo prosty- w tym miejscu warto wspomnieć o funkcji, która przypisuje wspomniany wcześniej typ rozszerzeń w Power Query:

Dodatkowo, aby umożliwić prostą prezentację największych plików dodano miarę wykorzystującą funkcję RANKX

Oto jak może wyglądać przykładowy raport przygotowany w Power BI do analizy plików SSAS. Jest to raport poglądowy oparty na plikach z przykładowej bazy Adventure Works. Dla bazy produkcyjnej powinien wyglądać jeszcze ciekawiej.

Directory Size Analysis – główna strona raportu. Prezentuje ogólny rozmiar kostki, średni rozmiar plików czy liczbę plików. Oprócz tego ukazuje podział wielkości względem głównych typów obiektów jak grupy miar, wymiary, data mining i inne oraz prezentuje 25 największych plików w całej bazie danych SSAS.

Directory Path Analysis – analiza struktury folderu. Ścieżki folderów SSAS z głównego folderu zostały w Power BI podzielone i następnie została utworzona hierarchia. Pozwala to na graficzną analizę poszczególnych folderów. Rozpoczynając od głównego folderu.

Directory Path Analysis – ciąg dalszy. Przejście o jeden poziom niżej (Drill down) do kolejnych folderów.

Directory Path Analysis – ciąg dalszy. Przejście o jeden poziom niżej (Drill down) tym razem do konkretnego folderu kostki. Podział rozmiaru plików względem grup miar.

Measure Groups and Dimensions – strona raportu prezentująca w jaki sposób użycie zasobów dyskowych rozdziela się względem miar oraz wymiarów.

Measure Groups and Dimensions – podobnie jak w poprzednim przykładzie analiza dotyczy głównych składowych czyli miar oraz wymiarów, ale oprócz konkretnych typów rozszerzeń.

Extensions – analiza rozszerzeń plików, czyli które typy plików zajmują najwięcej miejsca. Dodatkowo natężenie koloru na środkowym wykresie odpowiada ilości plików.

File change date – ostatnia strona raportu pozwala na analizę czasu w którym pliki zostały zmienione. Pozwala to na zauważenie ewentulanych błędów przy procesowaniu oraz na zrozumienie zmian zachodzących w wymiarach.

Zakończenie

Analiza plików serwera SSAS może okazać się kluczowa dla zrozumienia problemów z wydajnością, procesowaniem oraz samego rozmiaru kostek. W realnym scenariuszu moglibyśmy zauważyć na przykład:

  • które agregacje są na tyle duże, że warto rozważyć ich usunięcie
  • które grupy miar są największe. Być może warto rozważyć zmiane poziomu szczegółowości lub zrezygnować np. z niektórych grup miar “Distinct”
  • które wymiary, atrybuty i hierarchie zajmują najwięcej przestrzeni dyskowej. Być może nie wszystkie hierarchie lub atrybuty są potrzebne?

Oczywiście analiza tego typu nie da nam jednoznacznej odpowiedzi na pytanie czy określone obiekty powinny być usunięte czy też nie. Celem tej analizy jest uzupełnienie wiedzy o tym które obiekty są używane i jak często. Dzięki temu uzyskamy całościowy pogląd na to co może być usunięte czy też zmienione, a co nie.

Slawomir Drzymala
Follow me on

Slawomir Drzymala

Still playing with data and .NET technologies
Slawomir Drzymala
Follow me on

Latest posts by Slawomir Drzymala (see all)

Leave a Comment

Your email address will not be published. Required fields are marked *