Merge conflicts in SSAS Tabular

I sometimes find working with Visual Studio's projects a challenge in multi-developer environments, because each project type seems to have its own vulnerability to Git merge conflicts. In the case of SSAS tabular, I've found two issues to be a regular source of conflicts:

1. If you're using an OAuth connection to a source data store (e.g. an instance of Azure SQL DB), every time you update your connection you get a new expiry timestamp.

   Two devs working on the same model in different feature branches get independently-updated timestamps, so the second dev merging into main almost always gets a conflict.

2. While you're working, Visual Studio frequently reorders the contents of your model's underlying .bim file.

   This has the result that pull requests are not only conflict-prone, but too noisy for meaningful review.

   

   In this example, almost the “changes” have been made by VS, with only one real, substantive difference – but how to find it?

The order of components in your .bim file doesn't matter to Visual Studio, and it won't object to expired OAuth timestamps either. So here's a solution:

• Before you commit you changes to Git…
• …sort the file into a consistent order…
• …and replace the timestamp.

Achieving that is the subject of this post 😀.

A Git hook is a local script which runs automatically at a specific point in a Git workflow. Hook scripts are located in your repository's /.git/hooks folder and have names which indicate when they get run. For example, pre-commit is a script which is run automatically when you issue a git commit command, before the commit actually takes place.

By default, most Git repos come with a hooks folder full of .sample scripts – to turn your pre-commit.sample file into a pre-commit hook, remove the extension to name it pre-commit. To install a custom pre-commit hook, save your own pre-commit file in the hooks folder.

Here's my pre-commit hook script:

#!C:/Program\ Files/Git/usr/bin/sh.exe
 
# sort tabular models consistently to avoid merge conflicts
powershell.exe -NoProfile -ExecutionPolicy Bypass -File ".\HookScripts\Sort-TabularModel.ps1"

In case you're not familiar with UNIX-style scripts:

• Line 1 is a shebang – it tells the system the location of the interpreter to run this script. I've specified C:\Program Files\Git\usr\bin\sh.exe, an interpreter included with my Git for Windows install
• Line 3 is a comment
• Line 4 calls PowerShell to execute a script located at HookScripts\Sort-TabularModel.ps1, in the root of my repository.

I could put the whole script in /.git/hooks, but that folder is only stored locally. That means that anything inside it is harder to share, and easier to lose – so I keep the script inside the repository, under version control.

The pre-commit hook script above uses PowerShell to execute Sort-TabularModel.ps1, located in my repository's HookScripts folder. The script looks like this:

$scriptFolder = $MyInvocation.MyCommand.Path | Split-Path
Set-Location $scriptFolder
Import-Module $scriptFolder\HookFunctions\HookFunctions.psm1 -Force
 
$f = Resolve-Path -Path "..\SsasProjects\MyTabularModel\Model.bim"
ConvertTo-OrderedModel -ModelFile $f
Invoke-Utility git add $f  # re-stage file after reordering

On line 6, the script calls ConvertTo-OrderedModel on the specified .bim file. This modifies the file, potentially sorting it into a different order. These new changes haven't been staged for git (using git add) – line 7 uses Invoke-Utility to call git add again, making sure that the new modifications are included in the commit.

Both ConvertTo-OrderedModel and Invoke-Utility are PowerShell functions defined in my HookFunctions.psm1 module, imported on line 3.

ConvertTo-OrderedModel sorts sections of the model into alphabetical order:

• tables, relationships, roles, expressions and annotations are sorted by name
• columns, hierarchies, measures and partitions are sorted, also by name, within each table.

In addition, the function overwrites any OAuth credential expiry with a specific date and time – this ensures that, as far as git is concerned, the value never changes (so never conflicts).

The .bim file stores the tabular model as a JSON object – as a result, the script can convert it into a PowerShell object, sort it, and re-serialise it using (mostly) standard PowerShell functions.

Resolving merge conflicts can be tricky and time-consuming. Doing so when your tooling creates conflicts is doubly annoying! The approach I've described here uses a git pre-commit hook to sort SSAS tabular models into a consistent order.

This approach reduces the likelihood of experiencing a conflict, but it doesn't eliminate it. If two developers make independent changes which are sorted into the same place in the file, a real conflict occurs (but should be simple to resolve). If two developers modify the same measure, independently and at the same time, you have a process conflict too 😉.

Merge conflicts resulting from tooling behaviour are experienced in other project types, including SSDT (.sqlproj) and SSIS (.dtproj) project files. Conflicts can be reduced when working with these projects in a similar way, but the mechanics of sorting are different because the affected files are XML, not JSON. Avoiding merge conflicts in SSDT projects is the subject of a separate post.