24.1 Facts¶
A fact is a measure in your data – an amount, a quantity, a cost. In Modeller a fact is simply a named column, e.g. Sales Amount = the Amount column of the sales lines. Datashop users then build metrics on the facts, deciding how they're summed and formatted – see 10.3 Creating and Editing Metrics.
| Defined in Modeller | Defined in Datashop |
|---|---|
| Facts (which columns hold measures) | Metrics (aggregation, formula, format, description) |
| Dimensions and units | Metric folders, filters, workbooks |
Modeller doesn't show or edit metrics.
Creating Facts¶
| Way | How |
|---|---|
| From a column | Right-click the column in the Warehouse diagram → Add to model → Mark as fact. The fact is named Table.Column. |
| Several columns at once | Right-click Facts (or a fact) in the Object Tree → Add Fields, select the columns and click Add Fields. Not offered on fact folders. |
| Empty fact | HOME → New → New Fact, then add columns. If a fact folder – or a fact in one – is selected, the new fact is created in that folder. |
A column can belong to one fact only. Rename facts with a clear name – it's what Datashop users see when they create metrics.
Fact Folders¶
New in build 20304.
Keep many facts tidy in folders under Facts in the Object Tree. Folders can be nested.
| To | Do this |
|---|---|
| Create a folder | Right-click Facts, a fact folder or a fact → New Folder. The folder is created in the selected folder – or in the selected fact's folder – and you type its name right away. |
| Create a fact in a folder | Select the folder (or a fact in it) and use New Fact, or drag a table field onto the folder. |
| Create a fact from a field | Mark as fact in the Warehouse diagram, dragging the field onto Facts, or Add Fields put the fact into the folder named after the field's table; Modeller creates the folder if it doesn't exist (new in build 20337). To put it somewhere else, drop the field on that folder instead. |
| Move facts or folders | Drag them onto another folder. |
| Rename a folder | Rename (F2), as for other objects. |
| Delete a folder | Delete. Its facts and subfolders move up to the parent folder – deleting a folder never deletes facts. |
Existing Projects¶
Build 20312 and later.
The first time Modeller opens a project made before fact folders existed, it asks once whether to move the facts in the root of Facts into folders – one folder per table, named after the current name of the table (or dynamic view) of the fact's first field. The question says how many facts would move and gives an example, e.g. that fact X goes into folder Y.
- Yes moves the facts. Fact names don't change, and an existing fact folder with the same name is used.
- No leaves the facts where they are.
Either way, the project then has unsaved changes. Your answer takes effect when you save the project: after that, Modeller doesn't ask again, so facts you later move back to the root stay there. If you close without saving, nothing is kept – the facts are back where they were, and Modeller asks again the next time. Facts that aren't mapped to a field stay in the root. You can always create fact folders and drag facts into them yourself.
One Fact in Several Tables¶
A fact can point to columns in several tables – e.g. the amount in the detail sales lines and in a monthly summary table. Datashop then uses the smallest table that has the dimensions a workbook needs, which can make reports much faster. Select the fact, then right-click a column in another table → Add to model → Map to fact …, or drag the column onto the fact's field list under Properties.
Collect statistics (PERFORMANCE tab) counts the tables' rows, which helps Datashop pick the right table – see 27.1 Performance Tuning.
The Fact's Fields¶
Select a fact to see its Name, Title, Description and its fields with Field, Table and Datatype.
- Remove a field: select it and press Ctrl+Del, or right-click the column in the diagram → Delete mapping to fact …. A fact that loses its last field is deleted, unless a metric uses it.
- The fact table must have at least one column mapped to a dimensional unit; otherwise validation warns that the fact has no dimensionality.
Deleting Facts¶
Select the fact → HOME → Delete. The DELETE window lists the Datashop metrics that use it – they stop working when the fact is deleted. Dependencies shows the same list without deleting.
Common Issues – Facts¶
Problem: The fact "…" is not mapped to any attributes. - Solution: Add a column to the fact, or delete it.
Problem: The fact '…' has no dimensionality. - Solution: Map the fact table's key columns to dimensional units – see 23.2 Linking Units to Tables.