Skip to main content

Build a model

A model tells the explorer what a user may analyse and how the tables relate. It is built in Settings → Explorer by a superuser, with no SQL: every entity reads an existing query of a connector, and the columns are discovered from that query's result.

At a glance​

Settings · ExplorerAppcrm+ Add an appModelpipeline ▾+ Add modelDelete modelSaveLabelCRM pipelineHubdeals ▾Description—Preset (Screen Assistant)None ▾▤ Entities+ Add entitydeals— DealsHub1..1crm.deals_get · key: ID · Σ AMOUNT · CLOSE_DATEEdit · Deletecustomers— Customers1..1crm.customers_get · key: IDEdit · Deleteactivities— Activities1..ncrm.activities_get · key: IDEdit · Delete🔗 Relations+ Add relationcustomers ▾→deals ▾ID=CUSTOMER_ID+ 🗑activities ▾→deals ▾DEAL_ID=ID+ 🗑One app at a timeseveral models per appLabel and hubthe hub starts every analysisEntitiesa query each, 1..1 or 1..nRelationscolumn pairs to the hubSavewrites and reloadsFrom a presetfills entities and joins

The app and the model​

  1. App — the bar at the top lists the apps that already have a model; + Add an app offers the SQL connectors that have none.
  2. Model — pick a model of the app, or Add model and give its id (letters, digits, _ and -). An app can hold several models: tables that do not relate to one another belong in different models. Delete model removes it with its entities and relations.
  3. The header of the model:
FieldUse
LabelThe name users see in the explorer's model picker.
HubThe entity every analysis starts from: a fact table, a document header, the lines of a document.
DescriptionShown under the explorer's title.
Preset (Screen Assistant)Fills the model from a preset — see below.

Save writes the model and reloads it: the explorer sees it at once. A model without any entity is not saved.

Entities​

Add entity (or a click on an entity) opens its form:

Entity · dealsCancelSaveIddealsLabelDealsRows per hub row1..1 · one1..n · manyConnectorcrm (the app) ▾Querydeals_get ▾Screen (column hints)Deals ▾Default dateCLOSE_DATE ▾COLUMNSCOLUMNLABELTYPEKEYMEASUREHIDDENIDIdnumber☑— dimension —☐STAGEStagestring☐— dimension —☐AMOUNTAmountnumber☐sum ▾☐CLOSE_DATEClose datedate☐— dimension —☐CUSTOMER_IDCustomernumber☐— dimension —☑Cardinalityrows per hub rowSourcea SELECT query of a connectorDiscovered columnsthe query runs for one rowScreen hintslabels, formats, listsMeasuredefault aggregateHiddenleft out of the pane
FieldUse
IdThe entity's identifier, fixed once created (deals, customers).
LabelThe name of the group in the fields pane.
Rows per hub row1..1 · one — at most one row per hub row (the customer of a deal); 1..n · many — several rows (the activities of a deal). The hub itself is always 1..1.
DescriptionFree text.
ConnectorThe connector of the query; the app's own by default.
QueryA SELECT query of that connector, without parameters. Its columns are discovered by running it for one row.
Screen (column hints)A screen whose column settings give the labels, formats and lists of values. The screens that read the same query come first.
Default dateThe date field the period applies to first.

The Columns table lists the discovered columns, with the label and the type taken from the screen. For each one:

  • Key — the columns that identify a row. A new entity takes the screen's key columns. The key opens a row in its screen's dialog from the detail view.
  • Measure — a numeric column that users sum, average, count…: pick its default aggregate (sum, avg, min, max, count, count_distinct). Left on — dimension —, the column groups and filters.
  • Hidden — left out of the fields pane. A column hidden by the screen stays hidden.

A column that the query no longer returns is flagged with ? so it can be unticked.

What users get from a column​

Each column becomes a field of the pane, labelled and formatted through the dictionary and the screen, and typed as text, number, date, date-time or yes/no. A column with a list of values (an enum or a lookup in the screen) shows its labels and offers its values in the filters. A column declared as a measure but not numeric stays a dimension, and the model reports it to superusers.

Relations​

A relation joins two entities on one or more column pairs: customers → deals on ID = CUSTOMER_ID. Add relation, pick both entities, then the columns (the lists show each entity's columns; a name can also be typed); + adds a pair for a composite key.

Every entity must reach the hub, directly or through other entities. The explorer then picks the joins an analysis needs, and never multiplies rows twice: an analysis groups by at most one 1..n entity, and the others are reached without duplicating the hub's rows.

A model from a preset​

A Screen Assistant preset already declares a set of tables and their joins. Picking it in Preset (Screen Assistant) fills the model:

  • every table of the preset becomes an entity (all its columns, or the preset's own column list), the base table the hub;
  • the joins become relations, matched on the real columns — the column of that name, the one carrying that dictionary item, or, on a connector with a table prefix (JD Edwards EnterpriseOne: ABAN8, WWAN8), the one whose name without the prefix is the item.

Entities already declared are kept and completed; a key, measures, a default date or a cardinality set by hand stay as they are. Then adjust what is needed and Save. Picking the preset again fills the model once more.

Applying the preset also creates the dictionary entries its tables lack — on a JD Edwards source, from its data dictionary: Preset applied: 6 entities · 42 dictionary entries created.

Models shipped by a preset​

A preset can also give the explorer a model by itself: the full presets of the JD Edwards catalogs (Address Book, A/P ledger, sales order, work order…) appear in the model picker without anything in Settings → Explorer. The model is built from the preset when it is first opened; the page header says shipped by the preset “…”. In such a preset, a table's label becomes the entity label, and a lookup table joined to a transaction hub has the cardinality one.

To change such a model, add a model in Settings → Explorer from the same preset: the declared model replaces the shipped one.

Things to know​

SituationWhat happensWhat to do
An entity is not linked to the hub.Save is refused and names the entities left out.Add the missing relation.
The entity's query expects parameters.The explorer runs it without any.Point the entity at a query without parameters.
No column is discovered.The query failed or returned nothing.Check the query in Settings → Connectors.
A field is missing in the pane.It is hidden, or the query does not return it.Untick Hidden, or add the column to the query.
Fields show their raw column name.The columns have no dictionary entry.On the explorer page, a superuser sees N column(s) have no dictionary entry and show their column name and Create the entries from the data dictionary creates them in one click.

What's next​