Marco Russo has a nice post about the basics of configuring the workspace database server. You can use his post to learn how to set the tabular instance to use for the workspace database server for new and existing projects.
However, there’s more to picking a workspace database server than just picking an instance. You should think about whether you are picking a local or remote instance, and you should also think about the service account running the instance. Your choices here affect the behaviour of the tabular designer.
First, let’s compare the capabilities of local versus remote workspace database servers. The following table shows the differences in behaviour.
Next, let’s compare the capabilities of using a high privileged service account for the workspace database instance (such as yourself) versus using a low privileged account (say the default NT Service\MSSQLServerOLAPService account). The following table shows the differences in behaviour.
Finally, let’s consider your access to the workspace database server’s file system. Whenever you create or open a tabular project, a workspace database is saved in the server’s OLAP\Data directory. Over time, this folder may become bloated with temporary databases. From time to time you need to go in there and get rid of the stuff you don’t need any more. To do that, you need file system access. Therefore, it is best if you pick a workspace database server that you can clean up. This is not required, but your server administrator will thank you.
This is why I configure my workspace database server instance to be a local instance on my dev box that I run as myself. My dev box is a 64 bit box with 64 bit Office installed. This maximizes the amount of functionality available to me in the tabular designer. The only thing I can’t do is work with large data sets. Fortunately, I know a trick to import only a subset of data, so I don’t run into this problem too much.