Analysis Services has a useful feature called Usage-Based Optimization. This feature is used to design aggregations based on actual user queries using Business Intelligence Development Studio (BIDS). Information on how to configure (or disable) the query logging required for this is fairly hard to find.
The Analysis Services Properties identified by "Log \ QueryLog" in the Analysis Services Properties window are used to configure the query logging used by Usage-Based Optimization:
The definition of these properties can be found in Books On Line (BOL) and on MSDN.
Enabling Usage-Based Optimization:
1. Set CreateQueryLogTable to true
2. Set the value of QueryLogConnectionString appropriately (database specified in the connection string must already exist)
3. Set the value of QueryLogSampling to the appropriate value for your environment (the default value of 10 means every 10th query will be logged). The lower the value of this property, the higher the overhead of query logging will be on the system.
4. Set the value of QueryLogTableName appropriately.
5. Click "OK"
Disabling Usage-Based Optimization:
1. Set the value of QueryLogSampling to "0". This will stop query logging.
2. Click "OK"
Advantages of Usage-Based Optimization and logging:
1. If you have an environment where users are submitting repetitive queries with similar tuples/slicers - the usage based-optimization will probably do a good job of helping design useful aggregations.
2. Very little detailed knowledge of the cube structure and querying practices is needed to create aggregations.
3. Aggregations will be designed (using BIDS or BIDS Helper utilities) based on actual user queries and not just gather requirements.
Disadvantages of Usage-Based Optimization and logging:
1. Can create overhead on the system is sampling is too frequent.
2. Resulting aggregation designs may not help as much as desired or may not help the average use-case.
3. The logging table must be maintained and kept at a manageable size:
a. Any structure changes will cause analysis services to issue a delete against the logging table for any queries containing the cube being changed/affected. This can cause cube structure changes to take a long time if the logging table is extremely large.
b. Utilities from BIDS (or BIDS Helper functionality) can be mislead by older log data that may no longer apply to the current usage patterns of the database.
One of the key performance concepts in Analysis Services is the design and use of good Aggregations to support user queries. However, Analysis Services (Both 2005 and 2008) by default is not configured very well to take advantage of multi-processor environments for parallel processing of these indexes, particularly for large MOLAP partitions. Keep in mind, using a large portion of available processors for aggregation building is only desirable in environments where a processing window is allocated and user activity is minimized or prevented entirely for that window. If you have small datasets and processing time is not a problem then making changes to support better parallel processing may not be needed or recommended. Changing of these settings is best done under guidance of Microsoft.
Problem Isolation: Using a combination of SQL Server Profiler and watching the Task Manager from windows allows simple detection of the key symptom of this problem: seeing only one processor (or processor core) being utilized while processing of aggregations is taking place. To test this, issue a multi-partition ProcessIndexes XMLA statement such as the one below and watch your Profiler and Task Manager (could also use Perfmon as well).
<Parallel>
<Processxmlns:xsd="http://www.w3.org/2001/XMLSchema"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">
<Object>
<DatabaseID>Adventure Works DW 2008</DatabaseID>
<CubeID>Adventure Works</CubeID>
<MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
<PartitionID>Internet_Sales_2001</PartitionID>
</Object>
<Type>ProcessIndexes</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
<Processxmlns:xsd="http://www.w3.org/2001/XMLSchema"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">
<Object>
<DatabaseID>Adventure Works DW 2008</DatabaseID>
<CubeID>Adventure Works</CubeID>
<MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
<PartitionID>Internet_Sales_2002</PartitionID>
</Object>
<Type>ProcessIndexes</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
<Processxmlns:xsd="http://www.w3.org/2001/XMLSchema"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">
<Object>
<DatabaseID>Adventure Works DW 2008</DatabaseID>
<CubeID>Adventure Works</CubeID>
<MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
<PartitionID>Internet_Sales_2003</PartitionID>
</Object>
<Type>ProcessIndexes</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
<Processxmlns:xsd="http://www.w3.org/2001/XMLSchema"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">
<Object>
<DatabaseID>Adventure Works DW 2008</DatabaseID>
<CubeID>Adventure Works</CubeID>
<MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
<PartitionID>Internet_Sales_2004</PartitionID>
</Object>
<Type>ProcessIndexes</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>
If, during the course of processing you see only one partition processing at a time (in the Profiler window) and that also correlates to mdsmdsrv.exe only using the equivalent of one CPU (i.e. ~50% on a 2-CPU machine) then tuning the SSAS server properties to allow better parallel aggegation/index processing might be an advantage for the environment (see original statement regarding processing windows and data sets in the first paragraph).
Configuration Changes: The configuration properties of AggregationMemoryLimitMax and AggregationMemoryLimitMin (these are in the OLAP \ Process section of the Analysis Services Properties Window):
The default value of 80 for AggregationMemoryLimitMax will prevent more than one partition from being processed, especially for large cubes/partitions with large aggregations. This is because a setting of 80 means up to 80% of the available memory can be used for building aggregations on the partition. That leaves only 20% of the available memory for other partitions, which is not enough to start another partition until the partition currently being processed is completed. I use the following formula for setting AggregationMemoryLimitMax and AggregationMemoryLimitMin (this is only used for reference, the proper settings vary from environment to environment based on hardware resources, business requirements and availability requirements):
AggregationMemoryLimitMax=ceiling(100/(<number processing cores>-2))
AggregationMemoryLimitMin=ceiling(100/((<number processing cores>-2)*1.5))
Using (<number processing cores>+1) helps to leave enough memory for at least one processor core for non-SSAS activities (such as paging, SQL Server activities).
On a 16-core machine the AggregationMemoryLimitMax would come out to 7 which means up to 14 (100/7) partitions could process aggregations in parallel. The AggregationMemoryLimitMin would come to 5 which means no more than 20 (100/5) partitions could process in parallel.
Result:
Processing time for rebuilding aggregations and indexes is significantly reduced in multi-processor environments, provided the environment does not have other resource constraints. The degree of improvement will depend on the number of processing cores and amount of memory available to the system for the Aggregation processing. However, on large mult-core, multi-processor servers with large partitions and aggregations, this can make the difference between being able to process the aggregations frequent enough to help user queries or having the user queries suffer longer durations due to missing indexes and/or aggregations that are not processed.