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

OMBCREATE LOGICAL_TABLE

Purpose

Creates a logical table that can be used in a business view.

Prerequisites

Should be in the context of an OBIEE Business Definition Module.

Syntax

createLogicalTableCommand =  ( OMBCREATE LOGICAL_TABLE "QUOTED_STRING" [ 
          SET "setpropertiesClauseDelayed" ] [ SET "setReferenceIconSetClause" ]
           { "addLogicalTableSCOClauses" } )
     setpropertiesClauseDelayed =  PROPERTIES "(" "propertyNameListVector" ")" 
          VALUES "(" "propertyValueListVector" ")"
     setReferenceIconSetClause =  ( REFERENCE | REF ) ICONSET "QUOTED_STRING"
     addLogicalTableSCOClauses =  ADD ( "addItemClause" | "addJoinClause" )
     propertyNameListVector =  "UNQUOTED_STRING" { "," "UNQUOTED_STRING" }
     propertyValueListVector =  "propertyValue" { "," "propertyValue" }
     addItemClause =  ITEM "QUOTED_STRING" [ SET "setPropertiesClause" ] [ SET (
           REF | REFERENCE ) ( "ItemItemReferencesClause" | 
          "ItemListOfValuesReferencesClause" | 
          "ItemDrillToDetailReferencesClause" | 
          "ItemAlternativeSortOrderReferencesClause" | 
          "ItemColumnReferencesClause" ) ]
     addJoinClause =  JOIN "QUOTED_STRING" [ SET "setPropertiesClause" ] [ SET (
           REF | REFERENCE ) "JoinForeignKeyReferencesClause" ] { 
          "joinComponentClause" }
     propertyValue =  ( "QUOTED_STRING" | "INTEGER_LITERAL" | 
          "FLOATING_POINT_LITERAL" )
     setPropertiesClause =  PROPERTIES "(" "propertyNameList" ")" VALUES "(" 
          "propertyValueList" ")"
     ItemItemReferencesClause =  ITEM "QUOTED_STRING" OF ( ( ITEM_FOLDER 
          "QUOTED_STRING" "itemJoinUsages" ) | ( LOGICAL_TABLE "QUOTED_STRING" )
           )
     ItemListOfValuesReferencesClause =  LIST_OF_VALUES "QUOTED_STRING"
     ItemDrillToDetailReferencesClause =  DRILL_TO_DETAIL "QUOTED_STRING"
     ItemAlternativeSortOrderReferencesClause =  ALTERNATIVE_SORT_ORDER 
          "QUOTED_STRING"
     ItemColumnReferencesClause =  COLUMN "QUOTED_STRING" OF ( TABLE | ( 
          EXTERNAL_TABLE | ( VIEW | MATERIALIZED_VIEW ) ) ) "QUOTED_STRING" [ 
          USING FOREIGN_KEY "QUOTED_STRING" OF ( TABLE | ( VIEW | 
          MATERIALIZED_VIEW ) ) "QUOTED_STRING" ]
     JoinForeignKeyReferencesClause =  FOREIGN_KEY "QUOTED_STRING" OF ( TABLE | 
          ( VIEW | MATERIALIZED_VIEW ) ) "QUOTED_STRING"
     joinComponentClause =  ADD JOIN_COMPONENT "QUOTED_STRING" [ SET 
          "setPropertiesClause" ] { SET ( REF | REFERENCE ) 
          "setJoinComponentClauseDetails" }
     propertyNameList =  "UNQUOTED_STRING" { "," "UNQUOTED_STRING" }
     propertyValueList =  "propertyValue" { "," "propertyValue" }
     itemJoinUsages =  { USING JOIN "QUOTED_STRING" OF ITEM_FOLDER 
          "QUOTED_STRING" }
     setJoinComponentClauseDetails =  LOCAL ITEM "QUOTED_STRING" | REMOTE ITEM 
          "QUOTED_STRING" OF ( ( ITEM_FOLDER "QUOTED_STRING" ) | ( LOGICAL_TABLE
           "QUOTED_STRING" ) )

Parameters

createLogicalTableCommand

This command creates a logical table.

QUOTED_STRING

Specify the name of the logical table to be created.

setpropertiesClauseDelayed

This clause sets the properties.

setReferenceIconSetClause

Set specified Icon Set.

addLogicalTableSCOClauses

This clause adds items to a logical table.

propertyNameListVector

This clause holds the names of the properties.

propertyValueListVector

This clause holds the values of the properties.

addItemClause

This clause adds an item to a logical table.

ITEM

A field within the logical table.

QUOTED_STRING

The name of the item to be added.

addJoinClause

This clause adds a foreign key relationship to another logical table.

JOIN

A foreign key relationship with another logical table.

QUOTED_STRING

The name of the join to be added.

propertyValue

This is a property value.

setPropertiesClause

Used to set properties (core, logical, physical, user-defined) for logical tables. Valid properties are shown below:

getPropertiesClause

Basic properties for ITEM_FOLDER: 

Name: BUSINESS_NAME
Type: STRING(200)
Valid Values: Any valid character string in supported character set.
Default: Empty string
Business name of the item folder 

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

Name: EXTERNAL_TABLE_NAME
Type: STRING(255)
Valid Values: Any valid character string in supported character set.
Default: Empty string
The physical name for the corresponding table or view.  This is automatically set if the Folder is associated with a Table

Name: VISIBLE
Type: BOOLEAN
Valid Values: Y,N
Default: 'Y'
Whether the item folder should be visible to the user 

Name: DISTINCT_VALUES
Type: BOOLEAN
Valid Values: Y,N
Default: 'N'
Whether to select distinct values from the source. Oracle Business Intelligence Modules (OBIEE) only 

Name: BRIDGE_TABLE
Type: BOOLEAN
Valid Values: Y,N
Default: 'N'
Whether this represents an intermediate table between a fact and dimension. Oracle Business Intelligence (OBIEE) only 

Name: FOLDER_TYPE
Type: STRING(40)
Valid Values: SIMPLE, COMPLEX
Default: Empty string
The type of item folder

Basic properties for ITEM:

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

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

Name: ALIGNMENT
Type: STRING(40)
Valid Values: GENERAL, LEFT, CENTER, RIGHT
Default: 'GENERAL'
The default alignment for displaying the item

Name: DISPLAY_CASE
Type: STRING(40)
Valid Values: GENERAL, LOWER, UPPER, INITCAPPED
Default: 'GENERAL'
How alphabetic characters should be displayed

Name: CASE_STORAGE
Type: STRING(40)
Valid Values: GENERAL, LOWER, UPPER, MIXED
Default: 'GENERAL'
How alphabetic characters are stored

Name: CONTENT_TYPE
Type: STRING(40)
Valid Values: No Value or FILE. For datatypes such as BLOB, it may contain a file extension such as DOC, AVI, WAV, JPG
Default: Empty string
Details on whether the Item contains a file name or should be processed by an external application

Name: DEFAULT_AGGREGATE
Type: STRING(255)
Valid Values (in a Logical Table): NONE, SUM, AVG, COUNT, COUNT DISTINCT, MAX, MIN, FIRST, LAST, MEDIAN, STDDEV and STDDEV_POP
Default: 'NONE'
Valid Values (in an Item Folder): Detail, AVG, COUNT, MAX, MIN, SUM
Default: 'SUM' when the datatype is Numeric, 'Detail' otherwise
Name of the default rollup function for the item

getPropertiesClause

Name: DEFAULT_POSITION
Type: STRING(40)
Valid Values: MEASURE, TOP OR SIDE, TOP, SIDE, PAGE
Default: 'MEASURE' when the datatype is NUMBER or FLOAT, 'TOP OR SIDE' otherwise
Default position for the item

Name: REPLACE_NULL_WITH
Type: STRING(255)
Valid Values: Any valid character string in supported character set.
Default: Empty string
The value to be displayed for null values

Name: FORMULA
Type: STRING
Valid Values: Any valid character string in supported character set.
Default: Empty string
The text of the derivation expression for a derived item 

Name: EXTERNAL_COLUMN_NAME
Type: STRING(255)
Valid Values: Any valid character string in supported character set.
Default: Empty string
The external name of the corresponding column.  This is automatically set if the Item is is associated with a Column

Name: FORMAT_MASK
Type: STRING(255)
Valid Values: Any valid character string in supported character set.
Default: Empty string
The display format mask for the item 

Name: HEADING
Type: STRING(255)
Valid Values: Any valid character string in supported character set.
Default: Empty string
The displayed heading text for the item 

For Logical Table Items for OBI EE
Name: DATATYPE
Type: STRING(40)
Valid Values: BINARY, BIT, CHAR, DATE, DATETIME, DOUBLE, 
FLOAT, INT, INTERVAL, LONGVARBINARY, LONGVARCHAR, SMALLINT, 
TIME, TIMESTAMP, TINYINT, UNKNOWN, VARBINARY, VARCHAR
Default: 'VARCHAR'
For Item Folder Items for Oracle Discoverer
Name: DATATYPE
Type: STRING(40)
Valid Values: BINARY_DOUBLE, BINARY_FLOAT, BLOB, CHAR, CLOB, DATE, FLOAT, INTEGER, INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH, LONG, LONG RAW 
NCHAR, NCLOB, NUMBER, NVARCHAR2, RAW, SYS.ANYDATA, SYS.LCR$_ROW_RECORD, TIMESTAMP, TIMESTAMP WITH LOCAL TIME ZONE
TIMESTAMP WITH TIME ZONE, VARCHAR, VARCHAR2, XMLTYPE
Default: 'VARCHAR2'
The datatype for the item 

Name: VISIBLE
Type: BOOLEAN
Valid Values: Y,N
Default: 'Y'
Whether the item should be visible to the user 

Name: MAX_CHAR_FETCHED
Type: Number
Valid Values: Any valid character string in supported character set.
Default: Empty string
The maximum number of characters fetched for an item 

Name: DEFAULT_WIDTH
Type: Number
Valid Values: Any valid character string in supported character set.
Default: Empty string
The default number of characters to display 

Name: WORD_WRAP
Type: BOOLEAN
Valid Values: Y,N
Default: 'N'
Whether wordwrap is allowed in the display

getPropertiesClause

Basic properties for JOIN:

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

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

Name: INCLUDE_UNMATCHED_DETAIL_ROWS
Type: BOOLEAN
Valid Values: Y,N
Default: 'N'
Whether detail rows with no related master row should be included in the join

Name: INCLUDE_UNMATCHED_MASTER_ROWS
Type: BOOLEAN
Valid Values: Y,N
Default: 'N'
Whether master rows with no related detail rows should be included in the join

Name: EXTERNAL_KEY_NAME
Type: STRING(255)
Valid Values: Any valid character string in supported character set.
Default: Empty string
The external name of the corresponding foreign key.  This is automatically set if the Join is is associated with a Foreign Key 

Name: DETAIL_ALWAYS_HAS_MASTER
Type: BOOLEAN
Valid Values: Y,N
Default: 'Y'
Whether every detail row must reference a unique master row 

Name: ONE_TO_ONE
Type: BOOLEAN
Valid Values: Y,N
Default: 'N'
Whether a master row only ever has a single detail row

Basic properties for JOIN_COMPONENT:

Name: JOIN_OPERATOR
Type: STRING(200)
Valid Values: =, <>, <, <=, > or >= 
Default: Empty string
Business name of the join 

Basic properties for CONDITION:

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

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

Name: MATCH_CASE
Type: BOOLEAN
Valid Values: Y,N
Default: 'Y'
Whether the case of alphabetic characters must match exactly

Name: FORMULA
Type: STRING
Valid Values: Any valid character string in supported character set.
Default: Empty string
The expression for the condition

Name: MANDATORY
Type: BOOLEAN
Valid Values: Y,N
Default: 'N'
Whether the Condition is optional or mandatory

ItemItemReferencesClause

This clause is used to reference another item.

QUOTED_STRING

name of the referenced item.

ItemListOfValuesReferencesClause

This clause is used to reference a list of values.

QUOTED_STRING

name of the list of values.

ItemDrillToDetailReferencesClause

This clause is used to reference a drill to detail.

QUOTED_STRING

name of the drill to detail.

ItemAlternativeSortOrderReferencesClause

This clause is used to reference an alternative sort order.

QUOTED_STRING

name of the alternative sort order.

ItemColumnReferencesClause

This clause is used to reference a column.

QUOTED_STRING

name of the referenced column.

JoinForeignKeyReferencesClause

The foreign key reference.

joinComponentClause

The join components.

propertyNameList

This is the list of property names.

propertyValueList

This is the list of property values.

itemJoinUsages

The specific joins to be used.

setJoinComponentClauseDetails

The structure of the join component.

LOGICAL_TABLE Object

Table 8-5 LOGICAL_TABLE Object

Property Type Choices Min Max Default Description

BRIDGE_TABLE

BOOLEAN

true, false

none

none

false

Whether the Logical Table represents an intermediate table between a fact table and a dimension table (to resolve many-to-many associations).

DEPLOYABLE

BOOLEAN

true, false

none

none

true

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

DISTINCT_VALUES

BOOLEAN

true, false

none

none

false

Whether to only select distinct values from the source physical table.

EXTERNAL_TABLE_NAME

STRING

none

none

none

empty string

The physical name for the corresponding table or view.

VISIBLE

BOOLEAN

true, false

none

none

false

Whether this object is visible for use in query definitions.