By: Siddharth Mehta
Overview
A discussion on best practices is a very contextual subject depending upon the area of practice. In case of SQL Server Analysis Services (SSAS), there are best practices for system and network, performance optimization, Cube / Dimension / Measure Group design as well as processing. As this tutorial focuses on SSAS Administration, we are going to review some of the best practices for server resource management.
Explanation
1) In general, any OLAP data model reads data out of a relational data warehouse or a relational OLTP database. Tuning the server resources such that the performance of the data pulls from a relational OLTP database server to a SSAS server remains smooth, is one of the first activities to address before the server can be handed over for development / production use.
2) Provided that server resources are optimally provisioned for the OLTP and OLAP server, the next item to address would be optimal performance of the OLTP database objects by ensuring that proper indexing is in place for read operations. OLAP data models may read a huge amount of data from OLTP servers during processing and having the right indexes in place can provide optimal SSAS performance.
3) As a best practice, any client application should not be allowed to read data directly from the tables. Instead views / stored procedures act as the source of data for the client application, which introduces a facade and allows loosely coupled architecture. Typically SSAS data models query data from views created on the top of the relational data warehouses and aggregates the same. Indexing these views can reasonably improve the performance SSAS server and significantly reduce the resource requirements of SSAS server during data processing.
4) As the data in the cube grows, generally the size of the fact table starts growing fairly large. Its very normal to have a couple of million records in fact tables. Partitioning facts tables, preferably each partition hosted on a separate drive or on a SAN can improve the server performance significantly.
5) SSAS makes a connection to the data source when it starts reading data during processing. Parallelism can help the SSAS server read more data in less time. By default SSAS allows 10 connections, but changing it to allow more connections can help to increase parallelism and improve processing performance.
6) As we allow faster and larger volumes of data exchange between the OLTP and OLAP server for SSAS server performance, one factor that can become bottleneck is the network. Configuring the network packet size depending upon the volume and frequency of data exchange can ensure that network does not become a bottleneck for SSAS server performance.
Additional Information
- For other SSAS best practices, considering reading this SSAS Best Practices and Performance Optimization series.
