3.2.1.1 Key Dimension Tables
Each key dimension contains a set of the following tables:
- DIM_<DIMENSION>_B: Stores leaf and node member codes within the dimension.
- DIM_<DIMENSION>_TL: Stores names of leaf and node and their translations.
- DIM_<DIMENSION>_ATTR: Stores attribute values for the attributes of the dimension.
- DIM_<DIMENSION>_HIER: Stores parent-child relationship of members and nodes that are part of hierarchies.
Note:
Replace <DIMENSION> with the keyword representing the key dimension.Seeded key dimension tables are present in 'ALM-FTP-PFT-HM-BSP – Dimensions' subject area within the ERwin model. The above tables need to be created for the new dimension. For more information on creating dimension tables in ERwin, see leaflet (Adding And Customizing Leaf.pdf).
Note:
For ease of use, user can copy an existing set of dimension tables such as for ORG_UNIT dimension and rename the tables (in both physical and logical view) to represent the new dimension.Table structure of one of the seeded key dimension is given following with remarks on how this can be used as the basis for modeling new key dimensions.
DIM_ORG_UNIT_ATTR
Stores the values of the attributes of the members (leaf and nodes) of the dimension.
| Column Name | Logical Column Name | Datatype | NULL | Column Description | Remarks |
| ORG_UNIT_ID | Organization Unit ID | NUMBER(14) | NOT NULL | Leaf column which stores the id for the organization unit dimension | Column name and description should reflect the new dimension. Datatype and other constraints should be retained. |
| ORG_UNIT_DISPLAY_CODE | Organization Unit Display Code | NUMBER(14) | NULL | Leaf column which stores the display code for the organization unit dimension | Column name and description should reflect the new dimension. Datatype and other constraints should be retained. |
| ENABLED_FLAG | Enabled Flag | VARCHAR2(1) | NOT NULL | Store if the item is enabled or not | Internally used and hence should be retained in the same form within the new dimension table. |
| LEAF_ONLY_FLAG | Leaf or Node Flag | VARCHAR2(1) | NOT NULL | Indicates if the member is leaf only or not | Internally used and hence should be retained in the same form within the new dimension table. |
| DEFINITION_LANGUAGE | Definition Language | VARCHAR2(4) | NOT NULL | Language that is used to define | Internally used and hence should be retained in the same form within the new dimension table. |
| CREATED_BY | Created By | VARCHAR2(30) | NOT NULL | Indicates who created this item | Internally used and hence should be retained in the same form within the new dimension table. |
| CREATION_DATE | Creation Date | TIMESTAMP | NOT NULL | Indicates when was this item created | Internally used and hence should be retained in the same form within the new dimension table. |
| LAST_MODIFIED_BY | Last Modified By | VARCHAR2(30) | NOT NULL | Indicates who modified this item | Internally used and hence should be retained in the same form within the new dimension table. |
| LAST_MODIFIED_DATE | Last Modified Date | TIMESTAMP | NOT NULL | Indicates when was this item modified | Internally used and hence should be retained in the same form within the new dimension table. |
| ORG_UNIT_CODE | ORG_UNIT_CODE | VARCHAR2(20) | NULL | This column is used by staging and contains the alpha-numeric codes for each dimension member. Staging dimension table contains unique alpha-numeric codes and a unique numeric identifier is generated while loading into ALM-FTP-PFT-HM-BSP dimension table. | Column name and description should reflect the new dimension. Datatype and other constraints should be retained. |
DIM_ORG_UNIT_TL
Stores the names and descriptions of the members (leaf and nodes) of the dimension in various languages.
| Column Name | Logical Column Name | Datatype | NULL | Column Description | Remarks |
| LANGUAGE | Language | VARCHAR2(4) | NOT NULL | Language | Internally used and hence should be retained in the same form within the new dimension table. |
| ORG_UNIT_ID | Organization Unit ID | NUMBER(14) | NOT NULL | Leaf column which stores the id for the organization unit dimension | Column name and description should reflect the new dimension. Datatype and other constraints should be retained. |
| ORG_UNIT_NAME | Organization Unit Name | VARCHAR2(150) | NOT NULL | Leaf column which stores the name for the organization unit dimension | Column name and description should reflect the new dimension. Datatype and other constraints should be retained. |
| DESCRIPTION | Description | VARCHAR2(255) | NULL | Description of an Item | Internally used and hence should be retained in the same form within the new dimension table. |
| CREATED_BY | Created By | VARCHAR2(30) | NOT NULL | Indicates who created this item | Internally used and hence should be retained in the same form within the new dimension table. |
| CREATION_DATE | Creation Date | TIMESTAMP | NOT NULL | Indicates when was this item created | Internally used and hence should be retained in the same form within the new dimension table. |
| LAST_MODIFIED_BY | Last Modified By | VARCHAR2(30) | NOT NULL | Indicates who modified this item | Internally used and hence should be retained in the same form within the new dimension table. |
| LAST_MODIFIED_DATE | Last Modified Date | TIMESTAMP | NOT NULL | Indicates when was this item modified | Internally used and hence should be retained in the same form within the new dimension table. |
DIM_ORG_UNIT_ATTR
Stores the values of the attributes of the members (leaf and nodes) of the dimension.
| Column Name | Logical Column Name | Datatype | NULL | Column Description | Remarks |
| ORG_UNIT_ID | Organization Unit ID | NUMBER(14) | NOT NULL | Leaf column which stores the id for the organization unit dimension | Column name and description should reflect the new dimension. Datatype and other constraints should be retained. |
| ATTRIBUTE_ID | Attribute ID | NUMBER(22) | NOT NULL | Stores attribute id number for a member of a dimension | Internally used and hence should be retained in the same form within the new dimension table. |
| DIM_ATTRIBUTE_NUMERIC_MEMBER | Numeric Dimension Value | NUMBER(22) | NULL | This field stores the number values for the attribute of a member | Internally used and hence should be retained in the same form within the new dimension table. |
| DIM_ATTRIBUTE_VARCHAR_MEMBER | Varchar Dimension Value | VARCHAR2(30) | NULL | This field stores the varchar values for the attribute of a member | Internally used and hence should be retained in the same form within the new dimension table. |
| NUMBER_ASSIGN_VALUE | Numeric Value Of A Member | NUMBER(22) | NULL | This field stores the number values for the attribute of a member | Internally used and hence should be retained in the same form within the new dimension table. |
| VARCHAR_ASSIGN_VALUE | Varchar Member Value | VARCHAR2(1000) | NULL | This field stores the varchar values for the attribute of a member | Internally used and hence should be retained in the same form within the new dimension table. |
| DATE_ASSIGN_VALUE | Date Value | DATE | NULL | Date value that is assigned | Internally used and hence should be retained in the same form within the new dimension table. |
DIM_ORG_UNIT_HIER
Stores the parent-child relationship of various nodes and leaf within hierarchies of the dimension.
| Column Name | Logical Column Name | Datatype | NULL | Column Description | Remarks |
| HIERARCHY_ID | Hierarchy ID | NUMBER(10) | NOT NULL | Unique Id that is generated for every hierarchy that is created | Internally used and hence should be retained in the same form within the new dimension table. |
| PARENT_ID | Parent ID | NUMBER(14) | NOT NULL | Column that store the id of the child member | Internally used and hence should be retained in the same form within the new dimension table. |
| CHILD_ID | Child Member ID | NUMBER(14) | NOT NULL | Store child id number for a dimension | Internally used and hence should be retained in the same form within the new dimension table. |
| PARENT_DEPTH_NUM | Parent Depth Number | NUMBER(14) | NOT NULL | Stores parent depth number | Internally used and hence should be retained in the same form within the new dimension table. |
| CHILD_DEPTH_NUM | Child Depth Number | NUMBER(14) | NOT NULL | Stores child depth number | Internally used and hence should be retained in the same form within the new dimension table. |
| DISPLAY_ORDER_NUM | Display Order Number | NUMBER(14) | NOT NULL | Stores the display order number for the member | Internally used and hence should be retained in the same form within the new dimension table. |
| SINGLE_DEPTH_FLAG | Single Depth Flag | VARCHAR2(1) | NOT NULL | Indicates if the hierarchy is of single depth or not | Internally used and hence should be retained in the same form within the new dimension table. |
| CREATED_BY | Created By | VARCHAR2(30) | NOT NULL | Indicates who created this item | Internally used and hence should be retained in the same form within the new dimension table. |
| CREATION_DATE | Creation Date | TIMESTAMP | NOT NULL | Indicates when was this item created | Internally used and hence should be retained in the same form within the new dimension table. |
| LAST_MODIFIED_BY | Last Modified By | VARCHAR2(30) | NOT NULL | Indicates who modified this item | Internally used and hence should be retained in the same form within the new dimension table. |
| LAST_MODIFIED_DATE | Last Modified Date | TIMESTAMP | NOT NULL | Indicates when was this item modified | Internally used and hence should be retained in the same form within the new dimension table. |