Via MSDN, there’s now a great whitepaper called Performance Tuning of Tabular Models in SSAS 2012 available for your viewing pleasure. There’s a treasure trove of great information in this whitepaper and I highly recommend anyone developing or reporting on Tabular Models to take a look at this whitepaper. So definitely take a moment to download and read this whitepaper.
While you download this historic piece of literature, here’s three little tidbits of knowledge I picked after my initial skim-through:
Partitions Don’t Help Query Performance
Partitions do not improve processing time and/or query time. In Tabular Models, partitioning tables in the model only serves to allow the administrator to selectively refresh smaller subsets of data as is the case with an incremental load of a fact table. If your fact table is incrementally loaded, you can save processing time and help you make your processing window by only processing the affected partitions, but partitioning alone will not improve processing time if you’re still processing all partitions.
Partitions in a table are processed serially, unlike partitions in a measure group of a Multidimensional Database which are processed in parallel. In a Tabular Model, however, since each table is independent of each other, tables can be processed in parallel even if partitions in a given table aren’t.
Unlike in a Multidimensional Database, dimension tables in a Tabular Model can be partitioned. This opens the door for incremental processing of those dimensions as well as some unique partitioning strategies for those dimensions.
Memory Is Crucial, Duh!
Obviously with a Tabular Model you need to be able to fit the model in memory to fully utilize the magical power that is Tabular. But if you have a 10 GB model, how much memory do you need? The correct answer is about 30 GB of memory. Why 30 GB you ask? During a Process Full of your Tabular Model, the database is kept online until the transaction for the processing operation is committed. That means that for the given 10 GB model, you need to be able to hold two copies in memory: 10GB for the old data and 10GB for the new data. Then you’ll likely need around 5 GB – 10 GB for various processing overheads. So keep in mind that you could need significantly more memory than you might think is necessary to support a single Tabular Model.
Table Queries Are Unaltered During Processing
In a Multidimensional database, the heavy lifting for dimensions is transferred
from SSAS to the relational database by executing “Select Distinct” statements for each attribute. For measure groups in a Multidimensional database, the query is wrapped in a subselect with only the necessary columns returned. This also means that using CTE’s, Order By statements, and procedures are not an option in a Multidimensional database. In a Tabular Model, however, the query for the table is unaltered which means that using CTE’s, procedures, Order By’s, and various other t-sql features is now possible. But this also means that the responsibility for returning only the required columns in the query is now the developer’s. Without removing the unnecessary columns from the query, processing will be less efficient and could take longer.
Like I said earlier, this is a great whitepaper and I highly recommend that you check it out!