This recipe guides you through creating an Orders cube and linking the shared Date dimension to the business fact dates, such as order date, required date, and shipped date. We will be creating the main measures, such as the total price, the quantity ordered, and the calculated measured average price.
Before you start this recipe, you need to make sure that you have the MongoDB database created, as done in the previous chapters. The Data Integration server should be running and you should have the schema you created in the previous recipe.
Proceed with the following steps:
- In the Schema, right-click on it and select the Add Cube option, or you can just click on the Add Cube icon in the tools menu of your subwindow.
- Select the new cube object and set the name field to Orders and the caption field to Orders, as you can see here:
- Select the Orders cube, right-click, and select Add Table. Next, select the table object, and for the name field, select the Kettle->orders option.
- Let's add the relation of our Date dimension to the Orders cube. Right-click on the Orders cube and select Add Dimension Usage. In the new dimension usage object, set the name field to orderDate. For the foreignKey field, select the orderDate option; for the source field, select date; and for the caption field, set Order date. The configuration should be similar to the following screenshot:
- As you did in the step before, add a new dimension usage for the required date. The configuration should be similar to what is shown in this screenshot:
- Again, as you did in the step before, add a new dimension usage for the shipped date. The configuration should be similar to the following:
Let's add some measures to the Orders:
- Right-click on the Orders cube and select Add Measure. In the new measure object, set the name field to totalPrice. For the aggregator field, select the sum option, and for the column field, select totalPrice. In datatype, select the Numeric option, and for the caption field, set Total Price. The configuration should be similar to what is shown in this screenshot:
- Now, right-click on the Orders cube and select Add Measure. In the new measure object, set the name field to quantityOrdered. For the aggregator field, select the sum option, and for the column field, select quantityOrdered. In datatype, select the Numeric option, and for the caption field, set Quantity Ordered. The configuration should be similar what is shown here:
- Next, right-click on the Orders cube and select Add Calculated Member. In the new calculated member object, set the name field to avgPriceEach. For the caption field, set Avg Price Each, and in the formula field, set
IIF([Measures].[totalPrice]=0,0,[Measures].[totalPrice]/[Measures].[quantityOrdered]). The configuration should be similar to what is shown in the following screenshot:
After creating the shared Date dimension, we create the proper Orders cube, where the Orders table is the physical fact table. In this case, it isn't a table but the Orders MongoDB collection. This cube has three date dimensions that link to the date-shared dimension. One dimension is for describing the date when the order was placed; it is the Order Date dimension. The next one is for describing by when the order was required; it is the Required Date dimension. And the last one is for describing when the order was shipped; it is the Shipped Date dimension.
This cube contains two measures and one calculated measure. One measure represents the total price of the products, which is the Total Price measure. Basically, it is a sum of the values in the totalPrice column in the Orders table. In other words, it is used to aggregate as a sum the values of the totalPrice property in the Orders collection. The other measure is the quantity of products ordered, which is the Quantity Ordered measure. Like the total price, this aggregates as a sum the values of the quantityOrdered property in the Orders collection.
The calculated measure value is then obtained by the division of Total Price by Quantity Ordered. In this way, we get the average price of each product ordered. As you can see, there exists a condition for checking whether the quantity ordered is zero. This is because sometimes, the ordered quantity can be zero. This verification exists because you can't divide a number by zero.