Creating your first dashboard

http://docs.jboss.org/dashbuilder/release/6.2.0.Final/html/chap-dashbuilder-first_steps.html

2.1. Creating a data provider

The first thing to do, after you have accessed the web application, is to create a data provider. On the left menu, go to ‘Administration > Data providers‘ and once there, select the option ‘Create new data provider‘.

The purpose of data providers is to gather information from any system, either a database, a file or any other, and transform it to the internal in-memory representation for building dashboards. As you may guess there exists different data sources and therefore different ways to retrieve as we’re going to see next.

Data providers table

Figure 2.1. Data providers table


2.1.1. Retrieving data from a CSV file

Click on ‘Create new data provider‘. The following fields will be shown in the form, with some sensible defaults:

Once you have filled all the fields, click on ‘Try’, to check that everything works properly. The application will give you a message ‘Correct data set ...’ and we continue by pressing ‘Save‘.

CSV data provider creation form

Figure 2.2. CSV data provider creation form


Next, a screen is shown with all the fields found after parsing the file, giving us the option to change the name of each field. For numeric fields it gives us the option to specify if we want numeric values to be treated as labels by the dashboard engine. This is something really useful when dealing with numbers which actually behave as labels, f.i: the numeric reference code of a product item.

Data provider properties configuration panel

Figure 2.3. Data provider properties configuration panel


After this last step, you can save and finish the creation of your new data provider.

New data provider instance has been created

Figure 2.4. New data provider instance has been created

2.1.2. Reading data from an SQL query

You can create a data provider to query a relational database. Go to Administration > Data providersand click on ‘Create new data provider‘. Choose the ‘SQL Query‘ option and fill the form with the data provider name and the SQL query that will retrieve the data.

New SQL data provider form

Figure 2.5. New SQL data provider form


In this form you have the ability to select the data source where the data comes from. By default the local data source is selected but you can define new connections to external data sources. To do this you should go to the ‘Administration > External connections‘ section and from there you can create a new data source connection.

Data sources management panel

Figure 2.6. Data sources management panel


New data source creation form

Figure 2.7. New data source creation form


Let’s get back to the creation of our SQL data provider. Once the data source has been selected and the query is typed in, you can click on the ‘Try’ button, and if the query is successful you will get the following message.

SQL query input filed

Figure 2.8. SQL query input filed


After that, you can rename the name of the properties to provide a more user friendly name.

SQL provider columns configuration panel

Figure 2.9. SQL provider columns configuration panel


Finally, just click the ‘Save‘ button to confirm the creation of the data provider:

2.1.3. Dealing with high volume databases

The previous sections showed how data could be loaded from plain text like CSV files or query from a database connection. When data is small enough, Dashbuilder can handle pretty well the small data sets in memory as far as it doesn’t exceed the 2MB size limit. However, must of the time, our data sets are bigger and we can’t upload all the data for Dashbuilder to handle it by its own. Is in these cases where database backed queries can help us to implement nice drill down reports and charts without preloading all the data.

Imagine a database containing two tables:

Stock trade tables

Figure 2.10. Stock trade tables


Now, let’s take as an example a very simple example of a stock exchange dashboard which is fed from the two tables above. The dashboard displays some indicators about several companies from several countries selling their shares at a given price on every day closing date. The dashboard displays 4 KPIs as you can see in the following screenshot:

Stock trade dashboard

Figure 2.11. Stock trade dashboard


All the indicators are displaying data coming from the two database tables defined above.

  • Bar chart – Average price per company
  • Area chart – Sales price evolution
  • Pie chart – Companies per country
  • Table report – Stock prices at closing date

What we’re going to start discussing next is the two strategies we can use for building a dashboard. This is an important aspect to consider, specially if we’re facing big data scenarios.

2.1.3.1. The in-memory strategy

This strategy consists in creating a data provider which load all the data set rows by executing a single SQL query over the two tables.

    SELECT C.NAME, C.COUNTRY, S.PRICE_PER_SHARE, S.CLOSING_DATE
    FROM COMPANY C JOIN STOCK S ON (C.ID=S.ID_COMPANY)

Every single indicator on the dashboard share the same data set. When filters are executed from the UI no further SQLs are executed since all the calculations are done over the data set in memory.

Pros:

  • Data integration logic keeps very simple
  • Only a single data provider is needed
  • Faster configuration of KPIs since all the data set properties are available at design time
  • Multiple indicators from a single data provider

Cons:

  • Can’t be applied on medium/large data sets due to poor performance

2.1.3.2. The native strategy

The native approach consists in having a data provider for every indicator in the dashboard instead of loading an handling all the data set in memory. Every KPI is told what data has to display. Every time the user filters on the dashboard, the SQLs are parsed, injected with the filter values and re-executed. No data is hold in memory, the dashboard is always asking the DB for the data.

The SQL data providers are the following:

  • Bar chart – Average price per company
              SELECT C.NAME, AVG(S.PRICE_PER_SHARE)
              FROM COMPANY C JOIN STOCK S ON (C.ID=S.ID_COMPANY)
              WHERE {sql_condition, optional, c.country, country}
              AND {sql_condition, optional, c.name, name}
              GROUP BY C.NAME
    
  • Area chart – Sales price evolution
              SELECT S.CLOSING_DATE, AVG(S.PRICE_PER_SHARE)
              FROM COMPANY C JOIN STOCK S ON (C.ID=S.ID_COMPANY)
              WHERE {sql_condition, optional, c.country, country}
              AND {sql_condition, optional, c.name, name}
              GROUP BY CLOSING_DATE
    
  • Pie chart – Companies per country
              SELECT COUNTRY, COUNT(ID)
              FROM COMPANY
              WHERE {sql_condition, optional, country, country}
              AND {sql_condition, optional, name, name}
              GROUP BY COUNTRY
    
  • Table report
              SELECT C.NAME, C.COUNTRY, S.PRICE_PER_SHARE, S.CLOSING_DATE
              FROM COMPANY C JOIN STOCK S ON (C.ID=S.ID_COMPANY)
              WHERE {sql_condition, optional, c.country, country}
              AND {sql_condition, optional, c.name, name}
    

As you can see every KPI is delegating the filter & group by operations to the database. The filter magic happens thanks to the {sql_condition} statements. Every time a filter occurs in the UI the dashbuilder core gets all the SQL data providers referenced by the KPIs and it parses/injects into those SQLs the current filter selections made by the user. The signature of the sql_condition clause is the following:

      {sql_condition, [optional | required], [db column], [filter property]}

where:

  • optional: if no filter exists for the given property then the condition is ignored.
  • required: if no filter is present then the SQL returns no data.
  • db column: the db column where the current filter is applied.
  • filter property: the UI property which selected values are taken.

Pros:

  • Support for high volumes of data. The database tables need to be properly indexed though.

Cons:

  • The set up of the data providers is a little bit more tricky as it requires to create SQL queries with the required filter, group by and sort operations for every KPI.

When designing a dashboard never forget of thinking thoroughly about the origin, type and the volume of the data we want to display in order to go for the right strategy.

2.2. Creating a KPI

Once the necessary data providers have been created, you can continue by adding a new Key Performance Indicator to an existing page. All dashboards are created by adding indicators and other types of panels to pages. A dashboard is a page with a mixture of different kind of panels placed into it. The following screenshot shows an example of a Sales Dashboard:

Sales dashboard example

Figure 2.12. Sales dashboard example


Pages can be created from scratch, or duplicating an existing one. Both options are explained in the following sections. Meanwhile we will assume the page already exists and we only want to add an indicator.

Page layout with en empty region on the center

Figure 2.13. Page layout with en empty region on the center


Indicators are a special type of panels, which are the widgets that can be placed within the page. To add a panel or indicator just click on the toolbar icon ‘Add panel to the current page‘:

This will make a popup be shown with all the available panels:

Panel instance selector

Figure 2.14. Panel instance selector


To add a new ‘Key Performance Indicator‘, click onDashboard > Key Perfomance Indicator. Drag the ‘Create panel‘ option and drop it into any of the page regions. You will see that they are being highlighted while you move the mouse over them, then simply drop the panel.

Drag and drop of a panel instance into an empty region

Figure 2.15. Drag and drop of a panel instance into an empty region


Once dropped, the first step is to select the Data Provider you need to use, as configured before, to feed the charts and reports with data. Select any of the data providers and then you can start creating a new indicator.

KPI creation - Data provider selector

Figure 2.16. KPI creation – Data provider selector


Now, you must see the chart edition panel. It’s an intuitive environment which helps you configure different type of charts and reports…

KPI configuration panel

Figure 2.17. KPI configuration panel

  • Domain (X Axis): The data column that is taken as the X axis. In this example, we choose the property ‘Country’.
  • Range (Y Axis): Information to be grouped and aggregated for every domain interval. For example: ‘Amount’.
  • Renderer: The rendering library to use. Each one provides different features and visualization style. By default ‘NVD3’.
  • Sort intervals by: It’s how the domain values can be sorted, for example, according to its range value.
  • Sort order: It can be ascending or descending.

To finish the panel edition just close the panel edition window. If you want to get back again, just click on the right upper corner of the panel area and select the ‘Edit content‘ menu option.

Panel administration menu - Edit content option

Figure 2.18. Panel administration menu – Edit content option


The system provides you with 3 types of chart displayers: barpie and line, and a special table displayervery useful to create tabular reports. The system also comes with 2 rendering engines: NVD3 (pure HTML5/SVG technology) and a Flash based one, Open Flash Chart 2. Each renderer has its own available features, so depending on the type of chart and renderer choosen you can end up with some display features enabled or disabled depending the case. For instance, the ‘Paint area‘ feature is not available for OFC2 line charts.

2.3. Composing a dashboard

A dashboard is basically a page with some KPIs placed on it (plus some other additional widgets as we will see later on). There are two different ways of creating a page:

Starting as a blank page:

Duplicating an existing page:

You will find these icons at the top of the page, in the administration bar:

To create a new page, click on the ‘Create new page‘ icon:

A form will be shown to fill in some parameters:

Page creation form

Figure 2.19. Page creation form


  • Page title.
  • Parent page: Pages are organized in a hierarchical way. This is the parent page.
  • Skin: This will select and specific look’n’feel and CSS stylesheet for this page. You can leave the default value.
  • Envelope: Defines which kind of HTML template will be placed around the page layout.
  • Region layout: This is the template, that is, how regions are organized to place the panels inside the page. We can choose any of the installed types, for example, “Demo – Default template with sliding”.

New page item into the page tree administration screen

Figure 2.20. New page item into the page tree administration screen


Most of these properties will be discussed in the chapter about ‘Customing look’n’feel‘. After creating the page, you might realize the page is still not accessible from the left menu but you can see it in the combo list in the administration toolbar:

Brand new empty page

Figure 2.21. Brand new empty page


If you want this page to be shown in the left menu, you can click on ‘Edit content’ and then add the newly created page to the list of options displayed in the menu.

Repeat until the page has all the content and panels required. After dropping the panels into the right regions and configuring them, you might be able to create dashboards that look like the following one:

Panel composition for a typical dashboard

Figure 2.22. Panel composition for a typical dashboard


As you can see, a dashboard is usually composed by one or more instances of the following panel types:

  • Dashboard > Key Performance Indicator
  • Dashboard > Filter & Drill-down
  • Navigation > Tree menu
  • Navigation > Language menu
  • Navigation > Logout panel

2.3.1. Duplicating a page

As mentioned earlier, another way to create new pages is to copy an existing one. We can do that via the ‘Duplicate current page‘ icon which is a much faster way to create pages. After clicking on the clone icon located at the toolbar, a page similar to the image below will be shown. From there we can select those instances we want to duplicate and those we want to keep as is (to reuse).

Wizard for page cloning

Figure 2.23. Wizard for page cloning


Once finished, press the ‘Duplicate page‘ button and a brand new page will be created with the same name as the original one but starting with the prefix ‘Copy of‘. Notice that if a panel instance is reused then any changes made to it will be reflected on all the pages where such instance is being used. this is a cool feature when we are defining for instance our navigation menus since we can define a single‘Tree menu’panel and then configure all the pages to display the same menu instance.

2.3.2. Configuring filter and drill-down

The ‘Filter & Drill-down‘ panel allows for the quick definition of dynamic forms that will allow us to navigate troughout the data displayed by the dashboard. Once an instance of the ‘Filter & Drill-down‘ panel is dropped on the oage we just have to select the ‘Edit content‘ option from the panel menu. After that, a popup window similar to the following will be displayed:

Filter panel configuration

Figure 2.24. Filter panel configuration


This is a filter configuration panel where we can set the filter behaviour. Let’s focus first on the middle bottom part of the screen: the Data provider’s property table, which lists the properties of ALL the data providers referred by the KPIs on the page. For example, if we are building a sales dashboard and all its KPIs are built on top of the same data provider called ‘Sales dasboard demo‘ then the system lists all thedata properties of the ‘Sales dashboard demo ‘ provider. Hence, only the properties we select as ‘Visible‘ will be part of the filter form. Aditionally, we can enable the drill-down feature for each property. If enabled then the system will redirect to the target page when the property is selected on the filter form. Below is an screenshot of the filter panel of the ‘Sales dashboard demo‘.

Filter panel of the sales dashboard example

Figure 2.25. Filter panel of the sales dashboard example

About Nguyễn Viết Hiền

Passionate, Loyal
This entry was posted in Business Metrics, Integration, Knowledge, Problem solving, Programming, Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s