Tutorial/Schema cheatsheet

Schema structure overview

• Table (one):
  • Structure: The centre table

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

• Measures (many)
  • Structure: Values in the centre table
  • OLAP: Numbers to display in cell cubes

Defining dimensions for related data

Simple groupable values

Example:

Related table ONE step away

Solution structure

child --> parent

Database structure

data_child --> data_parent

• 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 table TWO steps away

Solution structure

child --> parent --> grandparent

Database structure

data_child --> data_parent --> data_grandparent

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