Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using Visual Studio Configurations in an Analysis Services Tabular 2016 Project


By:   |   Read Comments   |   Related Tips: > Analysis Services Development

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

One of the new features in Analysis Services 2016 is the ability to use Visual Studio Configuration in your Tabular project. Multidimensional projects already had this capability for some time, but now Tabular can benefit from this as well. In this tip, we’ll show you how you can use configurations to easily switch between servers as a target for deployment.

Solution

You can use Visual Studio configuration to create different sets of property values. When you change to a different configuration, the properties will change as well. This allows you for example to quickly change between servers when you want to deploy your Tabular model. Without configurations, you would have to type the server name in the model properties every time you want to change the destination of a deployment.

Visual Studio configurations are – as the name implies – only visible in Visual Studio. When you deploy your Tabular model to an Analysis Server (SSAS) instance, there won’t be any trace of them. They are a construct in Visual Studio to simplify builds and deployments.

Test set-up

Before we can create configurations, we need a Tabular project of course. You can follow the steps outlined in the tip Using Calculated Tables in Analysis Services Tabular 2016 for Aggregate Tables – Part 3 to create a Tabular project using the Wide World Importers data warehouse.

SSAS Tabular model for WWI DW

Creating a New Configuration

By default there’s only one configuration present in a Visual Studio project: Development. You can quickly spot it in the solutions configurations drop down in the standard toolbar.

configuration dropdown

Let’s create a new configuration. This configuration will hold the name of the production server, so if we want to deploy to production instead of development we need to switch between configurations. Right-click on the model and go to properties. There we can go to the Configuration Manager.

open configuration manager in properties dialog

In the Active solution configuration dropdown, you can click on New to create a new configuration.

create new config

In the pop-up, you need to specify a name. If you want, you can copy settings over from an existing configuration.

specify config name

After clicking OK, the Production configuration will be created and is now the active configuration.

configurations dropdown

Link Properties to the Configuration

When we take a look at the deployment properties, we can see they have all the same default values as the Development configuration. Let’s change a couple to see the effect of a configuration.

  • Processing Option will become Do not Process instead of Default.
  • The server name will be a point instead of localhost (both are the same though).
  • We will change the name of the database to WWI_Prod, just to see the effect. In normal scenarios you typically don’t change the name.
  • We’re also going to change the name of the model in both development and production to WWI.

I have noticed changing properties for different configurations doesn’t always go as smooth as expected. When you’re in the properties window, you can change between configurations using the dropdown. However, the property values do not switch when you change configuration; only the properties for the active configuration are shown. This is tested behavior in SSDT 2015 and SSDT 2017. The following animated gif shows the problem:

animated gif, changes environments doesn

So how do you change the properties? You can only change the properties for the active configuration. Follow these steps:

  1. You close the property pages.
  2. You set the configuration using the dropdown in the toolbar, thus changing the active configuration.
  3. You open the properties of the project again and you make your changes.
  4. Then you close the dialog again, change the active configuration and change the properties again if you need to.

Here are the properties when Development is the active configuration:

dev active env

And when Production is the active configuration:

prod active env

Deploy using Different Configurations

It’s time to test the configurations. When deploying with Development as the active configuration, we get the following model on the Analysis Services instance:

deploy for development environment

As you can see the data is already processed. When we change the configuration to Production and deploy the solution, we can see another model is added to the server:

deploy for production environment

This time, the model cannot be browser because the deployment was set to “Do Not Process”.

Conclusion

Using Visual Studio configurations, you can quickly change project properties. This can help you in easily deploying your SSAS Tabular solutions to different servers.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools