In this recipe, we will guide you through creating degenerate customer and product dimensions. A degenerate dimension consists of a dimension that doesn't have its own physical table, and the data lives in the fact table. In other words, the customer and product json are sub-documents within the Order documents of the Orders MongoDB collection.
Before you start this recipe, you need to make sure you have the MongoDB databases created in previous chapters. Also, the Data Integration server should be running, and you will need the schema that you created in the previous recipe.
Proceed with the following steps:
- Let's add the Customer dimension to the Orders cube:
- Right-click on the Orders cube and select Add Dimension.
- Expand the new dimension object, and in the default hierarchy created, remove the text for the name field. For the primaryKey field, select the customerNumber option.
- Right-click on the hierarchy and select Add Level. Select the new level object and set the name field to country. Select the customerCountry option for the column field and the String option for the type field. In levelType, select the Regular option; in hideMemberIf, select the Never option; and finally for the caption field, set Country. The configuration should be similar to this:
- Again, right-click on the hierarchy and select Add Level. Select the new level object and set the name field to name. Then select the customerName option for the column field and the String option for the type field. In levelType, select the Regular option. In hideMemberIf, select the Never option, and for the caption field, set Name. Now, the configuration should look similar to what is shown in the following screenshot:
- Select the dimension object and set the name field to customer. For foreignKey, select the customerNumber option, and in the caption field, set Customer. Thus, the configuration should be similar to what is shown here:
- Let's now add the Product dimension to the Orders cube:
- Right-click on the Orders cube and select Add Dimension.
- Expand the new dimension object, and in the default hierarchy created, remove the text of the name field. For the primaryKey field, select the productCode option.
- Right-click on the hierarchy and select Add Level. Next, select the new level object and set the name field to name. Then select the productName option for the column field and the String option for the type field. In levelType, select the Regular option, and in hideMemberIf, select the Never option. Finally, for the caption field, set Name. The configuration should now be similar to what is shown in the following screenshot:
- Select the dimension object and set the name field to product. For foreignKey, select the productCode option, and in the caption field, set Product. Thus, the configuration should be similar to this:
In this recipe, we add two more dimensions: product and customer.
The customer dimension consists of one hierarchy with two levels; the first is the country and the second is the customer name. However, it could be possible to add more levels such as city and postal code, or create an address dimension with information about the address—the country, city, postal code, and detailed address.
The same is possible with the product dimension, which has just one level—the product name—and it's possible at a product line parent level. As the goal of this book is to explain how to work with Pentaho and MongoDB, we won't cover data warehousing modulation in too much detail.
If you are looking to expand your knowledge about Mondrian schemas, you can check out the online documentation at http://mondrian.pentaho.com/documentation/schema.php.