Typically, developing dashboards can be useful for getting data from different sources, such as SQL, MDX, APIs, and so on. In cases such as these, it's a good thing to know how to execute a PDI transformation in a dashboard. With PDI transformations, you have almost unlimited power when it comes to data loading and manipulation.
This recipe guides you through creating a PDI transformation data source for a CDE dashboard.
To get ready for this recipe, you first need to start the MongoDB server with the same database as that of the last chapter. You will also need to start the Pentaho BA Server using the server control scripts. Once started, you should be able to log into the BI Server.
Proceed with the following steps:
- In the PUC, go to File | New | CDE Dashboard, like this:
- Click on the Data Sources tab that is shown as selected in the following screenshot:
- Expand the KETTLE Queries data source category.
- Click on the Kettle Over KettleFromTransFile data source, as shown in the following screenshot:
- Set the Name property to QUERY1.
- Click on the Browse button on the Kettle Transformation File property.
- Navigate to Public | Pentaho MongoDB Cookbook | Transformations and select Chapter7-getorders.ktr.
- Click on OK, as shown here:
- Set the Kettle Step Name property to OUT, like this:
- Click on Save As under the CDE main menu.
- Save your dashboard in Public | Pentaho MongoDB Cookbook | Dashboards with the name
Transformation Data Source.
In these steps, we simply added a valid data source to the CDE dashboard. However, if we were to preview this dashboard, we wouldn't see any data. This is because we haven't added any dashboard components that use this data source. Instead, the dashboard has saved this connection to another file, called a CDA file. This CDA file is attached to this dashboard and contains all our data sources.
To test that this data source works, we are going to execute it directly from the accompanying CDA file:
- Click the Opened menu on the Pentaho user console and select Browse Files.
- Navigate to Public | Pentaho MongoDB Cookbook | Dashboards.
- Select the Transformation Data Source.cda file.
- Click on Open on the right-hand side menu, as shown here:
- Click on the Data Access dropdown and select Data Access ID: QUERY1, as shown in the following screenshot:
The query will execute once you have selected it from the Data Access drop-down menu. You will see the contents of the transformation that is attached to the dashboard you defined earlier, as shown in this screenshot:
In this recipe, we created a new CDE dashboard and added a PDI transformation data source into the data sources section. The data source was added by selecting a transformation that was predefined and saved in the repository when we imported the ZIP file with the sample resources. Once we saved the CDE dashboard, it generated an accompanying CDA file that would store all our data sources.
To test whether the data source was working correctly, we opted to execute the query directly from the generated CDA file. It was possible to see that the transformation was executed successfully in the CDA file and returned a list of orders from the MongoDB database.