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 https://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:

Function Get-FolderItem {
    <#
        .SYNOPSIS
            Lists all files under a specified folder regardless of character limitation on path depth.

        .DESCRIPTION
            Lists all files under a specified folder regardless of character limitation on path depth.

        .PARAMETER Path
            The type name to list out available constructors and parameters

        .PARAMETER Filter
            Optional parameter to specify a specific file or file type. Wildcards (*) allowed.
            Default is '*.*'
        
        .PARAMETER ExcludeFile
            Exclude Files matching given names/paths/wildcards

        .PARAMETER MaxAge
            Exclude files older than n days.

        .PARAMETER MinAge
            Exclude files newer than n days.     

        .EXAMPLE
            Get-FolderItem -Path "C:\users\Administrator\Desktop\PowerShell Scripts"

            LastWriteTime : 4/25/2012 12:08:06 PM
            FullName      : C:\users\Administrator\Desktop\PowerShell Scripts\3_LevelDeep_ACL.ps1
            Name          : 3_LevelDeep_ACL.ps1
            ParentFolder  : C:\users\Administrator\Desktop\PowerShell Scripts
            Length        : 4958

            LastWriteTime : 5/29/2012 6:30:18 PM
            FullName      : C:\users\Administrator\Desktop\PowerShell Scripts\AccountAdded.ps1
            Name          : AccountAdded.ps1
            ParentFolder  : C:\users\Administrator\Desktop\PowerShell Scripts
            Length        : 760

            LastWriteTime : 4/24/2012 5:48:57 PM
            FullName      : C:\users\Administrator\Desktop\PowerShell Scripts\AccountCreate.ps1
            Name          : AccountCreate.ps1
            ParentFolder  : C:\users\Administrator\Desktop\PowerShell Scripts
            Length        : 52812

            Description
            -----------
            Returns all files under the PowerShell Scripts folder.

        .EXAMPLE
            $files = Get-ChildItem | Get-FolderItem
            $files | Group-Object ParentFolder | Select Count,Name

            Count Name
            ----- ----
               95 C:\users\Administrator\Desktop\2012 12 06 SysInt
               15 C:\users\Administrator\Desktop\DataMove
                5 C:\users\Administrator\Desktop\HTMLReportsinPowerShell
               31 C:\users\Administrator\Desktop\PoshPAIG_2_0_1
               30 C:\users\Administrator\Desktop\PoshPAIG_2_1_3
               67 C:\users\Administrator\Desktop\PoshWSUS_2_1
              437 C:\users\Administrator\Desktop\PowerShell Scripts
                9 C:\users\Administrator\Desktop\PowerShell Widgets
               92 C:\users\Administrator\Desktop\Working

            Description
            -----------
            This example shows Get-FolderItem taking pipeline input from Get-ChildItem and then saves
            the output to $files. Group-Object is used with $Files to show the count of files in each
            folder from where the command was executed.

        .EXAMPLE
            Get-FolderItem -Path $Pwd -MinAge 45

            LastWriteTime : 4/25/2012 12:08:06 PM
            FullName      : C:\users\Administrator\Desktop\PowerShell Scripts\3_LevelDeep_ACL.ps1
            Name          : 3_LevelDeep_ACL.ps1
            ParentFolder  : C:\users\Administrator\Desktop\PowerShell Scripts
            Length        : 4958

            LastWriteTime : 5/29/2012 6:30:18 PM
            FullName      : C:\users\Administrator\Desktop\PowerShell Scripts\AccountAdded.ps1
            Name          : AccountAdded.ps1
            ParentFolder  : C:\users\Administrator\Desktop\PowerShell Scripts
            Length        : 760

            Description
            -----------
            Gets files that have a LastWriteTime of greater than 45 days.

        .INPUTS
            System.String
        
        .OUTPUTS
            System.IO.RobocopyDirectoryInfo

        .NOTES
            Name: Get-FolderItem
            Author: Boe Prox
            Date Created: 31 March 2013
            Version History:
            Version 1.5 - 09 Jan 2014
                -Fixed bug in ExcludeFile parameter; would only work on one file exclusion and not multiple
                -Allowed for better streaming of output by Invoke-Expression to call the command vs. invoking
                a scriptblock and waiting for that to complete before display output.  
            Version 1.4 - 27 Dec 2013
                -Added FullPathLength property          
            Version 1.3 - 08 Nov 2013
                -Added ExcludeFile parameter
            Version 1.2 - 29 July 2013
                -Added Filter parameter for files
                -Fixed bug with ParentFolder property
                -Added default value for Path parameter            
            Version 1.1
                -Added ability to calculate file hashes
            Version 1.0
                -Initial Creation
    #>
    [cmdletbinding(DefaultParameterSetName='Filter')]
    Param (
        [parameter(Position=0,ValueFromPipeline=$True,ValueFromPipelineByPropertyName=$True)]
        [Alias('FullName')]
        [string[]]$Path = $PWD,
        [parameter(ParameterSetName='Filter')]
        [string[]]$Filter = '*.*',    
        [parameter(ParameterSetName='Exclude')]
        [string[]]$ExcludeFile,              
        [parameter()]
        [int]$MaxAge,
        [parameter()]
        [int]$MinAge
    )
    Begin {
        $params = New-Object System.Collections.Arraylist
        $params.AddRange(@("/L","/S","/NJH","/BYTES","/FP","/NC","/NDL","/TS","/XJ","/R:0","/W:0"))
        If ($PSBoundParameters['MaxAge']) {
            $params.Add("/MaxAge:$MaxAge") | Out-Null
        }
        If ($PSBoundParameters['MinAge']) {
            $params.Add("/MinAge:$MinAge") | Out-Null
        }
    }
    Process {
        ForEach ($item in $Path) {
            Try {
                $item = (Resolve-Path -LiteralPath $item -ErrorAction Stop).ProviderPath
                If (-Not (Test-Path -LiteralPath $item -Type Container -ErrorAction Stop)) {
                    Write-Warning ("{0} is not a directory and will be skipped" -f $item)
                    Return
                }
                If ($PSBoundParameters['ExcludeFile']) {
                    $Script = "robocopy `"$item`" NULL $Filter $params /XF $($ExcludeFile  -join ',')"
                } Else {
                    $Script = "robocopy `"$item`" NULL $Filter $params"
                }
                Write-Verbose ("Scanning {0}" -f $item)
                Invoke-Expression $Script | ForEach {
                    Try {
                        If ($_.Trim() -match "^(?<Size>\d+)\s(?<Date>\S+\s\S+)\s+(?<FullName>.*)") {
                           $object = New-Object PSObject -Property @{
                                ParentFolder = $matches.fullname -replace '(.*\\).*','$1'
                                FullName = $matches.FullName
                                Name = $matches.fullname -replace '.*\\(.*)','$1'
                                Length = [int64]$matches.Size
                                LastWriteTime = [datetime]$matches.Date
                                Extension = $matches.fullname -replace '.*\.(.*)','$1'
		                        FullPathLength = [int] $matches.FullName.Length
                            }
                            $object.pstypenames.insert(0,'System.IO.RobocopyDirectoryInfo')
                            Write-Output $object
                        } Else {
                            Write-Verbose ("Not matched: {0}" -f $_)
                        }
                    } Catch {
                        Write-Warning ("{0}" -f $_.Exception.Message)
                        Return
                    }
                }
            } Catch {
                Write-Warning ("{0}" -f $_.Exception.Message)
                Return
            }
        }
    }
}

Get-FolderItem -Path "C:\Program Files\Microsoft SQL Server\MSAS13.SQL2016\OLAP\Data" | 
    Select { 
                $_.FullName + " | " + 
                $_.ParentFolder + " | " + 
                $_.FullPathLength + " | " + 
                $_.Extension + " | " + 
                $_.Name + " | " + 
                $_.Length + " | " + 
                $_.LastWriteTime
            } |
    Ft –autosize | 
    Out-File c:\test\outputsSSAS.txt –width 4096

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:

let
    CurrentFileExtension = (CurrentFilePath as text) =>
let
    #"Changed Type" = #"SSAS File Extensions",
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Contains(CurrentFilePath , [file extension])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
    #"file extension" = #"Filtered Rows"{0}[file extension type]
in
  #"file extension"
in     
    CurrentFileExtension

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

File Size Rank = RANKX(ALL('File statistics'),CALCULATE(sum('File statistics'[Length])))

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 - Extensions — Measure Groups and Dimensions – Extensions

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.