Understanding Usage Based Optimization
-
i have done the Usage Based Optimization (USO) wizard and I'm trying to understand what it is actually doing.....
In Design Storage, the cube is set to 15% performance gain = 40 aggregations. When I go thru the Usage Based Optimization and select for All users (i.e. everything), 50% performance gain, it computes 26 aggregations. When I go back into Design Storage, it now says 41 aggregations. So 26 USO aggregations = 1 DS aggregation? Regardless, when processed, the cube takes virtually no extra time to process, and there are no noticeable performance gains.
Any ideas/advice?
THANKS
p.s. i'm off on holiday tomorrow so no replies from me for a week or so....in the meantime feel free to chime in!
-
Not sure exactly what's happening with your system bu i don't believe that 26 UBO aggs = 1 DS aggregation; an aggregation record is an aggregation record, you're just using two different approaches for the server to select which aggregations to create. What you may need to be careful of is when using either approach if there are already aggregations, unless you select the option to add to these, the existing aggs will be deleted.
IMHO one of the best descriptions of an aggregation record (and the process involved in creation etc) is in the Performance Guide which you can download from msdn (if you search back through this forum using either 'ops' or 'perf' you should find one or more posts pointing to the exact spot in msdn).
Lastly, if you're interested, you could download SSABI (again from MSDN) and completely ignore what it does but ue the included tool/s, one of which lets you view the aggregations that have been created (ie it shows you the level/s across all of the dimensions that each set of aggregate records has been created for). Also lets you change them manually if you really want to.
Cheers,
Steve.
-
If you read the perfr guide it should give you a good understanding of what the UBO is doing, but for a 2c view, here's mine
The UBO looks at the records held within the query log (noting that this holds them at the partition and dimension level, not mdx stored ere) and determines whioch of the dimensions (and the levels of same) have been used frequently and then decides which of these to put aggregations on. based on having a suitably representative sample of queries (remembering that by default it's logging only every 10th query), this *should* give you a more usefuly set of aggregations than the aggregation wizard in the 'Design Storage' dialogs, as this latter method uses only the counts of members in levels (and counts of levels, dims) to determine where it should put aggregations, so it may in fact create aggregaions that provide no real value to user queries.
HTH,
Steve.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply