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

   ...

... <Dimension name="Example" foreignKey="CATEGORY"> <Hierarchy hasAll="true" primaryKey="LookupID"> <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

   ...

... <Dimension name="Example" foreignKey="StatusID"> <Hierarchy hasAll="true" primaryKey="StatusID"> <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

   ...

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

Physical model	Broad category > Narrow category
Schema model	FACT table > Narrow category > Broad category

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

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