Various configurable properties control the OLAP engine’s behavior. In most cases, you can use the default values for these properties. However, if you want to adjust the performance, you may need to change them.
Jaspersoft recommends that you carefully review the effects of the changes you make to the performance tuning variables, and to test such changes before using them in a production environment. | |||||||
| The OLAP settings page requires you to have administrative permissions:
|
To change the OLAP settings:
1. | Click Manage > Server Settings. |
2. | Click OLAP Settings. |
The OLAP Settings page appears.
OLAP Settings Page, Commercial Editions |
Note that the name of each property (as it appears in the underlying OLAP engine) appears below its name is shown. The properties are described in the table below.
3. | If your edition of the server includes it, click the Performance Profiling Enabled check box to generate performance reports and views, and click Change. |
The page displays a message indicating that the setting was updated.
4. | Locate and analyze the performance reports and views. The reports are found in the repository at /performance/reports. The views are found at /performance/views. |
5. | Review the available properties described below to determine if they can be changed to improve performance. |
6. | Adjust any options as needed, click Change, and run the performance views and reports to understand the impact your changes made. |
7. | Adjust the settings as necessary. |
Test your views and adjust properties as your findings dictate.
To log the SQL queries Jaspersoft OLAP sends to the database, set the Generate Formatted SQL Traces option. This ensures that Jaspersoft OLAP writes the SQL queries it executes to the log file you specify. |
The following table lists the properties’ meanings.
OLAP Settings
Property
Notes
Performance Profiling Enabled
When enabled, performance profiling data is generated and recorded. The availability of this setting is controlled by your license.
Disable OLAP Memory Caching
Turns off caching completely. Disabling caching can have a very noticeable negative performance impact.
Generate Formatted SQL Traces
When tracing is enabled, Jaspersoft OLAP formats SQL queries in the trace output with line breaks, which makes them easier to read.
The maximum number of concurrent queries allowed.
When set to a number greater than 0, result sets are limited to the specified number of rows.
Maximum number of passes allowable while evaluating a MDX expression
When evaluating an MDX query, the maximum number of passes allowed. Jaspersoft OLAP returns an error when this threshold is exceeded; for example, the error may occur during complex calculations.
Class name of ExpCompiler to use
If entered, this must be a Java class name that is an implementation of the mondrian.calc. ExpCompiler interface. Refer to the Mondrian Javadoc for more information.
MDX identifiers are case-sensitive
Specifies whether the MDX parser considers the case of identifiers.
Sibling members are ordered according to their ordinal expression
Specifies whether siblings at the same level of a dimension are compared according to the order key value retrieved from their ordinal expression. By default, ordinal expressions are only used for ORDER BY, and Jaspersoft OLAP ignores the actual values. When this property is enabled, Jaspersoft OLAP can correctly order members when native filtering is used. Note that this setting requires that the RDBMS provides non-null instances of java.lang.Comparable that yield the correct ordering when calling their Comparable.compareTo method.
If > 0, Maximum query time (number of seconds)
When set to a value greater than zero, Jaspersoft OLAP times out if the query takes longer than the specified number of seconds. If a query exceeds the limit, Jaspersoft OLAP returns an error.
For more information, refer to the Query Limit and Result Limit entries in this table.
Number of elements read when processing high cardinality dimension elements
This property determines how many members Mondrian reads in one block from the database. Setting large values for this property increases performance but can overload memory. Values should be prime with mondrian.result.limit.
For more information, refer to the Mondrian Technical Guide.
Sparse Segment Density Threshold
Performance tuning variable. This property only applies when SparseSegmentCountThreshold is enabled. It determines whether to use a sparse or a dense representation when collections of cell values are stored in memory.
Sparse Segment Count Threshold (number of cell values)
Performance tuning variable. this property only applies when SparseSegmentDensityThreshold is set. It determines whether a sparse or a dense representation is used when collections of cell values are stored in memory.
When storing collections of cell values, Jaspersoft OLAP can use either a sparse or a dense representation. This is determined by the possible and actual number of values: density is calculated as actual / possible.
Whenever possible, Jaspersoft OLAP uses a sparse representation - countThreshold * actual > densityThreshold.
For example, for the default values (countThreshold = 1000, SparseSegmentDensityThreshold = 0.5), Jaspersoft OLAP uses a dense representation for:
• | 1000 possible, 0 actual, or |
• | 2000 possible, 500 actual, or |
• | 3000 possible, 1000 actual |
If there are fewer actual values or more possible values, Jaspersoft OLAP uses a sparse representation.
During schema load, invalid members are ignored and will be treated as a null
When enabled, Jaspersoft OLAP ignores invalid members during schema load; they are treated as null members if they are referenced in a later query.
During query validation, invalid members are ignored and will be treated as a null
When enabled, Jaspersoft OLAP ignores invalid members during query validation; invalid members are ignored and are treated as null members.
Defines how a null Member is represented in the result output
Specifies how Jaspersoft OLAP should represent a null member in the result output.
If > 0, the maximum number of iterations allowed when evaluating an aggregate
When set to a number greater than 0, the maximum number of iterations allowed when evaluating an aggregate. When set to 0, iterations are unlimited.
If a query exceeds the limit, Jaspersoft OLAP returns an error that specifies this property’s value. For more information, refer to the Query Limit and Result Limit entries in this table.
If > 0, crossjoin result limit beyond which the optimizer will be applied (number of rows)
When set to a number greater than 0, a threshold for a crossjoin input list's size; if it exceeds this value, and the axis has the NON EMPTY qualifier, Jaspersoft OLAP uses the non-empty optimizer.
When this property is set to 0, Jaspersoft OLAP applies the non-empty optimizer to all crossjoin input lists. To ensure that the optimizer is never applied to crossjoin input lists, set this value to the Integer.MAX_VALUE.
If there are unrelated dimensions to a measure in context during aggregation, the measure is ignored in the evaluation context
When dimensions unrelated to a measure are detected during aggregation, the measure is ignored in the evaluation context. Note that this property can only affects measures whose CubeUsage’s IgnoreUnrelatedDimensions is false.
For more information, refer to the Mondrian Technical Guide.
Do elements of a dimension (levels, hierarchies, members) need to be prefixed with dimension name in the MDX query
Determines if elements of dimension (levels, hierarchies, and members) must be prefixed with the dimension name in MDX queries.
This property determines whether certain queries succeed or fail based on the way the dimension elements are defined in the MDX query. When this property is enabled, this query fails:
select {[Omaha]} on columns from cust
When this property is disabled, that same query succeeds.
With the property enabled, the correct query is:
select {[Nebraska].[Omaha]} on columns from cust
If your schema is very large or complex, Jaspersoft recommends enabling this setting, as processing such schemas can be very resource-intensive.
For more information, refer to the Mondrian Technical Guide.
Division by null or zero produces NULL
By default, when Jaspersoft OLAP attempts to divide by zero or null, it evaluates to Infinity, which is correct for some analysis providers. When this property is enabled, Jaspersoft OLAP instead evaluates such calculations to null, which can be helpful in certain circumstances.
Solve Order evaluation behavior
Specifies how to evaluate the SOLVE_ORDER:
• | Absolute. Any specified scope is ignored and the SOLVE_ORDER value is absolute. For example, Jaspersoft OLAP gives precedence to a query-defined calculated member with a SOLVE_ORDER of 1 over a cube defined value of 2. |
• | Scoped. Jaspersoft OLAP first resolves cube-calculated members, then resolves session-scoped members, and finally resolves query-defined calculations. Jaspersoft OLAP only applies the SOLVE_ORDER value within the scope in which it was defined. |
Enable Aggregate Tables
When enabled, Jaspersoft OLAP identifies tables in the database that are aggregates, and uses those aggregate values.
For more information, refer to the Mondrian Technical Guide.
Choose Aggregate Table By Volume
Performance tuning variable for aggregates. This property only applies when aggregate tables are used. Consider using this property when optimizing for tables with many large columns. this property determines whether aggregate tables are ordered by volume or row count.
When this property is enabled, Jaspersoft OLAP uses the aggregate table with the smallest volume (number of rows multiplied by number of columns); when it is disabled, Jaspersoft OLAP uses the aggregate table with the fewest rows.
Optimize predicates
Determines whether Jaspersoft OLAP optimizes predicates. When this property is enabled, Jaspersoft OLAP optimizes certain predicates.
When it is disabled, predicates are only optimized when all of a dimension’s members are included. In this case, Jaspersoft OLAP only retrieves the data specified in the query.
For more information, refer to the Mondrian Technical Guide.
Rule file for aggregate table identification
Specifies a file that defines aggregate table recognition rules. This file can either reside in the application server or the file system. Typically, you can accept the default.
AggRule element's tag value
The AggRule element's tag value. Typically, you can accept the default.
SQL to log for aggregate table creation to support MDX with aggregates
Determines whether Jaspersoft OLAP prints the SQL code generated for aggregate tables.
When this property is enabled, Jaspersoft OLAP processes each aggregate request and prints both the lost and collapsed dimension create and insert SQL statements. When used in conjunction with the CmdRunner, it lets you automatically create aggregate table generation SQL.
Factory class for determining the tables and columns of a data source
If entered, this must be a Java class name that is an implementation of the mondrian.rolap.aggmatcher.JdbcSchema.Factory interface. The default implementation is mondrian.rolap.aggmatcher.JdbcSchema.StdFactory. Refer to the Mondrian Javadoc for more information.
Cache and SQL Generation Settings
Use a cache for the results of frequently evaluated expressions
Determines whether Jaspersoft OLAP caches the results of frequently-evaluated expressions. Enabling this property can reduce the number of unnecessary computations when processing a large amount of data.
Cache RolapCubeMember objects
Determines whether Jaspersoft OLAP caches RolapCubeMember objects, which each associate a member of a shared hierarchy with a cube that uses it.
Disable this property if you plan to use the member cache control.
Maximum number of constraints in a single `IN' SQL clause
The maximum number of constraints in a single SQL IN clause. This value varies with your RDBMS type and configuration. Jaspersoft recommends these values:
• | DB2: 2,500 |
• | MySQL: 10,000 |
• | Oracle: 1,000 |
• | Postgres: 10,000 |
• | Other: 10,000 |
Some NON EMPTY CrossJoin MDX statements will be computed in the database
When enabled, Jaspersoft OLAP pushes down to the database processing related to some NON EMPTY CrossJoin MDX statements.
Some TopCount MDX statements will be computed in the database
When enabled, Jaspersoft OLAP pushes down to the database processing related to some TopCount MDX statements.
Some Filter() MDX statements will be computed in the database
When enabled, Jaspersoft OLAP pushes down to the database processing related to some Filter() MDX statements.
Some NON EMPTY MDX statements will be computed in the database
When enabled, Jaspersoft OLAP pushes down to the database processing related to some NON EMPTY MDX set operations (Such as member.children, level.members and member.descendants).
Expand non native sub-expressions of a native expression into MemberLists
When enabled, Jaspersoft OLAP expands non-native sub-expressions of a native expression into MemberLists.
Alerting action in case native evaluation of a function is enabled but not supported in a particular query
Specifies the error level (OFF, WARN, or ERROR) to use when a function’s native evaluation is enabled but isn’t supported in the context of a particular query's usage. Errors are only returned in the case of a NonEmptyCrossJoin.
Alerts are only raised when there is a possibility that native evaluation would improve matters.
Some rollup queries will be combined using group by grouping sets if the SQL dialect allows it
When enabled, Jaspersoft OLAP combines some rollup queries using group by grouping sets. Note that this property only applies to data stored in Oracle, DB2, or Teradata.
XML/A Maximum Drill Through Rows
Limits the number of rows returned from an XML/A drill-through operation.
First row in the result of an XML/A drill-through request will be filled with the total count of rows
If this property is enabled, the first row returned for an XML/A drill-through request shows the total count of rows in the underlying database.
Use Java Memory monitoring to avoid out of memory
When enabled, Jaspersoft OLAP uses the Java’s memory monitoring capability. In this case, when memory is exhausted, Jaspersoft OLAP returns a MemoryLimitExceededException exception rather than an out of memory error.
Threshold to limit memory used (percent of total memory
Specifies the percent of memory usage that should trigger a notification to Jaspersoft OLAP that memory is low.
Class name of factory to manage memory
If entered, this must be a Java class name that is an implementation of the mondrian.util.MemoryMonitor interface. Such a class (mondrian.util.NotificationMemoryMonitor). Refer to the Mondrian Javadoc for more information.
These properties are stored in the repository (at /properties) as a list of values called GlobalPropertiesList. This list is populated the first time you alter one of the properties. Jaspersoft recommends that you allow JasperReports Server to manage this list of values for you automatically; therefore, only change the values using the Manage > Server Settings menu option.
For more information about these properties, refer to the Mondrian Technical Guide. Use the name of each property (shown below each property name) when searching this reference.
Many other properties can be set by editing configuration files, but some of them do not affect Jaspersoft OLAP because they are controlled in the repository user interface. For example, you do not have to provide a connectString property, or to specify the jdbcDrivers for Mondrian, because Jaspersoft OLAP automatically generates these properties when the DataSource for an AnalysisClientConnection is selected.
To log the SQL queries Jaspersoft OLAP sends to the database, set the Generate Formatted SQL Traces option. This ensures that Jaspersoft OLAP writes the SQL queries it executes to the log file you specify. |
Recommended Comments
There are no comments to display.