# Schemas {% hint style="info" %} #### 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. {% endhint %}

{% embed url="" %} *** 1. Select Model canvas > Model Structure.

Explore the Schema constructs: {% tabs %} {% tab title="Schema" %} {% hint style="info" %} #### 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? {% endhint %} ```xml ``` 1. Select \ Advanced tab to view the XML

{% hint style="info" %} 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. {% endhint %} {% endtab %} {% tab title="Cube" %} {% hint style="info" %} #### 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. {% endhint %} ```xml ... ... ``` 1. Click on the 3 dots > Edit option for the Sales\_2003\_2005 cube.

2. The Cube Editor enables you to edit and apply any changes.

{% hint style="info" %} 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. {% endhint %} | Attribute | Description | | ------------------------ | ----------------------------------------------------------------------------------------------------------------- | | **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 {% endtab %} {% tab title="Dimension" %} {% hint style="info" %} #### 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. {% endhint %} 1. Click on the 3 dots > Edit option for MARKETS dimension.

2. The Dimension Editor enables you to edit and apply any changes.

Attribute	Description
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}.

{% endtab %} {% tab title="Hierarchy" %} {% hint style="info" %} #### 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. {% endhint %} 1. Click on the Markets Hierarchy. 2. The Dimension Editor enables you to edit and apply any changes.

{% hint style="info" %} 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. {% endhint %}

Attribute	Decription
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}.

{% hint style="info" %} 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. {% endhint %} ```xml ... ``` {% hint style="info" %} When specified in this way, the default member can even be a calculated member. {% endhint %} {% endtab %} {% tab title="Level" %} {% hint style="info" %} #### 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. {% endhint %} 1. Click on the Territory Level. 2. The Dimension Editor enables you to edit and apply any changes.

Attribute	Description
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}.

{% endtab %} {% tab title="Properties" %} {% hint style="info" %} #### 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. {% endhint %} {% hint style="warning" %} To add Properties & Annotations you will have to manually edit the XML. {% endhint %} 1. Select the Advanced tab. 2. Scroll to the MARKETS Dimension to see how Annotations are applied & CUSTOMERS for Properties.

Attribute	Value
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.

{% endtab %} {% endtabs %}