Tables
The Tables sub-node in the RDM Logical Model node allows creating tables of reference data (dictionaries), defining their structure, validations, and various other functions.
After creating a table in the RDM Logical model and deploying the configuration to the web application, the administrator needs to set the permissions for the table, so that they can view it, edit it, and perform other operations like batch import and export. |
Add a new table in the Model Explorer
-
Right-click Tables > New table.
-
Fill in the attributes in all tabs.
-
Select OK to save changes.
Add a new table in the Data Model Editor
-
Right-click RDM Logical Model > Edit schema. The canvas of the Data Model Editor opens, with the elements palette on the left.
-
Select Table on the palette and then click on the canvas - an empty table is created.
-
Double-click the new empty table.
-
Fill in the attributes in all tabs.
-
Select OK to save changes.
![Add a table in the Data Model Editor](_images/tables-add-a-table.png)
Table attributes
General
![Table definition - General tab](_images/tables-table-definition.png)
The General tab allows configuring a variety of general table attributes:
Name | Required | Description | ||
---|---|---|---|---|
Table name |
Y |
Technical name of the table used in the configuration process (parent keys setup, SQL validations, etc.) and written to the database. The maximum length of the name is determined by the database type. Database type is set in the App Variables node.
|
||
Table label |
Y |
Business name of the table as shown in the RDM web application. |
||
Table description |
N |
Short description of the table to be displayed in the RDM web application by selecting Description. |
||
Initial Load |
N |
If checked, initial load plans are generated for this table. See configuring-initial-loads-to-rdm-tables.adoc for more information. |
||
Initial load - history |
N |
If checked, initial data load plans with historical data are generated for this table. See configuring-initial-loads-to-rdm-tables.adoc for more information. |
||
Authentication Strategy for initial load plans |
Y |
Determines the authentication configuration for RDM steps in initial load plans. See App Variables for information about authentication strategies. |
||
Show in all tables |
N |
Determines whether the table is shown in the table summary accessible by double-clicking the Tables node in the web application. |
||
Expected amount of records |
N |
Approximate number of records in the initial source tables (for documentation purposes only). |
||
Expected amount of changes |
N |
Approximate number of records incoming into the system (for documentation purposes only). |
||
Row validation strategy |
Y |
Defines whether rows are validated automatically as soon as a record is edited ( The |
||
Business owners' roles |
N |
Roles that have editing rights for this table (use content assist to select roles) (for documentation purposes only). |
||
Business owners of additional attributes roles |
N |
Roles that have publishing rights for this table (use content assist to select roles) (for documentation purposes only). |
Columns
The Columns tab allows to add columns to a table. Each column has the following parameters:
Name | Required | Description | ||
---|---|---|---|---|
Name |
Y |
Technical name of the table used in the configuration process and written to the database. The maximum length of the name is determined by the database type. The database type is set in the App Variables node. |
||
Label |
N |
Business name of the column as shown in the web application. |
||
Domain |
Y |
Name of the column’s domain. See Domains for information about defining domains. |
||
Required |
N |
Determines whether the column must possess a value or can remain empty. |
||
Display mode |
Y |
Sets special display properties for the column in the web application, with the following options:
|
||
Generated |
N |
Determines whether the values for the column are automatically generated on the side of the database. If selected, the column value can be entered only when creating a record; the column is inactive when editing the record. In addition, if this column is entered on the [ID generators] tab, it is inactive even when creating a record. Generated columns cannot be part of a unique key collection because they are parsed as null before the import. |
||
Default value |
N |
Default value for the column, which is pre-filled when creating a new record in the web application.
The following variables can be used:
Note that data type mismatches result in a validation error. |
||
Value Presenter |
N |
Assigns one of previously defined value presenters to this column. See Value Presenters. |
||
Description |
N |
Free text describing the attribute; the description is visible upon hovering over the column name or in the table Description dialog. |
Validations
The Validations tab allows to define various instant and web-service-based validations for the current table. The tab has the following sections.
Unique keys
In this section you define unique keys.
A unique key is either one column whose values must be unique in each record or a set of columns whose value combinations should be unique across rows. You can create multiple unique keys.
Where multiple unique keys exist, the first unique key to be configured is considered the primary key, unless an alternative primary key is explicitly set. Generated columns cannot be part of a unique key collection because they are parsed as null before the import.
When creating or editing a record in the web application, records are tested against this condition when trying to save the record or selecting Validate, and if the validation is not passed, a warning or an error is displayed. Users cannot publish such records although records with warnings can be saved.
Each unique key has the following attributes:
Only one unique key can be selected as a primary key. |
Name | Required | Description |
---|---|---|
Name |
Y |
Name of the unique key. |
Primary Key |
N |
Determines whether the unique key is the primary key for the table. Setting a primary key has several uses:
|
Columns |
Y |
Select the columns composing the unique key to the Name column. |
Expression validations
This section lets you define expressions for validating input data using the Ataccama ONE Expressions syntax. See Commonly Used Functions for the most common ONE Desktop functions. You can define multiple expression validations for each table.
For example, the following expression checks whether the name
column contains only letters:
matches("[a-zA-Z]*",name)
When creating or editing a record in the web application, this validation is performed when trying to save the record or selecting Validate, and if the validation is not passed, a warning is displayed. The warning message appears next to the column that the user has specified.
If no column is specified, the validation message is displayed as an error for the whole record in the validations panel. Users cannot publish such records.
Each expression validation has the following parameters:
Name | Required | Description |
---|---|---|
Expression |
Y |
An expression validating input. |
Enable |
N/A |
Lets you easily enable and disable the validation without having to delete and rewrite the expression. |
Message |
N |
The message that is shown when the validation is violated. |
Column |
N |
Specifies which column receives a warning message when the validation is violated. |
SQL validations
This section allows defining complex validations that utilize SQL scripts for validations across columns, rows of the table for which it is defined, and other tables.
For example, the following SQL statement checks whether there is only one branch manager per city in the BRANCH
table.
select d.generatedPk, 'code' as name
from $BRANCH$ d
where d.CITY in (
select distinct(CITY)
from $BRANCH$ c
GROUP BY CITY
having count(distinct(BRANCH_MANAGER))>1
)
An SQL validation consists of the following parameters:
Field | Required | Description |
---|---|---|
SQL expression |
Y |
An SQL SELECT statement that validates a record being created or edited. See Query guidelines. |
Enable |
N/A |
Lets you easily enable and disable the validation without having to delete and rewrite the expression. |
Validation message |
Y |
A message shown when the validation is not passed. |
Other involved tables |
N |
If the SQL expression uses other tables in addition to the table to which it is applied, all these table must be listed here. |
Query guidelines
-
The SQL query should represent the violation of the condition which is tested, that is, if the result of the query is not null, the record has not passed the validation.
-
The query must always return the generatedPk attribute and a column which displays the validation messages in single quotes marked with the name alias.
select d.generatedPk, 'code' as name
-
Table names must be wrapped into dollar signs, for example, $TABLENAME$, and aliased after the SQL FROM clause.
When is validation performed?
Validation is performed on published and edited data of tables used in the validation. The SQL query is executed every time:
-
The record is saved on creation and editing.
-
The record is validated (you select Validate in the record detail dialog).
-
The record is published.
-
The RDM web application is restarted (that is, when a new configuration is deployed).
To run full validation on records, you can schedule revalidation that runs at the next application restart. For more information, see RDM Features, section Record Validation on Application or Configuration Restart.
Online validations
This section allows selecting previously defined online services for validating input. Online services are created from plans or components and are served by ONE Runtime Server.
Each online validation has the following parameters:
Name | Required | Description |
---|---|---|
Name |
Y |
Name of the online service. |
Enable |
N/A |
Lets you easily enable and disable the validation without having to delete and rewrite the expression. |
Location |
Y |
URL location of the service in the following format: |
Soap action |
Y |
Usually matches the name of the service and describes the SOAP action (services are typically of the SOAP type in ONE Desktop and RDM). |
Namespace |
Y |
Namespace of the service. |
Soap version |
Y |
SOAP version of the defined service (1.1 or 1.2). |
Online enrichers
This section allows selecting previously defined online services to enrich a record being created or edited with data. This feature is called via the Enrich option in the Create/Edit Detail dialog in the web application. Parameters configuring online enrichers are the same as for online validations.
ID Generators
The ID Generators tab allows assigning columns which will have automatically generated IDs; manual assigning or changing of such a column’s value is not possible. These columns also need to have the Generated option selected (see Columns above).
Business date columns
RDM supports versioning of records, that is, a possibility to create multiple versions of the same record with different validity periods.
The Business Date Columns allows specifying two columns that mark the beginning (Business date FROM field) and the end (Business date TO) of record validity. These columns must have a datetime-based domain. See Domains.
Make sure you have specified Infinity to and Infinity from parameters in App Variables. |
Data sorting
Here you can override RDM’s default ordering or records by ID and specify the columns that you want the data to be sorted by. When specifying multiple columns, the records are sorted in the order the columns are listed, similarly to SQL ORDER BY.
To sort records in the descending order in a column, select Descending next to the column results.
![Data sorting](_images/tables-data-sorting.png)
Table locking
Performing certain operations on tables can make the tables temporarily unavailable (that is, locked) for other WRITE operations. While a table is locked, you can still read the data but cannot modify it further until the initial operation is completed.
The locking mechanisms implemented are as follows:
- Edit/delete lock
-
The table is locked as well as any child tables (if editing or deleting recursively) that do not have an MN relationship to the table.
If the table has MN relationships, the MN child tables are locked as well. However, the records are updated one by one and the table gets locked and unlocked for each affected record.
- Publishing lock
-
The table is locked as well as all parent and child tables (regardless of the type of reference to the affected table, MN or not). Spefically, this occurs when you publish or revert your changes, import data, merge or delete records in bulk, or group records.
- Simple lock
-
Some actions lock only the affected table. Specifically, this occurs when a record is rejected, returned to edit after it was moved to publishing, or moved to publishing. The lock also occurs during data import and validation.
Was this page useful?