Data Schema Dialog

The Data Schema dialog in QPR ProcessAnalyzer provides a visual way to manage your project's Snowflake datatables and the relationships (foreign keys) between them.

Datatables stored in SQL Server or those used in an object-centric model (Objects, Events, EventToObject, ObjectToObject) are not displayed in this dialog.

Overview

The Data Schema dialog displays an entity-relationship (ER) diagram of your datatables and their relations. The ER diagram automatically arranges the datatables and relations for a clear overview. You cannot manually change the position of items.

You can zoom in and out, and pan across the diagram to explore your data schema. The view is constrained to keep the diagram visible.

Interaction: Clicking on a datatable or a relation in the diagram will select it and open a corresponding details panel on the side, allowing you to view and edit its properties. Clicking on an empty area of the diagram will unselect any item and close the panel. Initial State: If your project has no datatables to display, a message "Start by adding a datatable" will appear.

Column Display

A dropdown menu allows you to control the level of detail shown for columns in the datatables. The following selections are available:

• Don't show columns: Hides all columns for a clean, high-level view.
• Show key and relation columns: Displays only primary keys, foreign keys, and the columns they target.
• Show all columns: Shows every column for all datatables in the diagram.

Permissions

Your ability to make changes is determined by your project permissions. If you lack GenericWrite permissions, all fields will be read-only, and buttons for creating new items will be hidden. Deleting datatables requires the DeleteModel permission.

Handling Invalid Relations

When the dialog opens, it checks for invalid relations (e.g., a relation pointing to a non-existent datatable or column). If any are found, an error message will appear with a Delete invalid relations button to remove them.

Creating a New Datatable

1. Click the New datatable button to open the "New datatable" side panel.
2. Enter a unique name for your datatable in the Datatable name field.
3. Click Save.

The new datatable will be created and will appear selected in the diagram, automatically opening the "Datatable details" panel for further configuration.
The Save button is disabled if the chosen name is already in use by another datatable in the project.

Datatable Details

To edit an existing datatable, select it in the diagram to open the Datatable details panel. This panel has three tabs: Properties, Columns, and Data location.

Properties Tab

This tab displays key information about the datatable. The Properties tab has the following editable Fields:

• Datatable name: The name of the datatable. Must be unique within the project.
• Description: An optional, multi-line text field for notes.

The Properties tab has the following read-only fields:

• Datatable ID: The system-assigned numeric ID.
• Snowflake object type: Shows if the datatable is a Table or a View in Snowflake.
• Row count: The number of rows in the datatable.
• Column count: The number of columns in the datatable.
• Data changed: Timestamp of the last data load performed via QPR ProcessAnalyzer.
• Data changed by: The user who performed the last data load.
• Properties changed: Timestamp of the last change to the datatable's properties (e.g., name, columns).
• Properties changed by: The user who last changed the properties.
• Created: Timestamp of the datatable's creation.
• Created by: The user who created the datatable.

Click Save to apply any changes made to the name or description.

Columns Tab

This tab shows a table of all columns in the datatable, allowing you to add, edit, reorder, and delete them.

Editing a Column

• To rename a column, simply edit the text in the Column name field. The change is saved automatically when you click outside the textbox. Duplicate column names are not allowed.
• Use the Key checkbox to designate a column as part of the datatable's primary key.

Note that the Data type of an existing column cannot be changed.

Adding a New Column

1. Click on the "Add column" row at the bottom of the table.
2. Enter a Column name.
3. Select the Data type from the dropdown: Text (default), Decimal, Integer, Date, or Boolean.
4. Check the Key box if it's a primary key column.
5. Click the save button on that row to create the column. The button is only active when a valid column name is entered.

Reordering and Deleting Columns

• Drag and drop rows using the handle to reorder columns.
• Click the trashcan icon at the end of a row to delete a column.

Data Location Tab

This tab specifies where the datatable's data is stored in Snowflake. You can link a datatable to an existing Snowflake table or let the system manage it. The following fields are available:

• Snowflake database: The database name. Leave empty to use the default from the connection string.
• Snowflake schema: The schema name. Leave empty to use the default.
• Snowflake table: The table or view name. Leave empty to use a system-defined table.

Note: Table names cannot start with "pa_dt" or "qprpa_dt" as these are reserved prefixes.
If you specify a database, you must also specify the schema and table for the changes to be saved.

Deleting a Datatable

To delete a datatable, click the Delete button. This button is hidden if you do not have the DeleteModel permission.

Managing Relations (Foreign Keys)

Relations define how your datatables are linked. You can create and manage these from the Data Schema diagram.

Creating a New Relation

1. Click the New relation button to open the "New relation" side panel.
2. Define the relation by selecting the source and target datatables and columns:
   • Source datatable: The table containing the foreign key.
   • Target datatable: The table the foreign key points to.
   • Source column: The column in the source table.
   • Target column: The column in the target table.
3. Click Create.

The new relation will appear as a line connecting the two datatables in the diagram.

Editing a Relation

To edit an existing relation, select it in the diagram to open the Relation details panel.

Defining Column Pairs

• A relation is defined by at least one pair of columns. Use the Source datatable, Target datatable, Source column, and Target column dropdowns to define the link.
• Only columns with Text or Integer data types can be used in a relation.
• You can add more column pairs for composite keys by clicking the "Add column pair" button.
• Each additional column pair can be deleted by clicking its delete button.

Searching: All dropdown lists include a search field to help you quickly find datatables or columns by name. The search is case-insensitive.
Saving Changes: Click the Save button to apply your changes. The button is disabled if any dropdown is empty or if the same column is used in multiple pairs within the same relation.
Automatic Closing: The panel will close without saving if you select another item or click on an empty space in the diagram.

Deleting a Relation

To delete a relation, click the Delete button in the Relation details panel. A confirmation dialog will appear.