../E14406-01.epub /> ../E14406-01.mobi />

OMBRETRIEVE DIMENSION

Purpose

This command retrieves a dimension.

Prerequisites

Should be in Oracle Module context.

Syntax

retrieveDimensionCommand =  OMBRETRIEVE DIMENSION "dimensionName" ( GET ( 
          PROPERTIES "propertyKeyList" | "getReferenceIconSetClause" | 
          DIMENSION_ATTRIBUTES | LEVELS | HIERARCHIES | DIMENSION_ROLES | ( REF 
          | REFERENCE ) SEQUENCE | DIMENSION_KEY COLUMN | SURROGATE_KEY | 
          PARENT_KEY | BUSINESS_KEYS | IMPLEMENTED_OBJECTS ) | 
          "dimensionAttributeDetailClause" | "levelDetailClause" | 
          "hierarchyDetailClause" | "roleDetailClause" )
     dimensionName =  "QUOTED_STRING"
     propertyKeyList =  "(" "propertyKey" { "," "propertyKey" } ")"
     getReferenceIconSetClause =  ( REFERENCE | REF ) ICONSET
     dimensionAttributeDetailClause =  "dimensionAttributeLocator" GET 
          PROPERTIES "propertyKeyList"
     levelDetailClause =  "levelLocator" ( GET ( PROPERTIES "propertyKeyList" | 
          LEVEL_ATTRIBUTES | IMPLEMENTED_OBJECT ) | "levelAttributeDetailClause"
           | "levelRelationshipDetailClause" | 
          "skipLevelRelationshipDetailClause" )
     hierarchyDetailClause =  "hierarchyLocator" GET ( PROPERTIES 
          "propertyKeyList" | ( REF | REFERENCE ) LEVELS | SKIP_LEVELS )
     roleDetailClause =  "roleLocator" ( GET ( PROPERTIES "propertyKeyList" | 
          DIMENSION ) )
     propertyKey =  "UNQUOTED_STRING"
     dimensionAttributeLocator =  DIMENSION_ATTRIBUTE "dimensionAttributeName"
     levelLocator =  LEVEL "levelName"
     levelAttributeDetailClause =  "levelAttributeLocator" GET ( PROPERTIES 
          "propertyKeyList" | ( REF | REFERENCE ) ( DIMENSION_ATTRIBUTE | 
          TYPE_THREE_SCD_PREVIOUS LEVEL_ATTRIBUTE | TYPE_THREE_SCD_CURRENT 
          LEVEL_ATTRIBUTE ) | IMPLEMENTED COLUMN )
     levelRelationshipDetailClause =  LEVEL_RELATIONSHIP IN "hierarchyLocator" 
          GET ( IMPLEMENTED COLUMN | PROPERTIES "propertyKeyList" )
     skipLevelRelationshipDetailClause =  SKIP_LEVEL_RELATIONSHIP IN 
          "hierarchyLocator" GET ( PARENT LEVEL | IMPLEMENTED COLUMN | 
          PROPERTIES "propertyKeyList" )
     hierarchyLocator =  HIERARCHY "hierarchyName"
     roleLocator =  DIMENSION_ROLE "roleName"
     dimensionAttributeName =  "QUOTED_STRING"
     levelName =  "QUOTED_STRING"
     levelAttributeLocator =  LEVEL_ATTRIBUTE "levelAttributeName"
     hierarchyName =  "QUOTED_STRING"
     roleName =  "QUOTED_STRING"
     levelAttributeName =  "QUOTED_STRING"

Parameters

retrieveDimensionCommand

This clause retrieves dimension's information: PROPERTIES, DIMENSION_ATTRIBUTES, LEVELS, HIERARCHIES, DIMENSION_ROLES, SEQUENCE, DIMENSION_KEY, COLUMN, SURROGATE_KEY, PARENT_KEY, BUSINESS_KEYS, IMPLEMENTED_OBJECTS.

dimensionName

The name of the dimension in QUOTED_STRING.

propertyKeyList

The list of properties.

getReferenceIconSetClause

This clause gets the reference to iconsets.

dimensionAttributeDetailClause

This clause gets dimension attribute details.

levelDetailClause

This clause gets the details of levels: PROPERTIES, LEVEL_ATTRIBUTES, IMPLEMENTED_OBJECT.

hierarchyDetailClause

This clause gets the details of hierarchy: PROPERTIES, LEVELS, SKIP_LEVELS.

roleDetailClause

This clause gets the details of a role: PROPERTIES, DIMENSION

propertyKey

The name of the property key in UNQUOTED_STRING

propertyKey

Basic properties for DIMENSION, DIMENSION_ATTRIBUTE, LEVEL, LEVEL_ATTRIBUTE and HIERARCHY: 

Basic properties for DIMENSION :
Name: BUSINESS_NAME
Type: STRING(200)
Valid Values: Any valid character string in supported character set.
Default: Empty string
Business name of the Dimension 

Name: DESCRIPTION
Type: STRING(4000)
Valid Values: Any valid character string in supported character set.
Default: Empty string
Description of the Dimension 

Name: SCD_TYPE
Type: INTEGER
Valid Values: 1, 2, 3
Default: 1 
Slowly changing policy to be applied on the dimension. Give Interger values 1, 2, 3 
for Slowly changing type one, two and three 

Name: LOAD_POLICY_FOR_NULL_KEY
Type: STRING
Valid Values: 'NO_MAINTENANCE', 'NO_MAINTENANCE_WITH_ERROR_TABLE', 'REJECT', 'DEFAULT_PARENT' 
Default: 'NO_MAINTENANCE' 
Data policy for loading dimension where OWB mapping code relies on 
database constraints to detect the orphans (null parent references). 
'NO_MAINTENANCE', OWB does not actively enforce the parentage integrity rule that requires every child record to have a parent. 
'NO_MAINTENANCE_WITH_ERROR_TABLE', Same as NO_MAINTENANCE, and additionally an error table will be deployed, thus orphan management can be switched on at the map level. 
'REJECT', the policy for loading dimension where OWB mapping code will actively detect the orphans (level records without parent) and reject them. 
'DEFAULT_PARENT', the policy for loading dimension where OWB mapping code will actively detect the orphans (level records without parent) and assign them under default parent.

Name: LOAD_POLICY_FOR_INVALID_KEY
Type: STRING
Valid Values: 'NO_MAINTENANCE', 'NO_MAINTENANCE_WITH_ERROR_TABLE', 'REJECT', 'DEFAULT_PARENT' 
Default: 'NO_MAINTENANCE' 
Data policy for loading dimension where OWB mapping code relies on 
database constraints to detect the orphans (could contain a value that is not actually found in the parent). 
'NO_MAINTENANCE', OWB does not actively enforce the parentage integrity rule that requires every child record to have a parent. 
'NO_MAINTENANCE_WITH_ERROR_TABLE', Same as NO_MAINTENANCE, and additionally an error table will be deployed, thus orphan management can be switched on at the map level. 
'REJECT', the policy for loading dimension where OWB mapping code will actively detect the orphans (level records without parent) and reject them. 
'DEFAULT_PARENT', the policy for loading dimension where OWB mapping code will actively detect the orphans (level records without parent) and assign them under default parent.

Name: REMOVE_POLICY
Type: STRING
Valid Values: 'NO_MAINTENANCE', 'REJECT' 
Default: 'NO_MAINTENANCE' 
Data policy for remove dimension data where OWB relies on database 
constraints to detect removal of parent records who still have child records.  
'NO_MAINTENANCE', does not actively enforce the parentage integrity rule that requires 
every child record to have a parent. 
'NO_MAINTENANCE_WITH_ERROR_TABLE', Same as NO_MAINTENANCE, and additionally an error table will be deployed, thus orphan management can be switched on at the map level. 
'REJECT', data policy for loading dimension where OWB mapping code will actively 
reject the removal of parent records who still have child records. 
#'CASCADE', data policy for removing dimension data where OWB mapping code will  
#actively detect the removal of parent records who still have child records,  
#and remove all of the descendents.

propertyKey

Name: TYPE
Type: STRING
Valid Values: NONE, TIME
Default: NONE 
'NONE'  it does not recognize it as any specific type of dimension. 
'TIME' dimension is a time dimension 

Name: OLAP_TYPE
Type: STRING
Valid Values: NONE, TIME 
Default: NONE 
Dimension type for OLAP, set regular dimension 'NONE' and for OLAP Time Dimension 'TIME' 

Name: OLAP_USER_VISIBLE
Type: STRING
Valid Values: true, false 
Default: true 
The dimension is visible to OLAP end user 

Name: UNIQUE_KEY_CONSTRAINT
Type: STRING
Valid Values: true, false 
Default: false 
set the Unique Key constraint on the Business Key 

Name: STORAGE
Type: STRING
Valid Values: RELATIONAL, AW 
Default: RELATIONAL 
The storage of a dimension can be AW or relational 

Name: AW_NAME
Type: STRING(32)
Valid Values: Any valid character string in supported character set.
Default: Empty string
Set the analytical workspace name where the dimension is implemented 

Name: AW_DIMENSION_NAME
Type: STRING(32)
Valid Values: Any valid character string in supported character set.
Default: Empty string
Set the Analytical Workspace dimension physical object name 

Name: USE_BUSINESS_KEYS
Type: STRING
Valid Values: true, false 
Default: false 
get the flag of Analytical Workspace dimension to use Business Keys as data source

propertyKey

Basic properties for DIMENSION_ATTRIBUTE:
Name: BUSINESS_NAME
Type: STRING(200)
Valid Values: Any valid character string in supported character set.
Default: Empty string
Business name of the Dimension_Attribute 

Name: DESCRIPTION
Type: STRING(4000)
Valid Values: Any valid character string in supported character set.
Default: Empty string
Description of the Dimension_Attribute

Name: DATATYPE
Type: STRING
Valid Values: BINARY_DOUBLE, BINARY_FLOAT, BLOB, CHAR, CLOB, DATE, FLOAT, INTEGER, INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH, NCHAR, NCLOB, NUMBER, NVARCHAR2, RAW, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, VARCHAR, VARCHAR2
Default: NUMBER 
Sets the datatype of a Dimension_Attribute 

Name: SCALE
Type: NUMBER
Valid Values: -85 - 125
Default: 1
The scale of a number.

Name: LENGTH
Type: NUMBER
Valid Values: 
Default: 1
The length of a number 

Name: PRECISION
Type: NUMBER
Valid Values: 0 - 39
Default: 1
The precision of a number. 

Name: DESCRIPTOR
Type: STRING
Valid Values: 'NONE', 'SHORT_DESCRIPTION', 'LONG_DESCRIPTION', 'END_DATE', 'TIME_SPAN', 'PRIOR_PERIOD', 'YEAR_AGO_PERIOD' 
Default: 'NONE'
The following properties are set on dimension attribute so that it is recognized by OLAP service 
'NONE' it is not specially recognized type by OLAP service 
'SHORT_DESCRIPTION' sets as a short description  
'LONG_DESCRIPTION' sets as a long description  
'END_DATE' sets as a last date of a period. 
'TIME_SPAN' sets as a number of days in a period. 
'PRIOR_PERIOD' sets as the prior period number. 
'YEAR_AGO_PERIOD' sets as the time period a year before this period

propertyKey

Name: OLAP_USER_VISIBLE
Type: STRING
Valid Values: 'true', 'false' 
Default: 'true' 
The dimension attribute is visible to OLAP end user 

Name: TYPE
Type: STRING
Valid Values: 'NONE', 'START_DATE', 'END_DATE', 'TIME_SPAN'
Default: 'NONE' 
'NONE'  dimension attribute so OWB does not recognize it as any specific type. 
'START_DATE' dimension attribute of time dimension as the start date of a period 
'END_DATE' dimension attribute of time dimension as the end date of a period 
'TIME_SPAN' dimension attribute of time dimension as the time span  

Name: AW_ATTRIBUTE_NAME
Type: STRING(32)
Valid Values: Any valid character string in supported character set.
Default: Empty string
set the AW object name implementing the dimension attribute 
Basic properties for Level :
Name: BUSINESS_NAME
Type: STRING(200)
Valid Values: Any valid character string in supported character set.
Default: Empty string
Business name of the Level

Name: DESCRIPTION
Type: STRING(4000)
Valid Values: Any valid character string in supported character set.
Default: Empty string
Description of the Level

Name: TYPE
Type: STRING
Valid Values: NONE, DAY, FISCAL_WEEK, FISCAL_MONTH, FISCAL_QUARTER, FISCAL_YEAR, CALENDAR_WEEK, CALENDAR_MONTH, CALENDAR_QUARTER, CALENDAR_YEAR 
Default: NONE 
For regular relational dimension level (non-time dimension level) use 'NONE'. For Relational Time Dimension use other values. 

Name: OLAP_TYPE
Type: STRING
Valid Values: NONE,DAY,MONTH,QUARTER,YEAR,TOTAL 
Default: NONE 
Level has an olap-type for OLAP-based levels, use 'NONE' for regular levels, and other values for OLAP Time Dimension.

Name: OLAP_USER_VISIBLE
Type: STRING
Valid Values: true, false
Default: true 
The level is visible to OLAP end user

propertyKey

coreGetPropertiesForDimension4??

propertyKey

coreGetPropertiesForDimension5??

dimensionAttributeLocator

This clause locates a DIMENSION_ATTRIBUTE.

levelLocator

This clause finds a LEVEL.

levelAttributeDetailClause

This clause gets the details of level attributes: PROPERTIES, DIMENSION_ATTRIBUTE, TYPE_THREE_SCD_PREVIOUS, LEVEL_ATTRIBUTE, TYPE_THREE_SCD_CURRENT, IMPLEMENTED, COLUMN

levelRelationshipDetailClause

This clause gets details of LEVEL_RELATIONSHIP, IMPLEMENTED, COLUMN, PROPERTIES.

skipLevelRelationshipDetailClause

This clause gets the details of skip level relationship: PARENT, LEVEL, IMPLEMENTED, COLUMN, PROPERTIES

hierarchyLocator

This clause finds a HIERARCHY.

roleLocator

This clause finds a DIMENSION_ROLE.

dimensionAttributeName

The name of the dimension attribute in QUOTED_STRING.

levelName

The name of a level in QUOTED_STRING.

levelAttributeLocator

This clause finds the LEVEL_ATTRIBUTE.

hierarchyName

The name of the hierarchy as a QUOTED_STRING.

roleName

The name of the role as a QUOTED_STRING.

levelAttributeName

The name of the level attribute as a QUOTED_STRING.

DIMENSION Object

Table 18-11 DIMENSION Object

Property Type Choices Min Max Default Description

DEPLOYABLE

BOOLEAN

true, false

none

none

true

Warehouse Builder generates a set of scripts to create an object only for those object marked as Deployable = true

DEPLOYMENT_OPTIONS

STRING

DEPLOY_ALL, DEPLOY_DATA_OBJECTS_ONLY, DEPLOY_TO_CATALOG_ONLY

none

none

DEPLOY_DATA_OBJECTS_ONLY

Warehouse Builder generates a set of scripts for Dimension, they are DDL Scripts for Relational Dimensional or Scripts for ROLAP or or Scripts for AW.

ENABLE_MV_REFRESH

BOOLEAN

true, false

none

none

false

Enable MV Refresh

GENERATION_COMMENTS

STRING

none

none

none

empty string

Enter additional comments for the generated code.

MVCONSTRAINTS

STRING

, ENFORCED, TRUSTED

none

none

TRUSTED

MV Constraints

NEXTDATE

STRING

none

none

none

empty string

Refresh Next Date

REFRESH

STRING

, COMPLETE, FAST, FORCE

none

none

FORCE

Refresh Mode

REFRESH_ON

STRING

, DEMAND, ONDATE

none

none

DEMAND

Refresh On

STARTWITH

STRING

none

none

none

empty string

Refresh Start Date

VIEW_NAME

STRING(30)

none

none

none

empty string

Name of the view that is generated to hide the control rows on the dimension implementation table of a star schema. If this field is left blank, the view name will default to '<Name of Dimension>_v'

VISIBLE

BOOLEAN

true, false

none

none

true

The Dimension is visible to OLAP end user if value is set = true.