Populating a CDF dashboard with data coming from a PDI transformation

A dashboard is in its broad sense is an application that shows you visual indicators, for example, bar charts, traffic lights, or dials. A CDF dashboard is a dashboard created with the Community Dashboard Framework. CDF accepts many kinds of data sources being the output of a Kettle transformation being one of them.

In this recipe, you will create a very simple dashboard that shows this capability. You will type the name of a city and the dashboard will display graphically the 5-days forecast for that city. The forecast information will be obtained with the sample transformation explained in the introduction.

Getting ready

In order to follow this recipe, you will need a minimal experience with the Pentaho BI Server. Some experience with the Community Dashboard Editor (CDE) is also desirable.

Before proceeding, make sure you have a Pentaho BI Server running. You will also need the CDE. You can download it from http://cde.webdetails.org. To install it, simply unzip the downloaded material and follow the instructions in the INSTALL.txt file.

Finally, you will need the sample transformation weather_np.ktr.

How to do it...

Carry out the following steps:

  1. Log into the Pentaho User Console.
  2. Create the solution folder where you will save your work.
  3. Copy the sample transformation to the solution folder and refresh the repository.
  4. From the File menu, select New | CDE Dashboard or click on the CDE icon in the toolbar.
  5. Save the dashboard in the solution folder that you just created, close the dashboard window, and refresh the repository. A new file with extension wcdf will appear in the solution folder.
  6. Go to the solution folder, right-click on the dashboard file and select Edit. The dashboard editor will appear. Maximize the window, so that you can work more comfortably.
  7. Define the dashboard layout by adding rows and columns from the layout toolbar, until you get the following screen:
How to do it...

Now, let's add the visual elements of the dashboard.

  1. Click on Components from the menu at the top-right area of the editor.
  2. From the Generic category, add a Simple parameter. Name it city_param and type Lisbon, Portugal for Property value.
  3. From the Selects category, add a TextInput Component. Name it city_textbox. For Parameter, select city_param and for HtmlObject, select filter_panel.
  4. Click on Save on the menu at the top of the editor.
  5. Click on Preview; you will see the dashboard with a textbox prompting for the city_name parameter, showing the default value Lisbon, Portugal.
  6. Close the preview window.
  7. Now, you will add the chart that will display the forecast. From the Charts category, add a CCC Bar Chart.
  8. Fill in the properties as follows:
    • For Name, type forecast_bars
    • For Width, type 350
    • For Height, type 250
    • For Datasource, type forecast
    • For Crosstab mode, select True
    • For Title, type 5-days forecast
    • For HtmlObject, select chart_panel
    • For Listeners, select city_param
  9. Click on the Parameters property and in the window that displays, add one parameter. For Arg0, type CITY and for Val0, type city_param, and then Click on Ok.

Finally, you have to create the datasource for that chart: forecast. The following steps will do it:

  1. Click on Data Sources from the menu at the top-right area of the editor. In the list of available data sources, click on KETTLE Queries and select kettle over kettleTransFromFile. A new datasource will be added.
  2. Fill in the list of properties as explained in the following steps:
  3. For Name, type forecast.
  4. For Kettle Transformation File, type weather_np.ktr.
  5. Click on Variables and in the window that displays, click on Add. For Arg0, type CITY and click on Ok.
  6. Click on Parameters and in the window that displays, click on Add.
    • For Arg0, type CITY
    • For Val0 type Lisbon, Portugal
    • For Type0 leave the default String and click on Ok.
  7. Click on Output Options and in the window that shows up, click on Add three times. For Arg0, Arg1, and Arg2, type 1, 2, and 3 respectively and click on Ok.
  8. Click on Column Configurations and in the window that displays, click on Add twice. In the first row, type 2 for Arg0 and MIN for Val0. In the second row, type 3 for Arg1 and MAX for Val1.
  9. Click on the little square to the next of the Query property. The Sql Wizard shows up. In the editing area, type next_days and click on Ok.
  10. Save the dashboard by clicking on Save and click on Preview to see the dashboard. You should see a result similar to that shown in the following screenshot:
    How to do it...
  11. If you take a look at the Pentaho console, then you will see the log of the Kettle transformation that is executed.
  12. Try changing the value for the city and press Enter. The chart should be refreshed accordingly.

How it works...

In this recipe, you created a very simple dashboard. The dashboard allows you to enter the name of a city and then refreshes a bar chart displaying the 5-days forecast for that city. The special feature of this dashboard is that it gets data from a web service through the execution of a Kettle transformation.

In order to use the output of your Kettle transformation as data source, you just have to add a new datasource from KETTLE Queries | kettle over kettleTransFromFile and configure it properly. This configuration involves providing the following properties:

Property

Meaning / Purpose

Example

Name

Unique datasource name inside the dashboard

forecast

Kettle Transformation File

Name of the transformation file

weather_np.ktr

Variables

Name of variables that are passed to the transformation. You have to provide it in pairs (<CDE parameter>,<Kettle named parameter>) or (<CDE parameter>,"") if both coincide.

"CITY",""

Access Level

Public (available from outside) / Private (available only from other data sources)

Public

Parameters

Name, default value, and type for each parameter to be passed to the transformation

"CITY", "Lisbon, Portugal", "String"

Output Options (opt)

Indexes of the columns to pick among the fields coming out from the transformation

1,2,3

Column Configurations (opt)

Renaming the columns coming out from the transformation

2,"MIN"

Column Configurations II (opt)

Calculating new columns based on other columns

AVG, (MAX + MIN)/2

Query

Name of the step that delivers the data

next_days

Cache

Keep results in cache (True/False)

TRUE

Cache Duration

Time to keep the results in cache in seconds

3600

Once you configured your Kettle transformation as a datasource, it was ready to be used in the components of your dashboard.

There's more...

CDF is a community project whose objective is mainly to integrate dashboards in the Pentaho's solution repository structure. In the recipe, you used the CDE, which is a graphical editor that complements the power of the CDF engine. With CDE, you can create dashboards without having to get involved in the low-level details of the CDF files, thus focusing on the business requirements.

Kettle is just one of several kinds of data sources accepted by CDF. Behind the scenes, most of the data sources definitions are saved in a CDA file.

Note

If you already have a CDA file that has a data access for your transformation, then you can avoid configuring the data source twice and use the Community Data Access | CDA Datasource instead.

CDF is bundled with the Pentaho BI Suite, but maintained by Webdetails with the help of the community. For more information about CDF, see the full documentation here: http://cdf.webdetails.org. For more on CDE visit http://cde.webdetails.org.

See also

The recipe named Generating files from the PUC with PDI and the Community Data Access (CDA) plugin in this chapter. This recipe explains about CDA.

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

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