Quickly Creating Dimension Tables


In addition to defining dimension attributes and configuring data for these attributes on Dimension Definition and Dimension Configuration, Quick Create lets you map data tables in the database to dimension tables without defining dimension attributes and hierarchies in advance. This can significantly improve efficiency when creating dimension tables.

Prerequisites


Before you start, ensure the following:

  • The current account has been assigned a menu group containing Dimensions. If not assigned, contact the application administrator.
  • To select a data table in the database as the data source for the dimension table, make sure that you have configured the data source information and created the data table according to data source registration.

Procedure


  1. Select Dimensions in the left navigation bar, which goes to the Dimension Definition tab by default.
  2. Select Quick Create and enter the following information on the Basic Information page.
    • Data Source: Select the data source type and the name of the data source registered under this type according to data source registration.
    • Query Mode: Select a query mode for the data.
    • Table Name: If Query Data Table is selected as Query Mode, select a data table in the database as the data source for the dimension table from the drop-down box.
    • SQL: If Use Custom SQL is selected as Query Mode, enter a custom SQL statement to query a data table in the database as the data source for the dimension table.
    • Dimension ID: Enter the unique ID for the dimension table under the current OU.
    • Dimension Name: Enter a name for the dimension table.
    • Source Table Data Preview: Preview data in the selected data table.
  3. Select Next.
  4. Enter the following configuration information on the Field Mapping page.
    • Data Type: The data type of the current Data Source Attribute will be used by default. You can change the data type in the drop-down box if necessary.
    • Dimension Attribute: Enter the name of the dimension attribute, and the name of the current Data Source Attribute will be used by default.
    • Description: Enter a description for the dimension attribute.
    • Primary Key: The primary keys have no business meaning and their value is required. The data type of the attribute set as a primary key is STRING. Set one or more attributes as the primary key(s) in sequence starting at 0 (the primary key numbered 0 can be treated as the first column of the dimension table), and the primary keys can all be associated with the foreign keys of a fact table. For example, in the device dimension table, the device ID is used as a primary key to find all other attributes of the device. In the corresponding fact table, there is also the same device ID, which allows the dimension table to be linked with the fact table.
    • Primary Key Name: The primary key name refers to the display name of the primary key. It is used together with the primary key of the same sequence number to query data accurately and quickly. For example, if the device data of multiple OUs are contained in the same dimension table and each device of the OU is unique, the target device can be found using both the “OU ID” set as the primary key and the “Device ID” set as the primary key name. Multiple attributes can be set as display names for multiple primary keys in a dimension table, but the number of primary key names must be less than or equal to the number of primary keys. For example, if “countryid 0”, “provinceid 1”, and “cityid 2” are set as primary keys, “provincename 1” and “cityname 2” can be set as primary key names of “provinceid 1” and “cityid 2”.
    • Not null: If data of the attribute is not null, the attribute value is required when configuring the data mapping, otherwise the metric query is affected.
  5. Select Publish to publish the dimension.


After a dimension is published, you can view the information of the dimension table on Dimension Definition and Dimension Configuration.