Getting the tabular model right is crucial to delivering a valuable data platform.

The semantic layer should provide a layer of abstraction on top of the Warehouse that will be easier to understand and navigate for the end-users. This can be achieved by implementing best practices, such as hiding unnecessary columns (e.g. surrogate keys) and appropriately naming those that remain.

Whilst this is known, there’s nothing stopping you from making a messy model.
Visual Studio isn’t going to throw an error when a column has a janky name, as it isn’t an issue from a technical perspective. Perhaps the end user will feed back to say it’s not intuitive, or worse yet they’ll just learn to live with it. These sorts of minor nit-picks can add up to make the solution a hassle to use.

Ideally we’d like to stop this from occurring in the first place, but how can that be done?
In truth, many of the tweaks that make a tabular model user-friendly are subjective changes, and therefore difficult to enforce programmatically. However, we can attempt to at least reduce these bad practices as well as introducing some uniformity into our delivery with the help of Tabular Editor.

Tabular Editor

Tabular Editor is a tool for interacting with tabular analysis services model files.
It allows you to make changes to the model in a quick and efficient way, and also provides some extra functionality above what the standard SSDT interface offers.

A key tool it offers is it’s Best Practice Analyser (BPA).

Using this, a set of best practice rules can be defined and then tested against the model file.
This then gives us the ability to begin regulating the content of our model file. Great!

We can begin to ensure certain practices are enforced in our models, such as:

– Hiding all primary key/foreign key fields
– Avoiding CamelCase column names
– Using the DIVIDE function for DAX divisions

The rules are captured in a JSON file, with the logic defined using Dynamic LINQ expressions. Some examples of building rules is available here.

Microsoft have been encouraging the adoption of this tool as part of model development in their blog, and have even provided their own set of rules that serves as a great starting point here. Of course, the rules can be updated in line with your preferences, providing plenty of flexibility.

This functionality is a great addition to the model developers toolbelt, however rather than conducting these checks manually, let’s now take it a step further and look at automating the process. This can be achieved by integrating the validations within the Build Pipeline.

Implementation

Tabular Editor can be conveniently run from the command line, so with a couple of clever PowerShell scripts, alongside the model file and rules file, the process can be run quite easily on an Azure Pipeline agent.

Let’s take a look at how to implement this.

Overview

The whole process will be executed on an Azure Pipeline agent as part of the build pipeline.

At a high level, the process will be:

1. Gathering the model file, rules file, and Tabular Editor tool into one folder (to keep file paths simple).
2. Executing the best practice checks against the model file, supplying the rules file.
3. If all checks pass, publish the model file as a pipeline artifact.

The artifact can then be used to deploy the model to an Analysis Services instance, but I won’t cover that here.

Pipeline Steps

First up is to create the pipeline steps to achieve this. These will be defined in YAML.

I’ll explain the planned steps first and then provide the code definition after.

Note: There are a couple of PowerShell scripts referenced in here that will be detailed further afterwards.

Step 1: ‘Collect Analysis Services Model & Rules’

This step simply copies all the elements we’ll be needing into a single folder (AnalysisServicesBuild).

Required elements:

– Tabular model file
– Best practice rules file
– PowerShell script: Get-TabularEditor
– PowerShell script: Analyse-ASModel

Adjust the file paths accordingly if you are storing your elements in a different folder structure.

Step 2: ‘Get Tabular Editor’

This step runs the PowerShell script: Get-TabularEditor.ps1

This will download Tabular Editor into our folder, ready for use.

Note that the version of Tabular Editor can be specified at this point.

Step 3: ‘Build Analysis Services Model’

This step runs the PowerShell script: Analyse-ASModel.ps1

This will run the Best Practice Analyser against the model file and output the results.

Step 4: ‘Delete Excess Files’

Assuming the checks have run successfully, this step will prepare the folder for publishing by deleting everything except for the model file.

Step 5: ‘Publish Artifact’

This step publishes the validated model file as an artifact, ready for use in the deployment pipeline.

Code

jobs:
- job: BuildAnalysisServices
  displayName: Build Analysis Services Model
  pool: 
    vmImage: windows-latest
  steps:

  - task: CopyFiles@2
    displayName: 'Collect Analysis Services Build Resources'
    inputs:
      SourceFolder: 'source/AnalysisServices'
      Contents: |
       Model.bim
       Scripts/Get-TabularEditor.ps1
       Scripts/Analyse-ASModel.ps1
       Rules/BPA-Rules.json
      TargetFolder: '$(System.DefaultWorkingDirectory)/AnalysisServicesBuild'
      FlattenFolders: true
  
  - task: PowerShell@2
    displayName: 'Get Tabular Editor'
    inputs:
      filePath: '$(System.DefaultWorkingDirectory)/AnalysisServicesBuild/Get-TabularEditor.ps1'
      arguments: >
        -Version "2.12.4"
  
  - task: PowerShell@2
    displayName: 'Build Analysis Services Model'
    inputs:
      filePath: '$(System.DefaultWorkingDirectory)/AnalysisServicesBuild/Analyse-ASModel.ps1'
  
  - task: DeleteFiles@1
    displayName: 'Delete Excess Files'
    inputs:
      sourceFolder: '$(System.DefaultWorkingDirectory)/AnalysisServicesBuild'
      contents: |
        TabularEditor
        Analyse-ASModel.ps1
        Get-TabularEditor.ps1
        BPA-Rules.json
        Model-BPA.xml

  - publish: '$(System.DefaultWorkingDirectory)/AnalysisServicesBuild'
    displayName: 'Publish Artifact'
    artifact: AnalysisServicesArtifact

PowerShell Scripts

Two PowerShell scripts are executed as part of the pipeline.

Script: Get-TabularEditor

This script will download the specified version of Tabular Editor from GitHub.
The version is parameterised so that it can be easily updated from within the pipeline definition.

Code

# Script to get Tabular Editor (portable edition)
# Tabular Editor allows applying build rules to Analysis Services models

Param (
  [string] $Version = '2.12.4'
)

$WorkingDir = join-path (get-location) "/AnalysisServicesBuild"

# Download URL for Tabular Editor portable (note: Version parameter):
$TabularEditorURL = "https://github.com/otykier/TabularEditor/releases/download/$Version/TabularEditor.Portable.zip" 

# Download destination:
$DownloadPath = "$WorkingDir/TabularEditor.zip"

# Create Folder to contain Tabular Editor executable and files
$TabularEditorPath = "$WorkingDir/TabularEditor/"

# Download from GitHub:
Invoke-WebRequest -Uri $TabularEditorURL -OutFile $DownloadPath

# Unzip Tabular Editor portable into the folder
Expand-Archive -Path $DownloadPath -DestinationPath $TabularEditorPath

# Delete the .zip
Remove-Item $DownloadPath

Analyse-ASModel

This script is where it all comes together.
The Best Practice Analyser is executed, passing in the model and rules files, and any violations are spewed back out.
The ‘-V’ in the argument list ensures they are output in a way that Azure Pipelines can understand.

Code

# Use Tabular Editor to analyse Analysis Services model

# Get model path
$Dir = "/AnalysisServicesBuild"
$WorkingDir = join-path (get-location) $Dir
$ModelPath = "$WorkingDir/Model.bim"

# Best Practice Rules file
$BPAPath = "$WorkingDir/BPA-Rules.json"

# Tabular Editor executable location
$TabularEditorExe = "$WorkingDir/TabularEditor/TabularEditor.exe"

# Argument list to pass into command line
$ArgList = "`"$ModelPath`" -AX `"$BPAPath`" -V"

# Run Best Practice Analysis
$p = Start-Process -filePath $TabularEditorExe -Wait -NoNewWindow -PassThru -ArgumentList $ArgList
exit $p.ExitCode

Example Run

The result of the checks will be viewable against the ‘Build Analysis Services Model’ task in the pipeline.
Here’s an example:

As you can see, in this example there were several violations. It would then be a task for the model developer to resolve these before merging the code.

Controlling Build Outcome

A final thing to note is the ‘Severity’ property within the rules file.
Each rule defined in the file has an associated severity that you can set to determine what happens when a violation of that rule is encountered.

The options are as follows:

• Severity: 3 – The build will fail.
• Severity: 2 – The build will succeed with warnings.
• Severity: 1 – The build will succeed, but will still post the violation in the output.

Summary

Overall, I like this solution as it helps encourage some structure and quality when building tabular models, whilst allowing you to flexibly change the rules.

It’s also positive to see Microsoft endorsing the tool – no doubt their rules file will mature and it’s likely that Tabular Editor will continue to be a key tool in building effective tabular models.