Database Links for Source Systems (Project)

Applies to
Platform: Oracle
Architecture: Stage · Data Store · Data Vault

Database Links for Source Systems (CreateDatabaseLinks) decides how the Stage layer of an Oracle project reads its source systems. It reads them through database links that the deployment creates, through links that exist already, or directly. Choose the value that matches where your source data lives.

The Data Mart generator configuration does not offer the property.

Values

A selection in the Technology group of the Additional Settings in Project Settings:

Create Database Links (default): the deployment creates one private database link per source system.
Use Existing Database Links: the Stage views read through a database link that you provide for each source system.
Do Not Use Database Links: the Stage views read the source objects in the same database directly.

Effect on generated output

Create Database Links: the Stage script starts with DROP DATABASE LINK and CREATE DATABASE LINK <source system>_DBL for each source system. The link connects as the source user to host, port and service name from the placeholder values. The Stage views read <source schema>.<table>@<source system>_DBL.
Use Existing Database Links: no link is created. The Stage views read <source schema>.<table>@<database link name>.
Do Not Use Database Links: the Stage views read <source schema>.<table>.

<source system> is the source system name with underscores for spaces. <source schema> and <database link name> come from the placeholder values, whose source system variables depend on this property.

Prerequisites

Create Database Links: the Stage user has CREATE DATABASE LINK.
Use Existing Database Links: the link exists and the Stage user can use it, for example a public link.
Do Not Use Database Links: the Stage user has SELECT on each source object. Without it, the Stage views fail with ORA-00942 at deployment.

Example

The source system BFM Mssql of a Data Vault project has the table BOM. With Create Database Links the Stage script creates the link and the view reads through it:

CREATE DATABASE LINK BFM_Mssql_DBL
CONNECT TO <source user> IDENTIFIED BY <source password>
USING '(DESCRIPTION= ... (HOST = <host name>) ... )';
...
FROM <source schema>.BOM@BFM_Mssql_DBL S1

With Use Existing Database Links and the link SRC_LINK, the view reads FROM <source schema>.BOM@SRC_LINK S1. With Do Not Use Database Links it reads FROM <source schema>.BOM S1. With the prerequisites met, the load succeeds in all three cases.

Changing the value

The value changes the Stage views and the placeholder variables of the source systems. Generate again, fill the new variables, replace the placeholders and deploy.