Creating a Mondrian 4 physical schema

In this recipe, we will guide you so that you can start creating a Mondrian 4 schema for use with the MongoDB native connection. This feature is available only on Pentaho Enterprise Edition. In this particular recipe, we will start by creating the physical schema. This schema is responsible for defining the physical database. In this case, the data source, MongoDB, is where we specify the JSONPath of the fields in the collection.

As no GUI tool exists yet for managing this different OLAP schema, we'll use a normal text editor. However, there is a tool that helps to generate schemas automatically by editing Mondrian 4 schemas using MongoDB and managing the olap4j.properties file that is responsible for storing the connections information. You can get the source code on GitHub at https://github.com/kromerm/MondrianMongoModel.

Getting ready

Open your favorite text editor, such as Notepad, sublime text, or any other editor. We recommend a good one for XML syntax and indentation as the Mondrian schema is in XML.

How to do it…

Proceed with the following steps:

  1. With the text editor opened, let's start by declaring the schema with the name Mondrian4MongoDBPentahoCookbook. We write this line:
    <Schema name='Mondrian4MongoDBPentahoCookbook' quoteSql='false' missingLink='ignore' metamodelVersion='4.00'></Schema>
  2. Then let's add the PhysicalSchema tag to the Schema tag. Write the following line inside the Schema tag: <PhysicalSchema></PhysicalSchema>.
  3. Add the Orders table (in this case, it is a collection, not a table) by writing this line inside the PhysicalSchema tag: <Table name='Orders'></Table>.
  4. Finally, let's add the columns of the Orders collection by putting the following lines inside the Table tag:
    <ColumnDefs>
  <CalculatedColumnDef name='orderDate' type='String'>
    <ExpressionView>
      <SQL dialect='generic'>
        $orderDate
      </SQL>
    </ExpressionView>
  </CalculatedColumnDef>
  <CalculatedColumnDef name='requiredDate' type='String'>
    <ExpressionView>
      <SQL dialect='generic'>
        $requiredDate
      </SQL>
    </ExpressionView>
  </CalculatedColumnDef>
  <CalculatedColumnDef name='customerNumber' type='Numeric'>
    <ExpressionView>
      <SQL dialect='generic'>
        $customer.customerNumber
      </SQL>
    </ExpressionView>
  </CalculatedColumnDef>
  <CalculatedColumnDef name='customerCountry' type='String'>
    <ExpressionView>
      <SQL dialect='generic'>
        $customer.address.country
      </SQL>
    </ExpressionView>
  </CalculatedColumnDef>
  <CalculatedColumnDef name='customerName' type='String'>
    <ExpressionView>
      <SQL dialect='generic'>
        $customer.name
      </SQL>
    </ExpressionView>
  </CalculatedColumnDef>
  <CalculatedColumnDef name='productName' type='String'>
    <ExpressionView>
      <SQL dialect='generic'>
        $product.name
      </SQL>
    </ExpressionView>
  </CalculatedColumnDef>
  <CalculatedColumnDef name='productCode' type='String'>
    <ExpressionView>
      <SQL dialect='generic'>
        $product.code
      </SQL>
    </ExpressionView>
  </CalculatedColumnDef>
  <CalculatedColumnDef name='totalPrice' type='Numeric'>
    <ExpressionView>
      <SQL dialect='generic'>
        $totalPrice
      </SQL>
    </ExpressionView>
  </CalculatedColumnDef>
  <CalculatedColumnDef name='quantityOrdered' type='Numeric'>
    <ExpressionView>
      <SQL dialect='generic'>
        $quantityOrdered
      </SQL>
    </ExpressionView>
  </CalculatedColumnDef>
</ColumnDefs>
  5. Save this Mondrian schema for now in your filesystem with the name MongoDBPentahoCookbook.mondrian.xml.

How it works…

In this recipe, we started creating the Mondrian 4 schema with the name Mondrian4MongoDBPentahoCookbook. The first step of creating a Mondrian 4 schema is the physical schema. The physical schema consists of a description of tables and columns in the database. This provides the data for dimensions and cubes in the logical schema.

In a Pentaho MongoDB native connection, the physical schema is responsible for mapping the JSON properties in the JSON documents in the collection to represent the columns of a table. In this specific example, we created an Orders table with the important columns for the OLAP schema. It's important to note that the Pentaho native connection works only for a single collection; you can't use multiple collections to join data.

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

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