Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Excerpt

Main ETL+ page. Opens after Sign in page.

Anchor
page_callout_1
page_callout_1

  1. Data Warehouse info: The target database/data warehouse that is loaded with data extracted from the Source Objects and Target Objects. Name of the data warehouse connected to this ETL+ session. The info shows as [<MS SQL Instance Name>].[<Database name>].

  2. Find image-20240522-194841.png. Click to open the search box. (shortcut: Ctrl + F)

image-20220902-175314.png

3. Log image-20240522-193856.png. Click to open the ETL Logpage. (shortcut Ctrl + G)

  1. ETL Working Files.

  2. Help image-20240522-193932.png. Click to open documentation for this ETL+ page in a web browser (shortcut F1)

  3. Settings (blue star) Click to open the Settings page.

  4. Version number: format: vYYYY.MM.DDBB.

    • Example: v2022.08.2901 means the 1st build on 08/29/2022.

  5. Anchor
    EntityDisplay
    EntityDisplay
    ETL+ User name and Entity for the current session.

    • User: the ETL+ username (email address) that is currently logged to ETL+.

    • Entity: the Entity identifies the mappings shown on this page.

    • SQL instance hosting ETL+ etl_metadata DB: only shows for admin users.

  6. Close. Click to close the current ETL+ session.
    To close without saving changes click Close and click Yes to the ‘Leave without saving?' prompt.

  7. Save. Click to save changes (shortcut Ctrl + S).

    • An asterisk indicates there are unsaved changes.

  8. Load Panel. Load All, Replace, Upsert and Append.
    For more see ETL+ Table Load Types.

  9. Confirm.

  10. Parse toggle. When on, changes made to the columns named in the SELECT clause on the Extract SQL Statement panel will be reflected on the target table.

  11. Source Objects panel.

  12. Target Objects panel.

  13. Extract SQL Statement panel.

NOTES: Hover over and/or right-click icons and objects on ETL+ to view their functions and/or metadata.

Anchor
ETLSourcePanel
ETLSourcePanel


Source Objects Panel

Names and maintains connections to data sources.

Source Objects panel
button toolbar

Source Objects panel of ETL+ Extract, Transform and Load (ETL) window.

  1. Add Table: Select a table under a data source and click Add Table.

  2. Add Data Source: Click to add a new data source. More at https://dataself.atlassian.net/wiki/spaces/DS/pages/1570963898#Adding-Data-Sources

  3. Delete Source.
    Select a data sourceand click to delete.

  4. (blue star) Refresh. Select a data source and click to refresh it's list of tables. (F5)

  5. (blue star) Properties. Select a data source and click to change the settings.
    Similar to Add Data Source. More at ETL+ Data Sources | Configuring a Data Source

  6. (blue star) Add Table to Target


data sources. Select a data sourceand click Properties to configure. (Ctrl + I)

Excerpt
hiddentrue
nameInternal_Note_Refresh
  1. Note about Refresh: Refresh loads meta-data but avoid using the word load to avoid confusion with the Load Now button which loads data.

  2. Anchor – Confluence issue – the Anchor macro may take up a line / puts a blank line into the page as displayed by Confluence and Scroll Viewport. This appears to be a Confluence bug of some kind.

    • Workaround: Put the Anchor macro at the end of a previous paragraph.

Anchor
DataSources
DataSources
Data Sources

Data sources are source systems (data sources) available to extract data.

zData Warehouse is the target/destination data warehouse. Read and write-back data to transform data already in the data warehouse. See also zData Warehouse (Reimports) in the Target Objects panel.

  • Used to created calculated fields, apply formulas, run SQL functions and make other data manipulations to the data warehouse.

Excerpt
hiddentrue
nameNo longer needed?

How to Expand the Data Source in the Source Objects panel / Refresh

To show the names of the tables in zData Warehouse for example:

  1. Click zData Warehouse.

  2. Click Refresh (blue star)

  3. Click the expand button next to zData Warehouse.

Related Pages: ETL+ Source Drivers, ETL+ Data Source List
Adding a new data source to ETL+

Anchor
TargetObjectsPanel
TargetObjectsPanel
Anchor
isMissingRequiredParameterstrue

Target Objects Panel

The center section of the Target Objects panel is a hierarchical menu consisting of target tables grouped by data source and target column names.

Configures the mapping of Source Objects to Target Objects. Allows data warehouse table maintenance and data load. Lists the tables and columns mapped to the data warehouse grouped by data source.

x

x

x

x

x

x

  1. Create Table. Select to create a new data warehouse table from scratch. Name it and then create SQL for it in the Extract SQL Statement panel.

  2. Delete Table. Select a table then select Delete Table to delete it, select Yes to confirm. (Ctrl + Del)

  3. Duplicate Table. Select a table, then select Duplicate Table to duplicate it. (Ctrl + 2)

  4. Rename Table: Select a table, then click Rename Table to rename it. (F2)

  5. Design: Select a table and click Design to design the columns format and extra data manipulation.
    More at: ETL+ Design Page

  6. Preview Table: Select a table, then lick Preview Table to preview data in the Preview page. (Ctrl + P)
    The Preview page display a sample of its records from the table’s data source (ETL+ executes the top records from the Extract SQL Statement to preview).

  7. Load Now: Select a table and click Load Now to load its source data into the data warehouse. (Ctrl + L)
    (The corresponding SQL and Load type options in the Extract SQL Statement panel determines how and what data is loaded.)

  8. More Actions.

    • Show Columns

    • Expand All / Collapse All: Expand or Collapse hierarchical menu

    • Download to CSV

    • Freeze: Disable changes.

  9. Job. Opens theJob page. (Ctrl + J)
    More at: ETL+ Job Page

Panel
panelIconIdd62b42b6-3331-40bb-b2b8-a7aff7a3b368
panelIcon:notes5:
panelIconText:notes5:
bgColor#F4F5F7

  • Select a table to view its mapping on the Extract SQL Statement panel.

  • Expand/collapse the list of tables by clicking the +/- icon by the Source Group (header).

  • Double-click a table to enable expand/collapse.

Right-click Target Objects (target databases, tables or columns)

  1. Load Now

  2. Preview Table

  3. Design

  4. Rename Table

  5. Duplicate Table

  6. Delete Table

  7. Create Table

  8. More Actions

    1. Show Columns

    2. Expand All / Collapse All

    3. Download to CSV

    4. Freeze

Target Objects

A target object is a list of data warehouse tables mapped to a data source in the Source Objects panel.

Anchor
zDataWarehouseReimports
zDataWarehouseReimports
zData Warehouse (Reimports) is a pre-defined source linked to the data warehouse. This source lists tables from the data warehouse re-imported into the data warehouse (for data manipulations).

Tables

Target Tablesrepresent tables in the data warehouse.

Table Columns

Every table consists of one or more table columns.

Double-click a table or right-click it → Show Columns → +/- to expand/collapse its list of columns.

Types of Table Columns

  • PK (Primary Key) icon: Columns that are part of the table’s primary key.

  • Black column icon: The data warehouse column format is the same as from the source.

  • Green column Icon: The data warehouse column format has been edited in the Design page.

How to edit column data types and PK
Anchor
Extract-SQLStatementPanel
Extract-SQLStatementPanel


Extract SQL Statement Panel

Edits the SQL code (SQL statements) that controls the mapping of the source data connections in the Source Objects panel to the data warehouse tables in Target Objects panel.

Each SQL statement corresponds to a source group and table from the Target Objects panel. The output of the SQL SELECT statement loads into the destination data warehouse displayed just below the page title. See Data Warehouse info.

Extract SQL Statement panel (Load All)

  1. Display of the names of the corresponding source group and table on the Target Objects panel.

  2. Extract SQL Statement. Editable text of SQL code/statement.

    • To view or edit SQL for a table: Select a table on the Target Objects panel. The corresponding SQL statement should appear in the Extract SQL Statement panel.

    • Use the SQL Statement syntax of the data source. Look at the SQL syntax for MS SQL Server sources in MS SQL Server, Providex syntax for for the Providex ODBC, etc.

  3. Load Type: Controls how the data load process for the selected table.
    More at ETL+ Table Load Types.

  4. Confirm. Click Confirm to accept the changes.
    To reject the changes click on another table and click No to the Confirm changes? prompt.

    • A asterisk indicates that the SQL has been edited.

  5. Parse.

Panel
bgColor#F4F5F7

More about SQL and the Extract SQL Statement panel.

Related Pages

v2023.07 ✅

Excerpt
hiddentrue
nameOld Image