Tutorial/Schema cheatsheet

Schema structure overview

• Table (one):
  • Structure: The centre table containing the measures (FACT table)
  • OLAP cube: The FACT table containing the measures

• 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

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

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

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