Stage the sources

What is a stage?

A Stage is a temporary location that can be used to mutualize transformations, perform unions and joins of multiple sources, and more.

Create a stage

A stage can be created:

From datastore field(s): These fields will be added to the stage’s data structure.
From the schema that will store the Staging Area (Staging Area Location).

Create a stage from Datastore Field(s)

To create a new stage from datastore fields:

In the Mapping Editor, select then drag and drop one or several fields from a datastore onto an empty area of the editor:
Select Create a Stage > Browse all staging areas:
The Select the Staging Area dialog opens. Select the schema from a database Metadata to create the stage. This location, called the Staging Area is the database schema where staging operations will be performed and temporary staging objects will be stored.
Optionally select Mark the selected Metadata as preferred (see Preferred staging area locations) and then click Select.
The stage is added to the diagram.

Create a stage from a Schema

To create a stage from a schema, drag a Schema icon schema from a database Metadata in the Project Explorer and drop it onto the Mapping editor.

If the schema is dropped onto an empty area of the editor: an empty stage is added to the diagram.

A Load Template and a Stage template are added to the stage as soon as it contains one mapped field.
If the schema is dropped onto an existing mapping link between one or several sources and a target, the Create Stage dialog opens:
1. Select the dataset to use to initialize the stage(s) field:
  - Select Initialize from Sources to add the fields from the source datastore(s) to the stage.
  - Select Initialize from Target to add the fields from the target datastore to the stage.
2. If Initialize from Target is selected, set the Target Mode Options:
  - Select Mapped Fields Only to add only the fields that are mapped with source fields.
  - Select Set DataType to pre-select the Enable DataType option for all added fields (see Query fields).
3. Click OK to create the stage.

Define stage properties

The following properties can be set in the Properties view of a stage:

Alias: User-friendly name used in the expressions when referring to this stage.
Tags: Add tags to the stage. Tags are used in certain Process Templates.
Description: Free form text to specify some description.
In the Advanced properties finger tab, the Integration Sequence specifies the order in which tables and stages without any mutual dependencies must be loaded.

Add query fields to a stage

To add a Query Field to a stage:

Select the stage.
Click on the button to add a new field to the stage.
In the Properties view, set the following properties:
- Alias: Name used in expressions to refer to this field.
- Enable: Enables or disables the mapping for this field.
- Execution Location: Defines whether the mapping of this field should be executed in the Source or the Staging Area.
- Aggregate: Indicates that this field contains an aggregate expression. Other (non-aggregated) columns are added in the GROUP BY clause of the queries.
- Enable DataType: Select to define a custom data type for the field. When this option is selected, the Structure finger tab is visible and allows setting the following properties to define the data type of the current field:
  - Type
  - Precision (when applicable for the selected Type)
  - Scale (when applicable for the selected Type)
    
    When Enable DataType is not ticked (default), the data type is automatically determined based on the source(s) and target(s).
- Tags: Add tags to the field. Tags are used in certain process templates.
- Description: Free form text.
In the Expression Editor view, enter the expression to populate the field.
Press Ctrl+S to save the editor.

To add a Query Field based on an existing datastore column, proceed as follows:

Drag and drop the column from the datastore onto the stage.
In the Properties View, review and edit the properties listed above.
In the Expression Editor review and edit the mapping expression for the field.

Combine data sources using sets

Sets allow combining several data sources into a single stage.

A Set defines a mapping configuration between a source datastore and a stage. Sets can be added to a stage to allow loading multiple sources in this stage, each using different mapping rules. Sets can then be combined using SQL set operators such as UNION or INTERSECT.

When creating a stage and mapping the first field, a Set aliased A is automatically defined. The stage’s expression is initialized with [A], which means that stage’s data entirely comes from this default set.

To create a new set and combine it with other existing Sets:

Select the stage in the mapping editor.
Click the button to add a new set to the stage.
In the Properties view, set the following properties:
- Alias: The alias used in expressions to refer to this set.
- Description: Free form text.
Select the created Set.
Define the mapping rules to apply to this Set: drag and drop columns from the source datastore and/or edit each stage field’s expression in the Expression Editor.
Edit the stage’s expression to combine data from the different Sets:
1. Select the stage again.
2. In the Expression Editor view, set the operators to use between the sets:
  - Each set can be referred to as [<Set’s alias>]
  - Combine Sets using SQL set operators. For example: ([A] UNION [B]) MINUS [C].
    
    The list of available set operators depends on the engine used by the Staging Area Location.
Press Ctrl+S to save the mapping.

Preferred staging area locations

Preferred Staging Area Locations appear as shortcuts in the contextual menu when creating a stage (HOTEL_MANAGEMENT in the example below):

create stage preferred access

Preferred Staging Area Locations can be defined:

When creating a stage by selecting the Mark the selected Metadata as preferred option:
In Designer’s preferences under Windows > Preferences > Semarchy xDI > Mapping > Staging Area.