The purpose of this document is to document a viable way to deploy tabular models from say development to test to production.
The lifecycle of a tabular project can be illustrated as follows :
All development of tabular models happens with Visual Studio where the developer uses a local instance as workspace.
A workspace database is created on the Analysis Services instance, specified in the Workspace Server property, when you create a new Business Intelligence project by using one of the tabular model project templates in SQL Server Data Tools. Each tabular model project will have its own workspace database. You can use SQL Server Management Studio to view the workspace database on the Analysis Services server. The workspace database name includes the project name, followed by an underscore, followed by the username, followed by an underscore, followed by a GUID.
Once chosen a workspace cannot easily be changed.
Once the development is over you can deploy the project to a test instance. The instance is specified using the properties of the project:
You can change the Server name, the database name and the Cube name. By default the cube name will always be “Model”. I advise you to change it to a more meaningful name as it is what will appear in client tools and AMO.
More information about the different deployment options from SSDT : http://msdn.microsoft.com/en-us/library/hh230970.aspx
Once the solution has been deployed to the test environment using Visual Studio (SQL Server Data Tools) you need to proper way to deploy into production is to use the cube deployment wizard.
The wizards can be used in 2 ways :
· Interactively : generate output based on the input files and on the user input during the use of the wizard
· Form the command prompt : generate outputs based on the input files and on switched specified for running the wizard.
The default location (for SQL Server 2014) of the wizard is : C:Program Files (x86)Microsoft SQL Server120ToolsBinnManagementStudioMicrosoft.AnalysisServices.Deployment.exe
5 The process is
1. Run the Deployment wizard
2. Find the database files needed for input
a. <project name>.asdatabase : declarative definitions of all the objects
Other files that you might find in the folder are :
a. <project name>.deploymenttargets : name of SSAS instance and database where objects will be created
b. <project name>.configsettings : environment specific settings as connection strings, storage locations…
c. <project name>.deploymentoptions : deployment options as transactional deployment, processing options…
3. Choose where to deploy to and the name of the database to deploy to
4. Choose the options for deployment
a. It’s a good idea to deploy roles but not to overwrite members !
6. The next window will be filled only if you had special configuration options in your project, like storage location or impersonation account. Depending on your setup you might want to add a production account in stead of the user account running the script.
7. Next step is to choose processing options
8. You can the either deploy immediately – if you have access – or create a script to give to your DBAs.