Revision as of 07:36, 28 November 2013 by old>Admin
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
<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
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
