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

<Dimension name="Scope" foreignKey="TASKTYPE"> <Hierarchy hasAll="true" primaryKey="LookupID"> <Level name="Type" column="Value" uniqueMembers="true"/> </Hierarchy> </Dimension> <Dimension name="Type" foreignKey="TYPE"> <Hierarchy hasAll="true" primaryKey="LookupID"> <Level name="Type" column="Value" uniqueMembers="true"/> </Hierarchy> </Dimension> <Dimension name="Status" foreignKey="StatusID"> <Hierarchy hasAll="true" primaryKey="StatusID"> <Level name="Type" column="Status" uniqueMembers="true"/> </Hierarchy> </Dimension> <Dimension name="Period" type="TimeDimension"> <Hierarchy name="Periode" hasAll="true" allMemberName="All period"> <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