Merge conflicts in tabular models

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 tabular models (for Power BI Premium, AAS or SSAS) 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.