When building the data warehouse, WhereScape RED makes use of a number of special tables and columns. The two tables used are called by default dss_source_system and dss_fact_table. These tables are discussed in detail in the sections below.
The special columns used are defined in the table below.
Column name | Description |
|---|---|
dss_batch | Not used at this stage. |
dss_source_system_key | Added to support dimensions that cannot be fully conformed and the inclusion of subsequent source systems. See the section below for more details. |
dss_fact_table_key | Used in composite roll-up fact tables to identify the source fact table that contributed to the particular row. |
dss_create_time | Indicates when a record was created. |
dss_update_time | Indicates when the record was last updated in the data warehouse. Used in the update of rollup fact tables and aggregate tables. |
dss_count | Applied to fact tables. Provides a simple row count variable that can be used by end-user tools. |
dss_current_flag | Used for slowly changing dimensions. This flag identifies the current record where multiple versions exist. |
dss_version | Used for slowly changing dimensions. This column contains the version number of a dimension record. Numbered from 1 upwards with the highest number being the latest or current version. It forms part of the unique constraint for the business key of a slowly changing dimension. |
dss_start_date | Used for slowly changing dimensions. This column provides a date time stamp when the dimension record began life. If null then it was the first record. It is used to ascertain which dimension record should be used when multiple are available. |
dss_end_date | Used for slowly changing dimensions. This column provides a date time stamp when the dimension record ceased to be the current record. It is used to ascertain which dimension record should be used when multiple are available. |
dss_change_hash | Used for a Satellite table. This column identifies the differences in the descriptive columns of a Satellite table which is used for generating the change hash key for creating a Satellite object. |
All of these special columns and tables can be renamed through the Tools > Options > DSS Tables and Columns menu option. The columns can simply be renamed, but the tables require valid table names that meet certain criteria. Refer to the related sections below.
When using table names other than the defaults for dss_source_system and dss_fact_table it is worth it because, by default, the metadata backups will include any table that begins with "dss_". Therefore, if a table is used it is recommended that it have a name starting with "dss_". The advantage is that a working meta-repository will be established through a backup and restore if these tables are included in the backup set.
dss_source_system
This pseudo dimension is designed to identify a data source for a dimension row. Its purpose is to handle non-conformed dimensions or changes in source systems. If its use is not desired (default), then leave this field blank.
For example:
An organization has a number of factories. These factories are referenced by all of the operational systems. The production system has its code for each factory, and this is the unique means of identifying the factory. The distribution system has a factory short name which it uses for the unique identifier. The raw materials system simply uses the factory name. It is probably not practical or even desirable to force these source systems to utilize a standard factory identification method, so instead we allow the dimension to be non-conformed. We do, however, insist on a standard factory name convention, so that our reports and queries will join information when the factory name is used.
In this example, the dss_source_system_key is used to identify the source of the data for the dimension row. It also adds to the unique business key, so that two source systems can utilize the same code to refer to different entities. This key also provides a degree of future-proofing in the data warehouse, to assist in the possible changing of an underlying source system.
The generated procedure code always sets the key value of this table to 1. Therefore, manual code changes is required to make use of the functionality that this table offers.
When dimensions are built, the dss_source_system_key is added by default. It can be deleted if the need for such a feature is not seen.
If this table is to be given a different name, then it and all its columns can be renamed, or the following steps can be taken:
- Create a new table by dragging the column dss_source_system_name from dss_source_system into a dimension target.
- Change the object type from dimension view to dimension and specify the new table name. (see note above on the use of "dss_").
- Rename the dss_source_system_name column to match the new table name.
- Delete the last two columns.
- Under the table Properties, change the table type to Mapping table. This prevents the table from being seen as a dimension in the documentation.
- Change the dss_source_system table name in the screen above, via the Tools>Options>DSS Tables and Columns defaults menu option.
dss_fact_table
This pseudo dimension is used internally by the WhereScape RED generated procedures to assist in the updating of rollup fact tables. It provides a means of identifying which fact table contributed a particular row to a composite rollup fact table. See the section on rollup fact tables for an explanation of how the generated procedures operate.
This table can be renamed, or a new table created. The table identified as the 'dss_fact_table' must, however, conform to a number of standards if the generated code is to compile and work correctly.
The following columns must exist, where table_name is the new name of the table:
Column name | Purpose |
|---|---|
table_name_key | The artificial key for the table. Must be a numeric data type. |
table_name_name | The name of the fact table. Must be at least a varchar2(64) data type. |
table_name_type | The type of fact table. Must be at least a varchar2(24). |
The generated procedures automatically add rows to this table, as required. As with the dss_source_system table, the table type for this table must be set to a Mapping table to prevent it from being seen as a Dimension in the documentation.
DSS Tables
This option enables you to set the DSS Tables.
Tables
Set the DSS Tables.
DSS Columns
This option enables you to set the DSS Columns.
Columns
Set the DSS Columns.
Fields | Description |
|---|---|
dss_create_time | Column added to all Stage, ODS, EDW 3NF, Dimension, Fact, and Aggregate tables for information purposes only. Leave the field blank to deactivate or add a name for the dss_create_time column, the default is dss_create_time. |
dss_update_time | Column added to all dimension, stage, and fact tables. It is required if the generated code for fact and aggregate tables is to be used. |
dss_start_date | Column used for slowly changing dimensions. It is used to identify when a dimension row was replaced. This is a required field. |
dss_end_date | Column used for slowly changing dimensions. It is used to identify when a dimension row was replaced. This is a required field. |
dss_version | Column used for slowly changing dimensions. Is it used to store the version of a dimension row. This is required for unique constraints. |
dss_current_flag | Column used for slowly changing dimensions. It is used to identify the current dimension row. This is a required field. |
dss_count | Column added to fact tables to provide a simple row counter. This is required but can be deleted after generation. Leave the field blank to deactivate. |
dss_change_hash | Column used to identify the differences in the descriptive columns of a Satellite table which is used for generating the change hash key for a Satellite object. Refer to Creating Data Vault Stage Tables for details. |
dss_load_date | Column used to store the date when the value in this row was loaded into the metadata repository. This column and the dss_record_source column below is added to new Load tables that have the option Add metadata columns to table selected. Refer to Data Vaults for details. |
dss_record_source | Column used to store a descriptive term to identify the source of this record. This column and the dss_load_date column above is added to new load tables that have the option Add meta data columns to table selected. Refer to Data Vaults for details. |
DSS Columns for Custom Targets
These settings are only available/displayed if your WhereScape RED license includes support for custom target databases.
This option enables you to set the DSS Columns for custom database targets.
Data types for Custom Database Targets
Enables you to set the data types that are used in the DSS columns added to new table objects.
- The Hash Key Generation Wizard creates new hub and link hash keys in a Data Vault Stage table with the same data type defined for the dss_change_hash column, if the table is in a Custom database target.
- If the table is not in a Custom database target, the data type CHAR(32) is used.
- Changing this data type has no impact on any existing hash key columns.
Fields | Description |
|---|---|
Default values for Custom Database Targets | Enables you to set the default values that are used when generating update procedures for Slowly Changing Dimension tables and Load tables. |
Start Date for Initial Member | Default date function to use for the start date of rows added in a Slowly Changing Dimension table. |
End Date for Current Member | Default date function to use for the end date of current rows in a Slowly Changing Dimension table. |
Start Date for New Member Entry | Default date function to use for the start date of current rows in a Slowly Changing Dimension table. |
End Date for Expiring Member Entry | Default date function to use for the end date of expiring rows in a Slowly Changing Dimension table. Refer to the Change Detection tab section under Generating the Dimension Update Procedure for details. |
Load Date Transformation | Default date function to use for populating the new records inserted in Load tables. |