Each dimension column has a set of associated properties. The definition of each property is described below:
Tip
If a database table's definition is changed in the metadata, then the table needs to be altered in the database. Use the Validate > Validate Table Create Status to compare metadata definitions to physical database tables. The option also provides the ability to alter the database table, through a pop-up menu option from the validated table name. See the example below.
A sample Properties screen is as follows:
Tip
The two special update keys enable you to update the column and step either forward or backward to the next column's properties. ALT-Left Arrow and ALT-Right Arrow can also be used instead of the two special update keys.
General
Options | Description |
|---|---|
Table Name | Database-compliant name of the table that contains the column. [Read-only]. |
Column Name | Database-compliant name of the column. Typically, column-naming standards exclude the use of spaces etc. A good practice is to only use alphanumerics, and the underscore character. Changing this field alters the table's definition. The case conversion button on the right converts the text between different cases: UPPER CASE, Capitalized Case and lower case. The mode cycles to the next case in the sequence of each conversion. |
Business Display Name / Column Title | Name that the business uses to refer to the column, which is included in the RED-generated documentation and can be used in the end user layer of other tools. [Does NOT affect the physical database table]. As such it is a free form entry and any characters are valid. Note The case conversion button on the right converts the text between different cases: UPPER CASE, Capitalized Case and lower case. The mode cycles to the next case in the sequence of each conversion. |
Column Description | This field contains the description for the column. It may be a description from a business user's point of view. This field might additionally contain information on where and how the column was acquired. For example, if the column is sourced from multiple tables or is a composite or derived column then this definition would normally describe the process used to populate the column. This field is used in the documentation and is available via the view ws_admin_v_dim_col . This field is also stored as a comment against the column in the database. |
Physical Definition
Options | Description |
|---|---|
Column Order | Numeric value that controls the relative order of columns in the database create statement. The lowest numbered column will appear first in the table. Although this affects the physical table definition no action will be taken unless the table is re-created in the database. The columns can be re-numbered based on the existing order by choosing the Respace Order Number pop-up menu option when positioned over any column in the table. This action will number the columns in increments of 10 starting at 10. In addition to a simple change of the order field, the column order can be changed by first displaying the columns in the middle pane and then using drag and drop to move the columns around. This drag and drop process will automatically renumber the columns as required. |
Data Type | Database-compliant data type that must be valid for the target database. Typical data types for Oracle are integer, number, char(), varchar2() and date. For SQL Server, common types are integer, numeric, varchar() and datetime. See the database documentation for a description of the data types available. Changing this field alters the table's definition. |
Null Values Allowed | Determines whether the table column can hold NULL values or whether a value is always mandatory. |
Default Value | Initial value that is assigned to the column when a row is inserted into the table but no value is specified for the column. |
Character Set | Teradata OnlyDatabase-compliant table column character-set used for storage. |
Format | Teradata OnlyDatabase-compliant table column format. It does not affect the physical table definition, but rather provides input to the view ws_admin_v_dim_col which can be used to assist in the population of an end user tool's end user layer. As such it is a free form entry and any characters are valid. Typically format masks are only used on numeric fields. Example: #,###0.00. It is not worth the effort of populating this field unless it can be utilized by the end user tools in use. |
Character Comparison/Sorting | Teradata OnlyDetermines how the column character values are treated for comparison and sorting operations. Choose from: case specific, not case specific, uppercase case specific or uppercase not case specific. |
Compress/Compress Value | Teradata OnlyOptional list of values to be compressed. By default, only NULL is compressed if no list of values is specified. |
Meta Definition
Options | Description | ||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Format | Optional format mask that can be used in end user tools. [Does NOT affect the physical database table]. As such it is a free form entry and any characters are valid. Typically format masks are only used on numeric fields. Example: #,###0.00. It is not worth the effort of populating this field unless it can be utilized by the end user tools in use. | ||||||||||||||||||||
Numeric | Indicates whether the table column holds values that are numeric. This is normally only relevant for fact tables. It does not affect the physical table definition, but rather provides input to the view ws_admin_v_dim_col which can be used to assist in the population of an end user tool's end user layer. The use of this field is not relevant unless it can be utilized by the end user tools. | ||||||||||||||||||||
Additive | Indicates whether the table column holds values that are additive. This implies that the column can be summed when performing data grouping in a query. This is normally only relevant for fact tables. It does not affect the physical table definition, but rather provides input to the view ws_admin_v_dim_col which can be used to assist in the population of an end user tool's end user layer. The use of this field is not relevant unless it can be utilized by the end user tools. | ||||||||||||||||||||
Attribute | Indicates whether the table column holds values that are descriptive, and/or are used for grouping/summing. An attribute is defined as a column that is non factual. For example we may have an order number, or a invoice number stored in the fact table. Such columns are considered attributes, rather than facts. This checkbox is therefore normally only relevant for fact tables. This check box does not affect the physical table definition, but rather provides input to the view ws_admin_v_dim_col which can be used to assist in the population of an end user tools end user layer. The use of this field is not relevant unless it can be utilized by the end user tools. | ||||||||||||||||||||
End User Layer Display | Indicates whether the table column is available/visible to end users. If set the documentation will include the column in the glossary and in the user documentation. It is also used to decide what columns appear in the view ws_admin_v_dim_col. Typically columns such as the artificial key would not be enabled for end user display. | ||||||||||||||||||||
| Business Key | Indicates whether the column is part of the BUSINESS key, which is defined during the update procedure build. [Normally maintained automatically]. Multiple columns can form the primary business key. | ||||||||||||||||||||
| Artificial Key | Indicates whether the column is the generated artificial/surrogate key (unique identifier) for the table. Only one artificial key per table is supported. [Normally maintained automatically]. | ||||||||||||||||||||
| Key Type | Key type that is assigned and used when generating the table's update procedure and indexes. [Normally maintained automatically]. It can be altered here, but this should only be done if the consequences are fully understood and tested. The supported values are:
|
Code Generation
Options | Description |
|---|---|
Zero Key Value | Determines the value populated for the column in the Invalid Join or Unknown record. By default, NULL is used when a value is not specified. All dimensions that use standard WhereScape RED generated procedures have a row with an artificial key of zero. This row is used to link to the fact records when no match on the dimension can be found. For example we may have some sales records that come through without a product defined. WhereScape RED will be default associate these fact records with the zero key product dimension entry. So, we might set the zero key value to 'Unknown product' for the name column in the product dimension. |
Source Details
Options | Description |
|---|---|
Source Table | Identifies the source table where the column's data comes from. This source table is normally a load table, or a dimension table within the data warehouse. If the column was sourced from multiple tables, then the normal practice is to record one of the tables in this field and a comment listing all of the other tables in the Source strategy field. This field is used when generating a procedure to update the stage table. It is also used in the track back diagrams and in the documentation. |
Source Column | Identifies the source column where the column's data comes from. Such a column is normally a load table column, which in turn may have been a transformation or the combination of multiple columns. This may also be a dimensional key where a dimension is being joined. |
| Transformation | Refer to Dimension Column Transformations for details. [Read Only] |
| Join | Indicates whether the table column is used in a table join. [Normally maintained automatically but can be optionally changed to override the default join logic used in the generated update procedure]. The Source table and Source column fields will provide the dimension table's side of the join. The options for this field are: False, True, Manual and Pre Join.
|
Changing a Column Name
If the Column name or Data type is changed for a column then the metadata will differ from the table as recorded in the database.
- Use the Validate > Validate Table Create Status menu option or the right-click menu to compare the metadata to the table in the database.
- A right-click menu option of Alter Table is available when positioned on the table name after the validate has completed. This option will alter the database table to match the metadata definition.
For example: Analysis Services does not like name as a column name.
For dim_customer it will therefore be necessary to change the column name from name to cname.
- Click on the dim_customer object in the left pane to display the dim_customer columns in the middle pane.
- When positioned on the column name in the middle pane, right-click and select Properties from the drop-down menu.
- Change the column name from name to cname as shown below. Click OK to leave the Properties window.
- Right-click the dim_customer object in the left pane and select Validate against Database.
- The results in the middle pane shows that the metadata has been changed to cname while the column name in the database is still name.
- Right-click dim_customer in the middle pane and select Alter table from the context menu.
- A warning appears, displaying the table and column name to be altered. Select Alter Table.
- A dialog appears confirming that dim_customer has been altered. Click OK.