Azure DevOps Pipelines – Release your Tabular Model without Users & Roles
Problem statement
A couple of weeks ago my team & I ran into an issue with SQL Server Analysis Services (SSAS), due to a network split between companies, We weren’t able anymore to manage our SSAS access into our SSAS Tabular Model. Since deploying a Tabular Model using Visual Studio is also overwriting members & roles, we needed to find a valid alternative to execute our deployments. Manually at first and automated in the end.
Solution
To overcome this challenge, we started to think about solutions and since some of us were using Tabular Editor to deploy, and made the remark that deploying the model and roles & members can be done separately in Tabular Editor, were started to investigate the deployment option in Tabular editor more closely.
During our investigation, we ran into the following documentation page: Command Line | Tabular Editor Documentation
After going through this documentation, I started to play around in Azure DevOps and tried to build a release pipeline which was first of all listening on my Azure DevOps Repository to check if there were incoming commits and secondly deploying the changes to my desired environment.
To get to this point I executed the following steps:
- Create a new Release Pipeline
- Add an Artifact pointing to my SSAS Azure DevOps Repository
- (Add a Continuous deployment trigger in case you want to have a new deployment as soon as a commit comes in)
- In the Stage section, create a new stage based on an empty job (give it a descriptive name)
- Choose your desired Agent Pool
- Add a new task to Agent Job, and search for PowerShell
- As soon as you have added this, give it a proper name and choose type: inline, there you can use the following code to download a portable version of Tabular Editor:
$TabularEditorDownloadUrl = "https://cdn.tabulareditor.com/files/TabularEditor.2.17.1.zip"
$DownloadDest = join-path (get-location) "TabularEditor.zip"
Invoke-WebRequest -Uri $TabularEditorDownloadUrl -OutFile $DownloadDest
Expand-Archive -Path $DownloadDest -DestinationPath (get-location).Path
Remove-Item $DownloadDest
- As a next step, we want to deploy our SSAS Tabular Model without deploying Roles & members, to do, we create an additional Agent Jog Step and this time we choose a Command Line Script:
- next up we need to add the commands that need to be executed:
TabularEditor.exe "$(System.DefaultWorkingDirectory)/_MYREPOSITORYNAME_SSAS/MYPATHTO/Model.bim" -D "$(MyConnectionString)" "$(MyDatabaseName)" -O -P -W -E
What is important to notice is that you will need to change _MYREPOSITORYNAME_SSAS by the Artifact Alias. (Can be found where you have added the Artifact in the Release pipeline.) You also will need to change MYPATHTO by the full path which needs to be followed to get to the model.bim file.
To get the correct path, just open your repository and check which folders you need to open to get to your model.bim file.
As you can see, I have chosen to execute this command with options: -O, -P, -W, -E, -V. practical this means:
- -D: This option is used to deploy your model to the desired destination, you need to pass the destination server & Database name.
- -O: Allows the overwrite of an existing database during deployment, if not provided you won’t be able to deploy changes to your Model.
- -P: Allows to overwrite existing partitions in your Model.
- -W: If there are any warnings after deployment, show the output. This shows that certain objects are not processed yet.
- -E: This option returns if your deployment was successful, if not a certain error code will be given as output.
If you saved your Release pipeline, it is time to run it for the first time. When you take a look at the Logs, you should be able to see a result similar to:
Hurray for Tabular Editor!