Build oraz Deployment modelu tabelarycznego Analysis Services

Nieodłącznym elementem pracy z modelami tabelarycznymi Analysis Services jest potrzeba ich wdrożenia na serwer docelowy. Kwestia ta dotyczy nie tylko standardowych modeli tego typu, ale również modeli Power BI opartych o tą samą technologię, które od jakiegoś już czasu mogą być budowane przy użyciu Visual Studio. Z tego też powodu postanowiłem napisać parę słów o tym w jaki sposób możemy podejść do deploymentu, jakie narzędzia mogą być dla nas przydatne i jak docelowo możemy zaimplementować pipeline w AzureDevops, który automatycznie to dla nas wykona. Jest to pierwszy artykuł z tej serii w którym wytłumaczymy sobie kilka terminów i wykonamy Build przy pomocy MSBuild oraz przedstawimy Analysis Services Deployment Utility w wersji opartej o graficzny interfejs użytkownika oraz linię komend – zapraszam do lektury.

W momencie gdy skończyliśmy jakąś część pracy z modelem tabelarycznym zapewne chcielibyśmy wrzucić efekt naszej pracy na serwer Analysis Services czy to znajdujący się w naszej infrastrukturze czy też w chmurze. Pierwszym krokiem aby tego dokonać jest wykonanie na naszym projekcie operacji Build – w uproszczeniu możemy tę operację utożsamiać z tym, że nasz projekt zostanie sprawdzony pod kątem poprawności składni, konfiguracji itd. Efektem builda są tzw. artefakty które w uproszczeniu są zestawem plików, które posłużą nam podczas operacji wrzucania projektu na serwer.

W tym miejscu warto wspomnieć, że z operacją Build wiążą się tak naprawdę trzy podstawowe operacje:

Clean – usuwa wszystkie artefakty powstałe w przypadku wcześniej wykonanego builda (jeśli taka operacja miała w ogóle miejsce),
Build – proces inkrementalnego builda w wyniku którego powstają artefakty. “Inkrementalny” oznacza, że przebudowywane są tylko te obiekty, które się zmieniły (chyba, że VS uzna pełny Build za konieczny),
Rebuild – proces pełnego przebudowywania projektu od początku czyli tak naprawdę jest to operacja Clean + Build.

Operacje te są oczywiście dostępne zarówno z GUI jak i z linii komend. Możecie do nich dotrzeć na wiele sposobów, najprostszym z nich jest kliknięcie prawym przyciskiem myszy na projekt Analysis Services i wybranie odpowiednich opcji z menu kontekstowego:

Na poniższym nagraniu możecie zobaczyć jak działa każda operacja i co się dzieje w folderze wynikowym:

Jak możecie zauważyć powyżej pliki powstałe w wyniku operacji Build domyślnie znajdują się w folderze Bin danego projektu (co oczywiście możemy zmienić). Będą one używane w momencie gdy będziemy chcieli wykonać Deployment ale do tego jeszcze dojdziemy. Wiemy zatem jak wykonać Build z poziomu interfejsu graficznego – spróbujmy przejść do linii komend bo to właśnie ta opcja będzie przydatna w automatyzacji. Programem, który pozwoli nam budować nasz projekt jest wspomniany wcześniej MSBuild.exe . Pozwala on nie tylko na zbudowanie projektu Analysis Services ale również całego szeregu innych projektów w tym również SQL Server. Znajdziemy go w folderze Visual Studio – w moim przypadku ścieżka wygląda następująco:

C:\Program Files (x86)\Microsoft Visual Studio\2019\Community\MSBuild\Current\Bin

Oczywiście powyższa ścieżka jest zależna od wersji i tego czy zainstalowaliśmy Visual Studio w standardowej lokalizacji – myślę jednak, że nikomu nie powinno sprawić problemu odnalezienie odpowiedniego pliku. Dobrym pomysłem może wydawać się parametryzacja w krypcie lokalizacji tego pliku tak aby nie trzeba było zmieniać tej ścieżki w razie potrzeby. Użycie MSBuild do operacji Build w najprostszej postaci sprowadza się do przekazania jako parametru pliku projektu, którym jest w przypadku modeli tabelarycznych plik o rozszerzeniu smproj. Cały kod sprowadza się do dwóch linijek i w moim przypadku wygląda następująco (wykonywane z poziomu Powershella):

Set-Location "C:\Program Files (x86)\Microsoft Visual Studio\2019\Community\MSBuild\Current\Bin"
.\MSBuild.exe "C:\Users\adria\source\repos\TabularProject3\TabularProject3\TabularProjectA.smproj"

Wynikiem tej operacji jest powstanie dokładnie tych samych plików co w przypadku użycia interfejsu graficznego:

Powyższa operacja to nic innego jak Build. Jeżeli chcielibyśmy wykonać Rebuild musimy dodać dodatkowy przełącznik /t:Clean,Build:

.\MSBuild.exe "C:\Users\adria\source\repos\TabularProject3\TabularProject3\TabularProjectA.smproj"  /t:Clean,Build

Od razu widzimy, że informacje zwracane na ekran potwierdzają to co powiedzieliśmy wcześniej na temat tych właśnie operacji:

Wiemy jak Buildować projekt to powiedzmy sobie czym są artefakty powstałe w jego wyniku, a jest ich trzy:

asdatabase – główny plik zawierający definicję modelu tabelarycznego,
deploymentoptions – plik zawierający informacje na temat tego czy takie obiekty jak partycje czy role mają zostać wrzucone na serwer czy też nie oraz informację o tym czy po deploymencie ma nastąpić procesowanie i jakiego typu.
deploymettargets – plik zawierający informacje o docelowym serwerze na jaki ma być wrzucony model tabelaryczny

Pierwszy z plików jest w formacie json ( w starszych wersjach SSAS był to XML), a pozostałe z nich mają formę XMLa. Nic nie stoi na przeszkodzie abyśmy modyfikowali te pliki bezpośrednio co też często się robi aby zmienić np. serwer docelowy:

To oczywiście nie wszystkie pliki związane z deploymentem bo możemy mieć jeszcze kilka dodatkowych ale te wystarczą do tego zbudować interesujący nas proces. Przejdźmy do deploymentu bo ten temat jest jeszcze ciekawszy niż Build. Najprostszym sposobem deploymentu jest wybranie odpowiedniej opcji z menu kontekstowego wybranego przez nas projektu Analysis Services:

Visual Studio poprosił o walidację połączenia do źródła danych, które zostało zdefiniowane w projekcie dlatego też podałem użytkownika i hasło (dla celów testowych wybrałem SQL Server Authentication jako metodę autoryzacji do mojej źródłowej bazy danych). Właściwie to była jedyna interakcja jaką zobaczyłem bo zaraz po tym pojawiło się okno pokazujące następujące informacje:

Wygląda na to, że mój model powędrował na serwer i został wypełniony danymi. To co się dzieje w momencie kliknięcia “Deploy” jest ustalone we właściwościach projektu:

Ustawienia te możemy traktować jako “Domyślny Deployment” jednakże zazwyczaj nie jest to docelowa metoda, którą będziemy chcieli używać ze względu na ograniczone możliwości dostosowywania. W moim odczuciu nieco lepiej sprawdza się użycie narzędzia Analysis Services Deployment Utility, które to zostanie zainstalowane w naszym systemie wraz z Management Studio. Możemy je znaleźć w folderze gdzie zainstalowaliśmy Management Studio pod nazwą Microsoft.AnalysisServices.Deployment.exe. Przy zwykłym uruchomieniu narzędzie zaprezentuje nam graficzny interfejs użytkownika, który przeprowadzi nas przez cały proces. Po przejściu przez ekran powitalny musimy wskazać plik asdatabase wygenerowany przez Builda:

Następny krok to wskazanie serwera docelowego oraz nazwy docelowej bazy danych. Tutaj warto również zwrócić uwagę na fakt, że to właśnie w tym miejscu możemy te opcje nadpisać w porównaniu do tego co jest w ustawieniach projektu:

Przechodząc dalej mamy bardzo ważną możliwość zdefiniowania czy role oraz partycje mają być nadpisane przez deployment czy też nie:

Zazwyczaj w moich projektach partycje są utrzymywane poza projektem Visual Studio przez procesy ETL, dlatego też podobnie jak role więc w większości przypadków ustawiam tutaj Retain partitions oraz Retain Roles and members ale oczywiście wszystko zależy od podejścia i konkretnego scenariusza. Przechodząc dalej mamy możliwość zdefiniowania łańcucha połączeniowego oraz danych uwierzytelniających do źródeł danych naszego modelu. Jeśli niechcemy nadpisywać tego co znajduje się już na serwerze możemy oczywiście zaznaczyć opcję “Retain configuration settings for existing objects“:

Ostatnie dwa kroki wskazują na to czy po deploymencie ma nastąpić procesowanie (w jednej transakcji lub nie) oraz czy wygenerowany skrypt ma zostać wykonany natychmiast czy też zapisany w celu późniejszego uruchomienia:

To by było na tyle jeśli chodzi o graficzny aspekt Analysis Services Deployment Wizard. W zależności od wybranych opcji narzędzie zakończy swoje działanie wygenerowaniem skryptu lub wykonaniem deploymentu i procesowaniem. Osobiście korzystam z tego narzędzia w trybie graficznym szczególnie jeśli wrzucam coś na swoje środowisko deweloperskie, a nie chcę nadpisać istniejących partycji lub też w przypadku ręcznego deploymentu. Ale oczywiście narzędzie to daje również możliwości automatyzacji związane z jego trybem linii komend.

Pierwszą komendą jakiej się przyjrzymy będzie uruchomienie narzędzia Deployment Utility z przełącznikiem /a:

& "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Microsoft.AnalysisServices.Deployment.exe"  /a

Powyższą komendę uruchamiam z poziomu Powershella dlatego też występuje znak ampersand, który nie jest potrzebny w przypadku zwykłej linii komend. Powyższe polecenie spowoduje, że uruchomiony zostanie ten sam kreator co powyżej z tym, że będzie on działał w specjalnym trybie. Tryb ten pozwoli wygenerować nam wspomniane wcześniej deploymentoptions i deploymenttargets, które będziemy mogli używać później w automatyzacji. Samego kreator nie będę szczegółowo pokazywał bo nie różni się on od tego co pokazałem powyżej, warto jednak zwrócić uwagę na dwa aspekty:

upewnijmy się, że model nie będzie procesowany po deploymencie (czyli w oknie Processing Method ustawmy None) – obsłużymy sobie to sami,
w ostatnim kroku narzędzia możemy zaznaczyć opcję aby nie przechowywać haseł w pliku konfiguracyjnym – na ten moment zaznaczmy tą opcję:

Powyższe uruchomienie komendy z przełącznikiem “/a” będzie operacją jednorazową służącą do zdefiniowania konfiguracji. Oczywiście jeśli mamy do dyspozycji pliki konfiguracyjne to możemy je sami zmodyfikować/dostosować do naszych potrzeb – pokazana metoda jest użyteczna w sytuacji gdy chcielibyśmy sobie te pliki wygenerować w celu późniejszego użycia. Po przejściu przez kreator w folderze wynikowym powinniśmy otrzymać kilka plików konfiguracyjnych. Oprócz wspomnianych deploymentoptions i deploymenttargets znajdziecie tam dwa dodatkowe pliki:

configsettings,
assecurityinformation.

W przedstawionym przeze mnie scenariuszu nie są one używane więc je usuwam – jeśli ktoś chciałby poczytać na ich temat to odsyłam do dokumentacji. Ustawienia które użyliśmy do wygenerowania plików są oczywiście tymi domyślnymi i w każdej chwili możemy je zmienić czy to ręcznie czy też poprzez kod np. Powershell.

Mając już wszystko gotowe możemy przejść do budowy Poniżej przedstawiam prosty skrypt Powershell pod postacią funkcji, który będzie robiłdostosowany deployment i opcjonalne procesowanie modelu:

function Invoke-SSASDeploymentProcess 
{
    [CmdletBinding()]
    param(
        [Parameter()]
        [string]$SSASDeploymentUtilityPath,

        [Parameter()]
        [string]$SSASDatabaseFolderPath,

        [Parameter()]
        [String]$TargetServer,      
        
        [Parameter()]
        [String]$TargetDatabase,

        [Parameter()]
        [String]$UserName,

        [Parameter()]
        [SecureString]$Password,

        [Parameter()]
        [Boolean]$ProcessTargetDatabase
    )
    $ErrorActionPreference = "Stop"


    Write-Host "Deploying $TargetDatabase to $TargetServer"
    
    #configuration
    $SSASDatabaseFilePath = $SSASDatabaseFolderPath + "\Model.asdatabase"

    #setting up deployment target file
    $Targetfile = "$SSASDatabaseFolderPath\Model.deploymenttargets"
    $TargetXmldata = [xml](Get-Content $Targetfile); 
    $TargetXmldata.DeploymentTarget.Database ="$TargetDatabase"
    $TargetXmldata.DeploymentTarget.Server = "$TargetServer"
    $TargetXmldata.Save($TargetFile)

    #setting up configuration file 
    $OptionsFile = "$SSASDatabaseFolderPath\Model.deploymentoptions"
    $OptionsXmldata = [xml](Get-Content $OptionsFile); 
    $OptionsXmldata.DeploymentOptions.ProcessingOption = "DoNotProcess"
    $OptionsXmldata.DeploymentOptions.ConfigurationSettingsDeployment = "Deploy"
    $OptionsXmldata.Save($OptionsFile)
 

    #Generate template for deployment
    Start-Process -FilePath "$SSASDeploymentUtilityPath\Microsoft.AnalysisServices.Deployment.exe"  -ArgumentList "$SSASDatabaseFilePath","/s","/o:$SSASDatabaseFolderPath\OutputQuery.json" -NoNewWindow -Wait

    #Read template and add credentials

    $xmladata = Get-Content -Path $SSASDatabaseFolderPath\OutputQuery.json | ConvertFrom-Json

    #look for datasources where sql authentication is used
    foreach ($ds in $xmladata.createOrReplace.database.model.dataSources)
        {
            $ds.Credential.AuthenticationKind = 'UsernamePassword'
            $ds.Credential.Username = $UserName

            #Add password property to the object and assign password from secure string
            $ds.credential | Add-Member -NotePropertyName Password -NotePropertyValue ([Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($Password)))
        }
    
    #assign deployment query with json with password
    $DeploymentQuery =   $xmladata | ConvertTo-Json -depth 100 

    $DeploymentResult = Invoke-ASCmd -Server $TargetServer -Query $DeploymentQuery 

    if($DeploymentResult -notlike "*error*")
        {
            Write-Host "Tabular deployed successfully"
        }
    else 
        {
            Write-Error "Error occurred during deployment: $DeploymentResult"
        }
    
    #Full Processing based on parameter
    if($ProcessTargetDatabase -eq $true)
    {
        $ProcessingQuery ='{"refresh": {"type": "full","objects": [{"database": "' +$TargetDatabase+'"}]}}'

        $ProcessingResult = Invoke-ASCmd -Server "$TargetServer" -Query "$ProcessingQuery"
        
        if ($ProcessingResult -notlike "*error*")
        {
            Write-Host "Database $TargetDatabase processed successfully"
        }
        else 
        {
            Write-Error "Error occurred during processing: $ProcessingResult"    
        }
    }
}

W powyższym skrypcie możecie znaleźć kilka ciekawych sekcji, które w skrócie opisałem komentarzami w języku angielskim. Oczywiście powyższy skrypt jest poglądowy i z całą pewnością może być rozbudowany o dodatkowe kwestie, ale może być dobrym punktem startowym do budowania swojego procesu do automatyzacji deploymentów.

Kilka kwestii związanych z powyższym skryptem:

Deployment Utility jest uruchamiane z przełącznikiem /s oznaczającym “silent mode” czyli wszelkie zwroty z narzędzia powędrują do konsoli. Dodatkowy przełącznik /o oznacza, że narzędie wygeneruje skrypt i zapisze go w pliku.
procesowanie nie odbywa się poprzez Deployment Utility tylko poprzez wywołanie komendy Invoke-ASCmd – jest to podejście w moim mniemaniu nieco prostsze w utrzymaniu i zarządzaniu,
Hasło wewnątrz skryptu jest trzymane jako tzw. Secure Sting i ostatecznie jest dekodowane i wrzucane jako część komendy json wysyłanej później w Invoke-ASCMD,
Hasło do funkcji może być przekazane w dowolny sposób najlepiej z repozytorium haseł pod postacią np. Azure Key Vault lub Credential Managera,
procesowanie odbywa się na żądanie i jest to procesowanie pełne (Full),
rezultat zwracany przez serwer Analysis Services ma postać kodu XML – w celu uproszczenia walidacji wyszukuje w tym kodzie słowo “error”, które może tam wystąpić zarówno podczas deploymentu jak i procesowania. Jak już wspomniałem nic nie stoi na przeszkodzie aby dodać bardziej wyrafinowane sprawdzanie zwrotki z serwera.

Ogólnie cały skrypt opiera się na tym żeby odpowiednio ustawić pliki konfiguracyjne wygenerować skrypt XMLA i uruchomić go na serwerze. Mając do dyspozycji coś takiego możemy stworzyć sobie mechanizm deploymentu rozwiązania np. przy pomocy Azure DevOps lub innego oprogramowania do automatyzacji. Przykładowe wywołanie:

$pass = ConvertTo-SecureString "zaq1@WSX" -Force -AsPlainText

Invoke-SSASDeploymentProcess -SSASDeploymentUtilityPath   "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\" `
                             -SSASDatabaseFolderPath      "C:\Users\adria\source\repos\TabularProject3\TabularProject3\bin" `
                             -TargetServer                "localhost" `
                             -TargetDatabase              "MyTabularDatabase" `
                             -UserName                    "test" `
                             -Password                    $pass `
                             -ProcessTargetDatabase       $true

skutkuje poprawnym deploymentem i procesowaniem:

Myślę, że w kolejnych artykułach postaram się pokazać inne możliwości związane z buildowaniem, deploymentem, procesami CICD oraz Azure Devops w odniesieniu do narzędzi wewnątrz platformy danych Microsoft. Na ten moment dziękuję bardzo za poświęcony czas, zapraszam do subskrypcji oraz życzę miłego dnia!

Linki: