# Multidimensional Data Modeling in Pentaho Pentaho Business Analytics is built on the Mondrian online analytical processing (OLAP) engine. OLAP relies on a multidimensional data model that, when queried, returns a dataset that resembles a grid. The rows and columns that describe and bring meaning to the data in that grid are dimensions, and the hard numerical values in each cell are the measures or facts. In Pentaho Analyzer, dimensions are shown in yellow and measures are in blue. OLAP requires a properly prepared data source in the form of a star or snowflake schema that defines a logical multi-dimensional database and maps it to a physical database model. When you have the initial data structure in place, you must design a descriptive layer for it in the form of a Mondrian schema, which consists of one or more cubes, hierarchies, and members. Only when you have a tested and optimized Mondrian schema is the data prepared on a basic level for end-user tools like Pentaho Analyzer. Pentaho also offers expanded functionality in Pentaho Analysis Enterprise Edition, including: * The Pentaho Analyzer visualization tool. * A pluggable Enterprise Cache with support for highly scalable, distributable cache implementations including Infinispan and Memcached. A special Pentaho Server package must also be installed. This process is covered in [Installation of the Pentaho design tools](https://docs.pentaho.com/install/pentaho-installation-overview-cp/installation-of-the-pentaho-design-tools). All relevant configuration options for these features are covered in this section. ### In this topic * [Prepare your data](#prepare-your-data) * [Dimensional modeling](#dimensional-modeling) * [Understanding data cubes](#understanding-data-cubes) * [Map a model with Schema Workbench](#map-a-model-with-schema-workbench) * [Configure Mondrian engine](#configure-mondrian-engine) * [Mondrian cache control](#mondrian-cache-control) * [Analysis schema security](#analysis-schema-security) * [OLAP log output](#olap-log-output) * [Multidimensional Expression Language](#multidimensional-expression-language) ### Prepare your data To prepare data for use with the Pentaho Analyzer and Report Designer, you should perform the following basic tasks: * **Design a star or snowflake schema** The entire process starts with a data warehouse. This section will not attempt to explain how to build this structure -- there are entire books on the subject, and an entire consulting industry dedicated to it already. The end result should be data model in the star or snowflake schema pattern. You do not have to worry too much about getting the model exactly right on your first try. Just cover all of your anticipated business needs; part of the process is coming back to the data warehouse design step and making changes to your initial data model after you have discovered what your operational needs are. * **Populate the star or snowflake schema** Once your data model is designed, the next step is to populate it with actual data, thereby creating your data warehouse. The best tool for this job is Pentaho Data Integration, an enterprise-grade extract, transform, and load (ETL) application. * **Build a Mondrian schema** Now that your initial data warehouse project is complete, you must build a Mondrian schema to organize and describe it in terms that Pentaho Analyzer can understand. You can use the Pentaho Schema Workbench to create an analysis schema. * **Initial testing** At this point, you should have a multi-dimensional data structure with an appropriate metadata layer. You can now start using the data inspection tools to drill down into your data and see if your first attempt at data modeling was successful. See the **Pentaho Data Integration** document for details on our data inspection tools. In all likelihood, it will need some adjustment, so take note of all of the schema limitations that you are unhappy with during this initial testing phase. Do not be concerned with performance issues at this time, just concentrate on the completeness and comprehensiveness of the data model. * **Adjust and repeat until satisfied** Use the notes you took during the testing phase to redesign your data warehouse and Mondrian schema appropriately. Adjust hierarchies and relational measure aggregation methods. Create virtual cubes for analyzing multiple fact tables by conforming dimensions. Re-test the new implementation and continue to refine the data model until it matches your business needs perfectly. * **Test for performance** Once you are satisfied with the design and implementation of your data model, you should try to find performance problems and address them by tuning your data warehouse database, and by creating aggregation tables. The testing can only be reasonably done by hand, using Pentaho Analyzer. Take note of all of the measures that take an unreasonably long time to calculate. Also, enable SQL logging and locate slow-performing queries, and build indexes for optimizing query performance. * **Create aggregation tables** Using your notes as a guide, create aggregation tables in Pentaho Aggregation Designer to store frequently computed Analyzer reports. Re-test and create new aggregation tables as necessary. If you are working with a relatively small data warehouse or a limited number of dimensions, you may not have a real need for aggregation tables. However, be aware of the possibility that performance issues may come up in the future. Check in with your users occasionally to see if they have any particular concerns about the speed of their content. * **Deploy to production** Your data warehouse and Mondrian schema have been created, tested, and refined. You're now ready to put it all into production. You may need to train or purchase Pentaho training for those in your organization who use Pentaho client tools. ### Dimensional modeling With the initial data structure in place, you can use dimensional modeling to design a descriptive layer. Dimensional modeling is the process of transforming data from multiple sources in non-human-friendly formats into a single data source that is organized to support business analytics. Below is a typical workflow for developing a dimensional model: 1. Collect user requirements for business logic and processes. 2. Considering the entirety of your data, break it down into subjects. 3. Isolate groups of facts into one or more fact tables. 4. Design dimensional tables which draw relationships between levels (fact groups). 5. Determine which members of each level are useful for each dimensional table. 6. Build and publish a Mondrian (Pentaho Analyzer) schema and collect feedback from users. 7. Refine your model based on user feedback, continue iterating through this list until users are productive. Or, expressed as a series of questions: 1. What topics or subjects are important to the users who are analyzing the data? What do your users need to learn from the data? 2. What are the important details your users will need to examine in the data? 3. How should each data column relate to other data columns? 4. How should datasets be grouped and organized? 5. What are some useful short descriptions for each dimensional level in a hierarchy (for each element, decide what is useful within that element; for instance, in a dimensional table representing time, your levels might be year, month, and day, and your members for the year level might be 2003, 2004, 2005). 6. How effective is this dimensional model for the intended user base? How can it be improved? Pentaho Data Integration offers data inspection tools to make dimensional modeling much easier than more traditional methods. Through PDI, you can quickly adjust your business logic, the granularity of your fact tables, and the attributes of your dimension tables, then generate a new model and push it out to a test environment for evaluation. See the **Pentaho Data Integration** document for details. ### Understanding data cubes Another name for a dimensional model is a cube. Each cube represents one fact table and several dimensional tables. This model should be useful for reporting and analysis on the subject of the data in the fact table. However, if you want to cross-reference this data with another cube (if you need to analyze data across two or more cubes, or need to combine information from two fact tables on the same subject but with different granularity) then you must create a virtual cube. The XML elements that compose a virtual cube are explained in detail below. **Note:** Virtual cubes cannot presently be created through Pentaho Data Integration's model perspective; you must use Schema Workbench instead. The **\** element specifies the cubes that are imported into the virtual cube. It holds **\** elements. The **\** element specifies the base cube that is imported into the virtual cube. Alternatively you can define a **\** and use similar imports from the base cube without defining a **\**. The `cubeName` attribute specifies the name of the base cube. The `ignoreUnrelatedDimensions` attribute determines whether or not the measures from this base cube will have non-joining dimension members pushed to the top level member. This attribute is false by default because it is still experimental. The **\** element imports a dimension from one of the constituent cubes. If you do not specify the `cubeName` attribute, this means you are importing a shared dimension. **Note:** If a shared dimension is used more than once in a cube, there is no way to determine which usage of the shared dimension you intend to import. The **\** element imports a measure from one of the constituent cubes. It is imported with the same name. If you want to create a formula or rename a measure as you import it, use the **\** element instead. Virtual cubes are useful for situations where there are fact tables of different granularities (for instance, one Time fact table might be configured on a Day level, another at the Month level), or fact tables of different dimensionalities (for instance one on Products, Time and Customer, another on Products, Time and Warehouse), and you need to present the results to users who don't know how the data is structured. Any common dimensions, shared dimensions which are used by both constituent cubes, are automatically synchronized. In this example, `[Time]` and `[Products]` are common dimensions. So if the context is (`[Time].[2005].[Q2], [Products].[Productname].[P-51-D Mustang]`), measures from either cube will relate to this context. Dimensions which only belong to one cube are called non-conforming dimensions. The `[Gender]` dimension is an example of this; it exists in the Sales cube, but not Warehouse. If the context is (`[Gender].[F], [Time].[2005].[Q1]`), it makes sense to ask the value of the `[Unit Sales]` measure (which comes from the `[Sales]` cube) but not the `[Units Ordered]` measure (from `[Warehouse]`). In the context of `[Gender].[F], [Units Ordered]` has value `NULL`. ### Map a model with Schema Workbench With a physical multidimensional data model in place, you must create a logical model that maps to it. A Mondrian schema is essentially an XML file that performs this mapping, thereby defining a multidimensional database structure. You can create Mondrian schemas using the Pentaho Schema Workbench. See the **Pentaho Schema Workbench** document for instructions. ### Configure Mondrian engine The Mondrian engine is configured through `mondrian.properties`. Use these settings to improve performance and behavior in Analyzer. * [Manage Analyzer data sources](#manage-analyzer-data-sources) * [Edit the Mondrian properties file](#edit-the-mondrian-properties-file) #### Manage Analyzer data sources To use a prepared data source with Pentaho Analyzer, you must define it first. Use one of these approaches: * Create a Native (JDBC) data source in the User Console. * Create a JNDI data source in your web application server. You must know the data source name later. You supply this name in Pentaho Schema Workbench. {% hint style="info" %} Schema Workbench supports one data source per Mondrian schema. You can define multiple data sources, but you can use only one at a time. {% endhint %} #### Edit the Mondrian properties file `mondrian.properties` is in `pentaho/server/pentaho-server/pentaho-solutions/system/mondrian`. Edit this file to tune Mondrian for your environment. {% hint style="info" %} This guidance targets Pentaho Analyzer performance. For all settings, see . {% endhint %} **Performance settings** Push as much work to the database as possible. This matters most for high-cardinality dimensions. It avoids large, sparse Cartesian products in memory. * `mondrian.expCache.enable=true` Improves summary calculations, like ranks and running sums. * `mondrian.native.crossjoin.enable=true` Enables database pushdown for cross joins. Prevents large in-memory cross joins. * `mondrian.native.filter.enable=true` Pushes `NOT IN` and exclude filters to the database. * `mondrian.native.nonempty.enable=false` Optimizes SQL generation for Analyzer-generated MDX. Keeps `NON EMPTY` cross joins in the database. * `mondrian.olap.maxConstraints=1000` Used with `mondrian.native.ExpandNonNative`. Increase this as your database allows. * `mondrian.native.ExpandNonNative=true` Pushes down more cross joins. Materializes inputs into `IN` lists when needed. * `mondrian.olap.elements.NeedDimensionPrefix=true` Analyzer generates dimension-qualified members. Mondrian can skip cross-dimension member searches. **Usability settings** These settings reduce user-facing failures and confusing results. * `mondrian.result.limit=5000000` Caps the largest cross join Mondrian will process in memory. This limit avoids long server hangs when pushdown fails. Analyzer can warn users when this limit is reached. * `mondrian.olap.case.sensitive=true` Preserves casing for equals filters. Example: `John Doe` matches only `John Doe`. * `mondrian.olap.ignoreInvalidMembers=true` Keeps saved reports running when members disappear after ETL. * `mondrian.olap.ignoreInvalidMembersDuringQuery=true` Use this with `mondrian.olap.ignoreInvalidMembers`. * `mondrian.olap.iterationLimit=5000000` Limits aggregate evaluation work. This is similar to `mondrian.result.limit`. * `mondrian.olap.compareSiblingsByOrderKey=true` Enables A–Z and Z–A member sorting. This fixes a Mondrian sorting issue. * `mondrian.olap.NullDenominatorProducesNull=true` Controls divide-by-null behavior. Example: `Booked Deals / Quota`. When `true`, divide-by-null returns null. `NON EMPTY` can then filter those rows out. When `false`, divide-by-null can return infinity. ### Mondrian cache control You can configure and control the cache infrastructure that the Pentaho Analyzer engine uses for OLAP data, which is useful for properly updating your OLAP cubes when your data warehouse is refreshed, and for performance-tuning. Most of the advanced cache features explained here are for Enterprise Edition deployments only. Within that, most of the Enterprise Edition features of the Analysis engine are only beneficial to large, multi-node OLAP deployments that are performing poorly. The Analysis engine does not ship with a segment cache, but it does have the ability to use third-party cache systems. If you've installed Pentaho Analysis Enterprise Edition, then you have a default configuration for the JBoss Infinispan distributed cache, though the actual Infinispan software is not included and must be downloaded separately. Infinispan supports a wide variety of sub-configurations and can be adapted to cache in memory, to the disk, to a relational database, or (the default setting) to a distributed cache cluster. The Infinispan distributed cache is a highly scalable solution that distributes cached data across a self-managed cluster of Mondrian instances. Every Mondrian instance running the Analysis Enterprise Edition plugin on a local network will automatically discover each other using UDP multicast. An arbitrary number of segment data copies are stored across all available nodes. The total size of the cache will be the sum of all of the nodes' capacities, divided by the number of copies to maintain. This is all fully configurable; options are explained later in this section. #### Cache control topics * [Switch to Memcached](#switch-to-memcached) * [Segment cache architecture](#segment-cache-architecture) * [Cache configuration files](#cache-configuration-files) * [Modify the JGroups configuration](#modify-the-jgroups-configuration) * [Switch to another cache framework](#switch-to-another-cache-framework) #### Switch to Memcached Use this procedure when you want Mondrian segment caching to use Memcached. This replaces the default Infinispan cache framework configuration. {% hint style="warning" %} Pentaho and Mondrian developers recommend against using Memcached. Infinispan usually gives better performance. {% endhint %} **Before you begin** * Set up your Memcached cluster. * Install the Analysis Enterprise Edition package on your Pentaho Server or standalone Mondrian engine. **Switch from Infinispan to Memcached** 1. Shut down the Pentaho Server or standalone Mondrian engine. 2. Verify the required JARs exist in `WEB-INF/lib/` in your deployed `pentaho.war` or Mondrian engine. If you installed the Analysis Enterprise Edition package, you usually already have these files. Required JARs: * `pentaho-analysis-ee` * `commons-lang` * `commons-io` * `commons-codec` * `pentaho-ee-dsc-core` * `memcached` 3. Edit `pentaho-analysis-config.xml` in `WEB-INF/classes/` and set `SEGMENT_CACHE_IMPL`: ```xml com.pentaho.analysis.segmentcache.impl.memcached.MemcachedSegmentCache ``` 4. Edit `memcached-config.xml` in `WEB-INF/classes/` and set `SALT`, `SERVERS`, and `WEIGHTS`: ```xml YOUR SECRET SALT VALUE HERE 192.168.0.1:1642,192.168.0.2:1642 1,1 ``` Restart the Pentaho Server or Mondrian engine. **Memcached configuration options** Set these options in `memcached-config.xml` in `WEB-INF/classes/` in your deployed `pentaho.war` or Mondrian engine. This list is not comprehensive. It covers the options most critical for Pentaho Analyzer. * **SERVERS**: Comma-separated list of Memcached nodes and port numbers. * **WEIGHTS**: Comma-separated list of relative capacities for the servers in `SERVERS`. Use one value per server. For example, `1,2` means the second server has double capacity. * **SALT**: Secret key prefix for segment data. Use the same value on all Mondrian nodes. If it differs, nodes cannot share cached data. #### Segment cache architecture Each Mondrian segment cache node, regardless of configuration, loads the segments required to answer a query into system memory. This cache space is called the **query cache**. It is composed of hard Java references to segment objects. Each node must have enough memory available to answer any query. Mondrian uses optimized data structures that usually take only a few megabytes, even for queries returning thousands of rows. After the query finishes, Mondrian usually tries to keep the data locally by using a weak reference to the segment data object. A weak reference does not force the JVM to keep an object in memory. As a node keeps answering queries, the JVM may free that space for higher-priority work, such as answering a larger query. This cache is the **local cache**. You can enable or disable the local cache in the Pentaho Analysis EE configuration file. Set `DISABLE_LOCAL_SEGMENT_CACHE` to `true` or `false`. This setting does not affect the query cache. {% hint style="info" %} The segment cache features in this topic target large OLAP deployments. {% endhint %} **How the Analysis engine uses memory** This is the order in which Mondrian obtains data for a required segment after receiving a query: 1. Parse the query and determine which segments are required. 2. Check the local cache, if enabled. 3. If the data is not in the local cache, check the external segment cache (Pentaho Analysis plugin). If found, copy it into the query cache. 4. If the data is not in the external cache, load it from SQL and store it in the query cache. 5. If the data was loaded from SQL, send the segment to the external cache so other Mondrian nodes can reuse it. 6. Answer the query. 7. Release the data from the query cache. 8. If the local cache is enabled, keep a weak reference to the data in the local cache. ![How the Analysis Engine Used Memory](https://2345962715-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqfaQ2p0JAZrP8b3cpM9a%2Fuploads%2Fgit-blob-ec3df32e6c3b764fd5c638682e412b0582a91230%2FMondrianSegmentCacheSPI_new.png?alt=media) **Cache control and propagation** The CacheControl API lets you modify the cache contents of a node. It controls both the data cache and the OLAP schema member cache. The API is documented in the Mondrian project documentation at . When you flush a segment region on a node, that node propagates the change to the external cache through the **SegmentCache SPI**. If nodes are not using local cache space, the next node that receives a query requiring that segment data will likely fetch it again through SQL. After loading from SQL, the node stores the segment in the external segment cache again. Do not use the local cache space when you use the external cache. For this reason, the local cache is disabled by default in Pentaho Analysis Enterprise Edition. Local cache space can improve performance because it increases data locality. It also requires cluster-wide propagation of cache control operations. Mondrian nodes do not propagate cache control operations across cluster members. If you deploy a cluster of Mondrian nodes and do not propagate changes manually across all nodes, some nodes will answer queries with stale data. #### Cache configuration files You can configure the Pentaho analysis cache frameworks by editing specific settings files. The following files contain configuration settings for Pentaho analysis cache frameworks. All of them are in the same directory inside of the deployed `pentaho.war`: `/WEB-INF/classes/`. * `pentaho-analysis-config.xml`: Defines the global behavior of the Pentaho Analysis Enterprise Edition plugin. Settings in this file enable you to define which segment cache configuration to use, and to turn off the segment cache altogether. * `infinispan-config.xml`: The **InfinispanSegmentCache** settings file. It configures the Infinispan system. * `jgroups-udp.xml`: Configures the cluster backing the Infinispan cache. It defines how the nodes find each other and how they communicate. By default, Pentaho uses UDP and multicast discovery, which enables you to run many instances on a single machine or many instances on many machines. There are examples of other communication setups included in the JAR archive. This file is referenced by infinispan as specified in the `infinispan-config.xml` configuration file. * `memcached-config.xml`: Configures the Memcached-based segment cache. It is not used by default. To enable it, modify **SEGMENT\_CACHE\_IMPL** in `pentaho-analysis-config.xml`. #### Modify the JGroups configuration The default Infinispan configuration uses JGroups to distribute the cache across all Mondrian instances it finds on the local network. If you want to modify how those communications are done, you must edit the JGroups configuration file. {% hint style="info" %} The segment cache features in this topic target large OLAP deployments. {% endhint %} Each node might require a different configuration. The default configuration is highly portable. If you are deploying this plugin on Amazon EC2, JGroups has a configuration file that you copied to your `/WEB-INF/classes/` directory when you installed the Analysis Enterprise Edition package. Default JGroups configuration files are inside of the JAR archive. Fine-grained JGroups configuration is covered in the [JGroups documentation](http://www.jgroups.org/ug.html). Read it before making changes. To switch implementations, edit `infinispan-config.xml` and set the appropriate `configurationFile` value: * UDP communication: ```xml ``` * TCP communication: ```xml ``` * Amazon EC2: ```xml ``` #### Switch to another cache framework Pentaho ships with configuration files that assume a JBoss Infinispan deployment. {% hint style="info" %} The segment cache features in this topic are for very large OLAP deployments. {% endhint %} Pentaho recommends Infinispan for best OLAP performance. **Choose your cache framework** * [Switch to Memcached](#switch-to-memcached) * [Switch to Pentaho Platform Delegating Cache](#switch-to-pentaho-platform-delegating-cache) * [Use a custom SegmentCache SPI](#use-a-custom-segmentcache-spi) **Switch to Pentaho Platform Delegating Cache** You must install the Analysis Enterprise Edition package on the Pentaho Server. Use this option to share the Pentaho Server solution cache with the Analyzer segment cache. {% hint style="warning" %} This cache system is experimental. Do not use it in production. {% endhint %} **Use a custom SegmentCache SPI** Use this approach when you want your own cache implementation. 1. Create a Java class that implements `mondrian.spi.SegmentCache`. 2. Compile your class and add it to Mondrian’s classpath. 3. Edit `mondrian.properties`. 4. Set `mondrian.olap.SegmentCache` to your fully qualified class name. 5. Restart the Pentaho Server or Mondrian engine. Contact Pentaho Support for developer assistance. ### Analysis schema security You can restrict what users see in your Mondrian OLAP schema. You do this by defining roles in the schema. Then map those schema roles to Pentaho Server roles. Pentaho Server then enforces those restrictions in Analyzer and Dashboard Designer. {% hint style="info" %} Use Schema Workbench to change an OLAP schema. Republish the schema to Pentaho Server after changes. Edit XML configuration files with a text editor. For example, Pentaho Server and Mondrian engine settings. {% endhint %} #### Restrict access to specific members Restrict access to parts of a schema with the `` element. Use it to define access at the hierarchy level. The following example grants access to most of California. It also hides Los Angeles. ```xml ``` **`access` attribute values** The `access` attribute can be: * `all`: All members are visible. * `none`: The hierarchy is hidden. * `custom`: Customized access. **Custom access options** With `access="custom"`, you can: * Use `topLevel` to define the highest visible level. * Use `bottomLevel` to define the lowest visible level. * Control member visibility with nested `` elements. You can only define `` when the enclosing `` has `access="custom"`. Member grants apply to a member and all its children. Rules to keep in mind: * Members inherit access from their parents. * Grants are order-dependent. * A member is visible if any child is visible. * Member grants do not override `topLevel` and `bottomLevel`. #### Mondrian role mapping in the Pentaho Server The role mapper connects user role restrictions in a Mondrian schema to roles in the Pentaho Server. This lets you manage schema access control in one place. If you do not configure a role mapper, roles defined in your schema do not restrict access in Pentaho Server. **Configure the role mapper** Configure the role mapper in `pentaho-solutions/system/pentahoObjects.spring.xml`. The file includes disabled example configurations for each role mapper implementation. Enable the one you want to use. **Mapper implementations** **Mondrian One-To-One UserRoleMapper** `Mondrian-One-To-One-UserRoleMapper` maps each Pentaho Server role name to a role in the OLAP schema. Use this mapper when your schema role names match your Pentaho Server role names. For example, if your schema has a role named `CTO` and the Pentaho Server also has a role named `CTO`, this mapper works well. ```xml ``` **Mondrian-SampleLookupMap-UserRoleMapper** `Mondrian-SampleLookupMap-UserRoleMapper` uses a translation table to map Pentaho Server roles to OLAP schema roles. Lookups are key/value pairs: * Key: Pentaho Server role name * Value: OLAP schema role name In this example, the `ceo` role in Pentaho Server maps to the `California manager` role in the schema. ```xml ``` **Mondrian-SampleUserSession-UserRoleMapper** `Mondrian-SampleUserSession-UserRoleMapper` retrieves OLAP schema roles from a named HTTP session variable. In this example, the session variable name is `MondrianUserRoles`. ```xml ``` ### OLAP log output The Pentaho Server and the standalone Mondrian engine use `log4j2` to log generated OLAP SQL queries. This output is useful for troubleshooting configuration and performance issues. #### Analysis SQL output logging You can log SQL generation, MDX generation, and debug MDX execution information in the Pentaho Server, Mondrian engine, or Schema Workbench. The default `log4j2.xml` configuration in the Pentaho Server includes the following rolling file appenders: * **SQLLOG**\ Logs SQL generated by Mondrian, including execution times and row counts. Re-running the same MDX query often produces no new SQL logs. Mondrian caches results from the first run. * **MDXLOG**\ Logs MDX executed by Mondrian, including execution times. * **MONDRIAN**\ Logs detailed MDX execution events. This output is verbose. Use it only for targeted troubleshooting. To enable SQL logging: 1. Stop the Pentaho Server, standalone Mondrian engine, or Schema Workbench. 2. Open the `log4j2.xml` file in a text editor: * **Pentaho Server:** `server/pentaho-server/tomcat/webapps/pentaho/WEB-INF/classes/log4j2.xml` * **Standalone Mondrian engine:** location depends on your runtime. `log4j2.xml` can be loaded from the classpath or passed as a JVM parameter. * **Schema Workbench:** check `workbench.bat` or `workbench.sh` to find the `log4j2.xml` location. 3. Add the appender and its logger: * In the Pentaho Server, each appender has a corresponding logger. * For a standalone Mondrian engine or Schema Workbench, copy the relevant appenders and loggers from a Pentaho Server `log4j2.xml`. 4. Save the file. 5. Start the Pentaho Server, Mondrian engine, or Schema Workbench. You can now review Analysis query logs in your application server logs. For segment-cache-specific logging, also enable the segment cache categories described in [Enabling segment cache logging](#enabling-segment-cache-logging). #### Enabling segment cache logging The Pentaho Server lets you view engine segment cache logs by opening the log window in Analyzer. If you use Analyzer with a standalone Mondrian engine, cache log information is available through `log4j2`. Update your `log4j2.xml` as described in [Analysis SQL output logging](#analysis-sql-output-logging), and set `com.pentaho.analysis.segmentcache` to `DEBUG`. The following `log4j2` categories can help diagnose configuration and performance issues: | Class Name | Cache System | | --------------------------------------------------- | --------------------------------------------------------------------------------------- | | `com.pentaho.analysis.segmentcache.impl.infinispan` | Outputs information related to the Infinispan segment cache implementation. | | `com.pentaho.analysis.segmentcache.impl.memcached` | Outputs information related to the Memcached segment cache implementation. | | `com.pentaho.analysis.segmentcache.impl.pentaho` | Outputs information related to the Pentaho BI Platform Delegating Cache implementation. | #### View log output in Analyzer To view log output in Analyzer: 1. Create a new Pentaho Analyzer report. 2. Drag a measure or dimension into the report. 3. When the report displays results, select **More actions and options** > **Administration** > **Log**. ![How to view the log output in Analyzer](https://2345962715-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqfaQ2p0JAZrP8b3cpM9a%2Fuploads%2Fgit-blob-a7428783130fd1cc64ba87b7415983ea8dfe6bee%2FPentaho%20Analyzer%20report%20Administration_Log.png?alt=media) {% hint style="info" %} If you do not see **Administration**, you are not signed in as an administrator. Sign out of the Pentaho User Console, then sign back in with an administrator account. {% endhint %} If your plugin is configured correctly, you will see segment cache entries. If configuration errors exist, Analyzer shows the related exception messages. ### Multidimensional Expression Language Multidimensional Expression Language (MDX) is an OLAP query and calculation language. It is similar to SQL. Use MDX to retrieve datasets from an OLAP database. You can use Pentaho tools without knowing MDX. Learn the basics to test Mondrian schemas. #### MDX syntax If you know SQL, much of MDX syntax looks familiar. You can edit MDX in Analyzer or a text editor. MDX has six data types: * Dimension or hierarchy * Level * Member * Tuple * Scalar * Set Example MDX query: ``` SELECT { [Measures].[Salesfact] } ON COLUMNS, { [Date].[2004], [Date].[2005] } ON ROWS FROM Sales ``` Microsoft originally developed MDX for SQL Server analysis products. It later became an independent standard. See these references: * [Microsoft documentation](https://learn.microsoft.com/en-us/analysis-services/multidimensional-models/mdx/multidimensional-expressions-mdx-reference) * [Multidimensional Expressions (Wikipedia)](https://en.wikipedia.org/wiki/Multidimensional_Expressions) MDX implementations vary by product. Pentaho Analysis does not support every MDX function or extension. #### Mondrian schema element reference Mondrian schema elements are listed below in the hierarchy in which they are used. Element details appear later in this section. | Element | Definition | | ------------------------------------------------------------------------------------------------------------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------- | | [\](https://docs.pentaho.com/install/legacy-redirects/mondrian-schema-element-reference-legacy-pages/schema) | A complete Mondrian schema; a collection of cubes, virtual cubes, shared dimensions, and roles. | | [\](https://docs.pentaho.com/install/legacy-redirects/mondrian-schema-element-reference-legacy-pages/cube) | A collection of dimensions and measures, all centered on a fact table. | | [\](https://docs.pentaho.com/install/legacy-redirects/mondrian-schema-element-reference-legacy-pages/virtualcube) | A cube defined by combining the dimensions and measures of one or more cubes. A measure originating from another cube can be a **\**. | | [\](https://docs.pentaho.com/install/legacy-redirects/mondrian-schema-element-reference-legacy-pages/cubeusages) | Base cubes that are imported into a virtual cube. | | [\](https://docs.pentaho.com/install/legacy-redirects/mondrian-schema-element-reference-legacy-pages/cubeusage) | Usage of a base cube by a virtual cube. | | [\](https://docs.pentaho.com/install/legacy-redirects/mondrian-schema-element-reference-legacy-pages/virtualcubedimension) | Usage of a dimension by a virtual cube. | | [\](https://docs.pentaho.com/install/legacy-redirects/mondrian-schema-element-reference-legacy-pages/virtualcubemeasure) | Usage of a measure by a virtual cube. | | [\](https://docs.pentaho.com/install/legacy-redirects/mondrian-schema-element-reference-legacy-pages/dimension) | Defines a dimension: a collection of hierarchies. | | [\](https://docs.pentaho.com/install/legacy-redirects/mondrian-schema-element-reference-legacy-pages/dimensiongrant) | Grants or denies this role access to a dimension. | | [\](https://docs.pentaho.com/install/legacy-redirects/mondrian-schema-element-reference-legacy-pages/dimensionusage) | Usage of a shared dimension by a cube. | | [\](https://docs.pentaho.com/install/legacy-redirects/mondrian-schema-element-reference-legacy-pages/hierarchy) | Specifies a predefined drill-down. | | [\](https://docs.pentaho.com/install/legacy-redirects/mondrian-schema-element-reference-legacy-pages/level) | A level of a hierarchy. | | [\](https://docs.pentaho.com/install/legacy-redirects/mondrian-schema-element-reference-legacy-pages/keyexpression) | SQL expression used as key of the level, in lieu of a column. | | [\](https://docs.pentaho.com/install/legacy-redirects/mondrian-schema-element-reference-legacy-pages/nameexpression) | SQL expression used to compute the name of a member, in lieu of Level.nameColumn. | | [\](https://docs.pentaho.com/install/legacy-redirects/mondrian-schema-element-reference-legacy-pages/captionexpression) | SQL expression used to compute the caption of a member, in lieu of **Level.captionColumn**. | | [\](https://docs.pentaho.com/install/legacy-redirects/mondrian-schema-element-reference-legacy-pages/ordinalexpression) | SQL expression used to sort members of a level, in lieu of **Level.ordinalColumn**. | | [\](https://docs.pentaho.com/install/legacy-redirects/mondrian-schema-element-reference-legacy-pages/parentexpression) | SQL expression used to compute a measure, in lieu of **Level.parentColumn**. | | [\](https://docs.pentaho.com/install/legacy-redirects/mondrian-schema-element-reference-legacy-pages/property) | A member property. The definition is contained in a hierarchy or level, but the property will be available to all members. | | [\](https://docs.pentaho.com/install/legacy-redirects/mondrian-schema-element-reference-legacy-pages/propertyexpression) | SQL expression used to compute the value of a property, in lieu of **Property.column**. | | [\](https://docs.pentaho.com/install/legacy-redirects/mondrian-schema-element-reference-legacy-pages/measure) | Specifies an aggregated numeric value. | | [\](#calculatedmember) | A member whose value is derived using a formula, defined as part of a cube. | | [\](#namedset) | A set whose value is derived using a formula, defined as part of a cube. | | [\](#table) | A fact or dimension table. | | [\](#view) | Defines a table by using an SQL query, which can have different variants for different underlying databases. | | [\](#join) | Defines a table by joining a set of queries. | | [\](#inlinetable) | Defines a table using an inline dataset. | | [\](#closure) | Maps a parent-child hierarchy onto a closure table. | | [\](#aggexclude) | Exclude a candidate aggregate table by name or pattern matching. | | [\](#aggname) | Declares an aggregate table to be matched by name. | | [\](#aggpattern) | Declares a set of aggregate tables by regular expression pattern. | | [\](#aggfactcount) | Specifies name of the column in the candidate aggregate table which contains the number of fact table rows. | | [\](#aggignorecolumn) | Tells Mondrian to ignore a column in an aggregate table. | | [\](#aggforeignkey) | Maps a foreign key in the fact table to a foreign key column in the candidate aggregate table. | | [\](#aggmeasure) | Maps a measure to a column in the candidate aggregate table. | | [\](#agglevel) | Maps a level to a column in the candidate aggregate table. | | [\](#aggtable) | A definition of an aggregate table for a base fact table. | | [\](#role) | An access-control profile. | | [\](#schemagrant) | A set of rights to a schema. | | [\](#cubegrant) | A set of rights to a cube. | | [\](#hierarchygrant) | A set of rights to both a hierarchy and levels within that hierarchy. | | [\](#membergrant) | A set of rights to a member and its children. | | [\](#union) | Definition of a set of rights as the union of a set of roles. | | [\](#roleusage) | A reference to a role. | | [\](#userdefinedfunction) | Imports a user-defined function. | | [\](#parameter) | Part of the definition of a hierarchy; passed to a **MemberReader**, if present. | | [\](#calculatedmemberproperty) | Property of a calculated member. | | [\](#formula) | Holds the formula text within a **\** or **\**. | | [\](#columndefs) | Holder for **\** elements. | | [\](#columndef) | Definition of a column in an **\** dataset. | | [\](#rows) | Holder for **\** elements. | | [\](#row) | Row in an **\** dataset. | | [\](#value) | Value of a column in an **\** dataset. | | [\](#measureexpression) | SQL expression used to compute a measure, in lieu of **Measure.column**. | | [\](#sql) | The SQL expression for a particular database dialect. | #### Element details **CalculatedMember** A member whose value is derived using a formula, defined as part of a cube. **Attributes** | Attribute | Data Type | Definition | | ------------ | --------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | name | String | Name of this calculated member | | formatString | String | Format string with which to format cells of this member. See the **Format** class under **mondrian.util** in for more details. | | caption | String | A string being displayed instead of the name. Can be localized from Properties file using **#{propertyname}**. | | description | String | Description of this calculated member. Can be localized from Properties file using **#{propertyname}**. | | formula | String | MDX expression which gives the value of this member. Equivalent to the Formula sub-element. | | dimension | String | Name of the dimension which this member belongs to | | hierarchy | String | Name of the hierarchy that this member belongs to | | parent | String | Fully-qualified name of the parent member. If not specified, the member will be at the lowest level (besides the 'all' level) in the hierarchy. | | visible | Boolean | Whether this member is visible in the user-interface. Default true. | **Constituent Elements** | Element | Definition | | ----------------------------------------------------- | ---------------------------------------------------- | | [Formula](#formula) | MDX expression which gives the value of this member. | | [CalculatedMemberProperty](#calculatedmemberproperty) | Property of a calculated member. | **NamedSet** A set whose value is derived using a formula, defined as part of a cube. **Attributes** | Attribute | Data Type | Definition | | ----------- | --------- | ----------------------------------------------------------------------------------------------- | | name | String | Name of this named set | | caption | String | Caption of this named set. Can be localized from Properties file using **#{propertyname}**. | | description | String | Description of this named set. Can be localized from Properties file using **#{propertyname}**. | | formula | String | MDX expression which gives the value of this set. Equivalent to the Formula sub-element. | **Constituent Elements** | Element | Definition | | ------------------- | ------------------------------------------------ | | [Formula](#formula) | MDX expression which gives the value of this set | **Table** A fact or dimension table. **Attributes** | Attribute | Data Type | Definition | | --------- | --------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | name | String | The name of the table | | schema | String | Optional qualifier for table | | alias | String | Alias to be used with this table when it is used to form queries. If not specified, defaults to the table name, but in any case, must be unique within the schema. You can use the same table in different hierarchies, but it must have different aliases. | **Constituent Elements** | Element | Definition | | ------------------------- | ---------------------------------------------------------------------- | | [SQL](#sql) | The SQL WHERE clause expression to be appended to any select statement | | [AggExclude](#aggexclude) | Exclude a candidate aggregate table by name or pattern matching | | [AggTable](#aggtable) | A definition of an aggregate table for a base fact table. | **View** Defines a table using a SQL query, which can have different variants for different underlying databases. **Attributes** | Attribute | Data Type | Definition | | --------- | --------- | --------------------------------------------------------------- | | alias | String | Alias to be used with this view when it is used to form queries | **Constituent Elements** | Element | Definition | | ----------- | ---------------------------------------------------- | | [SQL](#sql) | The SQL expression for a particular database dialect | **Join** Defines a table by joining a set of queries. **Attributes** | Attribute | Data Type | Definition | | ---------- | --------- | ------------------------------------------------------------------ | | leftAlias | String | Defaults to left's alias if left is a table, otherwise required. | | leftKey | String | | | rightAlias | String | Defaults to right's alias if right is a table, otherwise required. | | rightKey | String | | **Constituent Elements** A **\** must define exactly two relations: * Left relation: **\
**, **\**, **\**, or **\** * Right relation: **\
**, **\**, **\**, or **\** **InlineTable** Defines a table using an inline dataset. **Attributes** | Attribute | Data Type | Definition | | --------- | --------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | alias | String | Alias to be used with this table when it is used to form queries. If not specified, defaults to the table name, but in any case, must be unique within the schema. You can use the same table in different hierarchies, but it must have different aliases. | **Constituent Elements** | Element | Definition | | ------------------------- | ------------------------------------ | | [ColumnDefs](#columndefs) | Holder for **\** elements | | [Rows](#rows) | Holder for **\** elements | **Closure** Specifies the transitive closure of a parent-child hierarchy. This element is optional. It is recommended for better performance. **Attributes** | Attribute | Data Type | Definition | | ------------ | --------- | ----------------- | | parentColumn | String | The parent column | | childColumn | String | The child column | **Constituent Elements** | Element | Definition | | --------------- | ------------------------- | | [Table](#table) | A fact or dimension table | **AggExclude** This element excludes a candidate aggregate table via name or pattern matching. **Attributes** | Attribute | Data Type | Definition | | ---------- | --------- | -------------------------------------------- | | pattern | String | A Table pattern not to be matched. | | name | String | The Table name not to be matched. | | ignorecase | Boolean | Whether or not the match should ignore case. | **AggName** Declares an aggregate table to be matched by name. **Attributes** | Attribute | Data Type | Definition | | -------------- | --------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | name | String | The table name of a specific aggregate table | | approxRowCount | String | The estimated number of rows in this aggregation table. Setting this property improves the performance of the aggregation optimizer and prevents it from issuing a `select count(*)` query over the aggregation table. | **AggPattern** Declares a set of aggregate tables by regular expression pattern. **Attributes** | Attribute | Data Type | Definition | | --------- | --------- | --------------------------------------------------------- | | pattern | String | A Table pattern used to define a set of aggregate tables. | **Constituent Elements** | Element | Definition | | ------------------------- | ---------------------------------------------------------------- | | [AggExclude](#aggexclude) | Exclude a candidate aggregate table by name or pattern matching. | **AggFactCount** Specifies name of the column in the candidate aggregate table which contains the number of fact table rows. **Attributes** (None) **AggIgnoreColumn** Tells Mondrian to ignore a column in an aggregate table. **Attributes** (None) **AggForeignKey** Maps foreign key in the fact table to a foreign key column in the candidate aggregate table. **Attributes** | Attribute | Data Type | Definition | | ---------- | --------- | -------------------------------------------- | | factColumn | String | The name of the base fact table foreign key. | | aggColumn | String | The name of the aggregate table foreign key. | **AggMeasure** Maps a measure to a column in the candidate aggregate table. **Attributes** | Attribute | Data Type | Definition | | --------- | --------- | --------------------------------------------------- | | column | String | The name of the column mapping to the measure name. | | name | String | The name of the Cube measure. | **AggLevel** Maps a level to a column in the candidate aggregate table. **Attributes** | Attribute | Data Type | Definition | | --------- | --------- | ------------------------------------------------------------------------------------------------------- | | column | String | The name of the column mapping to the level name. | | name | String | The name of the Dimension Hierarchy level. | | collapse | Boolean | Whether this is a collapsed level. The parents of that level are also present in the aggregation table. | **AggTable** A definition of an aggregate table for a base fact table. This aggregate table must be in the same schema as the base fact table. **Attributes** | Attribute | Data Type | Definition | | ---------- | --------- | -------------------------------------------- | | ignorecase | Boolean | Whether or not the match should ignore case. | **Constituent Elements** | Element | Definition | | ----------------------------------- | ---------------------------------------------------------------------------------------------- | | [AggFactCount](#aggfactcount) | Describes what the fact\_count column looks like. | | [AggIgnoreColumn](#aggignorecolumn) | Tells Mondrian to ignore a column in an aggregate table. | | [AggForeignKey](#aggforeignkey) | Maps a foreign key in the fact table to a foreign key column in the candidate aggregate table. | | [AggMeasure](#aggmeasure) | Maps a measure to a column in the candidate aggregate table. | | [AggLevel](#agglevel) | Maps a level to a column in the candidate aggregate table. | **Role** A role defines an access-control profile. It has a series of grants (or denials) for schema elements. **Attributes** | Attribute | Data Type | Definition | | --------- | --------- | --------------------- | | name | String | The name of this role | **Constituent Elements** | Element | Definition | | --------------------------- | ------------------------------------------------------------ | | [SchemaGrant](#schemagrant) | A set of rights to a schema | | [Union](#union) | Definition of a set of rights as the union of a set of roles | **SchemaGrant** Grants (or denies) this role access to this schema. Access may be `all`, `all_dimensions`, or `none`. If access is `all_dimensions`, the role has access to all dimensions but still needs explicit access to cubes. **Attributes** (None) **Constituent Elements** | Element | Definition | | ----------------------- | ------------------------- | | [CubeGrant](#cubegrant) | A set of rights to a cube | **CubeGrant** Grants (or denies) this role access to a cube. access may be `all` or `none`. **Attributes** | Attribute | Data Type | Definition | | --------- | --------- | --------------------------- | | cube | String | The unique name of the cube | **Constituent Elements** | Element | Definition | | --------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------- | | [DimensionGrant](https://docs.pentaho.com/install/legacy-redirects/mondrian-schema-element-reference-legacy-pages/dimensiongrant) | Grants (or denies) this role access to a dimension | | [HierarchyGrant](#hierarchygrant) | A set of rights to both a hierarchy and levels within that hierarchy | **HierarchyGrant** Grants (or denies) this role access to a hierarchy. Access may be `all`, `custom`, or `none`. If access is `custom`, you may also specify `topLevel`, `bottomLevel`, and the member grants. **Attributes** | Attribute | Data Type | Definition | | ------------ | --------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | hierarchy | String | The unique name of the hierarchy | | topLevel | String | Unique name of the highest level of the hierarchy from which this role is allowed to see members. May only be specified if the **HierarchyGrant.access** is custom. If not specified, role can see members up to the top level. | | bottomLevel | String | Unique name of the lowest level of the hierarchy from which this role is allowed to see members. May only be specified if the **HierarchyGrant.access** is custom. If not specified, role can see members down to the leaf level. | | rollupPolicy | String | Policy which determines how cell values are calculated if not all of the children of the current cell are visible to the current role. Allowable values are full (the default), partial, and hidden. | **Constituent Elements** | Element | Definition | | --------------------------- | -------------------------------------------- | | [MemberGrant](#membergrant) | A set of rights to a member and its children | **MemberGrant** A set of rights to a member and its children. **Attributes** | Attribute | Data Type | Definition | | --------- | --------- | ----------------------------- | | member | String | The unique name of the member | | access | String | (None) | **Union** Body of a Role definition which defines a Role to be the union of several Roles. The **\** elements must refer to roles that were declared earlier in the schema file. **Attributes** (None) **Constituent Elements** | Element | Definition | | ----------------------- | --------------------- | | [RoleUsage](#roleusage) | A reference to a role | **RoleUsage** Usage of a role in a union role. **Attributes** | Attribute | Data type | Definition | | --------- | --------- | -------------------- | | roleName | String | The name of the role | **Constituent Elements** (Empty) **UserDefinedFunction** A `UserDefinedFunction` extends the MDX language. It must be implemented by a Java class that implements `mondrian.spi.UserDefinedFunction`. **Attributes** | Attribute | Data Type | Definition | | --------- | --------- | ------------------------------------------------------------------------------------------------------------------------------- | | name | String | Name with which the user-defined function will be referenced in MDX expressions. | | className | String | Name of the class which implements this user-defined function. Must implement the `mondrian.spi.UserDefinedFunction` interface. | **Parameter** Part of the definition of a hierarchy. It is passed to a **MemberReader**, if present. **Attributes** | Attribute | Data Type | Definition | | ------------ | --------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | name | String | Name of this parameter | | description | String | Description of this parameter | | type | String | Indicates the type of this parameter: String, Numeric, Integer, Boolean, Date, Time, Timestamp, or Member | | modifiable | Boolean | If set to false, statement cannot change the value of this parameter; the parameter becomes effectively constant (provided that its default value expression always returns the same value). Default is true. | | defaultValue | String | Expression for the default value of this parameter | **CalculatedMemberProperty** Property of a calculated member defined against a cube. It must have either an expression or a value. **Attributes** | Attribute | Data Type | Definition | | ----------- | --------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | name | String | Name of this member property | | caption | String | A string being displayed instead of the Properties's name. Can be localized from Properties file using **#{propertyname}**. | | description | String | Description of this calculated member property. Can be localized from Properties file using **#{propertyname}**. | | expression | String | MDX expression which defines the value of this property. If the expression is a constant string, you could enclose it in quotes, or just specify the **value** attribute instead. | | value | String | Value of this property. If the value is not constant, specify the **expression** attribute instead. | **Formula** Holds the formula text within a **\** or **\**. **Attributes** (None) **ColumnDefs** Holder for an array of **\** elements. **Attributes** (None) **Constituent Elements** | Element | Definition | | ----------------------- | ----------------------------------- | | [ColumnDef](#columndef) | The columns to include in the array | **ColumnDef** Column definition for an inline table. **Attributes** | Attribute | Data Type | Definition | | --------- | --------- | ------------------------------------------------------------------------------- | | name | String | Name of the column. | | type | String | Type of the column: String, Numeric, Integer, Boolean, Date, Time or Timestamp. | **Rows** Holder for an array of **\** elements. **Attributes** (None) **Constituent Elements** | Element | Definition | | ----------- | ------------------------------------- | | [Row](#row) | Row in an **\** dataset. | **Row** Row definition for an inline table. Must have one column value for each **\** in the **\**. **Attributes** (None) **Constituent Elements** | Element | Definition | | --------------- | ---------- | | [Value](#value) | | **Value** Column value for an inline table. The element body holds the value. **Attributes** | Attribute | Data Type | Definition | | --------- | --------- | ------------------ | | column | String | Name of the column | **MeasureExpression** SQL expression used to compute a measure, in lieu of **Measure.column**. **Attributes** (None) **SQL** The SQL expression for a particular database dialect. The element body holds the SQL. **Attributes** | Attribute | Data Type | Definition | | --------- | --------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | dialect | String | Dialect of SQL the view is intended for. Example values include `generic`, `access`, `db2`, `firebird`, `hsqldb`, `mssql`, `mysql`, `oracle`, `postgres`, `sybase`, `teradata`, `ingres`, and `infobright`. |