# Optimize Pentaho Analyzer Once you've properly tuned your data warehouse, you can move on to tuning your ROLAP schema, the Mondrian engine, and the Analyzer client tool. ## Partitioning high-cardinality dimensions If you cannot avoid creating high-cardinality dimensions, then you must devise a strategy to make them more performant without reducing their size. Typically a database will partition large tables, which makes querying one partition a quick operation. However, the Analyzer engine does not have any way of detecting which tables are partitioned and which are not. Therefore, MDX queries will be translated into SQL statements that are too broad, resulting in a query that traverses all of a table's partitions. To instruct the Analyzer engine to properly address a (partitioned) high-cardinality dimension, you must modify the ROLAP schema and explicitly set the **highCardinality** property of the **ElementCubeDimension** element to `true` on each applicable dimension. This will streamline SQL generation for partitioned tables; ultimately, only the relevant partitions will be queried, which could greatly increase query performance. ## Mondrian log analysis To determine Analyzer performance problems, you can view a log of the Analyzer engine and your data warehouse database. This will reveal information about the infrastructure and the SQL and MDX queries involved in your Analyzer 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: 1. Log into the BA Server as an administrator. 2. Create or load an Analyzer report. 3. Click the **More actions and options** icon in the report toolbar, and select **Administration** > **Clear Cache**. Click **Ok**. 4. Click the icon again and select **Administration** > **XML**. Click **Ok**. 5. 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 ## Configuring Pentaho Analyzer for large data warehouses Analyzer has some low-level configuration options that will improve performance when working with large data warehouses and high-cardinality dimensions: * `filter.members.max.count=500` * `filter.dialog.apply.report.context=false` * `filter.dialog.useTopCount=true` * `report.request.service.result.expire.time.seconds=30` * `report.request.service.result.cleanup.time.seconds=300` These **analyzer.properties** settings are explained in the following table: | Property | Description | | ------------------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | **filter.members.max.count** | Controls the maximum number of values to show in the filter dialogue, such as include/exclude filters and date range dropdowns. | | **filter.dialog.apply.report.context** | If set to `true`, when showing available members in the filter dialog, Analyzer will limit those members to the existing filters or measures on the report. This means that when retrieving the list of members, Analyzer will perform the join in the fact table and then apply dimension filters. For a high-cardinality dimension, this may significantly reduce the list of members loaded into memory. | | **filter.dialog.useTopCount** | If both this and **mondrian.native.topcount.enable** in mondrian.properties are set to `true`, when showing the first set of members in the filter dialogue, Analyzer will only show that set of members sorted within hierarchy. For high-cardinality dimensions, this is required to avoid loading all members into memory. However, if a user uses the Find box in the filter dialogue or if you have **filter.dialog.apply.report.context** set to true, then the TopCount will not be used. | | **report.request.service.result.expire.time.seconds** | Report results are released after this amount of time has passed.Analyzer report requests are processed asynchronously and immediately cleaned up after the first download. While this is efficient because clients usually don't need to download a report more than once, it causes issues with popup blockers that will block the first download and re-submit the download after prompting the user. If you expire the request after 30 seconds, you will work around the popup blocker issues while also enabling people to refresh the browser to re-download a report. This only applies to PDF, Excel or CSV downloads. | | **report.request.service.result.cleanup.time.seconds** | Report result cleanup occurs after this amount of time. | ## Configuring the Mondrian engine for large data warehouses There are several **mondrian.properties** options that control how the Analyzer engine interacts with large data warehouse volumes in conjunction with Pentaho Analyzer: * `mondrian.result.limit=5000000` * `mondrian.rolap.iterationLimit=5000000` * `mondrian.rolap.queryTimeout=300` * `mondrian.native.crossjoin.enable=true` * `mondrian.native.topcount.enable=true` * `mondrian.native.filter.enable=true` * `mondrian.native.nonempty.enable=true` * `mondrian.rolap.maxConstraints=1000` * `mondrian.native.ExpandNonNative=true` * `mondrian.expCache.enable=true` These **mondrian.properties** settings are explained in the following table: | Property | Description | | ------------------------------------ | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | **mondrian.result.limit** | Controls the largest cross join size that Mondrian will handle in-memory. Ideally, no queries should involve large cross joins in-memory; instead, they should be handled by the database. | | **mondrian.rolap.iterationLimit** | This is similar to **mondrian.result.limit**, except this applies to calculating aggregates in-memory such as SUM, MAX, AGGREGATE, etc. This should be set to the same value as **mondrian.result.limit**. | | **mondrian.rolap.queryTimeout** | If any query runs past this number of seconds, then the query is immediately cancelled. The total sum of all SQL statements to process a single MDX statement must be less than this timeout. Setting this to zero disables query timeout, which is not recommended because runaway queries can deprive system resources from other necessary processes. | | **mondrian.native.crossjoin.enable** | If this is set to `true`, when Mondrian needs to cross join multiple dimensions in a report, if the cross join is non-empty, meaning a fact table relationship has been defined, then the join operation is done using SQL. The resultant SQL query returns only combined dimension members that actually have fact data. This reduces the amount of tuples that need to be processed and is critical for performance on high-cardinality dimensions. | | **mondrian.native.topcount.enable** | If set to `true`, when fetching the first set of records for the filter dialog, Mondrian will only read that set of records into memory. If set to `false`, all records from the dimension level will be read into memory. | | **mondrian.native.nonempty.enable** | If set to `true`, when fetching the first set of records for the filter dialog, Mondrian will only read that set of records into memory. If set to `false`, all records from the dimension level will be read into memory. | | **mondrian.rolap.maxConstraints** | This should be set to the largest number of values that the data warehouse database supports in an IN list. | | **mondrian.native.ExpandNonNative** | Works in conjunction with native evaluation of cross joins. If set to `true`, Mondrian will expand cross join inputs to simple member lists that are candidates for pushdown. | ## Redesigning Analyzer reports for maximum performance Once you have an idea of what you want to show with your Analyzer report, you will almost certainly have to redesign it to be more performant. Because an Analyzer report is basically a hierarchical list of actions, the order in which fields and filters are added to the report can make a big difference in query response time. Even though this does not change the report's graphical output, what happens behind the scenes can make that output display more quickly. When you re-create your reports, follow this process for best performance: 1. Add and filter by low-cardinality dimensions first 2. Add measures to the report 3. Add high-cardinality dimensions last **Note:** When filtering, always choose include/exclude over contains/doesn't contain. ## Mondrian cache control You can control the cache infrastructure that the Pentaho Analyzer engine uses for OLAP data. See **Install Pentaho Data Integration and Analytics** for details about Mondrian cache control.