Skip Headers
Oracle® Warehouse Builder OMB*Plus Command Reference
11g Release 2 (11.2)

Part Number E14406-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
Mobi · ePub

OMBALTER DATA_PROFILE

Purpose

Alter the Data Profile by renaming it, and/or reset its properties.

Prerequisites

Should be in the context of project.

Syntax

alterDataProfileCommand =  OMBALTER ( DATA_PROFILE "QUOTED_STRING" ( 
          "renameClause" [ "alterPropertiesOrReferenceClause" ] | ( [ 
          "alterPropertiesOrReferenceClause" ] ( { ( ADD | DELETE ) ( TABLE | 
          VIEW | EXTERNAL_TABLE | MATERIALIZED_VIEW | DIMENSION | CUBE ) 
          "QUOTED_STRING" [ USING ATTRIBUTE_SET "QUOTED_STRING" ] [ 
          LIMIT_COLUMNS ] } ) ) ) )
     renameClause =  RENAME TO "QUOTED_STRING"
     alterPropertiesOrReferenceClause =  SET ( "setPropertiesClause" [ SET 
          "setReferenceClause" [ UNSET "unsetReferenceClause" ] | UNSET 
          "unsetReferenceClause" [ SET "setReferenceClause" ] ] | 
          "setReferenceClause" [ UNSET "unsetReferenceClause" ] ) | UNSET 
          "unsetReferenceClause" [ SET "setReferenceClause" ]
     setPropertiesClause =  PROPERTIES "(" "propertyNameList" ")" VALUES "(" 
          "propertyValueList" ")"
     setReferenceClause =  ( "setReferenceLocationClause" [ SET 
          "setReferenceIconSetClause" ] | "setReferenceIconSetClause" )
     unsetReferenceClause =  ( "unsetReferenceLocationClause" [ UNSET 
          "unsetReferenceIconSetClause" ] | "unsetReferenceIconSetClause" )
     propertyNameList =  "UNQUOTED_STRING" { "," "UNQUOTED_STRING" }
     propertyValueList =  "propertyValue" { "," "propertyValue" }
     setReferenceLocationClause =  ( REFERENCE | REF ) LOCATION "QUOTED_STRING"
     setReferenceIconSetClause =  ( REFERENCE | REF ) ICONSET "QUOTED_STRING"
     unsetReferenceLocationClause =  ( REFERENCE | REF ) LOCATION 
          "QUOTED_STRING"
     unsetReferenceIconSetClause =  ( REFERENCE | REF ) ICONSET
     propertyValue =  ( "QUOTED_STRING" | "INTEGER_LITERAL" | 
          "FLOATING_POINT_LITERAL" )

Parameters

alterDataProfileCommand

This command modifies an existing Data Profile.

QUOTED_STRING

Name of the existing Data Profile in single quotes.

renameClause

Rename a Data Profile.

alterPropertiesOrReferenceClause

Alter existing Data Profile's core properties, locations and icon sets.

setPropertiesClause

Associate a set of properties with the existing Data Profile.

setPropertiesClause

Configuration properties for DATA_PROFILE that affect loading:

Name: COPY_DATA

Type: BOOLEAN

Valid Values: true | false

Default: true

Setting this to true will enable copying of data from source to profile workspace.

Name: FORCE_COPY_DATA

Type: BOOLEAN

Valid Values: true | false

Default: false

Setting this to true will allways force a profile to run.

Name: CALCULATE_DATATYPES

Type: BOOLEAN

Valid Values: true | false

Default: false

Setting this to true will enable data type discovery for the selected table.

Name: CALCULATE_COMMON_FORMATS

Type: BOOLEAN

Valid Values: true | false

Default: false

This tells the profiler if common formats are to be discovered for all sources in this profile.

Name: NULL_VALUE

Type: STRING

Valid Values: any string value

Default: null

This value will be considered as the null value when profiling. Please enclose the value in single quotes. An unqouted null (the current default value) will be considered a database null.

Name: SAMPLE_RATE

Type: NUMBER

Valid Values: 1-100

Default: 100

This value will be the percent of total rows that will be randomly selected during loading.

setPropertiesClause

Configuration properties for DATA_PROFILE that affect profiling:

Name: CALCULATE_DOMAINS

Type: BOOLEAN

Valid Values: true | false

Default: true

Setting this to true will enable domain discovery.

Name: DOMAIN_MAX_COUNT

Type: NUMBER

Valid Values: 1-any number

Default: true

The maximum number of distinct values in a column in order for that column to be discovered as possibly being defined by a domain. Domain Discovery of a column occurs if the number of distinct values in that column is at or below the Max Distinct Values Count property, AND, the number of distinct values as a percentage of total rows is at or below the Max Distinct Values Percent property.

Name: DOMAIN_MAX_PERCENT

Type: NUMBER

Valid Values: 1-100

Default: true

The maximum number of distinct values in a column, expressed as a percentage of the total number of rows in the table, in order for that column to be discovered as possibly being defined by a domain. Domain Discovery of a column occurs if the number of distinct values in that column is at or below the Max Distinct Values Count property, AND, the number of distinct values as a percentage of total rows is at or below the Max Distinct Values Percent property.

Name: DOMAIN_MIN_COUNT

Type: NUMBER

Valid Values: 1-any number

Default: true

The minimum number of rows for the given distinct value in order for that distinct value to be considered as compliant with the domain. Domain Value Compliance for a value occurs if the number of rows with that value is at or above the Min Rows Count property, AND, the number of rows with that value as a percentage of total rows is at or above the Min Rows Percent property.

Name: DOMAIN_MIN_PERCENT

Type: NUMBER

Valid Values: 1-100

Default: true

The minimum number of rows, expressed as a percentage of the total number of rows, for the given distinct value in order for that distinct value to be considered as compliant with the domain. Domain Value Compliance for a value occurs if the number of rows with that value is at or above the Min Rows Count property, AND, the number of rows with that value as a percentage of total rows is at or above the Min Rows Percent property.

Name: CALCULATE_UK

Type: BOOLEAN

Valid Values: true | false

Default: true

Setting this to true will enable unique key discovery.

Name: UK_MIN_PERCENT

Type: NUMBER

Valid Values: 1-100

Default: 75

This is the minimum percentage of rows that need to satisfy a unique key relationship.

Name: CALCULATE_FD

Type: BOOLEAN

Valid Values: true | false

Default: true

Setting this to true will enable functional dependency discovery.

Name: FD_MIN_PERCENT

Type: NUMBER

Valid Values: 1-100

Default: 75

This is the minimum percentage of rows that need to satisfy a functional dependency relationship.

Name: FD_UK_LHS_COUNT

Type: NUMBER

Valid Values: 1-number of attributes of source less 1

Default: 1

This is the maximum number of attributes for unique key and functional dependency profiling.

setPropertiesClause

Name: CALCULATE_FK

Type: BOOLEAN

Valid Values: true | false

Default: true

Setting this to true will enable foreign key discovery.

Name: FK_MIN_PERCENT

Type: NUMBER

Valid Values: 1-100

Default: 75

This is the minimum percentage of rows that need to satisfy a foreign key relationship.

Name: CALCULATE_REDUNDANT_COLUMNS

Type: BOOLEAN

Valid Values: true | false

Default: false

Setting this to true will enable redundant column discovery with respect to a foreign key-unique key pair.

Name: REDUNDANT_MIN_PERCENT

Type: NUMBER

Valid Values: 1-100

Default: 75

This is the minimum percentage of rows that are redundant.

Name: CALCULATE_DATA_RULES

Type: BOOLEAN

Valid Values: true | false

Default: false

Setting this to true will enable data rule profiling for the selected table.

Name: CALCULATE_PATTERNS

Type: BOOLEAN

Valid Values: true | false

Default: false

Setting this to true will enable pattern discovery.

Name: MAX_NUM_PATTERNS

Type: NUMBER

Valid Values: any number less than the number of rows of the source

Default: 10

This tells the profiler to get the top-N patterns for the attribute.

setReferenceClause

A general clause for setting references for a Data Profile.

unsetReferenceClause

A general clause for unsetting references for a Data Profile.

propertyNameList

Comma separated list of property names. Property names are unquoted.

propertyValueList

Comma separated list of property values.

setReferenceLocationClause

Set a location for an existing Data Profile.

setReferenceIconSetClause

Set icon set for the Data Profile.

unsetReferenceLocationClause

Unset a location for an existing Data Profile.

unsetReferenceIconSetClause

Unset icon set for the Data Profile.

propertyValue

Value of a property.

DATA_PROFILE Object

Table 2-5 DATA_PROFILE Object

Property Type Choices Min Max Default Description

ABAP_DIRECTORY

STRING

none

none

none

/tmp

Location where SAP data is dumped as flat files

ABAP_EXTENSION

STRING

none

none

none

.abap

File name extension for ABAP scripts

ABAP_RUN_PARAMETER_FILE

STRING

none

none

none

_run.ini

Run Parameter File Suffix for the parameter script in a ABAP job.

ABAP_SPOOL_DIRECTORY

STRING

none

none

none

abap\log\

Location where ABAP scripts are buffered during script generation processing.

APPLICATION_SHORT_NAME

STRING

none

none

none

WB

Application Short Name

ARCHIVE_DIRECTORY

STRING

none

none

none

archive\

Archive Directory

CALCULATE_COMMON_FORMATS

BOOLEAN

true, false

none

none

false

Setting this to true will enable common format discovery for all the columns in this profile.

CALCULATE_DATATYPES

BOOLEAN

true, false

none

none

true

Setting this to true will enable data type discovery for all the columns in this profile.

CALCULATE_DATA_RULES

BOOLEAN

true, false

none

none

false

Setting this to true will enable data rule profiling for the selected table.

CALCULATE_DOMAINS

BOOLEAN

true, false

none

none

true

Setting this to true will enable domain discovery.

CALCULATE_FD

BOOLEAN

true, false

none

none

true

Setting this to true will enable functional dependency discovery.

CALCULATE_FK

BOOLEAN

true, false

none

none

true

Setting this to true will enable foreign key discovery.

CALCULATE_PATTERNS

BOOLEAN

true, false

none

none

false

Setting this to true will enable pattern discovery.

CALCULATE_REDUNDANT_COLUMNS

BOOLEAN

true, false

none

none

false

Setting this to true will enable redundant column discovery.

CALCULATE_SOUNDEX

BOOLEAN

true, false

none

none

false

Setting this to true will enable soundex relationship discovery for columns with string data types. Note that you must ensure that these attributes are part of relationship discovery. You can set this by clicking on the attribute and enabling the checkbox for Row Relationship Discovery Configuration.

CALCULATE_UK

BOOLEAN

true, false

none

none

true

Setting this to true will enable unique key discovery.

COPY_DATA

BOOLEAN

true, false

none

none

true

Setting this to true will enable copying of data from source to staging area.

CREATE_MVIEWS

BOOLEAN

true, false

none

none

false

Setting this to true will create materialized views for each column in every table of this profile to enhance query performance during drill down.

DB_LOCATION

STRING

none

none

none

empty string

If this is a source module, this value indicates the location from which data will be read. If this is a target warehouse module, this value indicates the location where generated code will be deployed to and/or where data will be written to.

DDL_DIRECTORY

STRING

none

none

none

ddl\

Location where scripts for database objects for the target schema are stored.

DDL_EXTENSION

STRING

none

none

none

.ddl

File name extension for DDL scripts.

DDL_SPOOL_DIRECTORY

STRING

none

none

none

ddl\log\

Location where DDL scripts are buffered during script generation processing.

DEBUG

BOOLEAN

true, false

none

none

false

 

DEFAULT_INDEX_TABLESPACE

STRING(30)

none

none

none

empty string

Default name of tablespace to install indexes into.

DEFAULT_OBJECT_TABLESPACE

STRING(30)

none

none

none

empty string

Default name of tablespace to install objects into.

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

DESIGNER_CONTAINER_ELEMENT

STRING

none

none

none

empty string

 

DOMAIN_MAX_COUNT

NUMBER

none

none

none

100

The maximum number of distinct values in a column in order for that column to be discovered as possibly being defined by a domain. Domain Discovery of a column occurs if the number of distinct values in that column is at or below the Max Distinct Values Count property, AND, the number of distinct values as a percentage of total rows is at or below the Max Distinct Values Percent property.

DOMAIN_MAX_PERCENT

NUMBER

none

0

100

50

The maximum number of distinct values in a column, expressed as a percentage of the total number of rows in the table, in order for that column to be discovered as possibly being defined by a domain. Domain Discovery of a column occurs if the number of distinct values in that column is at or below the Max Distinct Values Count property, AND, the number of distinct values as a percentage of total rows is at or below the Max Distinct Values Percent property.

DOMAIN_MIN_COUNT

NUMBER

none

none

none

2

The minimum number of rows for the given distinct value in order for that distinct value to be considered as compliant with the domain. Domain Value Compliance for a value occurs if the number of rows with that value is at or above the Min Rows Count property, AND, the number of rows with that value as a percentage of total rows is at or above the Min Rows Percent property.

DOMAIN_MIN_PERCENT

NUMBER

none

0

100

1

The minimum number of rows, expressed as a percentage of the total number of rows, for the given distinct value in order for that distinct value to be considered as compliant with the domain. Domain Value Compliance for a value occurs if the number of rows with that value is at or above the Min Rows Count property, AND, the number of rows with that value as a percentage of total rows is at or above the Min Rows Percent property.

END_OF_LINE

STRING

none

none

none

\r\n

End of Line

FD_MIN_PERCENT

NUMBER

none

0

100

100

This is the minimum percentage of rows that need to satisfy a functional dependency relationship.

FD_UK_FK_LHS_COUNT

NUMBER

none

none

none

1

This is the maximum number of attributes for unique key and functional dependency profiling.

FK_MIN_PERCENT

NUMBER

none

0

100

75

This is the minimum percentage of rows that need to satisfy a foreign key relationship.

FORCE_COPY_DATA

BOOLEAN

true, false

none

none

false

Setting this to true will force a profile to run.

INPUT_DIRECTORY

STRING

none

none

none

input\

Input Directory

INVALID_DIRECTORY

STRING

none

none

none

invalid\

Directory for SQL*Loader errors and rejected records

LAST_PROFILE_LOCATION

STRING

none

none

none

empty string

 

LAST_SEQUENCE

NUMBER

none

none

none

1

 

LIB_DIRECTORY

STRING

none

none

none

lib\

LIB Directory

LIB_EXTENSION

STRING

none

none

none

.lib

LIB Extension

LIB_SPOOL_DIRECTORY

STRING

none

none

none

lib\log\

LIB Spool Directory

LOADER_DIRECTORY

STRING

none

none

none

ctl\

Location where control files are stored.

LOADER_EXTENSION

STRING

none

none

none

.ctl

Suffix for the loader scripts

LOADER_RUN_PARAMETER_FILE

STRING

none

none

none

_run.ini

Suffix for the parameter initialization file.

LOG_DIRECTORY

STRING

none

none

none

log\

Log Directory for the SQL*Loader

MAIN_APPLICATION_SHORT_NAME

STRING

none

none

none

ora

Main Application Short Name

MAX_NUM_PATTERNS

NUMBER

none

none

none

10

This tells the profiler to get the top-N patterns for the attribute.

NULL_PERCENT

NUMBER

none

0

100

10

If the percentage of null values in a column is less than this threshold percentage, then that column will be discovered as a possible Not Null column.

NULL_VALUE

STRING

none

none

none

null

This value will be considered as the null value when profiling. Please enclose the value in single quotes. An unqouted null (the current default value) will be considered a database null.

PLSQL_DIRECTORY

STRING

none

none

none

pls\

Location where PL/SQL scripts are stored.

PLSQL_EXTENSION

STRING

none

none

none

.pls

File name extension for PL/SQL scripts.

PLSQL_GENERATION_MODE

STRING

Default, Oracle10g, Oracle10gR2, Oracle11gR1, Oracle11gR2, Oracle8i, Oracle9i

none

none

Default

Generation mode controls validation and generation for version specific features.

PLSQL_RUN_PARAMETER_FILE

STRING

none

none

none

_run.ini

Suffix for the parameter script in a PL/SQL job.

PLSQL_SPOOL_DIRECTORY

STRING

none

none

none

pls\log\

Location where PL/SQL scripts are buffered during script generation processing.

RECEIVE_DIRECTORY

STRING

none

none

none

receive\

Receive Directory

REDUNDANT_MIN_PERCENT

NUMBER

none

0

100

75

This is the minimum percentage of rows that are redundant with respect to a foreign key-unique key pair.

SAMPLE_RATE

NUMBER

none

0

100

100

This value will be the percent of total rows that will be randomly selected during loading.

SORT_DIRECTORY

STRING

none

none

none

sort\

Sort Directory

SOUNDEX_MIN_PERCENT

NUMBER

none

0

100

75

This is the minimum percentage of rows that need to satisfy a soundex relationship. Values with the same soundex value will be considered the same.

SQLPLUS_DIRECTORY

STRING

none

none

none

sqlplus\

Location where SQLPLUS scripts are stored

SQLPLUS_EXTENSION

STRING

none

none

none

.sql

Suffix for the SqlPlus scripts

SQLPLUS_RUN_PARAMETER_FILE

STRING

none

none

none

_run.ini

Suffix for the parameter initialization file

STREAMS_ADMINISTRATOR_LOCATION

STRING

none

none

none

empty string

The location corresponding to the Streams Administrator

TCL_DIRECTORY

STRING

none

none

none

tcl\

Location for TCL scripts that are generated after registration with Oracle Enterprise Manager

TOP_DIRECTORY

STRING

none

none

none

..\..\codegen\

Top Directory where generated code will get stored

UK_MIN_PERCENT

NUMBER

none

0

100

75

This is the minimum percentage of rows that need to satisfy a unique key relationship.

WORK_DIRECTORY

STRING

none

none

none

work\

Work Directory