by Paul Turley, Mentor, SolidQ
Components of a Tabular Model Project
The tabular projetent-fragment-bottom">
by Paul Turley, Mentor, SolidQ
The tabular projetent-fragment-bottom">
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.
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.
Best Practices
Keep each partition under 2 million rows
Partitioning on data ranges and time periods
Partitioning on key distribution
XMLA Essentials
Conclusion & Best Practices