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.

Comments

• Anonymous
  September 20, 2016
  great new feature. Will the analyze in Excel button continue to work seamlessly when using the Integrated Workspace option?
  • Anonymous
    November 30, 2016
    Yes, and we also show you the dynamic workspace port so that you can connect to the model for testing purposes with any tool as long as SSDT Tabular has the model loaded.
• Anonymous
  September 20, 2016
  wonderful :)
• Anonymous
  September 21, 2016
  The comment has been removed
  • Anonymous
    November 30, 2016
    Yes, we have some plans to improve the measure editing experience going forward, incl. the items you mentioned. No promises on a timeline, though.
• Anonymous
  September 22, 2016
  Please add capability to drag-and-drop measures between tables in SSDT Tabular or another way to quickly edit table location of measures.
  • Anonymous
    November 30, 2016
    Yes, this will be quite useful.
• Anonymous
  September 22, 2016
  The comment has been removed
  • Anonymous
    November 30, 2016
    Think of Integrated Workspace Mode as a SSDT Tabular internal feature. If you can install SSDT Tabular, which is supported on Windows 7 SP1 (x86 and x64), you'll also get the internal engine that SSDT uses. If you have issues with the installation, please open a support request. It should work.
• Anonymous
  May 29, 2017
  Very helpfull,Thanks,