Displaying Tabular Data using the DataGrid and BigNumbers Widget

Viewpoint
Teradata Viewpoint is Teradata's strategic and innovative SOV (single operational view) for Teradata DB, Aster, and HDP Hadoop systems management and monitoring that enables Teradata's Unified Data Architecture (UDA).
Teradata Employee

Displaying Tabular Data using the DataGrid and BigNumbers Widget

The following tutorial is a guide on how to implement the Viewpoint DataGrid and BigNumbers Widget.  In this example we will show how these widgets were incorporated into the SkewedSessions Portlet.  The actual source code can be found in the SkewedSessions Portlet supplied in PDK version 14.01.

What is the DataGrid Widget

The DataGrid Widget was created to address the issue of displaying large amounts of data in tabular format within portlets.  The widget currently includes an extensive list of built in features such as:

  • Fixed top header as data scrolls
  • Sortable columns
  • Filterable columns
  • User-configurable column widths
  • User-configurable column ordering
  • User-configurable column hiding
  • Columns can be fixed on the left
  • Built-in support for row menus
  • Built-in support for a checkbox column
  • Supports row drilldown
  • Supports large datasets with paging controls
  • Built-in support for column threshold value highlighting
  • Custom column sorting functions can be specified
  • Custom column display functions can be specified
  • Extensible widget menu options

What is the BigNumbers Widget

The BigNumbers Widget is used in conjunction with the DataGrid widget, and acts as a filter on the tabular data displayed in the DataGrid.

What we will be doing in this tutorial

In this tutorial we will describe how the SkewedSessions portlet displays skewed sessions data using the DataGrid and BigNumbers widgets.  We will start from the tags in jsp pages, then to the view controller and finally all the necessary back end support components. 

Note; This tutorial assumes that the portlet has been modified to support horizontal resizing. See instructions at Upgrading a Viewpoint 14.0 Portlet to 14.01

Widget tags in the JSP page

The first thing to note is the DIV tags in the summary-content.jsp page where we want the widgets to appear.  It is important that the DIV tag's IDs  match what is supplied in the widget tags.

SkewedSessions\web\WEB-INF\portlet-jsp\summary-content.jsp:

...
<div id="bigNumbersdataGridDiv${context}" ></div>
<div id="dataGridDiv${context}" ></div>
...

Next, note the DataGrid Widget JSP tag in the summary-content.jsp page in the SkewedSessions Portlet.  Few important attributes are listed below. See viewpoint-core.tld for a full listing.

  • context (required): the unique context id of the portlet.
  • url (required): the target url for the ajax request to fetch data.
  • widgetdomId (required): the id of the containing div for the DataGrid widget.
  • className (optional): CSS class to be applied to the element containing the widget
  • controllerName (optional): Javascript controller class to be used in place of the default
  • requestFunction (optional): The name of the function that will request data from the server
  • responseFunction (optional): The name of the function that will handle responses from the server
  • width / minWidth (optional): the width of the DataGrid in pixels
  • height / minHeight / maxHeight (optional): the height of the DataGrid in pixels
  • fixedColumns (optional): The number of fixed columns in the widget
  • clientSort (optional): Indicates whether or not to perform sorting on the client side. Defaults to true
  • bigNumbersWidth / bigNumbersWidthMin / bigNumbersWidthMax (optional): The width of the BigNumbers widget in pixels
  • bigNumbersCount (optional): The count of the BigNumbers bubbles
  • pageSize (optional): the size of each page displayed by the DataGrid.

SkewedSessions\web\WEB-INF\portlet-jsp\summary-content.jsp:

<vp:dataGrid context="${context}"
url="/SkewedSessionsPortlet/dataserver/getSkewedSessionsReportData"
widgetDomId="dataGridDiv${context}"
bigNumbersWidthMin="86"
bigNumbersWidthMax="100"
requestFunction="requestFunction${context}"
pageSize="250"
clientSort="false" />

The View Controller

Next, we look at the controller which will pass the necessary data from the back end service layer to the DataGrid Widget we added in summary-content.jsp: SkewedSessionsViewController.

This controller has a method called getSkewedSessionsReportData(), which is called by DataGrid widget tag in the summary page.  This method will construct the TableState object used by the DataGrid to generate the view, and also retrieve the data we want displayed from the back end service layer. The TableState object is a representation of the state of the DataGrid Widget.  It incorporates both the configuration settings of the widget, and any data that has been retrieved so far. 

The Controller is responsible for maintaining the TableState object, both configuration (what page it is on, what columns are sorted, etc.), and   data, and returning this to the View. The DataGrid and BigNumbers widget will then render this data.

The DataGrid widget can send three type of requests to the Controller:

  • Data Request
  • BigNumbers Update Request
  • Preference Update Request

SkewedSessions\src\java\com\teradata\portlets\skewedsessions\controllers\SkewedSessionsViewController.java

...
public void getSkewedSessionsReportData(ControllerContext context) throws JSONException
{
SkewedSessionsPreferences prefs = (SkewedSessionsPreferences) ctx.loadPreferences(
SKEWED_SESSIONS, new SkewedSessionsPreferences());

String stateParam = ctx.getParameter(TableState.PARAM_NAME);
TableState state = null;
if (stateParam != null)
{
state = TableState.newInstanceFromJson(stateParam);
}

if (isBigNumbersUpdate(ctx))
{
updateBigNumbersPreferences(ctx, prefs);
}

if (isPreferenceUpdate(state))
{
handleTableStatePreferenceUpdate(ctx, state);
}

if (isDataRequest(ctx, state))
{
executeReport(ctx, state);
}
}

Data Request

A data request is a request to retrieve new or additional data.  This typically happens when the user navigates to the page, or the portlet auto refreshes, or the user applies a new filter or sort.  

When a data request is received, the Controller merges the current TableState with the previous saved state to preserve the settings.  It then fetches the data and places it into the merged TableState object to be passed back to the View. 

SkewedSessions\src\java\com\teradata\portlets\skewedsessions\controllers\SkewedSessionsViewController.java

/**
* Execute the updated report and update state
* @param context the portlet context
* @param newState the table state
*/
private TableState executeReport (ControllerContext context, TableState newState)
{
// merge passed state into prefs' state
SkewedSessionsPreferences prefs = (SkewedSessionsPreferences) context.loadPreferences(
SKEWED_SESSIONS_PORTLET, new SkewedSessionsPreferences());
TableState mergedState = prefs.getTableState();
if (newState != null)
{
mergedState.mergeDataRequest(newState);
}
...
// create BigNumbers to populate with data
BigNumbers<SkewedSessionsFilter> bigNumbers = new BigNumbers<SkewedSessionsFilter>();
// execute report
QueryResults<Session> dataGridResults = skewedSessionsManager
.getCurrentSkewedSessions(prefs, bigNumbers, mergedState, context.getLocale());
mergedState.setResults(dataGridResults);
mergedState.setStatus(STATUS_OK);
...
// serialize to JSON, add to view
JSONBuilder builder = new JSONBuilder();
tableStateJSONBuilder.toJSON(builder, mergedState, context.getLocale());
builder.addArray(BIG_NUMBERS_RESPONSE, bigNumbers.getFilters().toArray());
context.addViewObject(TABLE_STATE, builder.toString());
}

BigNumbers Update Request

A BigNumbers update request is a request to retrieve filtered data.  This happens when the user clicks on a BigNumbers filter.  The widget sends an HTTP request to the server with the "bigNumbersKey" parameter populated with the selected filter.

SkewedSessions\src\java\com\teradata\portlets\skewedsessions\controllers\SkewedSessionsViewController.java

    private static boolean isBigNumbersUpdate(ControllerContext ctx)
{
return !StringUtil.hasNullEmptyOrBlank(ctx.getParameter(BIG_NUMBERS_KEY));
}
....
/**
* Update BigNumbers filters in preferences
*
* @param ctx the portlet context
* @param preferences the portlet preferences
*/
private void updateBigNumbersPreferences(ControllerContext ctx,
SkewedSessionsPreferences preferences)
{
String bigNumbersKeyString = ctx.getParameter(BIG_NUMBERS_KEY);
SkewedSessionsFilter filter = SkewedSessionsFilter.valueOf(bigNumbersKeyString);

// update preference if new big numbers filter is different from existing filter
if (isNotEmpty(bigNumbersKeyString) && !filter.equals(preferences.getFilter()))
{
preferences.setFilter(filter);
ctx.savePreferences(SKEWED_SESSIONS_PORTLET, preferences);
}
}

Preference Update Request

A preference update request is one that persists the current configuration settings of the widget.  An example of such a request would be: resizing the column width, reordering columns in a report, etc..   The widget will send an HTTP request to the server with the "preferenceUpdate" flag set to true when this type of request is triggered.

SkewedSessions\src\java\com\teradata\portlets\skewedsessions\controllers\SkewedSessionsViewController.java

/**
* Returns true if the tableState request is for preference update
*
* @param state the table state
* @return true if preference update, false otherwise
*/
private static boolean isPreferenceUpdate(TableState state)
{
return state != null && state.isPreferenceUpdate();
}
...
/**
* Merge TableState updates into TableState in preferences
*
* @param ctx the portlet context
* @param newState the new TableState
*/
private void handleTableStatePreferenceUpdate(ControllerContext ctx, TableState newState)
{
SkewedSessionsPreferences prefs = (SkewedSessionsPreferences) ctx.loadPreferences(
SKEWED_SESSIONS_PORTLET, new SkewedSessionsPreferences());
TableState savedState;
savedState = prefs.getTableState();

savedState.mergePreferenceRequest(newState);
ctx.savePreferences(SKEWED_SESSIONS_PORTLET, prefs);
ctx.setViewName(VIEW_EMPTY);
}

Wiring up the Controller

Add the Controller to the portlet applicationContext.

SkewedSessions\web\WEB-INF\applicationContext.xml

...
<bean id="skewedSessionsViewController" class="com.teradata.portlets.skewedsessions.controllers.SkewedSessionsViewController">
<description>Controller for summary and data requests</description>
<property name="skewedSessionsManager" ref="skewedSessionsManager"/>
<property name="messageSource" ref="messageSource" />
<property name="tableStateJSONBuilder" ref="tableStateJSONBuilder" />
</bean>

<bean id="tableStateJSONBuilder" class="com.teradata.portlets.json.datatable.TableStateJSONBuilder">
<constructor-arg index="0" ref="skewedSessionsColumnDefinitionClass" />
<constructor-arg index="1" ref="messageSource" />
</bean>

<bean id="skewedSessionsColumnDefinitionClass" class="java.lang.Class" factory-method="forName">
<constructor-arg index="0" value="com.teradata.portlets.skewedsessions.enums.SkewedSessionsColumn" />
</bean>
...

We also need to map the request url to the SkewedSessionsViewController.

SkewedSessions\web\WEB-INF\dataserver-servlet.xml

<bean id="handlerMapping" class="org.springframework.web.servlet.handler.SimpleUrlHandlerMapping">
<property name="mappings">
<props>
<prop key="/summary">skewedSessionsViewController</prop>
<prop key="/getSkewedSessionsReportData">skewedSessionsViewController</prop>
...
</props>
</property>
</bean>

View that displays the JSON data need by the widget

The TableState object we created that contains the DataGrid settings and results need to be transformed into JSON so the widget can consume it.  We do this by calling the TableStateJSONBuilder, and then pass the generated JSON data to a simple jsp page for output and widget consumption.

SkewedSessions\web\WEB-INF\portlet-jsp\refreshSkewedSessions.jsp

<%@ page contentType="text/html" language="java"%>
...
${tableState}

Back End Components

In order to support the DataGrid widget, there are several back end components that need to be created.

Implementing the TableColumnDefinition Interface

The TableColumnDefinition interface defines the methods that provide the necessary metadata for each of the columns in the DataGrid.  We will need to implement this interface and specify the column names and column types used in our DataGrid example.  Note, the columnName in the TableColumnDefinition needs to match the member name in the Model class (defined in the next section).

Here is a list of the standard column data types currently supported by the DataGrid widget:

Type Java Class
String java.lang.String
Long java.lang.Long
Integer java.lang.Integer
Float java.lang.Float
Number java.lang.Integer
Timestamp java.sql.Timestamp
Duration java.lang.Integer
Checkbox java.lang.Boolean
Icon java.lang.String
Custom any

Our example DataGrid has 5 columns:

  • Session ID - type Integer
  • Username - type String
  • CPU - type Float
  • CPU Skew - type Float
  • Hot AMP - type Integer.

There are other column settings that can be set, such as turning on/off column filtering, hiding columns from the menu, and adding tool tips, etc..

SkewedSessions\src\java\com\teradata\portlets\skewedsessions\model\SkewedSessionsTableWidget.java

public enum SkewedSessionsColumn implements TableColumnDefinition
{
SESSION_NO("sessionNo", TableColumnState.Type.NUMBER, true, false, null, "SESSION ID", "SESSION ID"),
USER_NAME("userName", TableColumnState.Type.STRING, true, false, null, "USER NAME", "USER NAME"),
REQUEST_AMP_CPU("requestAmpCPU", TableColumnState.Type.FLOAT, true, false, null, "CPU", "CPU"),
CPU_SKEW("cpuSkew", TableColumnState.Type.FLOAT, true, false, null, "CPU SKEW", "CPU SKEW"),
HOT_AMP("hotAmp1CPUId", TableColumnState.Type.FLOAT, true, false, null, "HOT AMP", "HOT AMP");
....

private SkewedSessionsColumn(String columnName, TableColumnState.Type type, boolean filterable,
boolean hiddenFromMenu, Integer defaultWidth, String header,
String menuToolTip)
{
this.columnName = columnName;
this.type = type;
this.filterable = filterable;
this.hiddenFromMenu = hiddenFromMenu;
this.defaultWidth = defaultWidth;
this.header = header;
this.menuToolTip = menuToolTip;
}
...

public String getHeader(MessageSource messageSource, Locale locale)
{
return messageSource.getMessage(getName(), null, locale);
}
}

Localization of header names is done by using the portlet resource bundle

SkewedSessions\src\resources\messages.properties

#data grid column headers
SESSION_NO=Session ID
USER_NAME=User Name
REQUEST_AMP_CPU=Request AMP CPU
CPU_SKEW=CPU Skew
HOT_AMP=Hot AMP

Adding preference items to support the DataGrid preferences

In order for the DataGrid to retain its settings after a user has logged out, the settings need to be saved in the portlet's preferences.  To do this, we will need to add the TableState object to the portlet's existing PreferencesModel with a PreferenceReference annotation.  You can add any number of objects, including different objects for normal view versus maximized view of a portlet if desired.

SkewedSessions\src\java\com\teradata\portlets\skewedsessions\model\SkewedSessionsPreferences.java

public class SkewedSessionsPreferences extends PreferencesModel
{
// the DataGrid configuration
@PreferenceReference
private TableState tableState;

// the BigNumbers filter
@Preference
private SkewedSessionsFilter filter;

// constructor
public SkewedSessionsPreferences()
{
// set default values
...
tableState = initDefaultColumns(DEFAULT_SKEWED_SESSION_COLUMNS);
filter = DEFAULT_FILTER;
}

// initialize TableState from preferences
private TableState initDefaultColumns(SkewedSessionsColumn[] defaultColumns)
{
List defaultColumnsList = Arrays.asList(defaultColumns);

TableState tableState = new TableState();
for (SkewedSessionsColumn column : SkewedSessionsColumn.values())
{
TableColumnState columnState = new TableColumnState();
columnState.setName(column.name());
columnState.setColumnName(column.getColumnName());
columnState.setHidden(!defaultColumnsList.contains(column));
if (column == DEFAULT_SKEWED_SESSION_SORT_COLUMN)
{
columnState.setSortOrder(1);
columnState.setSortAscending(true);
}
tableState.addColumn(columnState);
}

return tableState;
}

...

Retrieving the data to be displayed in our DataGrid

The SkewedSessionsPortlet gets a list of skewed sessions for a selected system. The details of the query are not relevant to this discussion, but are available in the SkewedSessionsManagerImpl for reference. The DataGrid widget expects its results to be of type QueryResults. The Controller gets QueryResults from the Manager, and passes it to the TableState object, which is then serialized for consumption by the widget. this way, we are able to maintain separation between the Model, View and Controller in this application.

SkewedSessions\src\java\com\teradata\portlets\skewedsessions\service\SkewedSessionsManager.java

...
/**
* Gets a list of active skewed sessions running on the specified system. Each list item is a
* session model.
*
* @param preferences The users portlet preferences for this portlet instance.
* @param bigNumbers The session counts for big number widget (to be set)
* @param tableState The current TableState
* @param locale The users current locale
* @return
*/
public QueryResults<Session> getCurrentSkewedSessions(SkewedSessionsPreferences preferences,
BigNumbers<SkewedSessionsFilter> bigNumbers, TableState tableState, Locale locale);
...