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.

PlyxSQL — FireDAC SQL ManagerMenu bar and toolbarthey change with the active tab — part I and chapters 25–37Treeconnections and databasesPart A: connectingPart B: tree and TablesTablesPart BBuilderCh. 1–14DesignerParts C, G, HETLPart DAnalisysPart FPerformance-ProfilerPart EBuilderdiagram of tables and JOINs → SQLbar Run · Schema · ERD · Explain…query tabs, snippets, AIDesignercanvas of tables and foreign keyssemantic configuration for AI (G)Star schema analysis (H)ETLphases, fields, rulesrun and scheduleAnalysischarts from the queryexport PNG · SVG · CSVPerformance-Profilermeasures the Editor queryplan, historyStar schema (H)facts, dimensions, queriesETL ↔ star (H16)SQL Editor (ch. 15–24)at the bottom, shared: SQL text, results, messages, transactions, historyArrows: the Builder query goes to the Editor, Analysis and Profiler; the Designer opens the star schema, which links to the ETL.
PartTopicWhat you find
AConnectionsConnection types, network, file, ODBC, SSH, saving→
BTree and TablesDatabase tree, search, export, editing data and structure→
CDesignerVisual schema, foreign keys, roles, DDL→
GSemantic configurationAliases, lookups, calculated fields, filters, AI prompt, package→
HStar schemaFacts, dimensions, grain, guided queries, ETL link→
DETLPhases, field rules, running, scheduling→
EPerformance-ProfilerMeasurements, thresholds, execution plan, history→
FAnalysisCharts and maps from query results→
IToolbarsThe buttons of every tab, with numbered screenshots→
1–14BuilderDiagram, JOINs, fields, command bar, tabs, saving→
15–24SQL EditorWriting and running SQL, results, transactions, history→
25–37MenuAll 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.

TabVisible menusToolbar groups
Tables / BuilderFile, Modifica, Query, Vista, Schema, DDL, Connessione, Tab, Snippet/History, Strumenti, Impostazioni, Extra, ?1, 2, run, transactions, editor, AI, compare (I2)
DesignerFile, Modifica, Schema Design, DDL, Connessione, Tab, Snippet/History, Strumenti, Impostazioni, Extra, ?1, 2, schema, view, generate, import/save, tabs, AI, compare (I3)
ETLFile, Impostazioni, ETL, ?1, 2, phases, fields, connections, project, run (I4)
AnalisysFile, Impostazioni, Analytics, ?1, 2, configuration, export, refresh (I5)
Performance-ProfilerFile, Impostazioni, Analytics, ?same as Analisys (I6)
Button orderIn the screenshots the buttons of the Designer, ETL, Analisys and Profiler tabs appear in the reverse order of the menu: the tables follow the order you see them in, left to right.

Groups common to all tabs

#ButtonWhat it doesKeys
1New projectStarts an empty project. If the current one has changes it asks whether to save them.Ctrl+N
1Open projectOpens a saved project file (connections, tabs, diagrams).Ctrl+Shift+O
1Save projectSaves the project. The window title shows * while there are unsaved changes.Ctrl+S
1Save asSaves the project to a new file.Ctrl+Shift+S
2ConnectOpens the active connection (the one selected in the tree).—
2DisconnectCloses the active connection.—

I2. Builder

Toolbar with the Builder tab: groups numbered 1 to 7

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:

#ButtonWhat it doesKeys
3RunRuns the current SQL in the Editor, or the selected part.F5
3Run as scriptRuns the text as a multi-statement script.F6
3Run Builder queryGenerates and runs the query of the active Builder tab.F7
4Begin transactionBEGIN TRANSACTION: the following statements stay pending.Ctrl+B
4CommitMakes the changes of the transaction permanent.Ctrl+M
4RollbackUndoes the changes of the transaction.Ctrl+Shift+B
5UndoUndoes the last operation.Ctrl+Z
5RedoRedoes the undone operation.Ctrl+Y
5Format SQLReformats the current SQL text.Ctrl+Shift+F
5Copy allCopies the whole SQL text to the clipboard.Ctrl+Shift+C
6AI queryGenerates the query from a natural-language request.Ctrl+Shift+A
6Build from Editor SQLReads the Editor SQL and draws tables and JOINs in the Builder.Ctrl+Shift+Q
7Schema compareCompares the schema of two connections and shows the differences.—
7Data compareCompares the data of two tables.—
7Tag compareCompares 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

Toolbar with the Designer tab: groups numbered 1 to 9

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).

#ButtonWhat it doesKeys
3Generate AI promptGenerates the JSON prompt from the configured semantic schema (AI prompt check window).Ctrl+Shift+J
3Refine with AIAsks the AI to modify the schema already on the canvas.—
3Generate with AIDescribe the schema in words and the program proposes it.Ctrl+Shift+I
4Rename schema tabRenames the active schema tab.—
4Close schema tabCloses the active schema tab.Ctrl+Alt+W
4New schema tabAdds a new schema tab to the Designer.Ctrl+Alt+N
5Export PNGSaves the diagram as a PNG image.—
5Export SVGSaves the diagram as a vector SVG image.—
5Clear schemaEmpties the schema (asks for confirmation).—
5Load schemaLoads a .sdschema schema file (JSON), with the star model if present.—
5Export semantic packageExports the Semantic Configurator to a portable JSON for a server component (G18).—
5Save schemaSaves the schema to a file (tables, columns, FKs, positions, star model).—
5Update from databaseRe-aligns the schema already on the canvas with the database.—
5Import from databaseReads the schema from the active connection, choosing the tables.—
5Import from DDLBuilds the schema from a DDL script.—
6Export dialectExports the DDL in the chosen dialect.—
6Generate RESTThe Generate REST item of the Schema Design menu.—
6Generate ALTERGenerates the ALTER TABLE statements for the differences against a snapshot.—
6Generate DDLGenerates the schema DDL script in the Editor’s Schema Designer tab (C8).F7
7Zoom outZooms the canvas out.Ctrl+-
7Zoom inZooms the canvas in.Ctrl+=
7Zoom to fitFits the zoom to show the whole schema.Ctrl+Shift+0
7Auto layoutArranges the tables automatically.—
8Delete selectionDeletes the selected tables or foreign keys.—
8Add foreign keyCreates a foreign key between two tables.—
8Add tableAdds a table to the canvas.—
9Schema compareCompares the schema of two connections and shows the differences.—
9Data compareCompares the data of two tables.—
9Tag compareCompares schema tags (versions).—
Run in the DesignerIn the Designer the Editor tab holds the DDL and “Run” runs it as a script, statement by statement. If the Editor contains a single SELECT (for example the one opened from the star schema window) it runs as a query and the results appear in the results panel, at the bottom.

I4. ETL

Toolbar with the ETL tab: groups numbered 1 to 7

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.

#ButtonWhat it doesKeys
3Cancel runStops the import in progress.—
3Run ETLRuns the phases of the ETL project (D8).F9
4Save for serviceSaves a .petl file for the Windows service of scheduled imports (D9).—
4Load ETL projectOpens a saved ETL project.—
4Save ETL projectSaves the ETL project to reopen it in this application.—
5Destination connectionSets the connection selected in the tree as the destination.—
5Source connectionSets the connection selected in the tree as the source.—
6Generate fieldsCreates the fields of the phase from the columns.—
6Delete fieldDeletes the selected field.—
6Edit fieldOpens the rules of the selected field (D5).—
6New fieldAdds a field to the phase.—
7Delete all phasesDeletes all phases (asks for confirmation).—
7Delete phaseDeletes the selected phase.—
7Edit phaseOpens the phase settings (D4).—
7Generate phases from matching tablesCreates a phase for every pair of same-named tables in source and destination.—
7New phaseAdds a phase to the project.—

I5. Analisys

Toolbar with the Analisys tab: groups numbered 1 to 5

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.

#ButtonWhat it doesKeys
3Load configurationLoads the chart configuration from a file.—
3Save configurationSaves the chart configuration to a file.—
4Export CSVExports the chart data to CSV.—
4Export SVGExports the chart as SVG.—
4Export PNGExports the chart as a PNG image.—
5RefreshRe-reads the query data and redraws the chart.F5

I6. Performance-Profiler

Toolbar with the Performance-Profiler tab: the same groups as Analisys

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

WhereWhat 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 schemaThe 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.

Star schema analysis window just opened

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.

Star schema analysis window with a query of three FACT_BUDGET measures

Buttons

ButtonWhat it does
Suggest model from schemaDraft of facts and dimensions from the foreign keys (H3).
Validate modelWrites the STARxxx messages in the bottom box (H14).
+ Attribute / + Measure / + FilterAdd the node selected in the tree to the query.
Grain columns…, Apply to model, + Derived…Edit the model (H4, H11).
Delete fact/dimensionRemoves the selected node from the tree.
Copy SQLCopies the query to the clipboard.
RunRuns 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 editorMoves 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 BuilderOnly 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:

Designer with the star query in the Editor and the result in a grid

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:

TabWhat it is for
NetworkDatabase servers: Firebird, InterBase, MySQL / MariaDB, SQL Server, PostgreSQL, Oracle, Informix, IBM DB2, plus SQLite and MS Access as files.
File / LocalSQLSQLite, CSV, Excel / ODS, JSON, XML, fixed-position layouts, ClientDataSet, FDMemTable and REST APIs: files become tables you can write SQL against.
ODBCAny source with an installed ODBC driver, or a DSN already defined in Windows.
SSH TunnelNot 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

Start screen with New project

At startup the start screen appears, with three columns and two buttons:

ItemWhat 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 recentRecently 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.
FavoritesThe 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 coursesA list of video tutorials linked online.
Open projectOpens a .mfdproj project file with the file-picker window. The project opens and the start screen gives way to the workspace.
Dark / LightTheme choice.

A3. Opening the New Connection dialog

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

New Connection dialog, Network tab1234567891011
  1. Connection alias
  2. Tabs: Network, File / LocalSQL, ODBC, SSH Tunnel
  3. Driver
  4. Server / host and Port
  5. Database / path with Browse…
  6. User name and Password
  7. Create database if it does not exist
  8. Additional parameters (Key=Value)
  9. Test connection
  10. Advanced parameters…
  11. Connect and add

When you choose the driver, port and user are set to their default values:

DriverPortUserNotes
FB (Firebird)3050SYSDBADatabase = name or path on the server, for example SB.FDB
IB (InterBase)3050SYSDBA
MySQL3306rootAlso valid for MariaDB (see note)
MSSQL (SQL Server)1433sa
PG (PostgreSQL)5432postgres
Ora (Oracle)1521sys
Infx (Informix)9088informix
IBM (DB2)50000db2inst1
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.

MariaDBFor MariaDB, use the MySQL driver. The MariaDB dialect is chosen manually for that connection (Settings → Dialect) and is saved in the project.

A5. File / LocalSQL tab

File / LocalSQL tab with the type drop-down

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.

TypeWhat it does
SQLite — fileOpens 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 — ClientDataSetFolder with .cds .cdc .aff .icp files; each file is a table.
LocalSQL — FDMemTableFolder with .bin .mem files.
LocalSQL — CSVFolder with .csv / .txt files, imported as in-memory tables.
LocalSQL — SQLite filesFolder with .db / .sqlite files: the databases are attached with ATTACH DATABASE.
LocalSQL — Fixed-position layoutFixed-width text files described by a layout.
REST — JSON APIHTTP 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

A6. ODBC tab

Lists the ODBC drivers installed on the computer (in alphabetical order). You can use it in two ways:

For file-based drivers (Excel, Access…) the … button next to Database lets you choose the file.

A7. SSH Tunnel tab

SSH Tunnel tab

Needed when the database cannot be reached directly but only through an SSH server. Check Enable SSH tunnel and fill in:

FieldMeaning
SSH HostAddress of the SSH server (for example bastion.myserver.com).
SSH portDefault 22.
SSH userUser for logging in to the SSH server.
SSH passwordOptional. It is not saved in the project for security: it must be re-entered at every session.
.ppk/.pem keyPrivate key file, as an alternative to the password.
Requires plink.exe (PuTTY)The program looks for it in 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

ButtonWhat it does
▶ Test connectionTries 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 addOpens the connection, saves it and adds it to the tree, automatically creating the linked tabs in the Editor and in the Builder.
CancelCloses 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

What is not savedThe SSH password is never written to the project. The internal option 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):

NodeItems
Connections folderNew connection, Connector management, Open connectors folder, Compare schemas, Compare data, Refresh.
.fdc fileConnect; 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

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

  1. 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.
  2. 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.
  3. 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.

PaginationLong results are downloaded in pages. Each endpoint declares whether the next-page token must be passed as a parameter or whether the response already contains the full URL to call.

A12. Common problems

SymptomCause 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 plinkPuTTY is not installed or plink.exe is not in the PATH. Install it (see A7).
The test succeeds but the connection is offlineThe test does not save or add anything: you must press Connect and add.
The Firebird database does not existCheck 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 reopeningREST 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 columnWrong 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.

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

Table data view1234567891011
  1. Title: the table being shown
  2. Navigation bar: first, previous, next, last, refresh
  3. More rows: loads the next block
  4. Column the filter acts on
  5. Operator of the filter
  6. Value to search for
  7. Apply and X (clears the filter)
  8. Counter: position and total rows
  9. Export: CSV, JSON, XML, XLSX, MemTable, ClientDS
  10. Filter=: filters on the value of the cell
  11. 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

OperatorMeaning
=Equal
containsText contained in the value
>   <Greater, less
<>Not equal
is null / is not nullValue absent or present

The available operators depend on the type of the chosen column (for example, numeric ones on a numeric field).

The filter does not change the SQLIt acts on the rows already loaded in the grid. To filter on the database side, use the Builder or the Editor.

B4. Exporting data

ButtonResult
CSV.csv file
JSON.json file
XML.xml file
XLSXExcel workbook; when finished, it asks whether to open it in Excel
MemTableBinary .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.

Not always availableEditing works only on connections to a real, connected database. It is blocked on LocalSQL connections (CSV, Excel, REST, JSON…): in that case the button pops back up and a warning appears. If you switch table to a non-writable connection while editing, the grid goes back to read-only.

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:

NodeMenu items
Tables folderCreate table, Refresh
TableOpen 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 indexCreate index · Rebuild · Delete
Triggers folder / a triggerCreate trigger · Enable / Disable · Delete
Constraints folder / a constraintAdd foreign key · Delete constraint
Views folder / a viewCreate view · Open in Builder · Create trigger on view · Find usages · Show dependencies · Delete
Sequences folder / a sequenceCreate sequence · Reset value · Delete
Stored Procedures folder / a procedureCreate procedure · Create from SQL body · Run · Show dependencies · Delete
Database rootRefresh, Server capabilities, SQL matrix, ERD, Compare schemas/data, Backup, Restore, Export/Import team package, Disconnect

What the guided procedures ask for

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.

Schema Designer1234567
  1. Designer toolbar
  2. Schema tab (in the example sbt_Schema1)
  3. Find table, Go and Bring closer
  4. Side bar with filters and layout
  5. Table: header with name and role (MST = master), columns with type
  6. Relationship (foreign key)
  7. 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

ButtonFunction
▦Show all
⊞Tables only
□Views only
λProcedures and functions only
☰Views… (view management)
ƒStored Proc… (procedure management)
⤆Auto layout: arranges the tables automatically
1:1Zoom 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

  1. Add a table: Schema Designer → Add table menu or right-click on the canvas.
  2. In the Edit table window enter Table name, Comment, Note and, for lookup tables, the label field name.
  3. Add the columns (right-click the table → Add column). For each column:
FieldMeaning
Column name, TypeName and data type; for a type not in the list use Custom type.
Length, Precision, ScaleSize of the type (for example VARCHAR(30) or DECIMAL(15,2)).
Default, CHECK constraint, CommentDefault value, check constraint and comment.
Nullable, Primary Key, Auto Inc, UniqueCheckbox options. Auto Inc generates the identity the right way for the dialect (on Firebird, a sequence and a trigger).

From something that already exists

C4. Relationships (foreign keys)

  1. Right-click the starting column → Draw FK.
  2. 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.

Distant tables?Use Bring closer in the top bar to bring the target table next to the starting one, then draw the FK.

C5. Moving around the canvas

GestureEffect
Drag a tableMoves it
Drag the table borderResizes it
Mouse wheel over a long tableScrolls the fields (▲ ▼ arrows in the indicator row)
Double-click a columnOpens column editing
Double-click the headerOpens table editing
MinimapDrag to move the view
Ctrl+= / Ctrl+-Zoom in / out
Ctrl+Shift+0Fits the schema to the window
DelDeletes the selected table or FK
EscCancels 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):

RoleLabel
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

  1. Press F7 (or Generate DDL in the Schema Designer menu, or right-click the canvas).
  2. In the Export DDL window choose the Dialect, the server Version and, if needed, Include DROP statements.
  3. 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 schemaJSON 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 SVGImage of the diagram
Clear schemaClears the whole canvas (can be undone)
New schema tab (Ctrl+Alt+N), Close (Ctrl+Alt+W), RenameSeveral schemas open together, each in its own tab
Add table / FK, Delete selection, Auto layoutAs from the side bar and the context menu
Zoom in / out / fitCtrl+=, 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:

  1. Question in natural language.
  2. Table detection: the program picks the configured tables whose name or keywords appear in the question.
  3. JSON prompt: schema, aliases, lookups, filters, constraints and parameter values are assembled into a single structured document.
  4. Sending to the AI (Anthropic, OpenAI or Ollama, depending on the active profile) and receiving the SQL.
  5. Safety check: only a read query is allowed (G12).
  6. Verification run with a row limit, and the option to save the question/SQL pair as a verified example.
A layer separate from the schemaThe semantic configuration is a layer independent of the DDL: it does not change the tables, does not enter the generated script and does not mix with the schema's undo/redo. Each configuration is tied to the table through an internal identifier, not by name: if you rename the table, the configuration follows it.

G2. Where to open it

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

Semantic configuration of a table, General tab12345678
  1. Tabs: General, Field aliases, Lookups, Calculated, Keyword filter, Fixed filters, Examples
  2. Table alias
  3. Description
  4. Allowed roles [RBAC]
  5. Table identification keywords
  6. Active fields
  7. + All / − None
  8. 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.

In the Designer it is only a previewRole restrictions are enforced by the server component, which receives an already authenticated role. In the Designer they are used to configure and to see "what this role would see": they are not a security control.

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:

SymbolMeaningExample
+ORinsegne+insegna: "insegne" or "insegna" appears
&ANDinsegne&insegna: both appear
-NOT-occupazione: "occupazione" does not appear
( )Grouping-(a+b+c): none of the three appears
space, comma, ;Implicit AND between two termsa 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.

Watch out for the screenshot exampleinsegne+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:

ColumnMeaning
FieldThe physical field. If you change its name by mistake and it no longer matches a real field, the row is ignored
AliasDomain 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.
RolesRoles allowed to see the field, comma-separated; empty = no restriction (preview only in the Designer)

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:

ColumnMeaning
FieldThe field of this table (key towards the other)
Ref. tableThe table to link. It can contain placeholders such as [[COMUNE]]
Ref. fieldThe corresponding field in the linked table
Display fieldThe field to show (the name, the description)
Cardinality1:1, 1:N, N:1 (optional). The most common case is N:1: many rows point to the same reference row
JoinINNER, 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}%'
Single curly braces and double square brackets{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:

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:

ColumnContent
QuestionThe typical question, as an operator would write it
SQLThe right query
Regression testYES (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

Global semantic configuration12345678910
  1. General constraints
  2. Custom key: Parameter, Key, Value, Default
  3. Default column
  4. + Row adds a row to the grid above
  5. Aggregation aliases
  6. Prefill standard
  7. Configurable roles
  8. Regression suite
  9. Telemetry
  10. 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:

ColumnMeaningExample
ParameterThe name of the placeholder (without the brackets)COMUNE
KeyThe readable label the operator uses in the question. It accepts the same syntax as keywords, so synonyms such as riccione+rnriccione
ValueThe text that takes the place of [[PARAMETER]] in queriesSB
DefaultYES marks the default value of that ParameterYES

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.

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:

ExpressionMeaning
YEAR(TODAY()) · YEAR(TODAY())-1 · YEAR(TODAY())+1Current, 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.

Generate AI prompt window1234567891011121314151617
  1. Question
  2. Keep the context of the previous question
  3. Preview as role
  4. Dialect
  5. Version
  6. Active AI: profile, provider and model
  7. ⚙: opens the AI Configuration
  8. Generate prompt
  9. Copy
  10. Send to AI
  11. Insert in editor
  12. Test SQL
  13. ✓ Verify and save as example
  14. Steps: log of the sending
  15. JSON preview / AI response
  16. Outcome and rows of the verification run
  17. Close

Fields at the top

FieldMeaning
QuestionThe question as an operator would write it ("how many permanent occupations in Riccione in 2025?").
Keep the context of the previous questionCheck 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 roleSimulates 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, VersionThe 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 AIThe 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

ButtonWhat it doesEnabled when
Generate promptBuilds 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
CopyCopies 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 AISends 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 editorTakes 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 SQLTries 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 exampleSaves 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
CloseCloses the window with no other action.Always

What happens after "Send to AI"

  1. 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).
  2. 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.
  3. If it is allowed, it is run for verification on the current connection, with a row limit. In case of error ✗ Execution failed: message appears and the error is sent back to the AI for a new attempt (automatic correction).
  4. 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.
  5. If communication with the AI fails, the status says ✗ Communication error with the AI — see above and 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:

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.

Save the schemaThe example is written to the Designer's in-memory configuration: to make it permanent you must save the schema to a file. There is no hidden automatic saving, as with every other change made in the semantic configuration dialogs.
StatisticsEvery verification run, even a failed Test SQL, is recorded in the telemetry (G13): a hand-written query that does not pass verification is useful data for understanding what the configuration is missing.
An active AI profile is requiredFor Send to AI a profile is needed (G17). Without one, the rest of the window still works: you can generate the prompt, copy it into an external AI and try the SQL with Test SQL.

What the prompt contains

SectionSource
tags, questionDialect and version; the question
constraintsGeneral constraints (G10)
tablesReal schema of the included tables (fields, primary keys) plus aliases and description
custom_semantic / custom_semantic_matchesCustom keys and, for each, whether the question names it
query_fieldsThe active fields (G3)
field_aliasesField synonyms (G4), human identifier
lookupsLinks between tables (G5)
calculated_fieldsCalculated fields (G6)
matched_filtersKeyword filters that triggered (G7)
always_filtersFixed filters (G8)
examplesExamples (G9)
aggregate_aliasesAggregation vocabulary (G10)
previous_turnPrevious question and SQL, for conversations (see below)

G12. Security

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").

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

  1. Global configuration: write the general constraints, define the custom keys (municipality, year…), press Prefill standard for the aggregation aliases and add the company terms.
  2. For the main tables (not all at once): alias, description, keywords and active fields. Remove the useless fields.
  3. Press Import from FK in the Lookups tab and complete cardinality and join where known.
  4. Add the mandatory fixed filters (logical deletion, tenant) and the recurring keyword filters.
  5. Write 2–3 examples for the difficult patterns.
  6. 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.
  7. Launch the Regression suite and re-run it at every important change.

G15. Common problems

SymptomCause and remedy
The right table does not enter the promptNo 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 triggerThe 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 municipalityThe 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 fieldsToo 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 rightThe comparison is textual: verify by hand. If the example is only for style, set Regression test = NO (G9).
A role still sees a reserved tableIn 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.

Semantic graph12345678
  1. Schema tab (here AI_SEMANTIC_Schema1; the asterisk indicates unsaved changes)
  2. ⚙ Global config (G10)
  3. ⇄ to go back to the physical schema
  4. Node of a configured table (selected: orange border)
  5. Counters for filters and calculated fields
  6. Lookup relationship with cardinality and join
  7. Minimap
  8. Node without keywords: the table will never be recognized from the text of a question

How to read a node

PartContent
HeaderThe 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 rowThe first identification keyword, or — no keyword.
N filters chipHow many keyword filters the table has (G7)
N calc. chipHow 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.

Quick checkBefore trying questions, look at the graph: a node without keywords will never trigger; an isolated table (no curve) will not be linked to the others; a 0 filters counter on a table that should have some is the sign of a forgotten filter.

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.

AI configuration12345678910
  1. Configured profiles (● = active)
  2. Provider: Anthropic, OpenAI, Ollama
  3. Model
  4. Endpoint
  5. Ollama parameters (Ollama only)
  6. Context window (num_ctx) and max tokens (num_predict)
  7. Timeout in ms
  8. Save profile
  9. ● Activate
  10. Close
FieldMeaning
Configured profilesThe 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.
ProviderAnthropic (Claude), OpenAI, Ollama (local model, on your computer or on a server of your own)
ModelThe model name (in the screenshot gpt-oss:20b, an Ollama model; for Claude, for example, claude-sonnet-4-6)
API keyFor Anthropic and OpenAI. Not needed for Ollama.
EndpointAddress 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 threads0 = 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

  1. Open File → AI Configuration.
  2. Choose + New (or edit "Default"), give it a name, choose the provider, model and, if needed, key and endpoint.
  3. Press Save profile, then ● Activate: the program confirms that this profile will be used by every window that calls the AI.
  4. At the bottom you find Active AI: profile name. With several profiles you activate one at a time.
The API key does not move to another computerThe key is saved protected with Windows (DPAPI): tied to the user and the machine. Opening the profile from another computer or with another Windows user, the key appears empty and must be re-entered, without errors. The settings file is in the user's folder (%APPDATA%\PGSOFT\plyxsql).
First time after an updateIf AI settings from a previous version already existed, they are automatically migrated to a profile called Default, activated automatically; the old plain-text key is deleted after the migration.
Local or cloud?With Ollama the schema data and the questions stay on your computer or your network: useful with confidential data. Cloud services (Anthropic, OpenAI) are normally more accurate on complex queries but send the prompt to an external service. Choose according to the confidentiality required.

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.

Export semantic package
  1. From the Schema Designer menu choose Export semantic package (server)….
  2. If no table has a configuration, a message invites you to configure at least one (right-click the table → ⚙ Configure semantics…) before exporting.
  3. 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.
  4. At the end a message reports how many configured tables were exported.
Star schemaThe star schema (part H) is a layer separate from the semantic configuration: it is not included in the semantic package.

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:

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.

TableRoleRowsContent
DIM_DATEdate dimension365every day of 2026; DATE_KEY is the date as YYYYMMDD
DIM_CUSTOMERdimension20customers with city, region, country and segment
DIM_PRODUCTdimension10products with category (5) and brand
DIM_STOREdimension5stores with channel: Retail, E-commerce, B2B
DIM_CAMPAIGNdimension5campaigns with type, channel, start and end dates
FACT_SALESfact1,607one row per order line: quantity, price, discount, revenue, cost, margin
FACT_ORDER_HEADERfact786one row per order: channel, payment, status (no measures)
FACT_RETURNSfact137one row per return: quantity, refund, reason, resolution
FACT_INVENTORY_SNAPSHOTfact600month-end stock per product and store (10 × 5 × 12)
FACT_BUDGETfact300budget and actuals per month, category and store (12 × 5 × 5)
FACT_CAMPAIGN_PERFORMANCEfact122impressions, 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.

To reproduce the examplesThe script is for Firebird 3 and drops and recreates the tables and views with these names: run it with 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.
Where the numbers come fromEvery result in this part was obtained by running the SQL generated by the program on the script’s data, and compared with an independent calculation and with the script’s views: the totals match. Engines may round the last decimal digit differently.

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.

WhatHow it works
Where the model livesIn the "star_model" section of the schema file (Save schema, C9). A file saved before this feature opens normally, with an empty model.
ReferencesThe model refers to tables and columns by identifier, not by name: renaming a table or a column in the Designer does not break it.
ChangesIf 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 packageThe star model is separate from the semantic configuration: it is not included in the package of G18.
OutputThe 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

ItemRule
FactA 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).
HeaderA 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.
DimensionEvery 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…).
MeasuresNon-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…).
DegenerateDocument 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.
SCDA 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.
CalendarIn 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

FactTypeDimensionsMeasuresDegenerate
FACT_SALEStransactionalDIM_DATE, DIM_CUSTOMER, DIM_PRODUCT, DIM_STOREQUANTITY, UNIT_PRICE, DISCOUNT_AMOUNT, REVENUE, COST_AMOUNT, GROSS_MARGINORDER_NUMBER, ORDER_LINE
FACT_ORDER_HEADERfactlessDIM_DATE, DIM_CUSTOMER, DIM_STORE—ORDER_NUMBER, ORDER_CHANNEL, PAYMENT_METHOD, ORDER_STATUS
FACT_RETURNStransactionalDIM_DATE, DIM_CUSTOMER, DIM_PRODUCT, DIM_STORERETURNED_QUANTITY, REFUND_AMOUNTORIGINAL_ORDER_NUMBER, ORIGINAL_ORDER_LINE, RETURN_REASON, RESOLUTION_TYPE
FACT_INVENTORY_SNAPSHOTperiodic snapshotDIM_DATE, DIM_PRODUCT, DIM_STOREQUANTITY_ON_HAND, QUANTITY_RESERVED, QUANTITY_AVAILABLE, QUANTITY_INCOMING, INVENTORY_VALUE—
FACT_BUDGETtransactionalDIM_STOREREVENUE_TARGET, REVENUE_ACTUAL, REVENUE_VARIANCEBUDGET_YEAR, BUDGET_MONTH, PRODUCT_CATEGORY
FACT_CAMPAIGN_PERFORMANCEtransactionalDIM_CAMPAIGN, DIM_DATEIMPRESSIONS, 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.

Always review

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.

FactGrain (one row is…)Grain columns
FACT_SALESOne row per order line sold (ORDER_NUMBER + ORDER_LINE)ORDER_NUMBER, ORDER_LINE
FACT_ORDER_HEADEROne row per orderORDER_NUMBER
FACT_RETURNSOne row per return recordedRETURN_KEY
FACT_INVENTORY_SNAPSHOTOne row per product, store and month endSNAPSHOT_DATE_KEY, PRODUCT_KEY, STORE_KEY
FACT_BUDGETOne row per month, product category and storeBUDGET_YEAR, BUDGET_MONTH, PRODUCT_CATEGORY, STORE_KEY
FACT_CAMPAIGN_PERFORMANCEOne row per campaign and dayCAMPAIGN_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:

TypeMeaningIn the example schema
transactionalOne row per event, never changedFACT_SALES, FACT_RETURNS, FACT_CAMPAIGN_PERFORMANCE, FACT_BUDGET
periodic snapshotOne row per subject at every period (the month-end photograph). Requires the time roleFACT_INVENTORY_SNAPSHOT
accumulating snapshotOne row per process, updated at every step (ordered, shipped, delivered), with several datesnone
factlessRecords 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.

AdditivityRuleExamples in the schema
additiveSums along every dimension.REVENUE, QUANTITY, COST_AMOUNT, REFUND_AMOUNT, MARKETING_SPEND
semi-additiveSums 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-additiveNever 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:

FieldWhat it decides
Additivityadditive, semi-additive (does not add up over time), non-additive
AggregationThe aggregation used when you do not pick one: SUM, COUNT, COUNT DISTINCT, AVG, MIN, MAX, LAST, FIRST
Over timeOnly 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).

The unit price caseOn 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

DimensionSurrogate keyNatural keyAttributesFacts using it
DIM_DATEDATE_KEYCALENDAR_DATE95
DIM_CUSTOMERCUSTOMER_KEYCUSTOMER_CODE73
DIM_PRODUCTPRODUCT_KEYPRODUCT_CODE53
DIM_STORESTORE_KEYSTORE_CODE55
DIM_CAMPAIGNCAMPAIGN_KEYCAMPAIGN_CODE61

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:

FactDIM_DATEDIM_CUSTOMERDIM_PRODUCTDIM_STOREDIM_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.

In the example schemaNo fact has two FKs to the same 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] │
└──────────────────────┴──────────────────────┴────────────────────────────┘
  1. Suggest model from schema — draft of facts and dimensions (H3)
  2. Validate model — STARxxx messages (H14)
  3. Facts / Dimensions tree — below it, a hint line for the selected node
  4. Properties — grain and type of the fact, additivity and aggregations of the measure, role name; Apply to model, + Derived…, Delete derived, Grain columns…
  5. Group by (rows) — + Attribute, ↑ Up, Remove
  6. Measures (columns) — + Measure, aggregation of the selected measure, Remove
  7. Filters — operator, value, + Filter, Remove
  8. Subtotals (ROLLUP) and Exclude facts with no dimension row (INNER JOIN)
  9. Generated SQL — updated at every change; the line above states the shape of the query
  10. Messages — QRYxxx codes of the query (H14)
  11. 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

ActionEffect
Double-click an attributeAdds it to Group by. Works for attributes under a role, hierarchy levels and degenerate columns.
Double-click a measureAdds it to Measures. Row count counts the rows of the fact at its grain, with COUNT(*).
Select an attribute and press + FilterAdds a filter with the operator and value written next to it (H8).
Change the aggregation in the Measures listSelect the measure in the list and pick the aggregation: the SQL updates. “default” uses the one in the model.

Where the SQL goes

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?

  1. Open FACT_SALES › Dimensions › DIM_PRODUCT and double-click CATEGORY; then DIM_STORE and double-click CHANNEL. They appear in Group by, in the order you pick them.
  2. Open FACT_SALES › Measures and double-click REVENUE and GROSS_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
CATEGORYCHANNELREVENUEGROSS_MARGIN
AudioB2B29,865.658,448.65
AudioE-commerce23,280.256,681.25
AudioRetail64,796.7018,271.70
MonitorB2B37,878.609,983.60
MonitorE-commerce50,092.2513,217.25
MonitorRetail136,592.6535,516.65

First 6 rows of 15.

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:

AttributeOperatorValue
DIM_CUSTOMER › SEGMENTINBusiness; Enterprise
DIM_DATE › CALENDAR_DATEBETWEEN01/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
BRANDREVENUEQUANTITYROW_COUNT
DataFlash18,994.3012461
KeyLab12,896.5514957
SoundCore19,486.1513458
TechNova143,038.9513353
ViewMax47,002.0513558

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.

CategorySUM over the whole year (wrong)LAST over time (right)
Audio15,3081,542
Monitor14,1141,320
Notebook15,1591,583
Periferiche14,6361,151
Storage15,6911,667
Total74,9087,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
CATEGORYQUANTITY_ON_HANDINVENTORY_VALUE
Audio1,542120,707.00
Monitor1,320325,117.00
Notebook1,5831,113,301.00
Periferiche1,15164,264.00
Storage1,667173,284.00
  1. base: sums the measures by category and by instant (one row per category and month end).
  2. bounds: finds the first and last instant for each category.
  3. 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_QUARTERQUANTITY_ON_HAND
16,021
26,272
36,245
47,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):

CATEGORYQUANTITY_ON_HAND
Audio1,275.67
Monitor1,176.17
Notebook1,263.25
Periferiche1,219.67
Storage1,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.

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
CATEGORYREVENUE (FACT_SALES)REFUND_AMOUNT (FACT_RETURNS)
Audio117,942.606,809.20
Monitor224,563.508,743.55
Notebook784,707.1547,179.95
Periferiche72,743.603,849.60
Storage109,530.255,918.40

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_QUARTERREVENUE (FACT_SALES)REFUND_AMOUNT (FACT_RETURNS)
1271,409.8517,457.75
2370,535.4524,967.30
3354,635.8515,333.20
4312,905.9514,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:

CHANNELREVENUE (FACT_SALES)REVENUE_TARGET (FACT_BUDGET)
B2B262,133.551,568,118.00
E-commerce327,485.851,540,501.00
Retail719,867.704,750,247.00
Mind the example dataIn the script the budget (about 7.9 million) and the actuals of 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.

  1. Select the fact (or one of its measures) in the tree.
  2. Press + Derived… under the Properties panel.
  3. Type the name (for example ROAS) and then the formula.
FactDerived measureFormula
FACT_SALESMargin %100.0 * {GROSS_MARGIN} / NULLIF({REVENUE}, 0)
FACT_BUDGETVariance %100.0 * {REVENUE_VARIANCE} / NULLIF({REVENUE_TARGET}, 0)
FACT_CAMPAIGN_PERFORMANCECTR %100.0 * {CLICKS} / NULLIF({IMPRESSIONS}, 0)
FACT_CAMPAIGN_PERFORMANCEROAS{ATTRIBUTED_REVENUE} / NULLIF({MARKETING_SPEND}, 0)

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_NAMEMARKETING_SPENDATTRIBUTED_REVENUECTR %ROAS
Audio Summer107,808.52668,136.392.606.20
Back to School61,848.80497,109.742.848.04
Black Friday Tech69,085.03377,010.132.945.46
Corporate Upgrade267,588.531,699,799.792.486.35
Spring Storage73,293.74472,148.292.236.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_MONTHREVENUE_TARGETREVENUE_ACTUALVariance %
1638,703.00623,183.23-2.43
2699,041.00674,052.95-3.57
3641,032.00625,490.01-2.42
4608,257.00571,909.76-5.98
5649,732.00632,342.24-2.68
6708,807.00685,392.78-3.30
7648,170.00632,030.04-2.49
8604,988.00545,198.63-9.88
9616,928.00565,382.29-8.36
10717,971.00675,888.53-5.86
11770,428.00728,363.20-5.46
12554,809.00545,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_QUARTERCATEGORYREVENUEGROSS_MARGINMargin %
1Audio24,413.456,976.4528.58
1Monitor42,887.0511,396.0526.57
1Notebook155,802.9042,845.9027.50
1Periferiche14,882.554,286.5528.80
1Storage33,423.9010,266.9030.72
2Audio28,227.307,783.3027.57
2Monitor55,435.4013,977.4025.21
2Notebook241,426.3565,145.3526.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
CATEGORYCHANNELREVENUE
AudioB2B29,865.65
AudioE-commerce23,280.25
AudioRetail64,796.70
AudioNULL117,942.60
NULLNULL1,309,487.10

What changes from one dialect to another

DialectROLLUPTyped NULLs (drill-across)
Firebird, InterBasenot supported: ignored with a warning (QRY013)yes: CAST(NULL AS DOUBLE PRECISION)
SQLite, MS Accessnot supported (QRY013)no
MySQL, MariaDBGROUP BY a, b WITH ROLLUP (without ORDER BY, QRY017)no
PostgreSQL, SQL Server, OracleGROUP BY ROLLUP (a, b)no
IBM DB2GROUP 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_REASONROW_COUNTREFUND_AMOUNT
Cambio idea2910,907.15
Danno trasporto2815,054.05
Errore ordine3322,626.60
Non conforme2614,468.30
Prodotto difettoso219,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_METHODORDER_STATUSROW_COUNT
BonificoAnnullato54
BonificoConsegnato46
BonificoIn preparazione49
BonificoIn transito62
CartaAnnullato51
CartaConsegnato47
CartaIn preparazione43
CartaIn transito51

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).

Limits and advice

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)

CodeKindMeaning
STAR001errorTable declared as fact or dimension but missing from the schema.
STAR002errorFact grain not declared.
STAR003warningFact with no dimensions.
STAR004errorRole pointing to an undeclared dimension.
STAR005errorRole FK column missing or not in the fact.
STAR006errorSame dimension used several times without distinct role names.
STAR007error / warningMeasure with no column (error) or with a non-numeric column (warning).
STAR008warningSemi-additive measure with no aggregation over time.
STAR009errorSUM aggregation on a non-additive or derived measure.
STAR010error / warningDerived measure: reference to a measure that does not exist (error); nested derived measure or inconsistent additivity (warning).
STAR011errorPeriodic snapshot without a main time role.
STAR012error / warningSurrogate key missing (error) or different from the primary key (warning).
STAR013warningDimension without a natural key.
STAR014errorType 2 attributes without validity columns (start, end, current version).
STAR015errorType 2 dimension whose surrogate key equals the natural key.
STAR016errorHierarchy level on a column that does not belong to the dimension.
STAR017errorSame table declared as both fact and dimension.
STAR018infoNo physical FK between fact and dimension (acceptable in a DW).
STAR019errorDuplicate measure names in the same fact.
STAR020error / warningGrain columns that do not exist (error) or not given (warning).
STAR021infoDimension not used by any fact.
STAR022infoSnowflake: dimension with an FK to another dimension.
STAR023warningSurrogate key of the date dimension is not an integer.
STAR024errorUnknown and Not applicable members with the same key.
STAR025errorColumn used both as a measure and as a dimension FK.
STAR026warningTime role that matches no role of the fact.
STAR027errorAttribute, key or validity column that does not belong to the dimension.

Query (QRY)

CodeKindMeaning
QRY001errorNo measure requested.
QRY002errorFact not declared in the model, or table missing from the schema.
QRY003errorMeasure does not exist, or a component of a derived measure does not exist.
QRY004errorAttribute of a table that is not declared as a dimension.
QRY005errorAttribute of a dimension that is not linked to the fact (or to one of the facts).
QRY006errorAmbiguous role: dimension with several roles and a missing or wrong role name.
QRY007errorAttribute or measure column missing.
QRY008warningAggregation corrected by the additivity rule (for example SUM → AVG, SUM → LAST).
QRY009errorLAST or FIRST requested without a time role.
QRY010warningSemi-additive measure in a fact without a time role.
QRY011errorCOUNT DISTINCT cannot be computed in the two-level calculation.
QRY012warningROLLUP ignored: the query is not simple.
QRY013warningROLLUP not supported by the dialect.
QRY014warningROLLUP with a semi-additive measure and time in the grouping.
QRY015errorJOIN impossible: the surrogate key of the dimension cannot be determined.
QRY016errorFilter with missing values or values that are not valid for the column type.
QRY017infoWith WITH ROLLUP (MySQL) the ORDER BY is left out.
QRY018infoROLLUP with LEFT JOIN: NULLs of missing members look like subtotals.

H15. Limits and common problems

Current limits

LimitDetail
“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

SymptomCauseWhat 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

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

CodeMeaning
ETLSTAR001A fact FK is not mapped
ETLSTAR002/003The FK is copied directly, or the lookup points to the wrong place
ETLSTAR004/005Lookup without a fallback (Unknown member) or one that does not pick the current SCD2 version
ETLSTAR006/007Re-running the ETL duplicates facts; the idempotency key differs from the grain
ETLSTAR008The fact phase comes before the dimension phase
ETLSTAR009-014Dimension written directly, no natural keys, staging not cleared or not deduplicated, natural key or attribute unmapped
ETLSTAR015-017SQL after import missing, different from the generated one, or not on the destination connection
ETLSTAR018/019Dimension used but loaded by no phase; error generating the SQL

What you have to do

  1. Create in the destination database the STG_<dimension> tables with the natural keys and attributes (the tool does not generate DDL).
  2. Create the ETL phases (one per dimension into staging, one per fact) and map the fields: phases are not created automatically.
  3. Open the window, read the messages, press Apply fixes, save the ETL project.

Limits

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:

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 mode123456789101112
  1. ETL toolbar (same commands as the ETL menu)
  2. Mode tabs: ETL active
  3. Filter stages… searches among stages and fields
  4. Expand / collapse everything or the current stage
  5. Highlighted rule: a transformation
  6. Direct rule: simple copy
  7. Field rule panel
  8. Initial query / Final query of the stage
  9. Data preview: Source and Destination
  10. Total records of the source
  11. Scheduled ETL stages
  12. 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]:

In the empty state the tree offers two shortcuts: New stage and Auto-match.

D3. Creating a project

  1. 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.
  2. Create the stages in one of the following ways.
  3. Define the rules for the fields (D5).
  4. Save the project (D10) and run it (D8).

Three ways to create stages

MethodHow it works
New stageETL → New stage menu: choose the source and destination tables (or a custom query) and the connections.
New stage from the treeStarts from the table selected in the database tree.
Auto-matchChoose 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

D4. Stage settings

From right-click on the stage → Edit settings a tabbed window opens.

TabWhat it sets
Source / DestinationSource 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.
OptionsStage 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.
DedupRemoves duplicates based on one or more fields; with Keep the last and a sort criterion you decide which row survives.
IdempotencyIdempotency key (fields or expression) to avoid rewriting rows already processed. The hint on the tab explains the trade-offs.
TransactionsMode: 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.
FormatsDefault date format, decimal separator and thousands separator, used when the source contains text to convert (typical of CSVs).
Initial query / Final querySQL 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.

Check before runningBefore execution the project is validated: missing key fields, invalid identifiers, absent dedup fields and the like produce errors or warnings, which you find in the log.

D5. Field rules

Field rule window1234567
  1. Source field and Destination field, with the type of each
  2. Different types warning
  3. Rule: the type of transformation
  4. Expression and Assistant… button
  5. Run rule only if: condition
  6. Preview on the real values of the source
  7. 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.

RuleWhat it does
DirectCopies 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.
ConstantAlways writes the same value (for example -1 or 100), regardless of the source.
ExpressionCalculates the value with a formula (see below). In the tree it appears as [Expression = …].
LookupLooks 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.
IgnoreThe field is not written.
AutoincrementNumbers the rows: it starts from an initial value or from the last value already present in the destination.
FormulaCombines 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}.

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:

GroupConditions
ContentEmpty · Not empty · Spaces only · Digits only · Letters only (accents included) · Alphanumeric (at least one letter and one digit)
TextStarts with · Contains · Ends with · Equal · Different (case-insensitive)
LengthLength equal · greater · less
NumbersGreater · less · equal (accepts comma and period)
OtherRegular expression · Valid date in the specified format (for example dd/mm/yyyy)
Write errorsAn invalid regular expression or an unreadable number does not block execution: that condition simply turns out false and the field takes the alternative value.

Advanced condition composer

For "if… then…" transformations with several conditions, the Composer button (or Open composer) builds a CASE expression for you:

  1. Choose Field, Operator and Compare with and press Add condition.
  2. Repeat to add more conditions to the same branch; decide whether they must be all true (AND) or just one (OR).
  3. Write the Result if the branch is true (or insert it from a field) and press Confirm branch.
  4. Add more branches: they appear in Branches already added; you can remove conditions or branches.
  5. 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:

D8. Running

ActionHow
Run the whole projectETL → Run menu or F9. Deactivated stages are skipped.
Try a single stageRight-click the stage → Run stage.
InterruptETL → 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

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:

FieldMeaning
ConnectionsSource and destination. The project must have the connections saved: otherwise the program tells you.
FrequencyHourly · Daily · Weekly · Monthly · Weekend · End of month
Interval, TimeHow often to repeat the execution and at what time
Day of the week / of the monthFor Weekly and Monthly. The day of the month has a note for shorter months
Catch up missed runIf 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:

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

ItemEffect
ETL → New stage / New stage from the treeCreates a stage
ETL → Auto-matchCreates the stages for same-named tables
ETL → Edit / Delete stage, Clear all stagesStage management
ETL → New / Edit / Delete / Generate fieldsField 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 serviceSaves the project for execution by a service (with scheduling)
ETL → Run (F9) / CancelExecution

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):

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.

Queries are really runTo measure time the Profiler runs the statement. This also applies to 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.
What is profiledThe Profiler measures the SQL text that is in the Editor at that moment, running it on the tree's active connection. An empty Editor starts nothing. On LocalSQL connections (CSV files, Excel, REST…) the measurement runs on the same connection and synchronously (the interface waits for the result): this is necessary because the "virtual" tables exist only on that connection. On other databases the measurement starts in the background. Plan capture is on by default and the thresholds start at 150 and 600 ms.

E2. What is measured

Statement typeWhat is measured
SELECT, WITH, SHOW, PRAGMA, EXPLAINThe 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 procedureTime and rows. For procedures the plan is not captured.
Execution in errorThe time up to the error and the database message. No plan.

E3. Thresholds and colors

OutcomeWhenBar appearance
Goodless than 150 msGreen
Warningfrom 150 ms to less than 600 msAmber, with a small shape indicator
Critical600 ms or moreRed, with shape indicator
Errorthe query failedDashed 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

E5. Examining a measurement

E6. The execution plan

The format depends on the engine: each uses its own mechanism.

EngineHow the plan is obtained
PostgreSQLEXPLAIN (ANALYZE, BUFFERS, FORMAT JSON): real times, but the query is run a second time.
MySQLEXPLAIN FORMAT=JSON: does not re-run the query.
SQL ServerSHOWPLAN_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.
SQLiteEXPLAIN QUERY PLAN
OracleEXPLAIN 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.
FirebirdFirebird 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 AccessNo plan: only time and rows are measured.

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.

If profiling does not startA message indicates the cause: no active connection in the tree, query or connection not set, or profiling already in progress.

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:

Builder with the query for analysis12345
  1. Connection tab on which the query runs (here CSV_Conn, a LocalSQL connection on CSV)
  2. Table in the diagram, with the selected fields
  3. Generated SQL in the Editor
  4. Result counter: 45 rows x 3 cols [0 ms]
  5. Results grid
  1. 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).
  2. Prepare the columns the chart needs: a column of labels (the X) and one or more numeric columns (the Y). Add filters, GROUP BY and 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).
  3. Run with Run. Check in the grid that the rows are the expected ones (45 rows x 3 cols).
  4. 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).
Run the query firstIf there is no open result in the Editor (the query has never been run, or it was closed), switching to the Analysis tab shows the message Active query and the program takes you back to the Builder. Run the query with Run / Execute and then try again. If you change query in the Editor, go back to the Builder, run it and re-enter Analysis to reload the data: the chart title restarts from Title and must be renamed.
Fewer rows, more readable chartsA bar for each of thousands of rows is unreadable: filter or aggregate in the Builder, or use TOP / LIMIT from the Advanced SQL panel (ch. 8). Beyond a certain number of points the chart simplifies the data by itself and flags it with the aggregated data note.
Changing queryIf you load a different query, the column menus (X, Y, formats…) are reset: the column indexes would no longer have the same meaning.

F3. The interface

Analysis - radar chart12345678910111213
  1. Export ▾: PNG, SVG, PDF, CSV, Excel
  2. Copy image to the clipboard
  3. Save ▾ / Load ▾: configuration or complete analysis
  4. Refresh: rereads the data
  5. Resolution (1x, 2x, 3x)
  6. Title of the chart
  7. Chart type
  8. X column (labels)
  9. Y columns (values): one or more columns
  10. Display options
  11. Number format per column
  12. Chart (here a radar: each spoke is a country, the distance from the center is the population)
  13. 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.

F4. Chart types

TypeRequired columns
Bars · Stacked bars · Lines · Area · ComboX = labels, one or more Y. Combo: each series can be a bar or a line and use the right-hand Y axis.
Pie · RadarX = labels, Y = values
HistogramA 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 · BubblesX = 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.
CandlestickX = 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 plotX = category, one Y = value. The program calculates minimum, first quartile, median, third quartile and maximum for each category.
HeatmapX = category column (horizontal axis), Heatmap row = category on the vertical axis, Y = value that gives the cell's color.
WaterfallX = category, Y = change (positive or negative). The chart appends the Total bar at the end.
FunnelX = stage, Y = value. The first stage is 100% and the following ones are proportional.
Map (choropleth)X = area code, Y = value (see F6).
TreemapX = label, Y = value (area of the rectangle). Optional: Treemap group, a second column that groups the rectangles into colored boxes.
SparklineX = 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.
SankeySource and Destination columns (the nodes) and Y = flow. A single hop: for multiple steps you need one query for each pair.
In the screenshot's radarThe X is 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

OptionEffectApplies to
Highlight outliersCircles the points beyond 2 standard deviations from the series meanBars, Lines
Trend lineLinear regression overlaid on the seriesTypes with a Cartesian axis
Moving average (N periods)Lighter dashed line for each visible series. 0 = offBars, Stacked bars, Lines, Area, Combo
Threshold (value + label)Dashed horizontal line with a label, for example a monthly targetTypes with a linear Y axis (not pie, radar, maps, heatmap, funnel, treemap, sparkline, Sankey)
Histogram binsNumber of bins (2–100)Histogram
Max pointsBeyond this number the chart simplifies the data and flags aggregated data. 0 = default valueAll

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

Analysis - choropleth map1234567
  1. Map level: Regions or Provinces
  2. Map file (mappe folder) and button to reread the folder
  3. Density (KDE) instead of regions
  4. Colors of the scale: low, medium, high density
  5. Color scale with minimum and maximum
  6. Ranking of values (with "+N more")
  7. Zoom: "zoom active — double-click to reset"

The map colors each area according to the value. To use it:

  1. In the Builder prepare a query with a code column and a value column (in the example codice_iso and popolazione).
  2. Choose the Map (Choropleth) type, the code column as X and the value as Y.
  3. Choose the Map file among those found in the mappe folder next to the program (in the example europe.geojson). If you add a file while the program is open, press the refresh button next to the list.

F7. Number format

In the Column number format section choose a column and the format in which to show its values:

SettingEffect
FormatAutomatic (K/M) (default: 91.8M, 764…), Number, Currency or Percentage
DecimalsNumber of decimal digits
Thousands separatorTurns on the thousands separator
Currency symbolFor 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

ButtonResult
Export ▾ → PNGImage of the chart. The Resolution selector (1x, 2x, 3x) adjusts the definition of the images
Export ▾ → SVGVector image (XML text)
Export ▾ → PDFPDF document with the chart
Export ▾ → CSVThe 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 imagePuts the chart on the clipboard, without a save window: ideal for pasting it into Word or PowerPoint

Saving the analysis

ItemWhat 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 analysisThe 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.
Analysis saved on another computerDPAPI protection ties the file to the user and the Windows computer that saved it: opening a password-protected .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

F11. Common problems

SymptomCause and remedy
The chart is emptyThe query returned no rows, or the chosen Y columns are not numeric. Check the grid in the Builder or the Editor.
Too many bars, unreadableAggregate or filter in the query, or use TOP / LIMIT.
The candles are wrongYou need exactly 4 columns in the order open, high, low, close: reorder them in the query.
The map is empty or few areas are coloredThe 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 refreshThe query's connection is not open: connect it from the tree and try again.
Reopening an analysis with a password does not workThe 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

Builder screen 1 2 3 10 4 5 6 7 8 9
  1. Command bar: run, save, ERD, Explain… (ch. 9)
  2. Tabs: one row for connections and one for queries (ch. 11)
  3. Table node: name, alias and fields (ch. 4)
  4. Field search inside the node
  5. Relationship between two fields: JOIN (ch. 5)
  6. Counter of visible / total fields, with scroll arrows
  7. Advanced SQL panel (ch. 8)
  8. Minimap to get oriented in large diagrams
  9. Zoom − / percentage / +
  10. 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.

If nothing happensCheck that the connection is connected and that the tree is not in browse data mode (Browse): in that case double-clicking opens the table's data in the grid instead of adding it to the Builder.

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:

ButtonFunction
×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

5. Relationships and JOINs

Each field has a dot on the edge of the node. To create a JOIN:

  1. Press the dot of the starting field (right side) and drag: a temporary line follows the mouse.
  2. Release on the field of the other table.
  3. In the JOIN type popup choose the type of join.
TypeNotes
INNER JOINOnly the rows with a match in both tables.
LEFT / RIGHT / FULL JOINKeep the rows with no match on one side or both.
CROSS JOINCartesian product.
CROSS APPLY / OUTER APPLYTypical of SQL Server.
JOIN LATERALTypical 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.

Immediate checkThe result of each choice is visible right away in the panel's SQL Preview tab and in the Editor below the canvas.

Right-click a field. The menu header shows TABLE.FIELD (type).

ItemEffect
AggregatesNo 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 BYInclude 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 expressionCOALESCE, 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 / hintInherit 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 nodeDuplicates 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.

SectionContent and actions
Table AliasesList of tables with the current alias (or no alias). Click the pencil ✎ to edit it.
Fields & AggregatesOne 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 / HAVINGThe 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.
OptionsDISTINCT, 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 PreviewPreview of the generated SQL, with keyword highlighting.
Free text (RAW)Wherever you enter free expressions (HAVING, WHERE RAW, hints) a RAW label appears: that text is copied into the SQL as is, without dialect checks.

9. Command bar

ButtonActionRequirements
RunGenerates the SQL, takes it to the Editor and runs it.Active connection
SchemaImports the schema from the connection.Connection
ClearClears the diagram, after confirmation. It can be undone with Undo.—
Save / LoadSaves and loads the diagram in a .json file. If the file is not valid the current diagram stays unchanged.—
PrintPrints the diagram over several pages.—
Undo / RedoUndo and redo; each action on the model is a single step.—
SQLCopies the generated SQL to the clipboard.—
FitAdjusts the zoom to show the whole diagram.—
ERDToggles between the Builder view and the entity-relationship diagram view.—
DDLShows the DDL script of the diagram.—
AIGenerates the query from a natural-language request.AI service configured
PerfPerformance Advisor: suggestions on the query.—
SnipSnippet library.—
ExportExports the diagram to PNG, SVG, HTML or Markdown.—
TabsAdds a new query tab (asks for the name).—
SubqInserts a subquery node.—
ExplExecution plan in text form (toggle).Connection
ExpGExecution plan as a graph of nodes.Connection
CmpPlanComparison between two execution plans.Connection
Save.fdp / Load.fdpSaves and opens the .mfdproj project (see ch. 13).—
GestureEffect
Mouse wheelZoom 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 nodeMoves the table; on release it snaps to the grid.
Drag on the backgroundArea selection (rubber band).
Ctrl + clickAdds or removes a node from the multiple selection; dragging one moves the whole group.
MinimapDrag in the minimap to move the view.
− / % / + buttonsZoom 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.

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.

Builder or Free?As long as the Editor button shows [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

WhatHow
A diagramSave / Load → .json file.
A query as an image or documentExport → PNG, SVG, HTML, Markdown.
The whole projectSave.fdp / Load.fdp → .mfdproj file. Saves the connections and tabs; the window title shows an asterisk (*) when there are unsaved changes.
A recurring queryCtrl+Shift+S saves it as a snippet.

14. Keyboard shortcuts

KeysAction
F1Shortcut guide
Ctrl+Z / Ctrl+YUndo / redo
Ctrl+ASelect all nodes
Ctrl+CCopy the generated SQL
Ctrl+DDuplicate the selected node
Ctrl+FSearch tables and fields in the diagram
Ctrl+RFind and rename everywhere
Ctrl+Shift+F / Ctrl+G / Ctrl+0Fit all / zoom to selection / reset zoom
+ / −Zoom in / out
Tab / Shift+TabNext / previous node
DelDeletes the selected node or relationship
EscCancels the relationship in progress or closes the overlay
Ctrl+EGraph Explain Plan
Ctrl+HQuery history
Ctrl+PPrint
Ctrl+T / Ctrl+W / Ctrl+TabNew tab / close tab / next tab
Ctrl+Shift+S / Ctrl+Shift+LSave as snippet / open snippet library
Ctrl+Shift+TDark / 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.

SQL Editor12345678910
  1. Execute: runs the query
  2. [FR] Free / [QB] Builder: who controls the text
  3. Undo / Redo
  4. Copy, Find, Format, ⚙, Clear, Load
  5. Script: batch execution
  6. Tx, OK, RB: transaction
  7. Hist: history
  8. Editor tabs (connection and query)
  9. Gutter with line numbers
  10. 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

17. Builder / Free mode

The button next to Execute decides who owns the text:

ButtonMeaning
[QB] BuilderThe text is the one generated by the Builder and is rewritten at every change to the diagram.
[FR] FreeFree SQL: the Builder no longer touches the Editor. Execute runs what you have written.
Recommended flowBuild the base in the Builder, switch to [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

ActionHowDetails
Running the queryExecute, F5 or Ctrl+EnterRequires 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 scriptScript or F6Runs 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 fileLoad or Ctrl+OThe file opens in a new tab. Both UTF-8 and old ANSI files (for example saved from Notepad) are accepted.
Without a connectionIf the connection is not open, execution shows a help message instead of starting. Connect from the tree and try again.

19. Transactions

By default statements are run normally. To group several changes in a single transaction use the three buttons:

ButtonActionEnabled when
● TxBEGIN TRANSACTION (Ctrl+B)Connection open and no transaction in progress
✔ OKCOMMITTransaction in progress
↺ RBROLLBACKTransaction 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.

ItemFunction
Status rowRows returned or affected, or the error on a single line (the full detail is in the Messages panel).
Search 🔍Filters the rows already loaded.
PaginationFor 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.
ExportCSV, JSON, XML, XLSX, or an INSERT INTO SQL script.
✎ EditMakes 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.
MsgShows or hides the Messages panel (log of executions, errors, transactions); the × Clear button empties it.

21. Command bar

ButtonAction
ExecuteRuns the query (F5).
[FR] Free / [QB] BuilderChanges the mode (ch. 17).
↶ ↷Undo / redo typing.
CopyCopies all the text to the clipboard.
FindFind and replace (Ctrl+H): Find next, Replace, Replace all, with Regex mode.
FormatFormats the SQL: uppercase keywords and indentation.
⚙Formatter options.
ClearEmpties the current tab.
LoadOpens one or more .sql files in new tabs (Ctrl+O).
ScriptRuns as a batch (F6).
Tx / OK / RBTransaction (ch. 19).
HistQuery 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.

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

KeysAction
F5 / Ctrl+EnterRun
F6Run as a script
F2 / Ctrl+SpaceIntelliSense
Ctrl+/Comment / uncomment the line
Ctrl+HFind and replace
Ctrl+BBEGIN TRANSACTION
Ctrl+OLoad .sql file
Ctrl+Alt+HHistory
Ctrl+T / Ctrl+W / Ctrl+TabNew tab / close tab / next tab
TabInserts two spaces

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.

ModeVisible menus
BuilderFile, Edit, Query, View, Schema, DDL, Connection, Tab, Snippet, Tools, Settings, Extras, ?
DesignerThe Schema Designer menu appears; Query, View and Schema (those of the Builder) are hidden
ETLThe ETL menu appears. Edit, DDL, Connection, Tab, Snippet, Tools and Extras are hidden; File, Settings and ? remain
AnalysisThe Analysis menu appears (refresh, export, configuration)
Item namesThe names here are descriptive, derived from the function of each command: the exact text depends on the language chosen in Settings → Language.
Same shortcuts, different modesSome combinations are reused in different modes (for example F7, Ctrl+Shift+I and Ctrl+Shift+0 in the Designer). In the Builder F5 is assigned to both Run and Refresh tree.

26. File menu

ItemShortcutWhen it is active
New projectCtrl + NAlways
New editor tab / Close editor tabCtrl + T / Ctrl + WClose: if at least one tab exists
Save editor tabCtrl + Shift + EOnly if the tab has been modified
Load SQL fileCtrl + OOpens one or more files in new tabs
Save projectCtrl + SOnly with unsaved changes
Save project as—Always
Open project (.mfdproj)Ctrl + Shift + OAlways
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 diagramCtrl + PBuilder mode, with at least one table in the diagram
AI Configuration—AI service settings
ExitAlt + F4

27. Edit menu

ItemShortcutWhen it is active
Undo / RedoCtrl + Z / Ctrl + YThey act on the active Builder or Designer
Copy all the SQLCtrl + Shift + CNeeds text in the editor
Find / ReplaceCtrl + HNeeds text in the editor
Global find and renameCtrl + Shift + RRenames a table or field throughout the diagram. Builder mode, with at least one table in the diagram
Format SQL / Formatter optionsCtrl + Shift + FThe options are always accessible
Clear editor—Needs text in the editor
Free modeCtrl + Shift + MDetaches 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

ItemShortcutWhen it is active
RunF5Needs SQL in the editor and a connection
Run as a scriptF6As above, Builder mode only
Run Builder queryF7Runs the query of the active Builder tab. Needs the tree's connection
Begin / Commit / RollbackCtrl + B / Ctrl + M / Ctrl + Shift + BBegin 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 QueryCtrl + Shift + AGenerates the query from natural language
Generate AI promptCtrl + Shift + JJSON 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

ItemShortcutWhen it is active
IntelliSenseCtrl + SpaceTurns autocompletion on/off
Line numbers—Show/hide in the editor
Messages panelCtrl + Shift + G
Editor historyCtrl + 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 selectionCtrl + 0 / Ctrl + Shift + 0Builder mode, with at least one table in the diagram
Reset view—Builder only
Explain → Show plan, Plan graph, Compare plans, HideCtrl + 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.

ItemShortcutWhen it is active
Import schemaCtrl + INeeds connection. Builder only
Refresh tree / Expand rootF5 (Refresh)Needs the tree
Send schema to IntelliSenseCtrl + Shift + INeeds tree, editor and connection
Add table / Remove tableCtrl + Shift + T / DelRemove: needs a table
Duplicate table, Self-joinCtrl + DBuilder 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 dialogCtrl + FBuilder only
Select all / Deselect / Clear diagramCtrl + A / EscBuilder 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 panelCtrl + Shift + PQuery :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.

ItemShortcutWhen 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

ItemShortcutWhen 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

ItemShortcutWhen it is active
New editor tab / Close editor tabCtrl + T / Ctrl + W
New query tabCtrl + Alt + TAdds a tab to the Builder
Remove query tabCtrl + Alt + WOnly 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.

ItemShortcutWhen it is active
Show snippetsCtrl + Alt + SBrowses 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

ItemShortcutWhen it is active
Validate diagramCtrl + Shift + VChecks 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 guideF1

36. Settings, Extras and ?

ItemShortcutWhen 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.

MenuContent
Schema DesignerAdd 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.
ETLNew 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.
AnalysisRefresh (F5); export to PNG, SVG, CSV; save and load the configuration.