Dashbuilder 6.2.0 released

The 6.2.0 Final release is available for download in the dashbuilder.org website.

• Improved import/export support for unattended dashboard deployment. This allows for automating the movement of dashboards between different environments (dev/test/prod). Further details in the following 6.2.0 book's chapter. 

• Added support for Weblogic 12c and Sybase DB (ASE 15.7) 

UF Dashbuilder - Rendering Subsystem

   More and more data is being collected nowadays, but collecting lots of data does not make any sense until these data can be extracted, analyzed and interpreted to make it understandable for business people. Data visualization is one of the key points of this process: data visualization is the presentation of data in a pictorial or graphical format. There exist several ways of displaying and representing these data such as tables or charts.

Data visualization software is wide extended in the market, there exist lots of libraries that provides the ability to create data tables and all kind of charts. Some of them are proprietary, some open source, some provides specific graphs and others are designed to be used in specific scenarios.

In Dashbuilder, we think the best approach is to be able to adapt to any of those libraries easily. It allows the developer to use all the power of the Dashbuilder's data set API and display the data as user expects, using different data visualization components from different libraries that can be adapted to every situation.

These visualization components that integrate the visualization libraries with Dashbuilder Data Set API are called Displayers. Dashbuilder provides by default some displayer components, such as the Google bar, pie and table displayers, or the Lienzo bar chart displayer, among others.

Another concept that comes into play is the Renderer Library:  a Dashbuilder component that allows the framework to use any third party data visualization library. It allows the communication between data set API and the way how data is visualized. The goal of a renderer is to provide a Displayer for each of the visualizations supported.

As you can see, data visualization is the last stage in business data analysis; as it's what the end user will see and use to take business decisions. So it's very important to talk about how Dashbuilder renders the data and how it adapts to any situation by integrating and handling third party data visualization libraries.

This article explains the internals of the Dashbuilder rendering subsystem, its architecture and the design of the different components and interfaces involved.

The rendering subsystem

   The rendering subsystem in Dashbuilder is the responsible for generating the different kinds of visualization components, such as bar charts, pie charts, area charts, bubble charts, tables, maps and so on.

These are some examples of visualization components provided by default:

GWT Bar and Pie Charts

The main advantage of the rendering subsystem is that it's not tied to any visualization library, as it provides a pluggable mechanism in order to use your favorite one.

Data visualization libraries are pluggable. What does that mean?

   Dashbuilder is a data visualization framework that provides the ability to query, collect and organize your business data, but its goal is not focused in developing and providing data visualization components on the client side; it just provides the grouped, filtered an sorted data to use in those visualization components, that can be provided by any third party data visualization library.

Summarizing, the rendering subsystem provides a pluggable mechanism that connects the data set API with a data visualization component, and it allows to use any third party library to visualize it. 

This external data visualization mechanism is represented by the  RendererLibrary interface and managed by the RendererLibLocator as you can see in the following diagram: 

Renderer subsystem main interfaces

As an example, consider the following two charts. Both display the same chart type and same data but using different data visualization libraries: GWT and Lienzo:

Bar Chart - Lienzo vs GWT

Renderer Library

   A RendererLibrary is a component that displays the data using an specific visualization library. Its main goal is to provide a data Displayer component for each visualization component the visualization library provides.

For example, consider the GWT charting library. As it provides a bar chart, the Dashbulder's Google Renderer library will provide the GoogleBarChartDisplayer. As it provides a pie chart, the Dashbulder's Google Renderer library will provide the PieBarChartDisplayer. And make it extensible for all kind of GWT charts.

The following UML diagram shows an example of a RendererLibrary implementation for Google GWT charting.

In this example, the renderer implementation class is GoogleRenderer (implements Renderer interface) and it provides a GoogleDisplayer based class (implements Displayer interface). Note that GoogleDisplayer class extends some other abstract Google base classes, but it's up to the developer the architecture for it. Dashbuilder just requires to implmenent the Displayer interface for drawing the component.

Google Renderer & Displayer classes diagram

As you can see in the diagram above, each RendererLibrary has a unique identifier. But as most important, a RendererLibrary component must provide two methods: draw and redraw, which are used by the framework as: draw method to init & render the visualization for the first time, and redraw to get and display the latest data changes.

It's important to note that each third party data visualization library can provide custom functionality and custom settings for a visualization component that are not present in the same component from another library.

For those interested in developing a new renderer, please just take a look at the current implementations of the  GoogleRenderer and LienzoRenderer in GitHub.

Available rendering libraries

   Dashbuilder supports by default:

• GWT - Data visualization library that provides several chart types that are build using the popular GWT framework. Provided by Google.
  
  The project is located at Google Code and you can find the gallery here.

• Lienzo  - Data visualization library that  it's still a prototype under development and not ready to be used in production environments. It provides, at this stage, two chart types: bar & pie. It's tight integrated with Dashbuilder and will provide extended functionality. Keep updated on it!
  
  Note that it's Open Source and distributed by Ahomé Innovation Technologies.
  
  The project is located at GitHub and you can find the gallery here.
    

These are the visualization components supported by each Rendering Library:

Renderer Library	Bar Chart	Pie Chart	Area Chart	Line Chart	Bubble Chart	Meter Chart	Table	Map
GWT	X	X	X	X	X	X	X	X
Lienzo	X	X	-	-	-	-	-	-

IMPORTANT: In future releases we will integrate new renderer libraries in order to support other visualization frameworks such as D3. Stay tuned!

The rendering process

   Once your are familiarized with the basic components in Dashbuilder, let's explain how the rendering process works. Rendering is the process of generating a component or an image from a given model by a computer program. In Dashbuilder, the rendering process allows for getting & draw a Displayer instance from a given DisplayerSettings configuration.

In the previous article, we talked about Displayers and how to create them. Now we are going to give some insights about the internals, and how Dashbuilder uses the renderer libraries to create & draw a  Displayer.

The rendering process implies:

• To perform a data set look up

Given a data set (looked up from an external system) the users want to display the data in a certain way, by performing operations on it, such as grouping, filtering or sorting. This is the initial step to achieve data visualization: collect and organize your business data in a concrete way that will allow the business decision maker to analyze and interpret it. (In this previous article you can get all the details about the data set lookup process).

• To provide the settings for the data visualization component that will display the information

At this point, the data is already collected and organized, next step is to decide which data visualization library to use and configure it for your purposes.

• To obtain a Displayer component from Renderer class

Once data set is collected, organized and the user have decided & configured the data visualization library to use, next step is to obtain the Displayer component that matches all these requirements. This displayer is provided by the renderer class.

• To draw the data visualization component (draw the Displayer instance)

Having the Displayer component instance it's time to show the data visualization component to the end user. To achieve that goal the Displayer component provides two methods: draw and redraw.

Rendering process in action

Next lines gets you into a deep detail in Dashbuilder architecture and how component interact to achieve the rendering process.

Consider your are creating a view (MyView) with a bar chart component using Dashbuilder. Your view source code would be similar to:

Example of view that uses a Bar Chart Displayer

The following diagram shows the sequence of calls between all the components involved during this bar chart displayer creation:

Displayer lookup sequence diagram

1.  Build the settings for your Displayer component that will be used to show the bar chart.
    
1. Build the displayerSettings instance from the factory methods that provide DisplayerSettingsFactory
    
2. The settings instance contains the data set look up, the renderer library to use, the data visualization component to render and its settings
     
2. Build a DisplayerHelper instance and use it to show the component (see init method)
    
1. Obtain the Displayer component instance for your settings.
     
1. Use the helper class DisplayerHelper to perform a displayer look up by using the method lookupDisplayer.  As you can see, to look up a displayer you have to provide the settings as method argument.
     
2. DisplayerHelper delegates the look up of the displayer to the DisplayerLocator class and uses the returned instance to apply other configurations.
     
3. The DisplayerLocator uses the RendererLibLocator component to locate the data visualization component specified by the settings instance. It's done by calling the method lookupRenderer. The result is a RendererLibrary instance, that is the responsible to provide a Displayer component that will display the data by using the settings provided.
     
2. Use Displayer component API methods to draw the data visualization component.
     
1. Once we have a Displayer component instance that matches our settings, it's time to draw the data visualization component, in this case, the bar chart.
     
2. The DisplayerHelper class provides a method for showing the data visualization component: draw. This method takes the Displayer instance as argument and perform all the operation to draw the chart.
     
3. Note that DisplayerHelper delegates the draw logic to the RendererLib instance, as it's each renderer library the responsible to draw the data in a certain way and using a certain library.

Conclusion

Data visualization is one of the most important aspects in any data analysis software, it provides an understandable way to analyze your business data.

In Dashbuilder we don't want to force end users to see and analyze all kind of data in the same way, we don't want to be tied or focused to an specific data visualization component, as depending on the nature of the data, a different visualization technique or library could be required.

For those reasons we think the best approach is to provide a rendering subsystem that can be integrated with any third party data visualization library, as it provides an extensible and scalable way to analyze and display your business data!

UF Dashbuilder Displayer Editor & API

  In the previous article, we introduced the data set API and how to perform data set look ups. Next step is to visualize this data set in a graphical way. So translating that into Dashbuilder language: to create a Displayer. A Displayer is a component responsible for rendering a data visualization component such as a bar chart, pie chart, tables, etc. This article explains how you can create a displayer to visualize your data. 

There exist two methods:

• The Data Displayer UI Editor
  
  This is a UI component used to configure all the data set look up, displayer settings and other stuff. Can be used to create, update or remove data displayers at runtime.

• The Dashbuilder's Displayer API
  
  Instead of creating the displayer at runtime using the UI, you can create displayers at compile time using the Dashbuilder's API.   

Creating a displayer from the UI

You can create, update or remove displayers at runtime using the Displayer Editor. This is the initial screen of this component:

Data Displayer Editor component

As you can see, there exist three main tabs:

• Types tab - Allows to select the displayer type, such as bar chart, pie chart, table, etc.
    
• Data tab - Allows to configure the data set look up for that displayer in order to fetch the data that will be displayed.
    
• Display tab - Allows to select a renderer and configure the settings for the displayer type selected. the set of settings available varies depending on the displayer type and renderer selected.

So the steps to create or update a displayer are:

• Choose the desired visualization type from the type tab. In this example. a Line Chart is selected.

Data Displayer Editor - Type tab

• Click on the Data tab and configure the data lookup settings.

Data Displayer Editor - Data tab

• First step is to select a Data Set instance. In that case we are using the expenseReports data set.
   
• You can filter the data set rows by adding filters in the Filters section.
   
• Select the categories property for the Line chart, in this case, the office data property.
   
• Add the series for the Line chart: the property and the function to apply. In this example, we are using the sum function to calculate the amount per office.

• Click on Display tab to configure the display settings.

Data Displayer Editor - Display tab

In this tab you have all the displayer settings available for the displayer type selected. Here you can configure the chart position, chart size, chart title, axis titles, margins, legend, tooltips and much more.

Another interesting setting is the renderer selector. It allows to change the RendererLibrary for type of chart. For example, we can switch from GWT to Lienzo charts by just changing the selector value:

Renderer selector

Note that this selector only appears when there exist more than one renderer for the selected displayer type.

• Once the type, the data lookup and display settings are configured, just click the OK button to see the results. The chart is rendered according the set of settings defined:

Displayer built using Data Displayer Editor in the UI

Creating a chart using the Displayer API

You can also use the Dashbuilder's API to create a Displayer instance. Basically, the first step is to create a DisplayerSettings instance and use this instance to lookup a Displayer instance that will show the data visualization component. Next screenshot is an example of a DisplayerSettings used to build a Line chart with a data set that contains the sales evolution per year:

DisplayerSettings creating using Dashbuilder's API

From the code above:

1. Use of DisplayerSettingsFactory to create a DisplayerSettings instance for a displayer type, in this example, a line chart.
     
2. Configure the Displayer configuration parameters such as title, columns, filters and so on. Several API methods are available in the DisplayerSettings interface: title, columns, filters, renderer, sort operations, grouping, etc.
    
3. Configure a DataSetLookup.
    
1. You can create a new data set inline as in the example by using the DataSetFactory component.
    
2. You can use an existing data set by specifying its UUID (the data set must have been previously deployed or created using the UI).
    
4. Last step is to call the buildSettings method from DisplayerSettingsFactory. It provides the DisplayerSettings instance for your settings. 

If you want to use a concrete RendererLibrary for rendering the displayer, you can use the renderer method from DisplayerSettings as:

Using a concrete renderer via API

                   
Once you have the DisplayerSettings instance built you can use the DisplayerHelper class to visualize your displayer in your view page. As easy as follows:

Creating your view

  As you can see, the constructor for the DisplayerView helper class requires the DisplayerSettings instance previously built and it can be added in any GWT panel, as it's considered a composite GWT widget.

Once DisplayerHelper widget instance is created and added into your view panels, you can use the draw method to display it. 

Finally your view look like:

Displayer created using Dashbuilder's API

and.... that's all! :-). For those interested in the API details, please take a look at the Dashbuilder gallery which contains more examples of how to build displayer instances using the API - Gallery examples.

UF Dashbuilder and the new data set architecture

   Uberfire and the GWT echosystem brings not only a lot of exciting cool features but also an extremely powerful development environment. UF Dashbuilder stands for the Uberfire-zed version of Dashbuilder.  Since last year, we've been working on rewriting the whole Dashbuilder application on top of Uberfire and GWT. During this migration stage we have rewritten, almost entirely, the backend layer and some of the UI components. There is still a lot of work to do, as we know, but we want to start sharing what we have achieved so far.  In previous articles we showed how to use the technology to build dashboards using the Displayer API. Today we're going to explore the Data Set architecture, how to define and deploy data sets in Dashbuilder and make the dashboards feed from them.

   So what is a data set? Well, basically, it is a set of columns populated with some rows. Another valid definition is: a matrix of data composed by timestamps, texts and numbers values. A data set can be stored into different systems: a database, an excel file, in the memory of an app. or into a lot of other different systems. The good news is that in Dashbuilder there exist an standard way to define a data set, regardless where the data set is stored.

Data set definitions

  Every time we want to provide access to a given external data, a data set definition has to be deployed. That definition contains information about:

• where the data set is stored, 
• how can be accessed, read and/or parsed, and
• what columns contains and of which type.

    Let's take for instance the following data set definition:

CSV data set definition example

     The definition is a JSON document containing the following properties:

• uuid: A unique universal identifier. 

• provider: The method used to get access to the data set. Depending on the selected method a set of extra properties need to be provided, For instance, the filePath is needed in CSV along with separatorChar, quoteChar and escapeChar, in order to parse & read the CSV file. 

         At the time of this writing we support the following providers:

• CSV, for accessing data stored in comma-separated-value files.
• SQL, for getting data from relational databases through SQL queries.
• BEAN, a Java bean interface for generating data sets directly from Java. 
• ELASTICSEARCH, for querying documents stored into Elastic Search indexes.

• isPublic: if set to true means that it can be accessed from the UI editors by anyone with the right permissions. 

• columns: this section is used to define which are the data columns we want to be part of the data set, including their type and format.  Columns not defined here will be considered as non-existing  even if they are part of the data stored. There exist 4 types available:

• date: for date-time values.
• number: for numeric values.
• label: for text context that can be categorized.
• text: for non-categorizable text content (more info at the Data set lookups section).

• pushEnabled
• pushMaxSize
• refreshTime
• refreshAlways: this 4 properties are related to the caching & refresh mechanisms. Will talk about that later on in the Caching & Refresh section.

    All the properties listed above are common to all the providers, regardless of its type. Let's take a look at some other data set definition examples:

SQL data set definition   

   For SQL data sets you need to specify an existing app. server dataSource, the dbSchema (optional)  and the dbSQL used to get the data. As you can see the allColumnsEnabled is specific and tells the provider that we want to consider all the columns in the SQL as part of the data set. As for the column types they will be inferred from the database metadata. If we want to override an existing column definition we can still define a columns section as in the CSV example.

Java Bean generated data set definition

   The Java Bean provider is an extension mechanism that allows to delegate into a Java class the data set generation. In this case we only have to specify a fully qualified class name plus an optional set of parameters that will be passed to the Java Bean.

Elastic Search data set definition

   The Elastic Search provider is a very good example of integration with a nonSQL storage. The columns serverURL, clusterName, index and type are specific and tells the provider what concrete Elastic Search index and document type this data set defines.

Data set deployment

  As we've seen so far, Dashbuilder supports several data set types, and it offers a common mechanism for defining such data sets. Once a data set is defined it needs to be deployed, otherwise it won't be accessible to the Dashbuilder modules.

   Dashbuilder is delivered as a web application archive (WAR file). Inside this WAR file there exists a directory called dashbuilder.war/WEB-INF/datasets  containing all the data set definitions. Deploying a new data set it's as easy as creating a .dset file containing our JSON definition and copying that file to the deployment directory. Once detected, the data set definition will be loaded and registered automatically in the Dashbuilder data set registry. Changes made to the deployed files will be also detected. When this happens, the entire data set definition is reloaded and any cached data is removed (see the Refresh & cache section below).

   The following video, shows a live demo of how to deploy a data set at runtime and how the dashboards can get access to it automatically.

   The process described is more intended for technical people. In the next few weeks though, we will be working on a new feature for allowing the end users to edit & deploy its data sets from the UI, the Data Set Editor. We also plan for storing the data sets definitions into GIT repositories through the Uberfire's VFS service as well, to make it easier to share and move data sets between installations.

Data set lookups 

   So far so good. The next question is: how the charts in a dashboard get the data they need? Well, once a data set is deployed is ready for receiving lookup requests. For instance:

• Get the total amount of expenses by department
• Get the outstanding sales till the end of this year, grouped by office
• Get the orders received in the last 5 minutes
• Get the travel expenses by employee, only from the sales department 
• Get the sales pipeline expected for the next few years 

  As you can see, a data set lookup request is basically a query over an existing data set, but with some constraints. To be more specific, a lookup request is a sequence of data manipulation operations which produce a resulting data set. The set of operations supported are:

• filter: to get a subset of the whole data set by means of specifying constraints on one or multiple data set columns.

• group: to categorize the whole data sets into groups. LABEL and DATE are the only supported column types. 

• sort: to sort the resulting data set by one or multiple columns.

• trim: To limit the maximum number of rows the resulting data set must have.

  A lookup request takes an input data set and produces a resulting data set. As illustrated in the following diagram:

Data set lookup request

 

  So the way to express some of the examples above as a lookup request is as follows: 

• Get the total amount of expenses by department

        .dataset("expenses")

        .group("department")

        .column("department")

        .column("amount", "sum")

• Get the outstanding sales till the end of this year, grouped by office

        .dataset("expenses")

        .filter("date", timeFrame("now till end[year]"))

        .group("office")

        .column("office")

        .column("expectedAmount", "sum")

• Get the travel expenses by employee, only from the sales department 

        .dataset("expenses")

        .filter("department", equalsTo("sales"))

        .group("employee")

        .column("employee")

        .column("amount", "sum")

   How this lookup requests relates to the displayers/charts in the UI? The answer is that every displayer, no matter whether is a chart, a table or a selector, performs a lookup request in order to retrieve the data required. Obviously, the set of operations in the lookup request varies depending on the chart type. For instance, a pie chart feeds from a two column data set where the first column is usually the result of a group operation, whereas a table displayer can feed both from grouped and non-grouped data sets and they also permits a variable number of columns in the resulting data set.

  For those interested in the internals or just want to see how the Dataset API looks like, I recommend taking a look at the different test cases existing on GitHub.

   Using the Displayer Editor users can configure all the data retrieval settings, as shown in the next screenshot.

Displayer Editor UI

   From this editor, users can define the lookup's filter, group, and sort operations as well as configuring the resulting data set columns. The editor is adaptable, than means that the available settings varies depending on the displayer type selected. Actually, what the application is doing behind the scenes is building and executing a single lookup request over the selected data set.

Data providers

    So far, we have learned how to define, deploy and perform data lookup requests on a given data set. Now we're going to go a little deeper in order to describe how the Dashbuilder core deals with the processing of data set lookup requests.

  As we described before, every data set definition is linked to a provider: CSV, SQL, BEAN or ELASTICSEARCH. Each data set lookup  request is delegated to the proper data provider implementation which is responsible for resolving the request. In case of an SQL dataset, the lookup request is transformed into an SQL query which contains all the lookup's filter, group and sort operations.  Thanks to the existing provider interface, Dashbuilder does not have to take care about the lookup request resolution. We can start with a CSV data set definition and move our data to a relational database later on and all our implementations on top of such data set won't break, this includes the dashboards we build and any other client implementations we might have.

  The next diagram shows the internal pieces of the Dashbuilder's Data Set Subsystem. Every lookup request received is processed following these steps:

1. Get the data set definition the lookup request is referring to.
2. Get the provider implementation the data set is linked to.
3. Delegate into the provider the processing of the lookup request. 

Data Set Subsystem Architecture

   

   In the diagram, we can see the DataSetDeployer component which looks for data set deployments & updates.  There also exists an especial type of provider called  StaticProvider which holds and resolves lookup requests in memory. Unlike the SQL or ELS providers which execute queries against the external data storage, the CSV and BEAN providers are not query processing engines. So what they actually do is to read/generate and register the whole data set into the static (in-memory) provider. This is specially helpful for small data set use cases. For big data scenarios you should definitely consider using an SQL or ELS provider.  

   When the first lookup request over a CSV data set is requested, the CSVProvider loads, registers and delegates into the StaticProvider the lookup request processing.  So the CSV and BEAN providers are just data set loaders since the real processing is carried out by the static provider. The static provider relies on a data set operation engine implementation capable of resolving a sequence of filter, group and sort operations over a data set (further details in the next section).

Caching & Refresh

   In the beginning of this article we stated that a data set definition may contain four extra properties:

• pushEnabled (false by default )
• pushMaxSize (1024Kb by default )
• refreshTime (-1=disabled by default)
• refreshAlways (false by default )

All of them are related with the caching & refresh mechanisms. Let's take a look at the following diagram which depicts the Dashbuilder's client/server architecture.   

Client/Server Architecture 

   Imagine we have an end user interacting with a dashboard. Let's see what happens when a chart issues a data set lookup request:

   1. The DataSetClientServices class receives the request  and 

   2. ... asks the server for the data set metadata which contains the data set definition, size, ... 

   If  "pushEnabled=true" and "pushMaxSize<dataSetSize" then,

   3. The whole data set is pushed to the browser. 

   4. The data set is registered into the ClientDataSetManager.

   5. Finally, the initial (and the subsequent) data set lookup request is processed on the client.

   If  "pushEnabled=false" or "pushMaxSize is not < dataSetSize" then the lookup requests is always processed in the backend. 

   The push mechanism allows for uploading an entire data set to the user's browser. It applies to any kind of data set, no matter what is the provider type. It's a kind of browser caching mechanism. The main motivations behind this mechanism are the following:

• Improve the performance. Once a data set is loaded all the data set group, filter sort operations performed issued from the UI are resolved without any further calls to the backend.

• Support a pure lightweight client approach. The whole Dashbuilder UI components could be used without the need of the backend layer. Data sets can be registered through calls to the  ClientDataSetManager and all the lookup requests will be resolved at a client side. Obviously, this approach is not suitable for large data sets.

   The DataSetManager interface is the main entry point for any data set access operation, including the lookup requests. As shown in the diagram, there exists two implementations of the DataSetManager interface, one in GWT and  a server implementation in pure Java.  Both depend on the DataSetOpEngine, which is a GWT shared implementation that can run on both client & server, this makes possible the ability to process lookup requests in the client side.


   So far, so good. However, what if a data set is pushed and the source data is updated? or, for instance, what if a CSV file changes or if a new document is added to an Elastic Search index? Here is when the two remain settings refreshTime & refreshAlways  comes into action.


   Imagine a database which is updated every night. If we want to get the most updated data then we must set "refreshTime=1day" and "refreshAlways=true". On the contrary, if our data changes every now and then then we must set "refreshAlways=false" which means that the system will ask the database (once a day) whether the data set is outdated before invalidating the current data set.

   For SQL/ELS data sets , it makes no sense to set the refresh settings if push is disabled, since all the lookup requests will always be executed against the external storage.  Otherwise, for CSV/BEAN it always makes sense, since the contents of the data sets are always loaded and cached in the backend.

  For every data set with refresh enabled, an invalidation task is registered into the Scheduler component, as shown in the diagram above. When the refresh interval is reached, the task is executed,  a DataSetStaleEvent is fired and any data set cached data (both on the client & backend) is removed.

   From the UI perspective we can control in detail when we want a chart to get refreshed. The refresh settings are located in the Displayer Editor > Display tab > Refresh category. One option is to refresh every time a DataSetStaleEvent is received. Another option is to force to refresh every N seconds. This last option is  more suitable for real-time use cases.
 
   To sum up, if we know our data is going to change and if we want our dashboards to be notified on every update we must enable the refresh settings. Optionally, if we want to improve our dashboard performance then we can go for enabling the data push feature, but only if our data set is small enough.

   In next articles we will talk about real-time dashboards, how to build them and how to integrate Dashbuilder with a non SQL storage like Elastic Search. Stay tuned!
 

Dashbuilder Overview

For those of you who would like to learn more about Dashbuilder, Jan Hrcek QE Engineer at RedHat, published an article on DZone. The article gives a conceptual overview of the application and presents its main features. 

Thanks Jan for this contribution! 

Using filtered SQL queries for building big data dashboards

    Dashbuilder is a tool I like to describe as a “micro” BI. It lets the user create dashboards and showcase their data using pie, bar or line charts as well as display data in a tabular form. 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 whole set in memory as far as it doesn't exceed the 2MB size limit. However, most 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. 

   Let's take as an example a very simple stock exchange dashboard  which is fed from two database tables:

    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  (Key Performance Indicators) as you can see in the following screenshot: 

    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 

   At the end of this article [1] you'll find detailed instructions about how to download and install this example dashboard. 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.

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 will consume 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. The following video shows a browser window and a bash console showing that only a single SQL is executed when the dashboard is initialized.

  Pros:

• Data retrieval 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

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. The next video  shows a full SQL based version of the sales stock dashboard. As you can see, every time the user filters on the dashboard, some SQL queries are executed. No data is hold in memory, the dashboard is always asking the DB for the data.

  As you can see, on every filter request the SQLs are parsed, injected with the filter values and re-executed. 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.

-----------------------------------------------------------------------------------------------------------

  

[1]    These are the steps to download an deploy the Stock Trade sample dashboard: 

1. Download  & deploy the Dashbuilder webapp on your favorite app server - Installation instructions.  

          (You can also build from sources)

1. Extract the contents of the following zip file into  dashbuilder.war/WEB-INF/deployments folder
2. Create the stock trade database tables. Use or adapt  the H2 script file stocktrade-h2.sql provided.
3. Start the app. The dashboard should be automatically deployed.

Dashbuilder 6.1.0 released

   It's been 7 months after the latest release of Dashbuilder. During this time we've spent most of the time testing the application on other platforms such as WebSphere or WildFly as well as adding several bug fixes. As a result, the current 6.1 is much more stable and offers wider compatibility with other platforms.

   New features added:

• Support for the WildFly 8.x and WebSphere 8.x application servers
• Ability to embed KPIs into third-party applications  (as we advanced a few months ago in this  blog entry)

  The links to the release artifacts can be found on the project website http://dashbuilder.org