22.1 Tables in the Warehouse

The Warehouse tab is a diagram of the tables and views used in the model. Each box is a table with its fields; lines show how tables are related. Only tables in the Warehouse can be used for dimensions and facts.

Adding Tables

• Drag a table from the Source tables pane onto the Warehouse diagram – see The Source Tables Pane below.
• Or right-click a table in the pane → Add table (also DATA SOURCE → Add table).
• Or WAREHOUSE → Add table to warehouse (build 20353 and later), or right-click the diagram background → Add table to warehouse, and pick from all tables.

[!NOTE] Before build 20282, each data source in the Object Tree listed its tables, and you dragged them from there.

The same table can be added more than once, e.g. to use it in two roles.

On the WAREHOUSE tab (build 20353 and later; before, HOME → Warehouse), Go to table... (Ctrl+G) jumps to a table in the diagram; Attribute sorting orders the fields Alphabetic, by Physical / DB Order or with Mapped items first.

The Source Tables Pane

New in build 20272; this look since build 20304.

On the Warehouse tab, the Source tables pane on the right lists the tables and views in a data source, ready to drag onto the diagram.

• Header – the title, the number of tables on the right (e.g. 1105, or 12 of 1105 when a filter is set), and × to close the pane.
• Data source – the database to list. The pane opens on the project's first regular database data source, not on Audit log or the metadata. A small square shows the data source's colour, the same colour as the titles of its tables in the diagram. If two data sources have a database with the same name, the server is shown in brackets. The audit log is shown as Audit log () (build 20376 and later; before, only the database name). Hover over it to see the data source name, server and database.
• Table name contains... – type part of a name to filter the list. Put the name in double quotes, e.g. "Item", to list only the table with exactly that name (build 20364 and later).
• Three drop-downs, side by side (stacked when the pane is narrow):
• All schemas or one schema;
• All types, Table, View or Partitioned;
• All tables, In use or Not in use. In use means a warehouse table in the model uses it, or a dynamic view's SQL reads it (build 20355 and later) – these are shown in bold. Hover over the table to see what uses it: the hint lists them under In use by:, one per line, e.g. • Main Customer (table) or • Sales Entry (in its SQL) (build 20359 and later; in builds 20355 to 20358, "Read by the dynamic view" and its name). This also works for partitioned tables written with the partition placeholder in the SQL, quoted or not, e.g. FROM {Regnskab}$Customer (unquoted: build 20359 and later).
• Group by – for projects where a table exists once per partition, e.g. one table per company in Business Central or NAV (CRONUS$Customer, Other Co$Customer). The caption uses the project's partition unit, e.g. Group by Regnskab.
• Ticked (default): such tables are shown as one partitioned table, without the partition prefix. Hover over it to see the number of partitions. Browse shows sample rows from all partitions, and adding it creates one partitioned table in the model.
• Not ticked: every physical table is listed separately.
• In a project without a partition unit, it reads Group partitioned tables and groups tables named $; these are added as plain tables.
  The check box is always there; it does nothing when no tables match.

  To show the pane again after closing it:

  • WAREHOUSE tab → Source tables (on the DATA SOURCE tab before build 20353; it switches to the Warehouse tab if needed) – the button shows and hides the pane;
  • the same button in the small toolbar at the top left of the Warehouse diagram;
  • right-click a data source in the Object Tree → Show source tables – opens the pane with that data source selected. Selecting a data source in the Object Tree doesn't change the pane – use the pane's Data source box or Show source tables (build 20365 and later; before, the pane followed the data source selected in the Object Tree).

  Modeller remembers whether the pane was open, and its width, until next time.

  Hover over a table to see what uses it (In use by: from build 20359; In use as … before). Right-click it → Show in warehouse and pick what to go to in the diagram: the model tables by name, and the dynamic views that read the table, marked (in its SQL) (build 20359 and later; before, it went straight to the table when only one model table used it, and listed only model tables).

  Searching the Data

  New in build 20362; this look since build 20364.

  To find which tables contain a value – a customer number, a code, a name – tick Search data below the filters. It's off each time Modeller starts. Or click DATA SOURCE → Search data (Ctrl+Alt+F): it opens the pane with Search data ticked and the cursor in the value field (build 20370 and later). Clicking the button again hides the search (build 20371 and later); while a search runs, use Cancel.

  1. Type the value in the field (Value to find in the data of the listed tables).
  2. Exact is ticked: only values equal to it are found. Untick it to also find text that contains the value.
  3. Click Search, or press Enter.

  It searches the database of the data source chosen in the pane's Data source box – any database data source, not only the staging database. While Search data is ticked, each table has a check box: only the ticked tables are searched, or all the listed tables when none is ticked. Narrow the list first with the name, schema, type and In use filters. A grouped partitioned table is searched in all its partitions. Columns that can't hold the value are skipped – for example a date column when you search for text.

  The tables are searched in list order, two at a time, in the background; each table in one query for all its columns (build 20366 and later; before, one table and one column at a time). On a large database it can still take a long time. While it runs, the filters, the data source, Group by, the check boxes and the search fields are locked, and the button reads Cancel – the search then stops after the tables being searched (Cancelling...). Each table shows its state on the right: a faint dot while waiting, a small progress bar while it's searched – it moves on per partition – then the number of rows found in bold, or a faint 0. Tables with matches turn light yellow as soon as something is found. The line below has a progress bar and shows the progress in short, in bold, e.g. 114/1105 tables - hits in 2; hover over it for the full text, e.g. Searching: 114 of 1105 tables done - found in 2 tables (61 rows) - 0:14. When it's done, it shows e.g. Hits in 2, 61 rows - 0:41 or No hits - 0:41, with (cancelled) if you stopped it; the hint then says e.g. Found in 2 tables (61 rows) - 0:41, and N columns could not be searched if some columns gave an error (build 20367 and later; in build 20366 the full text was on the line itself, and in build 20364 the progress read "Searching table 12 of 62 - …"). When the search is done, the tables with the most rows come first.

  • Hover over a table to see where the value was found: Found "" in: followed by each column and its number of rows. A column that gave an error is listed with could not be searched and the reason.
  • Double-click a table with results to open the rows found in a SQL tab named
  - "" – up to 1000 rows, from all partitions. The value is already in the tab's Data filter, so the matching cells are highlighted; its Exact follows the search's Exact, and it ignores upper and lower case (build 20366 and later). The right-click menu has the same as its first item, Browse rows with "" – or Browse rows containing "" when Exact isn't ticked – and Browse all rows to browse the whole table (build 20365 and later; before, Browse data always browsed the whole table). A table without results is browsed as usual by double-click or Browse data.

  The results are cleared when you untick Search data, choose another data source, change Group by, or refresh the list.

  [!NOTE] In build 20362 the check box was Advanced: search the data, and Partial match (off by default) found text that contains the value. All listed tables were searched – there were no check boxes – and the progress was counted in columns, e.g. Searching: 120 of 3400 columns, 0:12.

  Working in the Warehouse Diagram

  New in build 20335.

  Zooming and Scrolling

  To	Do this
  Zoom in or out	Ctrl+mouse wheel, Ctrl++ / Ctrl+-, or the zoom bar at the bottom right (zoom out, slider, zoom in)
  Back to 100 %	Ctrl+0, or click the percentage in the zoom bar
  Show all tables	Fit all in the zoom bar, or Shift+1
  Show the selected tables	Fit selection in the zoom bar (up to 200 %), or Shift+2
  Scroll	Drag the empty background – the selection is kept (build 20360 and later; before, it was cleared) – or use the mouse wheel; Shift+wheel scrolls sideways. PageUp / PageDown scroll a page (with Shift: sideways); Home goes to the top left.

  A table with a long list of fields scrolls inside itself – use the mouse wheel over it, or its thin scroll bar.

  The Overview Map

  New in build 20344.

  A small map above the zoom bar shows the whole diagram: sections in their colours, and tables as they look in the diagram – a coloured title bar and a white body, without text or joins (build 20345 and later; before, plain blocks in their title colours). The part you see in the window is lightly tinted blue, with a thin blue frame.

  • Click the map to move the view there, or drag on it and the view follows.
  • Turn the mouse wheel over the map to zoom.
  • To make the map bigger or smaller, drag its top left corner – the one with three small diagonal lines (Drag to resize the overview map) (new in build 20345). The map keeps the diagram's proportions, so it may follow the mouse in only one direction. It is never wider than the window or higher than half of it.
  • The button at the left end of the zoom bar shows or hides the map (Show or hide the overview map); it looks pressed while the map is shown.
  • The map is hidden when the window is less than 300 pixels high, and it is never part of a picture of the diagram.

  Modeller remembers – for you, on this computer – the zoom and scroll position of each project's diagram, whether the map is shown (new in build 20344), and its size (build 20345 and later). Other users have their own.

  Searching the Diagram

  New in build 20342.

  Type in the search box at the top right of the diagram (Search tables and fields). While you type, tables whose name contains the text get an orange frame around their title bar, and the matching letters get an orange background (build 20344 and later); matching fields get an orange background. Upper and lower case don't matter – also for æ, ø, å, ä, ö and ü. Left of the box, Modeller shows how many matches there are, e.g. 12 matches, or 3 of 12 after Enter or Shift+Enter, or No matches (new in build 20344).

  • Enter goes to the next match: the field or table is selected and centred. Shift+Enter goes back. At the end it starts again from the beginning.
  • Matches go through the tables alphabetically – in each table first its name, then its fields in the order shown.
  • The box turns light red when nothing matches.
  • Esc clears the search and returns to the diagram.

  The box has no shortcut: Ctrl+F still opens the project-wide Search – see 20.3 The Modeller Window.

  Selecting, Moving and Resizing

  • Click a table to select it. Ctrl+click adds or removes a table. Ctrl+drag on the empty background selects the tables inside the rectangle. Ctrl+A selects all tables (and sections), Esc or a click on the empty background clears the selection.
  • Drag a table's title bar – or the thin band just outside the table – to move it. The arrow keys move the selected tables.
  • Selected tables have handles for resizing.
  • Tables snap to a 5-pixel grid when you move or resize them; hold Alt to place them freely.
  • Double-click a title bar to collapse the table to its title, or expand it again.

  Undo and Redo

  New in build 20350.

  Ctrl+Z undoes the last change to the layout, Ctrl+Y or Ctrl+Shift+Z redoes it – while the diagram has the focus. The same commands are on the background's right-click menu – below the section items (build 20355 and later; at the top before) – and as Undo and Redo buttons on the small tool strip at the top left of the diagram (new in build 20355), whose tooltips name the step, e.g. Undo move (Ctrl+Z). They're named after what they undo or redo – move, resize, align, distribute, same size or collapse/expand, e.g. Undo move or Redo same size. They're greyed out as Undo / Redo when there's nothing to undo or redo.

  • What can be undone: moving tables and sections (dragging, or the arrow keys – several moves of the same tables in a row count as one step), resizing tables and sections, the Align commands, and collapsing or expanding a table.
  • What can't: adding or deleting tables and sections, renaming, and a section's colour or text.
  • Up to 100 steps. The history starts afresh when you open a project. If a table was deleted after you moved it, undoing the move leaves it out.

  Aligning Tables

  New in build 20350.

  Select two or more tables, right-click one of them → Align:

  Command	What it does
  Left, Center, Right	Lines the tables up on the left edge of the leftmost table, the middle of the selection, or the right edge of the rightmost table.
  Top, Middle, Bottom	The same, vertically.
  Distribute horizontally / Distribute vertically	With three or more tables: the outermost tables stay, and the others are placed with equal gaps.
  Same width / Same height	Makes the tables as wide or high as the widest or highest of them. Collapsed tables keep their height.

  Only tables are aligned – selected sections stay where they are. Each command is one step for Undo.

  Renaming

  F2, or Rename in the table's right-click menu, opens a box on the title bar. Leave it empty to give the table its physical name back.

  Right-Click Menus

  There are separate menus for a table, for a field, and for the empty background. The table menu has Align while two or more tables are selected (build 20350 and later). The background menu has New section – plus Section color... and Delete section while sections are selected, and Audit log settings..., Install audit log data model... and Uninstall audit log data model... while the section Audit log alone is selected (build 20377 and later) – then Undo … and Redo … (build 20350 and later; at the top before build 20355), then Create indexes, Collect statistics, Add table to warehouse, Create Dynamic View…, Paste – which pastes a copied table where you opened the menu – and Copy diagram as image and Save diagram as image... (named after what is selected, e.g. Copy table as image).

  Drag and Drop

  • Drag fields out of the diagram, e.g. onto a dimensional unit or onto Facts.
  • Drop fields, units or facts on a field in the diagram.
  • Drop a physical partition unit on a title bar to convert the table to a partitioned table. Modeller asks first; tick Do not ask me again to skip the question next time.
  • Drop a database table on the empty background to add it at that spot.

  While you drag, the target field or title bar is highlighted, and the status bar says what dropping would do.

  Where New Tables Appear

  Tables you add from the database, paste, or create as a dynamic view are placed on a free spot near the middle of what you see. If another table already has the name, _1, _2, … is added.

  New tables get a size that fits them: the default width and height, but only as high as their fields need, and wider if the name doesn't fit in the title bar (new in build 20339).

  Going to a table centres it in the diagram: Show location... in Go to table... (Ctrl+G), Search, View dependent objects and Project Cleanup, Show in warehouse in the Source tables pane, or double-clicking a table or field in Messages.

  Sections

  New in build 20339.

  A section is a coloured box behind the tables that groups them visually, e.g. Sales or Audit log. It has a title bar and can have a short text under the title. Sections are saved with the project. They appear only in the diagram – not in the Object Tree or in search results.

  To	Do this
  Create a section	Right-click the empty background → New section. If tables are selected, the section is drawn around them, with room for its title bar; otherwise it gets a default size where you right-clicked. The new section is selected.
  Select a section	Click anywhere on it – title bar, edge or inside. Ctrl+click adds or removes a section; tables and sections can be selected together. Ctrl+drag selects the tables and sections lying completely inside the rectangle; Ctrl+A selects all.
  Move sections	Drag a selected section's title bar or edge. Everything selected moves, and each section takes the tables and sections lying completely inside it along – a table that sticks out over its border, even a little, stays where it is. Hold Shift when you start dragging to move the section alone, without its contents. Moves snap to the grid; hold Alt to place it freely.
  Resize a section	Select it and drag its handles
  Change the title	Double-click the title bar and type – Enter keeps it, Esc cancels. Or change Name in Properties.
  Add or change the text	Double-click inside the section. The text wraps to the section's width. Enter starts a new line; Ctrl+Enter or clicking elsewhere keeps it; Esc cancels.
  Change the colour	Right-click the section → Section color... – applies to all selected sections. The palette colours are offered as custom colours.
  Delete sections	Right-click → Delete section (Delete sections when several are selected), or press Del, then confirm. Only the sections are deleted – the tables in them stay where they are. This can't be undone.

  Each new section gets the palette colour – blue, green, yellow, orange, red, purple, teal or gray – that the fewest sections have. Build 20345 and later: the colours are stronger, and red replaces pink; existing sections keep their colour.

  Inside a section, dragging scrolls the diagram, Ctrl+drag selects, and you can drop database tables there, just as on the empty background.

  Where sections overlap, their colours blend. A section inside another is drawn on top of it. Fit all includes the sections; Fit selection fits the selected tables and sections.

  Pictures of the Diagram

  New in build 20342.

  Right-click the empty background:

  • Copy diagram as image copies a picture to the clipboard – paste it into e.g. Word, PowerPoint or an e-mail.
  • Save diagram as image... saves it as a PNG file.

  With tables or sections selected, the items name what they copy – e.g. Copy table as image, Copy 3 tables as image, Copy section as image, or Copy selection as image for tables and sections together; the same for Save … as image... (build 20344 and later; before, always … selection as image). The picture holds only the selected tables and sections, the tables and sections lying completely inside a selected section, and the joins between those tables – nothing next to them (build 20345 and later; before, everything in the area of the selection, cut off at its edge). A table that sticks out over a section's border doesn't belong to the section – the same rule as when you move the section.

  The picture is drawn at 100 %, whatever the zoom, on a white background with no margin – it ends exactly at the outermost table or section (build 20346 and later) – and without selection marks, search highlights or the zoom bar. Joins are shown as on screen, e.g. only the joins of the selected tables if that's what the diagram shows. A very large diagram is drawn smaller, so the picture stays within what Windows can handle (about 40 million pixels, at most 16,000 pixels per side).

  What the Colours Mean

  You see	Meaning
  Customer * – an asterisk after the name	The table, or something it owns, has unsaved changes
  Red title bar	The table doesn't exist in the database (you cancelled the rename or delete question during the check), or it's a dynamic view with invalid SQL
  Red field list	A dynamic view with invalid SQL
  A field in red text	The field doesn't exist in the database

  The colour of the other title bars comes from the data source's Colors option – see 21.2 Connecting to a Database.

  [!NOTE] Before build 20335, the Warehouse tab had an older diagram: zooming, selecting and drag and drop worked differently, and fields missing in the database had a "deleted" icon instead of red text.

  Tables from the Metadata and Audit Log Nodes

  Two nodes in the Object Tree give access to Acinta's own tables:

  Node	Lists
  Metadata	The tables of the project's repository database. Only for database repositories – for a project in a file or folder, it says This feature is only available for database-based repositories.
  Audit log	Right below Metadata, with a small square showing an A – like the M on the Metadata data source. It has three states: Audit log (off) while the project's audit log is off; Audit log (no database) while the location is In its own database but no database has been set up yet, or the location is the repository database but the project is in a file or folder; otherwise it is the Audit log data source for the AUDIT_* tables in the audit log database – the repository database or its own database, whichever the audit log uses. Right-click it → Show source tables to list the tables in the Source tables pane, and drag them onto the diagram from there (before build 20282, the node listed the tables itself). The A is grey while off or without a database and gets the data source colour when the log works. Double-click the node, or right-click → Audit log settings... (available in all three states), to change the settings. If that database can't be reached, the node says The audit log database cannot be reached: followed by the error. See 32.3 The Audit Log.

  Use their tables like any other – the Metadata node still lists its tables in the Object Tree; the audit log tables are in the Source tables pane: Add table, drop a table on the diagram, or drag a field onto a dimension unit, a fact or the fact list. The first time, Modeller asks This action will add "" to the project's data sources. Continue? OK adds the data source to the project – Metadata... is then listed under Data Sources, while Audit log stays as its own node next to Metadata; Cancel does nothing.

  The Audit log data source is the same in every project, so audit log reports can be copied between projects.

  The audit log settings are also on the PROJECT tab → Audit log settings.... Switching the log off turns the node back into Audit log (off). See Opening the Settings in 32.3 The Audit Log.

  The Audit Log Data Model

  Instead of building audit log reports from scratch, let Modeller install a ready-made data model. The Audit log node's right-click menu starts with the audit log commands:

  Command	Does
  Audit log settings...	Opens the audit log settings – also by double-clicking the node
  Install data model...	Adds the audit log data model – while the audit log is on
  Uninstall data model...	Removes it again – shown when the project has the model

  Installing the Model

  Right-click the Audit log node → Install data model... – or, once the model is installed, right-click the orange section Audit log in the Warehouse diagram → Install audit log data model... (build 20377 and later). The confirmation lists what is added as bullets (build 20336 and later); click OK. If the audit log isn't linked to a date dimension yet, Modeller then asks which dates to use – see The Audit Log's Dates below. A progress window is shown while Modeller adds:

  • the Audit log data source, if it isn't added yet;
  • the tables AUDIT_SESSION, AUDIT_REPORT, AUDIT_OBJECT and AUDIT_VISUAL_ISSUE – and (build 20348 and later) AUDIT_DISTRIBUTION, AUDIT_EMAIL, AUDIT_AI, AUDIT_SYSTEM_HEALTH and AUDIT_DB_TABLE – limited to the current project, as several projects can share one audit log database. AUDIT_OBJECT only holds existing workbooks. The questions users typed to nikolAI (Question_Text in AUDIT_AI) are left out;
  • a dimension Audit log with the units Workbook execution, Workbook, Folder, Session, User, Application, Event kind, Execution status, Failure cause, Visual and Visual issue – and (build 20348 and later) Distribution, Distribution run, Distribution status, Email address and Email status, and (build 20375 and later) Distribution run mode (scheduled or test). Each unit is named Audit log plus its title, e.g. Audit log Workbook, so the names never clash with your own units. Workbook, Visual and Distribution show and sort by name;
  • the dimension Audit log system health with the units Database, Database role, Database type, Database server, Health snapshot and Database table, for the daily system health snapshots – size, disk, memory and largest tables of the project's databases (new in build 20348). It is a dimension of its own, as the snapshots aren't linked to sessions or workbooks;
  • the dimension Audit log AI with the units AI call, AI intent, AI provider, AI model and AI call status (new in build 20352). The AI calls are still linked to the session and the workbook in Audit log;
  • the dimension Audit log calendar with its dynamic views, when the audit log uses its own calendar (build 20324 and later);
  • facts named Audit log: ..., in the fact folder Audit log (build 20321 and later);
  • metrics in the metric folder Audit log:

  Metric	Counts
  Executions	Report executions
  Average duration (s), Max duration (s)	How long executions took, in seconds
  Failed executions	Executions that failed
  Workbooks	Existing workbooks
  Used workbooks	Workbooks that were run in the period
  Unused workbooks	Workbooks minus used workbooks – 1 for a workbook nobody ran in the period
  Sessions	Logins
  Active users	Users who logged in
  Failed visuals, Slow visuals	Visuals that failed or were slow

  The folder also has metrics for distributions, e-mails, AI calls and system health (new in build 20348):

  Metric	Shows
  Distribution runs, Failed distribution runs	Distribution runs, and the runs that failed
  Average distribution duration (s)	How long distribution runs took, in seconds
  Distributed workbooks, Distributed workbooks failed, Distributed workbooks skipped (no data)	The reports of the runs – skipped ones had no data and weren't sent
  Emails sent, Emails failed, Emails skipped	The e-mails of the runs
  Emails	E-mail rows – for collated e-mails one per address
  Distribution warnings	Warnings in the status reports
  AI calls, Failed AI calls	Calls to the AI service, and those that failed or got no answer
  Average AI call duration (s)	How long AI calls took, in seconds
  AI units	AI usage as one number – see AI Units in 32.3 The Audit Log
  AI input tokens, AI cached input tokens, AI output tokens, AI web searches	The raw AI usage
  Database size (MB), Data size (MB), Log size (MB)	The size of a database
  Disk size (MB), Free disk (MB)	The disk the database is on
  Free server memory (MB), Database engine memory (MB)	Memory of the database server
  Connections	Connections to the database
  Table size (MB), Table rows	The largest tables of a database

  The system health metrics are snapshots, so they aren't added up over days: they show the largest value in the period – the smallest for Free disk (MB) and Free server memory (MB).

  The counting metrics are shown without decimals (build 20331 and later).

  [!NOTE] Before build 20331, the unit Folder was called Workbook folder, and Workbook and Visual were sorted by their ID. Units installed by an older build keep their name and sorting when you install again – change them yourself if you like.

  It also creates any audit log tables that are missing in the audit log database. Afterwards the new tables are arranged around AUDIT_REPORT inside an orange section Audit log – see Sections above – and the Dimensions and Warehouse tabs are shown side by side, with the new dimension selected (build 20343 and later). The tables of the Audit log data source have orange title bars; installing again replaces the old light blue, but keeps a colour you picked yourself. The tables of distributions, AI calls and system health form a band below the others (build 20348 and later).

  [!NOTE] Before build 20343, the new tables were placed to the right of the existing ones without a section, and Modeller switched to the Dimensions tab. Save the project as usual to keep the model.

  Running Install data model... again only adds what is missing, e.g. the tables, units, facts and metrics of a newer build; objects you have changed are left as they are. New tables are placed next to the audit log tables already in the diagram, lined up with them, and the section Audit log grows to cover them – its colour, title and position stay as you left them (build 20348 and later).

  [!NOTE] Before build 20348: installing again placed new tables to the right of all other tables, and the section Audit log didn't change.

  [!NOTE] Before build 20352, the AI units AI call, AI intent, AI provider, AI model and AI call status were part of the dimension Audit log. If you installed the model with build 20348 to 20351, installing again leaves these units in Audit log and adds the dimension Audit log AI without units.

  The objects have the same IDs in every project, so audit log reports can be copied between projects.

  When you click OK in the audit log settings and the log is on with a working database, but the project has no audit log data model yet, Modeller asks Install the audit log data model now? – Yes does the same as Install data model....

  If something goes wrong, Modeller says The audit log data model cannot be installed: followed by the reason. If only the tables in the database can't be created, it says The data model was installed, but the audit log tables cannot be created in the database: followed by the reason.

  The Audit Log's Dates

  New in build 20314.

  The dates of the sessions, workbook executions and visual issues – and (build 20348 and later) of distribution runs, AI calls and system health snapshots – are linked to a date dimension, so reports can show the audit log by day, week, month or year. E-mails have no date of their own: use Emails sent by the date of the distribution run. When you install the model, Modeller asks Which dates should the audit log use? in the dialog Audit log date dimension:

  Option	What it does
  The audit log's own calendar	Adds the dimension Audit log calendar – see below. Double-click the option to pick it right away.
  A date dimension of the project	Links the audit log dates to the day unit you select in the list. Reports can then filter audit log data and other data on the same dates. Double-click a unit to pick it.

  The list shows the project's day units – units whose key is a date, or a number or text with a date format that has a day, e.g. yyyymmdd. Its columns are Unit, Dimension, Lookup table and References – the number of objects that use the dimension, as Dependencies counts them. The most used unit comes first and is preselected; it is usually the project's main date dimension. When the dates are already linked, the current choice is preselected instead (build 20373 and later).

  If the project has no date dimension, Modeller doesn't ask and uses the audit log's own calendar.

  Changing the dates (build 20373 and later): Modeller asks every time you install, so running Install data model... again lets you pick another date dimension, or switch between a date dimension of the project and the audit log's own calendar. Keep the preselected choice to leave it as it is. If you pick something else, Modeller asks Change the dates of the audit log? with what it changes From and To:

  • Leaving the audit log calendar removes the dimension Audit log calendar and its tables AUDIT_CALENDAR, AUDIT_MONTH and AUDIT_WEEKDAY. The message lists the objects outside the data model that use the calendar – workbooks by name, at most 10 – because they stop working.
  • Leaving a date dimension of the project: reports that show audit log data by that dimension's units no longer filter or group it by those dates. Change them to the new date units – Modeller doesn't list them, as the dimension is used by much more.

  [!NOTE] Before build 20373, Modeller asked only the first time; installing again kept the dates, and the date columns of audit log tables added by a newer build could be left without a link to the project's date dimension. Install again with build 20373 or later to link them.

  For a day unit with a date format – e.g. an integer key like 20261028, as in Visma Business – Modeller adds a virtual field to each audit table, e.g. Login_Date_Key, that turns the audit date into that format. If the format can't be converted on the audit log's database, it says The date format "…" of the unit "…" is not supported for this database.

  The audit log calendar (build 20324 and later) is the dimension Audit log calendar with the units Date, Week, Month, Quarter, Year, Month in year and Day in week, each named Audit log plus its title. Month in year and Day in week show the month and weekday names (e.g. January, Monday) in the user's language. Weeks are ISO weeks, starting on Monday. The calendar table also has the fields DaysAgo, WeeksAgo, MonthsAgo, QuartersAgo, YearsAgo and DateLY (the date last year) for filters such as the last 30 days.

  The calendar is made of the dynamic views AUDIT_CALENDAR, AUDIT_MONTH and AUDIT_WEEKDAY, used as CTE – nothing is created in the database. It runs from 1 January of the year of the project's first audit log session (or of this year, if nothing has been logged yet) to 31 December of next year. Running Install data model... again updates the first day.

  [!NOTE] In builds 20324 to 20330, the calendar was offered as one entry in the list of date units, and its names tables had other names; installing again renames them to AUDIT_MONTH and AUDIT_WEEKDAY. Before build 20324, the audit log dates could only be linked to a date dimension of the project. Before build 20314, they weren't linked to a date dimension at all.

  Uninstalling the Model

  Right-click the Audit log node → Uninstall data model.... It removes the tables, the dimensions Audit log, Audit log system health (build 20348 and later) and Audit log AI (build 20352 and later) with their units, the Audit log calendar with its dynamic views, the section Audit log (build 20343 and later), the facts and the metrics. The metric folder Audit log stays if you put other metrics in it. A date dimension of your own project that the audit log used stays as well, and so do the audit log settings.

  Modeller first looks for objects that use the model, then asks Uninstall the audit log data model? The question lists as bullets (build 20336 and later):

  • what is Removed from the project;
  • what is Not changed: the audit log keeps logging (to stop it, switch it off in the audit log settings), the logged data in the audit log database, and the Audit log data source;
  • the objects – e.g. workbooks – that use the data model and stop working, at most 10 of them, then … and N more.

  Click OK to uninstall. The progress window shows each step, e.g. Removing the facts... (build 20336 and later). Save the project as usual to keep the change.

  Table Settings

  Select a table to see its settings under Properties:

  Setting	What it does
  Name, Title, Description	The table's name in the model, and texts for users
  Data source	Where the table is
  Table/view	The physical table – ... (Change source table) points the model table to another physical table
  Row count	The number of rows – helps Datashop pick the best table when facts exist in several tables. The button counts them.
  Pre-select using derived table	Reads the table through a SELECT DISTINCT sub-query – only when it's proven to be faster
  WHERE clause	A filter always applied to the table, e.g. Status_ID = 1
  Indexes	Indexes Acinta creates – see 27.1 Performance Tuning

  Field names and data types come from the database and can't be changed.

  Virtual Fields

  A virtual field is a calculated column, defined by an SQL expression on the table's other fields – e.g. {Quantity} * {Unit Price}, or a cleaned-up code.

  1. Select the table, then HOME → New → New virtual field (or right-click the table).
  2. Rename it (it starts as VA New attribute), and click Edit... to write the expression. Refer to other fields as {FieldName}.

  Modeller test-runs the expression and asks you to correct it if it fails. Virtual fields can be mapped to units and facts like any field.

  Extract to Virtual Attribute (right-click a field) creates a virtual field from an existing one and asks Move mappings to new attribute? if the field is used. For fields of dynamic views, the AI service works out the expression from the view's SQL; if it can't, Modeller says Unable to extract the expression for "…": with the reason, e.g. why the column can't be written as a virtual field, and creates nothing. For database tables and views, the new virtual field simply refers to the field, e.g. {Amount} (build 20112 and later). The command isn't offered on virtual fields.

  To delete a virtual field, select it in the diagram and press Del, or right-click it → Delete (build 20096 and later). Other fields come from the database and can't be deleted.

  [!NOTE] Before build 20112, Extract to Virtual Attribute could fail on database tables and views with Unable to read the definition of the view, and a failed AI call could create a virtual field containing the error text.

  Browsing Data

  Browse Table (Ctrl+B, or right-click a table) shows the table's contents in a SQL Explorer tab – see 19.1 SQL Explorer. For a materialized dynamic view it shows the stored table; Browse underlying SQL runs the view's SQL and shows current data – see 27.2 Materialized Dynamic Views and Caching (build 20306 and later).

  Related Topics

  • 22.2 Dynamic Views
  • 22.3 Joins and Table Relations
  • 23.2 Linking Units to Tables