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:

  1. #!C:/Program\ Files/Git/usr/bin/sh.exe
  2.  
  3. # sort tabular models consistently to avoid merge conflicts
  4. 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.

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:

  1. $scriptFolder = $MyInvocation.MyCommand.Path | Split-Path
  2. Set-Location $scriptFolder
  3. Import-Module $scriptFolder\HookFunctions\HookFunctions.psm1 -Force
  4.  
  5. $f = Resolve-Path -Path "..\SsasProjects\MyTabularModel\Model.bim"
  6. ConvertTo-OrderedModel -ModelFile $f
  7. 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 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.

  • 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!