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

  1. In the project, set the database name for the deployment server. By default, the various configurations will add suffixes to this base name.
  2. Set up some configurations. Only names are needed. With a Tabular project, SSDT does not actually store different settings based upon the configuration.
  3. Save and close the project.
  4. Copy the custom_post_build.targets file to the project directory.
  5. Open the .smproj file in your favorite XML editor.
  6. Add the highlighted line to the end of the file:
    Text Example
  7. Save and close the .smproj file.
  8. Open the custom_post_build.targets file in your favorite XML editor.
  9. In the file, navigate to the if…else block:
    Text Example
  10. 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.
  11. Change the connection string variables to the proper values for each configuration.
    1. $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”;
    2. $TabularName – Set this to the servernameInstance. You may need to use the DNS name for servername.
    3. $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.
  12. Save and close the custom_post_build.targets file.
  13. Open the project again.
  14. Select a build configuration.
  15. Select Build → Build to build/deploy/process the model.

Detailed Walkthrough

  1. The MS Build Extension Pack is installed:MS Build Extension Pack is Installed
  2. Create a Tabular Project
    Tablar Project
  3. Open the project properties and set the base name of the database.  In our case, we use TabularProject as the base name:
    Open the project properties
  4. Create the data model
  5. Open the configuration manager:
    Open the configuration manager
  6. Select new configuration:
    select new configuration
  7. Name the new configuration:
    name the new configuration
  8. Create any configurations needed.Here we’re using Development, Test, and QA:
    Creater any configurations needed. Here we're using Development,Test, and QA
  9. Click Save All once you’ve created your model:
    Click Save
  10. Close the project:
    Close the project
  11. Copy the targets file to the project directory:
    Copy the targets file to the project directory
  12. Open the .smproj file in an XML editor:
    XML editor
  13. Add the tag at the end of the file:
    Add the tag
  14. Save and close the .smproj file
  15. Open the custom_post_build.targets file in the XML editor:
    Open the custom post
  16. Find the per-configuration if…elseIf…else block:
    Find the per-configuration
  17. We have Development and Test blocks already. We need to add a QA block for our example:
    Development and Test blocks already
  18. Now we have to alter the variables for each configuration.
    1. For Test we change the connection string:
      Text Example
    2. For QA we change the database name and the tabular server. Note the change of the += to + for $DBName:
      Text Example
  19. Save and cloe the custom_post_build.targets file
  20. Open the tabular project again
  21. Select a configuration. Here, we’re using Development:
    Select a Configuration
  22. Select Build→Build
    Select Build
  23. Look for build succeeded in the lower left hand corner (circled in red here):
    Look for build succeeded
  24. Open SSMS
  25. Connect to the SSAS Server. In our case, localhostTABULAR:
    SSAS Server
  26. Verify that the database exists with the correct name:
    Verify that the database exists
  27. Open a new MDX query to verify that processing succeeded.  You can use MDX or DAX in the query window:
    MDX Query
  28. 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.