User Defined Dimensions
A user defined dimension (UDD) is a means of extension for an existing fact. These dimensions have a foreign key reference to a fact; however no out-of-box ETL is delivered. Customers can define an ODI ETL process to populate these dimensions with the source data as per their requirement. Each fact is provided with two such user defined dimensions. For details about extending using UDDs, see the Oracle Utilities Analytics Warehouse Developer’s Guide.
All user defined dimensions have the same structure. The only technical difference is the column name of the primary key, which follows the pattern of using the fact for which it is linked to.
Below is the structure of the Fields table which is common for all user defined dimensions.
Fields
Target Field
Description
Surrogate Key
<Fact_name_prefix>_UDDn_KEY
 
For example: The surrogate key for Asset Location UDD is ASSET_LOC_UDD1_KEY
<Fact name> User Defined Dimension n Dimension Surrogate Key
 
For example: Asset Location User Defined Dimension 1 Dimension Surrogate Key
Natural Keys
UDD1_CD
User Defined Dimension 1 Code
Attributes
UDD1_DESCR
User Defined Dimension 1 Description
User Defined Attributes
UDF1_CD
User Defined Field 1 Code
UDF1_DESCR
User Defined Field 1 Description
UDF2_CD
User Defined Field 2 Code
UDF2_DESCR
User Defined Field 2 Description
UDF3_CD
User Defined Field 3 Code
UDF3_DESCR
User Defined Field 3 Description
UDF4_CD
User Defined Field 4 Code
UDF4_DESCR
User Defined Field 4 Description
UDF5_CD
User Defined Field 5 Code
UDF5_DESCR
User Defined Field 5 Description
UDF6_CD
User Defined Field 6 Code
UDF6_DESCR
User Defined Field 6 Description
UDF7_CD
User Defined Field 7 Code
UDF7_DESCR
User Defined Field 7 Description
UDF8_CD
User Defined Field 8 Code
UDF8_DESCR
User Defined Field 8 Description
UDF9_CD
User Defined Field 9 Code
UDF9_DESCR
User Defined Field 9 Description
UDF10_CD
User Defined Field 10 Code
UDF10_DESCR
User Defined Field 10 Description
UDF11_CD
User Defined Field 11 Code
UDF11_DESCR
User Defined Field 11 Description
UDF12_CD
User Defined Field 12 Code
UDF12_DESCR
User Defined Field 12 Description
UDF13_CD
User Defined Field 13 Code
UDF13_DESCR
User Defined Field 13 Description
UDF14_CD
User Defined Field 14 Code
UDF14_DESCR
User Defined Field 14 Description
UDF15_CD
User Defined Field 15 Code
UDF15_DESCR
User Defined Field 15 Description
UDF16_CD
User Defined Field 16 Code
UDF16_DESCR
User Defined Field 16 Description
UDF17_CD
User Defined Field 17 Code
UDF17_DESCR
User Defined Field 17 Description
UDF18_CD
User Defined Field 18 Code
UDF18_DESCR
User Defined Field 18 Description
UDF19_CD
User Defined Field 19 Code
UDF19_DESCR
User Defined Field 19 Description
UDF20_CD
User Defined Field 20 Code
UDF20_DESCR
User Defined Field 20 Description
UDF21_CD
User Defined Field 21 Code
UDF21_DESCR
User Defined Field 21 Description
UDF22_CD
User Defined Field 22 Code
UDF22_DESCR
User Defined Field 22 Description
UDF23_CD
User Defined Field 23 Code
UDF23_DESCR
User Defined Field 23 Description
UDF24_CD
User Defined Field 24 Code
UDF24_DESCR
User Defined Field 24 Description
UDF25_CD
User Defined Field 25 Code
UDF25_DESCR
User Defined Field 25 Description
UDF26_CD
User Defined Field 26 Code
UDF26_DESCR
User Defined Field 26 Description
UDF27_CD
User Defined Field 27 Code
UDF27_DESCR
User Defined Field 27 Description
UDF28_CD
User Defined Field 28 Code
UDF28_DESCR
User Defined Field 28 Description
UDF29_CD
User Defined Field 29 Code
UDF29_DESCR
User Defined Field 29 Description
UDF30_CD
User Defined Field 30 Code
UDF30_DESCR
User Defined Field 30 Description
UDF31_CD
User Defined Field 31 Code
UDF31_DESCR
User Defined Field 31 Description
UDF32_CD
User Defined Field 32 Code
UDF32_DESCR
User Defined Field 32 Description
UDF33_CD
User Defined Field 33 Code
UDF33_DESCR
User Defined Field 33 Description
UDF34_CD
User Defined Field 34 Code
UDF34_DESCR
User Defined Field 34 Description
UDF35_CD
User Defined Field 35 Code
UDF35_DESCR
User Defined Field 35 Description
UDF36_CD
User Defined Field 36 Code
UDF36_DESCR
User Defined Field 36 Description
UDF37_CD
User Defined Field 37 Code
UDF37_DESCR
User Defined Field 37 Description
UDF38_CD
User Defined Field 38 Code
UDF38_DESCR
User Defined Field 38 Description
UDF39_CD
User Defined Field 39 Code
UDF39_DESCR
User Defined Field 39 Description
UDF40_CD
User Defined Field 40 Code
UDF40_DESCR
User Defined Field 40 Description
UDF41_CD
User Defined Field 41 Code
UDF41_DESCR
User Defined Field 41 Description
UDF42_CD
User Defined Field 42 Code
UDF42_DESCR
User Defined Field 42 Description
UDF43_CD
User Defined Field 43 Code
UDF43_DESCR
User Defined Field 43 Description
UDF44_CD
User Defined Field 44 Code
UDF44_DESCR
User Defined Field 44 Description
UDF45_CD
User Defined Field 45 Code
UDF45_DESCR
User Defined Field 45 Description
UDF46_CD
User Defined Field 46 Code
UDF46_DESCR
User Defined Field 46 Description
UDF47_CD
User Defined Field 47 Code
UDF47_DESCR
User Defined Field 47 Description
UDF48_CD
User Defined Field 48 Code
UDF48_DESCR
User Defined Field 48 Description
UDF49_CD
User Defined Field 49 Code
UDF49_DESCR
User Defined Field 49 Description
UDF50_CD
User Defined Field 50 Code
UDF50_DESCR
User Defined Field 50 Description
COMMENT1
Comment 1
COMMENT2
Comment 2
COMMENT3
Comment 3
COMMENT4
Comment 4
COMMENT5
Comment 5
Data Load Attributes
EFF_START_DTTM
Effective Start Date/Time
EFF_END_DTTM
Effective End Date/Time
DATA_LOAD_DTTM
Data Load Date/Time
DATA_SOURCE_IND
Data Source Indicator
JOB_NBR
Job Number
 
Asset Location UDD1
Property
Value
Target Table
CD_ASSET_LOC_UDD1
Table Type
Dimension
SCD Type
Type 2
 
Asset Location UDD2
Property
Value
Target Table
CD_ASSET_LOC_UDD2
Table Type
Dimension
SCD Type
Type 2
 
Operational Device UDD1
Property
Value
Target Table
CD_OPR_DEVICE_UDD1
Table Type
Dimension
SCD Type
Type 2
 
Operational Device UDD2
Property
Value
Target Table
CD_OPR_DEVICE_UDD2
Table Type
Dimension
SCD Type
Type 2
 
Operational Device Snapshot UDD1
Property
Value
Target Table
CD_OPR_DEVICE_SNAP_UDD1
Table Type
Dimension
SCD Type
Type 2
 
Operational Device Snapshot UDD2
Property
Value
Target Table
CD_OPR_DEVICE_SNAP_UDD2
Table Type
Dimension
SCD Type
Type 2
 
Service History UDD1
Property
Value
Target Table
CD_SERVICE_HIST_UDD1
Table Type
Dimension
SCD Type
Type 2
 
Service History UDD2
Property
Value
Target Table
CD_SERVICE_HIST_UDD2
Table Type
Dimension
SCD Type
Type 2