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:
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.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 😀.
Before you commit your changes to Git
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.
I keep a copy of the pre-commit
file itself in the same location – it doesn't do anything there, but it's in a safe place. To install the hook in a fresh clone of the repository, new collaborators can copy the file into their own /.git/hooks
folder.
Sort the file into a consistent order
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.
In repositories containing multiple SSAS tabular projects, I put lines 6 and 7 into a foreach
loop, sorting every project at pre-commit. This doesn't mean I modify every .bim
file, because any file I haven't recently edited is already in order from its last commit – sorting it again makes no further changes.
Both ConvertTo-OrderedModel
and Invoke-Utility
are PowerShell functions defined in my HookFunctions.psm1
module, imported on line 3.
ConvertTo-OrderedModel
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.
Summary
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.
Code: Scripts from this post are available on
Github, in the git-pre-commit-hooks folder of my “community” repo.
Share: If you found this article useful, please share it!