In SQL Server Data Tools for SQL Server 2012, Tabular projects are not configuration aware. Through the UI, you have static sources and target databases. However, with some MS Build extensions and some XML and PowerShell knowledge, you can roll your own configuration-aware tabular projects. Many thanks goes to Cathy Dumas’ Blog entry: http://blogs.msdn.com/b/cathyk/archive/2011/08/10/deploying-tabular-projects-using-a-custom-msbuild-task.aspx
Requirements
- An Analysis Services Tabular Project
- SQL Server Data Tools
- MS Build Extension Pack http://www.msbuildextensionpack.com/
- Install the .NET 4.0 32-bit version to its default location.
- Custom targets file: custom_post_build.targets
- This article uses the following assumptions:
- The project uses Windows authentication to the SSAS Tabular instance.
- One connection to a source database is shared among all tables in the model.
Features
If you add this MS Build targets file to your SSAS tabular project, selecting the build option will accomplish the following:
- The tabular model will be built.
- The tabular model will be deployed to a server and database based on the selected configuration.
- The source connection string will be altered based on the selected configuration.
- The model will be processed immediately upon being deployed.
Setting up the Project
- In the project, set the database name for the deployment server. By default, the various configurations will add suffixes to this base name.
- Set up some configurations. Only names are needed. With a Tabular project, SSDT does not actually store different settings based upon the configuration.
- Save and close the project.
- Copy the custom_post_build.targets file to the project directory.
- Open the .smproj file in your favorite XML editor.
- Add the highlighted line to the end of the file:
- Save and close the .smproj file.
- Open the custom_post_build.targets file in your favorite XML editor.
- In the file, navigate to the if…else block:
- Copy the elseIf block as necessary to account for all configurations. See the highlighted comment in step 7. Ideally the else block will not be necessary.
- Change the connection string variables to the proper values for each configuration.
- $DBName – By default, the code adds a suffix rather than renaming the database. If you want to rename the database, change the += to an = character:
$DBName = “ThisIsANewDBName”; - $TabularName – Set this to the servernameInstance. You may need to use the DNS name for servername.
- $ConnectionString – This is the connection string for the source database. All connection strings must use the same type of source, e.g. ODBC, OLE DB connections, file connections, etc.
- $DBName – By default, the code adds a suffix rather than renaming the database. If you want to rename the database, change the += to an = character:
- Save and close the custom_post_build.targets file.
- Open the project again.
- Select a build configuration.
- Select Build → Build to build/deploy/process the model.
Detailed Walkthrough
- The MS Build Extension Pack is installed:
- Create a Tabular Project
- Open the project properties and set the base name of the database. In our case, we use TabularProject as the base name:
- Create the data model
- Open the configuration manager:
- Select new configuration:
- Name the new configuration:
- Create any configurations needed.Here we’re using Development, Test, and QA:
- Click Save All once you’ve created your model:
- Close the project:
- Copy the targets file to the project directory:
- Open the .smproj file in an XML editor:
- Add the tag at the end of the file:
- Save and close the .smproj file
- Open the custom_post_build.targets file in the XML editor:
- Find the per-configuration if…elseIf…else block:
- We have Development and Test blocks already. We need to add a QA block for our example:
- Now we have to alter the variables for each configuration.
- For Test we change the connection string:
- For QA we change the database name and the tabular server. Note the change of the += to + for $DBName:
- For Test we change the connection string:
- Save and cloe the custom_post_build.targets file
- Open the tabular project again
- Select a configuration. Here, we’re using Development:
- Select Build→Build
- Look for build succeeded in the lower left hand corner (circled in red here):
- Open SSMS
- Connect to the SSAS Server. In our case, localhostTABULAR:
- Verify that the database exists with the correct name:
- Open a new MDX query to verify that processing succeeded. You can use MDX or DAX in the query window:
- Congratulations! You have a tabular project that is configuration-aware, self-deploys, and self-processes.
Conclusion
Again, this method is an extension of the method used by Cathy Dumas. With a bit of XML knowledge, you can extend and improve this method to accomplish any number of things. It can be modified to use multiple data sources, changing the data source type, or adjusting the authentication to the Analysis Services instance. An enterprising sort might even be able to use it to hide or remove tables completely from the model. With this method, you can have quite a bit more control over deployment versus using the default.