Geeks With Blogs
James Rogers BI from the trenches...
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.
 
Posted on Tuesday, November 2, 2010 3:31 AM Analysis Services | Back to top


Comments on this post: SSAS Usage-Based Optimization, OlapQueryLog (Enabling and Disabling), Aggregations and Structure Changes.

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © James Rogers | Powered by: GeeksWithBlogs.net | Join free