Performance Tuning For SSAS 2008 - Summary For Chris Webb Session "Introduction to Performance Tuning Analysis Services 2008"

The following is a summary for the very good session held by Chris Webb for SQLBits and titled Introduction to Performance Tuning Analysis Services 2008:

• In general, when you design attributes relationships the kind of more of these long thin strings of attribute relationships you can find in your data the better your dimension will perform; so if you have a kind of flat pushy structure then in general the performance will be slightly worse, if you have long finger like structure is going up throw your data and performance will be better.
• You have to check if your data reflects your dimension relationship design. You can right click your dimension and choose to “Dimension Health Check” after installing the very good tool BIDS Helper.
• For any child in the attribute relationship, remember to change the key to a composite key of the attribute itself and its ancestors, otherwise you may have a many to many relationship between the parent and the child attribute.
• Parent/child hierarchies can perform badly sometimes; so if you know the maximum depth of the hierarchy try to flatten out structure and turn it to a regular dimension with a regular set of relationships, rather than having one column with a kind of recursive join on it. The “Parent/child hierarchy naturaliser” in BIDS Helper can do this automatically.
• There are two ways (other than improving hardware) to tune the Storage Engine: Partitioning and more importantly designing Aggregation.
• Partition your measure group, after that AS engine will be able to read from multiple partition simultaneously, and it will be able to aggregate up data from multiple partitions simultaneously.
• Also the benefit of partitioning is that if you sliced you measure groups into partitions which reflect the way that you’re using the query in the cube then you will effectively reduce the amount of IO because the AS engine will just go to the partition that it knows contains the data.
• Although SSAS should auto-detect the slice of a partition, it is good practice to set the Slice property manually (The Slice property is MDX expression that tells AS what data in that partition), even on MOLAP partitions, otherwise you may find AS hits partitions that you know they should not by hit for certain queries.
• Aggregations are pre-calculated sets of summary values, similar to what is returned by a GROUP BY query in tSQL.
• Building aggregations improves query performance by reducing the amount of calculation done at query time.
• Aggregations start to show an effect on regular SUM measures where partition size is greater than a few million rows.
• Designing aggregations is something that you can do when your cube design is stable.
• Use the Aggregation Design Wizard to design your aggregations and associate the aggregation design with a particular partition, but before all of that you have to set the AggregationUsage property of the attributes (in the cube editor), and what this done is that it controls how the Aggregation Design Wizard uses those attributes for designing aggregates, and there are a number of options for that property:
• Full: means that every aggregation design must include this attribute.
• None: means that this attribute will never be included in aggregation.
• Unrestricted: leaves it to the Wizard to decide whether to use it or not.
• Default: means the same as Unrestricted if the attribute is the key attribute or the             dimension or it’s involved in natural user hierarchy.
• You also can design aggregation by setting up Query Logging and run Usage-Based Optimization.
• A third option to design aggregation is manually using BIDS Helper or the advance tab aggregation Design.
• To know whether your query is Storage Engine bound or Formula Engine bound you can use Profiler or MDX Studio and find how much time it takes to read data from disk, so look at the duration associated with query sub-cube event or look at the duration associated with progress report begin and end events, and if there is a significant amount of time associated with these event, say half a second, then yes aggregations is going to be useful, otherwise the problem is in the Formula Engine.
• Other sign that may point to the Formula Engine as the problem, is when you have a slow performing query that uses one core processor; as the Formula Engine is Single-threaded while the Storage Engine is Multi-threaded.
• MDX Script Performance Analyser can help to work out which, if any, calculation on the cube is the problem.
• Caching can take place in the Storage Engine and the Formula Engine. If Storage Engine read data from disk it will store it in the cache and it will be available there until you clear the cache or you process the cube, and caches there can be shared between all users.
• There are three different ‘scopes’ or lifetimes of a Formula Engine cache:
• Query: for calculations defined in the WITH clause of query, the Formula Engine values can only be cached for the life time of the query.
• Session: for calculations defined for a session, using the CREATE MEMBER statement executed on the client, Formula Engine values can only be cached for the lifetime of a session.
• Global: for calculations defined in the cube’s MDX script, Formula Engine values can be cached usually until processing takes place. Which is a good argument to define all of your calculation in the cube rather than in your query because then after the first time you run a calculation _in general_ that calculation value will be cached on the server for next time the query runs.
• Data can be loaded into the Storage Engine cache by executing CREATE CACHE statements. SSIS packages can be used to do this.
• The Formula Engine can only be warmed by running MDX queries.
• You can also use SSRS to warm the cache.