You’re Deploying it Wrong! – AS Edition (Part 3)

This is part 3 of the Analysis Services DevOps blog series. Go to part 2

Automation Magic with Azure Pipelines

Azure Pipelines is the service within Azure DevOps, that lets us automate our builds and deployments. In short, there are two kinds of pipelines in Azure DevOps: Build Pipelines, which are used to validate code and produce build artifacts, and Release Pipelines, which take an artifact from a build pipeline and deploys it to one or more environments.

For an Analysis Services project, you can think of the Model.bim file as an artifact, as it contains all the model metadata necessary to deploy the project somewhere. For other project types, you would have different artifacts: An SQL Server Database Project (SSDT) would use a .dacpac file, an Integration Services project would use an .ispac file, and so on.

Within a pipeline, you define a series of tasks to perform the operations needed. Azure DevOps offers a lot of standard tasks out-of-the-box, with specific functionality for common build and deployment scenarios. A lot of these task are related to web and mobile application development. If you search the marketplace, there’s also a couple of free 3rd party tasks related to Analysis Services. For this blog series, however, we will mostly use the standard (Windows) Command line task and the Tabular Editor CLI, in order to validate code within source control, produce a Model.bim file, and then deploy it to an instance of Analysis Services.

The examples provided here, are easily converted to PowerShell if that’s what you prefer.

Pipeline Agents

An Agent is the service application that executes the steps defined in our pipelines. Before we set up a new pipeline in Azure DevOps, we have to decide where this application should run.

Self-hosted Agents

If our Analysis Services project is going to be deployed on an on-premises instance of SQL Server Analysis Services, we will have to run the service application from a location that can actually access that instance. This requires us to install the Agent software on an on-premises machine (self-hosting). Specifically, we need a Windows machine to host the Agent software, as TabularEditor.exe can only be executed on Windows. To download and install the Agent software locally, go to “Project settings” > “Agent pools” > Choose the “Default” pool > “New agent”:

From here, follow the instructions on the screen, to download, install and configure the agent. Once that’s done, you should see the self-hosted agent correctly registered within Azure DevOps, in the “Default” agent pool:

Microsoft hosted Agents

If using Azure Analysis Services, we can choose one of the Microsoft-hosted agent pools instead. These are agents that run on Microsoft Azure VMs, and as such, they will only be able to connect to instances of Analysis Services that are reachable from the cloud. You get a single hosted agent with 1800 minutes of pipeline executions pr. month for free with Azure DevOps. Take a look at the Service Plans to learn what other options are available.

Note, that we can still decide to use a self-hosted agent even if your entire BI solution is running in the cloud.

Making TabularEditor.exe available for an agent

In order for our pipelines to use TabularEditor.exe, we must make sure that the application is available on the machine hosting the agent. For a self-hosted agent, it’s only a matter of installing Tabular Editor on the same machine, and (optionally) registering the location of Tabular Editor within the path variable on that machine, to make executions easier. Since the self-hosted agent will run in the context of a local system service account, we need to make sure that we amend the PATH variable at the system scope. To do this, run the following command as an administrator, on the machine hosting the agent:

setx /M PATH "%PATH%;c:\Program Files (x86)\Tabular Editor\"

(making sure the specified path points to the location of Tabular Editor on that machine)

Tabular Editor on Microsoft-hosted agents

On Microsoft-hosted agents, we can’t preinstall any software, so one option to make sure that TabularEditor.exe (and its required dependencies) is available for our pipelines, is to copy the entire installation into source control (.exe file + the Microsoft DLLs). This will ensure that you can execute TabularEditor.exe on the Microsoft-hosted machine when running a build pipeline. However, executing TabularEditor.exe as part of the release pipeline will be a little more tricky, as there’s no default step that copies the files from source control into the working directory. This means that you would have to either include TabularEditor.exe and its dependencies as part of your build pipeline artifact, or manually create a step within your release pipeline to copy the files from source control. Luckily, there is a better way!

All Tabular Editor releases since version 2.8.4 contain a portable version (.zip) of Tabular Editor which includes the redistributable DLLs. So a much better way to ensure the availability of Tabular Editor on your build agent, is to simply download it directly from GitHub to the build agent. The following PowerShell script does just that. I recommend to explicitly specify the version number of Tabular Editor to download, to ensure a consistent deployment experience:

# Download URL for Tabular Editor portable:
$TabularEditorUrl = "https://github.com/otykier/TabularEditor/releases/download/2.12.4/TabularEditor.Portable.zip" 

# Download destination (root of PowerShell script execution path):
$DownloadDestination = join-path (get-location) "TabularEditor.zip"

# Download from GitHub:
Invoke-WebRequest -Uri $TabularEditorUrl -OutFile $DownloadDestination

# Unzip Tabular Editor portable, and then delete the zip file:
Expand-Archive -Path $DownloadDestination -DestinationPath (get-location).Path
Remove-Item $DownloadDestination

This technique can of course also be used for self-hosted build agents, provided they have internet access.

Your first Analysis Services build pipeline

Let’s create our very first build pipeline. To keep things simple, we will assume that you already have a source control repository set up, which contains a Model.bim or Database.json folder structure, representing a Tabular Model. We will set up a simple build pipeline that picks up the code and deploys it to a target server.

Since I don’t have any experience with YAML, I am going to stick to the classic pipeline editor in Azure DevOps. Here are the step-by-steps:

• On the Builds pane, click “New” and then choose “New build pipeline…”

• Click on the “Use the classic editor” at the bottom of the page.
• Configure the source. This should be pretty self-explanatory – at least when using Azure Repos. Click “continue”.

• Click “Empty job” near the top of the page, as we can’t really use any of the featured templates (Hey! Maybe someone should create a Tabular Editor template for the Azure Marketplace some day??)
• Give your build pipeline a name, for example “ASBuild”, and choose the agent pool it will be executed on. If you’re not planning on setting up a self-hosted build agent, simply use the hosted Azure Pipelines, which is free for up to 1800 minutes/month. The “vs2017-win2016” specification is fine for running Tabular Editor, but any Windows-based environment should work.

• Add a PowerShell script task by clicking the “+” button next to “Agent job 1”. Search for “PowerShell”. Make sure you don’t pick the “PowerShell on target machines” or “Azure PowerShell” options.
• Name the task “Download Tabular Editor”. Change type to “Inline” and paste in the script from above.

• Next, we’re going to define some variables, to hold information about our target server and database. Go to the “Variables” tab, add a variable called “ASConnectionString” and another variable called “ASDatabaseName”. Fill in the strings for both. Note, your connection string needs to hold the credentials of a user with admin access on Analysis Services. Remember to click the little “lock” icon, to protect the connection string. Note, if you don’t like to store credentials within Azure DevOps, it should be possible to use an Azure Resource Manager Service connection to impersonate a Service Principal during pipeline execution, in order to read the credentials from an Azure Key Vault, for example. This is a more complex topic that is outside the scope of this blog.

• Going back to the “Tasks” tab, add a Command Line script task, again by clicking the “+” button next to “Agent job 1”. Search for “command”.
• Name the task “Deploy AS model using Tabular Editor”. Use the following script:TabularEditor.exe "$(Build.SourcesDirectory)\AdventureWorks\Database.json" -D "$(ASConnectionString)" "$(ASDatabaseName)" -O -C -P -R -M -W -E -V

Here, we use $(Build.SourcesDirectory) to point to the root of our source repo, where we assume that the “Database.json” file is located within a folder called “AdventureWorks”. You’d have to modify this to suit your repo folder structure of course. By the way, here is a full list of predefined pipeline variables.

The switches provided at the end of the command (-O -C -P -R -M) instructs Tabular Editor to allow overwriting an existing database, while also deploying connections, partitions, roles and role members. Depending on your scenario, you may omit one or more of these switches. The lastmost switches -W -E -V are used for customising how errors are reported. These last three switches should always be used when deploying within an Azure DevOps pipeline, to make sure that errors are reported in a way that Azure DevOps can understand. More information on the switches available in TabularEditor is available here.

Note, you can put an @ sign in front of TabularEditor.exe if you don’t want the command to appear in the log (potentially exposing credentials as clear text). Output from Tabular Editor should still appear in the log. Another option is to map the credentials to an environment variable of the command line task and feed Tabular Editor arguments using the environment variable, instead of the Azure DevOps variables directly.

Finally, we’re ready to save the pipeline. If you dare, click “Save & queue”, sit back and enjoy your first automated deployment using Tabular Editor.

Next steps

In the next article, we will set up a more advanced build pipeline, that performs a schema check, runs the Best Practice Analyzer, performs a “validation deployment” and produces an artifact. The artifact is then handed over to the release pipeline, which is used to deploy the model to a UAT or production environment.

Once we have this in place, we will look into continuous integration, which is a matter of triggering the build automatically, when changes are made to the code repository.

Stay tuned!