Automate SSAS Tabular deployment using Tabular Editor 2.0

Learn how to combine Tabular Editor with continuous integration to optimize your development flow and avoid to loose roles, connection strings or partitions. The only thing you have to do is write some command lines to execute the application.

When working with continuous integration and automation it is a prerequisites to have easy access to build and deployment procedures through command line og scripting.

Looking at the current functionality availably with Team Services for SQL Server 2016 tabular models you will have to build using MSBuild and deploy the tabular model with scripts.

This works great – but currently it has some shortcoming in terms of possibilities to limit deployment. The two most challenging areas in my experience is in regards to roles and partitions.

When deploying your model with the current possibilities both the configuration of security on a table level and changes in members will be lost.

Some might argue that you should control all your memberships through Active Directory groups – but this is not always an option.

For different reason you might also have to add or remove roles without executing a deployment (processing might take a long time). Usually, when deploying a model with changes to roles, there should not be a reason to do any processing – often this is not the scenario since you might have implemented an automated partition strategy which you then might risk overriding.

Deployment with Tabular Editor

Earlier this week I posted more details about the deployment wizard which is shipping with the Tabular Editor. In addition to the interface the tool is also shipping with command line support for deployment tasks – supporting the same functionality as the wizard.

The best thing is that you only have to specify the path for your model.bim file and add information about the instance and database you want to deploy to:

If the database is not already deployed to the server it will ensure to create the model – Tabular editor is utilizing the createOrReplace action of the Tabular Model Scripting Language (TMSL).

By default deployment using through command line will not override  partitions, data sources nor roles. In case you want to override any of these items you will have to include some of the following switches in your command:

Continuous integration

When working with automation of deployment it is often necessary to wait for the a specific step to complete before proceeding with the next steps such a processing of the model and testing.

Tabular Editor is a Windows Form application and therefore it will run the application in a separate thread when executing it through command list. This will cause the job to return control and therefore not wait until the task is completed.

To avoid this situation you will have to include start /wait in the beginning of your Command Line call:

For more details, documentation of the feature or issues with the Tabular Editor I suggest visiting the GitHub page.

Bent Pedersen
Member since November 20, 2016

I am a Senior Business Analytics Architect at Kapacity A/S, where I have been working with enterprise customers on solutions with SSAS Tabular, Datazen and SQL for the last three years. During the past year, he concentrated on working with devOps, Automation, testing and simplifying development processes for Microsoft BI.

Comments

  • Dominik 5 months ago

    Hi Bent. Regarding this topic you gave a very interesting presentation at SQLKonferenz 2018 in Darmstadt. You mentioned that you are going to share you slides and the examples with the audience if interested. At the moment there are not available via the SQLKonfernez website. Can I ask you to share them maybe via another alternative channel. That would be great. Thanks a lot. Cheers, Dominik

Your email address will not be published. Required fields are marked *