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

OMBCREATE TIME_DIMENSION

Purpose

Prerequisites

Syntax

Parameters

createTimeDimensionCommand

TimeDimensionName

setFiscalPropertyClause

addFiscalCalendarHierarchyClause

addCalendarHierarchyClause

setPropertiesClause

addDimensionRoleClause

implementationClause

addSequenceClause

populationClause

setFiscalPropertiesClause

hierarchyName

fiscalCalendarLevelList

normalCalendarLevelList

weekCalendarLevelList

propertyKeyList

propertyValueList

roleName

calendarYear

yearCount

fiscalCalendarLevelType

levelName

normalCalendarLevelType

weekCalendarLevelType

propertyKey

propertyKey

propertyValue

DIMENSION Object

HIERARCHY Object

See Also