As was mentioned in the explanation of the last recipe, Pentaho Instaview automatically creates ETL transformations to define data flow from the source (MongoDB in this case) to the target. Instaview gives us the ability to edit those transformations with Pentaho data integration.
This recipe guides you through editing the transformation of MongoDB Orders data source, which was created in the last recipe, in order to get better and cleaner data from the MongoDB database.
To get ready for this recipe, you first need to start Instaview with the MongoDB Orders data source, and the MongoDB server with the same database as that of the last chapter.
As with any automated tool there is always something that needs to be tweaked. Perform the following steps to make a little change to the transformation that populates data into the analysis view:
- In Instaview home, click on the Open Existing button.
- Select MongoDB Orders from the Data Sources list and click on OK.
- Click on the Configure tab.
- Next, click on the Edit link of Data Integration section. Accept the alert about editing the transformation by clicking on the OK button.
- Add the order year and month to the transformation as follows:
- Select the Design tab in the left-hand-side view of the Data Integration perspective.
- From the Transform category folder, find the Calculator step and drag and drop it into the working area in the right-hand-side view.
- Connect the Input step to the new Calculator step and then connect the Calculator step to the Do Not Edit step.
- Double-click on the Calculator step to open the configuration dialog.
- Add a new field by clicking on New field and name it
orderYearDateas the calculation is Year of date A, and select orderDate in the column Field A. - Again, add a new field named
orderMonthDateas the calculation is Month of date A, and select orderDate in the column Field A. - Click on the OK button of the Calculator step and save the transformation. The transformation structure should be like that shown in the following screenshot:
- Save the transformation and change to the Instaview perspective.
- Click on the Run button as seen in the following screenshot:
- After the execution, the two new levels will be visible in the report. Drag and drop the orderYearDate level into the Rows area, orderMonthDate into the Columns area, and then drag and drop Totalprice into the Measures area. Basically, you have a report with the total price of order by year over each month, as shown in the following screenshot:
The possibility of editing the data flow in Pentaho data integration opens many doors to solutions that require different data sources and data processing. In this recipe, we just changed the transformation a little to add two new fields useful for analysis reporting.
Basically, we change the transformation responsible for extracting the data for the report analysis. This allows us to aggregate the data in years and months. This was possible using the simple calculator step, which extracts the year or month from a specific date field in the data stream.