Skip to content

Managing data mart views

Overview

Once a data mart is configured and verified, you can create a physical object in a connected database based on it. This allows you to use the data mart in reports, dashboards and other analytical tools.

Where view creation is available

  • On the data mart configuration screen
  • On the data mart verification screen (after successful script generation and/or execution)

View creation process

  1. Creation SQL script generation
    • The system generates an SQL script to create the object in the database
    • The SELECT part of this script is formed exactly as it is during verification
    • The script is displayed in a readable, formatted view

  2. Selecting the object type The user can choose one of three object types:

    • Regular view — a virtual table; data is retrieved dynamically
    • Materialized view — data is physically stored and refreshed periodically
    • Regular table — a physical table populated with the data mart's data

  3. Selecting a database or schema for view creation

    • The database or schema name can either be entered manually or selected from the list of databases or schemas available to the user
    • When connecting to ClickHouse, a database is selected where the view will be created
    • When connecting to PostgreSQL or MS SQL Server, a schema is selected where the view will be created
  4. Working with the generated script
    • The script can be copied to the clipboard
    • The script can be exported to a text file
    • The script can be executed directly from the interface on the connected database
  5. Execution results
    • After script execution, the system displays a report with the database response (success/failure, error messages, etc.)

Error handling

  • If the system cannot generate the script (e.g., due to errors in the data mart configuration) or execute it on the database, the user receives a notification with a detailed explanation of the problem

Subsequent management

  1. Available actions for created views:
    • Show — the system generates an SQL script to display data from the created view
    • Update — the system generates an SQL script to drop the previously created view and create a new view according to the current data mart settings
    • Delete — the system generates an SQL script to drop the previously created view
  2. Created views can also be managed using the standard tools of the DBMS in use, for example, to periodically refresh data in materialized views or tables without changing their settings
  3. The association between a data mart and the view created for it in the database is stored only in the specific data mart instance and is not copied when cloning the data mart, creating a new project version, or cloning the project