In this article, we will discuss how to create a Mapping Layout in QuerySurge and explain how Layouts can be used to standardize the information captured in your Mappings. Before creating a Mapping Layout, it is important to understand a few core concepts.
What is a Mapping?
An ETL Mapping defines how data is transformed and moved from a source system to a target system during an ETL (Extract, Transform, Load) process. It specifies the relationships between source and target fields, including any transformations, calculations, filters, lookups, or business rules applied to the data.
What is a Layout?
A Layout is a user-defined template used to create a base Mapping in QuerySurge. It defines the collection of fields that will be available when a user creates or edits a Mapping. Each field represents a piece of information that may be required to describe source systems, target systems, transformation logic, business rules, validation requirements, or other mapping-related metadata.
Once a Mapping is created from a Layout, users can complete and maintain the Mapping within QuerySurge. Layouts provide a flexible way to standardize the information captured in Mappings while allowing different types of mappings to collect the information most relevant to their use case. By configuring the fields included in a Layout, organizations can ensure that Mapping authors consistently capture the information required for ETL development, testing, and maintenance.
Creating a Layout
1. To get started, from the Project Administration section, navigate to Mappings > Layouts in the Administration Tree.
2. On the Layouts grid, click the Add button to create a new Layout.
3. Enter a Layout Name and optional description and click Save.
Note: Layout names must be unique per project.
The Layout Editor
When creating or editing a Layout, you will be presented with the Layout Editor. This editor is divided into three main components: the Layout grid (A), the fields panel (B), and the properties panel (C) as seen below.
Layout Grid (A)
The Layout Grid is the main editing area where fields can be added and arranged. Fields can be moved by clicking and dragging anywhere within the field and resized by clicking and dragging the handles along its corners and sides.
When moving or resizing a field, borders are displayed around all fields in the Layout to help with alignment. Fields are automatically compacted toward the top of the Layout to minimize empty space.
The Layout Grid provides a visual representation of how Mappings created from the Layout will appear in QuerySurge. A Preview option is also available to view the Layout without the editing controls and styling, providing a closer representation of how the resulting Mapping will appear to users.
Fields Panel (B)
The Fields Panel contains a list of all the objects that can be added to a Mapping. The panel is divided into multiple tabs, with each tab grouping items by type.
- Base Fields - Contains the different types of fields that can be added to the Layout. Selecting and dragging a field (row) onto the Layout Grid adds a field of that type to the Layout.
- Separators - Contains horizontal and vertical separators that can be dragged and dropped onto the Layout Grid.
- Layout Fields - Contains all of the fields currently added to the Layout. Selecting a field in the table also selects the corresponding field in the Layout Grid.
Properties Panel (C)
The Properties Panel provides customization options for the Layout Grid as well as for individual fields.
- Grid Properties - Contains settings for the Layout Grid, including options to modify the dimensions of the Layout.
- Field Properties - Contains settings for the selected field. The available settings vary depending on the field type. This tab is disabled when no field is selected.
Adding Fields
To add a field to a Layout, simply click and drag the desired field type you would like to add from the Base Fields panel into the Layout Grid. As you drag the field, a placeholder is displayed in the grid to indicate where the field will be placed when the mouse button is released. Existing fields in the Layout Grid will dynamically adjust their positions to prevent overlapping with the field being added.
Resizing and Moving Fields
Fields can be resized by clicking and dragging any of the eight handles that appear when hovering over a field. You can also set the field size manually by adjusting the width and height values in the Field Properties panel when the field is selected.
Fields can be moved by clicking and dragging anywhere on the field except the resize handles.
Note: Whenever a field is being resized or moved, bounding boxes of all other fields in the Layout will be displayed to aid with alignment.
Note: Fields will always be compacted toward the top of the Layout. If there is any space above a field wide enough to fit it, it will automatically be pulled up to take that space.
Fields
There are currently 12 different types of fields that can be added to a Layout. Each field serves a specific purpose, as described below.
Field Name | Description | Example |
|---|---|---|
| Agent | A single selection drop-down input with autocomplete containing all Agents assigned to your project | |
| Checkbox | A group of one or more checkbox inputs that allows for multiple selections | |
| Connection | A single selection drop-down input with autocomplete containing all Connections configured in your project | |
| Date Time | An input that can be configured for selection of dates, times, or both | |
| Label | A decorative label that allows the font style, size, and color to be configured | |
| Number | A number based input that can be configured for both integer and decimal values | |
| Radio | A group of one or more radio inputs that only allows a single selection | |
| Select | A single selection drop-down input with autocomplete and customizable options | |
| SQL Dialect | A single selection drop-down input with autocomplete containing commonly used SQL dialects | |
| Text | A single line text input | |
| Text Area | A multi-line text input | |
| User | A single selection drop-down input with autocomplete containing all Users assigned to your project |
Separators
Horizontal and Vertical Separator components can be used to help organize your Layout. Separators are available in the Separators tab of the Fields Panel. Separators can be dragged into the Layout Grid the same way fields can and provide configuration options for both the color and size of the separator.
Field Properties
A field's properties can be configured in the Field Properties tab of the Properties Panel. The panel automatically opens whenever you add a new field or select an existing field in the Layout Grid.
Each type of field has its own set of configurable properties, however a few properties are common to all field types:
- Field Name - The name of the field, which must be unique among all fields in the Layout. Field names are automatically generated to avoid duplicates; however, it is recommended that you give your fields useful and descriptive names, as field names are used by several Mapping-related features such has Mapping Import/Export.
- Width and Height - The width and height of the field, measured in pixels.
- Label Text - For all field types except for Label fields, this property adds a simple text label above the field's input element, as shown in the examples above. For Label fields, the label text is edited inside of a rich text editor to allow for the font's style, color, and size to be configured for cases where a more decorative label is needed.
Many field types also have Required and Default Value properties. When a field is marked as Required, a value must be provided for the field in any mapping created using the Layout. Fields marked as Required will have a red asterisk (*) next to their label text, or above their input component if no label text is set.
Setting a Default Value causes the field to be automatically populated with the specified value whenever a new Mapping is created using the Layout.
In addition to these common properties, some field types have additional configurable properties specific to their functionality.
Number Fields
- Allow Decimals - Determines whether the input accepts decimal values (e.g. 2.718), or is restricted to integer values (e.g. 123).
- Min - Specifies the minimum value that the input accepts. If a Default Value is set, it cannot be less the the minimum value.
- Max -Specifies the maximum value that the input accepts. If a Default Value is set, it cannot be less the the maximum value.
Date Time Fields
- Mode - Determines whether the input allows users to select a date, a time, or both a date and time.
Select Fields
- Options - Configures the options displayed in the drop-down component. One option can be designated as the default selection.
Radio Fields
- Options - Configures the individual radio inputs in the group and their associated labels. One option can be designated as the default selection.
Checkbox Fields
- Options - Configures the individual checkbox inputs in the group and their associated labels. Each option can be individually designated as checked by default.
Updating Layouts
When updating a Layout that is already in use by a Mapping, it is important to keep the following considerations in mind:
- Deleting a field - Any data saved to that field in existing Mappings that use the Layout will be lost.
- Adding a required field without a default value - Existing Mappings using the Layout will require a value to be provided for the new field the next time they are edited.
- Making a non-required field required or removing the default value from an existing required field - Existing Mappings using the Layout that do not already have a value for the field will require one to be provided the next time they are edited.
The first time you attempt to make any of these modifications, you will be shown a warning similar to the following:
Deleting Layouts
When deleting a Layout, any Mappings that are using the Layout will also be deleted. Attempting to do this in the Layouts grid will show a warning indicating how many Mapping will be affected and will required explicit confirmation.