Configure database views

Database Views allow designers to create physical views derived from a model. These views are designed in this model and deployed in the hub schema, where they can be consumed:

  • By external applications for downstream analysis and access to the data.

  • By Dashboard Builder queries.

Database view definition

A Database View is a configuration object that is defined with:

  • A base entity.

  • A view type that defines the scope of data returned by the physical view.

  • An optional SemQL condition to filter the content of the view.

  • A physical name that will be used to access the view.

  • A list of columns configured with:

    • An alias.

    • A SemQL expression that defines the value.

Create a database view

To create a Database View:

  1. Right-click the Database Views node and select Add Database View. The Create New Database View wizard opens.

  2. In the Create New Database View wizard, enter the following values:

    • Name: Internal name of the object.

    • Label: Business-friendly name of the object.

    • Entity: Select the entity at the root of the database view.

    • View Type: Select the type of data to access (see View Types).

      Not all view types are available for database views. For example, views bound to loads and batches are not listed.

    • Physical Name: Name of the generated physical view.

  3. Click Next. In the second wizard step, provide the Filter condition applied to retrieve the data.

  4. Click Next. In the third wizard step, select the columns amongst the list of attributes from the entity you selected at the first step.

  5. Click Finish to close the wizard. The Database View editor opens.

  6. Optionally add or customize Columns.

  7. Enter an optional Description.

  8. Press Control+S (or Command+S on macOS) to save the editor.

Create a database view from another model object

Alternatively, Database Views can be created:

  • From a Named Query: the Database View will be initialized with the SemQL filter and Object Properties of the Named Query.

  • From a Form: each field in the form will convert to a view column.

  • From a Collection: each column in the collection will convert to a view column.

Variables are not supported in SemQL expressions used for filters and columns within Database Views. Any filter or column having variables in its SemQL expression will be ignored when the Database View is deployed.

Create a database view from a named query

To create a Database View based on a Named Query:

  1. Right-click the Named Query you want to use under the Named Queries node of the Model Design View, and then select Create Database View.

  2. The Database View editor opens, pre-filled with:

    • Name: Initialized with the name of the selected Named Query.

    • Label: Initialized with the label of the selected Named Query.

    • Entity: Populated with the root entity of the Named Query.

    • View Type: Initialized with Golden Data (see View Types).

    • Filter: Initialized with the filter of the selected Named Query.

    • Columns: Initialized with all Object Properties from the Named Query.

  3. Modify any property as required.

  4. Enter an optional Description.

  5. Press Control+S (or Command+S on macOS) to save the editor.

Create a database view from a form

To create a Database View based on a Form:

  1. Right-click the Form you want to use in the Model Design View, and then select Create Database View.

  2. The Database View editor opens, pre-filled with:

    • Name: Initialized with the name of the selected Form.

    • Label: Initialized with the label of the selected Form.

    • Entity: Populated with the entity of the Form.

    • View Type: Initialized with Golden Data (see View Types).

    • Columns: Initialized with all Form fields.

  3. Modify any property as required.

  4. Enter an optional SemQL Filter.

  5. Enter an optional Description.

  6. Press Control+S (or Command+S on macOS) to save the editor.

Create a database view from a collection

To create a Database View based on a Collection:

  1. Right-click the Collection you want to use in the Model Design View, and then select Create Database View.

  2. The Database View editor opens, pre-filled with:

    • Name: Initialized with the name of the selected Collection.

    • Label: Initialized with the label of the selected Collection.

    • Entity: Populated with the entity of the Collection.

    • View Type: Initialized with Golden Data (see View Types).

    • Columns: Initialized with all Collection columns:

  3. Modify any property as required.

  4. Enter an optional SemQL Filter.

  5. Enter an optional Description.

  6. Press Control+S (or Command+S on macOS) to save the editor.

Deploy database views

When a model edition is deployed, a physical view named DBV_<physical view name> is generated in the schema location for each Database View configured in the model. This view is composed of the list of columns defined on the corresponding Database View and returns data as per the configured entity, view type, and SemQL filter.

Deploying on Oracle requires to grant view creation privileges to the database user configured for the Data Location’s datasource.

GRANT CREATE VIEW TO <user>