Managing data mart views

A configured data mart can be published in the connected database as a view, a materialized view or a table. External systems — BI tools, reporting systems — can then read the data mart result directly through the database connection, without going through DataForge.

Once an object exists in the database, DataForge keeps track of it: it shows the state of the object, notices when the data mart no longer matches what was deployed, and records every operation performed on the object. Carrying the access rights of the source tables over to the object is described on the Access rights transfer page, and scheduled and event-driven updates of deployed objects on the Refreshing data mart views page.

Where view management is available

On the data mart constructor page — the Manage data mart view in the database button next to Verify data mart
On the verification panel, next to the generated query
In the data marts list — the database icon in the Actions column, coloured by the status of the view
In the data marts list — the Status column, which shows the status of the deployed object

Managing views is available to the Super administrator, Administrator, Developer and Analyst roles.

Object types

Type	Description
Regular view	A virtual table: the query is executed on every access, so the object always reflects the current state of the source
Materialized view	The query result is stored physically and has to be refreshed to be brought in step with the source
Table	A physical table holding a snapshot of the result at the moment it was created

The available types depend on the target database:

Database	Default schema	Supported types
PostgreSQL	`public`	Regular view, materialized view, table
MS SQL Server	`dbo`	Regular view, table
ClickHouse	`default`	Regular view, materialized view, table

If the data source used by the connection has the Transfer access rights to created data mart views option enabled, a materialized view cannot be created: a stored snapshot of the result cannot be filtered for each reader separately. On such a source create a regular view or a table instead — see Access rights transfer.

Creating a view

The Create data mart view in the database action opens the creation dialog.

Select the view type — one of the types supported by the target database. When the access-rights transfer is enabled on the data source, Materialized view is shown disabled
Select the target location — for PostgreSQL and MS SQL Server a schema is selected (Select view database schema), for ClickHouse a database (Select view database). The name can be typed in or picked from the list of available schemas or databases, loaded with the Load schemas or Load databases button
Review the script — the system generates the script that creates the object. Its SELECT part is built exactly as during verification. The script can be copied to the clipboard, exported to a text file, or executed on the connected database directly from the dialog. Above the script the Access rights to be transferred panel shows which access rights the object will receive — see Access rights transfer
Read the result — after execution the system reports the response of the database, and on success shows the notification "Data mart view created successfully in the database"

The name of the object is built from the data mart name and is shown in the View in the DB column of the data marts list.

Notes:

The Create data mart view in the database action is unavailable while the data mart has critical errors or an invalid configuration ("Data mart contains invalid elements")
While the data mart contains a measure with the Error health status, the view cannot be created or updated from the constructor: "The data mart contains measures with Error health status"
If an object with that name already exists in the database, the system offers to link the data mart to it instead of recreating it: "The view already exists in the database. Do you want to link the data mart to this view?" On linking, the system checks whether the existing object matches the current configuration of the data mart and marks the view as up to date or as outdated accordingly

Actions on an existing view

Action	Description
Show data mart view	Queries the object and displays the rows. The same Result limit selector is used as for data mart verification, confirmed with the Show button. When the query runs under your personal database credentials, only the columns your account is granted are shown; if the account has no rights on the object, the system reports "You do not have sufficient permissions to access the requested data in the database." and the status of the view does not change
Update data mart view	Recreates the object from the current data mart configuration: the previous object is dropped and a new one is created. The dialog shows the script and the access-rights preview, and the object is recreated when the script is executed. Use it after the data mart has changed
Open data mart view details	Opens the details dialog of the view — its status, transferred access rights, assigned update rules and update calendar
Delete data mart view	Removes the object from the database, after the confirmation "Are you sure you want to delete the data mart view in the database?"
Unlink from view in the database	Removes only the link stored in DataForge and leaves the object in the database untouched

The contents of the menu follow the status of the view: while nothing is deployed only Create data mart view and Open data mart view details are offered, and while an operation on the object is running, Open data mart view details stays available and the other actions are replaced by Cancel update.

In an on-premises installation Cancel update stops the creation, recreation, data refresh or deletion of the object that is in progress: an operation still waiting in the queue is cancelled at once, and a running one is stopped in the database. The notification "Cancellation requested: the operation is being stopped in the database" is shown. A cancelled creation leaves no object behind. After any other cancelled operation, an object that is still in the database returns to Active; if the previous object had already been dropped, the view goes to Update error with the message "The operation was cancelled after the old object had been dropped — recreate the object". Cancel update is available to the Super administrator, Administrator and Developer roles.

Notes:

Update data mart view is unavailable while the data mart configuration is invalid
For a materialized view deployed earlier, Update data mart view is disabled with an explanatory tooltip once the access-rights transfer is enabled on the data source (see Access rights transfer)
If the object has been removed from the database by hand, the system reports "View not found in database" and explains that only the link can be removed: "The view may have been deleted manually. You can only remove the link to this view"
Created objects can also be managed with the standard tools of the database in use
A data mart cannot be deleted while a view is linked to it, unless the deletion dialog is told what to do with the object — see Deleting a data mart that has a view below
The link between a data mart and its view is stored only in that data mart. It is not copied when the data mart is cloned, when a new project version is created, or when the project is cloned

View statuses

Every data mart carries the status of its deployed object. The status is shown as a badge in the Status column of the data marts list, in the details dialog of the view, and as the colour of the database icon in the Actions column.

Status	Colour	Meaning
Not deployed	Blue	No object has been created for the data mart yet
Active	Green	The object exists and matches the current configuration of the data mart
Deploying	Grey	The object is being created in the database
Updating	Grey	The object is being recreated or its data is being reloaded
Stale	Yellow	The object exists, but the data mart has changed since it was deployed, or its data has not been refreshed recently
Update error	Red	The last operation on the object failed

Notes:

The Update error badge carries the message returned by the database in its tooltip
The Stale badge carries a call to action: "The data mart settings have changed — the view in the database is outdated. Update the view"
Statuses change as operations run, without reloading the screen

Long-running operations in an on-premises installation

In an on-premises installation creating, recreating, refreshing and deleting the object run on the server independently of the user's session, so an object that takes hours to build still appears in the application once the database has finished:

The request only starts the operation, and its progress is shown by the status of the view. One operation per data mart runs at a time: starting the creation again while the object is being created joins the running operation
The statements of these operations are not limited in time, so building or refreshing a large object is not interrupted by a timeout
The outcome is kept as a personal notification. The user who started the operation receives every outcome — success, failure or cancellation, for example "Data mart view for “…” created in the database" or "Failed to recreate the data mart view for “…”: …" — and the creator of an update rule receives the failures of the runs the rule started ("Scheduled run: …"). A user who was signed out when the operation ended sees the notification at the next sign-in
An operation is not lost when the application server restarts. Within a few minutes after the restart the system checks the database and resumes the operation: if the database is still executing it, the operation is followed to the end; if it has already completed there, only the result is recorded; otherwise the operation is run again
After three interruptions in a row the operation is stopped with the message "The operation was interrupted by application restarts 3 times and was stopped. Start it again". If the object is not found after an interrupted operation, the message is "The operation was interrupted by an application restart; the object was not found in the target database"

When a view becomes outdated

Every time a data mart is saved, the system compares the saved configuration with the definition that was deployed and decides what the change requires:

What changed	What it requires
Name or description of the data mart	Nothing. An existing object cannot be renamed — to give it a new name, delete the view and create it again
Composition of the data mart, aggregation levels, filters, hidden elements, data mart type, merge type, joins, source fact tables	The object no longer matches the data mart and is marked as outdated. It has to be recreated
Data types, or the structure of the source tables in the database	The object no longer matches the data mart and is marked as outdated. It has to be recreated
Access rights on the source tables	Only the access rights of the object are re-synchronized; its definition is left untouched

A view is also marked as outdated when an RMD element it relies on changes — its name, its formula or its connected source, including a change applied by a rollback in the RMD change history.

An outdated object is never recreated silently: the data mart keeps the Stale status until you recreate the view yourself, or until an update rule triggered by a configuration change does it for you.

When you ask to show an outdated view, or a view of a data mart whose configuration is invalid, the system warns first with the Data mart view is outdated dialog: "The data mart settings have changed since this view was created. Displayed data may not match the current configuration". You can then either Show anyway or Return to settings and recreate the object.

Recreating a view

Recreating drops the object in the database and creates it again from the current data mart configuration, so that the deployed object matches the data mart once more.

The new definition is validated before the old object is dropped. If validation fails, the existing object is left in place and the view goes to Update error with the message returned by the database
The confirmation dialog Recreate view states what will happen: "The view of the data mart "…" will be dropped from the database and created again. Continue?", shows the access-rights preview and is confirmed with Recreate
After a successful recreation, the access rights of the source tables are applied to the new object again
The view walks through Updating and back to Active, and the operation is written to the update calendar

Data mart view details

Open data mart view details opens a dialog that gathers everything known about the deployed object. Its header shows the Data mart name, the View in the DB, the View type, the Status and the Last update, and its tabs are:

Tab	Contents
Transferred access rights	Access rights discovered on the source tables and whether each of them reached the object
Assigned update rules	Update rules linked to this data mart, and the Add rule action for a new one
Update calendar	Past and future updates of this data mart

The first tab is described on the Access rights transfer page, the last two on the Refreshing data mart views page.

Deleting a data mart that has a view

When a data mart with a deployed object is deleted, the confirmation dialog asks what to do with the object:

Leave the checkbox Delete the view … from the database clear to keep the object. The dialog warns what that means: "The view will stay in the database without a DataForge connection and will no longer be managed by the product"
Select the checkbox to drop the object from the database together with the data mart

Errors

If the script cannot be generated — for example because of errors in the data mart configuration — or cannot be executed on the database, a notification explains the problem. Errors of the access-rights transfer are listed on the Access rights transfer page.

Message	Cause
"You do not have sufficient permissions to access the requested data in the database."	Show data mart view ran under your personal credentials, and your account has no rights on the object
"The account running the query lacks the required privileges in the target database. Contact the database administrator"	The database refused the operation: the account running it — the service account of the data source, or your personal account when the object is read — lacks the privileges it needs
"The operation timed out. Please try again."	A long-running operation did not finish in time. In an on-premises installation operations on the object are not limited in time