# OLAP 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 hierarchy: Year / Quarter / Month
- Categories by Scope, Type and Status

## <span class="mw-headline" id="bkmrk-setting-up-the-cube-1">Setting up the cube</span>

A new OLAP cube is set up in: "Integration" &gt; "OLAP cubes" &gt; "Add"

### <span class="mw-headline" id="bkmrk-basic-information-1">Basic information</span>

- Name display: sample1
- Cube header: Report sample1

### <span id="bkmrk-"></span><span class="mw-headline" id="bkmrk-cube-definition-%28%22cu-1">Cube definition ("Cube Schema XML")</span>

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

- [http://mondrian.pentaho.com/documentation/schema.php](http://mondrian.pentaho.com/documentation/schema.php)

```xml
<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>
```

### <span id="bkmrk--1"></span><span class="mw-headline" id="bkmrk-mdx-query-%28%22cube-que-1">MDX query ("Cube Query MDX")</span>

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

- [http://technet.microsoft.com/en-us/library/ms145971.aspx](http://technet.microsoft.com/en-us/library/ms145971.aspx)

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

## <span class="mw-headline" id="bkmrk-results-1">Results</span>

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

[![image.png](https://docs.tsnocode.com/uploads/images/gallery/2025-04/scaled-1680-/DT1uGurX8iq4UTEA-image.png)](https://docs.tsnocode.com/uploads/images/gallery/2025-04/DT1uGurX8iq4UTEA-image.png)

[![image.png](https://docs.tsnocode.com/uploads/images/gallery/2025-04/scaled-1680-/RdSDIs4HYtgV3pWL-image.png)](https://docs.tsnocode.com/uploads/images/gallery/2025-04/RdSDIs4HYtgV3pWL-image.png)