Levels and alignment
Strata SQL models data at four linked levels, from the business down to the database and back up to the numbers people use.
| Level | Shows | Switch |
|---|---|---|
| Conceptual | Business concepts (Customer, Order, Product) with a definition and business verbs ("Customer places Order") | Cmd/Ctrl + Alt + C |
| Logical | Entities with attributes, generic types (Integer, Decimal, Text), keys and cardinality | Cmd/Ctrl + Alt + L |
| Physical | Tables and columns with the real data types of your database | Cmd/Ctrl + Alt + P |
| Semantic | Metrics next to the tables they are calculated from | Cmd/Ctrl + Alt + S |
On a Mac the modifier is Cmd + Option. Each level remembers its pan, zoom, view scope, open panel and selection.
How the levels link#
A concept is described by one or more entities, and each entity is realised by one or more tables. Each table names the entity it realises, each entity names its concept. Descriptions, tags and properties flow down, so you describe and classify a concept once. Relationships get more abstract going up: tables have foreign keys, entities have cardinality, concepts have business verbs.
A model with no concepts or entities still draws its tables on those levels, with display names and generic types.
Start: generate, then merge#
Open Alignment (the stacked-layers icon; a yellow number counts open warnings) and click Generate logical and conceptual levels:
- One entity per table and one concept per entity, named from the table names. Prefixes and suffixes such as
dim_,fact_,stg_,hub_,sat_,_vand_histare dropped and plurals made singular, sodim_customerandcustomersboth become Customer. - Logical relationships from foreign keys, and conceptual relationships between the concepts they connect.
- Views and pure bridge tables are left out: a bridge realises a many-to-many relationship, not an entity.
- What you already have is kept; run it again after adding tables (Fill gaps from physical).
Then merge what belongs together: double-click an entity header and choose Merge into another entity. Its tables and relationships move over. Concepts merge the same way.
Map tables quickly#
Alignment → Mapping lists every table and view by schema with the entity it realises. A yellow bar marks a table that still needs one.
- Per table: pick the entity, or + New entity "..." named after the table.
- In bulk: tick tables (or a whole schema), choose the entity at the bottom and click Assign.
- Suggestions from names:
customer_addressanddim_customerboth suggest Customer. Click the suggestion chip on a row, or Accept all. - Entities → concept works the same way.
- One table, several entities (One Big Table, a wide dimension): tick them in the table dialog under Realises logical entity. The first one ticked is the main one.
Every mapping is a normal version in the Modelling stream, so undo, history and preview work as usual.
Alignment warnings#
| Warning | What to do |
|---|---|
| Table not mapped to an entity | Map it, or accept. Views and pure bridge tables are not reported. |
| Entity without a concept | Link it to a concept. |
| Concept without an entity | Add or link an entity, merge or delete the concept, or accept it as not built yet. |
| Entity not realised by a table | Map a table to it, or accept it as planned. |
| Physical relationship missing in the logical model | One click creates it with the same cardinality and label. |
| Logical relationship missing in the conceptual model | One click adds it. |
| Cardinality differs between levels | Take the lower level's cardinality. |
| Inherited tag dropped | Restore it, or accept when intended. |
| References that point at nothing | A relationship to a table or column that is not in this model. Strata SQL keeps it exactly as written and warns; nothing is dropped. |
A yellow badge on a card counts its warnings; hover for the list. Fix all and Accept all handle a whole group. Accept means "left like this on purpose": it is stored in the model, versioned like any change, and listed under Accepted, where Undo reports it again. Checks start once a level exists; a model with only tables gets no alignment warnings.
Views, scope, impact and load order on every level#
View scope and impact analysis work on every level. On the conceptual and logical levels they are worked out from the tables behind each concept or entity.
Moving around#
- Drag the background to pan; mouse wheel or trackpad pans, Cmd/Ctrl + wheel or pinch zooms.
- The canvas controls in the bottom-right corner zoom, fit, auto-layout and toggle the mini-map.
- Cmd/Ctrl + 1 tidies the layout. Auto layout is not a version unless you turn on Record auto layout as a version in Settings.