# 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](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 namessample : CATEGORY = xxx
Database table namesdata\_sample : CATEGORY = xxx
**Data model** - data\_sample - CATEGORY: Field containing the lookup value **Cube schema** ```xml ... ... ``` ### 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 namessample : CHOICE = xxx
Database table namesdata\_sample : CHOICE = xxx
**Data model** - data\_sample - CHOICE: Field containing the lookup value **Cube schema** ```xml ... ... ``` ### 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 namessample : "StatusID" = xxx
Database table namesdata\_sample "StatusID" = xxx
**Data model** - data\_sample - StatusID: Field containing status reference (allways the same) **Cube schema** ```xml ... ... ``` ## Defining dimensions for related records ### Related solution ONE step away **Structure**
Solutions as displayed"Some child" : "Parent" -> "Father or mother"
Solution system nameschild : PARENT -> parent
Database table namesdata\_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** ```xml ... ... ``` ### Related solution TWO steps away 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 nameschild : PARENT -> parent : GRANDPARENT -> grandparent
Database table namesdata\_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** ```xml ... ... ``` ## Defining dimensions for inline values ### Text values **Cube schema** ```xml ... ... ``` ### Year/integer values **Cube schema** ```xml ... ... ``` ### Date / datetime values **Cube schema** ```xml ... Year(CreatedAt) YEAR Month(CreatedAt) Month ... ``` ### Enumeration values **Cube schema** ```xml ... 1 High 2 Medium ... more values ... ... ``` ## Defining measures ### Normal values Measures are allways numeric values, that can be agggated to higher levels (the levels in the dimensions)
TypeAggregatorExamples
SumsSUMTime spent, costs
AverageAVGProcess time
**Cube schema** ### Calculated values