To determine Analysis performance problems, you can view a log of the Analysis engine and your data warehouse database. This will reveal information about the infrastructure and 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, ensuring that statistics are up to date (access plans are computed and rational) and that 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 that 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 More actions and options icon in the report toolbar, and select Administration > Clear Cache. Click Ok.
- Click the icon again and select Administration > XML. Click Ok.
- Click the icon again and select Administration > Log.
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