Architecture – Microsoft BI Solution
Before we start digging into the details, let’s see how a typical Microsoft Business Intelligence (BI) application architecture would look. This will give you an understanding of identifying and locating the performance issues/bottlenecks.
The below diagram shows a typical Microsoft BI application architecture which has different layers shown from left to right. On the left layer you have source systems or a relational data warehouse, in the middle layer you have the Analysis Services cube pulling data from the source systems and storing it in an Analysis Services cube/OLAP store and on the right layer you have reporting applications which consume the data from the Analysis Services cube/OLAP cube.
Although a typical Microsoft BI application architecture is to have each layer on a different physical machine, that’s not usually the case. Very often you will see these layers overlap; for example in one scenario you have the relational data warehouse, Analysis Services cube and reporting services/applications on the same machine whereas in another scenario you might have a relational data warehouse and the Analysis Services cube on one machine and reporting applications on another machine or relational data warehouse on one machine and Analysis Services cube, reporting services/applications on another machine or all on separate machines.
Whatever your system architecture or design approach is, you need to make sure that your OLAP query performance is very fast which the Microsoft BI platform is known for. But we can not overlook the processing performance as well, as this ensures data gets refreshed within the defined SLA (Service Level Agreement ).
SSAS performance optimization is primarily focused on:
- Query Performance
- Processing Performance
- Proper and adequate utilization of hardware resources
During processing, SSAS refreshes the Cube/OLAP store with the latest data from the source systems and relational data warehouse and generates aggregates if any are defined. It also creates an Attribute store for all the attributes of the dimensions and a Hierarchy store for all natural hierarchies. Though it sounds like the processing time does not matter much in comparison with Query Processing since users are not directly impacted, it is equally important to make sure you provide reports with refreshed data within the defined SLA.
Querying performance is what SSAS is known for. There are several ways you can improve the performance of your queries running against a SSAS cube. You should spend time in designing dimension and measure groups for optimal performance, create aggregation and bitmap indexes (by setting appropriate properties), optimize your MDX queries for faster execution (for example to avoid the cell by cell mode and using subspace mode).
SSAS instance/hardware resources optimization
Both processing and querying performance is determined by how well you tune your resources for better throughput. You can specify number of threads that can be created for parallel processing, specify the amount of memory available to SSAS for its usage, improving or using better I/O (Input/Output) systems or placing your data and temp files on the fastest disks possible.
SSAS Internal Architecture
By reviewing the SSAS internal engine will help us at a high level understand how SSAS responds and ultimately provides data as a result of a query issued against a cube. When a MDX query is issued against a cube, the first stop is the Query Parser. The Query Parser has a XMLA listener which accepts requests, parses the query, and passes it to the Query Processing Engine for query execution.
The Query Processor upon receiving the validated and parsed query from the Query Parser prepares an execution plan which determines how the requested results will be provided from the cube data and the calculations used. The Query Processor caches the the calculation results in the Formula Engine Cache.
The Storage Engine responds to the sub cube data (a subset or logical unit of data for querying, caching and data retrieval) request generated by the Query Processor. It first checks if the requested sub cube data is already available in the Storage Engine cache, if yes then it serves it from there. If not then it checks if the aggregation is already available for the request, if yes then it takes the aggregations from the aggregation store and caches it to the Storage Engine cache and also sends it to Query Processor for serving the request. If not then it grabs the detail data, calculates the required aggregations, caches it to the Storage Engine and then sends it to Query Processor for serving the request.
The easiest way to remember this is:
- the formula engine works out what data is needed for each query and requests it from the storage engine.
- the storage engine handles retrieval of raw data from disk and any aggregations required.
There are several tools that can be used to troubleshoot where a bottleneck may be occurring. This will help you target the right areas for optimization such as the Query Processing Engine or Storage Engine. Tools such as SQL Profiler can be used to perform an OLAP trace which can be used to capture certain events which will tell you the time taken by each of these components.
Additionally, Performance Monitor (perfmon) is a tool that will permit the collection of information using a set of counters to monitor SSAS performance. You can learn more about Performance Monitor here.