Difference between revisions of "Tutorial/Schema cheatsheet"

From TempusServa wiki
Jump to navigation Jump to search
old>Admin
m (76 revisions imported)
 
(69 intermediate revisions by one other user not shown)
Line 1: Line 1:
== 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 ==
== Schema structure overview ==


* Table (one):  
* Table (one):  
** Structure: The centre table  
** Structure: The centre table containing the measures (FACT table)
** OLAP cube: ''Not displayed''


* Dimensions (many)
* Dimensions (many)
** Structure: Related tables or groupable values  
** Structure: Related tables or groupable values (FACT table relations)
** OLAP: Columns/row "headers" in the cube
** OLAP cube: Columns/row "headers" in the cube


* Measures (many)
* Measures (many)
** Structure: Values in the centre table
** Structure: Values found in the centre table (FACT table values)
** OLAP: Numbers to display in cell cubes
** 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'''
 
    ...
    <Table name="data_sample"/>
    ...
    <Dimension name="Example" foreignKey="CATEGORY">
        <Hierarchy hasAll="true" primaryKey="LookupID">
            <Table name="formfieldlookup"/>
            <Level name="Category" column="Value" uniqueMembers="true"/>
        </Hierarchy>
    </Dimension>
    ...
 


== Defining dimensions ==
=== Simple choice value ===
The cube schema below only needs adjustment for the system name of the lookup field.


=== Simple groupable values ===
'''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


Example:
'''Cube schema'''
 
    ...
    <Table name="data_sample"/>
    ...
    <Dimension name="Example" foreignKey="CHOICE">
        <Hierarchy hasAll="true" primaryKey="ChoiceID">
            <Table name="formfieldchoice"/>
            <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'''
 
    ...
    <Table name="'''data_sample'''"/>
    ...
    <Dimension name="Example" foreignKey="StatusID">
        <Hierarchy hasAll="true" primaryKey="StatusID">
            <Table name="formstatus"/>
            <Level name="Status" column="Status" uniqueMembers="true"/>
        </Hierarchy>
    </Dimension>
    ...


=== Related table ONE step away ===
== Defining dimensions for related records ==


==== Solution structure ====
=== Related solution ONE step away ===
child --> parent


==== Database structure ====
'''Structure'''
data_child --> data_parent
{|
|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  
* data_child  
** PARENT: Key to the "parent" solution
** PARENT: Key to the "parent" solution
* data_parent
* data_parent
** GRANDPARENT: Key to the "grandparent" solution
** GRANDPARENT: Key to the "grandparent" solution
** PARENTNAME: Descriptive field
** PARENTNAME: Descriptive field


==== Cube schema ====
'''Cube schema'''


     ...
     ...
Line 48: Line 160:
     ...
     ...


=== Related table TWO steps away ===
=== 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.


==== Solution structure ====
'''Structure'''
child --> parent --> grandparent
{|
|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
|-
|}


==== Database structure ====
'''Data model'''
data_child --> data_parent --> data_grandparent


* data_child  
* data_child  
** PARENT: Key to the "parent" solution
** PARENT: Key field pointing to the "parent" solution


* data_parent
* data_parent
** GRANDPARENT: Key to the "grandparent" solution
** GRANDPARENT: Key field pointing to the "grandparent" solution
** PARENTNAME: Descriptive field
** PARENTNAME: Descriptive field


Line 66: Line 188:
** GRANDPARENTNAME: Descriptive field
** GRANDPARENTNAME: Descriptive field


==== Cube schema ====
'''Cube schema'''
 
     ...
     ...
     <Table name="data_child"/>
     <Table name="data_child"/>
Line 83: Line 204:
     ...
     ...


=== Enumerations / groups ===
== 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">
        <Table name="data_changemanagement"/>
        <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 ==
== 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'''
=== Calculated values ===

Latest revision as of 11:55, 10 December 2021

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

Related solution ONE step away

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

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 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">
</Join> <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

Calculated values

Retrieved from "https://wiki.tempusserva.dk/index.php?title=Tutorial/Schema_cheatsheet&oldid=4763"