Data Store Objects in WhereScape RED do not have an artificial (surrogate) key, by default.
Artificial keys can be added manually but if needed could indicate Data Store Objects are not the correct WhereScape RED object for this table (perhaps an EDW 3NF Table would be more appropriate).
Edit the properties of the new column to have the correct name and order, source table and column, data type, key type, and flags.
Specifically:
- Right-click a column in the middle pane and select either Add Column or Duplicate Column from the context menu.
- Edit the properties of the new column to have the correct name and order, source table and column, data type, key type, and flags as below:
- The Column Name and Source Column must be the same.
- The Source Table must be empty.
- The Data Type should be:
- DB2: integer generated by default as identity (start with 1, increment by 1)
- SQL Server: integer identity(0,1)
- The Key Type must be 0.
- Only the Numeric and Artificial Key options should be enabled.
The following example shows a manually added artificial key column:
A WhereScape standard for the creation of special rows in the Data Store tables is as follows:
Key value | Usage |
|---|---|
1 upwards | The standard artificial keys are numbered from 1 upwards, with a new number assigned for each distinct Data Store Object record. |
0 | Used as a join to the Data Store Object when no valid join existed. It is the convention in the WhereScape-generated code that any EDW 3NF table business key that either does not exist or does not match is assigned to key 0. |
-1 through -9 | Used for special cases. The most common is where an EDW 3NF table is not appropriate for the record. A new key is used rather than 0 as we want to distinguish between records that are invalid and not appropriate. |
-10 backward | Pseudo records. In many cases, we have to deal with different granularities in our data. For example, we may have a table that contains actual sales at a product SKU level and budget information at a product group level. The product table only contains SKU based information. To be able to map the budget records to the same table, we need to create these pseudo keys that relate to product groups. The values -10 and backward are normally used for such keys. |
To have a surrogate key auto-added for Data Store tables, refer to Global Naming of Key Columns.