PlyxSQL — Manual
Connecting to your data (part A), using the tree and the Tables tab (part B), designing the schema with the Designer (part C), configuring its semantics for AI (part G), reading it as a star schema of facts and dimensions (part H), moving data with ETL (part D), measuring performance (part E), visualizing data with Analysis (part F), building queries by dragging tables (chapters 1–14) and refining, running and analyzing their results (chapters 15–24), and using the menus (chapters 25–37).
Overview: the parts of PlyxSQL
PlyxSQL is a single window with a tree on the left, a row of tabs (Tables, Builder, Designer, ETL, Analisys, Performance-Profiler) and the SQL Editor at the bottom. Menus and toolbar change with the active tab. The map shows how the parts connect; every box links to its chapter.
| Part | Topic | What you find | |
|---|---|---|---|
| A | Connections | Connection types, network, file, ODBC, SSH, saving | → |
| B | Tree and Tables | Database tree, search, export, editing data and structure | → |
| C | Designer | Visual schema, foreign keys, roles, DDL | → |
| G | Semantic configuration | Aliases, lookups, calculated fields, filters, AI prompt, package | → |
| H | Star schema | Facts, dimensions, grain, guided queries, ETL link | → |
| D | ETL | Phases, field rules, running, scheduling | → |
| E | Performance-Profiler | Measurements, thresholds, execution plan, history | → |
| F | Analysis | Charts and maps from query results | → |
| I | Toolbars | The buttons of every tab, with numbered screenshots | → |
| 1–14 | Builder | Diagram, JOINs, fields, command bar, tabs, saving | → |
| 15–24 | SQL Editor | Writing and running SQL, results, transactions, history | → |
| 25–37 | Menu | All menu items and the other modes | → |
I1. The main toolbar
The toolbar sits under the menus and is shared by all tabs, but it shows only the groups of the active tab: Builder and Designer each have their own, ETL has its own, Analisys and Performance-Profiler share the Analysis ones. Groups 1 and 2 (project and connection) are always there.
| Tab | Visible menus | Toolbar groups |
|---|---|---|
| Tables / Builder | File, Modifica, Query, Vista, Schema, DDL, Connessione, Tab, Snippet/History, Strumenti, Impostazioni, Extra, ? | 1, 2, run, transactions, editor, AI, compare (I2) |
| Designer | File, Modifica, Schema Design, DDL, Connessione, Tab, Snippet/History, Strumenti, Impostazioni, Extra, ? | 1, 2, schema, view, generate, import/save, tabs, AI, compare (I3) |
| ETL | File, Impostazioni, ETL, ? | 1, 2, phases, fields, connections, project, run (I4) |
| Analisys | File, Impostazioni, Analytics, ? | 1, 2, configuration, export, refresh (I5) |
| Performance-Profiler | File, Impostazioni, Analytics, ? | same as Analisys (I6) |
Groups common to all tabs
| # | Button | What it does | Keys |
|---|---|---|---|
| 1 | New project | Starts an empty project. If the current one has changes it asks whether to save them. | Ctrl+N |
| 1 | Open project | Opens a saved project file (connections, tabs, diagrams). | Ctrl+Shift+O |
| 1 | Save project | Saves the project. The window title shows * while there are unsaved changes. | Ctrl+S |
| 1 | Save as | Saves the project to a new file. | Ctrl+Shift+S |
| 2 | Connect | Opens the active connection (the one selected in the tree). | — |
| 2 | Disconnect | Closes the active connection. | — |
I2. Builder
Under the main bar the Builder has a second bar with the diagram buttons (Run, Schema, Clear, Save, Load, Print, Undo, Redo, SQL, Fit, ERD, DDL, AI, Perf, Snip, Export, Subq, Expl, ExpG, CmpPlan, Tabs, Save.fdp, Load.fdp): they are described in chapter 9. Here are the groups of the main bar:
| # | Button | What it does | Keys |
|---|---|---|---|
| 3 | Run | Runs the current SQL in the Editor, or the selected part. | F5 |
| 3 | Run as script | Runs the text as a multi-statement script. | F6 |
| 3 | Run Builder query | Generates and runs the query of the active Builder tab. | F7 |
| 4 | Begin transaction | BEGIN TRANSACTION: the following statements stay pending. | Ctrl+B |
| 4 | Commit | Makes the changes of the transaction permanent. | Ctrl+M |
| 4 | Rollback | Undoes the changes of the transaction. | Ctrl+Shift+B |
| 5 | Undo | Undoes the last operation. | Ctrl+Z |
| 5 | Redo | Redoes the undone operation. | Ctrl+Y |
| 5 | Format SQL | Reformats the current SQL text. | Ctrl+Shift+F |
| 5 | Copy all | Copies the whole SQL text to the clipboard. | Ctrl+Shift+C |
| 6 | AI query | Generates the query from a natural-language request. | Ctrl+Shift+A |
| 6 | Build from Editor SQL | Reads the Editor SQL and draws tables and JOINs in the Builder. | Ctrl+Shift+Q |
| 7 | Schema compare | Compares the schema of two connections and shows the differences. | — |
| 7 | Data compare | Compares the data of two tables. | — |
| 7 | Tag compare | Compares schema tags (versions). | — |
Run and transaction buttons require an active connection and SQL in the Editor. The same functions are in the Query and Edit menus (ch. 28).
I3. Designer
In the Designer the bar contains the buttons to build, import, save and generate the schema. The menu is Schema Design (part C). Star schema analysis has no icon on the bar: it opens from the Schema Design menu (I7).
| # | Button | What it does | Keys |
|---|---|---|---|
| 3 | Generate AI prompt | Generates the JSON prompt from the configured semantic schema (AI prompt check window). | Ctrl+Shift+J |
| 3 | Refine with AI | Asks the AI to modify the schema already on the canvas. | — |
| 3 | Generate with AI | Describe the schema in words and the program proposes it. | Ctrl+Shift+I |
| 4 | Rename schema tab | Renames the active schema tab. | — |
| 4 | Close schema tab | Closes the active schema tab. | Ctrl+Alt+W |
| 4 | New schema tab | Adds a new schema tab to the Designer. | Ctrl+Alt+N |
| 5 | Export PNG | Saves the diagram as a PNG image. | — |
| 5 | Export SVG | Saves the diagram as a vector SVG image. | — |
| 5 | Clear schema | Empties the schema (asks for confirmation). | — |
| 5 | Load schema | Loads a .sdschema schema file (JSON), with the star model if present. | — |
| 5 | Export semantic package | Exports the Semantic Configurator to a portable JSON for a server component (G18). | — |
| 5 | Save schema | Saves the schema to a file (tables, columns, FKs, positions, star model). | — |
| 5 | Update from database | Re-aligns the schema already on the canvas with the database. | — |
| 5 | Import from database | Reads the schema from the active connection, choosing the tables. | — |
| 5 | Import from DDL | Builds the schema from a DDL script. | — |
| 6 | Export dialect | Exports the DDL in the chosen dialect. | — |
| 6 | Generate REST | The Generate REST item of the Schema Design menu. | — |
| 6 | Generate ALTER | Generates the ALTER TABLE statements for the differences against a snapshot. | — |
| 6 | Generate DDL | Generates the schema DDL script in the Editor’s Schema Designer tab (C8). | F7 |
| 7 | Zoom out | Zooms the canvas out. | Ctrl+- |
| 7 | Zoom in | Zooms the canvas in. | Ctrl+= |
| 7 | Zoom to fit | Fits the zoom to show the whole schema. | Ctrl+Shift+0 |
| 7 | Auto layout | Arranges the tables automatically. | — |
| 8 | Delete selection | Deletes the selected tables or foreign keys. | — |
| 8 | Add foreign key | Creates a foreign key between two tables. | — |
| 8 | Add table | Adds a table to the canvas. | — |
| 9 | Schema compare | Compares the schema of two connections and shows the differences. | — |
| 9 | Data compare | Compares the data of two tables. | — |
| 9 | Tag compare | Compares schema tags (versions). | — |
I4. ETL
In the ETL tab only the File, Settings, ETL and ? menus remain visible. The buttons on phases and fields are grey until you select a phase or a field. The ETL menu also has the Link to the star schema… item (H16), which has no icon.
| # | Button | What it does | Keys |
|---|---|---|---|
| 3 | Cancel run | Stops the import in progress. | — |
| 3 | Run ETL | Runs the phases of the ETL project (D8). | F9 |
| 4 | Save for service | Saves a .petl file for the Windows service of scheduled imports (D9). | — |
| 4 | Load ETL project | Opens a saved ETL project. | — |
| 4 | Save ETL project | Saves the ETL project to reopen it in this application. | — |
| 5 | Destination connection | Sets the connection selected in the tree as the destination. | — |
| 5 | Source connection | Sets the connection selected in the tree as the source. | — |
| 6 | Generate fields | Creates the fields of the phase from the columns. | — |
| 6 | Delete field | Deletes the selected field. | — |
| 6 | Edit field | Opens the rules of the selected field (D5). | — |
| 6 | New field | Adds a field to the phase. | — |
| 7 | Delete all phases | Deletes all phases (asks for confirmation). | — |
| 7 | Delete phase | Deletes the selected phase. | — |
| 7 | Edit phase | Opens the phase settings (D4). | — |
| 7 | Generate phases from matching tables | Creates a phase for every pair of same-named tables in source and destination. | — |
| 7 | New phase | Adds a phase to the project. | — |
I5. Analisys
In the Analisys tab the visible menus are File, Settings, Analytics and ?. Under the main bar there is the Analysis bar itself (Export, Copy image, Save, Load, Refresh, Resolution, Title), described in F3 and F8.
| # | Button | What it does | Keys |
|---|---|---|---|
| 3 | Load configuration | Loads the chart configuration from a file. | — |
| 3 | Save configuration | Saves the chart configuration to a file. | — |
| 4 | Export CSV | Exports the chart data to CSV. | — |
| 4 | Export SVG | Exports the chart as SVG. | — |
| 4 | Export PNG | Exports the chart as a PNG image. | — |
| 5 | Refresh | Re-reads the query data and redraws the chart. | F5 |
I6. Performance-Profiler
The Performance-Profiler tab uses the same bar and the same Analytics menu as Analisys: the groups are those of I5. The Profiler measures the SQL text in the Editor on the active connection (E1), so you need a connection and SQL in the Editor; the profiler functions (measure, thresholds, execution plan, history) are in the controls of the tab itself (E2–E7), not on the bar.
I7. Star schema in the interface
Where to find it
| Where | What it does |
|---|---|
| Schema Design → Analisi star schema (fatti e dimensioni)… | Opens the star model window. A schema must be open in the Designer. |
| ETL → Collegamento al modello a stella… | Compares the ETL project with the model of the active schema and proposes fixes (H16). An ETL project and a Schema Designer must be open. |
| Salva schema / Carica schema | The star model lives in the schema file (H2). |
The window: screenshots
On opening with an empty query the tree shows the facts and dimensions read from the schema; the query boxes are still empty.
The two screenshots predate the Run button, which in the current version sits between Open in Query Builder and Close.
Selecting a measure and pressing + Measure builds the query: the generated SQL appears on the right and the messages below it. In the screenshot the query sums three measures of FACT_BUDGET; under the tree are the measure properties (additivity, aggregation, “over time”) with Apply to model.
Buttons
| Button | What it does |
|---|---|
| Suggest model from schema | Draft of facts and dimensions from the foreign keys (H3). |
| Validate model | Writes the STARxxx messages in the bottom box (H14). |
| + Attribute / + Measure / + Filter | Add the node selected in the tree to the query. |
| Grain columns…, Apply to model, + Derived… | Edit the model (H4, H11). |
| Delete fact/dimension | Removes the selected node from the tree. |
| Copy SQL | Copies the query to the clipboard. |
| Run | Runs the generated query on the active connection and shows the result in a window with a grid (at most 5000 rows). The star window stays open: you can change the query and run again. |
| Open in editor | Moves the SQL to the Editor; to run it press Run (a SELECT gives a result set in the Designer too, see I3). |
| Open in Query Builder | Only for the simple form (H7). |
Running a query in the Designer
With Open in editor the SELECT goes to the Editor’s Schema Designer tab; pressing Run on the Editor bar shows the result in the results panel, under the text, with rows, columns and milliseconds:
A1. Connections overview
Every database, file or web service you work with in PlyxSQL is a connection. A connection has an alias (the name you see in the Builder and Editor tabs), a type and its parameters. You can create one in three different ways, chosen from the tabs of the New Connection dialog:
| Tab | What it is for |
|---|---|
| Network | Database servers: Firebird, InterBase, MySQL / MariaDB, SQL Server, PostgreSQL, Oracle, Informix, IBM DB2, plus SQLite and MS Access as files. |
| File / LocalSQL | SQLite, CSV, Excel / ODS, JSON, XML, fixed-position layouts, ClientDataSet, FDMemTable and REST APIs: files become tables you can write SQL against. |
| ODBC | Any source with an installed ODBC driver, or a DSN already defined in Windows. |
| SSH Tunnel | Not a database type: it routes the connection through an SSH server (bastion). It applies to network connections. |
A connection can be saved to an .fdc file in the connections folder and reappears in the tree at every startup.
A2. Start screen
At startup the start screen appears, with three columns and two buttons:
| Item | What it does |
|---|---|
| New project… | The list of source types (Excel/Open Calc, Firebird, InterBase, MySQL, SQL Server, PostgreSQL, Oracle, SQLite, MariaDB, Access, Informix, DB2, CSV files and ClientDataSet…), with a search box that filters as you type. Choosing an entry opens the New Connection dialog already set to the right tab and driver (in the example, "Native Excel" opens the File / LocalSQL tab on the Excel/ODS type). Below each entry a link points to the FireDAC documentation for the driver. The entries for web connectors (Gmail, Google Calendar…) open the connector management. Some entries come with a layout template (.tracciato, in the templates folder next to the program) to open a fixed-position file directly. |
| Open recent | Recently used projects, with a search box. Selecting an entry opens the project. A recent entry can be added to Favorites or removed from the list. |
| Favorites | The projects you have pinned. The list is saved in the user's folder (%APPDATA%\PGSOFT\plyxsql) and reappears at every startup; this one has its own search too. |
| Video courses | A list of video tutorials linked online. |
| Open project | Opens a .mfdproj project file with the file-picker window. The project opens and the start screen gives way to the workspace. |
| Dark / Light | Theme choice. |
- The start screen also shows the tiles of your saved analyses (see F8).
- F10 at any time takes you back to the start screen, hiding the menu and toolbar.
- When there are unsaved changes, on closing the program you are asked whether to save the project (Yes, No, Cancel).
- An update panel at the top indicates whether a new version of the program is available.
A3. Opening the New Connection dialog
- From the start screen (see above).
- From the tree: right-click the Connections folder or an
.fdcfile → New connection. - From the Connection → New connection menu.
At the top of the dialog is the Connection alias. It is proposed automatically based on the type (FB_Conn, SQLite_Conn, Excel_Conn…) and updates when you change tab or driver, until you edit it by hand. You cannot use an alias already in use by an open connection: the program warns you and suggests a free one.
A4. Network tab
- Connection alias
- Tabs: Network, File / LocalSQL, ODBC, SSH Tunnel
- Driver
- Server / host and Port
- Database / path with Browse…
- User name and Password
- Create database if it does not exist
- Additional parameters (Key=Value)
- Test connection
- Advanced parameters…
- Connect and add
When you choose the driver, port and user are set to their default values:
| Driver | Port | User | Notes |
|---|---|---|---|
| FB (Firebird) | 3050 | SYSDBA | Database = name or path on the server, for example SB.FDB |
| IB (InterBase) | 3050 | SYSDBA | |
| MySQL | 3306 | root | Also valid for MariaDB (see note) |
| MSSQL (SQL Server) | 1433 | sa | |
| PG (PostgreSQL) | 5432 | postgres | |
| Ora (Oracle) | 1521 | sys | |
| Infx (Informix) | 9088 | informix | |
| IBM (DB2) | 50000 | db2inst1 | |
| SQLite | — | — | Server and port disappear: Database is the path of the .db / .sqlite file |
| MSAcc (MS Access) | — | — | Database is the .mdb / .accdb file |
When you change driver the user is reset to its default value and the password is cleared. If the Port is empty or 0, the driver's default is used.
Create database if it does not exist
The checkbox appears for Firebird, InterBase, MySQL, SQL Server, PostgreSQL, Oracle, Informix and DB2 (not for SQLite and Access). With Firebird and InterBase the connection is opened in OpenOrCreate mode; with the other drivers, creation of the database is requested.
MySQL driver. The MariaDB dialect is chosen manually for that connection (Settings → Dialect) and is saved in the project.A5. File / LocalSQL tab
The Connection type menu lists the 11 available formats. A help line under the menu describes the chosen format, and for folder-based types the Files folder field (with Browse…) and the Extensions to consider appear.
| Type | What it does |
|---|---|
| SQLite — file | Opens an SQLite database (.db, .sqlite, .s3db). You specify the file path. |
| SQLite — in-memory | :memory: database: the data exists only for the duration of the session. |
| LocalSQL — ClientDataSet | Folder with .cds .cdc .aff .icp files; each file is a table. |
| LocalSQL — FDMemTable | Folder with .bin .mem files. |
| LocalSQL — CSV | Folder with .csv / .txt files, imported as in-memory tables. |
| LocalSQL — SQLite files | Folder with .db / .sqlite files: the databases are attached with ATTACH DATABASE. |
| LocalSQL — Fixed-position layout | Fixed-width text files described by a layout. |
| REST — JSON API | HTTP endpoints that return JSON. |
| JSON, XML | .json and .xml files treated as tables. |
| Excel / ODS | .xlsx .xls .ods files (extensions can be changed). One node per sheet, not per file. |
On all LocalSQL types you can then write SQL just as on a regular database: the Builder, JOINs across different files and the Editor all work the same way.
CSV options
When the CSV type is chosen, a panel appears with a sample file (…), field delimiter, separator, quote character, the first row = header checkbox, the encoding and a preview with a Refresh button. If the data does not look right, change the separators and refresh the preview before confirming.
Fixed-position layout
The panel contains record type, encoding, rows to skip and a layout grid (field name, start, length) with the buttons Add field, Delete field, Paste spec (parses a layout pasted from text), Save spec and Load spec, plus a preview.
REST — JSON API
- In the endpoints grid each row is a table: Table name, URL, JSON path (where the array of records is located in the response, for example
data.items). Use Add / Delete to manage the rows. - Test endpoint downloads the first endpoint and tells you how many records it found, or the error (invalid JSON, path not found, no endpoint).
- Authentication: none, Bearer (token), API Key (header name and value) or Basic (user and password).
- Long results are paginated: from the Editor, the next-block button downloads the following page. The data is kept in an in-memory SQLite database, so after the project is reopened it is downloaded again on first use.
A6. ODBC tab
Lists the ODBC drivers installed on the computer (in alphabetical order). You can use it in two ways:
- With a driver: select it in the list and fill in Server, Database / file, User and Password.
- With a DSN: type the DSN name in the dedicated field. If the DSN field is filled in, it takes precedence over the selected driver.
For file-based drivers (Excel, Access…) the … button next to Database lets you choose the file.
A7. SSH Tunnel tab
Needed when the database cannot be reached directly but only through an SSH server. Check Enable SSH tunnel and fill in:
| Field | Meaning |
|---|---|
| SSH Host | Address of the SSH server (for example bastion.myserver.com). |
| SSH port | Default 22. |
| SSH user | User for logging in to the SSH server. |
| SSH password | Optional. It is not saved in the project for security: it must be re-entered at every session. |
| .ppk/.pem key | Private key file, as an alternative to the password. |
C:\Program Files\PuTTY, C:\Program Files (x86)\PuTTY, C:\PuTTY and in the Windows PATH. If it cannot find it, the test fails with a message. Install PuTTY and try again.The local port of the tunnel is chosen automatically: the server and port given in the Network tab are redirected to the tunnel during the connection and restored on closing. The maximum wait to open the tunnel is 15 seconds. The Test connection button uses the tunnel if it is enabled. For an existing connection you can change these settings by right-clicking the .fdc file → Configure SSH.
A8. Additional and advanced parameters
At the bottom of the dialog is the Additional parameters box: one line per parameter in the Key=Value format; lines starting with # are ignored. They are for FireDAC options that have no dedicated field, for example:
CharacterSet=UTF8 Pooled=True Pool_MaximumItems=10 Pool_CleanupTimeout=30000 ExtendedMetadata=True FetchOptions.RecsMax=1000
The Advanced parameters… button opens an editor with all the connection parameters, already composed from the fields you filled in; with Apply to wizard the changes go back into the dialog.
A9. Testing, connecting and saving
| Button | What it does |
|---|---|
| ▶ Test connection | Tries to open the connection without adding it. The result appears above the buttons: ✓ Connection successful (or "via SSH tunnel") or ✗ with the database error message. |
| Connect and add | Opens the connection, saves it and adds it to the tree, automatically creating the linked tabs in the Editor and in the Builder. |
| Cancel | Closes without creating anything. |
Before connecting, the program checks that the alias is filled in and not already used by an open connection. If a saved connection with the same name already exists, it asks whether to overwrite it.
Where it is saved
- .fdc file: at the bottom of the dialog is the Save .fdc checkbox (active by default) with the path, proposed as
connections-folder\alias.fdc, and the Browse button. The file is plain text with a[FireDAC]section and oneKey=Valueline per parameter. - Application data store: if the program is integrated with its own database for connections, the file is not used: the name and parameters are written to the corresponding record and the checkbox does not appear.
LoginPrompt=False is always set and does not appear in the files.A10. Managing connections from the tree
The context menu changes depending on the node you click (the exact names depend on the language):
| Node | Items |
|---|---|
| Connections folder | New connection, Connector management, Open connectors folder, Compare schemas, Compare data, Refresh. |
| .fdc file | Connect; if connected: Disconnect, Edit parameters, AI Assistant. Always: Configure SSH, New connection, Connector management, Open file location, Delete file. |
| Database root (connected) | Refresh, Server capabilities, SQL matrix, Disconnect, Edit parameters. |
Edit parameters opens the parameters editor on an already created connection, so you can fix the host, port or credentials without recreating it.
A11. Connector management
Connectors link PlyxSQL to web services (Gmail, Google Calendar, Google Drive, GitHub, FreshBooks, Fitbit…). Each connector is a definition in a JSON file, one per service, that lists the endpoints: the read ones (GET) become tables you can write SQL against, the write ones (POST) are actions.
It opens from the context menu of the Connections folder → Connector management (Open connectors folder shows where the files are). The table has four columns: ID, Name, Available endpoints and Status. The search field filters the list as you type. The To be configured status means some service data is still missing (for example the client ID).
How to use a connector
- Configure… Enter the data required by the service (typically
clientId,clientSecret,redirectUri). Secret fields are masked. If the connector is already configured, the fields reopen with their current values, so you only correct what is needed. Save closes; Back or Esc cancel. - Authorize… Opens the browser to log in to the service (OAuth2 with PKCE). Tokens are encrypted on disk with Windows (DPAPI) and renewed automatically when they expire.
- Then choose how to use the data:
- Create connection… adds the connector to the tree as a connection, with its tables.
- Load into MemTable… downloads an endpoint into an in-memory table.
- POST action… sends data to a write endpoint (for example inserting an event into the calendar); you fill in the fields required by the template.
If a connector is still To be configured, Authorize, Create connection, Load into MemTable and POST action are blocked with a clear message instead of failing with an OAuth error. The dark box under the buttons is the log: it shows the outcome of every operation.
A12. Common problems
| Symptom | Cause and remedy |
|---|---|
| "Connection name already in use" | An open connection with the same alias exists. Choose a different alias (the program suggests a free one). |
| "A saved connection already exists…" | The .fdc file with that name already exists. Answer Yes to overwrite it, No to change the alias. |
| Test failed with a message about plink | PuTTY is not installed or plink.exe is not in the PATH. Install it (see A7). |
| The test succeeds but the connection is offline | The test does not save or add anything: you must press Connect and add. |
| The Firebird database does not exist | Check Create database if it does not exist or verify the path on the server (it is the server's path, not your PC's). |
| REST table "no such table" after reopening | REST data is kept in memory and starts empty again. Run the query again: the program re-downloads the table on the fly. |
| CSV data all in one column | Wrong delimiter: change it in the CSV panel and press Refresh. |
B1. The database tree
To the left of the workspace, the tree shows everything you can open: the connections and, under each, the structure of the database. It is also the place from which you view and edit tables.
- Hierarchy: Connections →
.fdcfile → database root → Tables, Views, Sequences, Stored Procedures folders. A table expands into Fields, Indexes, Triggers and Constraints (constraints and foreign keys). The contents are loaded the first time the node is opened. - Filter tables…: the box at the top filters the tree as you type, useful with hundreds of tables.
- Double-click a table: adds it to the Builder (ch. 3). If Browse mode is active, it instead opens the data in the Tables tab. The quick switch is in the context menu.
- Right-click: the menu changes depending on the node (see B6).
Above the workspace, the Tables · Builder · Designer · ETL · Analysis · Performance-Profiler tabs change the mode. Tables shows the data of the table selected in the tree.
B2. The Tables tab
- Title: the table being shown
- Navigation bar: first, previous, next, last, refresh
- More rows: loads the next block
- Column the filter acts on
- Operator of the filter
- Value to search for
- Apply and X (clears the filter)
- Counter: position and total rows
- Export: CSV, JSON, XML, XLSX, MemTable, ClientDS
- Filter=: filters on the value of the cell
- Edit: enables direct editing
Selecting a table in the tree fills the grid with its data (SELECT *). On databases such as Access and SQLite all rows are read; on other engines the data arrives in blocks and More rows appends the next block without losing what you have already loaded.
The counter (1 of 641 rows) shows the position of the current record out of the real total of the table, not out of the number of filtered rows.
B3. Searching and sorting
- Sorting: click a column header.
- Filtering: choose the Column (it also updates when you click a cell in the grid), the Operator, type the value and press Apply. X removes the filter.
- Filter=: takes the value of the selected cell and applies it immediately as an "equal to" filter, without using the drop-downs.
| Operator | Meaning |
|---|---|
| = | Equal |
| contains | Text contained in the value |
| > < | Greater, less |
| <> | Not equal |
| is null / is not null | Value absent or present |
The available operators depend on the type of the chosen column (for example, numeric ones on a numeric field).
B4. Exporting data
| Button | Result |
|---|---|
| CSV | .csv file |
| JSON | .json file |
| XML | .xml file |
| XLSX | Excel workbook; when finished, it asks whether to open it in Excel |
| MemTable | Binary .fdmt file (FDMemTable) |
| ClientDS | .cds file (ClientDataSet) |
The export contains what you see: if you have applied a filter or a sort order, the file contains only those rows, in that order. The program asks where to save the file.
B5. Editing data in the grid
The Edit button (toggle) makes the grid writable. When it is pressed, the navigation bar adds the commands to insert, delete, edit, confirm (Post) and cancel a row.
You get the same result by right-clicking the table → Edit data. Changes go directly to the database: make sure you are on the right connection.
B6. Changing the structure from the context menu
Right-clicking the tree nodes opens guided procedures that generate the DDL script. What happens to the script depends on the AutoExecute option:
- AutoExecute on (and connection open): the script is run immediately and the tree is refreshed.
- AutoExecute off: the script is taken to a DDL tab of the Editor, where you can review and run it yourself.
- A template with parts still to be completed (placeholders, TODO) is never run automatically: it ends up in the Editor with a header marking it as incomplete.
| Node | Menu items |
|---|---|
| Tables folder | Create table, Refresh |
| Table | Open in Builder · Select top N rows · Create index · Create trigger · Add foreign key · Edit table (ALTER) · Delete table (asks for confirmation) · Edit data · Import data · Find usages · Show dependencies · Calendar view |
| Indexes folder / an index | Create index · Rebuild · Delete |
| Triggers folder / a trigger | Create trigger · Enable / Disable · Delete |
| Constraints folder / a constraint | Add foreign key · Delete constraint |
| Views folder / a view | Create view · Open in Builder · Create trigger on view · Find usages · Show dependencies · Delete |
| Sequences folder / a sequence | Create sequence · Reset value · Delete |
| Stored Procedures folder / a procedure | Create procedure · Create from SQL body · Run · Show dependencies · Delete |
| Database root | Refresh, Server capabilities, SQL matrix, ERD, Compare schemas/data, Backup, Restore, Export/Import team package, Disconnect |
What the guided procedures ask for
- Edit table: reads the current fields from the database and generates the
ALTER TABLEfor the differences. On LocalSQL connections the driver does not support DDL: the wizard shows only a comment. - Create index: name, comma-separated columns, UNIQUE, Descending. If the engine cannot express the request (for example mixed directions in Firebird) the program says so and generates nothing.
- Foreign key: child table, constraint name, columns, target table and columns, ON DELETE and ON UPDATE (RESTRICT, CASCADE, SET NULL, NO ACTION).
- Create view: name, OR REPLACE and
SELECT. On SQL Server,CREATE OR ALTERis used instead of OR REPLACE. - Create sequence: not available on engines that do not have them (MySQL, SQLite, MS Access); the program tells you so.
C1. What the Designer is
The Designer is the designer of the physical schema: you draw tables, columns and foreign keys on a canvas and the program generates the DDL script for the dialect you choose. It also works starting from an existing database or from an SQL script.
- Designer toolbar
- Schema tab (in the example
sbt_Schema1) - Find table, Go and Bring closer
- Side bar with filters and layout
- Table: header with name and role (MST = master), columns with type
- Relationship (foreign key)
- Minimap
Below the canvas the Editor shows the generated DDL in the Schema Designer tab, with a comment header: schema name, dialect, generation date, number of tables and foreign keys. In the example there are 18 tables and 4 FKs, in the Firebird dialect.
C2. Side bar and top bar
| Button | Function |
|---|---|
| ▦ | Show all |
| ⊞ | Tables only |
| □ | Views only |
| λ | Procedures and functions only |
| ☰ | Views… (view management) |
| ƒ | Stored Proc… (procedure management) |
| ⤆ | Auto layout: arranges the tables automatically |
| 1:1 | Zoom 100% |
| ⌖ | Center the schema |
| ⇄ | Toggle between physical schema and semantic graph (C7) |
At the top left of the canvas: the Find table box (editable) with the Go button, which centers the view on the chosen table, and Bring closer, which brings the chosen table next to the selected one, handy for linking a foreign key between two distant tables.
C3. Creating the schema
From scratch
- Add a table: Schema Designer → Add table menu or right-click on the canvas.
- In the Edit table window enter Table name, Comment, Note and, for lookup tables, the label field name.
- Add the columns (right-click the table → Add column). For each column:
| Field | Meaning |
|---|---|
| Column name, Type | Name and data type; for a type not in the list use Custom type. |
| Length, Precision, Scale | Size of the type (for example VARCHAR(30) or DECIMAL(15,2)). |
| Default, CHECK constraint, Comment | Default value, check constraint and comment. |
| Nullable, Primary Key, Auto Inc, Unique | Checkbox options. Auto Inc generates the identity the right way for the dialect (on Firebird, a sequence and a trigger). |
From something that already exists
- Import from DDL: paste or open a script with
CREATE TABLEstatements; the dialect is recognized from the keywords. - Import from database: reads the schema from the active connection (you can choose which tables to import). Refresh from database realigns the schema already on the canvas.
- Generate with AI (Ctrl+Shift+I) and Refine with AI: describe the schema in words and the program proposes one. Requires the AI configuration (File → AI Configuration).
C4. Relationships (foreign keys)
- Right-click the starting column → Draw FK.
- Drag to the target column and release. The FK uses the columns you clicked; if you did not indicate one, it uses the primary key.
An FK identical to an existing one is not duplicated. To choose ON DELETE and ON UPDATE, use right-click → FK rules… (also for a new FK: the guide asks for the source field, the target table and the target field). To delete it, select it and press Del. Esc cancels the drawing in progress.
C5. Moving around the canvas
| Gesture | Effect |
|---|---|
| Drag a table | Moves it |
| Drag the table border | Resizes it |
| Mouse wheel over a long table | Scrolls the fields (▲ ▼ arrows in the indicator row) |
| Double-click a column | Opens column editing |
| Double-click the header | Opens table editing |
| Minimap | Drag to move the view |
| Ctrl+= / Ctrl+- | Zoom in / out |
| Ctrl+Shift+0 | Fits the schema to the window |
| Del | Deletes the selected table or FK |
| Esc | Cancels the FK in progress or exits focus mode |
All changes (adding, deleting, FKs…) can be undone with Undo in the Edit menu. Focus mode (Focus table and Focus lookup items of the context menu) highlights a table with its relationships; Esc or Exit focus closes it.
C6. Table roles
From the context menu, Role classifies the table. The role shows up as a label and as the color of the header (in the screenshot MST, master):
| Role | Label |
|---|---|
| Master | (default, MST) |
| Lookup | ◆ Lookup |
| ReadOnly | ■ ReadOnly |
| WriteThrough | ✎ WriteThrough |
| Transaction | ▶ Transaction |
| Operational | ⚙ Operational |
| Audit | ☰ Audit |
| Financial | ¤ Financial |
C7. Semantic graph
The full chapter on semantic configuration is in part G. The ⇄ button in the side bar switches from the physical schema to the semantic graph: a node view that describes the meanings of the tables (lookups, filters, calculated fields) for AI and for code generation, with no effect on the DDL. Hovering over it with the mouse, a tooltip shows the full detail of the node. In the semantic view the ⚙ Global config button appears, for custom constraints and keys not tied to a table.
| Item (context menu) | Function |
|---|---|
| Configure semantics… | Full configurator for the table (also double-click on a node) |
| Link semantic lookup… | Draws a lookup between two precise fields (no FK, no DDL) |
| Import lookups from existing FKs… | Converts FKs into semantic lookups |
| Global semantic configuration… | Custom constraints and keys for the whole schema |
| Configure Vue generation… | Options for Vue code generation |
From the Schema Designer menu: Generate REST, Generate AI prompt (Query menu, Ctrl+Shift+J) and Export semantic package (server)…. To read the schema as facts and dimensions and generate queries with the right JOINs there is the Star schema analysis (facts and dimensions)… item, described in part H.
C8. Generating the DDL
- Press F7 (or Generate DDL in the Schema Designer menu, or right-click the canvas).
- In the Export DDL window choose the Dialect, the server Version and, if needed, Include DROP statements.
- The script appears in the Schema Designer tab of the Editor, with the comment header. You can read it, edit it, copy it or run it with Script (F6): trigger blocks are recognized and not split.
The script includes tables, foreign keys and indexes, and where applicable comments, triggers, views and stored procedures. The data type and auto-increment adapt to the chosen dialect. The Export dialect item of the Schema Designer menu is used for export to the chosen dialect.
C9. Saving, exporting, managing tabs
| Item (Schema Designer menu) | Effect |
|---|---|
| Save schema / Load schema | JSON file (UTF-8) with tables, columns, FKs and positions, plus the star schema model if present (H2). If the file is not valid, the current schema stays as it is. |
| Star schema analysis (facts and dimensions)… | Opens the star schema window: facts, dimensions, grain, measures and queries with generated JOINs (part H) |
| Export PNG / Export SVG | Image of the diagram |
| Clear schema | Clears the whole canvas (can be undone) |
| New schema tab (Ctrl+Alt+N), Close (Ctrl+Alt+W), Rename | Several schemas open together, each in its own tab |
| Add table / FK, Delete selection, Auto layout | As from the side bar and the context menu |
| Zoom in / out / fit | Ctrl+=, Ctrl+-, Ctrl+Shift+0 |
G1. What semantic configuration is for
The semantic configuration teaches the artificial intelligence the vocabulary of your database. It is needed for Text-to-SQL: the operator writes a question in plain language ("how many permanent occupations in Riccione in 2025?") and the AI turns it into an SQL query. Without context the AI would see often cryptic table and field names (SBEREGISTRO2026, IDUBICAZIONE); with the configuration it knows what they mean, how they link together, which filters are mandatory and what things are called in the company's language.
The complete flow is this:
- Question in natural language.
- Table detection: the program picks the configured tables whose name or keywords appear in the question.
- JSON prompt: schema, aliases, lookups, filters, constraints and parameter values are assembled into a single structured document.
- Sending to the AI (Anthropic, OpenAI or Ollama, depending on the active profile) and receiving the SQL.
- Safety check: only a read query is allowed (G12).
- Verification run with a row limit, and the option to save the question/SQL pair as a verified example.
G2. Where to open it
- Configuration of a table: click the ⚙ icon in the table header, or the Configure semantics… item of the context menu (also by double-clicking a node of the semantic graph). The window title shows the table name: Semantic configuration — SBEREGISTRO2026.
- Global configuration: the Global semantic configuration… item of the context menu, or the ⚙ Global config button in the semantic view (C7).
Both windows have Save and Cancel: nothing changes until you press Save.
The settings are split across two levels. Those per table (G3–G9) describe a single table; the global ones (G10) apply to every generated prompt.
G3. General tab
- Tabs: General, Field aliases, Lookups, Calculated, Keyword filter, Fixed filters, Examples
- Table alias
- Description
- Allowed roles [RBAC]
- Table identification keywords
- Active fields
- + All / − None
- Save / Cancel
Table alias
The name the AI must use for the table in queries. It can contain placeholders in double square brackets, as in the screenshot: [[COMUNE]]EREGISTRO[[ANNO]]. This is the typical case of tables that exist in several copies (one per municipality or per year): the placeholder is replaced with the value of the corresponding custom key (G10). The program does not resolve them in the prompt: they stay literal and the AI replaces them following the parameter map.
Description
Free, optional text about what the table represents ("detailed register of the various permanent and temporary occupations and signs of each taxpayer in the tax year"). It is context for the AI, especially useful when the physical name says nothing.
Roles allowed to see this table [RBAC]
Comma-separated roles. Empty means no restriction. A role that is not in the allowed list does not even see the existence of the table. Suggested roles come from the global configuration.
Table identification keywords
One expression per line: the words that, if they appear in the question, cause the table to be included in the prompt. Syntax:
| Symbol | Meaning | Example |
|---|---|---|
+ | OR | insegne+insegna: "insegne" or "insegna" appears |
& | AND | insegne&insegna: both appear |
- | NOT | -occupazione: "occupazione" does not appear |
( ) | Grouping | -(a+b+c): none of the three appears |
space, comma, ; | Implicit AND between two terms | a b is equivalent to a&b |
The precedence is: NOT, then AND, then OR. Each term counts as a whole word or phrase in the text of the question: cup does not trigger inside an unrelated longer word. An empty or malformed expression never triggers anything, without errors.
insegne+oggetti+occupazioni+permanenti,icp: since the comma is an AND, which takes precedence over OR, it reads insegne OR oggetti OR occupazioni OR (permanenti AND icp). If you meant "one of the first four, and icp", write (insegne+oggetti+occupazioni+permanenti)&icp.Besides the keywords, the table is also recognized when its real name appears in the question.
Active fields
The list of the table's fields with a checkbox each. Only the checked fields are shown to the AI (query_fields). Uncheck technical fields, sensitive data and anything that would be noise: fewer fields means more precise queries. + All and − None select or deselect everything in one go.
G4. Field aliases
One row per field, with four columns:
| Column | Meaning |
|---|---|
| Field | The physical field. If you change its name by mistake and it no longer matches a real field, the row is ignored |
| Alias | Domain synonyms, comma-separated. Example: the DESCRIZIONE field of the streets table can have the aliases ubicazione, via, indirizzo. Each synonym leads to the same physical field. |
| Identif. | YES marks the field as the "human name" of the row (for example RAGIONE_SOCIALE for users, DESCRIZIONE for municipalities). It is used to answer "show me the users" without having to say which field to list. |
| Roles | Roles allowed to see the field, comma-separated; empty = no restriction (preview only in the Designer) |
- Each table can have only one human identifier. If you mark more than one, the program keeps the first, deselects the others and tells you when you save.
- An alias is not a lookup: the alias brings different words back to the same field; the lookup (G5) resolves a code through another table.
G5. Lookups
A lookup explains to the AI how to go from a code to its description: for example, from IDCOMUNE to the name of the municipality in the municipalities table. Columns:
| Column | Meaning |
|---|---|
| Field | The field of this table (key towards the other) |
| Ref. table | The table to link. It can contain placeholders such as [[COMUNE]] |
| Ref. field | The corresponding field in the linked table |
| Display field | The field to show (the name, the description) |
| Cardinality | 1:1, 1:N, N:1 (optional). The most common case is N:1: many rows point to the same reference row |
| Join | INNER, LEFT, RIGHT (optional) |
By leaving Cardinality and Join empty you give the AI no invented guarantees: they remain unspecified. Unrecognized values are ignored and the program tells you after saving.
The Import from FK button creates lookups from the foreign keys already present in the schema. At the end of the import it tells you how many it imported and how many it skipped (duplicates and composite foreign keys, which cannot become a simple lookup).
G6. Calculated fields
Fields that do not exist in the table but that the AI can use: each row has a name and an SQL expression valid for the target dialect, referring to real fields. Example:
Name: TIPO_SOGGETTO Expression: CASE WHEN NGIURIDICA=0 THEN 'FISICA' ELSE 'DITTA' END
Double-clicking the expression cell opens a multi-line editor with SQL coloring and the Format button. The program does not verify the expression: if you rename a field, update it by hand.
G7. Keyword filter
A filter that triggers only if the question contains a certain word. Two columns: the Keyword (same syntax as G3: & + - ( )) and the Text, i.e. the WHERE condition. In the text you can use the {keyword} placeholder with single curly braces, which is filled in at runtime with the value taken from the question. Example:
Keyword: inciso
Text: [[COMUNE]]REGISTRO[[ANNO]].DESCRIZIONE_PUBBLICITA LIKE '%{inciso}%'
{inciso} is resolved on the fly from the text of the question; [[COMUNE]] is resolved from the parameter map of the global configuration. They are two different mechanisms, deliberately kept separate.The filters that trigger appear in the prompt as matched_filters. On saving, if a filter's keyword shares no word with the table's keywords (General tab), the program warns you: a keyword filter triggers only if the table has already been included in the prompt, and that depends on the table's keywords, not on the filter's. If no sensible question includes the table, the filter will never trigger.
G8. Fixed filters
One WHERE condition per row, applied always when the table is in the prompt, whatever the operator asks. They are for rules that must never depend on the question:
- logical deletion:
DELETED=0; - active records:
ATTIVO=1; - isolation between customers or municipalities (multi-tenant);
- system records to hide.
The expression must be complete and valid on its own: here there is no {…} placeholder at runtime.
G9. Examples
Question → SQL pairs written by you for this table. This is the intervention that improves the quality of the generated SQL the most, especially when a join or a filter cannot be deduced from the schema alone (a particular CASE, a self-join, the "canonical" way of answering a typical question). Three columns:
| Column | Content |
|---|---|
| Question | The typical question, as an operator would write it |
| SQL | The right query |
| Regression test | YES (default): the example enters the regression suite (G13). NO: it serves only as a model for the AI. Use NO for "style" examples that an automatic comparison would judge as different even if correct. |
The program does not check or run these examples: if the real names of fields or tables change, they must be updated by hand. The examples written here do not enter the answer cache, because nobody has ever actually run them on a database (G13).
G10. Global semantic configuration
- General constraints
- Custom key: Parameter, Key, Value, Default
- Default column
- + Row adds a row to the grid above
- Aggregation aliases
- Prefill standard
- Configurable roles
- Regression suite
- Telemetry
- Save / Cancel
General constraints
Natural-language rules, one per line, valid for every generated prompt. They are the instructions you would give a colleague who writes queries for you: naming conventions, things not to do. The screenshot example requires always using the alias given to a table in all references (SELECT, WHERE, JOIN, subqueries), unique aliases even in subqueries (EP, EP2, EP3) and column aliases in uppercase, with underscores, of at most 30 characters.
Custom keys
A table that resolves the [[PARAMETER]] placeholders used in aliases and filters. Four columns:
| Column | Meaning | Example |
|---|---|---|
| Parameter | The name of the placeholder (without the brackets) | COMUNE |
| Key | The readable label the operator uses in the question. It accepts the same syntax as keywords, so synonyms such as riccione+rn | riccione |
| Value | The text that takes the place of [[PARAMETER]] in queries | SB |
| Default | YES marks the default value of that Parameter | YES |
In the screenshot: the COMUNE parameter has two variants, riccione (value SB, default) and gatteo (value H231); the ANNO parameter is YEAR(TODAY()). So "occupations in Riccione" turns [[COMUNE]]EREGISTRO[[ANNO]] into a table with the Riccione prefix and the current year.
- If the question names no municipality, the variant with Default = YES applies.
- If the question names more than one (for example Riccione and Gatteo), the prompt reports all the values involved and explicitly tells the AI not to silently choose only the default.
Dynamic values
In the Value column you can write a fixed value or a date expression, calculated at the moment you generate the prompt (not when you save: the value is not "frozen"). The recognized expressions are in the drop-down list, and you can also type freely:
| Expression | Meaning |
|---|---|
YEAR(TODAY()) · YEAR(TODAY())-1 · YEAR(TODAY())+1 | Current, previous, next year |
MONTH(TODAY()) · DAY(TODAY()) · QUARTER(TODAY()) · WEEKDAY(TODAY()) | Month, day, quarter, day of the week |
TODAY() · YESTERDAY() · TOMORROW() | Today, yesterday, tomorrow (dd/mm/yyyy) |
FIRST_DAY_OF_MONTH() · LAST_DAY_OF_MONTH() | First and last day of the month |
FIRST_DAY_OF_YEAR() · LAST_DAY_OF_YEAR() | First and last day of the year |
CURRENT_TIME() · CURRENT_TIMESTAMP() | Current time and date-time |
An unrecognized value stays literal (like SB or H231).
Aggregation aliases
A small vocabulary that links natural-language terms to the SQL function: the Word column ("total", "amount", "revenue") and the Function column, chosen from a closed list: SUM, COUNT, AVG, MIN, MAX (or empty). Without it the AI has to guess that "what is the total of the amounts" means SUM(IMPORTO); with the company vocabulary ("revenue" → SUM) there is no doubt. The Prefill standard button adds in one click the most common vocabulary (total and sum → SUM; count and number → COUNT; average → AVG; minimum; maximum), skipping words already present, so you can press it several times without creating duplicates. It is a starting point: every row remains editable and deletable. This vocabulary is always included in the prompt, regardless of the question.
Configurable roles
One role per line (for example administrator, operator, public). It is the list of suggested roles in the General and Field aliases tabs: it is not a constraint, a role typed by hand and not present here remains valid.
Regression suite and Telemetry
The two buttons at the bottom left are separate actions: they are not part of Save and Cancel. Regression suite works on the schema as it is in memory, with no need to save first. Telemetry does not depend on the open schema. See G13.
G11. Trying out the configuration: Generate AI prompt
From the Query → Generate AI prompt menu (Ctrl+Shift+J; a schema must be open in the Designer) the window opens with which you check whether the configuration really works: you see the exact prompt, send it to the AI, check the SQL that comes back and turn it into an example.
- Question
- Keep the context of the previous question
- Preview as role
- Dialect
- Version
- Active AI: profile, provider and model
- ⚙: opens the AI Configuration
- Generate prompt
- Copy
- Send to AI
- Insert in editor
- Test SQL
- ✓ Verify and save as example
- Steps: log of the sending
- JSON preview / AI response
- Outcome and rows of the verification run
- Close
Fields at the top
| Field | Meaning |
|---|---|
| Question | The question as an operator would write it ("how many permanent occupations in Riccione in 2025?"). |
| Keep the context of the previous question | Check it for questions that continue the previous one (now filter them for 2025 too): the prompt carries the previous question and SQL with it. Leave it off for a new question, so as not to drag along an unrelated context. |
| Preview as role | Simulates what a certain role would see (reserved tables and fields are removed from the prompt). The wording on the window says it clearly: verification only, not a real security control. Default: (none — show everything). |
| Dialect, Version | The SQL dialect of the requested query (in the screenshot Firebird) and the server version (Latest (default) = dialect only). A specific version, for example "Firebird 3.0", gives the AI an extra hint (FIREBIRD 3.0+). |
| Active AI | The profile in use, with provider and model (Default (Ollama · gpt-oss:20b)). The ⚙ button opens the AI Configuration (G17) and on return the label updates by itself, without having to reopen the window. With no profile, an orange warning appears pointing to the configuration. |
The buttons, and when they are enabled
| Button | What it does | Enabled when |
|---|---|---|
| Generate prompt | Builds the JSON prompt from the question and the configuration and shows it in JSON preview / AI response. It is the fastest way to understand why a query comes out wrong: look at which tables, filters and parameter values went in. | Always. A new prompt invalidates the previous response: Insert and Verify are turned off |
| Copy | Copies the JSON to the clipboard, to paste it into an external AI (for example in a browser) without using a configured profile. | After a correctly generated prompt |
| Send to AI | Sends the prompt to the provider of the active profile. The Steps log shows the events ("Connecting to Ollama…", "Sending request…"). When the response arrives, the SQL appears in the preview, cleaned of any Markdown code block, and it is checked and tried out (see below). | After a correctly generated prompt |
| Insert in editor | Takes the SQL to an Editor tab. It remains available even if the automatic verification fails, so you can always correct it by hand. | When there is SQL in the preview |
| Test SQL | Tries the SQL in the preview with the same safety checks and the same verification run, without calling the AI. It is useful when the SQL comes from outside: you copied the prompt into a browser, asked for the query there and pasted the response into the preview. There is no automatic correction loop: if it fails you see the reason and decide whether to correct and retry or switch to Send to AI. | Always (with SQL in the preview; otherwise it warns "No SQL to test") |
| ✓ Verify and save as example | Saves the question/SQL pair as a verified example (see below). | Only after a successful verification, from Send to AI or from Test SQL, or when the response comes from the cache |
| Close | Closes the window with no other action. | Always |
What happens after "Send to AI"
- The question may be served from the cache (Found in cache — verification in progress…): in that case there is no call to the AI (G13).
- Otherwise the received SQL goes through the safety check (reads only, G12). If it is rejected, the status shows
✗ Rejected by the safety checks: reason. - If it is allowed, it is run for verification on the current connection, with a row limit. In case of error
✗ Execution failed: messageappears and the error is sent back to the AI for a new attempt (automatic correction). - If execution succeeds, the first rows returned appear in the box at the bottom: it is not a full results panel, but a visual confirmation that the query really runs and produces sensible data.
- If communication with the AI fails, the status says
✗ Communication error with the AI — see aboveand the details are in Steps.
The SQL in the preview can be corrected by hand before inserting it in the editor or re-running Test SQL: what remains in the preview is exactly what is tried and, if valid, saved.
Verify and save as example
The button opens a small window with four choices:
- Table: the "owner" table of the example. The program proposes the one recognized from the question; if it could not recognize any, it says so with a warning, and you must choose it yourself (a question with a JOIN concerns several tables, but the example is saved on only one).
- Question and SQL, editable before saving.
- The Regression test checkbox: if active, the example enters the Regression suite (G9, G13).
With Save the example ends up in the Examples tab of the chosen table, and a message shows the number of examples of the table. It is a verified example: it really ran, so it can also serve as a cached answer.
What the prompt contains
| Section | Source |
|---|---|
tags, question | Dialect and version; the question |
constraints | General constraints (G10) |
tables | Real schema of the included tables (fields, primary keys) plus aliases and description |
custom_semantic / custom_semantic_matches | Custom keys and, for each, whether the question names it |
query_fields | The active fields (G3) |
field_aliases | Field synonyms (G4), human identifier |
lookups | Links between tables (G5) |
calculated_fields | Calculated fields (G6) |
matched_filters | Keyword filters that triggered (G7) |
always_filters | Fixed filters (G8) |
examples | Examples (G9) |
aggregate_aliases | Aggregation vocabulary (G10) |
previous_turn | Previous question and SQL, for conversations (see below) |
- Multi-turn conversation: if the next question refers to the previous one ("now filter them for 2025 too"), the prompt carries the previous question and SQL with it, with the instruction to use them only if the reference is implicit, without forcing a link that is not there.
- Automatic retries: if the SQL is rejected by the safety check or fails in verification, the error is sent back to the AI for a new attempt (the reason for the last error is shown at the end).
- Guardrails: if something essential is missing (unconfigured table, empty alias, field not present in the real table) the program does not silently generate a partial prompt: it reports the error.
G12. Security
- Reads only: the SQL produced by the AI goes through a strict filter. Only one
SELECTis allowed (also withWITH/CTE); anything that could change data or structure, or even just look like multiple statements, is rejected before execution. At most one final;is allowed. The filter is deliberately cautious: when in doubt it rejects, even at the cost of a few false positives on an unusual but legitimate query. - The filter does not verify that the query is correct, only that it is read-only: correctness is seen from the verification run.
- Roles: as said in G3, real enforcement is in the server. The Designer shows only a preview for the chosen role.
- Package for the server (G18): Export semantic package (server)… (Schema Designer menu) saves the entire configuration (tables, global constraints, custom keys) in a portable JSON file, with the real structure (fields, primary keys) needed to build the prompt. A server component can thus regenerate the same prompt on every request, without the Designer. Dynamic values (
YEAR(TODAY())) remain expressions in the package and are calculated by the server on every request.
G13. Verifying over time
Regression suite
Re-runs all the examples with Regression test = YES: the same question is sent back to the AI (same profile and provider as Generate AI prompt), one at a time, and the SQL produced is compared with the expected one. The comparison is textual (spaces and case normalized, final ; ignored), not semantic: two equivalent queries written differently (JOIN versus subquery) come out as different. A "different" result does not necessarily mean "wrong": it is a radar that tells you where to look, not a certifier. Re-run the suite after changing the AI model, constraints or configuration.
Answer cache
Examples created with Verify and save as example (already passed through a real execution) can avoid the call to the AI altogether: if the question matches, the program answers with the verified SQL ("Found in cache — verification in progress, no AI call").
- Exact match: identical normalized question.
- Approximate match: only with very high similarity (0.90) and only if the numbers in the two questions are identical: "invoices 2024" and "invoices 2023" are almost equal as text but different in meaning, and are not confused.
- If after verification you change the configuration (a lookup, a filter…), the example is not discarded but is considered to be revalidated: the program asks the AI for a new answer instead of trusting an old one.
- Examples written by hand in the Examples tab never enter the cache.
Telemetry
The Telemetry button opens an aggregated summary of the events recorded in real use and the list of the most recent ones: it serves to answer "where am I losing time and queries?" without opening the log file by hand. It is not an analytics dashboard: it is the minimal view to get oriented.
G14. How to start: a recommended path
- Global configuration: write the general constraints, define the custom keys (municipality, year…), press Prefill standard for the aggregation aliases and add the company terms.
- For the main tables (not all at once): alias, description, keywords and active fields. Remove the useless fields.
- Press Import from FK in the Lookups tab and complete cardinality and join where known.
- Add the mandatory fixed filters (logical deletion, tenant) and the recurring keyword filters.
- Write 2–3 examples for the difficult patterns.
- Try with Generate AI prompt on real questions: look at the JSON preview and the result; with Verify and save as example the right answers become verified examples.
- Launch the Regression suite and re-run it at every important change.
G15. Common problems
| Symptom | Cause and remedy |
|---|---|
| The right table does not enter the prompt | No keyword (nor the real name) appears in the question. Add synonyms with + and check that you have not used the comma or & where you wanted an OR (G3). |
| The keyword filter does not trigger | The table was not included, or the filter's keyword does not appear in the question. Check the message on saving (G7). |
| The AI uses the wrong municipality | The row with Default = YES is missing for that parameter, or the Key does not match the word in the question (add synonyms with +). |
| The AI invents or ignores fields | Too many active fields: deactivate the useless ones; add aliases and a human identifier; write an example. |
| "No active AI profile" | File → AI Configuration: create a profile and press Activate (G11). |
| The query is rejected ("read-only") | The AI produced multiple statements or something that changes data. Retry or strengthen the general constraints (G12). |
| The suite says "different" but the query is right | The comparison is textual: verify by hand. If the example is only for style, set Regression test = NO (G9). |
| A role still sees a reserved table | In the Designer there is no enforcement, it is only a preview: the restriction applies in the server component (G3). |
G16. The semantic graph
The ⇄ button in the Designer side bar toggles between the physical schema and the semantic graph: an overview of how you have configured the AI. In this view the Editor below the canvas is hidden, to have more space.
- Schema tab (here
AI_SEMANTIC_Schema1; the asterisk indicates unsaved changes) - ⚙ Global config (G10)
- ⇄ to go back to the physical schema
- Node of a configured table (selected: orange border)
- Counters for filters and calculated fields
- Lookup relationship with cardinality and join
- Minimap
- Node without keywords: the table will never be recognized from the text of a question
How to read a node
| Part | Content |
|---|---|
| Header | The table alias in bold (for example [[COMUNE]]EREGISTRO[[ANNO]]); below, in small type, the physical name if it differs from the alias (SBEREGISTRO2026). A table without an alias shows the physical name. |
| First row | The first identification keyword, or — no keyword. |
| N filters chip | How many keyword filters the table has (G7) |
| N calc. chip | How many calculated fields (G6) |
| Tooltip (on mouse hover) | Human identifier, filters, keywords, calculated fields and lookups of the table |
Only tables that have a semantic configuration appear: for this reason, in the graph, a "missing" node means a table not yet configured. The curves are the lookups (G5): they go from the "many" table to the "one" table, with an arrow at the end and, halfway, the cardinality · join label (for example 1:N · LEFT). Double-clicking a node opens the table's configuration; the context menu offers the items listed in C7.
The SVG export in the semantic view proposes the name grafico_semantico.svg.
G17. AI configuration: profiles
All AI functions use a single profile, set from File → AI Configuration (⚙ icon). The active profile applies to Generate AI prompt, Generate schema with AI, Refine schema, the assistant in the tree and the Query Builder, and to the Regression suite. There is no Provider menu or key to readjust window by window: you change AI for everything with a single click on ● Activate.
- Configured profiles (● = active)
- Provider: Anthropic, OpenAI, Ollama
- Model
- Endpoint
- Ollama parameters (Ollama only)
- Context window (num_ctx) and max tokens (num_predict)
- Timeout in ms
- Save profile
- ● Activate
- Close
| Field | Meaning |
|---|---|
| Configured profiles | The list of named profiles (for example "Local Ollama", "Claude cloud"). + New creates one by asking for the name; Delete asks for confirmation. The active profile has the ● dot. |
| Provider | Anthropic (Claude), OpenAI, Ollama (local model, on your computer or on a server of your own) |
| Model | The model name (in the screenshot gpt-oss:20b, an Ollama model; for Claude, for example, claude-sonnet-4-6) |
| API key | For Anthropic and OpenAI. Not needed for Ollama. |
| Endpoint | Address of the service. For local Ollama: http://localhost:11434 |
| Context window (num_ctx) | How many tokens the model can read in a request: in the screenshot 16384. The prompt grows with the number of tables and active fields: if the model "loses" parts of the schema, raise this value (subject to available memory) |
| Max response tokens (num_predict) | Maximum length of the returned SQL (in the screenshot 8192) |
| CPU threads | 0 = automatic |
| Timeout (ms) | Maximum time to wait for the response (in the screenshot 180000, three minutes). It applies to Ollama, where a local model can be slow; for cloud services the program uses its own, shorter limit |
Steps
- Open File → AI Configuration.
- Choose + New (or edit "Default"), give it a name, choose the provider, model and, if needed, key and endpoint.
- Press Save profile, then ● Activate: the program confirms that this profile will be used by every window that calls the AI.
- At the bottom you find Active AI: profile name. With several profiles you activate one at a time.
%APPDATA%\PGSOFT\plyxsql).G18. Exporting the semantic package
The semantic package is a JSON file that contains the whole configuration (configured tables, global constraints, custom keys, roles, aggregation aliases) plus the real structure of the schema (fields and primary keys). A server component reads it to regenerate the same prompt as the Designer for each new question, without the Designer in between.
- From the Schema Designer menu choose Export semantic package (server)….
- If no table has a configuration, a message invites you to configure at least one (right-click the table → ⚙ Configure semantics…) before exporting.
- In the Export semantic package window choose the folder. The proposed name is SchemaName_semantic_package.json (in the example
DEST_semantic_package.json); the file type is JSON. - At the end a message reports how many configured tables were exported.
- The file is written in UTF-8 without BOM, to avoid problems with stricter JSON parsers.
- Dynamic values (
YEAR(TODAY())) remain expressions: the server calculates them on every request (G12). - The package does not contain the database data, nor the AI keys.
- Re-export the package every time you change the configuration: the server works on a copy.
H1. What the star schema is for
A star schema splits the data into two kinds of tables. Facts hold what you measure (revenue, quantity, refunds, stock), one row per event. Dimensions describe the context you read it in: date, product, customer, store, campaign. The fact points to the dimensions with surrogate keys, and dimensions are denormalized: category and brand sit in the same row of DIM_PRODUCT, with no intermediate tables.
In PlyxSQL the star schema is a descriptive layer on top of the Designer tables, like the semantic configuration (part G): it changes neither the DDL nor the data. It does three things:
- it shows the Facts / Dimensions tree: which tables are facts, with their grain, measures and dimension roles;
- it generates the SQL with the right JOINs: you pick attributes and measures from the tree and the program writes the query;
- it checks aggregations: stock does not add up over time, a price never adds up, and two facts are not joined to each other.
The example schema used in this part
All the examples use the script plyxsql_star_schema_extended_firebird3_v4.sql: a small electronics shop with 5 dimensions and 6 facts, 17 foreign keys and 5 check views.
| Table | Role | Rows | Content |
|---|---|---|---|
DIM_DATE | date dimension | 365 | every day of 2026; DATE_KEY is the date as YYYYMMDD |
DIM_CUSTOMER | dimension | 20 | customers with city, region, country and segment |
DIM_PRODUCT | dimension | 10 | products with category (5) and brand |
DIM_STORE | dimension | 5 | stores with channel: Retail, E-commerce, B2B |
DIM_CAMPAIGN | dimension | 5 | campaigns with type, channel, start and end dates |
FACT_SALES | fact | 1,607 | one row per order line: quantity, price, discount, revenue, cost, margin |
FACT_ORDER_HEADER | fact | 786 | one row per order: channel, payment, status (no measures) |
FACT_RETURNS | fact | 137 | one row per return: quantity, refund, reason, resolution |
FACT_INVENTORY_SNAPSHOT | fact | 600 | month-end stock per product and store (10 × 5 × 12) |
FACT_BUDGET | fact | 300 | budget and actuals per month, category and store (12 × 5 × 5) |
FACT_CAMPAIGN_PERFORMANCE | fact | 122 | impressions, clicks, conversions, spend and revenue per campaign and day |
The views in the script (V_SALES_ANALYSIS, V_RETURNS_ANALYSIS, V_LATEST_INVENTORY, V_BUDGET_VS_ACTUAL, V_CAMPAIGN_PERFORMANCE) compute some of the same totals by hand: they work as a cross-check.
isql on a test database. Then, in the Designer, import the schema with Import from database (C3): the 17 FKs are read together with the tables, and the star schema starts from there.H2. Opening the analysis and saving the model
With a Designer open, choose Star schema analysis (facts and dimensions)… from the Schema Designer menu. The item is only enabled when there is a schema in the Designer. The window is modal: you work on the model and the query, then close it.
| What | How it works |
|---|---|
| Where the model lives | In the "star_model" section of the schema file (Save schema, C9). A file saved before this feature opens normally, with an empty model. |
| References | The model refers to tables and columns by identifier, not by name: renaming a table or a column in the Designer does not break it. |
| Changes | If you change the model, the project is marked as modified when you close the window. Model changes are not part of the Undo / Redo history and do not show up in schema comparison. |
| Semantic package | The star model is separate from the semantic configuration: it is not included in the package of G18. |
| Output | The generated SQL can be copied to the clipboard, opened in the Editor or loaded into the Query Builder (H7). |
H3. Building the model
The starting point is the Suggest model from schema button: it reads the foreign keys and the column names and fills in a first draft. It only adds what is missing: what you already wrote in the model is left alone.
How it decides
| Item | Rule |
|---|---|
| Fact | A table with at least two outgoing FKs and a numeric column that is not a key, code or date part. Or a table with a single FK, at least two measures and a name starting with FACT_. Or a table with no measures that has an FK to the date dimension (an event, like the order). |
| Header | A table referenced by another fact (an order header, with lines pointing at it) is proposed as a dimension, not a fact: the finest grain is the lines. |
| Dimension | Every table pointed at by a fact FK. It is suggested as conformed: same meaning and same keys for every fact that uses it, the condition for comparing several facts (H10). The surrogate key is the primary key; the natural key is the UNIQUE column (here CUSTOMER_CODE, PRODUCT_CODE…). |
| Measures | Non-key numeric columns. Names with PRICE, PERC, RATE… are non-additive; names with ON_HAND, STOCK, BALANCE… are semi-additive. In a periodic snapshot every quantity is a level, hence semi-additive, except names that denote a flow (SOLD, RECEIVED…). |
| Degenerate | Document and line numbers (ORDER_NUMBER, ORDER_LINE), text columns left on the fact (RETURN_REASON) and date parts (BUDGET_YEAR): they are not measures, but they are used to group. |
| SCD | A dimension is historized (SCD Type 2) only if it has a pair of validity columns with unambiguous names (VALID_FROM / VALID_TO, VALIDO_DAL / VALIDO_AL) or a current-version flag. START_DATE and END_DATE alone are not enough. |
| Calendar | In the date dimension the Year › Quarter › Month › Day hierarchy comes from the columns CAL_YEAR, CAL_QUARTER, CAL_MONTH, CALENDAR_DATE (or ANNO, TRIMESTRE, MESE, DATA). |
What it suggests on the example schema
| Fact | Type | Dimensions | Measures | Degenerate |
|---|---|---|---|---|
FACT_SALES | transactional | DIM_DATE, DIM_CUSTOMER, DIM_PRODUCT, DIM_STORE | QUANTITY, UNIT_PRICE, DISCOUNT_AMOUNT, REVENUE, COST_AMOUNT, GROSS_MARGIN | ORDER_NUMBER, ORDER_LINE |
FACT_ORDER_HEADER | factless | DIM_DATE, DIM_CUSTOMER, DIM_STORE | — | ORDER_NUMBER, ORDER_CHANNEL, PAYMENT_METHOD, ORDER_STATUS |
FACT_RETURNS | transactional | DIM_DATE, DIM_CUSTOMER, DIM_PRODUCT, DIM_STORE | RETURNED_QUANTITY, REFUND_AMOUNT | ORIGINAL_ORDER_NUMBER, ORIGINAL_ORDER_LINE, RETURN_REASON, RESOLUTION_TYPE |
FACT_INVENTORY_SNAPSHOT | periodic snapshot | DIM_DATE, DIM_PRODUCT, DIM_STORE | QUANTITY_ON_HAND, QUANTITY_RESERVED, QUANTITY_AVAILABLE, QUANTITY_INCOMING, INVENTORY_VALUE | — |
FACT_BUDGET | transactional | DIM_STORE | REVENUE_TARGET, REVENUE_ACTUAL, REVENUE_VARIANCE | BUDGET_YEAR, BUDGET_MONTH, PRODUCT_CATEGORY |
FACT_CAMPAIGN_PERFORMANCE | transactional | DIM_CAMPAIGN, DIM_DATE | IMPRESSIONS, CLICKS, CONVERSIONS, MARKETING_SPEND, ATTRIBUTED_REVENUE, NET_CAMPAIGN_RETURN | — |
All five dimensions are suggested as conformed, and DIM_DATE is recognised as the date dimension. DIM_CAMPAIGN stays without historization: its START_DATE and END_DATE are the campaign dates, not the validity of a version.
- The grain is never suggested: you write it (H4). While it is missing, the model reports error
STAR002. FACT_ORDER_HEADERis suggested as a factless fact: fine for counting orders.FACT_BUDGEThas no FK toDIM_DATE: time is inBUDGET_YEARandBUDGET_MONTH. This matters when comparing with sales (H10).- Derived measures (Margin %, ROAS…) are not suggested: you add them (H11).
The Validate model button checks the model and writes the STARxxx messages in the box at the bottom right (H14). After the suggestion, the example schema still has 6 errors (a missing grain for each fact) and 6 warnings (grain columns not given): once the grains are written and their columns ticked, the model has no messages left.
H4. The grain: what one row is
The grain is the sentence that says what one row of the fact represents. It is written before measures and dimensions, because the grain decides which measures and dimensions make sense. A fact has a single grain: if a table mixes rows from two levels (an order total next to its lines), sums double up.
To write it: select the fact in the tree and fill in the field in the Properties panel, then press Apply to model. The grain columns are the ones that identify the row together and are used to find duplicates: with the fact selected, press Grain columns… and tick the columns. Until you give them, the model reports warning STAR020.
| Fact | Grain (one row is…) | Grain columns |
|---|---|---|
FACT_SALES | One row per order line sold (ORDER_NUMBER + ORDER_LINE) | ORDER_NUMBER, ORDER_LINE |
FACT_ORDER_HEADER | One row per order | ORDER_NUMBER |
FACT_RETURNS | One row per return recorded | RETURN_KEY |
FACT_INVENTORY_SNAPSHOT | One row per product, store and month end | SNAPSHOT_DATE_KEY, PRODUCT_KEY, STORE_KEY |
FACT_BUDGET | One row per month, product category and store | BUDGET_YEAR, BUDGET_MONTH, PRODUCT_CATEGORY, STORE_KEY |
FACT_CAMPAIGN_PERFORMANCE | One row per campaign and day | CAMPAIGN_KEY, DATE_KEY |
Grain columns are not always foreign keys. The budget has BUDGET_YEAR, BUDGET_MONTH and PRODUCT_CATEGORY, which are columns of the fact; the stock fact has the snapshot date next to product and store.
Checking the grain on the data
If the grain is right, no combination of those columns appears twice. For FACT_SALES:
SELECT ORDER_NUMBER, ORDER_LINE, COUNT(*) AS N FROM FACT_SALES GROUP BY ORDER_NUMBER, ORDER_LINE HAVING COUNT(*) > 1
On the example schema the query returns no rows, and the same holds for the other five facts with their grain columns.
The fact type
Next to the grain, the Type field describes how the table grows:
| Type | Meaning | In the example schema |
|---|---|---|
| transactional | One row per event, never changed | FACT_SALES, FACT_RETURNS, FACT_CAMPAIGN_PERFORMANCE, FACT_BUDGET |
| periodic snapshot | One row per subject at every period (the month-end photograph). Requires the time role | FACT_INVENTORY_SNAPSHOT |
| accumulating snapshot | One row per process, updated at every step (ordered, shipped, delivered), with several dates | none |
| factless | Records that an event happened; counted with COUNT(*) | FACT_ORDER_HEADER |
H5. Measures and additivity
A measure is a number of the fact that gets aggregated. Not all of them aggregate the same way: additivity says along which dimensions summing makes sense.
| Additivity | Rule | Examples in the schema |
|---|---|---|
| additive | Sums along every dimension. | REVENUE, QUANTITY, COST_AMOUNT, REFUND_AMOUNT, MARKETING_SPEND |
| semi-additive | Sums across products, stores and categories at the same instant, not over time. Over time you take the last value, the first, the average, the minimum or the maximum. | QUANTITY_ON_HAND, QUANTITY_AVAILABLE, INVENTORY_VALUE |
| non-additive | Never sums: average, minimum, maximum, or a ratio of two aggregates. | UNIT_PRICE, and the derived measures |
Selecting a measure in the tree, the Properties panel shows three fields:
| Field | What it decides |
|---|---|
| Additivity | additive, semi-additive (does not add up over time), non-additive |
| Aggregation | The aggregation used when you do not pick one: SUM, COUNT, COUNT DISTINCT, AVG, MIN, MAX, LAST, FIRST |
| Over time | Only for semi-additive measures: how to aggregate over time (usually LAST) |
The program corrects wrong aggregations and says so. Asking for SUM(UNIT_PRICE) produces AVG(f.UNIT_PRICE) with warning QRY008; asking for SUM on stock over time produces LAST (H9).
UNIT_PRICE the average per category is a plain average of the line prices. For a weighted average price over quantities you need a derived measure: revenue divided by quantity (H11).H6. Dimensions, roles and conformed dimensions
| Dimension | Surrogate key | Natural key | Attributes | Facts using it |
|---|---|---|---|---|
DIM_DATE | DATE_KEY | CALENDAR_DATE | 9 | 5 |
DIM_CUSTOMER | CUSTOMER_KEY | CUSTOMER_CODE | 7 | 3 |
DIM_PRODUCT | PRODUCT_KEY | PRODUCT_CODE | 5 | 3 |
DIM_STORE | STORE_KEY | STORE_CODE | 5 | 5 |
DIM_CAMPAIGN | CAMPAIGN_KEY | CAMPAIGN_CODE | 6 | 1 |
A dimension has a surrogate key (a meaningless number generated by the DW: here the primary key) used by the fact, and a natural key (the code of the source system: CUSTOMER_CODE, PRODUCT_CODE…) used to recognise the same entity at each load. Attributes are the columns you group and filter by.
Conformed dimensions: the facts matrix
A dimension is conformed when it has the same meaning and the same keys in every fact that uses it. This is what lets you put measures of different facts side by side: the revenue of FACT_SALES and the refunds of FACT_RETURNS are compared by category because both point to the same DIM_PRODUCT (H10). The example schema:
| Fact | DIM_DATE | DIM_CUSTOMER | DIM_PRODUCT | DIM_STORE | DIM_CAMPAIGN |
|---|---|---|---|---|---|
FACT_SALES | ● | ● | ● | ● | |
FACT_ORDER_HEADER | ● | ● | ● | ||
FACT_RETURNS | ● | ● | ● | ● | |
FACT_INVENTORY_SNAPSHOT | ● | ● | ● | ||
FACT_BUDGET | ● | ||||
FACT_CAMPAIGN_PERFORMANCE | ● | ● |
Read it by column: DIM_STORE is used by five facts out of six, and FACT_BUDGET is the only one without DIM_DATE.
Roles: the same dimension several times
When a fact points twice to the same dimension (an order date and a ship date, both to DIM_DATE) this is called role-playing. Each use is a role with its own name, and the name is mandatory: without it the model reports STAR006 and the query could not tell which date to use. In the tree the role appears as Order date → DIM_DATE; to rename it, select it and change the field in the Properties panel. For each role the query uses a distinct alias of the dimension.
FACT_SALES uses DATE_KEY and FACT_RETURNS uses RETURN_DATE_KEY, one date per fact, so the role name stays empty. If you added a SHIP_DATE_KEY column with its FK to DIM_DATE to FACT_SALES, the program would suggest the roles Date and Ship date, derived from the column names.History (SCD)
Each attribute of a dimension has a rule for changes: Type 0 never changes; Type 1 overwrites the value (no history); Type 2 creates a new row with validity dates, so past facts stay tied to the version of the time. A dimension with Type 2 attributes must have three columns: validity start, validity end and the current-version flag; if any is missing the model reports STAR014.
In the example schema every attribute is Type 1: no dimension has validity columns. The START_DATE and END_DATE columns of DIM_CAMPAIGN describe the campaign period and are ordinary attributes.
Special members
Every dimension reserves two rows for facts with no match: Unknown (key −1) and Not applicable (key −2). This way no fact is left with a null key and no query loses it. The example schema does not have these rows: every FK points to a real member. The two keys (−1 and −2) and their labels are part of the model and are checked by the program (STAR024 if they are equal).
Hierarchies
A hierarchy is a drill-down path from the most aggregated level to the most detailed. For DIM_DATE the program suggests one, Calendar: Year (CAL_YEAR) › Quarter (CAL_QUARTER) › Month (CAL_MONTH) › Day (CALENDAR_DATE). In the tree the levels appear under the role, and a double-click on a level moves it to Group by.
H7. The “Star schema analysis” window
┌──────────────────────────────────────────────────────────────────────────┐ │ ★ Star schema analysis — facts and dimensions │ ├──────────────────────┬──────────────────────┬────────────────────────────┤ │ [1 Suggest model] │ 5 Group by (rows) │ 9 Generated SQL │ │ [2 Validate model] │ │ (query shape) │ │ │ 6 Measures (columns)│ │ │ 3 Facts/Dimensions │ │ │ │ tree │ 7 Filters │ │ │ │ ├────────────────────────────┤ │ 4 Node properties │ 8 ROLLUP / │ 10 Messages │ │ │ INNER JOIN │ │ │ │ │ 11 [Copy][Editor][Builder] │ └──────────────────────┴──────────────────────┴────────────────────────────┘
- Suggest model from schema — draft of facts and dimensions (H3)
- Validate model — STARxxx messages (H14)
- Facts / Dimensions tree — below it, a hint line for the selected node
- Properties — grain and type of the fact, additivity and aggregations of the measure, role name; Apply to model, + Derived…, Delete derived, Grain columns…
- Group by (rows) — + Attribute, ↑ Up, Remove
- Measures (columns) — + Measure, aggregation of the selected measure, Remove
- Filters — operator, value, + Filter, Remove
- Subtotals (ROLLUP) and Exclude facts with no dimension row (INNER JOIN)
- Generated SQL — updated at every change; the line above states the shape of the query
- Messages — QRYxxx codes of the query (H14)
- Copy SQL, Open in editor, Open in Query Builder, Run, Close
This is a sketch of the window (screenshots are in I7). The tree looks like this on the example schema (lines with … are shortened):
Facts [6]
FACT_SALES [transactional]
Grain: One row per order line sold (ORDER_NUMBER + ORDER_LINE)
Measures [7]
REVENUE [additive · SUM]
UNIT_PRICE [non-additive · AVG]
Margin % [derived]
Row count [COUNT(*)]
Dimensions [4]
DIM_DATE [N:1 · time]
Calendar [hierarchy]
Year [CAL_YEAR]
Quarter [CAL_QUARTER]
CAL_YEAR [SCD1]
…
DIM_PRODUCT [N:1]
CATEGORY [SCD1]
…
Degenerate [2]
ORDER_NUMBER [degenerate]
FACT_INVENTORY_SNAPSHOT [periodic snapshot]
…
Dimensions [5]
DIM_DATE [date · conformed]
Surrogate key: DATE_KEY [PK]
Natural key: CALENDAR_DATE
Attributes [9]
Hierarchies [1]
Special members: Unknown = -1, Not applicable = -2
Used by: FACT_SALES, FACT_ORDER_HEADER, …
How you work
| Action | Effect |
|---|---|
| Double-click an attribute | Adds it to Group by. Works for attributes under a role, hierarchy levels and degenerate columns. |
| Double-click a measure | Adds it to Measures. Row count counts the rows of the fact at its grain, with COUNT(*). |
| Select an attribute and press + Filter | Adds a filter with the operator and value written next to it (H8). |
| Change the aggregation in the Measures list | Select the measure in the list and pick the aggregation: the SQL updates. “default” uses the one in the model. |
Where the SQL goes
- Copy SQL — to the clipboard.
- Open in editor — in an SQL Editor tab, ready to run.
- Run — runs the query on the active connection and shows the result in a window with a grid (at most 5000 rows). An open connection is required. Screenshots of the window are in I7.
- Open in Query Builder — available only for the simple form (one fact, no derived measure, no calculation over time, no ROLLUP): the Builder reads it and draws tables and JOINs. An active connection is required, because the Builder checks the tables on the database; without a connection the SQL goes to the Editor. If the Builder already has tables, it asks for confirmation before replacing them.
The line above the SQL states the shape of the query: simple query, two-level calculation over time (H9) or drill-across (H10). The dialect of the schema decides identifier quoting, ROLLUP syntax and typed NULL values (H12).
H8. The first query: revenue and margin by category and channel
Question: how much did we sell and earn for each product category and sales channel?
- Open FACT_SALES › Dimensions › DIM_PRODUCT and double-click
CATEGORY; then DIM_STORE and double-clickCHANNEL. They appear in Group by, in the order you pick them. - Open FACT_SALES › Measures and double-click
REVENUEandGROSS_MARGIN.
The SQL appears immediately on the right:
SELECT
d_product.CATEGORY AS CATEGORY,
d_store.CHANNEL AS CHANNEL,
SUM(f.REVENUE) AS REVENUE,
SUM(f.GROSS_MARGIN) AS GROSS_MARGIN
FROM FACT_SALES f
LEFT JOIN DIM_PRODUCT d_product ON d_product.PRODUCT_KEY = f.PRODUCT_KEY
LEFT JOIN DIM_STORE d_store ON d_store.STORE_KEY = f.STORE_KEY
GROUP BY d_product.CATEGORY, d_store.CHANNEL
ORDER BY d_product.CATEGORY, d_store.CHANNEL
| CATEGORY | CHANNEL | REVENUE | GROSS_MARGIN |
|---|---|---|---|
| Audio | B2B | 29,865.65 | 8,448.65 |
| Audio | E-commerce | 23,280.25 | 6,681.25 |
| Audio | Retail | 64,796.70 | 18,271.70 |
| Monitor | B2B | 37,878.60 | 9,983.60 |
| Monitor | E-commerce | 50,092.25 | 13,217.25 |
| Monitor | Retail | 136,592.65 | 35,516.65 |
First 6 rows of 15.
- The JOINs are written by the program: from the fact to each chosen dimension, using the fact FK and the dimension surrogate key. It never joins two dimensions to each other.
- They are LEFT JOINs: if a fact finds no dimension row it stays in the result (with a null value) and totals always match those of the fact. With Exclude facts with no dimension row (INNER JOIN) they become
INNER JOIN: use it when the load guarantees Unknown members (H6). - GROUP BY and ORDER BY follow the Group by columns. Aliases are the column and measure names.
- The totals match the script view
V_SALES_ANALYSIS, grouped the same way.
This is a simple query (one fact, plain aggregations): Open in Query Builder is available.
Filters
Question: for each brand, revenue, units and sales lines over the spring for Business or Enterprise customers. Select the attribute to filter in the tree, pick the operator, type the value and press + Filter:
| Attribute | Operator | Value |
|---|---|---|
DIM_CUSTOMER › SEGMENT | IN | Business; Enterprise |
DIM_DATE › CALENDAR_DATE | BETWEEN | 01/03/2026; 31/05/2026 |
With Row count among the measures the SQL becomes:
SELECT
d_product.BRAND AS BRAND,
SUM(f.REVENUE) AS REVENUE,
SUM(f.QUANTITY) AS QUANTITY,
COUNT(*) AS ROW_COUNT
FROM FACT_SALES f
LEFT JOIN DIM_PRODUCT d_product ON d_product.PRODUCT_KEY = f.PRODUCT_KEY
LEFT JOIN DIM_CUSTOMER d_customer ON d_customer.CUSTOMER_KEY = f.CUSTOMER_KEY
LEFT JOIN DIM_DATE d_date ON d_date.DATE_KEY = f.DATE_KEY
WHERE d_customer.SEGMENT IN ('Business', 'Enterprise')
AND d_date.CALENDAR_DATE BETWEEN '2026-03-01' AND '2026-05-31'
GROUP BY d_product.BRAND
ORDER BY d_product.BRAND
| BRAND | REVENUE | QUANTITY | ROW_COUNT |
|---|---|---|---|
| DataFlash | 18,994.30 | 124 | 61 |
| KeyLab | 12,896.55 | 149 | 57 |
| SoundCore | 19,486.15 | 134 | 58 |
| TechNova | 143,038.95 | 133 | 53 |
| ViewMax | 47,002.05 | 135 | 58 |
- Multiple values (for
IN,NOT IN,BETWEEN) are separated by;. - Values are typed without quotes: the program formats them according to the column type. Numbers are checked, text and dates go between quotes (with a doubled quote if the text contains one), and dates typed
dd/mm/yyyybecomeyyyy-mm-dd. - Available operators:
=,<>,<,<=,>,>=,LIKE,IN,NOT IN,BETWEEN,IS NULL,IS NOT NULL. - Filters apply to attributes (the
WHEREclause); there are no filters on the value of a measure (HAVING) yet.
H9. Semi-additive measures: stock
FACT_INVENTORY_SNAPSHOT holds the stock of every product in every store at month end: 12 photographs. Summing QUANTITY_ON_HAND over the whole year adds the same units twelve times, and the result is not a stock figure.
| Category | SUM over the whole year (wrong) | LAST over time (right) |
|---|---|---|
| Audio | 15,308 | 1,542 |
| Monitor | 14,114 | 1,320 |
| Notebook | 15,159 | 1,583 |
| Periferiche | 14,636 | 1,151 |
| Storage | 15,691 | 1,667 |
| Total | 74,908 | 7,263 |
The wrong sum is about ten times the true value. That is why the measure is semi-additive with Over time = LAST: across products and stores it adds up, over time it takes the last photograph.
Question: what is the stock and its value, now, by category? Just pick CATEGORY and the two measures: the program sees that time is not in the grouping and writes a two-level calculation.
WITH base AS (
SELECT
d_product.CATEGORY AS g1,
f.SNAPSHOT_DATE_KEY AS t_key,
SUM(f.QUANTITY_ON_HAND) AS b1,
SUM(f.INVENTORY_VALUE) AS b2
FROM FACT_INVENTORY_SNAPSHOT f
LEFT JOIN DIM_PRODUCT d_product ON d_product.PRODUCT_KEY = f.PRODUCT_KEY
GROUP BY d_product.CATEGORY, f.SNAPSHOT_DATE_KEY
),
bounds AS (
SELECT g1, MAX(t_key) AS t_last, MIN(t_key) AS t_first
FROM base
GROUP BY g1
)
SELECT
b.g1 AS CATEGORY,
SUM(CASE WHEN b.t_key = l.t_last THEN b.b1 END) AS QUANTITY_ON_HAND,
SUM(CASE WHEN b.t_key = l.t_last THEN b.b2 END) AS INVENTORY_VALUE
FROM base b
INNER JOIN bounds l ON (b.g1 = l.g1 OR (b.g1 IS NULL AND l.g1 IS NULL))
GROUP BY b.g1
ORDER BY b.g1
| CATEGORY | QUANTITY_ON_HAND | INVENTORY_VALUE |
|---|---|---|
| Audio | 1,542 | 120,707.00 |
| Monitor | 1,320 | 325,117.00 |
| Notebook | 1,583 | 1,113,301.00 |
| Periferiche | 1,151 | 64,264.00 |
| Storage | 1,667 | 173,284.00 |
- base: sums the measures by category and by instant (one row per category and month end).
- bounds: finds the first and last instant for each category.
- The final query keeps, for each category, only the row of the last instant.
The total (7,263) matches the sum of V_LATEST_INVENTORY, the script view that takes the latest photograph of each product and store.
Time in the grouping
If you group by CAL_QUARTER the calculation becomes “the last photograph of each quarter”:
| CAL_QUARTER | QUANTITY_ON_HAND |
|---|---|
| 1 | 6,021 |
| 2 | 6,272 |
| 3 | 6,245 |
| 4 | 7,263 |
If you group by day (the grain), no calculation is needed and the query goes back to being simple. The same holds with a filter on a single day.
Other aggregations over time
Choosing AVG for the measure, stock becomes the average of the photographs (first sum by instant, then average over time):
| CATEGORY | QUANTITY_ON_HAND |
|---|---|
| Audio | 1,275.67 |
| Monitor | 1,176.17 |
| Notebook | 1,263.25 |
| Periferiche | 1,219.67 |
| Storage | 1,307.58 |
If you ask for SUM on a semi-additive measure with time outside the grouping, the program does not run it: it uses LAST and says so.
W QRY008: "QUANTITY_ON_HAND" ? semi-additiva: si somma per istante, ma lungo il tempo va aggregata con LAST. Sommarla su pi? periodi moltiplica il valore per il numero di periodi. Usata LAST.
- “Last” means the highest time key: the key of the date dimension must grow with time.
DATE_KEYasYYYYMMDDworks; a randomly assigned surrogate key does not. COUNT DISTINCTcannot be computed in two levels: the program refuses it (QRY011).- A snapshot without a time role cannot use
LASTorFIRST(QRY009). The time role is the FK to the date dimension, chosen by the program: if the fact has none, the model reportsSTAR011.
H10. Several facts together: drill-across
Question: for each category, how much did we sell and how much did we refund? Revenue is in FACT_SALES, refunds in FACT_RETURNS. Pick the REVENUE measure of one fact, REFUND_AMOUNT of the other and the CATEGORY attribute of DIM_PRODUCT, which the two facts have in common.
Joining two facts with a JOIN multiplies the rows (each sale repeats for every return of the same line) and totals come out inflated: this is the fan trap. The program never joins two facts. It aggregates each one on its own over the shared dimension and then puts the results side by side:
WITH q1 AS (
SELECT
d_product.CATEGORY AS g1,
SUM(f.REVENUE) AS m1_1
FROM FACT_SALES f
LEFT JOIN DIM_PRODUCT d_product ON d_product.PRODUCT_KEY = f.PRODUCT_KEY
GROUP BY d_product.CATEGORY
),
q2 AS (
SELECT
d_product.CATEGORY AS g1,
SUM(f.REFUND_AMOUNT) AS m2_1
FROM FACT_RETURNS f
LEFT JOIN DIM_PRODUCT d_product ON d_product.PRODUCT_KEY = f.PRODUCT_KEY
GROUP BY d_product.CATEGORY
)
SELECT
u.g1 AS CATEGORY,
MAX(u.m1_1) AS "REVENUE (FACT_SALES)",
MAX(u.m2_1) AS "REFUND_AMOUNT (FACT_RETURNS)"
FROM (
SELECT g1, m1_1, CAST(NULL AS DOUBLE PRECISION) AS m2_1 FROM q1
UNION ALL
SELECT g1, CAST(NULL AS DOUBLE PRECISION) AS m1_1, m2_1 FROM q2
) u
GROUP BY u.g1
ORDER BY u.g1
| CATEGORY | REVENUE (FACT_SALES) | REFUND_AMOUNT (FACT_RETURNS) |
|---|---|---|
| Audio | 117,942.60 | 6,809.20 |
| Monitor | 224,563.50 | 8,743.55 |
| Notebook | 784,707.15 | 47,179.95 |
| Periferiche | 72,743.60 | 3,849.60 |
| Storage | 109,530.25 | 5,918.40 |
- One CTE per fact (
q1,q2), each with its own JOINs to the dimensions. - The CTEs are stacked with
UNION ALLand compacted withMAXper row: there is no JOIN between facts. - The placeholder
NULLs areCAST(NULL AS DOUBLE PRECISION), because Firebird cannot infer the type of a bareNULL(H12). - The headings carry the fact name:
REVENUE (FACT_SALES). - A category present in only one fact would show the other column empty, not zero.
Time in the comparison
Grouping by CAL_QUARTER, each fact uses its own link to the date: revenue by sale date (DATE_KEY), refunds by return date (RETURN_DATE_KEY). A January refund for a December sale falls in the quarter of the return.
| CAL_QUARTER | REVENUE (FACT_SALES) | REFUND_AMOUNT (FACT_RETURNS) |
|---|---|---|
| 1 | 271,409.85 | 17,457.75 |
| 2 | 370,535.45 | 24,967.30 |
| 3 | 354,635.85 | 15,333.20 |
| 4 | 312,905.95 | 14,742.45 |
When the comparison is not possible: the budget
Sales and budget can be compared by channel, because both facts point to DIM_STORE:
| CHANNEL | REVENUE (FACT_SALES) | REVENUE_TARGET (FACT_BUDGET) |
|---|---|---|
| B2B | 262,133.55 | 1,568,118.00 |
| E-commerce | 327,485.85 | 1,540,501.00 |
| Retail | 719,867.70 | 4,750,247.00 |
FACT_BUDGET (about 7.5 million) are much higher than the revenue of FACT_SALES (1.31 million): they are test data that do not reconcile. In a real DW the actuals are not copied into the budget fact, they are read from sales, with a comparison just like this one.By calendar month, instead, the comparison cannot be done. FACT_BUDGET has no FK to DIM_DATE: its time is in BUDGET_YEAR and BUDGET_MONTH. If you pick CAL_MONTH of DIM_DATE together with the measures of both facts, the program answers:
E QRY005: La dimensione "DIM_DATE" non ? collegata alla fact "FACT_BUDGET": i suoi attributi non possono essere usati per analizzarne le misure.
The fix is in the schema, not in the query: give the budget an FK to DIM_DATE (for example BUDGET_DATE_KEY, the first day of the month) and declare it as a role of the fact. From then on the date dimension is shared and the monthly comparison works. The budget on its own, by month, can already be read today with the BUDGET_MONTH column (H11).
| Rule for comparing facts |
|---|
Every Group by attribute must belong to a dimension linked to all the chosen facts (QRY005 otherwise). |
| Degenerate columns do not count: they belong to a single fact. |
If a dimension has several roles in a fact, you must say which (QRY006); with a single role the name is ignored, so the same attribute works for different facts. |
| A derived measure only uses measures of its own fact, but it can sit next to those of other facts: each one is computed in the CTE of its fact. |
H11. Derived measures: ratios of aggregates
A percentage margin, a click rate or a return on spend are ratios: they do not exist as a column and they do not add up. You define them as derived measures, with a formula over measures of the same fact. The program does not suggest them: you add them.
- Select the fact (or one of its measures) in the tree.
- Press + Derived… under the Properties panel.
- Type the name (for example
ROAS) and then the formula.
| Fact | Derived measure | Formula |
|---|---|---|
FACT_SALES | Margin % | 100.0 * {GROSS_MARGIN} / NULLIF({REVENUE}, 0) |
FACT_BUDGET | Variance % | 100.0 * {REVENUE_VARIANCE} / NULLIF({REVENUE_TARGET}, 0) |
FACT_CAMPAIGN_PERFORMANCE | CTR % | 100.0 * {CLICKS} / NULLIF({IMPRESSIONS}, 0) |
FACT_CAMPAIGN_PERFORMANCE | ROAS | {ATTRIBUTED_REVENUE} / NULLIF({MARKETING_SPEND}, 0) |
- In the formula
{{MEASURE}}is the aggregate of the measure (the sum, with the default aggregation), not a row value. The rest is SQL: operators,NULLIF, constants. - Always write
NULLIF(denominator, 0): a group with a zero denominator gives a null value instead of an error. - The program checks the formula: a measure name that does not exist in the fact makes it reject the measure (
STAR010). - The derived measure is non-additive and has no aggregation to choose: the value is always computed on the group.
- To delete it, select the measure and press Delete derived. Measures that correspond to columns cannot be deleted from here.
Why a ratio of aggregates and not an average of ratios
The percentage margin of all sales is the sum of margins divided by the sum of revenue: 27.70%. The average of the line-by-line percentage margins would give 28.55%, because it counts a 20 euro line and a 2,000 euro one as equal. The same goes for the click rate of the campaigns: 2.54% correct against 2.62% with the average of the daily rows.
Example: campaigns
Question: for each campaign, spend, attributed revenue, CTR and ROAS. You pick CAMPAIGN_NAME, the measures MARKETING_SPEND and ATTRIBUTED_REVENUE and the two derived ones:
SELECT
d_campaign.CAMPAIGN_NAME AS CAMPAIGN_NAME,
SUM(f.MARKETING_SPEND) AS MARKETING_SPEND,
SUM(f.ATTRIBUTED_REVENUE) AS ATTRIBUTED_REVENUE,
100.0 * (SUM(f.CLICKS)) / NULLIF((SUM(f.IMPRESSIONS)), 0) AS "CTR %",
(SUM(f.ATTRIBUTED_REVENUE)) / NULLIF((SUM(f.MARKETING_SPEND)), 0) AS ROAS
FROM FACT_CAMPAIGN_PERFORMANCE f
LEFT JOIN DIM_CAMPAIGN d_campaign ON d_campaign.CAMPAIGN_KEY = f.CAMPAIGN_KEY
GROUP BY d_campaign.CAMPAIGN_NAME
ORDER BY d_campaign.CAMPAIGN_NAME
| CAMPAIGN_NAME | MARKETING_SPEND | ATTRIBUTED_REVENUE | CTR % | ROAS |
|---|---|---|---|---|
| Audio Summer | 107,808.52 | 668,136.39 | 2.60 | 6.20 |
| Back to School | 61,848.80 | 497,109.74 | 2.84 | 8.04 |
| Black Friday Tech | 69,085.03 | 377,010.13 | 2.94 | 5.46 |
| Corporate Upgrade | 267,588.53 | 1,699,799.79 | 2.48 | 6.35 |
| Spring Storage | 73,293.74 | 472,148.29 | 2.23 | 6.44 |
The values match V_CAMPAIGN_PERFORMANCE (in the view the CTR is rounded to two decimals).
Example: budget and actuals by month
In the budget the time is in the BUDGET_MONTH and BUDGET_YEAR columns of the fact, recognised as degenerate (H13). On a single fact they can be used as attributes:
SELECT
f.BUDGET_MONTH AS BUDGET_MONTH,
SUM(f.REVENUE_TARGET) AS REVENUE_TARGET,
SUM(f.REVENUE_ACTUAL) AS REVENUE_ACTUAL,
100.0 * (SUM(f.REVENUE_VARIANCE)) / NULLIF((SUM(f.REVENUE_TARGET)), 0) AS "Variance %"
FROM FACT_BUDGET f
GROUP BY f.BUDGET_MONTH
ORDER BY f.BUDGET_MONTH
| BUDGET_MONTH | REVENUE_TARGET | REVENUE_ACTUAL | Variance % |
|---|---|---|---|
| 1 | 638,703.00 | 623,183.23 | -2.43 |
| 2 | 699,041.00 | 674,052.95 | -3.57 |
| 3 | 641,032.00 | 625,490.01 | -2.42 |
| 4 | 608,257.00 | 571,909.76 | -5.98 |
| 5 | 649,732.00 | 632,342.24 | -2.68 |
| 6 | 708,807.00 | 685,392.78 | -3.30 |
| 7 | 648,170.00 | 632,030.04 | -2.49 |
| 8 | 604,988.00 | 545,198.63 | -9.88 |
| 9 | 616,928.00 | 565,382.29 | -8.36 |
| 10 | 717,971.00 | 675,888.53 | -5.86 |
| 11 | 770,428.00 | 728,363.20 | -5.46 |
| 12 | 554,809.00 | 545,444.45 | -1.69 |
The variance is negative when actuals are below budget. The values match V_BUDGET_VS_ACTUAL.
Example: percentage margin by quarter and category
SELECT
d_date.CAL_QUARTER AS CAL_QUARTER,
d_product.CATEGORY AS CATEGORY,
SUM(f.REVENUE) AS REVENUE,
SUM(f.GROSS_MARGIN) AS GROSS_MARGIN,
100.0 * (SUM(f.GROSS_MARGIN)) / NULLIF((SUM(f.REVENUE)), 0) AS "Margin %"
FROM FACT_SALES f
LEFT JOIN DIM_DATE d_date ON d_date.DATE_KEY = f.DATE_KEY
LEFT JOIN DIM_PRODUCT d_product ON d_product.PRODUCT_KEY = f.PRODUCT_KEY
GROUP BY d_date.CAL_QUARTER, d_product.CATEGORY
ORDER BY d_date.CAL_QUARTER, d_product.CATEGORY
| CAL_QUARTER | CATEGORY | REVENUE | GROSS_MARGIN | Margin % |
|---|---|---|---|---|
| 1 | Audio | 24,413.45 | 6,976.45 | 28.58 |
| 1 | Monitor | 42,887.05 | 11,396.05 | 26.57 |
| 1 | Notebook | 155,802.90 | 42,845.90 | 27.50 |
| 1 | Periferiche | 14,882.55 | 4,286.55 | 28.80 |
| 1 | Storage | 33,423.90 | 10,266.90 | 30.72 |
| 2 | Audio | 28,227.30 | 7,783.30 | 27.57 |
| 2 | Monitor | 55,435.40 | 13,977.40 | 25.21 |
| 2 | Notebook | 241,426.35 | 65,145.35 | 26.98 |
First 8 rows of 20.
A query with derived measures is not a simple query: Open in Query Builder stays disabled and the SQL opens in the Editor.
H12. Subtotals and dialects
With Subtotals (ROLLUP) the same query also returns partial totals and the grand total. Example: revenue by category and channel, with PostgreSQL as the dialect:
SELECT
d_product.CATEGORY AS CATEGORY,
d_store.CHANNEL AS CHANNEL,
SUM(f.REVENUE) AS REVENUE
FROM FACT_SALES f
LEFT JOIN DIM_PRODUCT d_product ON d_product.PRODUCT_KEY = f.PRODUCT_KEY
LEFT JOIN DIM_STORE d_store ON d_store.STORE_KEY = f.STORE_KEY
GROUP BY ROLLUP (d_product.CATEGORY, d_store.CHANNEL)
ORDER BY d_product.CATEGORY, d_store.CHANNEL
| CATEGORY | CHANNEL | REVENUE |
|---|---|---|
| Audio | B2B | 29,865.65 |
| Audio | E-commerce | 23,280.25 |
| Audio | Retail | 64,796.70 |
| Audio | NULL | 117,942.60 |
| NULL | NULL | 1,309,487.10 |
- A row with
NULLin the last grouping column is the subtotal of the category; withNULLin all of them it is the grand total. The grand total equals the sum ofFACT_SALES. - Where the
NULLs sort depends on the engine. - ROLLUP only applies to the simple query: with several facts or with the calculation over time it is ignored with a warning (
QRY012); with a semi-additive measure and time in the grouping it warns that subtotals are not right (QRY014). - With LEFT JOIN, a fact with no dimension row produces a
NULLthat looks like a subtotal (QRY018). With Unknown members loaded by the DW, or with INNER JOIN, the ambiguity goes away.
What changes from one dialect to another
| Dialect | ROLLUP | Typed NULLs (drill-across) |
|---|---|---|
| Firebird, InterBase | not supported: ignored with a warning (QRY013) | yes: CAST(NULL AS DOUBLE PRECISION) |
| SQLite, MS Access | not supported (QRY013) | no |
| MySQL, MariaDB | GROUP BY a, b WITH ROLLUP (without ORDER BY, QRY017) | no |
| PostgreSQL, SQL Server, Oracle | GROUP BY ROLLUP (a, b) | no |
| IBM DB2 | GROUP BY ROLLUP (a, b) | yes |
On the Firebird dialect of the example schema the same request returns the SQL without ROLLUP and this warning:
W QRY013: ROLLUP ignorato: il dialetto del database non lo supporta.
The dialect also decides identifier quoting: a name like "Margin %" or a reserved word is quoted according to the rules of the engine, and a plain name is not.
H13. Degenerate dimensions and factless facts
A degenerate dimension is a column that sits on the fact and works as an attribute, with no dimension table behind it: the order number, the line number, the reason for a return, the status of an order. In the tree they are in the fact’s Degenerate folder and are used like any attribute.
Returns by reason
Question: how many returns and how much refunded for each reason? Pick RETURN_REASON (degenerate), Row count and REFUND_AMOUNT:
SELECT
f.RETURN_REASON AS RETURN_REASON,
COUNT(*) AS ROW_COUNT,
SUM(f.REFUND_AMOUNT) AS REFUND_AMOUNT
FROM FACT_RETURNS f
GROUP BY f.RETURN_REASON
ORDER BY f.RETURN_REASON
| RETURN_REASON | ROW_COUNT | REFUND_AMOUNT |
|---|---|---|
| Cambio idea | 29 | 10,907.15 |
| Danno trasporto | 28 | 15,054.05 |
| Errore ordine | 33 | 22,626.60 |
| Non conforme | 26 | 14,468.30 |
| Prodotto difettoso | 21 | 9,444.60 |
Row count counts the rows of the fact: in FACT_RETURNS a row is a return, so the count is the number of returns (137 in all).
Orders: a factless fact
FACT_ORDER_HEADER has no numeric columns to sum: it records that an order happened, when, for whom and where. It is a factless fact, and it is queried by counting rows. Question: how many orders by payment method and status?
SELECT
f.PAYMENT_METHOD AS PAYMENT_METHOD,
f.ORDER_STATUS AS ORDER_STATUS,
COUNT(*) AS ROW_COUNT
FROM FACT_ORDER_HEADER f
GROUP BY f.PAYMENT_METHOD, f.ORDER_STATUS
ORDER BY f.PAYMENT_METHOD, f.ORDER_STATUS
| PAYMENT_METHOD | ORDER_STATUS | ROW_COUNT |
|---|---|---|
| Bonifico | Annullato | 54 |
| Bonifico | Consegnato | 46 |
| Bonifico | In preparazione | 49 |
| Bonifico | In transito | 62 |
| Carta | Annullato | 51 |
| Carta | Consegnato | 47 |
| Carta | In preparazione | 43 |
| Carta | In transito | 51 |
First 8 rows of 16.
The total is 786 rows, as many as the distinct orders in FACT_SALES: every order has its header. To analyze orders by month, customer or store just add the attributes of the linked dimensions (DIM_DATE, DIM_CUSTOMER, DIM_STORE).
- A degenerate column belongs to a single fact: it is used in queries on that fact, not in comparisons between several facts (H10).
- Text columns like
PAYMENT_METHODandORDER_STATUSwork as they are. If many columns of this kind pile up on a fact, moving them to a small dimension (a junk dimension) makes the fact narrower and the values reusable.
H14. Messages and codes
Every message has a stable code. STARxxx concern the model (written by Validate model and shown in the tree, in the Model problems node); QRYxxx concern the query being built (in the Messages box). An error blocks the generation of the SQL or the item concerned; a warning lets the SQL be generated but says it was corrected or that the result must be read with care; an info is just a note.
Model (STAR)
| Code | Kind | Meaning |
|---|---|---|
STAR001 | error | Table declared as fact or dimension but missing from the schema. |
STAR002 | error | Fact grain not declared. |
STAR003 | warning | Fact with no dimensions. |
STAR004 | error | Role pointing to an undeclared dimension. |
STAR005 | error | Role FK column missing or not in the fact. |
STAR006 | error | Same dimension used several times without distinct role names. |
STAR007 | error / warning | Measure with no column (error) or with a non-numeric column (warning). |
STAR008 | warning | Semi-additive measure with no aggregation over time. |
STAR009 | error | SUM aggregation on a non-additive or derived measure. |
STAR010 | error / warning | Derived measure: reference to a measure that does not exist (error); nested derived measure or inconsistent additivity (warning). |
STAR011 | error | Periodic snapshot without a main time role. |
STAR012 | error / warning | Surrogate key missing (error) or different from the primary key (warning). |
STAR013 | warning | Dimension without a natural key. |
STAR014 | error | Type 2 attributes without validity columns (start, end, current version). |
STAR015 | error | Type 2 dimension whose surrogate key equals the natural key. |
STAR016 | error | Hierarchy level on a column that does not belong to the dimension. |
STAR017 | error | Same table declared as both fact and dimension. |
STAR018 | info | No physical FK between fact and dimension (acceptable in a DW). |
STAR019 | error | Duplicate measure names in the same fact. |
STAR020 | error / warning | Grain columns that do not exist (error) or not given (warning). |
STAR021 | info | Dimension not used by any fact. |
STAR022 | info | Snowflake: dimension with an FK to another dimension. |
STAR023 | warning | Surrogate key of the date dimension is not an integer. |
STAR024 | error | Unknown and Not applicable members with the same key. |
STAR025 | error | Column used both as a measure and as a dimension FK. |
STAR026 | warning | Time role that matches no role of the fact. |
STAR027 | error | Attribute, key or validity column that does not belong to the dimension. |
Query (QRY)
| Code | Kind | Meaning |
|---|---|---|
QRY001 | error | No measure requested. |
QRY002 | error | Fact not declared in the model, or table missing from the schema. |
QRY003 | error | Measure does not exist, or a component of a derived measure does not exist. |
QRY004 | error | Attribute of a table that is not declared as a dimension. |
QRY005 | error | Attribute of a dimension that is not linked to the fact (or to one of the facts). |
QRY006 | error | Ambiguous role: dimension with several roles and a missing or wrong role name. |
QRY007 | error | Attribute or measure column missing. |
QRY008 | warning | Aggregation corrected by the additivity rule (for example SUM → AVG, SUM → LAST). |
QRY009 | error | LAST or FIRST requested without a time role. |
QRY010 | warning | Semi-additive measure in a fact without a time role. |
QRY011 | error | COUNT DISTINCT cannot be computed in the two-level calculation. |
QRY012 | warning | ROLLUP ignored: the query is not simple. |
QRY013 | warning | ROLLUP not supported by the dialect. |
QRY014 | warning | ROLLUP with a semi-additive measure and time in the grouping. |
QRY015 | error | JOIN impossible: the surrogate key of the dimension cannot be determined. |
QRY016 | error | Filter with missing values or values that are not valid for the column type. |
QRY017 | info | With WITH ROLLUP (MySQL) the ORDER BY is left out. |
QRY018 | info | ROLLUP with LEFT JOIN: NULLs of missing members look like subtotals. |
H15. Limits and common problems
Current limits
| Limit | Detail |
|---|---|
| “Last” over time is the highest time key. | The key of the date dimension must grow with time (YYYYMMDD works). |
| SCD Type 2: “as it was” reading. | The query joins with the surrogate key stored in the fact, so it shows the attributes of the version at the time. There is no “as it is today” reading on the current version yet. |
| Filters on attributes only. | No filters on the value of a measure (HAVING). |
| Query Builder: simple form only. | One fact, no derived measure, no calculation over time, no ROLLUP. |
| Comparing facts. | Only with attributes of dimensions linked to all the facts; degenerate columns do not count. |
| ROLLUP. | Only on the simple query and in dialects that support it. |
| Names of facts and dimensions. | In the tree they carry the table name. |
| Loading data does not use the model yet. | ETL (part D) does not yet generate surrogate keys, SCD2 versions, Unknown members or the date dimension from this model: the model describes the DW, it does not load it. |
Common problems
| Symptom | Cause | What to do |
|---|---|---|
| Suggest model does not find a fact I expect. | The table has no outgoing FKs in the Designer (they are often absent in a DW) or no non-key numeric columns. | Add the FKs in the Designer (C4) and press the button again: it only adds what is missing. |
STAR002 on every fact. | The grain is never suggested. | Write it in the Properties panel (H4). |
| Open in Query Builder is disabled. | The query is not simple (derived measures, several facts, calculation over time, ROLLUP) or the Builder is not available. | Use Open in editor. |
| I ask for SUM and get AVG or LAST. | The measure is non-additive or semi-additive (QRY008). | If the measure really is additive, fix the additivity in the Properties panel. |
QRY005: dimension not linked. | One of the chosen facts has no role towards that dimension. | Pick an attribute of a shared dimension, or link the fact (H10). |
| Rows with null dimension values show up. | Facts with no row in the dimension, kept by the LEFT JOIN. | Load the Unknown members, or turn on Exclude facts with no dimension row (H8). |
| After reopening the schema the model is empty. | The schema was saved before filling in the model, or not saved afterwards. | The model lives in the schema file: use Save schema (C9). |
H16. Connecting the ETL to the model
The star model knows the surrogate keys, natural keys, grain and versions (SCD) of every dimension. The ETL → Link to the star schema… item compares the open ETL project with the model of the active Schema Designer and reports what does not match; Apply fixes corrects what can be corrected automatically.
What it does
- Facts: for each FK it sets a lookup from the natural key (source field) to the dimension’s surrogate key, run on the destination, falling back to the Unknown member (−1). If the dimension is SCD2 the lookup picks the current version.
- Grain: the grain columns become the idempotency key of the fact phase, so re-running the ETL does not duplicate rows.
- Dimensions: the phase writes into a staging table
STG_<dimension>(cleared first, without duplicates) and the SQL after import inserts the Unknown/Not applicable members and applies SCD1 and SCD2. - Order: dimension phases must come before the fact phases that use them.
The same check runs in Validate project once the window has been opened at least once in this session: messages appear with the ETLSTARnnn code.
Example: DIM_CUSTOMER (SCD1)
With natural key CUSTOMER_CODE and type 1 attributes, the SQL generated for Firebird is the following. Statements are separated by the -- ETL:NEXT line, because the ETL engine runs one at a time.
INSERT INTO "DIM_CUSTOMER" ("CUSTOMER_KEY", "CUSTOMER_CODE", "CUSTOMER_NAME", "SEGMENT") SELECT -1, '-1', 'Sconosciuto', 'Sconosciuto' FROM RDB$DATABASE WHERE NOT EXISTS (SELECT 1 FROM "DIM_CUSTOMER" WHERE "CUSTOMER_KEY" = -1)
-- ETL:NEXT
INSERT INTO "DIM_CUSTOMER" ("CUSTOMER_KEY", "CUSTOMER_CODE", "CUSTOMER_NAME", "SEGMENT") SELECT -2, '-2', 'Non applicabile', 'Non applicabile' FROM RDB$DATABASE WHERE NOT EXISTS (SELECT 1 FROM "DIM_CUSTOMER" WHERE "CUSTOMER_KEY" = -2)
-- ETL:NEXT
UPDATE "DIM_CUSTOMER" SET "CUSTOMER_NAME" = (SELECT STG_."CUSTOMER_NAME" FROM "STG_DIM_CUSTOMER" STG_ WHERE STG_."CUSTOMER_CODE" = "DIM_CUSTOMER"."CUSTOMER_CODE"), "EMAIL" = (SELECT STG_."EMAIL" FROM "STG_DIM_CUSTOMER" STG_ WHERE STG_."CUSTOMER_CODE" = "DIM_CUSTOMER"."CUSTOMER_CODE"), "CITY" = (SELECT STG_."CITY" FROM "STG_DIM_CUSTOMER" STG_ WHERE STG_."CUSTOMER_CODE" = "DIM_CUSTOMER"."CUSTOMER_CODE"), "REGION" = (SELECT STG_."REGION" FROM "STG_DIM_CUSTOMER" STG_ WHERE STG_."CUSTOMER_CODE" = "DIM_CUSTOMER"."CUSTOMER_CODE"), "COUNTRY" = (SELECT STG_."COUNTRY" FROM "STG_DIM_CUSTOMER" STG_ WHERE STG_."CUSTOMER_CODE" = "DIM_CUSTOMER"."CUSTOMER_CODE"), "SEGMENT" = (SELECT STG_."SEGMENT" FROM "STG_DIM_CUSTOMER" STG_ WHERE STG_."CUSTOMER_CODE" = "DIM_CUSTOMER"."CUSTOMER_CODE"), "ACQUISITION_DATE" = (SELECT STG_."ACQUISITION_DATE" FROM "STG_DIM_CUSTOMER" STG_ WHERE STG_."CUSTOMER_CODE" = "DIM_CUSTOMER"."CUSTOMER_CODE") WHERE EXISTS (SELECT 1 FROM "STG_DIM_CUSTOMER" STG_ WHERE STG_."CUSTOMER_CODE" = "DIM_CUSTOMER"."CUSTOMER_CODE" AND ((STG_."CUSTOMER_NAME" <> "DIM_CUSTOMER"."CUSTOMER_NAME" OR (STG_."CUSTOMER_NAME" IS NULL AND "DIM_CUSTOMER"."CUSTOMER_NAME" IS NOT NULL) OR (STG_."CUSTOMER_NAME" IS NOT NULL AND "DIM_CUSTOMER"."CUSTOMER_NAME" IS NULL)) OR (STG_."EMAIL" <> "DIM_CUSTOMER"."EMAIL" OR (STG_."EMAIL" IS NULL AND "DIM_CUSTOMER"."EMAIL" IS NOT NULL) OR (STG_."EMAIL" IS NOT NULL AND "DIM_CUSTOMER"."EMAIL" IS NULL)) OR (STG_."CITY" <> "DIM_CUSTOMER"."CITY" OR (STG_."CITY" IS NULL AND "DIM_CUSTOMER"."CITY" IS NOT NULL) OR (STG_."CITY" IS NOT NULL AND "DIM_CUSTOMER"."CITY" IS NULL)) OR (STG_."REGION" <> "DIM_CUSTOMER"."REGION" OR (STG_."REGION" IS NULL AND "DIM_CUSTOMER"."REGION" IS NOT NULL) OR (STG_."REGION" IS NOT NULL AND "DIM_CUSTOMER"."REGION" IS NULL)) OR (STG_."COUNTRY" <> "DIM_CUSTOMER"."COUNTRY" OR (STG_."COUNTRY" IS NULL AND "DIM_CUSTOMER"."COUNTRY" IS NOT NULL) OR (STG_."COUNTRY" IS NOT NULL AND "DIM_CUSTOMER"."COUNTRY" IS NULL)) OR (STG_."SEGMENT" <> "DIM_CUSTOMER"."SEGMENT" OR (STG_."SEGMENT" IS NULL AND "DIM_CUSTOMER"."SEGMENT" IS NOT NULL) OR (STG_."SEGMENT" IS NOT NULL AND "DIM_CUSTOMER"."SEGMENT" IS NULL)) OR (STG_."ACQUISITION_DATE" <> "DIM_CUSTOMER"."ACQUISITION_DATE" OR (STG_."ACQUISITION_DATE" IS NULL AND "DIM_CUSTOMER"."ACQUISITION_DATE" IS NOT NULL) OR (STG_."ACQUISITION_DATE" IS NOT NULL AND "DIM_CUSTOMER"."ACQUISITION_DATE" IS NULL))))
-- ETL:NEXT
INSERT INTO "DIM_CUSTOMER" ("CUSTOMER_CODE", "CUSTOMER_NAME", "EMAIL", "CITY", "REGION", "COUNTRY", "SEGMENT", "ACQUISITION_DATE") SELECT STG_."CUSTOMER_CODE", STG_."CUSTOMER_NAME", STG_."EMAIL", STG_."CITY", STG_."REGION", STG_."COUNTRY", STG_."SEGMENT", STG_."ACQUISITION_DATE" FROM "STG_DIM_CUSTOMER" STG_ WHERE NOT EXISTS (SELECT 1 FROM "DIM_CUSTOMER" DIM_ WHERE STG_."CUSTOMER_CODE" = DIM_."CUSTOMER_CODE")
Example: the same dimension with CITY tracked (SCD2)
With CITY as type 2 and the columns VALID_FROM, VALID_TO, IS_CURRENT: it closes the current row when the city changes, overwrites type 1 attributes on all versions and inserts new or closed keys.
UPDATE "DIM_CUSTOMER" SET "VALID_TO" = CURRENT_DATE, "IS_CURRENT" = 0 WHERE "IS_CURRENT" = 1 AND EXISTS (SELECT 1 FROM "STG_DIM_CUSTOMER" STG_ WHERE STG_."CUSTOMER_CODE" = "DIM_CUSTOMER"."CUSTOMER_CODE" AND ((STG_."CITY" <> "DIM_CUSTOMER"."CITY" OR (STG_."CITY" IS NULL AND "DIM_CUSTOMER"."CITY" IS NOT NULL) OR (STG_."CITY" IS NOT NULL AND "DIM_CUSTOMER"."CITY" IS NULL))))
-- ETL:NEXT
UPDATE "DIM_CUSTOMER" SET "CUSTOMER_NAME" = (SELECT STG_."CUSTOMER_NAME" FROM "STG_DIM_CUSTOMER" STG_ WHERE STG_."CUSTOMER_CODE" = "DIM_CUSTOMER"."CUSTOMER_CODE"), "EMAIL" = (SELECT STG_."EMAIL" FROM "STG_DIM_CUSTOMER" STG_ WHERE STG_."CUSTOMER_CODE" = "DIM_CUSTOMER"."CUSTOMER_CODE"), "REGION" = (SELECT STG_."REGION" FROM "STG_DIM_CUSTOMER" STG_ WHERE STG_."CUSTOMER_CODE" = "DIM_CUSTOMER"."CUSTOMER_CODE"), "COUNTRY" = (SELECT STG_."COUNTRY" FROM "STG_DIM_CUSTOMER" STG_ WHERE STG_."CUSTOMER_CODE" = "DIM_CUSTOMER"."CUSTOMER_CODE"), "SEGMENT" = (SELECT STG_."SEGMENT" FROM "STG_DIM_CUSTOMER" STG_ WHERE STG_."CUSTOMER_CODE" = "DIM_CUSTOMER"."CUSTOMER_CODE"), "ACQUISITION_DATE" = (SELECT STG_."ACQUISITION_DATE" FROM "STG_DIM_CUSTOMER" STG_ WHERE STG_."CUSTOMER_CODE" = "DIM_CUSTOMER"."CUSTOMER_CODE") WHERE EXISTS (SELECT 1 FROM "STG_DIM_CUSTOMER" STG_ WHERE STG_."CUSTOMER_CODE" = "DIM_CUSTOMER"."CUSTOMER_CODE" AND ((STG_."CUSTOMER_NAME" <> "DIM_CUSTOMER"."CUSTOMER_NAME" OR (STG_."CUSTOMER_NAME" IS NULL AND "DIM_CUSTOMER"."CUSTOMER_NAME" IS NOT NULL) OR (STG_."CUSTOMER_NAME" IS NOT NULL AND "DIM_CUSTOMER"."CUSTOMER_NAME" IS NULL)) OR (STG_."EMAIL" <> "DIM_CUSTOMER"."EMAIL" OR (STG_."EMAIL" IS NULL AND "DIM_CUSTOMER"."EMAIL" IS NOT NULL) OR (STG_."EMAIL" IS NOT NULL AND "DIM_CUSTOMER"."EMAIL" IS NULL)) OR (STG_."REGION" <> "DIM_CUSTOMER"."REGION" OR (STG_."REGION" IS NULL AND "DIM_CUSTOMER"."REGION" IS NOT NULL) OR (STG_."REGION" IS NOT NULL AND "DIM_CUSTOMER"."REGION" IS NULL)) OR (STG_."COUNTRY" <> "DIM_CUSTOMER"."COUNTRY" OR (STG_."COUNTRY" IS NULL AND "DIM_CUSTOMER"."COUNTRY" IS NOT NULL) OR (STG_."COUNTRY" IS NOT NULL AND "DIM_CUSTOMER"."COUNTRY" IS NULL)) OR (STG_."SEGMENT" <> "DIM_CUSTOMER"."SEGMENT" OR (STG_."SEGMENT" IS NULL AND "DIM_CUSTOMER"."SEGMENT" IS NOT NULL) OR (STG_."SEGMENT" IS NOT NULL AND "DIM_CUSTOMER"."SEGMENT" IS NULL)) OR (STG_."ACQUISITION_DATE" <> "DIM_CUSTOMER"."ACQUISITION_DATE" OR (STG_."ACQUISITION_DATE" IS NULL AND "DIM_CUSTOMER"."ACQUISITION_DATE" IS NOT NULL) OR (STG_."ACQUISITION_DATE" IS NOT NULL AND "DIM_CUSTOMER"."ACQUISITION_DATE" IS NULL))))
-- ETL:NEXT
INSERT INTO "DIM_CUSTOMER" ("CUSTOMER_CODE", "CUSTOMER_NAME", "EMAIL", "REGION", "COUNTRY", "SEGMENT", "ACQUISITION_DATE", "CITY", "VALID_FROM", "VALID_TO", "IS_CURRENT") SELECT STG_."CUSTOMER_CODE", STG_."CUSTOMER_NAME", STG_."EMAIL", STG_."REGION", STG_."COUNTRY", STG_."SEGMENT", STG_."ACQUISITION_DATE", STG_."CITY", CURRENT_DATE, CAST('9999-12-31' AS DATE), 1 FROM "STG_DIM_CUSTOMER" STG_ WHERE NOT EXISTS (SELECT 1 FROM "DIM_CUSTOMER" DIM_ WHERE STG_."CUSTOMER_CODE" = DIM_."CUSTOMER_CODE" AND DIM_."IS_CURRENT" = 1)
Messages
| Code | Meaning |
|---|---|
ETLSTAR001 | A fact FK is not mapped |
ETLSTAR002/003 | The FK is copied directly, or the lookup points to the wrong place |
ETLSTAR004/005 | Lookup without a fallback (Unknown member) or one that does not pick the current SCD2 version |
ETLSTAR006/007 | Re-running the ETL duplicates facts; the idempotency key differs from the grain |
ETLSTAR008 | The fact phase comes before the dimension phase |
ETLSTAR009-014 | Dimension written directly, no natural keys, staging not cleared or not deduplicated, natural key or attribute unmapped |
ETLSTAR015-017 | SQL after import missing, different from the generated one, or not on the destination connection |
ETLSTAR018/019 | Dimension used but loaded by no phase; error generating the SQL |
What you have to do
- Create in the destination database the
STG_<dimension>tables with the natural keys and attributes (the tool does not generate DDL). - Create the ETL phases (one per dimension into staging, one per fact) and map the fields: phases are not created automatically.
- Open the window, read the messages, press Apply fixes, save the ETL project.
Limits
- The surrogate key must be generated by the database (identity); the −1/−2 members are inserted explicitly (in Firebird a
BY DEFAULTidentity column allows it). - The SCD2 lookup picks the current version, not the one valid at the fact date (no point-in-time).
- The natural key must be unique in staging (the automatic fix sets deduplication). An SCD2 dimension cannot have UNIQUE on the natural key.
- The SQL is standard (UPDATE/INSERT with subqueries, no MERGE) and was tried on DuckDB; dialect differences only concern the dummy table, dates and booleans.
D1. What ETL is
The ETL (Extract, Transform, Load) mode copies and transforms data from a source connection to a destination connection, even of different engines (for example from Firebird to Firebird, or from a CSV file to PostgreSQL). An ETL project is made of:
- Stages: each stage copies a source table (or query) into a destination table.
- Field rules: for each destination field you say where the value comes from (direct copy, constant, expression, lookup, etc.).
- Execution options: emptying the destination, write mode, transactions, error handling.
- Scheduling: optional, to run the project automatically.
The project is saved in a .fdmap file; the schedule in a .petl file. The menus in ETL mode are: File, Settings, ETL and ? (see ch. 37).
D2. The interface
- ETL toolbar (same commands as the ETL menu)
- Mode tabs: ETL active
- Filter stages… searches among stages and fields
- Expand / collapse everything or the current stage
- Highlighted rule: a transformation
- Direct rule: simple copy
- Field rule panel
- Initial query / Final query of the stage
- Data preview: Source and Destination
- Total records of the source
- Scheduled ETL stages
- Refresh the list of schedules
Reading the stage tree
Each stage is a node that opens onto its fields; each row has the form [X] source_field -> DESTINATION_FIELD [rule]:
- [X] indicates that the field is active; clearing the check, the field is skipped.
- Rows in orange have a transformation (everything except Direct); those in white are simple copies.
- The rule is written in square brackets, with its parameters:
[Constant = -1],[Autoinc (from 1)],[Expression = CASE(…)],[Lookup CODIFICA: (DESCRIZIONE) -> COD]. - If there is a condition, the row ends with
[Run rule only if: … otherwise = …]. - In the row without a source field (
[X] -> ID) the value does not come from the source: it is a constant, an autoincrement or a lookup. - If a source or destination field is missing, or the connection cannot be reached, a message reports it.
In the empty state the tree offers two shortcuts: New stage and Auto-match.
D3. Creating a project
- Link the connections. Source and destination must be already connected in the tree. Select the connection and use ETL → Source connection and Destination connection (from the tree). If you choose the same connection for both, the program warns you.
- Create the stages in one of the following ways.
- Define the rules for the fields (D5).
- Save the project (D10) and run it (D8).
Three ways to create stages
| Method | How it works |
|---|---|
| New stage | ETL → New stage menu: choose the source and destination tables (or a custom query) and the connections. |
| New stage from the tree | Starts from the table selected in the database tree. |
| Auto-match | Choose the source and destination connections. The program looks for tables with the same name on both and lists them with the match percentage (for example "12/15 matching fields"). You check the ones you want and press Generate stages: for each it creates the stage and a Direct rule for each field with the same name. Tables that already have a stage are disabled: duplicates are never created. |
Fields
- Generate fields reads the source fields and creates the rules. You can replace existing fields and choose with Select all / Deselect all which ones to bring in. A legend flags fields with different types between source and destination and warns if two rows point to the same destination field.
- Add, edit, delete field: from the ETL menu or the right-click. Deletion asks for confirmation and also works on several selected fields.
- Delete stage and Clear all stages always ask for confirmation.
D4. Stage settings
From right-click on the stage → Edit settings a tabbed window opens.
| Tab | What it sets |
|---|---|
| Source / Destination | Source and destination connection (Choose… or Global, i.e. the project's one), source and destination table, Generate fields. With Custom query the source becomes a SELECT written by you, or chosen among the queries of the Editor tabs. |
| Options | Stage active (if off it is skipped without deleting it); Empty destination first (TRUNCATE / DELETE); for REST sources, download all pages with a maximum of pages; Write mode; Key fields; WHERE filter on the source. |
| Dedup | Removes duplicates based on one or more fields; with Keep the last and a sort criterion you decide which row survives. |
| Idempotency | Idempotency key (fields or expression) to avoid rewriting rows already processed. The hint on the tab explains the trade-offs. |
| Transactions | Mode: Auto or Per block (one transaction for each block of rows, the historical behavior) or Per stage (a single transaction for the whole stage). Block error: Stop the stage (default) or Continue. Truncation: Allow, Warn (default) or Error that stops the stage. |
| Formats | Default date format, decimal separator and thousands separator, used when the source contains text to convert (typical of CSVs). |
| Initial query / Final query | SQL run before or after the stage (for example sequence resets). You choose whether to run it on the source or the destination connection. The same tabs are in the Field rule side panel (see screenshot). |
Write mode
The mode decides how the rows reach the destination: Always insert (no key check), Append, Update, Append + Update (updates if it exists, inserts if missing) and Delete. The modes that compare the rows with the destination require the Key fields: if they are missing the program reports it before starting. The key fields must be specified with the real keys of the destination table.
D5. Field rules
- Source field and Destination field, with the type of each
- Different types warning
- Rule: the type of transformation
- Expression and Assistant… button
- Run rule only if: condition
- Preview on the real values of the source
- Advanced condition composer (D6)
It opens with a double-click on a field, with Edit field, or you work directly in the side panel (which has the Full editor button). If the source and destination fields have different types a warning appears: consider an Expression rule to convert the value.
| Rule | What it does |
|---|---|
| Direct | Copies the value. In addition you can apply a cleaning quick action: none, remove text (remove all occurrences of a text, for example € before writing to a numeric field), replace text (search / replace), uppercase, lowercase, trim. It is a single step, applied before the stage's default formats. |
| Constant | Always writes the same value (for example -1 or 100), regardless of the source. |
| Expression | Calculates the value with a formula (see below). In the tree it appears as [Expression = …]. |
| Lookup | Looks up a value in another table. You set where to look (source or destination connection), the table, the key fields to search on, the source field (or expression) to compare, the value field to return and a default value if nothing is found. Example in the screenshot: the code is obtained from the street description. If the lookup table has several rows for the same key, the last one found is used. |
| Ignore | The field is not written. |
| Autoincrement | Numbers the rows: it starts from an initial value or from the last value already present in the destination. |
| Formula | Combines several source fields with a template containing field placeholders (for example an address composed of street, number and house number). The Preview button shows the result on real rows. |
Expressions
The expression engine is typed and allows nesting functions. Source fields are indicated in curly braces, for example {CAP}.
- Operators:
+ - * / %, comparisons (= <> < <= > >=),AND,OR,NOT. - Functions:
IF,COALESCE,ISNULL,ISBLANK,UPPER,LOWER,TRIM,PROPER,CONCAT,LEFT,RIGHT,SUBSTRING,LENGTH,REPLACE,LPAD,RPAD,ABS, andCASEin its forms. - The engine also accepts the older functions (REGEXMATCH, DATE and others) used by the assistants: the full list is in the Assistant.
IF(ISBLANK({CAP}); "00000"; LPAD(TRIM({CAP}); 5; "0"))
CASE({STATO}; "A"; "ATTIVO"; "S"; "SOSPESO"; "ALTRO")
CASE(Permanenti;2;Temporanei;3;1) — short form
The Assistant… button guides you through choosing the function and its arguments, and Examples offers ready-made templates (also for regular expressions). In the Preview on the values (screenshot) you see, for each real source value, the result that would be written, for example Permanenti → 2, Temporanea → 1.
D6. Run the rule only if…
Each rule can have a condition: if it is true the rule is applied, otherwise an alternative value is written. Check Run rule only if and set:
- Field to check (it can be different from the rule's source field),
- Condition type and Value,
- Negate condition (inverts the result),
- If the condition is false: a fixed value (empty =
NULL), the content of another field of the source or a formula.
| Group | Conditions |
|---|---|
| Content | Empty · Not empty · Spaces only · Digits only · Letters only (accents included) · Alphanumeric (at least one letter and one digit) |
| Text | Starts with · Contains · Ends with · Equal · Different (case-insensitive) |
| Length | Length equal · greater · less |
| Numbers | Greater · less · equal (accepts comma and period) |
| Other | Regular expression · Valid date in the specified format (for example dd/mm/yyyy) |
Advanced condition composer
For "if… then…" transformations with several conditions, the Composer button (or Open composer) builds a CASE expression for you:
- Choose Field, Operator and Compare with and press Add condition.
- Repeat to add more conditions to the same branch; decide whether they must be all true (AND) or just one (OR).
- Write the Result if the branch is true (or insert it from a field) and press Confirm branch.
- Add more branches: they appear in Branches already added; you can remove conditions or branches.
- Specify the Otherwise (default value) and check the Expression preview before confirming with OK.
D7. Viewing the data
While you build the rules you can look at the real data:
- Bottom panel: two tabs, Source and Destination, which follow the selected stage. It shows the data read-only and, on the right, the Total records of the source (in the screenshot, 489). The data arrives in blocks, without loading the whole table.
- Independent window: right-click the stage → View data. It does not follow the selection: it is handy on a second screen to compare a specific stage.
D8. Running
| Action | How |
|---|---|
| Run the whole project | ETL → Run menu or F9. Deactivated stages are skipped. |
| Try a single stage | Right-click the stage → Run stage. |
| Interrupt | ETL → Cancel, or the Cancel button in the progress window. |
During execution a window shows the current stage, a progress bar (determinate if the number of rows is known, indeterminate otherwise) and a log with the start and end of each stage, records read and written, and any errors. The interface stays responsive.
Safety features
- Schema check: with Record schema baseline (stage menu) the program stores the current structure of source and destination and can report when it later changes, for example if a column is renamed.
- Resume after interruption (checkpoint): for a very long stage you can specify a resume field on the source; a checkpoint file, saved next to the project, remembers how far it got, so an interrupted run restarts from there instead of from the beginning. It is not compatible with deduplication of the same stage.
- Errors: each error reports stage, field and row, and a category (configuration, connection, transformation, writing, idempotency, checkpoint, REST).
D9. Scheduling the execution
To run the project at fixed times, without opening PlyxSQL by hand, use right-click on the stage → Schedule (or ETL → Save for the service). The scheduling window opens:
| Field | Meaning |
|---|---|
| Connections | Source and destination. The project must have the connections saved: otherwise the program tells you. |
| Frequency | Hourly · Daily · Weekly · Monthly · Weekend · End of month |
| Interval, Time | How often to repeat the execution and at what time |
| Day of the week / of the month | For Weekly and Monthly. The day of the month has a note for shorter months |
| Catch up missed run | If the computer was off at the scheduled time, the run starts as soon as possible |
The Save button writes a .petl file into the schedules folder. The .petl files are run by the scheduler (TFDPetlScheduler), which can run inside an application, in a Windows service or in a console program:
- It checks the folder right at startup, not only when the timer expires: a file already due starts as soon as the host restarts.
- It never runs two overlapping scans.
- If several processes (for example the application and the service) watch the same folder, a locking mechanism prevents the same file from running twice.
At the bottom of the window the Scheduled ETL stages (N) panel lists the .petl files present in the folder: for each, the project name, the frequency and the number of stages. Refresh rereads the folder (the reading happens in the background, without blocking the interface).
D10. Menu, saving and printing
| Item | Effect |
|---|---|
| ETL → New stage / New stage from the tree | Creates a stage |
| ETL → Auto-match | Creates the stages for same-named tables |
| ETL → Edit / Delete stage, Clear all stages | Stage management |
| ETL → New / Edit / Delete / Generate fields | Field management |
| ETL → Source / destination connection (from the tree) | Assigns the connection selected in the tree |
| ETL → Save / Load project | .fdmap file |
| ETL → Save for the service | Saves the project for execution by a service (with scheduling) |
| ETL → Run (F9) / Cancel | Execution |
Right-click on the stage also offers: Edit settings, Generate fields, Run stage, View data, Record schema baseline, Schedule, Expand all, Collapse all and Print all stages. The latter opens a resizable preview with the complete list of stages and rules, with the Print and Save to file buttons: useful as documentation of the mapping.
If a source or destination field cited by a rule no longer exists, or a project connection cannot be reached, opening the project warns you and lets you reconnect by hand.
D11. Suggestions with AI
With the AI service configured (File → AI Configuration):
- Suggest fields: proposes the matches between source and destination fields and updates those already present.
- Generate expression: in the Assistant you describe in words what you want to obtain and, if needed, provide an example value; the program proposes the expression.
E1. What the Profiler is for
The Performance - Profiler tab measures how fast a query or stored procedure is. Each execution becomes a bar in a chart colored by duration, with the database's execution plan when the engine allows it. This way you immediately see which queries slow down and can compare before and after an optimization (for example after adding an index).
The Profiler works on the tree's active connection and uses its dialect. Each measurement also carries the connection's accent color, so you can tell at a glance queries coming from different databases.
INSERT, UPDATE, DELETE and DDL: there is no automatic rollback. To test a write, first start a transaction (Tx) and close it with RB, or work on a test database.E2. What is measured
| Statement type | What is measured |
|---|---|
SELECT, WITH, SHOW, PRAGMA, EXPLAIN | The execution time (opening and first block of rows), then the number of rows. Counting rows may force the driver to read them all: that cost is shown separately as fetch and does not inflate the main time. |
Other statements (INSERT, UPDATE, DELETE, DDL…) | The execution time and the rows affected. The plan is not requested, unless you have enabled analysis on writes too (see E6). |
| Stored procedure | Time and rows. For procedures the plan is not captured. |
| Execution in error | The time up to the error and the database message. No plan. |
E3. Thresholds and colors
| Outcome | When | Bar appearance |
|---|---|---|
| Good | less than 150 ms | Green |
| Warning | from 150 ms to less than 600 ms | Amber, with a small shape indicator |
| Critical | 600 ms or more | Red, with shape indicator |
| Error | the query failed | Dashed red border and ! icon |
The thresholds (150 and 600 ms) are the default values and can be changed, as can the colors. Besides color there is always a shape indicator, useful for anyone who has trouble telling red from green.
E4. The chart
- Each execution is a bar; the most recent is on the right. If the bar has two stacked segments, the second is the cost of the full fetch (
(+N ms fetch)). - The last 25 bars remain visible (configurable): when a new one arrives, the oldest leaves the chart.
- While a query is running a pulsing ghost bar appears; when it finishes it turns into the real bar, with a short animation.
- At the top four indicators (KPIs): LAST RUN, AVERAGE, MIN / MAX and PROFILED QUERIES.
- The legend explains the colors: Good, Warning, Critical, Error.
- Before the first measurement the chart shows a message inviting you to profile a query.
E5. Examining a measurement
- Hover over a bar: a tooltip shows the abbreviated SQL, the time (with any fetch), the rows, the dialect (badge) and, for procedures, the stored procedure label.
- Click a bar to select it: the detail panel opens with the SQL, the dialect, the date and time (
dd/mm/yyyy hh:mm:ss), time, rows (n/aif not available) and the execution plan in a fixed-width box. For queries in error the ERROR label and the message appear. - Mouse wheel over the chart with a bar selected: scrolls the plan, three lines per notch.
- Keyboard: the chart takes focus with a dedicated frame; ← and → move the selection between bars, Enter or Space confirm it.
E6. The execution plan
The format depends on the engine: each uses its own mechanism.
| Engine | How the plan is obtained |
|---|---|
| PostgreSQL | EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON): real times, but the query is run a second time. |
| MySQL | EXPLAIN FORMAT=JSON: does not re-run the query. |
| SQL Server | SHOWPLAN_XML: compiles without running and returns an XML document, which can be opened as .sqlplan in SSMS. The SET commands run on a cloned service connection, so your session does not change. |
| SQLite | EXPLAIN QUERY PLAN |
| Oracle | EXPLAIN PLAN with DBMS_XPLAN display (TYPICAL format). It uses the PLAN_TABLE with its own identifier and cleans it up after reading. If the table is missing, the message indicates the cause. |
| Firebird | Firebird has no SQL EXPLAIN: the plan is read from the MON$ monitoring tables (MON$COMPILED_STATEMENTS), available from version 5. On earlier versions the plan is not available. |
| InterBase, DB2, Informix, MS Access | No plan: only time and rows are measured. |
- The plan is an extra: if it cannot be obtained the measurement remains valid and the detail shows
[plan not available]with the reason. - It is requested only for read statements. For
INSERT/UPDATE/DELETEyou need to enable the analysis option on writes (off by default), because on some engines it would require running the statement again. - A trailing
;in the text is removed before requesting the plan (on Oracle it would cause an error).
E7. History and export
The Profiler keeps the last 200 measurements in memory (the oldest leave first; configurable, 0 = unlimited). The list can be saved to a CSV file (separator ;) with the columns Timestamp, Dialect, IsStoredProc, ElapsedMs, FetchAllMs, RowsAffected, Severity, SQL and Error: useful to compare several optimization sessions in a spreadsheet.
E8. Background execution
Measurements can run on a twin connection in a background thread: the interface does not freeze, and the result appears in the chart at the end of the run. Only one profiling at a time can be started; another request, while the first is in progress, is rejected with a message. Interruption is "best effort": it depends on the driver and, if it is not supported, the measurement finishes on its own anyway.
F1. What Analysis is
The Analysis tab turns the result of a query into a chart: bars, lines, pie, radar, maps, treemap and 19 types in all. It is for seeing the data without having to export to Excel. From the chart you can then save the image, the PDF or an Excel file.
In Analysis mode the menus become File, Settings, Analytics and ?; Builder, Designer and ETL are hidden. The panel is self-contained: it has its own list of columns and its own configuration.
F2. From the Builder query to the chart
The data in Analysis is always that of a query. The quickest way to prepare it is the Builder:
- Connection tab on which the query runs (here
CSV_Conn, a LocalSQL connection on CSV) - Table in the diagram, with the selected fields
- Generated SQL in the Editor
- Result counter:
45 rows x 3 cols [0 ms] - Results grid
- Choose the connection with its tab (ch. 11) and add the table by double-clicking from the tree (ch. 3). In the example:
POPOLAZIONE_EUROPA, with three fields (codice_iso,paese,popolazione). - Prepare the columns the chart needs: a column of labels (the X) and one or more numeric columns (the Y). Add filters,
GROUP BYand aggregates in the Builder: the charts draw what the query returns, so the query must already give the data in its final form (the histogram is the exception, as it groups by itself). - Run with Run. Check in the grid that the rows are the expected ones (
45 rows x 3 cols). - Switch to the Analysis tab (click the tab at the top). Analysis takes the result of the query active in the Editor, i.e. the one just run: choose chart type, X column and Y columns (F3).
F3. The interface
- Export ▾: PNG, SVG, PDF, CSV, Excel
- Copy image to the clipboard
- Save ▾ / Load ▾: configuration or complete analysis
- Refresh: rereads the data
- Resolution (1x, 2x, 3x)
- Title of the chart
- Chart type
- X column (labels)
- Y columns (values): one or more columns
- Display options
- Number format per column
- Chart (here a radar: each spoke is a country, the distance from the center is the population)
- Legend
The panel on the left shows only the controls useful for the chosen chart type: when you change type the irrelevant sections disappear and the remaining ones close up with no gaps. All changes are visible immediately.
- Chart type, X column and Y columns are the basis: the X gives the labels, the Y the values.
- Options: Show legend, Grid, Show values (writes the value on bars and points), Vertical labels (useful with many categories) and Tooltip on mouse hover.
- Title and Resolution (1x, 2x, 3x) stay in the top bar, because they apply to the entire chart and not to a single column.
F4. Chart types
| Type | Required columns |
|---|---|
| Bars · Stacked bars · Lines · Area · Combo | X = labels, one or more Y. Combo: each series can be a bar or a line and use the right-hand Y axis. |
| Pie · Radar | X = labels, Y = values |
| Histogram | A single Y column (the first), the X is ignored. The chart creates the intervals by itself (from 2 to 100) and counts the values in each: no need to aggregate in the query. |
| Scatter · Bubbles | X = numeric column (each row is a point at its true position); one or more Y. For bubbles also choose the Bubble size column; without a size they behave like the scatter. |
| Candlestick | X = label (usually the date) and exactly 4 series in the order open, high, low, close. The order follows that of the query columns: expose them this way in the Builder. |
| Box plot | X = category, one Y = value. The program calculates minimum, first quartile, median, third quartile and maximum for each category. |
| Heatmap | X = category column (horizontal axis), Heatmap row = category on the vertical axis, Y = value that gives the cell's color. |
| Waterfall | X = category, Y = change (positive or negative). The chart appends the Total bar at the end. |
| Funnel | X = stage, Y = value. The first stage is 100% and the following ones are proportional. |
| Map (choropleth) | X = area code, Y = value (see F6). |
| Treemap | X = label, Y = value (area of the rectangle). Optional: Treemap group, a second column that groups the rectangles into colored boxes. |
| Sparkline | X = labels, several Y columns: each becomes a compact lane, without axes, with the last point highlighted and minimum/maximum annotated. Suited to summaries with several metrics. |
| Sankey | Source and Destination columns (the nodes) and Y = flow. A single hop: for multiple steps you need one query for each pair. |
codice_iso, the Y is popolazione. The list of Y columns shows all the query's columns: select only the numeric ones.F5. Analysis options
| Option | Effect | Applies to |
|---|---|---|
| Highlight outliers | Circles the points beyond 2 standard deviations from the series mean | Bars, Lines |
| Trend line | Linear regression overlaid on the series | Types with a Cartesian axis |
| Moving average (N periods) | Lighter dashed line for each visible series. 0 = off | Bars, Stacked bars, Lines, Area, Combo |
| Threshold (value + label) | Dashed horizontal line with a label, for example a monthly target | Types with a linear Y axis (not pie, radar, maps, heatmap, funnel, treemap, sparkline, Sankey) |
| Histogram bins | Number of bins (2–100) | Histogram |
| Max points | Beyond this number the chart simplifies the data and flags aggregated data. 0 = default value | All |
When the chart has simplified the data, a single point represents a group of rows: keep this in mind if you use the chart to trace back to a specific row.
F6. Maps and density
- Map level: Regions or Provinces
- Map file (
mappefolder) and button to reread the folder - Density (KDE) instead of regions
- Colors of the scale: low, medium, high density
- Color scale with minimum and maximum
- Ranking of values (with "+N more")
- Zoom: "zoom active — double-click to reset"
The map colors each area according to the value. To use it:
- In the Builder prepare a query with a code column and a value column (in the example
codice_isoandpopolazione). - Choose the Map (Choropleth) type, the code column as X and the value as Y.
- Choose the Map file among those found in the
mappefolder next to the program (in the exampleeurope.geojson). If you add a file while the program is open, press the refresh button next to the list.
- The codes are compared case-insensitively and accept both numeric ISTAT codes (Italy: regions 1–20, provinces 1–107; for example Rome = 58, Milan = 15) and two-letter ISO codes (
IT,FR,US…). - By default the program looks for
italy_regions.geojsonoritaly_provinces.geojson(depending on the Map level). Any other.geojsonfile with thecodeandnameproperties (Europe, World, or your own boundaries) shows up in the list by itself. The files are not included: they must be supplied separately. - With Density (KDE) the map does not color areas but shows a density cloud of geographic points: the Longitude and Latitude columns are needed (and, optionally, a weight).
- Map/heatmap colors: three swatches (low, medium, high density). A click opens the color picker; they also apply to the heatmap.
- Zoom and pan with the mouse; double-click returns to the initial view.
F7. Number format
In the Column number format section choose a column and the format in which to show its values:
| Setting | Effect |
|---|---|
| Format | Automatic (K/M) (default: 91.8M, 764…), Number, Currency or Percentage |
| Decimals | Number of decimal digits |
| Thousands separator | Turns on the thousands separator |
| Currency symbol | For the Currency format; empty = € |
Press Apply format to column: the column appears in the Formatted columns list. The format applies everywhere the column is shown as a number: value labels, axes, tooltip, legend, CSV and SVG export. You can format one column at a time, and the format remains even if you change chart type or Y columns.
F8. Exporting, copying and saving
| Button | Result |
|---|---|
| Export ▾ → PNG | Image of the chart. The Resolution selector (1x, 2x, 3x) adjusts the definition of the images |
| Export ▾ → SVG | Vector image (XML text) |
| Export ▾ → PDF | PDF document with the chart |
| Export ▾ → CSV | The chart's data, with the chosen formats |
| Export ▾ → Excel | .xlsx file; if possible with the chart included, otherwise only the data: a message tells you what was saved |
| Copy image | Puts the chart on the clipboard, without a save window: ideal for pasting it into Word or PowerPoint |
Saving the analysis
| Item | What it saves |
|---|---|
Save ▾ → configuration only (.fdcfg) | Chart type, columns, colors, formats, trend and threshold. Not the connection or the query: it must be applied to an already open query with the same structure. |
Save ▾ → complete analysis (.fdanalysis) | Everything: connection parameters, SQL text and configuration. The password, if any, is protected with Windows (DPAPI) and is never written in plain text. |
| Load ▾ → configuration / complete analysis | The configuration is reapplied to the current query. The complete analysis opens its own connection and query, runs them and applies the configuration, all in one go. |
.fdanalysis on another machine or by another user, the password is not readable and opening fails with a clear message.Reopening an analysis from the start screen
Complete analyses (.fdanalysis) saved in a folder appear on the start screen as tiles with a preview. A click on a tile opens only that analysis: connection and query are recreated and the other tabs (Tables, Builder, Designer, ETL, Profiler), the tree and the menus are hidden. Press F10 to return to the start screen. The last folder used is remembered by itself between sessions.
The Analytics menu at the top repeats the same actions: Refresh (F5), export PNG, SVG and CSV, save and load the configuration.
F9. Refreshing the data
Refresh re-runs the query and redraws the chart. The operation runs in the background on a copy of the connection: the screen does not freeze and the button changes to Refreshing… until it finishes. A click while a refresh is in progress is ignored, not queued. If the connection is not available or the query gives an error, a message appears.
F10. Interacting with the chart
- Hover over a data point (if Tooltip is on) to see label and value, in the chosen format.
- Click on the legend: shows or hides the corresponding series.
- On maps: zoom and pan with the mouse, double-click to return to the initial view.
F11. Common problems
| Symptom | Cause and remedy |
|---|---|
| The chart is empty | The query returned no rows, or the chosen Y columns are not numeric. Check the grid in the Builder or the Editor. |
| Too many bars, unreadable | Aggregate or filter in the query, or use TOP / LIMIT. |
| The candles are wrong | You need exactly 4 columns in the order open, high, low, close: reorder them in the query. |
| The map is empty or few areas are colored | The codes in the query do not match those in the GeoJSON file (for example numeric ISTAT versus two-letter ISO), or the file is missing from the mappe folder. |
| "No connection" on refresh | The query's connection is not open: connect it from the tree and try again. |
| Reopening an analysis with a password does not work | The file was saved on another computer or with another Windows user (see F8). |
1. What the Builder is
The Builder is PlyxSQL's visual query generator. Each table is a node on the canvas, relationships are lines between fields and the SQL is recalculated at every change. There is no need to write a line of code for SELECT, JOIN, WHERE, GROUP BY, CTEs, subqueries and window functions.
The generated code adapts to the dialect of the active connection (Firebird, PostgreSQL, SQL Server, MySQL and the other supported ones): the options the dialect does not provide, for example certain types of JOIN or lock, are disabled and marked as unavailable.
2. The interface
- Command bar: run, save, ERD, Explain… (ch. 9)
- Tabs: one row for connections and one for queries (ch. 11)
- Table node: name, alias and fields (ch. 4)
- Field search inside the node
- Relationship between two fields: JOIN (ch. 5)
- Counter of visible / total fields, with scroll arrows
- Advanced SQL panel (ch. 8)
- Minimap to get oriented in large diagrams
- Zoom − / percentage / +
- Node header buttons: ◉ ✓ □ ×
Below the Builder you find the SQL Editor, which shows the generated query. The [QB] Builder button in its bar brings attention back to the Builder.
3. Adding tables
With an active connection, double-click a table in the tree on the left: it is added to the Builder, in the current connection's tab.
Alternatively, the Schema button in the command bar imports the schema from the active connection. Tables already present in the diagram can be duplicated (Duplicate table node, or Ctrl+D) and renamed with an alias.
4. The table node
Header
It shows schema.table and, if set, the alias in parentheses. If a lock or a hint is applied to the table, it appears in square brackets (for example [FOR UPDATE]). A ⚑ flags a table comment. From right to left the buttons are:
| Button | Function |
|---|---|
| × | Removes the table from the diagram (and its relationships). |
| □ | Deselects all the table's fields: none ends up in the SELECT. |
| ✓ | Selects all the table's fields. |
| ◉ | Shows or hides the values of the first record next to the fields (requires a connection). |
Fields
- Each row shows name and type (for example
CHAR(120)); primary keys have the PK badge. - A click on the field includes or excludes it from the
SELECT: selected fields are in bold. - Double-clicking the field sets the column alias (
AS); on the header it sets the table alias; on the right edge of the header, the table comment. - Small badges on the field indicate
CASE WHEN, window function, computed expression, scalar function, comment and alias. - The search field… bar filters the fields of that node: useful with tables of dozens of columns.
- If the table has more fields than can be displayed, the ▲ ▼ arrows and the counter appear (in the example
1-10/50); the mouse wheel on the node also scrolls the fields. - Drag the node's right edge to change its width.
5. Relationships and JOINs
Each field has a dot on the edge of the node. To create a JOIN:
- Press the dot of the starting field (right side) and drag: a temporary line follows the mouse.
- Release on the field of the other table.
- In the JOIN type popup choose the type of join.
| Type | Notes |
|---|---|
| INNER JOIN | Only the rows with a match in both tables. |
| LEFT / RIGHT / FULL JOIN | Keep the rows with no match on one side or both. |
| CROSS JOIN | Cartesian product. |
| CROSS APPLY / OUTER APPLY | Typical of SQL Server. |
| JOIN LATERAL | Typical of PostgreSQL. |
The popup shows the dialect name; unsupported entries are disabled. The relationship appears as a curve between the two fields (visible in the example between IDUBICAZIONE and CODICE). Select it and press Del to delete it; Esc cancels a relationship in progress.
Self-joins and duplicate aliases
From the field menu, Add self-join (same table, new alias)… adds the same table with a new alias. When two tables have columns with the same name, Auto-alias duplicate columns (JOIN)… (or the Auto-a,b… button in the panel) assigns the aliases automatically.
6. Fields, aggregates and sorting
Everything is set from the field's context menu (ch. 7) or, more quickly, from the corresponding row in the Advanced SQL panel. When you use an aggregate, the non-aggregated fields go into GROUP BY: you can mark them by hand or choose the ROLLUP, CUBE or GROUPING SETS mode from the panel's options.
7. Field context menu
Right-click a field. The menu header shows TABLE.FIELD (type).
| Item | Effect |
|---|---|
| Aggregates | No aggregation, COUNT, COUNT DISTINCT, SUM and AVG (numeric fields only), MIN, MAX, STRING_AGG / LISTAGG / GROUP_CONCAT. |
| Add WHERE condition… | Guided filter on that field. |
| Add WHERE condition (RAW)… | Free expression. |
| WHERE summary… | Summary of the WHERE, editable by hand. |
| Sort order (ORDER BY) | None, ASC, DESC. |
| Select / Deselect / GROUP BY | Include or exclude from the SELECT; group by this field. |
| Add CASE WHEN… | Conditional expression editor (with Edit / Remove if already present). |
| Add OVER (window function)… | Window function (with Edit / Remove). |
| Computed expression | COALESCE, NULLIF, IIF, free SQL expression. |
| Add self-join… | See ch. 5. |
| Analyze field… | Column statistics. |
| Set field comment… | Comment on the field (Edit / Remove). |
| Add scalar function… | Guided procedure for TRIM, CAST, date functions etc. (Edit / Remove). |
| Table lock / hint | Inherit from the query or a specific table lock (NOLOCK, UPDLOCK, FOR UPDATE, FOR SHARE, SKIP LOCKED). Entries not applicable to the dialect are disabled; the hint text is enabled only for SQL Server and MySQL. |
| Duplicate table node | Duplicates the entire node. |
| Auto-alias duplicate columns… | Automatic alias on columns with the same name. |
8. Advanced SQL panel
It is the column on the right of the canvas. Sections open and close with a click on the title; the ◄ ► tab on the edge collapses the whole panel to recover space.
| Section | Content and actions |
|---|---|
| Table Aliases | List of tables with the current alias (or no alias). Click the pencil ✎ to edit it. |
| Fields & Aggregates | One row per field, with the table's three-letter prefix (in the example SBU.IDUTENTE). Click on the left edge: select/deselect. Click in the middle: cycles through the aggregates (none → COUNT → COUNT DISTINCT → SUM → AVG → MIN → MAX). Click on the GB badge: GROUP BY. Click at the far right: sorting ↑ / ↓ / none. |
| GROUP BY / HAVING | The grouped fields and the HAVING row: one click opens the edit window. |
| CTE (WITH … AS) | List of CTEs with the number in parentheses; + Add CTE opens the editor with name, body, recursive and materialized options. |
| Options | DISTINCT, TOP / LIMIT, OFFSET, RETURNING, GROUP BY mode (plain, ROLLUP, CUBE, GROUPING SETS), query lock. At the bottom: + Note (annotation on the canvas), + Subq. (subquery), Auto-a,b… (automatic aliases). |
| WHERE Builder | + Condition and + AND/OR Group build a tree of filters. The pencil ✎ opens the summary of the full text, where you can switch to manual mode: the visual tree is ignored and the written text applies. Clear WHERE resets everything. |
| SQL Preview | Preview of the generated SQL, with keyword highlighting. |
9. Command bar
| Button | Action | Requirements |
|---|---|---|
| Run | Generates the SQL, takes it to the Editor and runs it. | Active connection |
| Schema | Imports the schema from the connection. | Connection |
| Clear | Clears the diagram, after confirmation. It can be undone with Undo. | — |
| Save / Load | Saves and loads the diagram in a .json file. If the file is not valid the current diagram stays unchanged. | — |
| Prints the diagram over several pages. | — | |
| Undo / Redo | Undo and redo; each action on the model is a single step. | — |
| SQL | Copies the generated SQL to the clipboard. | — |
| Fit | Adjusts the zoom to show the whole diagram. | — |
| ERD | Toggles between the Builder view and the entity-relationship diagram view. | — |
| DDL | Shows the DDL script of the diagram. | — |
| AI | Generates the query from a natural-language request. | AI service configured |
| Perf | Performance Advisor: suggestions on the query. | — |
| Snip | Snippet library. | — |
| Export | Exports the diagram to PNG, SVG, HTML or Markdown. | — |
| Tabs | Adds a new query tab (asks for the name). | — |
| Subq | Inserts a subquery node. | — |
| Expl | Execution plan in text form (toggle). | Connection |
| ExpG | Execution plan as a graph of nodes. | Connection |
| CmpPlan | Comparison between two execution plans. | Connection |
| Save.fdp / Load.fdp | Saves and opens the .mfdproj project (see ch. 13). | — |
10. Navigation and selection
| Gesture | Effect |
|---|---|
| Mouse wheel | Zoom centered on the pointer. Over a node with scrolling it scrolls the fields; over the side panel it scrolls the panel. |
| Right button + drag or Alt + left button | Pans the canvas. |
| Drag a node | Moves the table; on release it snaps to the grid. |
| Drag on the background | Area selection (rubber band). |
| Ctrl + click | Adds or removes a node from the multiple selection; dragging one moves the whole group. |
| Minimap | Drag in the minimap to move the view. |
| − / % / + buttons | Zoom from 30% to 300%; a click on the percentage returns to 100%. |
11. Query tabs
The Builder has two rows of tabs. The first contains an entry for each open connection (in the example SBT): clicking it activates that database in the tree and in the Editor too. The second contains the connection's queries: each has its own independent diagram.
- + (or Ctrl+T) creates a new query in the active connection.
- × on the tab (or Ctrl+W) closes it, after confirmation.
- Right-click on the tab: Rename tab…. The name is also updated in the corresponding Editor tab.
12. From the Builder to the Editor
Each Builder tab is linked to an SQL Editor tab. With Run the generated SQL is taken to the Editor on the correct connection and then run.
[QB] Builder, the Builder controls the text and rewrites it at every change to the diagram: your manual corrections would be lost. To refine the query by hand press the button: it becomes [FR] Free and the Builder stops overwriting the Editor (see ch. 17).13. Saving your work
| What | How |
|---|---|
| A diagram | Save / Load → .json file. |
| A query as an image or document | Export → PNG, SVG, HTML, Markdown. |
| The whole project | Save.fdp / Load.fdp → .mfdproj file. Saves the connections and tabs; the window title shows an asterisk (*) when there are unsaved changes. |
| A recurring query | Ctrl+Shift+S saves it as a snippet. |
14. Keyboard shortcuts
| Keys | Action |
|---|---|
| F1 | Shortcut guide |
| Ctrl+Z / Ctrl+Y | Undo / redo |
| Ctrl+A | Select all nodes |
| Ctrl+C | Copy the generated SQL |
| Ctrl+D | Duplicate the selected node |
| Ctrl+F | Search tables and fields in the diagram |
| Ctrl+R | Find and rename everywhere |
| Ctrl+Shift+F / Ctrl+G / Ctrl+0 | Fit all / zoom to selection / reset zoom |
| + / − | Zoom in / out |
| Tab / Shift+Tab | Next / previous node |
| Del | Deletes the selected node or relationship |
| Esc | Cancels the relationship in progress or closes the overlay |
| Ctrl+E | Graph Explain Plan |
| Ctrl+H | Query history |
| Ctrl+P | |
| Ctrl+T / Ctrl+W / Ctrl+Tab | New tab / close tab / next tab |
| Ctrl+Shift+S / Ctrl+Shift+L | Save as snippet / open snippet library |
| Ctrl+Shift+T | Dark / light theme |
15. What the Editor is
The SQL Editor is the lower half of the window: here you write, format and run queries and read the results. It shows the SQL generated by the Builder, but it also works on its own as a full editor.
- Execute: runs the query
- [FR] Free / [QB] Builder: who controls the text
- Undo / Redo
- Copy, Find, Format, ⚙, Clear, Load
- Script: batch execution
- Tx, OK, RB: transaction
- Hist: history
- Editor tabs (connection and query)
- Gutter with line numbers
- Status bar: driver @ host / database
To the right of the command bar you find the cursor position (Ln 1, Col 1) and, when needed, the syntax error indicator.
16. Writing SQL
- Real-time keyword coloring, even on long texts.
- IntelliSense: opens by itself as you type (from the first character after
SELECT,WHEREand the like, or after the dot) with tables and fields from the schema. You can open it with F2 or Ctrl+Space; scroll with ↑ ↓, confirm with Enter or Tab, close with Esc. - Ctrl+/ comments or uncomments the current line (
--). - Tab inserts two spaces.
- Error indicator: the command bar flags any syntax problems as you type.
17. Builder / Free mode
The button next to Execute decides who owns the text:
| Button | Meaning |
|---|---|
[QB] Builder | The text is the one generated by the Builder and is rewritten at every change to the diagram. |
[FR] Free | Free SQL: the Builder no longer touches the Editor. Execute runs what you have written. |
[FR] Free, then refine by hand. To go back to the text generated by the Builder, press the button again.In the screenshot the query SELECT * FROM "SBUTENTI" INNER JOIN … is in Free mode: the Builder does not overwrite it.
18. Running queries and scripts
| Action | How | Details |
|---|---|---|
| Running the query | Execute, F5 or Ctrl+Enter | Requires an active connection. If the query contains parameters (:name) a window opens to set their type and value. A query with several recordsets shows the results in separate tabs. |
| Running a script | Script or F6 | Runs several statements in a row, with progress. It correctly recognizes BEGIN … END blocks (the internal ; in a trigger do not split the statement), the isql SET TERM ^ format and the SQL Server GO separator. |
| Opening a .sql file | Load or Ctrl+O | The file opens in a new tab. Both UTF-8 and old ANSI files (for example saved from Notepad) are accepted. |
19. Transactions
By default statements are run normally. To group several changes in a single transaction use the three buttons:
| Button | Action | Enabled when |
|---|---|---|
| ● Tx | BEGIN TRANSACTION (Ctrl+B) | Connection open and no transaction in progress |
| ✔ OK | COMMIT | Transaction in progress |
| ↺ RB | ROLLBACK | Transaction in progress |
OK and RB are faded (as in the screenshot) until you start a transaction. The outcomes appear in the Messages panel.
20. Results panel
After execution, the results panel opens below the Editor. It is closed with × and resized by dragging the divider.
| Item | Function |
|---|---|
| Status row | Rows returned or affected, or the error on a single line (the full detail is in the Messages panel). |
| Search 🔍 | Filters the rows already loaded. |
| Pagination | For large results the query is read in blocks: the forward/back buttons scroll through the blocks (SQL pagination, or on the client). If the query has no ORDER BY a ⚠ warning appears because the order of the blocks is not guaranteed. |
| Export | CSV, JSON, XML, XLSX, or an INSERT INTO SQL script. |
| ✎ Edit | Makes the grid editable when the result comes from a single table; New row, Delete row, Save and Cancel appear. Changes stay in cache until you save them. If the result is not editable the button says so in its tooltip. |
| Msg | Shows or hides the Messages panel (log of executions, errors, transactions); the × Clear button empties it. |
21. Command bar
| Button | Action |
|---|---|
| Execute | Runs the query (F5). |
| [FR] Free / [QB] Builder | Changes the mode (ch. 17). |
| ↶ ↷ | Undo / redo typing. |
| Copy | Copies all the text to the clipboard. |
| Find | Find and replace (Ctrl+H): Find next, Replace, Replace all, with Regex mode. |
| Format | Formats the SQL: uppercase keywords and indentation. |
| ⚙ | Formatter options. |
| Clear | Empties the current tab. |
| Load | Opens one or more .sql files in new tabs (Ctrl+O). |
| Script | Runs as a batch (F6). |
| Tx / OK / RB | Transaction (ch. 19). |
| Hist | Query history (Ctrl+Alt+H). |
22. Editor tabs
The structure is the same as the Builder's: one entry per connection (SBT) and, next to it, the queries (Query 2). An asterisk after the name (Query 2 *) indicates unsaved changes.
- + or Ctrl+T: new tab in the active connection.
- × or Ctrl+W: closes the tab, after confirmation.
- Ctrl+Tab: switches to the next tab.
- Right-click → Rename…: the name is also updated in the linked Builder tab.
At the bottom left the status bar shows the driver, server and database the tab is connected to (in the example FB @ localhost / SB.FDB).
23. History and recovery
History
Hist opens the list of executed queries: at most 100 entries, without duplicates, with search and preview. From there you can insert a query into a tab.
Recovery after a crash
Every 30 seconds the active tab is saved to a temporary file. If the application closes abnormally, on restart you are offered to import the recovered drafts.
24. Editor shortcuts
| Keys | Action |
|---|---|
| F5 / Ctrl+Enter | Run |
| F6 | Run as a script |
| F2 / Ctrl+Space | IntelliSense |
| Ctrl+/ | Comment / uncomment the line |
| Ctrl+H | Find and replace |
| Ctrl+B | BEGIN TRANSACTION |
| Ctrl+O | Load .sql file |
| Ctrl+Alt+H | History |
| Ctrl+T / Ctrl+W / Ctrl+Tab | New tab / close tab / next tab |
| Tab | Inserts two spaces |
25. The menu bar
The bar at the top contains all the application's commands. The items change according to the active mode (the tabs above the workspace) and the current state: a gray item means something is missing (a connection, a table in the diagram, text in the editor). The tables below give, for each item, the condition that makes it active.
| Mode | Visible menus |
|---|---|
| Builder | File, Edit, Query, View, Schema, DDL, Connection, Tab, Snippet, Tools, Settings, Extras, ? |
| Designer | The Schema Designer menu appears; Query, View and Schema (those of the Builder) are hidden |
| ETL | The ETL menu appears. Edit, DDL, Connection, Tab, Snippet, Tools and Extras are hidden; File, Settings and ? remain |
| Analysis | The Analysis menu appears (refresh, export, configuration) |
26. File menu
| Item | Shortcut | When it is active |
|---|---|---|
| New project | Ctrl + N | Always |
| New editor tab / Close editor tab | Ctrl + T / Ctrl + W | Close: if at least one tab exists |
| Save editor tab | Ctrl + Shift + E | Only if the tab has been modified |
| Load SQL file | Ctrl + O | Opens one or more files in new tabs |
| Save project | Ctrl + S | Only with unsaved changes |
| Save project as | — | Always |
| Open project (.mfdproj) | Ctrl + Shift + O | Always |
| Recent | — | List of recently used projects |
| Export → SQL, DDL, Markdown | — | SQL: needs text in the editor. DDL and Markdown: Builder mode, with at least one table in the diagram |
| Export → CSV, JSON, XML, Excel | — | Needs SQL in the editor and a connection |
| Export → SVG, PNG, HTML | — | Images or page of the diagram. Builder mode, with at least one table in the diagram |
| Print diagram | Ctrl + P | Builder mode, with at least one table in the diagram |
| AI Configuration | — | AI service settings |
| Exit | Alt + F4 |
27. Edit menu
| Item | Shortcut | When it is active |
|---|---|---|
| Undo / Redo | Ctrl + Z / Ctrl + Y | They act on the active Builder or Designer |
| Copy all the SQL | Ctrl + Shift + C | Needs text in the editor |
| Find / Replace | Ctrl + H | Needs text in the editor |
| Global find and rename | Ctrl + Shift + R | Renames a table or field throughout the diagram. Builder mode, with at least one table in the diagram |
| Format SQL / Formatter options | Ctrl + Shift + F | The options are always accessible |
| Clear editor | — | Needs text in the editor |
| Free mode | Ctrl + Shift + M | Detaches the Editor from the Builder (ch. 17). Builder mode only |
| Detach editor / Re-dock editor | — | Moves the Editor to a floating window and back |
28. Query menu
| Item | Shortcut | When it is active |
|---|---|---|
| Run | F5 | Needs SQL in the editor and a connection |
| Run as a script | F6 | As above, Builder mode only |
| Run Builder query | F7 | Runs the query of the active Builder tab. Needs the tree's connection |
| Begin / Commit / Rollback | Ctrl + B / Ctrl + M / Ctrl + Shift + B | Begin without an active transaction; Commit and Rollback with an active transaction |
| Generate DML → SELECT, SELECT keyset, INSERT, multiple INSERT, UPDATE, DELETE, MERGE, TRUNCATE | — | They generate the script from the diagram. Builder mode, with at least one table in the diagram |
| Generate DDL → CREATE TABLE + FK + INDEX, ALTER (differences from snapshot), CREATE VIEW, temporary table, PIVOT, UNPIVOT, COMMENT ON | — | As above (ALTER also applies in the Designer) |
| AI Query | Ctrl + Shift + A | Generates the query from natural language |
| Generate AI prompt | Ctrl + Shift + J | JSON prompt from the semantic schema. Needs a schema open in the Designer |
| Build diagram from the editor text | — | Creates the diagram from the SQL text. Needs text, connection, Builder |
29. View menu
| Item | Shortcut | When it is active |
|---|---|---|
| IntelliSense | Ctrl + Space | Turns autocompletion on/off |
| Line numbers | — | Show/hide in the editor |
| Messages panel | Ctrl + Shift + G | |
| Editor history | Ctrl + Shift + H | |
| Dark / light theme | — | Toggles the theme |
| Minimap, Side panel | — | Builder only |
| ER mode | — | Toggles Builder and ERD. Builder only |
| Fit to view / Zoom to selection | Ctrl + 0 / Ctrl + Shift + 0 | Builder mode, with at least one table in the diagram |
| Reset view | — | Builder only |
| Explain → Show plan, Plan graph, Compare plans, Hide | Ctrl + E (Show plan) | Needs connection and diagram; Hide only if the plan is visible |
30. Schema menu
Commands on the Builder diagram and on the schema tree.
| Item | Shortcut | When it is active |
|---|---|---|
| Import schema | Ctrl + I | Needs connection. Builder only |
| Refresh tree / Expand root | F5 (Refresh) | Needs the tree |
| Send schema to IntelliSense | Ctrl + Shift + I | Needs tree, editor and connection |
| Add table / Remove table | Ctrl + Shift + T / Del | Remove: needs a table |
| Duplicate table, Self-join | Ctrl + D | Builder mode, with at least one table in the diagram |
| Add / Remove relationship | — | Add: at least 2 tables are needed. Remove: at least one relationship |
| Add CTE, Note, Subquery, Procedure node | — | Builder only |
| Automatic aliases (duplicate tables / fields) | — | Assigns a, b, c… to tables; unique aliases to columns with the same name |
| Repair tab links | — | Rechecks the links between Builder and Editor tabs |
| WHERE → Add AND/OR group, Clear WHERE | — | Clear: only if there are conditions |
| Search in the diagram / Search dialog | Ctrl + F | Builder only |
| Select all / Deselect / Clear diagram | Ctrl + A / Esc | Builder mode, with at least one table in the diagram |
| Scalar function wizard | — | On the selected field |
| Analyze field | — | Statistics (MIN/MAX/AVG…). Needs connection |
| Parameters panel | Ctrl + Shift + P | Query :param panel. Needs connection |
31. DDL menu
Guided procedures for creating and modifying objects on the database. All items except Create table require an active connection.
| Item | Shortcut | When it is active |
|---|---|---|
| Table → Create, Edit (ALTER), Delete | — | Create also works without a connection; the rest requires a connection |
| Index → Create, Delete, Rebuild | — | |
| Trigger → Create, Delete, Enable/Disable | — | |
| View → Create, Delete | — | |
| Procedure → Create, Delete, Run with parameters | — | |
| Foreign key → Create, Delete | — | |
| Sequence → Create, Delete, Reset value | — | |
| Select top N rows | — | Shows the first N rows |
32. Connection menu
| Item | Shortcut | When it is active |
|---|---|---|
| New connection | — | Creates or selects an .fdc file |
| Connect / Disconnect | — | Connect if not connected; Disconnect if connected |
| Load connections | — | Loads the .fdc files from a folder |
| Enable / Disable auto-save | — | Diagram auto-save. Builder only |
| Database backup / restore | — | Guided procedures. Needs connection |
| Import data | — | Guided procedure. Needs connection |
| Run the active tab's query | — | Needs connection |
33. Tab menu
| Item | Shortcut | When it is active |
|---|---|---|
| New editor tab / Close editor tab | Ctrl + T / Ctrl + W | |
| New query tab | Ctrl + Alt + T | Adds a tab to the Builder |
| Remove query tab | Ctrl + Alt + W | Only if there is more than one tab |
| Rename query tab | — | The name is also propagated to the Editor |
34. Snippet menu
Snippets and Builder query history.
| Item | Shortcut | When it is active |
|---|---|---|
| Show snippets | Ctrl + Alt + S | Browses and inserts snippets. Builder only |
| Load / Save snippets | — | JSON file. Save only if snippets exist |
| Builder query history | — | Only if there are entries |
| Clear Builder history | — | Only if there are entries |
| Load / Save history | — | Save only if there are entries |
35. Tools menu
| Item | Shortcut | When it is active |
|---|---|---|
| Validate diagram | Ctrl + Shift + V | Checks consistency. Builder mode, with at least one table in the diagram |
| Performance Advisor | — | Index and performance analysis. Needs connection |
| Compare schemas | — | With a connection, or with tables in the diagram |
| Compare data | — | Needs connection |
| Compare schemas (Tag) | — | Needs the tree |
| Find usages | — | Where the selected object is used. Needs connection |
| User management / Change password / Log out user | — | Only when logged in; User management only for the administrator |
| Load demo | — | Sample diagram. Builder only |
| Shortcut guide | F1 |
36. Settings, Extras and ?
| Item | Shortcut | When it is active |
|---|---|---|
| Dialect | — | Standard, SQL Server, Oracle, MySQL, MariaDB, PostgreSQL, Firebird, InterBase, SQLite. At the bottom, with limited support in the Builder: MS Access, IBM DB2, Informix |
| Detect dialect automatically | — | Takes the dialect from the connection |
| Language | — | Italiano, English, Deutsch, Français, Español, Português, Русский, 中文, 日本語 |
| Extras → Convert SQL for local taxes (tributi) | — | Needs text in the editor |
| Extras → Drop management | — | Drop wizard |
| ? → About, License, Register me | — | Always |
37. Menus of the other modes
When you switch to another tab, the dedicated menus appear; the Designer is described in part C; ETL is described in part D; Analysis is described in part F.
| Menu | Content |
|---|---|
| Schema Designer | Add table and FK, delete selection, auto layout, zoom (Ctrl+=, Ctrl+-); import from DDL or from database, refresh from database; generate DDL (F7) and REST; save, load and export (SVG, PNG, semantic package, dialect); star schema analysis (part H); schema tabs (Ctrl+Alt+N / Ctrl+Alt+W); AI generation (Ctrl+Shift+I) and refinement. |
| ETL | New stage, auto-match, edit and delete stages; fields (new, edit, delete, generate); stage and source/destination connections from the tree; save and load ETL project, save for the service; Run (F9) and Cancel. |
| Analysis | Refresh (F5); export to PNG, SVG, CSV; save and load the configuration. |