Data View DataObjects

A Data View DataObject represents a database "View" (which can be considered as a stored query) defined in the Ledger database. A Data View Data Object (hereafter DV) is more powerful then a Document Definition DataObject (described here) as it provides in a more flexible way to fetch data from the ledger database that can be used in reports.

Typically an administrator would prefer a Data View Object above a Document Definition DataObject when he or she want to create a report with information about reference fields and/or combinations of ledger database tables.

Creation / deletion of a DataView

An administrator is able to create a DataView by using the Data View menu item in the setup section. By clicking on the menu item the DataView overview page will show up as illustrated below.

By clicking on the use New button a new DataView wil be constructed after specifying a name and a label and pressing the Ok button a new DataView will be created. Clicking on the Delete button will remove the DataView from the application and archives the database view.

Defining a DataView query

When selecting a DataView in the DataView overview page and clicking on the Edit button; the DataView editor page will open. On this page the query that serves as the datasource of an DataView can be defined. A DataView can be defined by either using the 'Entities' or the 'SQL' tab. Each tab consists out of a editor section and a preview section. The latter will be described in detail in the Preview section paragraph where the specific editor areas will be described separately.

Define a DataView query by making use of the entities tab

The entities tab (illustrated below) follows the Document Definition (hereafter; DD) approach.

The left pane represents the source area where the ingredients for the DataView query can be selected by using the structure of a specific DD. The right pane represents the selected ingredients of the query which are the fields.

A DD can must be seen as starting point for the query. In the left pane a DD can be selected by making use of the dropdown in the top of the pane. Only DDs that are enabled for analytics are available in the dropdown (see document definition article). When selecting a DD (for instance Order as shown in the illustration) the treeview will be filled with its first level fields and lines. Lines and reference fields can be expanded so that recursively fields of a lower level can be selected. Each field can be dragged to the Selected fields pane.

When a field is dropped or removed (by using the bucket icon) from the 'Selected fields' pane a SQL query will be generated in the background. The exact completion of the query can be found in the SQL tab, which gives a live presentation of the active query.

Define a DataView query by making use of the SQL tab

In the SQL tab an administrator is able to define a DataView query manually.

Defining a SQL manually provides in great flexibility. The full expressive power of the SQL language can be used to read information from the Ledger database. Please keep in mind that any attempts to modify data in the database (for instance by using an update / insert statement) will have no effect. By using the SQL tab an already defined query can be modified. For instance: the name of the table in the DataView. It might be fruitfull to start defining a SQL query by making use of the Entities tab and customize the query when necessary.
Please pay attention to the fact that a manual defined SQL query will override a generated SQL query (if present). The administrator will be notified when such a activity will occur.

Please note that if you would like to switch between the "Actual" and "History" tables, you can do so in the SQL query by making use of the "type" property. For example: Inside a from clause you can write FROM <%=tableName({ documentDefinitionName: 'ddName', type: 'history' })%> or FROM <%=tableName({ documentDefinitionName: 'ddName', type: 'actual' })%>.

Preview section

The preview section in the bottom of the DataView editor screen gives a preview of the output of the DataView query up till 20 records.

Saving of the DataView is not necessary for displaying preview data as the DataView query will be directly executed on the Ledger database. The name of the columns reflect only the last bit of the fieldnames in case of the 'Entities' tab. In case of the 'SQL' tab the columns represent the full columns aliases as specified by the SQL query. We chose to apply a different approach in respect to both tabs because a prefix like 'A0' would confuse the administrator in case of the 'Entities' tab. When the DataView SQL query can not be executed because it is malformed, an error notification will be displayed to the user. The contents of the error is an detailed error thrown by the underlying database.
We chose to not parse the detailed error in a more user friendly message as it could lead to loss of detailed information.

Application

DataView Objects can be used as datasource for analytics reports. An administrator is able to choose a DataView Object when designing a report in the Report designer. A DataView Object can be found in the Report designer under the DataView section. The name of a DataView object consists out of a prefix : 'DV' followed by the name of the DataView.

Obviously; any changes made to an existing DataView that is being used in relation to a report could harm execution of the report. Think for instance what would happen when removing a fieldname from a DataView that is being used in a report. In the aforementioned case the field in the report will be transfered to a static text. This is handled quite gracefully but there is no cure for deletion of a DataView