Each Retro column has a set of associated properties. The definition of each property is defined below.
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 to compare the metadata to the table in the database. When positioned on the table name after the validate has been completed, a right-click menu option Alter Table will alter the database table to match the metadata definition.

If a database table's definition is changed in the metadata then the table will need 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.

General

FieldsDescription
Table NameDatabase-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.

A 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 each conversion.

Business Display Name

Name that the business uses refers 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.

A 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 each conversion.

Column DescriptionThis field contains the description for the column. In the case of dimension keys, this field is used to show the join between the Retro table and the dimension, once it has been defined as part of the Retro table update procedure generation.

Physical Definition

FieldsDescription
Column OrderNumeric 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. For SQL Server common types are integer, numeric, varchar() and datetime.

Refer to the database documentation for a description of the data types available. Changing this field alters the table's definition.

Null Values AllowedDetermines whether the table column can hold NULL values or whether a value is always mandatory.
Default ValueInitial value that is assigned to the column when a row is inserted into the table but no value is specified for the column.

Meta Definition

FieldsDescription
FormatOptional 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.
NumericIndicates whether the table column holds numeric values. 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.
AdditiveIndicates 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.
AttributeIndicates whether the table column holds values that are descriptive, and/or are used for grouping/summing. An attribute is defined as a non factual column. 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 checkbox 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.
Business KeyIndicates 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.
Key TypeKey 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:

Key typeMeaning
0The artificial key. Set when the key is added during drag and drop table generation.
1Component of all business keys. Indicates that this column is used as part of any business key. For example: By default the dss_source_system_key is added to every dimension table. It is considered part of any lookup on that table and has the key type set to 1. Set when the column is added during drag and drop table generation.
2Indicates that this column is a dimensional join. Used on fact tables to indicate the dimension keys. Results in bitmap indexes being built for the columns. Set during the update procedure generation for a fact table, based on information from the staging table.
3Slowly changing column indicator. Used on dimension tables to indicate that the column is being managed as a slowly changing column within the context of a slowly changing dimension. Set when a column is identified during the dimension update procedure generation.
4Previous value column indicator. Used on dimension tables to indicate that the column is being managed as a previous value column. The source column identifies the parent column. Set during the dimension creation.
AIndicates that the column is part of the primary business key. Set whenever a business key is defined as part of an update procedure generation.
B-ZIndicates that the column is part of a secondary business key. Only used during index generation and not normally set.

Source Details

FieldsDescription
Source TableIdentifies the source table where the column's data comes from.
Source ColumnIdentifies the source column where the column's data comes from.
TransformationTransformation. [Read-only].
JoinIndicates 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.
  • Setting this field to Manual changes the way the dimension table is looked up during the update procedure build. It allows you to join the dimension manually in the Cursor mapping dialog (where the 'Where' clause is built).
  • Setting this field to Pre Join activates the Pre Join Source Table field and allows you to select a table from the drop-down list.
Pre Join Source TableIndicates the table from which the pre joined column was sourced. When the Join option is set to False, this field becomes inactive. When the Join option is set to True or Manual, this field is set to the current table name. When the Join option is set to Pre Join, then you can select the required table from the drop-down list.


  • No labels