Tutorial/OLAP example

Example

We have a solution containing 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

Cube definition

 <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

 select
   NON EMPTY {[Measures].[Estimate]} ON COLUMNS,
   NON EMPTY {([Period],[Type],[Status])} ON ROWS
 from [sample1cube]