Analysis Services Project Configurations

If you’ve regularly found yourself changing the target server property and the data source connection string before you deploy your cube to another environment, it’s simply because you’re not aware of a feature that hasn’t been publicized well. (I’m just judging based on my own ignorance of this feature until recently. However, now that I go look, both this article and Teo Lachev’s book mention Configuration Manager, though they don’t mention the exact list of settings that are configurable.)

Visual Studio Configurations
Visual Studio has a configurations feature so that you can specify settings for different build types. In .NET projects, it’s usually used to compile debug versus release builds. For business intelligence projects, it can be used to setup a configuration per deployment target environment:

What settings can be saved per configuration? Obviously, all the settings in the Project Properties dialog:

What I Didn’t Know…
What I didn’t know until recently was that there are other settings (like the data source connection strings) in the Analysis Services project which can be controlled per configuration. Basically, any setting which is environment specific is “configurable.” I’ll enumerate those settings below.

The other tidbit to discuss is where these settings are stored. Some are stored in the .dwproj file and others are stored in the .dwproj.user file. The settings in the dwproj file can be considered “shared” because this file is checked into source control and shared among team members. The settings in the dwproj.user file can be considered “private” as this file often not checked into source control or shared. The counterargument is that the dwproj.user file holds the TargetServer setting which is a crucial part of setting up separate Visual Studio configurations per environment, so you may want to consider checking in that file.

Configurable Settings
The following table enumerates all the settings controlled per configuration, so you should be sure to review each of them when you setup a new deployment environment.

Setting Name

Storage File

Where To Edit Setting

ConnectionMappings

dwproj

Double click data source… edit connection string. The connection string is saved with the SQL security username and password removed.

ConnectionProviderMappings

dwproj

Double click data source… change provider

ConnectionSecurityMappings

dwproj

Double click data source…  this setting indicates whether SQL security username and password was removed when saved to disk. The username and passwords are stored in the dwproj.user file (see the UserIDs and UserPasswords setting below.)

CubeKeyErrorLogFiles

dwproj

Cube properties… Advanced… ErrorConfiguration… (custom)… KeyErrorLogFile

CubeStorageLocations

dwproj

Cube properties… Configurable… StorageLocation

DeploymentMode

dwproj.user

Project Properties dialog

DeploymentServerEdition

dwproj

Project Properties dialog

DimensionKeyErrorLogFiles

dwproj

Dimension Properties… Advanced… ErrorConfiguration… (custom)… KeyErrorLogFile

MeasureGroupKeyErrorLogFiles

dwproj

Measure Group Properties… Advanced… ErrorConfiguration… (custom)… KeyErrorLogFile

MiningStructureKeyErrorLogFiles

dwproj

Mining Structure Properties… Advanced… ErrorConfiguration… (custom)… KeyErrorLogFile

OutputPath

dwproj

Project Properties dialog

PartitionKeyErrorLogFiles

dwproj

Partition Properties… Advanced… ErrorConfiguration… (custom)… KeyErrorLogFile

PartitionRemoteServers

dwproj

Only applicable when using remote partitions

PartitionStorageLocations

dwproj

Partition properties… Configurable… StorageLocation

ProcessingOption

dwproj.user

Project Properties dialog

RemovePasswords

dwproj

Project Properties dialog

ReportActionPaths

dwproj

Cube Actions tab… properties on a report action… Path property

ReportActionServers

dwproj

Cube Actions tab… properties on a report action… ReportServer property

StartObject

dwproj.user

Project Properties dialog

TargetDatabase

dwproj.user

Project Properties dialog

TargetServer

dwproj.user

Project Properties dialog

TransactionalDeployment

dwproj.user

Project Properties dialog

UserIDs

dwproj.user

Double click data source…  set the SQL security username

UserPasswords

dwproj.user

Double click data source…  set the SQL security password

Visual Studio configurations are very helpful for SSAS. In fact, I would call it a best practice to use them.

Configurations vs. Deployment Wizard
When is it appropriate to use Visual Studio configurations, and when is it appropriate to use the Analysis Services Deployment Wizard? I would recommend you always use Visual Studio configurations, and if you’re doing a more advanced deployment where the Deployment Wizard would help, then you can use it, too. (The Deployment Wizard has some advanced deployment options like retaining partitions on the server, or retaining security roles on the server, etc.)

The Deployment Wizard works very well together with Visual Studio configurations. When you build or deploy your cube from Visual Studio, it writes files to the bin directory. The “bin\.configsettings” file contains all your Analysis Services project configurations, and that file drives the defaults on the “Specify Configuration Properties” screen in the Deployment Wizard. If you’ve properly setup your Visual Studio configurations, then you’ll need to do less work in the Deployment Wizard.

Visual Studio Configurations for SSIS and SSRS
Integration Services has a project properties dialog that lets you configure settings regarding debugging and regarding the building of the deployment manifest for use with the Package Installation Wizard. (And thanks to BIDS Helper, it has settings regarding deploying packages directly from BIDS.) And obviously those settings from the project properties dialog can be stored per Visual Studio configuration. SSIS projects do also have a Data Sources folder in Solution Explorer, and the connection strings are stored per Visual Studio configuration. However, the Data Sources folder is more trouble than it is worth. (See the comments from June 22, 2007 at the bottom of this post of Jamie Thompson’s.) You should use package configurations to control connection strings, and package configurations are unrelated to Visual Studio configurations.

Reporting Services has a project properties dialog that lets you configure settings regarding deploying of reports. These settings can be stored per Visual Studio configuration. However there are no other settings that are stored per configuration. Unfortunately, connection strings of the data sources in the Shared Data Sources folder are not controlled per configuration. (But the OverwriteDataSources setting in the project properties dialog prevents this from hurting you much. You just have to manually configure the data source in Report Manager the first time you deploy that data source, and then it will not be overwritten on subsequent deployments.)

Tabular models – 3/14/2014 Update

In Analysis Services 2012, configurations don’t work as described above for Tabular models (but they do continue work for Multidimensional models as described). This Connect feedback item requests this functionality be added for Tabular. Note Cathy Dumas posted a workaround.