Tutorial/OLAP example

Example

We have a solution

• Change management [changemanagement]

The solution contains the following lookup fields

• Scope [TYPE]
• Type [TASKTYPE]

It also has some decimal values

• Estimate [ESTIMATE]
• Realized [REALIZED]

In our reporting we want to see distributions by

• A period hierachy: Year / Quarter / Month
• Categorieres by Scope, Type and Status

Setting up the cube

A new OLAP cube is set up in: "Integration" > "OLAP cubes" > "Add"

Basic information

• Name display: sample1
• Cube header: Report sample1

Cube definition ("Cube Schema XML")

For further help to designing schemas please refer to the following ressources

• http://mondrian.pentaho.com/documentation/schema.php

<Cube name="sample1cube">
  <Table name="data_changemanagement"/>
  <Dimension name="Scope" foreignKey="TASKTYPE">
    <Hierarchy hasAll="true" primaryKey="LookupID">
    <Table name="formfieldlookup"/>
      <Level name="Type" column="Value" uniqueMembers="true"/>
    </Hierarchy>
  </Dimension>
  <Dimension name="Type" foreignKey="TYPE">
    <Hierarchy hasAll="true" primaryKey="LookupID">
    <Table name="formfieldlookup"/>
      <Level name="Type" column="Value" uniqueMembers="true"/>
    </Hierarchy>
  </Dimension>
  <Dimension name="Status" foreignKey="StatusID">
    <Hierarchy hasAll="true" primaryKey="StatusID">
    <Table name="formstatus"/>
      <Level name="Type" column="Status" uniqueMembers="true"/>
    </Hierarchy>
  </Dimension>
  <Dimension name="Period" type="TimeDimension">
    <Hierarchy name="Periode" hasAll="true" allMemberName="All period">
      <Table name="data_changemanagement"/>
      <Level name="Aar" levelType="TimeYears" uniqueMembers="true">
        <KeyExpression>
          <SQL dialect="mysql">Year(CreatedAt)</SQL>
          <SQL dialect="generic">YEAR</SQL>
        </KeyExpression>
      </Level>
      <Level name="Kvartal" uniqueMembers="false" levelType="TimeQuarters">
        <KeyExpression>
          <SQL dialect="mysql">Quarter(CreatedAt)</SQL>
          <SQL dialect="generic">Quarter</SQL>
        </KeyExpression>
      </Level>
      <Level name="Maaned" uniqueMembers="false" levelType="TimeMonths">
        <KeyExpression>
          <SQL dialect="mysql">Month(CreatedAt)</SQL>
          <SQL dialect="generic">Month</SQL>
        </KeyExpression>
      </Level>
    </Hierarchy>
  </Dimension>
  <Measure name="Revision" column="Revision" aggregator="sum" formatString="Standard"/>   
  <Measure name="Estimate" column="ESTIMATTIMER" aggregator="sum" formatString="Standard"/>   
  <Measure name="Realized" column="REALISERETTIMER" aggregator="sum" formatString="Standard"/>   
</Cube>

MDX query ("Cube Query MDX")

For further help to designing queries please refer to the following ressources

• http://technet.microsoft.com/en-us/library/ms145971.aspx

SELECT
  NON EMPTY {([Measures].[Estimate],[Measures].[Realized])} ON COLUMNS,
  NON EMPTY {([Period],[Type],[Status])} ON ROWS
FROM [sample1cube]

Results

The cube is acccesed via this URL (or press "test" form the OLAP overview) http://YOUR_DOMAIN/TempusServa/olapview.jsp?query=sample1