Datatable Properties Dialog

From QPR ProcessAnalyzer Wiki
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> (databaseid can be configured globally 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. For security reasons, it's not possible to configure a system managed table (starting with "qprpa_dt_") as the custom table.
  • 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 option when the data already exists in Snowflake and you want QPR ProcessAnalyzer to use those existing tables. Custom table/schema/database option is also useful when you want to use tables with user-friendly descriptive names instead of system assigned names.

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

QPR ProcessAnalyzer can create the Snowflake table if it doesn't exist. The schemas and databases are not created by QPR ProcessAnalyzer.

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

Columns

Columns tab shows a table of all columns in the datatable. Following operations can be performed for the columns:

  • New columns can be created. When a column is created, a name, data type and key flag is defined.
  • 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. Maximum column name length for Snowflake datatables is 255 characters and for SQL Server 128 characters.
  • Key columns can be defined. Datatable has an optional key (can also be called primary key) consisting of one or many columns. Defining a key gives a hint to the analytics queries which combination of columns uniquely identifies rows in the datatable. If this combination of columns exist, defining the key is recommended.
  • Columns can be reordered with drag-and-drop using the handle in the left side of the row. Note that the column order in the underlying database table or view is not changed. Note that when refreshing datatable from data source, the column order is reset to match with order in the data source.
  • Columns information can be exported to an Excel file.

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

When the datatable uses a database view, only the column order and key can be changed. Thus new columns cannot be created or columns renamed.

Retrieved from "https://wiki.onqpr.com/pa/index.php?title=Datatable_Properties_Dialog&oldid=24182"