View source for Tutorial/Schema cheatsheet


== 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 data ==

=== Standard lookup value ===
The cube schema below only needs adjustment for the system name of the lookup field.

'''Structure'''
{|
|Solution as displayed
|"Sample solution"
|-
|Solution system names
|sample
|-
|Database table names
|data_sample
|-
|}

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

=== 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"
|-
|Solution system names
|sample
|-
|Database table names
|data_sample
|-
|}

'''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 solution ONE step away ===

'''Structure'''
{|
|Solutions as displayed
|"Some child" -> "Father or mother" 
|-
|Solution system names
|child -> parent 
|-
|Database table names
|data_child -> 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'''

    ...
    <Table name="data_child"/>
    ...
    <Dimension name="Example" foreignKey="PARENT">
        <Hierarchy hasAll="true" primaryKey="DataID" primaryKeyTable="data_parent">
            <Table name="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" -> "Father or mother" -> "Grandma and Grandpa's"
|-
|Solution system names
|child -> parent -> grandparent
|-
|Database table names
|data_child -> data_parent -> 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'''
    ...
    <Table name="data_child"/>
    ...
    <Dimension name="Example" foreignKey="PARENT">
        <Hierarchy hasAll="true" primaryKey="DataID" primaryKeyTable="data_parent">
            <Join leftKey="GRANDPARENT" rightKey="DataID">
                <Table name="data_parent"/>
                <Table name="data_grandparent"/>
            </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 ==

=== Simple text/number values ===

=== Date/period values ===

=== Enumeration values ===

== Defining measures ==

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