Introducing Integrated Workspace Mode for SQL Server Data Tools for Analysis Services Tabular Projects (SSDT Tabular)
When you work with the model designer in SSDT Tabular, you are working with a temporary Analysis Services database that SSDT Tabular automatically loads on a workspace server. During the initial project creation, you must point SSDT Tabular to the desired workspace server, which must be a tabular Analysis Services instance that you can control with full permissions of a SSAS Administrator. In a typical configuration, you would deploy a workspace server on the local computer running SSDT Tabular. Yet, having to run SQL Server Setup in order to deploy a full SSAS instance in Tabular mode just for development purposes is burdensome. Now there is a better way—Integrated workspace mode!
The following screenshot shows the Tabular Model Designer dialog box displayed when creating a new tabular project by using the SSDT September release. Note the Integrated Workspace option. If you select it, SSDT Tabular no longer requires an explicit workspace server. Instead, it relies on its own internal Analysis Services instance.
In integrated workspace mode, SSDT Tabular dynamically starts its internal SSAS instance in the background and loads the database so that you can add and view tables, columns, and data in the model designer, and if you add additional tables, columns, relationships, and so forth, you are automatically modifying the workspace database, as well. Integrated workspace mode does not change how SSDT Tabular works with a workspace server and database. What changes is where SSDT Tabular hosts the workspace database.
For existing tabular projects that currently use an explicit workspace server, you can switch to integrated workspace mode by setting the Integrated Workspace Mode parameter to True in the Properties window, which is displayed when you select the Model.bim file in Solution Explorer, as highlighted in the following screenshot. Note that the Integrated Workspace Mode option does not let you configure any other workspace settings. SSDT Tabular uses default values for Workspace Database, Workspace Retention, and Workspace Server.
The Workspace Database and Workspace Server settings let you discover the name of the temporary database and the TCP port of the internal SSAS instance where SSDT Tabular hosts this database. By using this information, you can connect to the workspace database with SSMS or any other tool—as long as SSDT Tabular has the database loaded. The Workspace Retention setting, on the other hand, specifies that SSDT Tabular keeps the workspace database on disk, but no longer in memory after a model project is closed. This ensures a good user experience while consuming less memory than if the model was kept in memory at all times. If you want to control these settings, set the Integrated Workspace Mode property to False and then provide an explicit workspace server. An explicit workspace server would also make sense if the data you are importing into a model exceeds the memory capacity of your SSDT workstation. You can continue to use your existing workspace server and this is still fully supported.
The integrated workspace server is basically equivalent to the Developer edition of SQL Server Analysis Services, so you can try out advanced features, such as DirectQuery mode, which are typically only available with the Enterprise edition. Also note that SSDT Tabular will ship the latest version of the Analysis Services engine with every monthly release, so you automatically get the latest updates and new capabilities. However, keep in mind that the ultimate deployment target in your production environment must support the capabilities you use in the model. For example, if your production server runs Standard edition, then you will not be able to deploy a model that uses Enterprise-only features. If you use integrated workspace mode, make sure you test the deployment on a reference server early on to ensure your model is compatible with your production servers.
Another aspect worth mentioning is that the integrated workspace server is a 64-bit Analysis Services instance, while SSDT Tabular runs in a 32-bit environment of Visual Studio. Hence, if you are connecting to special data sources, make sure you install both the 32-bit and 64-bit versions of the corresponding data providers on your workstation. The 64-bit provider is required for the 64-bit Analysis Services instance and the 32-bit version is required for the Table Import Wizard in SSDT Tabular.
And that’s it for a brief introduction of Integrated Workspace Mode. We hope you find this new capability useful. Give it a try, send us your feedback through Microsoft Connect, community forums, or as comments to this blog post, and let us know what other capabilities you would like us to add to SSDT Tabular. As always, stay tuned for even more improvements in upcoming monthly SSDT releases.