Modules:Analytics Configuration
| Languages: |
English | Français | Translate this article... |
Contents |
Configuring Mondrian
Openbravo Analytics uses a sensible standard set of mondrian.properties (see below). For information on the valid Mondrian properties, see the Mondrian documentation.
Openbravo has an internal file to store the default mondrian properties. To override you have to do the following:
- create a file called mondrian.properties with the properties
- place this file in the src folder of a custom module
If there is a mondrian.properties in the root of the classpath then Openbravo will not read its own file, but instead use your file.
The mondrian.default.properties used by Openbravo:
# Allow the use of aggregates mondrian.rolap.aggregates.Use=false mondrian.rolap.aggregates.Read=false # mondrian.properties mondrian.result.limit=5000000 ###################################################################### # Integer property indicating timeout value, in seconds, for queries. # Default of 0 indicates no timeout mondrian.rolap.queryTimeout=300 # Property which controls the amount of tracing displayed. Seperate from log4j. mondrian.trace.level=0 # Maximum number of simultaneous queries the system will allow. mondrian.query.limit=40 # Property which determines when a dimension is considered "large". mondrian.rolap.LargeDimensionThreshold=100 # Boolean property that controls whether a RolapStar's aggregate data cache is cleared after each query. mondrian.rolap.star.disableCaching=false # Boolean property which controls SQL pretty-print mode. mondrian.rolap.generate.formatted.sql=true # Boolean property which controls whether the MDX parser resolves uses case-sensitive matching when looking up identifiers. mondrian.olap.case.sensitive=false # Boolean property which controls whether to use a cache for frequently evaluated expressions. mondrian.expCache.enable=true # if enabled some NON EMPTY CrossJoin will be computed in SQL mondrian.native.crossjoin.enable=true # if enabled some TopCount will be computed in SQL mondrian.native.topcount.enable=false # if enabled some Filter() will be computed in SQL mondrian.native.filter.enable=true # some NON EMPTY set operations like member.children, level.members and member descendants will be computed in SQL mondrian.native.nonempty.enable=false # Max number of constraints in a single `IN' SQL clause. mondrian.rolap.maxConstraints=1000 # Property which defines the maximum number of passes allowable while evaluating an MDX expression. mondrian.rolap.evaluate.MaxEvalDepth=10 ############################################################################### # Boolean property indicating whether errors related to non-existent members # should be ignored during schema load. If so, the non-existent member is # treated as a null member. mondrian.rolap.ignoreInvalidMembers=true ############################################################################### # Boolean property indicating whether errors related to non-existent members # should be ignored during query validation. If so, the non-existent member is # treated as a null member. mondrian.rolap.ignoreInvalidMembersDuringQuery=true ############################################################################### # Integer property indicating the maximum number of iterations allowed when # iterating over members to compute aggregates. Default of 0 indicates no # limit. mondrian.rolap.iterationLimit=5000000 ############################################################################### # String property which controls alerting behavior in case native # evaluation of a function is enabled but not supported for that # function's usage in a particular query. (No alert is ever raised in # cases where native evaluation would definitely have been wasted # effort.) Values recognized are { OFF, WARN, ERROR }. mondrian.native.unsupported.alert=WARN ############################################################################### # Boolean property which controls whether sibling members are # compared according to order key value fetched from their ordinal # expression. The default is false (only database ORDER BY is used). mondrian.rolap.compareSiblingsByOrderKey=true ############################################################################### # If true, a division having a non-null numerator and a null denominator # evaluates to Infinity. If false, the same division results in Null. mondrian.olap.NullDenominatorProducesNull=true ############################################################################### # Boolean property which controls whether to expand the non native sub- # expressions of a native expression into MemberLists mondrian.native.ExpandNonNative=true ############################################################################### # Property determines if elements of dimension (levels, hierarchies, members) # need to be prefixed with dimension name in MDX query. # # For example when the property is true, the following queries # will error out. The same queries will work when this property # is set to false. # * select {[M]} on 0 from sales # * select {[USA]} on 0 from sales # * select {[USA].[CA].[Santa Monica]} on 0 from sales # # When the property is set to true, any query where elements are # prefixed with dimension name as below will work # * select {[Gender].[F]} on 0 from sales # * select {[Customers].[Santa Monica]} on 0 from sales # # Please note that this property does not govern the behaviour where in # * [Gender].[M] # is resolved into a fully qualified # * [Gender].[All Gender].[M] # # In a scenario where the schema is very large and dimensions have large # number of members a MDX query that has a invalid member in it will cause # mondrian to to go through all the dimensions, levels, hierarchies, members # and properties trying to resolve the element name. This behaviour consumes # considerable time and resources on the server. Setting this property to # true will make it fail fast in a scenario where it is desirable # mondrian.olap.elements.NeedDimensionPrefix=true
Mondrian Mapping Webservice
The Mondrian mapping for the current user can be retrieved using a specific web url:
http://localhost:8080/openbravo/analytics/schema
Replace the host/port/context with the settings relevant for your installation.
Performance & Caching
Mondrian caches cell regions when MDX queries are being executed. This results in that subsequent queries are read from memory and are therefore much faster.
For specific information on Mondrian caching see this Mondrian documentation.
Openbravo Analytics automatically detects if certain user MDX queries take too much time (see below). These queries are automatically identified as cacheable queries and stored as Cube Cache Preload Definitions.
The cache preload definitions shows the following information:
- the name and description
- the MDX query
- the number of times it was run by users
- a flag to enable or disable preloading of this MDX by the cache preload process. This flag is enabled automatically if the same MDX query is executed several times (the treshold is defined through a property, see below).
- last reload result (success or an error message)
To enable preloading of the cache (at night for example) setup a process request which performs cache preloading at specific intervals (see the section on Mondrian integration processes). This process will read the cache preload definitions and execute each MDX query to load the data in memory.
 |
Notes:
|
The system analyzes the query timings and decides to store the queries to use for preloading, this logic can be configured with 2 properties in the Openbravo.properties file:
- analytics.cache_treshold: in milliseconds (default: 60000 milliseconds), if a MDX query takes more than this time it is stored to be used in the preload process (note: initially it won't be enabled for preloading, only stored in the cache preload definition table)
- analytics.preload_enabled_treshold: integer number (default: 10), if the same MDX query is too slow for more than this number of times then it will be enabled for preloading.
You can also manually create pre-load MDX queries and enable queries for preloading manually.
For example this setting will store each MDX which takes more than 30 seconds (30000 milliseconds) and enable it for preloading if the same MDX was executed more than 2 times.
analytics.cache_treshold=30000 analytics.preload_enabled_treshold=2
Saved Queries
Saiku/MDX queries are saved in the Analytics Queries window, from two directions:
- from the generic OB Analytics window
- from the finance dimensional cubes
Both are described above in more detail. The saved query is a xml document containing both the MDX as well as Saiku metadata. The publish checkbox defines if the query can be retrieved using the saved query process.
Analytics on distributed servers
Openbravo Analytics can possibly put occasional heavy load on your server. The load will be on the application server as well as on the database side.
Openbravo Analytics allows two different methods to distribute the load on multiple servers. The first light-weight and easier to setup, distributing the database load to another server. The second, more setup work but also allowing to run a separate application server specifically for analytics. The second approach means that a user has to visit a different URL to access analytics information.
Running Analytics using a different database server
Note: available from analytics module version 12.1.110.
In this scenario Openbravo analytics performs the sql queries on a second (replicated) database.
Configuration/setup: you need to setup a second database which is replicated from the production database. The fact tables reside in the production database and are updated in the production database. The replication will transfer all the changes to the (read-only) analytics database.
This setup has to be done on-site in your local database environment. Postgresql/Oracle provide standard procedures to setup such an environment.
The replication has to be run either streaming or after the fact table update process has run.
To let Openbravo analytics use the second database server you have to specify a set of bbdd properties in the Openbravo.properties file:
mondrian.bbdd.url=jdbc:postgresql://other.db.server:5432 mondrian.bbdd.sid=prod_analytics mondrian.bbdd.user=tad mondrian.bbdd.password=tad
if the specific mondrian property can not be found then the property without the 'mondrian.' prefix will be used from the Openbravo.properties (so if mondrian.bbdd.sid is not set then the system will use bbdd.sid).
This setup is quite lightweight, it does not change the URL for the user to use and it offloads the sql-processing to another server.
Running Analytics in a specific application server
To prevent standard users from being hurt by analytics processes it is possible to assign specific application servers to perform analytics. Other servers will then not execute analytics queries and therefore not take system performance.
To prevent a server from executing analytics queries set the following property in the Openbravo.properties file on that application server:
mondrian.server = false
Then on the server which runs analytics you don't need to set this parameter.
To access analytics a user always has to use the specific analytics server in the url in the browser.
Note:
- the analytics server can not be setup with a readonly database as Openbravo will update session/log tables when a user logs in.
- a longer term future topic is to allow one server on which a user accesses Openbravo, but analytics calls are forwarded from this single server to separate analytics servers.