Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
performance tuning analysis services
- Thread starter sunthart
- Start date
Hi,
I assume that you have problems with the speed of your queries on your cubes.
Analysis Services:
On each cube you have option (right click) Usage Analysis.
If you choose this, you can see how long it takes for each query to show the data to the user.
See which queries are problematical.
Then go, once again right click on the cube and choose option Usage-Based Optimization.
Now you can add aggregates to the existing ones only for thoose problematical queries.
Be aware that this aggregations don't stay when you reproccess your cube. You have to do this optimization regulary!
I hope I helped, please notify me if it worked for you.
Bye,
Andrej
I assume that you have problems with the speed of your queries on your cubes.
Analysis Services:
On each cube you have option (right click) Usage Analysis.
If you choose this, you can see how long it takes for each query to show the data to the user.
See which queries are problematical.
Then go, once again right click on the cube and choose option Usage-Based Optimization.
Now you can add aggregates to the existing ones only for thoose problematical queries.
Be aware that this aggregations don't stay when you reproccess your cube. You have to do this optimization regulary!
I hope I helped, please notify me if it worked for you.
Bye,
Andrej
UBO is a good way to start but doesn't provide aggregations for any queries that haven't been made. If you have a standard set of reports that are never deviated from then UBO may handle the needed aggregations. If users are giving free access to the data to query however they wish then you need to anticipate the aggregations needed.
Number of records in the cube
Number of measures and Types of measures (distinct count?)
The number of dimensions and levels within a dimension
Can all affect the number of aggregations needed. If you have shallow dimensions with 2 or 3 levels you'll need less than if the dimensions had 5 or 6 levels. Starting with 200-300 aggregations will probably capture a good number of combinations needed.
Number of records in the cube
Number of measures and Types of measures (distinct count?)
The number of dimensions and levels within a dimension
Can all affect the number of aggregations needed. If you have shallow dimensions with 2 or 3 levels you'll need less than if the dimensions had 5 or 6 levels. Starting with 200-300 aggregations will probably capture a good number of combinations needed.
- Status