Schemas
Mapping Start / Snowflake databases ..
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.
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?
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.
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
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.
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
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.
name
Name of the hierarchy. If this is not specified, the hierarchy 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.
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.
name
Name of the Level.
dsecription
Description of this Level. Can be localized from Properties file using #{propertyname}.
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
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.
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
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.
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.
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.
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.
Annotations
The major element types (schema, cube, virtual cube, shared dimension, dimension, hierarchy, level, measure, calculated member) support annotations. An annotation is a way of associating a user-definedproperty with a metadata element, and, allows tools to add metadata without extending the official Mondrian schema.
AnalyzerBusinessGroup
Level
Used to create folders in the UI
AnalyzerBusinessGroupDescription
Level
Description for the folders
AnalyzerDateFormat
Level
Used for relative date filters
AnalyzerHideInUI
Measure, CalculatedMember
Hides the field in the UI
AnalyzerDisableDrillLinks
Cube
Disables drillthrough links on the cube
Last updated
Was this helpful?