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 rollup fact tables to identify the source fact table that contributed 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.
Note
When using table names other than the defaults for dss_source_system and dss_fact_table it is worth considering the fact that 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 own 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 set 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 Mapping table to prevent it from being seen as a Dimension in the documentation.