Schemas
Schema Versions
A schema defines a multi-dimensional Star / Snowflake database. It contains a logical model, consisting of cubes, hierarchies, and members, and a mapping of this model onto a physical model.
The logical model consists of the constructs used to write queries in MDX language: cubes, dimensions, hierarchies, levels, and members.
The physical model is the source of the data which is presented through the logical model. It is typically a star schema, which is a set of tables in a relational database.
Select Model canvas > Model Structure.
Explore the Schema constructs:
Schema Element
The Schema element is the root container of a Mondrian schema file. Think of it as the outer wrapper that holds everything else. Every Mondrian schema XML file has exactly one Schema element.
Every schema needs a name attribute, though Mondrian doesn't use it for critical operations. It's recommended that you also add a description attribute to explain what the schema does in human-readable terms.
You should also specify metamodelVersion="3.14" as this helps track which version of the schema format you're using.
The Schema element contains all the building blocks of your data model. It always requires a PhysicalSchema element, which defines the actual database tables and columns.
Typically, a schema includes one or more Cube elements that define the multidimensional data structures. It may also contain Dimension elements for shared dimensions that multiple cubes can use, and Role elements for access control and security settings.
An analyst typically creates the Schema first as the outer container, then fills it with the components needed to answer business questions, such as dimensions and cubes.
Think of it like a filing cabinet: the Schema is the cabinet itself, and you fill the drawers with organized folders (cubes, dimensions) that help answer specific business questions.
Does this help clarify the concept?
Select </> Advanced tab to view the XML
In Mondrian v3 the schema parser was extremely sensitive about the order of child elements. If you get child elements in the wrong order (for example, a cube after a role..), Mondrian will ignore the cube.
Cube Element
A cube, defined by a Cube XML element, is the context for a report or interactive analysis session. It represents a collection of events, describing the occurrences of a business process over the lifetime of the data mart.
Cubes tend to be a complete set of Dimensions, Hierarchies, Levels and Measures for doing an analysis on the set of events. For instance, if you’re interested in sales by customer, you might want to look at sales amounts (Measures) by customer (Dimension) geography (Level).
A cube collects these things into one place, ready for analysis and querying.
Click on the 3 dots > Edit option for the Sales_2003_2005 cube.
The Cube Editor enables you to edit and apply any changes.
Recall that a cube is a collection of Dimensions, Hierarchies, Levels (attributes Mondrian v4) and Measures. The Measures quantitatively describe events or collections of events, and the Levels represent the context in which the events occurred.
Each combination of Levels and Measures is effectively a new report that can be created in seconds.
Mandatory
Cube name
Cube name !
Optional Information
Default Measure
Cache
Should the Fact table data for this Cube be cached by Mondrian or not. The default action is to cache the data.
Visible
Whether this cube is visible in the user-interface.
Enabled
Whether element is enabled - if true, then the Cube is realized otherwise it is ignored.
Describe Cube
Caption
A string being displayed instead of the cube's name. Can be localized from Properties file using #{propertyname}.
Description
Description of this cube. Can be localized from Properties file using #{propertyname}.
x
Dimension
A Dimension is a structural attribute of a Cube that is a list of related names – known as Members – all of which belong to a similar category in the user’s perception of a data. For example, months and quarters may make up a Months dimension; likewise, all cities, regions and countries may make up a Region dimension.
A Dimension acts an index for identifying values within a multidimensional array and offers a very concise, intuitive way of organizing and selecting data for retrieval, exploration and analysis. Dimensions are the business parameters normally seen in the rows and columns of a report. A Dimension is a collection of logically related attributes, equivalent to a table.
Click on the 3 dots > Edit option for MARKETS dimension.
The Dimension Editor enables you to edit and apply any changes.
Mandatory
Dimension Name
Dimension name
Connect to the Fact Table
Fact Table Column
The name of the column in the fact table.
Dimension Type
Type
The dimension's type may be one of "Standard" or "Time". A time dimension will allow the use of the MDX time functions (WTD, YTD, QTD, etc.). Use a standard dimension if the dimension is not a timerelated dimension. The default value is "Standard".
Optional Information
Visible
Whether this dimension is visible in the user-interface. Default true.
Usage prefix
Adds a text prefix to a dimension's column names in aggregate tables to prevent naming conflicts when multiple dimensions use similar column names.. This should only be set for private dimensions.
Describe Dimension
Caption
A string being displayed instead of the dimensions’ name. Can be localized from Properties file using #{propertyname}.
Description
Description of this dimension. Can be localized from Properties fileusing #{propertyname}.
Hierarchy
Hierarchies allow for a better experience in the user interface. A hierarchy is a way to organize data at different levels of aggregation. In viewing data, analysts use dimension hierarchies to recognize trends at one level, drill down to lower levels to identify reasons for these trends and roll up to higher levels to see what affect these trends have on a larger sector of the business.
Click on the Markets Hierarchy.
The Dimension Editor enables you to edit and apply any changes.
The Level in each hierarchy must have a strict one-to-many relationship with the next level. In the [Markets] hierarchy, each Country belongs to only one Territory, and each City belongs to only one State Province. The net effect is that each level down has more members than the last.
Hierarchy Name
Name
Name of the hierarchy. If this is not specified, the hierarchy has the same name as its dimension.
Mandatory Data
Source Table
The name of the table which contains primaryKey. If the hierarchy has only one table, defaults to that; it is required.
Alias
Hierarchy Key
The name of the column which identifies members, and which is referenced by rows in the fact table. If not specified, the key of the lowest level is used. See also CubeDimension.foreignKey.
Has All
Whether this hierarchy has an 'all' member.
Optional Information
visible
Whether this hierarchy is visible in the user-interface. Default true.
All Member Name
Name of the 'all' member. If this attribute is not specified, the all member is named 'All hierarchyName', for example, 'All Store'.
All Member Caption
A string being displayed instead as the all member's name. Can be localized from Properties file using #{propertyname}.
All Level Name
Name of the 'all' level. If this attribute is not specified, the all member is named '(All)'. Can be localized from Properties file using #{propertyname}.
Default Member
Default member value
Member Reader Class
Name of the custom member reader class. Must implement the mondrian.rolap.MemberReader interface.
Origin
Display Folder
String value to specify the folder in which to list the hierarchy for users in Pentaho Analyzer.
Unique Key Level Name
Describe Hierarchy
Caption
A string to be displayed in the user interface. If not specified, the hierarchy's name is used. Can be localized from Properties file using #{propertyname}.
Description
Description of this hierarchy. Can be localized from Properties file using #{propertyname}.
By default, every hierarchy contains a top level called '(All)', which contains a single member called '(All {hierarchyName})'. This member is parent of all other members of the hierarchy, and thus represents a grand total.
The all member is also the default member of the hierarchy; that is, the member that is used for calculating cell values when the hierarchy is not included on an axis or in the slicer. Various attributes allow you to control the all level and member. The element's allMemberName and allLevelName attributes override the default names of the all level and all member.
If the element has hasAll="false", the 'all' level is suppressed. The default member of that dimension will now be the first member of the first level; for example, in a Time hierarchy, it will be the first year in the hierarchy. Changing the default member can be confusing, so you should generally use hasAll="true".
You can also set the default member of a hierarchy explicitly. The defaultMember attribute should be the fully-qualified name of the default member.
When specified in this way, the default member can even be a calculated member.
Level
The Level Element can be thought of as representing a column in a table. It’s a collection of Members that are the same distance from the root of the hierarchy.
Click on the Territory Level.
The Dimension Editor enables you to edit and apply any changes.
Level Name
Name
Name of the Level.
Level Source
Source Table
The name of the table that the column comes from. If this hierarchy is based upon just one table, defaults to the name of that table; otherwise, it is required. Can be localized from Properties file using #{propertyname}.
Column
The name of the column which holds the unique identifier of this level.
Relevant Information
Name Column
The name of the column which holds the user identifier of this level.
Ordinal Column
The name of the column which holds member ordinals. If this column is not specified, the key column is used for ordering.
Optional Information
Visible
Aapprox Row Count
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.
Null Parent Value
Value which identifies null parents in a parent-child hierarchy. Typical values are 'NULL' and '0'.
Key Column Type
Indicates the type of this level's key column: String, Numeric, Integer, Boolean, Date, Time or Timestamp. When generating SQL statements, Mondrian encloses values for String columns in quotation marks, but leaves values for Integer and Numeric columns un-quoted. Date, Time, and Timestamp values are quoted according to the SQL dialect. For a SQL-compliant dialect, the values appear prefixed by their typename, for example, "DATE '2006-06-01'".
Internal Type
Indicates the Java type that Mondrian uses to store this level's key column. It also determines the JDBC method that Mondrian will call to retrieve the column; for example, if the Java type is 'int', Mondrian will call 'ResultSet.getInt(int)'. Usually this attribute is not needed, because Mondrian can choose a sensible type based on the type of the database column. Allowable values are: 'int', 'long', 'Object', 'String'.
Unique Members
Whether members are unique across all parents. For example, zipcodes are unique across all states. The first level's members are always unique.
Level Type
Whether this is a regular or a time-related level. The value makes a difference to time-related functions such as YTD (year-to-date). The "TimeHalfYear" value is deprecated and will be removed in mondrian-4.0; use "TimeHalfYears" instead.
Hide Member Iif
Condition which determines whether a member of this level is hidden. If a hierarchy has one or more levels with hidden members, then it is possible that not all leaf members are the same distance from the root, and it is termed a ragged hierarchy. Allowable values are: Never (a member always appears; the default); IfBlankName (a member doesn't appear if its name is null, empty or all whitespace); and IfParentsName (a member appears unless its name matches the parent's.
Formatter Class
Name of a formatter class for the member labels being displayed. The class must implement the mondrian.spi.MemberFormatter interface. This attribute is deprecated.
Please use a nested MemberFormatter element.
Caption Column
The name of the column which holds the caption for members.
Parent Column
The name of the column which references the parent member in a parent-child hierarchy.
Describe Level
Caption
A string being displayed instead of the level's name. Can be localized from Properties file using #{propertyname}.
Description
Description of this Level. Can be localized from Properties file using #{propertyname}.
Properties
Many dimensions, such as Customer, are large, and it is useful to subset them for viewing purposes even before you create a query. You might want to subset information by customer first / last name, address, income level, educational background, or marital status before you begin to drill down into a data set, stratifying the population.
To add Properties & Annotations you will have to manually edit the XML.
Select the Advanced tab.
Scroll to the MARKETS Dimension to see how Annotations are applied & CUSTOMERS for Properties.
name
The name of the Property.
description
Description of this member property. Can be localized from Properties file using #{propertyname}.
column
The data column that will determine this subcategory's content
type
Data type of this property: String, Numeric, Integer, Boolean, Date, Time or Timestamp.
formatter
Name of a formatter class for the appropriate property value being displayed. The class must implement the mondrian.spi.PropertyFormatter interface. This attribute is deprecated. Please use a nested PropertyFormatter element.
caption
A string being displayed instead of the name. Can be localized from Properties file using #{propertyname}.
dependsOnLevelValue
Should be set too true if the value of the property is functionally dependent on the level value. This permits the associated property column to be omitted from the GROUP BY clause (if the database permits columns in the SELECT that are not in the GROUP BY). This can be a significant performance enhancement on some databases, such as MySQL.
Last updated
Was this helpful?