Data visualization and dashboard building

Before we begin discussing dashboard designer tools, it is best to clarify that only Pentaho EE has a preconfigured package to create a dashboard using an easy and user-friendly wizard with predefined templates and official support. Fortunately, there is an open source equivalent available—CTools. CTools is an additional plugin from Webdetails. Pentaho acquired Webdetails in 2013.

The following components are the parts of CTools that we will use:

  • CDF: This is a development framework for building a user-friendly dashboard layout and design
  • Community Data Access (CDA): This is a data connector to various data sources, and has a caching mechanism
  • CDE: This is a web editor plugin that simplifies the process of creating dashboards based on CTools components
  • Community Chart Components (CCC): This is a JavaScript charting library that acts as a visualization toolkit

CDE has the ability to create static or highly interactive chart components based on a layout template that we can design in a flexible way. We can easily install the tools from Pentaho Marketplace (see Chapter 2, Setting Up the Ground, for more information on Pentaho Marketplace).

To work on CDE, we need to understand its three architecture layers, given in the following list:

  • Layout: This is the HTML code to provide a base for the layout rendering of the dashboard.
  • Components: These are items or widgets to display the data that comes from a data source
  • Data Sources: These are data providers for the components. They can query data from relational databases, OLAP engines, or text files.

The following diagram shows the architecture layers of CDE's dashboard:

Data visualization and dashboard building

In order to use CDE, you will need to have enough proficiency in HTML JavaScript, and CSS. These are important skill sets required to build the CTools dashboard.

Creating a layout using a predefined template

Use the following steps to create a layout using a predefined template:

  1. Launch PUC.
  2. Click on the New CDE Dashboard icon in the PUC menu bar as shown in the following screenshot:
    Creating a layout using a predefined template
  3. A new dashboard page editor appears. There you can see the Layout, Component, and data source menu bar items along with a few pull-down menus to manage your dashboard file. Click on Layout; it will be highlighted as shown in the following screenshot:
    Creating a layout using a predefined template
  4. The layout editor appears; it has two panes: Layout Structure and Properties.
  5. Above the Layout Structure pane, click on the Apply Template icon.
    Creating a layout using a predefined template
  6. In the template chooser dialog, select Filter Template and click on the OK button. Click on OK again in a confirmation dialog. The following screenshot shows the template chooser wizard dialog:
    Creating a layout using a predefined template
  7. In seconds, CDE populates the layout structure with the Row, Column, and Html type items organized in a tree layout structure. Every item will have names that correlate to their HTML DOM IDs.
  8. Click on Preview in the menu bar. You should get a warning telling you to save the dashboard first. This is a straightforward message and the only one that CDE can render. A preview of the dashboard will only be available once there is a CDE file stored in the solution folder.
  9. Fill in the following values, click on the Save menu item, and then click on the OK button:
    • Folder: Chapter 4
    • File Name: sales_order_dashboard
    • Title: Sales Order Dashboard
    • Description: Leave this field blank
  10. Refresh the repository cache. In a moment, the dashboard name shows up in the Files pane. The following screenshot shows the dashboard name we specified—Sales Order Dashboard—displayed in the Files pane:
    Creating a layout using a predefined template
  11. Click on the Preview menu in your CDE editor. You should see the simple, predefined layout of the dashboard. The following screenshot shows the IDs of layout's part. We will later use these IDs as placeholder identifiers for our components.
    Creating a layout using a predefined template
  12. Close the preview by clicking on the X button. The default title of the dashboard is Dashboard generated with CDF Dashboard Editor.
  13. Expand the first node of the Layout Structure pane, that is, the node with the Header ID.
  14. Expand the Column type node, which is a direct child node of the Row type.
  15. Click on the Html node with the name of title.
  16. Find the HTML property in the Properties pane list. Click on the edit (...) button.
  17. Change the HTML content using the following code:
    <h2 style="color:#fff;margin-top: 20px;">Sales Order Dashboard</h2>
  18. Click on the OK button.
  19. See the change by previewing the dashboard again.

Creating a data source

The following steps will help you create a data source:

  1. In the CDE editor, click on the Data Sources menu.
  2. You will see three panels here: Datasources, Properties, and a panel with a list of data source types.
  3. We will add a SQL query from one of the types. Click on SQL Queries and select sql over sqlJndi. Note that one query item is added to a group in the Datasources pane. The following screenshot shows the sql over sqlJndi item in the Datasources list
    Creating a data source
  4. Fill in the properties of the item with the following values. Note that for Jndi, you should wait a while for the autocomplete listbox to show up. Press any arrow key to make the list show up if it does not.
    • Name: top_10_overall_orders
    • Jndi: SampleData
    • Query:
      SELECT
CONCAT(CONCAT(O1.MONTH_ID,'-'), O1.YEAR_ID) as Period
, O1.PRODUCTCODE
, SUM(O1.QUANTITYORDERED) as Value
FROM ORDERFACT O1
JOIN
(
SELECT SUM(T.QUANTITYORDERED), T.PRODUCTCODE FROM ORDERFACT T GROUP BY T.PRODUCTCODE ORDER BY
SUM(T.QUANTITYORDERED) DESC LIMIT 10
) O2
ON O1.PRODUCTCODE = O2.PRODUCTCODE
GROUP BY O1.PRODUCTCODE, O1.YEAR_ID, O1.MONTH_ID
ORDER BY O1.PRODUCTCODE, O1.YEAR_ID, O1.MONTH_ID

    We will use this SQL query later as data for our line chart component. The output of the required three fields should be in this order: series, categories, and value.

  5. Save the dashboard.
  6. Refresh the repository cache. Note that there is a new item in the Files pane: sales_order_dashboard.cda. The following screenshot shows the new CDE and CDA items created and displayed in the Files pane:
    Creating a data source
  7. Double-click on sales_order_dashboard.cda to open the CDA solution item. From the listbox, select DataAccess ID: top_10_overall_orders. In a moment, CDA will preview the data in tabular format. The following screenshot shows a CDA data preview:

    Creating a data source
  8. Close the CDA and click on the dashboard editing tab to continue working on creating a component in our dashboard.

Creating a component

The following steps will help you create a component:

  1. Click on the Components menu in the CDE editor.
  2. You will see three panels here: Components, Properties, and a list of available component groupings.
  3. From the Charts grouping, click on CCC Line Chart. It will add CCC Line Chart to the Components pane. The following screenshot shows the CCC Line Chart component:
    Creating a component
  4. In the Properties pane, fill in the following values:
    • Name: top_10_overall_orders_chart
    • Title: Top 10 Orders
    • Datasource: top_10_overall_orders
    • Width: 300
    • Height: 500
    • HtmlObject: Panel_1
    • crosstabMode: False

    Note that HtmlObject refers to Panel_1, which is a right-hand side pane placeholder in our layout.

  5. Click on the Preview menu. You should see an animated line chart rendered on the right-hand side of the dashboard. The display looks similar to the following screenshot:
    Creating a component
  6. Close the preview.
  7. Open the CDE menu item directly for the same dashboard to show up.
..................Content has been hidden....................

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