SSAS Connection String when deploying across multiple environments?

Question

I have three different environments; Development, Test, and Production.

These three SSAS environments connect to three different SQL Server instances.

The server, though, is hard coded in the SSAS data source so when you deploy, the instance is broken because it points to the wrong, inaccessible, SQL server.

How can you configure the SSAS data source connection string so that its not overwritten when you deploy, OR, how can you make it variable so that it looks up the correct value for the environment its been deployed to?

I've been manually editing the .asdatabase file before deployment to each environment, but this is an unacceptable violation of change control procedures, so I've got to get a more suitable mechanism in place. Maybe there is a very easy solution to this, but if so, I haven't figured it out yet.

Answer 1

One approach perhaps is upon deploying your SSAS cube, you can run a XMLA script that sets the SQL Server data source within the SSAS connection string. Another approach is that you can set the source to a System DSN with the same name in all environments and adjust at the ODBC Data Source control panel. For various reasons, I do prefer the former but the latter can work.

Answer 2

In visual studio 2019's version of SSAS / SSDT, it looks like this gets sneakily changed by the configuration manager drop down when its changed. This is kept in the .dwproj file. Theres an entry like this for each configuration.

<Configuration>
  <Name>Prod</Name>
  <Options>
    <ConnectionMappings>
      <ConfigurationSetting>
        <Id>SII DW</Id>
        <Name>SII DW</Name>
        <Value xsi:type="xsd:string">Provider=SQLNCLI11.1;Data Source=SERVER;Integrated Security=SSPI;Initial Catalog=SII_DW</Value>
      </ConfigurationSetting>
    </ConnectionMappings>
    <ConnectionSecurityMappings>
      <ConfigurationSetting>
        <Id>SII DW</Id>
        <Name>SII DW</Name>
        <Value xsi:type="xsd:int">0</Value>
      </ConfigurationSetting>
    </ConnectionSecurityMappings>
    <DeploymentServerEdition>Standard</DeploymentServerEdition>
    <DeploymentServerVersion>13.0</DeploymentServerVersion>
  </Options>
</Configuration>

Answer 3

This has been driving me a little nuts recently but the way I've managed to do it, is deploy to development using the Deployment Wizard (I'm using tabular so I have to use v17). Then within SSMS, I connect to my development Analysis Services instance, right click on the data source and change the connection properties there and then process manually.

Previously if I did in in SSDT it wasn't working for some unknown reason so far (I will get to the bottom of it I hope).

I don't feel like this is the best solution though, and I tried using the deployment script but that didn't work either.