Hello Knut,
I understand that you'd like to know if it's possible to cache cube
calculation somehome so that clients can get report quicker after 1st
query. If I'm off-base, please let me know.
Analysis Services does not save the calculated results in the query results
cache, so these calculations must be re-executed each time they are
requested. Simple calculations are performed very quickly, but Analysis
Services may consume a significant amount of time resolving queries that
invoke these calculations when complex calculations are involved.
It's suggest taht you use the following guidelines when working with
complex query time calculations:
Ensure that the additional functionality provided through the use of
complex query time calculations justifies the cost in query responsiveness.
In particular, it is best whenever possible to avoid calculations that
recursively operate on cells or members, or scan large numbers of members
to obtain the desired result.
When you use a calculated member on the Measures dimension, set the Non
Empty Behavior property to the name of a measure, in order to treat the
calculated measure as empty if the specified measure is empty. Otherwise,
computations that include the NON EMPTY keyword can be slow and consume a
lot of client resources, because the calculation is performed for every row
or column just to determine whether the result is empty. On sparse cubes,
the computation time can be cut dramatically.
Consider shifting the performance impact of complex calculations to the
processing phase. You can accomplish this by using views or computed
columns in the relational database or by using SQL queries to define
computed columns in the Measures dimension.
You could see above information from the following articles:
Microsoft SQL Server 2000 Analysis Services Performance Guide
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ansvcspg.mspx
Microsoft SQL Server 2005 Analysis Services Performance Guide
http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b
2011c/SSAS2005PerfGuide.doc
You may want to consider cache the query result on application level so
that it can be re-used if same query is submitted to the application again.
For example, reporting service could cache the report with backend data
source as Analysis services. If report parameters are same, the report
server may return the result to clients from cache directly without
performing data retriving/report processing again. This may improve
performance of reporting if there is similar report requests.
Report Caching in Reporting Services
http://msdn2.microsoft.com/en-us/library/ms155927.aspx
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.