by Paul Turley, Mentor, SolidQ

Components of a Tabular Model Project

The tabular project consists of very few files with almost all critical information stored as XML elements in the Model.bim file.  Unlike a multidimensional project, where each object is defined in a separate file, in a tabular project, everything is contained in this one file.  During the Build process, which is implicitly executed when the model is deployed or processed, rather than needing to assemble all the pieces from individual dimension and cube definition files, everything is already in the right format.

Purpose of  the Workspace Database

 A challenge in multidimensional solution design is that all of the design work is performed disconnected with the assumption that data will conform to certain rules when the objects are processed.  The typical outcome is that data quality issues such as duplicate and missing key values are discovered as all of the source data are loaded when the database is processed.  By contrast, source data is loaded into the workspace database when the tabular model is designed from the start. 

What’s in a Name? – the Workspace Database Name


Where Should the Workspace Database Reside?

Sometimes the direction we get in the official documentation are a little vague, providing more options than clear guidance.  The on-line Help topic titled "Workspace Database (SSAS Tabular)" reads as follows:
The model workspace database typically resides on localhost or a local named instance of an Analysis Services server. You can use a remote instance of Analysis Services to host the workspace database, however, this configuration is not recommended due to latency during data queries and other restrictions. Optimally, the instance of Analysis Services that will host the workspace databases is on the same computer as SQL Server Data Tools. Authoring model projects on the same computer as the Analysis Services instance that hosts the workspace database can improve performance.

Now, let me translate... The article from the SQL Server product team said, in a very nice way mind you, that you could, if you want to, install SQL Server Analysis Services in Tabular mode on your own desktop computer  where you will have admin rights and complete control over your environment.  The article also said that maybe it ought to perhaps be slightly OK, if you had to  have a dedicated shared server for the purpose of managing these workspace databases in small groups.

My very strong recommendation is that SQL Server 2012 Developer Edition be installed on each and every database developers' workstation with a local, default instance of the SQL Server relational engine and Analysis Services installed in a Tabular mode instance.  If you do any multidimensional work, it should be there as well in its own named instance.  You could name both instances, one OLAP and then other TABULAR.  IT doesn't matter as long as they make sense to you.  You should be running on 64 bit hardware with a 64 bit OS and 64 bit Office apps and at least 8 GB of RAM, if not more.  When you ask to have your development machine loaded and configured for SSAS Tabular, your system admin probably isn't going to volunteer to install the SQL Server services on your machine and make you a local administrator (which you will need to be).  You need to make that request after having the appropriate conversation and getting approval from the boss.  This is important.  SSAS has changed and the development environment requirements have changed with it.

If, for whatever reason, you are not able to install a local instance of Analysis Services in Tabular mode on your local development machine, the second best option will be to create a dedicated instance for developers to share, to be used only for workspace databases.  Consider that this instance may need to be restarted if maintenance is needed.

Using Partitions to Minimize the Table Size Working Set


Partition Best Practices

Best Practices

Keep each partition under 2 million rows

Partitioning on data ranges and time periods

Partitioning on key distribution

Recovering a Workspace Using Script

 XMLA Essentials

Recovering a Workspace Using SSMS 


Conclusion & Best Practices