Tutorial/Schema cheatsheet

From TempusServa wiki
Jump to navigation Jump to search

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 text/number values

Date/period values

Enumerated values

Defining dimensions for related data

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

Enumerations / groups

Defining measures