OMBRETRIEVE PROFILE_REFERENCE

Purpose

To retrieve properties of a profile reference.

Prerequisites

In the context of a Data Profile.

Syntax

retrieveProfileReferenceCommand =  OMBRETRIEVE PROFILE_REFERENCE 
          "QUOTED_STRING" ( "retrieveProfileReferenceClause" | 
          "retrieveDataRuleUsageClause" | "retrieveProfileAttributeClause" | 
          "retrieveProfileForeignKeyClause" | "retrieveProfileUniqueKeyClause" |
           "retrieveRowRelationshipClause" | 
          "retrieveFunctionalDependencyClause" )
     retrieveProfileReferenceClause =  GET ( "getPropertiesClause" | 
          "getProfileReferenceSCOClause" )
     retrieveDataRuleUsageClause =  DATA_RULE_USAGE "QUOTED_STRING" ( GET ( 
          "getPropertiesClause" | GROUPS ) | GROUP "QUOTED_STRING" ( GET ( 
          "getPropertiesClause" | ATTRIBUTES | REF TABLE ) | ATTRIBUTE 
          "QUOTED_STRING" ( GET ( "getPropertiesClause" | REF COLUMN ) ) ) )
     retrieveProfileAttributeClause =  PROFILE_ATTRIBUTE "QUOTED_STRING" ( GET (
           "getPropertiesClause" | "getDomainValuesClause" | 
          "getCharPatternValuesClause" | "getWordPatternValuesClause" ) | 
          DOMAIN_VALUE "QUOTED_STRING" ( GET "getPropertiesClause" ) | 
          CHARPATTERN_VALUE "QUOTED_STRING" ( GET "getPropertiesClause" ) | 
          WORDPATTERN_VALUE "QUOTED_STRING" ( GET "getPropertiesClause" ) )
     retrieveProfileForeignKeyClause =  PROFILE_FOREIGN_KEY "QUOTED_STRING" ( 
          GET ( "getPropertiesClause" | "getRedundantAttributesForFKClause" | 
          "getReferencingAttributesClause" | "getReferencingUKClauseForFK" ) | 
          REDUNDANT_ATTRIBUTE "QUOTED_STRING" ( GET "getPropertiesClause" ) )
     retrieveProfileUniqueKeyClause =  PROFILE_UNIQUE_KEY "QUOTED_STRING" ( GET 
          ( "getPropertiesClause" | "getReferencingAttributesClause" ) )
     retrieveRowRelationshipClause =  ROW_RELATIONSHIP "QUOTED_STRING" ( GET ( 
          "getPropertiesClause" | "getReferencingAttributesClause" | 
          "getReferencingUKClauseForRR" ) )
     retrieveFunctionalDependencyClause =  FUNCTIONAL_DEPENDENCY "QUOTED_STRING"
           ( GET ( "getPropertiesClause" | "getDeterminantAttributesClause" | 
          "getDependentAttributeClause" ) )
     getPropertiesClause =  PROPERTIES "(" "propertyNameList" ")"
     getProfileReferenceSCOClause =  PROFILE_ATTRIBUTES | PROFILE_ATTRIBUTE AT 
          POSITION "INTEGER_LITERAL" | PROFILE_FOREIGN_KEYS | 
          PROFILE_UNIQUE_KEYS | DATA_RULE_USAGES | FUNCTIONAL_DEPENDENCIES | 
          ROW_RELATIONSHIPS
     getDomainValuesClause =  DOMAIN_VALUES
     getCharPatternValuesClause =  CHARPATTERN_VALUES
     getWordPatternValuesClause =  WORDPATTERN_VALUES
     getRedundantAttributesForFKClause =  REDUNDANT_ATTRIBUTES
     getReferencingAttributesClause =  PROFILE_ATTRIBUTES
     getReferencingUKClauseForFK =  UNIQUE_KEY
     getReferencingUKClauseForRR =  ROW_RELATIONSHIP
     getDeterminantAttributesClause =  DETERMINANT_ATTRIBUTES
     getDependentAttributeClause =  DEPENDENT_ATTRIBUTE
     propertyNameList =  "UNQUOTED_STRING" { "," "UNQUOTED_STRING" }

Parameters

retrieveProfileReferenceCommand

This clause retrieves the results of a profile of a source.

retrieveProfileReferenceClause

This clause retrieves

retrieveDataRuleUsageClause

This clause retrieves the data rule usages defined or derived for this particular profile reference.

retrieveProfileAttributeClause

This clause retrieves profile attribute values.

retrieveProfileForeignKeyClause

This clause retrieves the discovered foreign keys for this particular profile reference.

retrieveProfileUniqueKeyClause

This clause retrieves the discovered unique keys for this particular profile reference.

retrieveRowRelationshipClause

This clause retrieves the discovered row relationships for this particular profile reference.

retrieveFunctionalDependencyClause

This clause retrieves the discovered functional dependencies for this particular profile reference. The format for discovered functional dependencies are A->B, where A is the determinant column/s and B is the dependent column.

getPropertiesClause

This clause retrieves the properties for a particular object.

getPropertiesClause

Configuration properties for PROFILE_REFERENCE 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 always 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

Setting this to true will enable common format discovery for the selected table.

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: 100

Valid Values: 1-100

Default: 100

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

getPropertiesClause

Configuration properties for PROFILE_REFERENCE 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.

getPropertiesClause

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.

Configuration properties for PROFILE_ATTRIBUTE that affect loading:

Name: USE_IN_LOADING

Type: BOOLEAN

Valid Values: true | false

Default: true

This tells the profiler if the data for this column is to be copied from the source schema to the profile workspace schema.

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.

getPropertiesClause

Configuration properties for PROFILE_ATTRIBUTE that affect loading:

Name: USE_IN_FK

Type: BOOLEAN

Valid Values: true | false

Default: true

This tells the profiler if this column is to be part of the determinant in functional dependency discovery.By default, all non-numeric columns set this property to false

Name: USE_IN_LOADING

Type: BOOLEAN

Valid Values: true | false

Default: true

Setting this to true will include this column in profiling.

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

Setting this to true will enable common format discovery for the selected table.

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.

Configuration properties for PROFILE_ATTRIBUTE that affect profiling:

Name: USE_IN_DETERMINANT

Type: BOOLEAN

Valid Values: true | false

Default: true

This tells the profiler if this column is to be part of the determinant in functional dependency discovery.By default, all non-numeric columns set this property to false

Name: CALCULATE_DOMAINS

Type: BOOLEAN

Valid Values: true | false

Default: true

This tells the profiler if domain values are to be discovered for this column.

Name: DOMAIN_MAX_COUNT

Type: NUMBER

Valid Values: 1-any number

Default: true

This tells the profiler the maximum number of distinct values this column can have to be considered as domain attributes.

Name: DOMAIN_MAX_PERCENT

Type: NUMBER

Valid Values: 1-100

Default: true

This tells the profiler the maximum number of distinct values as a percentage this column can have to be considered as domain attributes.

Name: DOMAIN_MIN_COUNT

Type: NUMBER

Valid Values: 1-any number

Default: true

This tells the profiler the minimum number of rows a particular value should have in order to be considered a domain value.

Name: DOMAIN_MIN_PERCENT

Type: NUMBER

Valid Values: 1-100

Default: true

This tells the profiler the minimum number of rows as a percentage a particular value should have in order to be considered a domain value.

getProfileReferenceSCOClause

This clause retrieves reference table values.

getDomainValuesClause

This clause retrieves doman values for an attribute.

getCharPatternValuesClause

This clause retrieves character pattern values for an attribute.

getWordPatternValuesClause

This clause retrieves word pattern values for an attribute.

getRedundantAttributesForFKClause

This clause retrieves redundant attributes for a foreign key.

getReferencingAttributesClause

This clause retrieves profile attributes for a row relationship, foreign key or unique key.

getReferencingUKClauseForFK

This clause retrieves the unique key for a foreign key.

getReferencingUKClauseForRR

This clause retrieves the unique key for a row relationship

getDeterminantAttributesClause

This clause retrieves the determinant attributes of a functional dependency.

getDependentAttributeClause

This clause retrieves the dependent attribute of a functional dependency.

propertyNameList

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

PROFILE_REFERENCE Object

Table 20-57 PROFILE_REFERENCE Object

Property	Type	Choices	Min	Max	Default	Description
ANALYZE_TABLE_ESTIMATE_PERCENT	STRING	none	none	none	empty string	Value represents the sample size as a percentage (0-100) of total rows. When set to a nonzero value, Builder generates a DDL script to analyze the table. The default is 99.
BUFFER_POOL	STRING	, DEFAULT, KEEP, RECYCLE	none	none	empty string	Specify a default buffer pool (cache) for table or partition object. The default is DEFAULT.
CACHE_MODE	STRING	, CACHE, NOCACHE	none	none	empty string	Indicate how Oracle should store blocks in the buffer cache.
CALCULATE_COMMON_FORMATS	BOOLEAN	true, false	none	none	false	Setting this to true will enable common format discovery for the selected table.
CALCULATE_DATATYPES	BOOLEAN	true, false	none	none	true	Setting this to true will enable data type discovery for the selected table.
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 with respect to a foreign key-unique key pair.
CALCULATE_UK	BOOLEAN	true, false	none	none	true	Setting this to true will enable unique key discovery.
CDC_POSITION	NUMBER	none	none	none	0	Table position in change data capture.
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 a materialized view for each column in this table to enhance query performance during drill down.
DATA_RULE_SIGNATURE	STRING	none	none	none	empty string
DATA_SEGMENT_COMPRESSION	STRING	, COMPRESS, COMPRESS ALL, NOCOMPRESS	none	none	empty string	Use this clause to instruct Oracle whether to compress data segments to reduce disk use. The default is NOCOMPRESS.
DEPLOYABLE	BOOLEAN	true, false	none	none	false	Warehouse Builder generates a set of scripts to create an object only for those object marked as Deployable = true
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.
ERROR_TABLESPACE	STRING(30)	none	none	none	empty string	Use the Tablespace parameter to specify the name of tablespace.
ERROR_TABLE_NAME	STRING(30)	none	none	none	empty string	Use the error table name to specify the name of Error Table.
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, foreign 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.
FREELISTGROUPS	STRING	none	none	none	empty string	Specify the number of groups of free lists for the database object you are creating. The default is 1.
FREELISTS	STRING	none	none	none	empty string	Specify the number of free lists for each of the free list groups for the table, partition, cluster, or index. The default is 1.
GENERATED_OBJECTS	STRING	none	none	none	empty string
GENERATE_ERROR_TABLE_ONLY	BOOLEAN	true, false	none	none	false	Create, drop, replace or upgrade only the error table.
GENERATION_COMMENTS	STRING	none	none	none	empty string	Enter additional comments for the generated code.
INITIAL	STRING	none	none	none	empty string	Specify in bytes the size of the first extent. Use K or M to specify size in kilobytes or megabytes.
INITRANS	STRING	none	none	none	empty string	Specify the initial number (1-255) of concurrent transaction entries allocated within each data block allocated to the database object. The default is 1.
IS_CDC	BOOLEAN	true, false	none	none	false	Enable table for change data capture
LOGGING_MODE	STRING	, LOGGING, NOLOGGING	none	none	empty string	Specify whether the creation of the table and of any indexes required because of constraints, partition, or LOB storage characteristics will be logged in the redo log file (LOGGING) or not (NOLOGGING). The default is LOGGING.
MAXEXTENTS	STRING	none	none	none	empty string	Specify the total number of extents, including the first, that Oracle can allocate for the object.
MAXTRANS	STRING	none	none	none	empty string	Specify the maximum number (1-255) of concurrent transactions that can update a data block allocated to the database object.
MAX_NUM_PATTERNS	NUMBER	none	none	none	10	This tells the profiler to get the top-N patterns for the attribute.
MINEXTENTS	STRING	none	none	none	empty string	Specify the total number of extents to allocate when the object is created.
MONITORING_MODE	STRING	, MONITORING, NOMONITORING	none	none	empty string	Specify MONITORING if you want modification statistics to be collected on this table.
NEXT	STRING	none	none	none	empty string	Specify in bytes the size of the next extent to be allocated. Use K or M to specify size in kilobytes or megabytes.
NULL_PERCENT	NUMBER	none	0	100	10	If the percentage of null values in a column is less than this threshold percent, 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.
NUM_ROWS	NUMBER	none	none	none	0
OVERFLOW	STRING	none	none	none	empty string	Enter a comma separated list of tablespaces for overflow data. For simple-partitoned object, it is used for HASH BY QUANTITY partition overflow tablespaces. The number of tablespaces does not have to equal the number of partitions. If the number of partitions is greater than the number of tablespaces, then Oracle cycles through the names of the tablespaces.
PARALLEL_ACCESS_MODE	STRING	, NOPARALLEL, PARALLEL	none	none	empty string	Enables or disables parallel processing when the table is created. Also enables or disables parallel processing or access. The default is PARALLEL.
PARALLEL_DEGREE	STRING	none	none	none	empty string	Enter degree of parallelism, which is the number of parallel threads used in the parallel operation.
PARTITION_TABLESPACE_LIST	STRING	none	none	none	empty string	Enter a comma separated list of tablespaces. For simple-partitoned object, it is used for HASH BY QUANTITY partition tablespaces. For composite-partitioned tables, it is used for subpartition template to store a list of tablespaces.
PCTFREE	STRING	none	none	none	empty string	Specify a whole number representing the percentage (0-99) of space in each data block of the database object reserved for future updates to the rows of the object. The default is 10.
PCTINCREASE	STRING	none	none	none	empty string	Specify the percent by which the third and subsequent extents grow over the preceding extent. The default is 50.
PCTUSED	STRING	none	none	none	empty string	Specify a whole number representing the minimum percentage (0-99) of used space that Oracle maintains for each data block of the database object. The default is 40.
PROFILE_DONE	BOOLEAN	true, false	none	none	false
REDUNDANT_MIN_PERCENT	NUMBER	none	0	100	75	This is the minimum percentage of rows that are redundant.
ROWDEPENDENCIES_MODE	STRING	, NOROWDEPENDENCIES, ROWDEPENDENCIES	none	none	empty string	Specify ROWDEPENDENCIES to use row-level dependency tracking.
ROW_MOVEMENT	STRING	, DISABLE, ENABLE	none	none	empty string	Specify whether Oracle can move a table row.
SAMPLE_RATE	NUMBER	none	0	100	100	This value will be the percent of total rows that will be randomly selected during loading.
TABLESPACE	STRING(30)	none	none	none	empty string
UK_MIN_PERCENT	NUMBER	none	0	100	75	This is the minimum percentage of rows that need to satisfy a unique key relationship.
WHERE_CLAUSE	STRING	none	none	none	empty string	Where clause to be applied on the source when loading data into the profile staging area.

OMBRETRIEVE PROFILE_REFERENCE

Purpose

Prerequisites

Syntax

Parameters

retrieveProfileReferenceCommand

retrieveProfileReferenceClause

retrieveDataRuleUsageClause

retrieveProfileAttributeClause

retrieveProfileForeignKeyClause

retrieveProfileUniqueKeyClause

retrieveRowRelationshipClause

retrieveFunctionalDependencyClause

getPropertiesClause

getPropertiesClause

getPropertiesClause

getPropertiesClause

getPropertiesClause

getProfileReferenceSCOClause

getDomainValuesClause

getCharPatternValuesClause

getWordPatternValuesClause

getRedundantAttributesForFKClause

getReferencingAttributesClause

getReferencingUKClauseForFK

getReferencingUKClauseForRR

getDeterminantAttributesClause

getDependentAttributeClause

propertyNameList

PROFILE_REFERENCE Object

See Also