Model & ETL is the Business Intelligence feature used to build pipelines that pull data out of a connected Data Source, join and transform it across multiple tables, and load the result into a dataset the BI platform can use directly.
A pipeline is the set of instructions for that process: which data source and tables to read from, how those tables relate to each other, which columns to keep and how to rename or aggregate them, any extra transformation logic, the structure of the resulting output dataset, and how often the pipeline should run.
Once a pipeline has run successfully, its output dataset can be used the same way as any other dataset — for KPI tracking, report building, and further analysis.
Navigate to:
Creating a pipeline is a 6-step wizard:
1. Pipeline Info
2. Select Source
3. Field Mapping
4. Transform Builder
5. Output Dataset
6. Schedule
Step 1 – Pipeline Info
The first step captures the pipeline's basic information.
To complete this step:
Step 2 – Select Source
This step defines which data source and which tables the pipeline will read from.
To complete this step:
1. Select a Data Source from the dropdown: Choose one of the data sources already configured under Business Intelligence → Data Sources
2. In Select Tables, search for and check at least two tables: A searchable, scrollable checklist of every table available in the chosen data source (the same list retrieved via Load Schema on the data source). Check a table to include it in the pipeline; use the eye icon to preview its columns first. Examples: select tblinvoices and tblclients
3. Set one of the checked tables as the Primary Table: The main, driving table for the pipeline. This field only becomes selectable after at least one table is checked in Select Tables below. Examples: select tblinvoices as primary table
4. Adjust Preview limit if needed: The number of rows fetched when previewing data while building the pipeline (default 50).
5. Click Next.
Step 3 – Field Mapping
This step defines how the selected tables relate to each other, and which columns flow into the output dataset.
Join Builder
Define how each additional table connects to the Primary Table (or to another joined table):
• Join type – e.g. INNER (only matching rows from both tables), LEFT.
• Join condition – in the form table.column = table.column, for example tblacc_accounts.id = tblacc_account_history.account, or tblinvoices.clientid = tblclients.userid.
Column Selection
The left-hand panel lists each selected table as a card, with the Primary Table labeled PRIMARY and its primary key marked PK. Expand a table's card to see its columns, and check every column that should be included in the output dataset.
Important: You must tick at least one column from the left-hand panel before running Preview. If no columns are selected, the Mapping table stays empty and Run Preview falls back to a placeholder query (SELECT 1 AS no_data ... ) that only checks whether the join finds matching rows — it does not preview any real data. Select the columns you need first, then run Preview again to see actual field values. |
Mapping
Once columns are checked on the left, they appear automatically as rows in the Mapping table, which records:
• Source – the originating table.column.
• Target – the field name it will have in the output dataset.
• Type – the data type of the field (text, number, date, etc.).
• Aggregation – an optional summary function (e.g. Sum, Count, Average) applied when the dataset needs aggregated values rather than row-level detail.
Preview
Use Run Preview to execute the join and mapping against a sample of rows (up to the Preview limit set in Step 2) and confirm the result looks correct before moving on to Transform Builder.
To complete this step:
1. In the left-hand panel, check the columns to include from each table.
2. In Join Builder, choose the join type and set the join condition(s) linking each table back to the Primary Table.
3. Review or adjust the Target name, Type, and Aggregation for each mapped field.
4. Click Run Preview to check the joined result — confirm it shows real columns and data, not just a no_data placeholder.
5. Click Next once the preview looks correct.
Step 4 – Transform Builder
Transform Builder applies filters and calculated fields to the joined result from Field Mapping, before it is written to the output dataset.
Filters
Each filter row narrows down which rows are included in the output dataset:
• table.column – the field to filter on, e.g. tblinvoices.status.
• Operator – the comparison to apply (=, !=, >, <, LIKE, IN, BEETWEEN, DATE).
Click + Add Filter to add another condition. Click the red × button to remove a filter row.
Tip: Use filters to limit the dataset to the rows that matter — for example, only paid invoices, or only leads created this year — rather than loading every row from the joined tables. |
Calculated Fields
A calculated field adds a new column to the output dataset, computed from the fields already selected in Field Mapping:
• field name – the name the new column will have in the output dataset.
• Formula – an expression combining existing fields, e.g. total - discount.
Click + Add Calculated to add another calculated field. Click the red × button to remove one.
Preview
Click Preview to run the filters and calculated fields against the joined data and check the result before moving on to Output Dataset.
To complete this step:
1. Add any filter conditions needed to limit the rows in the dataset, using + Add Filter for each additional condition.
2. Add any calculated fields needed, giving each a field name and a formula, using + Add Calculated for each additional field.
3. Click Preview to check the filtered and calculated result.
4. Click Next to continue to Output Dataset.
Step 5 – Output Dataset
This step defines the dataset that will be produced once the pipeline runs, using the joined and transformed result from Field Mapping (Step 3) and Transform Builder (Step 4).
To complete this step:
1. Enter a Dataset Name: A unique, descriptive name for the output dataset. This is the name other Datasets, Reports, Dashboards, and KPI calculations will use to query this pipeline's result — e.g. invoices_by_client_paid_vn.
2. Select a Storage type — Table or View.
Table: The result is physically materialized and stored as its own table.
View: Only the query is stored; data is read live from the source tables each time.
3. Select a Write mode — Replace or Append.
Replace:
Append: Keeps existing data and adds only the new rows produced by each run. Use when: Historical or log-style datasets that should accumulate over time.
4. Click Preview dataset to check the generated SQL and the resulting data in Data Preview.
5. Click Next to continue to Step 6 – Schedule.
Step 6 – Schedule
This final step defines when and how often the pipeline runs automatically once Field Mapping, Transform Builder, and Output Dataset have been configured.
To complete this step:
1. Choose Select schedule: The overall schedule type for the pipeline: Hourly or Weekly.
2. Choose Frequency — How often the pipeline actually runs: Hourly, Daily, or Weekly. This field determines which additional fields appear below it.
3. Fill in the additional fields shown for the chosen Frequency.
4. Set Start Time — the date the schedule becomes active.
5. Enter Retry Attempts if automatic retries on failure are needed.
6. Check Notify on failure to receive alerts when a run fails.
7. Click Finish to complete pipeline creation.
Running and Verifying a Pipeline
After clicking Finish in Step 6, the pipeline is saved and appears in the Pipelines list under Business Intelligence → Model & ETL. This screen is also where a pipeline can be run manually and its result verified.
Running a Pipeline Manually
A newly created pipeline shows N/A in Run Status and “–” in Last Run, since it has not executed yet — whether or not a schedule was configured in Step 6. To run it immediately rather than waiting for the schedule:
1. Find the pipeline in the Pipelines list.
2. Click the green Run button (play icon) in the Actions column for that pipeline.
3. Wait for the run to complete. Run Status changes from N/A to Success once it finishes, and Last Run updates to the current timestamp.
4. If Run Status shows a failure instead of Success, click the pipeline name to re-open it and review Field Mapping, Transform Builder, and Output Dataset for issues.
Note: Run Status reflects only the most recent run. Running the pipeline again — manually or on schedule — overwrites this status with the new result. |
Pipeline Detail Page
Clicking a pipeline's name in the Pipelines list opens its detail page, with four tabs: Overview, Mapping, Output Dataset, and Logs. The same Run, Edit (pencil), and Delete actions available from the Pipelines list are also available here, in the top-right corner.
Overview tab
A read-only summary of the pipeline's configuration from Steps 1, 2, and 6:
Mapping tab
A read-only view of the join and field mapping configured in Step 3 – Field Mapping:
Output Dataset tab
Shows the SQL the pipeline generates and a live preview of its result:
Logs tab
A step-by-step log of the pipeline's most recent run — useful for troubleshooting a run that failed
Each row logs one stage of the run — Connecting data source, Executing dataset query, Writing dataset, Sync dataset registry, Extract dataset schema, and Pipeline executed successfully — with its own Status, Rows, and Execution Time. If a run fails, the stage where it failed will show a non-success status here.
