TableView: a uniform data viewer

For navigating and searching large datasets in a tabular view.

TableView (​modules/tableview) is a reusable table viewer component for the MOLGENIS research portal, in particular tailored to biobank projects such as LifeLines, AnimalDb, TIFN, and PANACEA. Core function is to uniformly navigate and search (large) data lists up to millions of records. This viewer gets its metadata and data loaded via an abstract backend serving the data, i.e., it should be unaware of how data is stored in the backend as 'entities' or 'protocols' or 'binary files' or Excel etc. so we can reuse it for different phenotype, molecular and genetic datasets.

Core concepts

• Column metadata: description of column name, dataType, label and description (optional). For example: {name=patientID, dataType=int, label='patient id', description='unique pseudonym for this individual'}
• Row data: list of rows of the form {row1={col1=value, col2=value, col3=value}, row2=...} where name matches a column name. Typically also metadata such as 'totalCount' is included
• Viewer state metadata: definition of the current state of the viewer. For example: 'data source', 'columns shown and their properties', 'filters', 'sorting', 'limit/offset/page'.
• Data provider: source of the data. For example 'entity=Measurement' or 'protocol=Demographic questionaire'.
• rowid: it is expected that for selection/action purposes we want to work with rowids. These may be simple number but also can be the ids used in the backend. The viewer can work with either as long as they are unique.

Architectural overview

Objective of the TableView is to seperate Model, View (client), Controller (server). The core idea is that the Controller can have multiple implementations to produce Model from different data sources.

Components:

1. molgenis 'widget' to pre-configure the initial MODEL state of your TableView.
2. jQuery table VIEW + client side CONTROLLER
3. AJAX server-side CONTROLLER interface (=MolgenisService?)
   • provider for MODEL of available columns/column groups (metadata).
   • provider for MODEL of table rows based on filter / paging (data).
4. Multiple implementation of (3) interface:
   • 'Entity' based backend that uses entity.getFields to provide column metadata and entity instances for row data. This can build on the Database interface.
   • 'Protocol' based backend that uses Measurement for columns and ProtocolAppplications? for rows. This can build on the Matrix interface.
   • 'File' based backends that uses binary/csv/excel/plink/?.. files.
   • Custom query based backends, for example to serve complicated 'group by' queries.

It is expected that

• the MODEL will be in json format.
• there will be a java class structure matching this MODEL for the provider services to use in the server
• that if we want to support multiple jqGrid or DataTables? that this should not affect above architecture (just a json reformatting may be needed on client preferably).

TableView Functional requirements

General goal is to keep the visual overhead of the tableview to a minimum (so minimum number of buttons in default view) but put many little actions in the viewer for users to activiate and enhance their view.

Rows

• view data in rows and columns (tv1)
  • As tableview user I can view my data using familiar row/column structure. This emphasis on tabular visualisation is based on the experience that most users do NOT understand a matrix with separate row headers, but are very comfortable with having 'patient ids' in the first column.
• page through long lists of rows (tv2)
  • As tableview user I can easily page through long lists of rows. Ideally this is implemented as an 'endless vertical scroller' that reloads rows via AJAX. If not possible, this can also be done via a pager (prev/next), depending on what is available in the VIEW component.
• sort rows using one or more columns (tv3)
  • As tableview user I can sort per column, optionally combining multiple columns. Sorting happens in server-side controller. Optimally, it should be possible to apply multiple sortings.
• filter rows using google-like search (tv4)
  • As tableview user I can type into a tablewide 'search' box. When typing, the view is renewed returning rows matching any column based on a 'like' query (for text strings) an '=' for numbers/dates. Multiple terms are treated as 'or'. Use quotes for matching a sentence incl spaces.
• filter rows using quickfilters per column (tv5)
  • As tableview user I can hover over columns to see a 'per-column' search box appear to filter on values in one column. When typing, rows are filtered on using only this column applying 'like' matches (for text) or perfect matches (for other data). If multiple terms are entered this is treated as 'or' unless quoted. Optionally, an operator can be given to say 'greater than' and a is provided for adding multiple rules. The search criteria entered stay in view but can be removed using button.

Columns

• browse available columns and add/remove them to view (tv6)
  • As tableview user I can list all columns available (which can be millions!!!), search them and select them for view. This would work by unhiding a div/popup listing all available columns in another tableview, incl search to find 'my' column in a long list and select boxes to choose columns. Note: if column groups (future) are available you can add all these fields in one sweep.
• page through longs lists of columns (tv7)
  • As tableview I can easily browse in horizontal direction if many columns are available (10s - 1000000s). Ideally this would work via a horizontal scroller but can be implemented by simply adding a 'next/prev columns' button to first/last column headers.
• reorder columns (tv8)
  • As tableview user I can reorder my columns. Ideally this would work via drag and drop on the columns themselves or alternatively using some dialog listing the visible columns. Discussion: should be disabled on many columns???

State

• reset tableview (tv9)
  • As tableview user I want to reset my view to the initial state. This means that the initial settings should be stored as 'fixed payload' within the tableview (for example: data source).
• multiple tableview instances can work next to each other(tv10)
  • As tableview user I can have multiple tableview instances in view. I.e. each tableview should have a unique id. Test: happens if multiple browser tabs with the same tableview are opened?
• state robust when switching to other html pages (tv11)
  • As tableview user I can switch to another page and then come back without losing state (just as in statefull app). Implementation note: Ideally ALL table view state is managed in client for efficiency (so people can mashup their tables). However, viewer should be robust against users switching between pages (using browser session store?).
• save current state of table view to server (tv12)
  • As tableview user I can save my current view for next time, or give it a name to restore it at a later date. This means the MODEL of current configuration should be stored on server.

Extension points

Extension points allow developers to customize the table and/or to add action buttons for users to work with elements in the table.

• change what features are enabled (tv13)
  • As tableview developer I can pre-configure FIXED and VARIABLE settings via Java widget. For example, a FIXED setting may be a (hidden) predefined filter, a VARIABLE setting may be columns visible.
• change formatting of values in one column using javascript (tv14)
  • As tableview developer I can configure a javascript function (strategy pattern) that changes how values are rendered. For example, I might want to enhance a column with an hyperlink, or to provide a tooltip effect, or have a list of action buttons added to this column. This means also adding a *.js file to your project.
• change formatting of values in one column using server-side java (tv15)
  • As tableview developer I can configure the widget to use a java class (strategy design pattern) to change how values are served by the AJAX server-side controller. Implementation note: I just provide a java class name in my widget configuration.
• editable formatting (tv16)
  • As tableview user I can edit and save my values. We assume this can be implemented with user stories t14/15 but the jQuery component of choice may be able to provide this (for all MOLGENIS data types).
• native selection feature (tv17)
  • As tableview user I can select rows for actions. Obviously we can use tv14 or tv15 to create tick boxes for selection. However, the view may have a native mechanism to
• descriptions of the columns (tv18)
  • As tableview user I can inspect metadata of each columns. This should be available as popup. This includes descriptions, but can also hold other properties (analogous to the popup in xqtl workbench)

N.B. this is all we need to enable, for example, 'download selected'. Because you can reformat one column to have the tick boxes you need, add a form+button around, et voila.

2D matrix: row-oriented features in addition to column-oriented features

For homogeneous data viewing, such as genotypes, gene expressions, etc, there is a desire to have both column AND row headers and than have actions on both axis. In these cases some 'column' oriented user stories should also be available considering row headers:

• sort columns using one or more rows (tv3-horizontal)
  • As tableview user I can sort per row, optionally combining multiple rows (=tv3 horizontally instead of vertically).
• filter columns using (quick)filters per row (tv5-horizontal)
  • As tablview user I can filter columns by row (=tv5 horizontally instead of vertically)
• browse available rows and add/remove them to view (tv6-horizontal)
  • As tableview user I can list all rows available, search them and select them for view (=tv6 horizontally instead of vertically)
• reorder columns (tv8-horizontal)
  • As tableview user I can reorder my rows. (=tv8 horizontally instead of vertically)

Future enhancements

The following extensions to above are desired:

• column groups
  • As tableview developer I can group column headers to show what goes together, for example to show result of a table join. Particular tricky will be how this interacts (a) with 'add column' and (b) with column reordering.
• use the tableview in server sessioned application
  • As tableview developer I can copy all state changes to session. Motivation is that some applications may have dependent plugins that they want to bind via the server (instead of javascript). In these cases, 'metadata' should be automatically server copied. Expected implementation: via custom server-side controller.
• change the column selection dialog
  • As tableview developer I can to choose to change the add columns dialog with a treeviewer for example.
• filter rows using combined filters
  • As tableview user I can define complex filters that combine multiple filters over multiple columns together using AND or OR. Important: simple queries should NOT require use of this mechanism as users generally don't want.
• show joined data with 1:many relations
  • As tableview user I can browse related data using a tree view presentation where master/detail are visualized. Discussion needed on how this should look like. For now we hold dogma that this should be solved by the data provider service.
• group by / aggregate query conditions
  • For example: give me the most recent protocolApplications per patient. In semi-sql: select * from table where date = max(date) group by patientId.

FAQ / Discussion

Q: What about repeating values?

A: This is up to the backend. Either you can have multiple values per cell, or you can decide to repeat the whole row, having null values in those columns that do not have this many repeats. The TableView does not need to know.

Q: What about protocols that have been applied multiple times.

A: protocols would typically translate to one tableview with each row showing a protocolApplication. Hence, multiple protocolApplications would translate into multiple rows (unless filtered). Again, the TableView does not need to know. A: protocols would typically translate to one tableview with each row showing a protocolApplication. Hence, multiple protocolApplications would translate into multiple rows (unless filtered)