Creating a Mondrian 4 cube

This recipe shows you how to create a Mondrian 4 cube. Basically, this is the second part, after the physical model. It is responsible for mapping the business side to the physical schema.

Getting ready

Open the schema created in the previous recipe in your favorite text editor.

How to do it…

Proceed with the following steps:

  1. Let's add the Orders cube to the schema by writing this line:
    <Cube name='Orders' defaultMeasure='Total Price'></Cube>
  2. In order to add the Product dimension, it's necessary to write the following lines in the Cube tag:
    <Dimensions>
  <Dimension name='Product' table='Orders' key='Product Code'>
    <Attributes>
      <Attribute name='Name' keyColumn='productName' hasHierarchy='false'/>
      <Attribute name='Product Code' keyColumn='productCode' hasHierarchy='false'/>
    </Attributes>
    <Hierarchies>
      <Hierarchy name='Product' hasAll='true'>
        <Level attribute='Name'/>
      </Hierarchy>
    </Hierarchies>
  </Dimension>
</Dimensions>
  3. Let's add the Customer dimension with the Country and Name levels by adding these lines to the Dimensions tag:
    <Dimension name='Customer' table='Orders' key='Product Code'>
  <Attributes>
    <Attribute name='Country' keyColumn='customerCountry' hasHierarchy='false'/>
    <Attribute name='Name' keyColumn='customerName' hasHierarchy='false'/>
    <Attribute name='Product Code' keyColumn='customerNumber' hasHierarchy='false'/>
  </Attributes>
  <Hierarchies>
    <Hierarchy name='Customer' hasAll='true'>
      <Level attribute='Country'/>
      <Level attribute='Name'/>
    </Hierarchy>
  </Hierarchies>
</Dimension>
  4. Now that the dimensions are declared, let's add Measures, Total Price, and Quantity Ordered. Insert the following lines into the Cube tag and after the Dimensions tag:
    <MeasureGroups>
  <MeasureGroup name='Orders' table='Orders'>
    <Measures>
      <Measure name='Total Price' column='totalPrice' aggregator='sum' formatString='Standard'/>
      <Measure name='Quantity Ordered' column='quantityOrdered' aggregator='sum' formatString='Standard'/>
    </Measures>
    <DimensionLinks>
      <FactLink dimension='Product'/>
      <FactLink dimension='Customer'/>
    </DimensionLinks>
  </MeasureGroup>
</MeasureGroups>
  5. Finally, let's add the Avg Price Each calculated measure to the Orders cube, writing these lines inside the Cube tag and after the MeasureGroups tag:
    <CalculatedMembers>
  <CalculatedMember name='Avg Price Each' dimension='Measures'>
    <Formula>IIF([Measures].[Quantity Ordered]=0,0,[Measures].[Total Price]/[Measures].[Quantity Ordered])</Formula>
    <CalculatedMemberProperty name='MEMBER_ORDINAL' value='9'/>
  </CalculatedMember>
</CalculatedMembers>
  6. Save the schema in your filesystem with the name MongoDBPentahoCookbook.mondrian.xml.

How it works…

In this second part, we created the proper logical cube with dimensions, measures, and calculated measures.

As we did in the previous recipes for Mondrian 3.x versions, we created two dimensions: products and customers. The product dimension has just one level, which is the name, and the customer dimension has two levels, namely the country and the customer name.

In Mondrian 4, one of the good features compared to the Mondrian 3.x version is measure groups. In Mondrian 3.x, cubes have only one fact physical table. If you want to create a cube with different fact tables joined together, you need to use a virtual cube. A virtual cube combines multiple cubes. However, this approach is no longer supported with Mondrian 4, and you need to use measure groups.

In this example, we created only one measure group. In other scenarios, it can be necessary to create more than one, with two measures: Total Price and Quantity Ordered.

After the measures, we created a calculated measure, as we did for Mondrian 3.x in previous recipes, called Avg Price Each. Basically, this is Total Price divided by Quantity Ordered, giving the average product price per order.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset