Schemas

Schema Versions

A schema defines a multi-dimensional 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.

Mondrian 3 Schema

Pentaho Mondrian Documentationmondrian.pentaho.com

---

Schema

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?

<Schema name="SteelWheels"
caption="Steel Wheels"
description="Optimizing the Sales process at Steel Wheels Inc"
metamodelVersion="3.14" measuresCaption="Metrics"
defaultRole="Associate" missingLink="warning">

Schema Element

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

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.

<Cube name="Sales 2003 to 2005">
<Table name=”ORDERFACT” />

<Dimension>
...
</Dimension>
<Measure>
...
</Measure> 
</Cube> 

Cube Element

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.

Attribute	Description
name	Cube name
description	Description of this cube. Can be localized from Properties file using #{propertyname}.
caption	A string being displayed instead of the cube's name. Can be localized from Properties file using #{propertyname}.
cache	Should the Fact table data for this Cube be cached by Mondrian or not. The default action is to cache the data.
enabled	Whether element is enabled - if true, then the Cube is realized otherwise it is ignored.
visible	Whether this cube is visible in the user-interface.

Dimension

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.

Dimension Element

Attribute	Description
name	Dimension name
description	Description of this dimension. Can be localized from Properties fileusing #{propertyname}.
foreign key	The name of the column in the fact table.
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".
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.
caption	A string being displayed instead of the dimensions’ name. Can be localized from Properties file using #{propertyname}.
visible	Whether this dimension is visible in the user-interface. Default true.

Hierarchy

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.

Hierarchy Element

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.

Attribute	Decription
name	Name of the hierarchy. If this is not specified, thehierarchy has the same name as its dimension.
description	Description of this hierarchy. Can be localized from Properties file using #{propertyname}.
hasAll	Whether this hierarchy has an 'all' member.
allMemberName	Name of the 'all' member. If this attribute is not specified, the all member is named 'All hierarchyName', for example, 'All Store'.
allMemberCaption	A string being displayed instead as the all member's name. Can be localized from Properties file using #{propertyname}.
allLevelName	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}.
defaultMember	Default member value
memberReaderClass	Name of the custom member reader class. Must implement the mondrian.rolap.MemberReader interface.
primaryKeyTable	The name of the table which contains primaryKey. If the hierarchy has only one table, defaults to that; it is required.
primaryKey	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.
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}.
visible	Whether this hierarchy is visible in the user-interface. Default true.

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.

<Hierarchy name="Yearly" hasAll="false"  defaultMember="[Time].[1997].[Q1].[1]">
    ...
    </Hierarchy> 

When specified in this way, the default member can even be a calculated member.

Level

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.

Level Element

Attribute	Description
name
dsecription
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.
nameColumn	The name of the column which holds the user identifier of this level.
parentColumn	The name of the column which references the parent member in a parent-child hierarchy.
nullParentValue	Value which identifies null parents in a parent-child hierarchy. Typical values are 'NULL' and '0'.
ordinalColumn	The name of the column which holds member ordinals. If this column is not specified, the key column is used for ordering.
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'".
internalType	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'.
uniqueMembers	Whether members are unique across all parents. For example, zipcodes are unique across all states. The first level's members are always unique.
levelType	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.
hideMemberif	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.
approxRowCount
caption	A string being displayed instead of the level's name. Can be localized from Properties file using #{propertyname}.
captionColumn	The name of the column which holds the caption for members.
formatter	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.

Properties

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.

Properties

name
caption	A string being displayed instead of the name. Can be localized from Properties file using #{propertyname}.
description	Description of this member property. Can be localized from Properties file using #{propertyname}.
column
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.
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.

Measures

Measures

The Measure XML element defines a measure. A measure is a value, almost always numeric, that appears in a cell. Strictly speaking, the XML Measure element defines a stored measure. Mondrian also supports calculated measures, which are calculated from other measures using an MDX formula. Though stored and calculated measures are defined and evaluated differently, they appear the same to a business user running a report.

<Measure name='Unit Sales' aggregator='sum' column='unit_sales' />
<Measure name='Store Sales' aggregator='sum' column='store_sales' />
<Measure name='Sales Count' aggregator='count' />

Measures

Aggregator	Description
sum	Sums numeric values. The most common aggregator.
count	Counts the number of rows for which a column is not null; if column isn’t specified, counts the number of rows.
distinct-count	Computes the number of distinct values of the column. Nulls are not counted.
max	Finds the maximum value of a column.
min	Finds the minimum value of a column.
avg	Computes the average value of a numeric column.