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

OMBCREATE TIME_DIMENSION

Purpose

This command creates a time dimension.

Prerequisites

Should be in Oracle Module context.

Syntax

createTimeDimensionCommand =  OMBCREATE TIME_DIMENSION "TimeDimensionName" 
          ( ( "setFiscalPropertyClause" "addFiscalCalendarHierarchyClause"+ | 
          "addCalendarHierarchyClause"+ [ "setFiscalPropertyClause" 
          "addFiscalCalendarHierarchyClause"+ ] ) | ( "setPropertiesClause" ( 
          "setFiscalPropertyClause" "addFiscalCalendarHierarchyClause"+ | ( 
          "addCalendarHierarchyClause"+ [ "setFiscalPropertyClause" 
          "addFiscalCalendarHierarchyClause"+ ] ) ) ) ) { 
          "addDimensionRoleClause" } [ "implementationClause" [ 
          "addSequenceClause" ] ] "populationClause"
     TimeDimensionName =  "QUOTED_STRING"
     setFiscalPropertyClause =  "setFiscalPropertiesClause"
     addFiscalCalendarHierarchyClause =  ADD FISCAL_CALENDAR HIERARCHY 
          "hierarchyName" [ "setPropertiesClause" ] SET ( REF | REFERENCE ) 
          "fiscalCalendarLevelList"
     addCalendarHierarchyClause =  ADD ( ( NORMAL_CALENDAR HIERARCHY 
          "hierarchyName" [ "setPropertiesClause" ] SET ( REF | REFERENCE ) 
          "normalCalendarLevelList" ) | ( WEEK_CALENDAR HIERARCHY 
          "hierarchyName" [ "setPropertiesClause" ] SET ( REF | REFERENCE ) 
          "weekCalendarLevelList" ) )
     setPropertiesClause =  SET PROPERTIES "propertyKeyList" VALUES 
          "propertyValueList"
     addDimensionRoleClause =  ADD DIMENSION_ROLE "roleName" [ 
          "setPropertiesClause" ]
     implementationClause =  IMPLEMENTED BY ( STAR | SNOWFLAKE ) [ USING 
          COMPOSITE_UNIQUE_KEY ]
     addSequenceClause =  ( ( ADD ( REF | REFERENCE ) SEQUENCE "QUOTED_STRING" )
           | ( SET ( REF | REFERENCE ) SEQUENCE "QUOTED_STRING" ) )
     populationClause =  POPULATE DATA FROM "calendarYear" FOR "yearCount" YEARS
     setFiscalPropertiesClause =  SET FISCAL_CALENDAR PROPERTIES 
          "propertyKeyList" VALUES "propertyValueList"
     hierarchyName =  "QUOTED_STRING"
     fiscalCalendarLevelList =  "(" ( "fiscalCalendarLevelType" LEVEL [ 
          "levelName" ] [ "setPropertiesClause" ] ) { "," 
          "fiscalCalendarLevelType" LEVEL [ "levelName" ] [ 
          "setPropertiesClause" ] } ")"
     normalCalendarLevelList =  "(" ( "normalCalendarLevelType" LEVEL [ 
          "levelName" ] [ "setPropertiesClause" ] ) { "," 
          "normalCalendarLevelType" LEVEL [ "levelName" ] [ 
          "setPropertiesClause" ] } ")"
     weekCalendarLevelList =  "(" ( "weekCalendarLevelType" LEVEL [ "levelName" 
          ] [ "setPropertiesClause" ] ) { "," "weekCalendarLevelType" LEVEL [ 
          "levelName" ] [ "setPropertiesClause" ] } ")"
     propertyKeyList =  "(" "propertyKey" { "," "propertyKey" } ")"
     propertyValueList =  "(" "propertyValue" { "," "propertyValue" } ")"
     roleName =  "QUOTED_STRING"
     calendarYear =  "INTEGER_LITERAL"
     yearCount =  "INTEGER_LITERAL"
     fiscalCalendarLevelType =  ( DAY | FISCAL_WEEK | FISCAL_MONTH | 
          FISCAL_QUARTER | FISCAL_YEAR )
     levelName =  "QUOTED_STRING"
     normalCalendarLevelType =  ( DAY | CALENDAR_MONTH | CALENDAR_QUARTER | 
          CALENDAR_YEAR )
     weekCalendarLevelType =  ( DAY | CALENDAR_WEEK )
     propertyKey =  "UNQUOTED_STRING"
     propertyValue =  ( "QUOTED_STRING" | "INTEGER_LITERAL" | 
          "FLOATING_POINT_LITERAL" )

Parameters

createTimeDimensionCommand

This clause creates a time dimension.

TimeDimensionName

The name of the time dimension.

setFiscalPropertyClause

set the fiscal properties.

addFiscalCalendarHierarchyClause

This clause adds an fiscal hierarchy to the time dimension.

addCalendarHierarchyClause

This clause adds a new hierarchy to the time dimension by: renaming the hierarchy, setting of hierarchy properties, or setting level references.

setPropertiesClause

This clause sets the following properties:

Name: BUSINESS_NAME

Type: STRING(200)

Valid Values: Any valid character string in supported character set.

Default: Empty string

Name: DESCRIPTION

Type: STRING(4000)

Valid Values: Any valid character string in supported character set.

Default: Empty string

addDimensionRoleClause

This clause adds a dimension role.

implementationClause

Time Dimension is implemented as STAR or as SNOWFLAKE.

addSequenceClause

Either create a new sequence and use it in the time dimension using ADD REF SEQUENCE ... statement, otherwise use SET REF SEQUENCE ... statement use to an existing sequence.

populationClause

This clause specifies the starting year and the number of years for which data will be populated.

setFiscalPropertiesClause

This clause sets the following properties:

Fiscal types allowed in OWB time dimension. Name: FISCAL_TYPE

Type: STRING

Valid Values: '544', '445'

Default: '544'

Fiscal calendar year start date, it could be any date of a year.

Name: FISCAL_CALENDAR_START_YEAR

Type: STRING

Valid Values: Dates in these format 'DD-MON-YYYY' or 'DD-MM-YYYY'

Default: '01-JAN-2000'

The day of the week when the fiscal year begins.

Name: FISCAL_CALENDAR_START_DAY_OF_WEEK

Type: STRING

Valid Values: 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY', 'SUNDAY'

Default: 'false'

hierarchyName

The name of a hierarchy.

fiscalCalendarLevelList

This clause creates a fiscal hierarchy and sets reference fiscal levels.

normalCalendarLevelList

This clause defines Levels used in Normal Calendar.

weekCalendarLevelList

This clause defines a week Calendar.

propertyKeyList

A list of time dimension properties.

propertyValueList

A list of time dimension property values.

roleName

A role name.

calendarYear

The Calendar year given by INTEGER_LITERAL.

yearCount

The year could is given by INTEGER_LITERAL.

fiscalCalendarLevelType

The types of Fiscal Calendar: DAY, FISCAL_WEEK, FISCAL_MONTH, FISCAL_QUARTER, FISCAL_YEAR

levelName

User defined Level name.

normalCalendarLevelType

The types of Normal Calendar Year: DAY, CALENDAR_MONTH, CALENDAR_QUARTER, CALENDAR_YEAR

weekCalendarLevelType

Type of Week Calendar: DAY, CALENDAR_WEEK

propertyKey

Property key in UNQUOTED_STRING

propertyKey

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

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

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

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 


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

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

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

propertyValue

Property key value in QUOTED_STRING, INTEGER_LITERAL, FLOATING_POINT_LITERAL.

DIMENSION Object

Table 9-108 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.


HIERARCHY Object

Table 9-109 HIERARCHY Object

Property Type Choices Min Max Default Description

HIERLOADLEVEL

STRING

none

none

none

empty string