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.
Siddharth has more than 14 years of experience in the IT Industry, with more than a decade of experience in Business Intelligence and Analytics, for clients banking, logistics, government, Media Entertainment, products, life sciences and other domains. He has been a lead architect for a portfolio of 40+ apps, containing apps in web, mobile, BI, Analytics, data warehousing, reporting, collaboration, CMS, NoSQL and other technologies. He has several certifications and is a published author for online and print-media publications, as well as the MSDN Library.
In his present role, he remains responsible for architecture design, technology stack selection, infrastructure design, 3rd party products evaluation and procurement, and performance engineering. These applications use technologies like Elasticsearch / Lucene, MongoDB, SharePoint 2013 and 2010, jQuery-based framework like Highcharts and GoJS, SQL Server and the Microsoft Business Intelligence stack (SSIS, SSAS, SSRS, MDX, PowerPivot, PowerView), jQueryMobile, Bootstrap, iOS xCode framework, and many others.
- MSSQLTips Awards: Champion (100+ tips) – 2018 | Author of the Year – 2017 | Author Contender – 2016, 2018-2019