Schema cheatsheet
Introduction
The below paragraphs contains sample snippets for your OLAP schema.
Precise documentation on how to define a schema, can be found at: http://mondrian.pentaho.com/documentation/schema.php
Schema structure overview
- Table (one):
- Structure: The centre table containing the measures (FACT table)
- OLAP cube: Not displayed
- Dimensions (many)
- Structure: Related tables or groupable values (FACT table relations)
- OLAP cube: Columns/row "headers" in the cube
- Measures (many)
- Structure: Values found in the centre table (FACT table values)
- OLAP cube: Numbers to be displayed in the table cells
Defining dimensions for related values
Standard lookup value
The cube schema below only needs adjustment for the system name of the lookup field.
Structure
| Solution as displayed | "Sample solution" : "Category" = xxx |
| Solution system names | sample : CATEGORY = xxx |
| Database table names | data_sample : CATEGORY = xxx |
Data model
- data_sample
- CATEGORY: Field containing the lookup value
Cube schema
...
<Dimension name="Example" foreignKey="CATEGORY">
<Hierarchy hasAll="true" primaryKey="LookupID">
<Level name="Category" column="Value" uniqueMembers="true" />
</Hierarchy>
</Dimension>
...Simple choice value
The cube schema below only needs adjustment for the system name of the lookup field.
Structure
| Solution as displayed | "Sample solution" : "My choice" = xxx |
| Solution system names | sample : CHOICE = xxx |
| Database table names | data_sample : CHOICE = xxx |
Data model
- data_sample
- CHOICE: Field containing the lookup value
Cube schema
...
<Dimension name="Example" foreignKey="CHOICE">
<Hierarchy hasAll="true" primaryKey="ChoiceID">
<Level name="Answer" column="Value" uniqueMembers="true" />
</Hierarchy>
</Dimension>
...Standard record Status
The cube schema below can be copied directly without modification: The status properties and tablenames are allways the same.
Structure
| Solution as displayed | "Sample solution" : "Status" = xxx |
| Solution system names | sample : "StatusID" = xxx |
| Database table names | data_sample "StatusID" = xxx |
Data model
- data_sample
- StatusID: Field containing status reference (allways the same)
Cube schema
...
<Dimension name="Example" foreignKey="StatusID">
<Hierarchy hasAll="true" primaryKey="StatusID">
<Level name="Status" column="Status" uniqueMembers="true" />
</Hierarchy>
</Dimension>
...Defining dimensions for related records
Structure
| Solutions as displayed | "Some child" : "Parent" -> "Father or mother" |
| Solution system names | child : PARENT -> parent |
| Database table names | data_child : PARENT -> data_parent |
Data model
- data_child
- PARENT: Key to the "parent" solution
- data_parent
- GRANDPARENT: Key to the "grandparent" solution
- PARENTNAME: Descriptive field
Cube schema
...
<Dimension name="Example" foreignKey="PARENT">
<Hierarchy hasAll="true" primaryKey="DataID" primaryKeyTable="data_parent">
<Level name="Parent" column="PARENTNAME" uniqueMembers="true" />
</Hierarchy>
</Dimension>
...Note that the schemas for multi join tables are written from "inside out", that might seem counterintuitive i relation to what you want to display in the cube later.
Structure
| Solutions as displayed | "Some child" : "Parent" -> "Father or mother" : "Grand parent" -> "Grandma and Grandpa's" |
| Solution system names | child : PARENT -> parent : GRANDPARENT -> grandparent |
| Database table names | data_child : PARENT -> data_parent : GRANDPARENT -> data_grandparent |
Data model
- data_child
- PARENT: Key field pointing to the "parent" solution
- data_parent
- GRANDPARENT: Key field pointing to the "grandparent" solution
- PARENTNAME: Descriptive field
- data_grandparent
- GRANDPARENTNAME: Descriptive field
Cube schema
...
<Dimension name="Example" foreignKey="PARENT">
<Hierarchy hasAll="true" primaryKey="DataID" primaryKeyTable="data_parent">
<Join leftKey="GRANDPARENT" rightKey="DataID" />
<Level name="Grandparent" table="data_grandparent" column="GRANDPARENTNAME" uniqueMembers="true" />
<Level name="Parent" table="data_parent" column="PARENTNAME" uniqueMembers="true" />
</Hierarchy>
</Dimension>
...Defining dimensions for inline values
Text values
Cube schema
...
<Dimension name="Category">
<Hierarchy hasAll="true">
<Level name="Name" column="NAME" uniqueMembers="true"/>
</Hierarchy>
</Dimension>
...Year/integer values
Cube schema
...
<Dimension name="Period" type="TimeDimension">
<Hierarchy hasAll="true">
<Level name="Aar" column="YEAR" type="Numeric" uniqueMembers="false" levelType="TimeYears"/>
</Hierarchy>
</Dimension>
...Date / datetime values
Cube schema
...
<Dimension name="Period" type="TimeDimension">
<Hierarchy name="Periode" hasAll="true" allMemberName="All period">
<Level name="Aar" levelType="TimeYears" uniqueMembers="true">
<KeyExpression>
<SQL dialect="mysql">Year(CreatedAt)</SQL>
<SQL dialect="generic">YEAR</SQL>
</KeyExpression>
</Level>
<Level name="Maaned" uniqueMembers="false" levelType="TimeMonths">
<KeyExpression>
<SQL dialect="mysql">Month(CreatedAt)</SQL>
<SQL dialect="generic">Month</SQL>
</KeyExpression>
</Level>
</Hierarchy>
</Dimension>
...Enumeration values
Cube schema
...
<Dimension name="Severity">
<Hierarchy hasAll="true" primaryKey="SEVERITY">
<InlineTable alias="enumeration">
<ColumnDefs>
<ColumnDef name="id" type="Numeric"/>
<ColumnDef name="desc" type="String"/>
</ColumnDefs>
<Rows>
<Row>
<Value column="id">1</Value>
<Value column="desc">High</Value>
</Row>
<Row>
<Value column="id">2</Value>
<Value column="desc">Medium</Value>
</Row>
... more values ...
</Rows>
</InlineTable>
<Level name="Severity" column="id" nameColumn="desc" uniqueMembers="true"/>
</Hierarchy>
</Dimension>
...Defining measures
Normal values
Measures are allways numeric values, that can be agggated to higher levels (the levels in the dimensions)
| Type | Aggregator | Examples |
|---|---|---|
| Sums | SUM | Time spent, costs |
| Average | AVG | Process time |
Cube schema