Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SSAS - Best Practices and Performance Optimization - Part 1 of 4


By:   |   Read Comments (9)   |   Related Tips: 1 | 2 | 3 | 4 | > Analysis Services Performance

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

There are several aspects that can take a toll on performance for your Analysis Services cubes. Some problems could be related to the source systems, some could be because of poor design of your cube and MDX queries and some could be related to network issues. In this tip series, I am going to talk about some of the best practices which you should consider during the design and development of your Analysis Services cube and some tips which you can follow to tune your existing environment if it is suffering from performance issues.

Solution

Before we start digging into the details of performance optimization, 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.

analysis services architecure layers

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 ).

So basically when we talk of SSAS performance optimization, we need to take care of:

  • Query Performance
  • Processing Performance
  • Proper and adequate utilization of hardware resources

Let me briefly talk about the SSAS internal architecture which will help you understand the different components in a SSAS environment, the operations they perform and how they work together as shown in the below diagram.

analysis services engine processing steps

Query Parser

The Query Parser has an XMLA listener which accepts requests, parses the request and passes it along to the Query Processor for query execution.

Query Processor

Upon receiving the validated and parsed query from the Query Parser, the Query Processor prepares an execution plan which dictates how the requested results will be provided from the cube data and the calculations used. The Query Processor caches the calculation results in the formula engine cache (a.k.a Query Processor Cache) so it can be reused across users with the same security permissions on subsequent requests.

This summarizes the Query Processor operations:

  • Makes a request for sub cube data from storage engine 
    • Translation of request into sub cube data requests
  • Produces result set by doing
    • Bulk calculation of sub cube
    • Cell-by-cell calculations
  • Stores calculation results in formula engine cache with varying scope
    • Query scope - cache will not be shared across queries in a session
    • Session scope - cache will be shared across queries in a session
    • Global scope - cache can be shared across sessions if the sessions have the same security roles

Storage Engine

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.

This summarizes the Storage Engine operations:

  • Creates Attribute Store (Key store, relationship store, bitmap indexes etc)
  • Creates Hierarchy Store
  • Creates Aggregation Store
  • Storage Engine Cache
    • Loads data from storage engine cache as queries execute
    • CClears data from storage engine cache with cleaner thread (in case of memory pressure) or processing of partitions
  • Aggregation Data
    • RResponds to request with aggregated values in storage
    • If new then summarizes lower level aggregated values on the fly as needed
  • Fact Data
    • Scans MOLAP partitions and partitions segments in parallel
    • Uses bitmap indexes to scan pages to find requested data

While troubleshooting you need to understand which component is taking more time and needs to be optimized; such as the Query Processing Engine or Storage Engine. To understand this you can use SQL Server Profiler and capture certain events which will tell you the time taken by these components for a cold cache (empty cache, to learn more about cache warning refer to the next tip in this series):

  • Storage Engine Time = Add elapsed time for each Query Subcube event
  • Formula Engine = Total execution time (Query End event) - Storage Engine time (A)

If most of the time is spent in the Storage Engine with long running Query Subcube events, the problem is more likely with the Storage Engine. In this case you need to optimize the dimension design, design of the aggregations and create partitions to improve query performance (discussed in details in next tips in this series). If the Storage Engine is not taking much time then it is the Query Processor which is making things slow, in that case you need to focus on optimizing the MDX queries.


So to address performance optimization, here are three different areas to address:

Processing Performance

DDuring 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, I would say its equally important to make sure you provide reports with refreshed data within the defined SLA.

Querying Performance

QQuerying 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

BBoth 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.

Stay tuned for the next tip in the series.

Next Steps


Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, March 31, 2016 - 2:59:30 AM - Nidhi Back To Top

SSAS cube - Having varied perfoamnce when data fed from SQL Server vs. Teradata.

 

Hi I wanted to know why I'm getting different performance fetching from SSAS cube. 

To do a POC, same dataset was used to create the cube from both SQL server and teradata. However once the cube was published, the cube created from SQL server is retriving data faster as compared to cube created from Teradata.

 

Are there any different compression techniques that SSAS considers while fetching data from different DBs?

 

Appreciate some expert advice on this.

 

Regards,

Nidhi

 


Friday, May 29, 2015 - 5:50:31 AM - olawale Back To Top

I have a question that i think you might be of a help. I have a table,let call it Customer information with 75 columns. Some of these columns are either NULL or EMPTY for some many rows within my table. I want to create a dimension from this table but i know the query Engineer will do extra work to go throught all the attributes when the dimesion is created. What would be you suggestion on how to go about this....would you suggest i first normalize the table and create independent dimesions from them or create a single dimesion and then create different Hierarchy on the dimesion with 75 attributes

 

Thanks


Friday, July 05, 2013 - 9:41:17 AM - Selwyn Back To Top

It takes more than 5 minutes. There is only one meaure (count for each CustomerKey, (which is 1 for each dimension row)). No aggregation and ive left the default partition as is. i am testing it on my laptop (4gb ram, Intel Core i5). But SSAS server is on the business sql server (i dont have the specs but it normally dos not give performance issues)


Wednesday, July 03, 2013 - 11:00:55 AM - Arshad Back To Top

Well it depends on several other factors for example number of records in measure group, level of aggregation you have used, partition of your measure group, hardware etc.

I think 150K records should not take more than few seconds. How much time its taking for you?


Wednesday, July 03, 2013 - 4:20:18 AM - Selwyn Back To Top

Im fairly new to SSAS but i would like to get a feel on what the ideal time frame should be to return a resultset. I have created a simple dimension with just a Customer key (integer) and then a cube (measure is to count that Customer Key). How long do you think it should take just to return +- 150000 rows of the Customer Key when i browse in the Cube browser?


Friday, March 22, 2013 - 2:38:59 PM - Pratap Chavda Back To Top

Arshad,

I am new to SQL server and am trying to learn SSIS. I have been able to create some packagese
using SSIS. What I am wondering is how I can deploy them to a different environment in the same
server? Please help me by writing a simple process of deploying a SSIS package.

Thanks.

Pratap.


Monday, January 28, 2013 - 8:36:26 AM - shirish Back To Top

I have a Question about Cube Warm up Process

We have query running against cube which tale more than 20 min

We decided that we will run all query Early Morning so that when user run they will get data Fast

Now Issue is we have one query which tale more than 30 min

If we break that Query in to 2 part for Warmup and keep same query for Report Does it going to help us ?

Does is going to warm up dimension so that original query will run Fast?

Thanks

 

 

 

 

 


Sunday, October 14, 2012 - 11:16:50 PM - Sreedhar Back To Top

Hello Arshad you articles are very informative and really very helpful in enhancing the skills.  Is it possible for you to attached the articles as pdf once you are finished with all parts of the articles as a single download.  This will be helpful to keep in our document library for ready reference.

 


Friday, February 24, 2012 - 4:51:24 AM - Caralyn Back To Top

This is a very interesting article, can't wait for the next one! :-)


Learn more about SQL Server tools