In order to determine the causes of your Analysis performance problems, you must enable logging in the Analysis engine and your data warehouse database so that you can view information about the infrastructure, and both the SQL and MDX queries involved in your Analysis calculations. Your DBA should perform the initial database performance-tuning work by looking at the database logs, making sure statistics are up to date (access plans are computed and rational), and your usage is profiled. Make sure the aggregation levels are based on the top 50-80 common uses.
Base all of your performance tuning on this data; it will tell you everything you need to know about bottlenecks in your data structure.
You can also determine the causes behind hanging queries in an Analyzer report by viewing Mondrian log information directly through the Analyzer interface:
- Log into the BA Server as an administrator
- Create or load an Analyzer report
- Click the Clear Cache link near the top of the report
- Click the XML link to the left of Clear Cache, and then click OK
- Click the Log link between XML and Clear Cache
A new browser tab will open with log information about the open report. You can refresh this page to see the query progress in real time. The following log entries are the most important to watch out for:
- If each SQL query is reported twice. The first time is for Mondrian to get the first record and the second time is to retrieve all records
- SQL queries with high execution times
- SQL queries that return large volumes of data (more than 1000 rows)
- SQL queries that don't join tables
- SQL queries that don't include filters
- This log entry: WARN mondrian.rolap.RolapUtil Unable to use native SQL evaluation for 'NonEmptyCrossJoin'; reason: arguments not supported. If you see this, try switching the contains filter into an includes filter, or make the contains filter more selective