Datatable Properties Dialog

From QPR ProcessAnalyzer Wiki
Revision as of 12:09, 2 November 2023 by Ollvihe (talk | contribs)
Jump to navigation Jump to search

Datatable Properties Dialog is for viewing and changing datatable properties. Data location settings can only be changed for Snowflake datatables. Only limited column settings can be changed for datatables using database views. Note that when using the dialog and switching tabs, changes made need to be saved before going to other tab.

General

General tab shows following information for the datatable:

  • Description: Editable description text for the datatable.
  • Source object type: Tells which type of database object the datatable is linked to (either Table or View). QPR ProcessAnalyzer is able to create and modify database table, but view is not modified by QPR ProcessAnalyzer. If desired to use database view for the datatable, the view needs to be created beforehand to the data source (e.g., Snowflake). The database table is also deleted when the datatable is deleted when using the default table.
  • Datatable id: System assigned numerical id for the datatable.
  • Row count: Number of rows in the datatable.
  • Column count: Number of columns in the datatable.
  • Data changed: Timestamp when the data was last time changed to the datatable (i.e., new rows imported, existing rows modified, or rows deleted).
  • Data changed by: User who last time changed the data in the datatable.
  • Properties changed: Timestamp when the datatable properties were last time modified.
  • Properties changed by: User who last time modified the datatable properties.
  • Created: Timestamp when the datatable was created.
  • Created by: User who created the datatable.
  • Used by models: List models which are using this datatable. Also the role (cases or events) is mentioned.

Data location

Data Location tab links the underlying Snowflake table or view for the datatable. There are following options:

  • Default table: Use the system managed table. Use this option when the data is fully managed in QPR ProcessAnalyzer UI (not in the Snowflake UI). The table name has the following format: qprpa_dt_<databaseid>_<datatableid>. The databaseid can be configured in the system settings.
  • Custom table: Use specific table which is located in the default database and schema (defined in the Snowflake connection string configured to the system). When this setting is enabled, the table name needs to be defined.
  • Custom schema: Use specific table and schema which is located in the default database. When this setting is enabled, the table and schema name needs to be defined.
  • Custom database: Use specific table, schema and database. When this setting is enabled, the table, schema and database name needs to be defined.

Use the custom table/schema/database options when the data pre-exists in Snowflake and want QPR ProcessAnalyzer to use those tables. Custom table/schema/database option is also useful when wanting to use tables with specific names instead of system assigned names.

When desired to use database view for the datatable, create the view to Snowflake and configure the view name in the datatable settings. When using a view, data cannot be imported to it and columns cannot be modified.

When using custom table for the datatable and deleting the datatable, the underlying custom table is not deleted. When using the default table and deleting the datatable, the underlying table is also deleted.

Columns

Columns tab shows a list of all columns in the datatable. Following operations can be done:

  • Columns can be created.
  • Columns can be deleted. Note that when a column is deleted, data in the column is also deleted.
  • Column name can be modified. Note that there cannot be duplicate column names.
  • Key columns can be defined. Datatable has an optional key, consisting of one or many columns. Defining a key gives a hint to analytics queries which combination of columns uniquely identifies rows in the datatable.
  • Columns can be reordered with drag-and-drop using the handle in the left side of the row.
  • Columns information can be exported to Excel file.

Note that column data type can only be set when the column is created, and the column data type cannot be modified afterwards. The reason is that changing the data type would also require to convert the data in the column. Column datatype can be changed by creating a new column, converting the data and deleting the original column.