If you can't find the product or version you're looking for, visit support.ataccama.com/downloads

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.

Table attributes

General

The General tab allows configuring a variety of general table attributes:

Name

Required

Description

Table name

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.

Some names are reserved and cannot be used. See Reserved Words and Keywords.

Table label

Business name of the table as shown in the RDM web application.

Table description

Short description of the table to be displayed in the RDM web application by selecting Description.

Initial Load

If checked, initial load plans are generated for this table. See configuring-initial-loads-to-rdm-tables.adoc for more information.

Initial load - history

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

Determines the authentication configuration for RDM steps in initial load plans. See App Variables for information about authentication strategies.

Show in all tables

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

Approximate number of records in the initial source tables (for documentation purposes only).

Expected amount of changes

Approximate number of records incoming into the system (for documentation purposes only).

Business owners' roles

Roles that have editing rights for this table (use content assist to select roles) (for documentation purposes only).

Business owners of additional attributes roles

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

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

Business name of the column as shown in the web application.

Domain

Name of the column’s domain. See Domains for information about defining domains.

Required

Determines whether the column must possess a value or can remain empty.

Display mode

Sets special display properties for the column in the web application, with the following options:

Normal - The column does not have any special properties.
Label - The column will has the following special properties.
- Values of this column are displayed in combo-box options of a child table in the web application. See Working with Records in RDM, section Lookups.
- Values of this column are displayed instead of corresponding foreign keys if Referenced data display mode is set to labels in the Columns setup in the web application. See Viewing Data in RDM, section Columns Setup.
- Values of this column are displayed instead of corresponding foreign keys when viewing hierarchies in the web application. See Data Tab Overview, section Hierarchies.

Generated

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

Default value for the column, which is pre-filled when creating a new record in the web application.

Attributes of the datetime type should have their default value defined in one of the following formats:

yyyy-MM-dd
yyyy-MM-dd HH:mm:ss
yyyy-MM-dd’T’HH:mm:ss.SSS

The following variables can be used:

$username$ in a STRING column is replaced by the active user (the user that creates the record). There is already a technical column with the username, but if you want to define a validation, approval workflow, or filter on it, you need to place the username in a logical column (for example, by using the $username$ variable).
$now$ in a DATETIME column is replaced by the current date and time.
$today$ in a DATE column is replaced by the current date.

Note that data type mismatches result in a validation error.

Value Presenter

Assigns one of previously defined value presenters to this column. See Value Presenters.

Description

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 pressing Validate, and if the validation is not passed, a warning is displayed. Users cannot publish such records.

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: A primary key is required for matching records when importing data into the table. A primary key must be defined for a parent table. This is the key connecting this table to a child table which has a column with a corresponding references-based domain. See How to Create an MN Relationship in RDM for more information.
Columns	Y	Select the columns composing the unique key to the Name column.

Name

Required

Description

Name

Name of the unique key.

Primary Key

Determines whether the unique key is the primary key for the table. Setting a primary key has several uses:

A primary key is required for matching records when importing data into the table.
A primary key must be defined for a parent table.
This is the key connecting this table to a child table which has a column with a corresponding references-based domain. See How to Create an MN Relationship in RDM for more information.

Columns

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.

Name

Required

Description

Expression

An expression validating input.

Enable

N/A

Lets you easily enable and disable the validation without having to delete and rewrite the expression.

Message

The message that is shown when the validation is violated.

Column

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.

Field

Required

Description

SQL expression

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

A message shown when the validation is not passed.

Other involved tables

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).

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	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: `http://[host]:[port]/[service_name]`.
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).

Name

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

URL location of the service in the following format: http://[host]:[port]/[service_name].

Soap action

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

Namespace of the service.

Soap version

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.

Was this page useful?