Creating a Data Table

Data Tables perform a function in the BI Server that is similar to a SQL database table. They are populated with data from Data Flows and the user can select a column or a combination of columns to set as a Primary Key on the Data Table. They can also have triggers to set policies for data refreshes and updates.

To add a data table:

  1. Open the Workbench, then expand Analytics, then BI Server, then Data Models (and, optionally, a Data Model folder), then select Add Data Table, as shown below.

    BI Server Data Models Node

    You can also select a Data Model or Data Model folder then select Add Data Table, as shown below, in the Edit section of the Home ribbon in the Workbench.

    BI Server Add Data Model

  2. This opens the New Data Table Properties window as shown below.

    BI Server New Data Table Form

  3. Enter a name in the Data Table Name field.

How to Configure a Data Source for the Data Table

To fully configure a Data Table, it is necessary to specify the table’s data source. Any dataset exposed in GENESIS can be used as a Data Table’s data source, however it is recommended to use Data Flows as they provide the highest flexibility in data ingestion. The data source can either be typed in manually, browsed by selecting the tag icon, or dropped from the Workbench Data Browser panel. Once a selection is made, the Data Table Schema will list information about the table columns.

BI Server Data Table Properties

Once a data source has been specified, it is queried for its schema, and the returned metadata is used to fill the Data Table Schema panel. The schema is only retrieved the first time the data source is queried, which means that changes to the underlying data source will not be reflected in the Data Table Schema automatically. To refresh the Data Table Schema, select Refresh Schema in the header area.

If the underlying data source accepts parameters (for example a Database Connector data source), the values of the parameters need to be specified in the Data Source field and cannot be bound to any type of parameter from this form.

To update the Runtime Status of the Data Table and to load the preview, apply the changes to the form as this will make the BI Server runtime pick up the Data Table and load its data in memory.

Once the Data Table Schema has been loaded, it is a good practice to define a Primary Key, similar in concept to the SQL database keys, in order to identify each Data Table record uniquely. This practice can improve BI Server performance and it safeguards data integrity when a Data Table gets refreshed with newer data.

Loading the Data Table with Data

The points under Runtime Status will briefly show a bad quality value while the BI Server runtime creates the Data Table and starts loading the data, and then they will update to reflect the current table’s status. In this example we are loading the table with data coming from a Data Flow ingesting real-time data sources.

BI Server Data Table Runtime Preview

The following list explains all the possible statuses that a Data Table can be including, in parenthesis, the associated numeric code:

  • Not Saved (N/A): The Data Table has just been created and has not been saved yet.
  • Offline (0): The Data Table’s parent model is currently in the offline state.
  • Initialized (1): The Data Table has been saved and the BI Server runtime has picked up the new entity and scheduled the process to load the Data Table’s data.
  • Loading (2): The data for the Data Table is currently being loaded by the BI Server runtime.
  • Online (3): The BI Server runtime has completed loading the data and the Data Table is ready to be queried by clients.
  • Error (4): The BI Server runtime has encountered an error while loading data into the Data Table. Data may be partially loaded or not loaded at all; TraceWorX can be used to diagnose the issues encountered while loading the data.

The Last Updated field under Runtime Status displays the last time data was added to the Data Table.

The Row Count reports how many rows of data are currently loaded in the Data Table.

The Drop and Reload Table Data hyperlink in the Runtime Status panel header allows to signal the server that it should drop the Data Table’s data and reload it from the data source. Clicking the link prompts a confirmation dialog to make sure the user wants to proceed with the operation.

BI Server Reload Data Table Data

Dropping and reloading the data causes the data source to be queried again and data to be reloaded and re-compressed by the BI Server runtime, which is potentially a computationally expensive operation. Only use this functionality when necessary.
You may find that the Data Table Schema has been loaded by the form, but no preview data is currently available, and the runtime status of the Data Table shows “Not Saved” for the current table. This could be due to several reasons:
  • The Data Table has just been created and has not been saved yet.
  • The BI Server service is not running
  • The Data Model is offline

Remedying these items will resolve the issue and allow the preview to show data.