| Oracle® OLAP Application Developer's Guide 10g Release 1 (10.1) Part Number B10333-02 |
|
|
View PDF |
| Oracle® OLAP Application Developer's Guide 10g Release 1 (10.1) Part Number B10333-02 |
|
|
View PDF |
Database standard form is a set of conventions describing the objects in an analytic workspace that can be managed by various Oracle OLAP utilities. This appendix describes database standard form conventions. It has the following sections:
An analytic workspace that conforms to database standard form has objects that implement a logical model for cubes, dimensions, and measures. Standard form includes naming conventions for workspace objects, and it specifies object properties relating the logical model to the workspace objects that implement it.
The purpose of the standard form conventions is to provide an agreed-upon logical model and workspace implementation to be used by related Oracle OLAP utilities. Because these utilities work with data and metadata that is in the standard form, the utilities are compatible with one another. Therefore, a DBA who uses these utilities can create and maintain analytic workspaces that can be accessed through more than one analytic tool. Currently, access is available through the OLAP API, BI Beans, and Discoverer.
The Analytic Workspace Manager provides the following utilities, which either create or depend upon the presence of a standard form workspace:
Analytic Workspace Creation Wizard creates a new workspace in standard form from OLAP Catalog metadata.
Analytic Workspace Refresh Wizard refreshes an existing workspace in standard form from OLAP Catalog metadata.
Enable for OLAP API and BI Beans feature enables a standard form workspace for access through the OLAP API and BI Beans.
Enable for Discoverer Wizard enables a standard form workspace for access through Discoverer.
Analytic Workspace Manager uses the PL/SQL package DBMS_AWM to create, refresh, and enable standard form analytic workspaces for access by the OLAP API. You can use the Analytic Workspace Manager to manage your analytic workspaces, or you can develop your own scripts using the DBMS_AWM procedures.
Ordinarily, you create, manage, and enable standard form workspaces using the tools and procedures provided. Therefore, you will typically have no need for detailed knowledge of the standard form. However, such knowledge is necessary under the following circumstances:
When you want to make manual additions to an existing standard form workspace. For example, the OLAP Catalog metadata might not have included a plural description for a dimension, and the DBA might want to add one in the workspace rather than in the OLAP Catalog.
When you are developing an application that uses standard form workspaces, and you want it to discover information at run-time, such as which measures are available for analysis in a particular workspace, how they are dimensioned, and what levels and hierarchies are defined.
This appendix describes the standard form so that if you have these requirements, you can understand the conventions of a standard form workspace. To understand these conventions, you must be familiar with multidimensional OLAP concepts and should be experienced in using the OLAP DML.
The standard form logical model includes cubes, measures, and dimensions, as well as the hierarchies, levels, and attributes that are associated with dimensions. A cube is considered to be the parent of the measures that it contains, and a dimension is considered to be the parent of its hierarchies, levels, and attributes. A cube has dimensionality; that is, it is associated with its list of dimensions.
The primary workspace object that implements a logical cube is a workspace dimension referred to as the cubedef dimension. The values of this dimension are the names of the cube's dimensions.
Secondary workspace objects that implement a logical cube are an aggmap (referred to as the comspec aggmap) and a composite (referred to as the loopspec composite).
For more information about these objects, see "Cube Objects".
The primary workspace object that implements a logical measure is a workspace variable, formula, or relation referred to as the measuredef object. The values of this object are the values of the logical measure.
The only secondary workspace object for a measure is the compspec aggmap for its cube.
For more information about these objects, see "Measure Objects".
The primary workspace object that implements a logical dimension is a workspace dimension referred to as the dimdef dimension. The values of this dimension are the values of the logical dimension.
Hierarchies and levels of a dimension do not have primary objects of their own. Instead, the following objects provide the implementation:
parentrel relation records the parent for each member of the dimension
levelrel relation records the level for each member of the dimension
The primary workspace object that implements an attribute of a dimension is a workspace variable, formula, or relation referred to as the attrdef object. The values of this object are the values of the logical attribute.
For more information about these objects, see "Dimension Objects".
Each standard form workspace object belongs to one of four classes:
Implementation class. Objects in this class implement the logical model. They include all the workspace objects described in the section "Logical Model and Workspace Objects", for example the cubedef, measuredef, dimdef, and hierlist objects.
Catalogs class. Objects in this class hold information about the logical model. They include a list of all the cubes in the workspace, a list of all the measures in the workspace, a list of all the dimensions in the workspace, and other lists that can facilitate the work of various utilities.
Features class. Objects in this class hold information about specific objects in the logical model. For example, one object stores the descriptions of all the logical objects, while another indicates whether the object is intended to be visible to the user.
Extensions class. Objects in this class are defined and maintained by the Oracle OLAP utilities. They are proprietary extensions to the standard form, and there is no commitment on the part of Oracle to maintain them from release to release.
Do not define, modify, or depend on objects in the Extensions class.
There are no restrictions on the names of the workspace objects that implement a standard form logical model, other than the rules imposed by the OLAP DML. For logical objects, however, standard form imposes strict naming rules. This is because the utilities that depend on standard form reference objects by their logical names.
Standard form naming conventions for logical names are consistent with those of the Oracle Database. They establish name spaces within which logical names must be unique, and they provide rules for constructing full names to reflect the name space organization. Logical names are sometimes referred to as "simple logical names" in order to distinguish them from full names.
In general, the simple logical name for an object, such as a cube or dimension, conforms to the rules for a SQL simple expression, with minor differences. The rules for standard form logical names require that a name:
Have 1 to 30 bytes.
Cannot be an Oracle reserved word.
Is not case-sensitive.
Cannot contain quotation marks.
Must begin with an alphabetic character from your database character set.
Must contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). However, Oracle strongly discourages you from using the dollar or pound sign. If your database character set contains multi byte characters, Oracle recommends that you include at least one single-byte character in each logical name.
The AW$LOGICAL_NAME property of a workspace object contains the simple logical name of the object that it implements. An example of a simple logical name is PRODUCT.
Standard form naming conventions impose an organization of logical objects that defines the following name spaces:
Schema. The logical names of cubes and dimensions must be unique within the schema that owns the analytic workspace.
Cube. The logical names of measures must be unique within a given cube.
Dimension. The logical names of hierarchies must be unique within a dimension. The logical names of levels must be unique within a dimension. The logical names of attributes must be unique within a dimension. Within a given dimension, a hierarchy can have the same name as a level or attribute.
The name space organization reflects an ownership, or parent, relationship among the logical objects. For example, a measure has a cube as its parent object, and an attribute has a dimension as its parent object. The AW$PARENT_NAME property on workspace objects records these relationships.
Because simple logical names are not unique outside their name space, standard form conventions specify a full name for each logical object. This full name includes the simple logical name, but also indicates the name space to which the object belongs and its object type. The following is an example of a full name for an attribute whose simple name is TIME_SPAN and whose parent object is a dimension called TIME.
GLOBAL_AW.TIME.TIME_SPAN.ATTRIBUTE
The final component of a full name is the object type. In this example, it is ATTRIBUTE. All the possible types are listed in the all_objtypes dimension, which is described in "ALL_OBJTYPES Dimension".
Full names are used in the catalog class objects that list various object types. For example, the values of the all_dimensions, all_cubes, and all_attributes dimensions are the full names of logical objects.
The section "Properties of Workspace Objects" introduced the use of properties in the standard form. Properties are the primary method by which logical objects are implemented by workspace objects. The properties are created on the workspace objects using the OLAP DML PROPERTY command.
Workspace objects in the standard form have well-defined properties that fall into three groups:
Properties specific to implementation class objects.
System properties on all workspace objects.
These properties are created and given values by Oracle OLAP utilities, either DBMS_AWM or the utilities offered by Analytic Workspace Manager. You must never modify or delete these properties.
Role property on all workspace objects.
All objects that are in the standard form have a property called AW$ROLE. It indicates the role (or function) that is played by the object in the standard form.
Properties for the logical name and parent name are on all implementation class objects. Three additional properties might or might not be present depending on the role of the object.
Table A-1 lists the implementation class properties and describes each one.
Table A-1 Implementation Class Properties
| Property | Description |
|---|---|
AW$LOGICAL_NAME |
The simple logical name of the logical object that is implemented by this workspace object. The value is set only for objects whose role is CUBEDEF, MEASUREDEF, DIMDEF, and ATTRDEF. The property exists, but the value is NA, for all other roles in the implementation class. |
AW$PARENT_NAME |
The simple logical name of the parent of the logical object that is implemented by this workspace object. The value is set for all implementation class objects except for those whose roles are CUBEDEF and DIMDEF. The value is NA for these two, because they have no parent. |
AW$LOOPSPEC |
For objects with role CUBEDEF, the name of the composite for the cube. This is the name of a workspace object, not the logical name of an object. For all other roles, this property is missing. |
AW$COMPSPEC |
For objects with role MEASUREDEF, the name of the AGGMAP object for the measure. This is the name of a workspace object, not the logical name of an object. For all other roles, this property is missing. |
AW$TYPE |
For objects with role DIMDEF and ATTRDEF, the type of the dimension or attribute. For all other roles, this property is missing.
If the role is If the role is |
All workspace objects that are part of the standard form have four system properties.
Table A-2 lists the system properties and describes each one.
Table A-2 System Properties
| Property | Description |
|---|---|
AW$CLASS |
The class of the workspace object. Possible values are IMPLEMENTATION, CATALOGS, FEATURES, and EXTENSIONS. For a description of these classes, see "Classes of Workspace Objects". |
AW$CREATEDBY |
The entity that created the workspace object. For example, if it was created by DBMS_AWM, then the value is AW$CREATE. |
AW$LASTMODIFIED |
The date and time when the workspace object was last registered. |
AW$STATE |
The state of the workspace object with respect to the standard form, for example, VALID_MEMBER. |
All workspace objects that are part of the standard form have a role property.
Table A-3 describes the role property.
Table A-3 Role Property
| Property | Description |
|---|---|
AW$ROLE |
The role (that is, function) that is performed by this object. The possible values are different for each object class. For information on property values, see "Role Property Values for Implementation Class Objects", "Role Property Values for Catalogs Class Objects", "Role Property Values for Features Class Objects", and "Role Property Values for Extensions Class Objects". |
The AW$ROLE property indicates the function (that is, role) that is performed by the workspace object. For implementation class objects, roles indicate fundamental building blocks of the logical model, such as cubes, measures, and dimensions.
There can be several implementation class objects that have the same role in a standard form workspace. For example, there are several objects with the role of DIMDEF because there is one such object for each dimension in the logical model.
Table A-4 lists the possible values and describes each role.
Table A-4 Role Property Values: Implementation Class
| Role Property Value | Role Description |
|---|---|
CUBEDEF |
Implements a cube whose logical name is in the AW$LOGICAL_NAME property. For information about objects with this role, see "Cubedef Dimension". |
MEASUREDEF |
Implements a measure whose logical name is in the AW$LOGICAL_NAME property. For information about objects with this role, see "Measuredef Object". |
DIMDEF |
Implements a dimension whose logical name is in the AW$LOGICAL_NAME property. For information about objects with this role, see "Dimdef Dimension". |
HIERLIST |
Lists the names of the hierarchies of the dimension whose name is in the AW$PARENT_NAME property. For information about objects with this role, see "Hierlist Dimension". |
LEVELLIST |
Lists the names of the levels of the dimension whose name is in the AW$PARENT_NAME property. For information about objects with this role, see "Levellist Dimension". |
MEMBER_LEVELREL |
Records the level for each member of the dimension whose name is in the AW$PARENT_NAME property. For information about objects with this role, see "Member_Levelrel Relation". |
MEMBER_PARENTREL |
Records the parent for each member of the dimension whose name is in the AW$PARENT_NAME property. For information about objects with this role, see "Member_Parentrel Relation". |
HIER_LEVELS |
Lists the levels that are included in each hierarchy of the dimension whose name is in the AW$PARENT_NAME property. For information about objects with this role, see "Hier_Levels Valueset". |
ATTRDEF |
Implements an attribute whose logical name is in the AW$LOGICAL_NAME property. For information about objects with this role, see "Attrdef Object". |
The AW$ROLE property indicates the function (or role) that is performed by the workspace object. For catalogs class objects, the objects with various roles provide information about the logical model such as a list of cubes, a list of object types, or a list of measures.
There is only one catalogs class object with a given role in a standard form workspace. For example, there is only one object that lists all the dimensions in the workspace.
Table A-5 lists the possible values and describes each role.
Table A-5 Role Property Values: Catalogs Class
| Role Property Value | Role Description |
|---|---|
ALL_OBJECTS |
Lists the full names of all the objects that have been registered with the standard form in this workspace. For information about the object with this role, see "ALL_OBJECTS Dimension". |
ALL_CUBES |
Lists the full names of all the cubes that have been registered with the standard form in this workspace. For information about the object with this role, see "ALL_CUBES Dimension". |
ALL_MEASURES |
Lists the full names of all the measures that have been registered with the standard form in this workspace. For information about the object with this role, see "ALL_MEASURES Dimension". |
ALL_DIMENSIONS |
Lists the full names of all the dimensions that have been registered with the standard form in this workspace. For information about the object with this role, see "ALL_DIMENSIONS Dimension" |
ALL_HIERARCHIES |
Lists the full names of all the hierarchies that have been registered with the standard form in this workspace. For information about the object with this role, see "ALL_HIERARCHIES Dimension". |
ALL_LEVELS |
Lists the full names of all the levels that have been registered with the standard form in this workspace. For information about the object with this role, see "ALL_LEVELS Dimension". |
ALL_ATTRIBUTES |
Lists the full names of all the attributes that have been registered with the standard form in this workspace. For information about the object with this role, see "ALL_ATTRIBUTES Dimension". |
ALL_OBJTYPES |
Lists types of objects currently supported by the standard form: CUBE, MEASURE, DIMENSION, HIERARCHY, LEVEL, and ATTRIBUTE. For information about the object with this role, see "ALL_OBJTYPES Dimension". |
ALL_DESCTYPES |
Lists the types of descriptions currently supported by the standard form: SHORT, LONG, and PLURAL. For information about the object with this role, see "ALL_DESCTYPES Dimension". |
ALL_ATTRTYPES |
Lists all the attribute types that are currently supported by the standard form. These are valid values for the AW$TYPE property of an object with the ATTRDEF role. For information about the object with the ALL_ATTRTYPES role, see "ALL_ATTRTYPES Dimension". |
AW_ROLES |
Lists all values for the AW$ROLE property currently supported by the standard form. The list includes roles for objects of all classes. For information about the object with this role, see "AW_ROLES Dimension". |
ALL_LANGUAGES |
Lists the names of all the languages that a DBA has included in the workspace. For information about the object with this role, see "ALL_LANGUAGES Dimension". |
CUBE_MEASURES |
Lists the full names of the measures that belong to each cube in the workspace. For information about the object with this role, see "CUBE_MEASURES Valueset". |
DIM_HIERARCHIES |
Lists the full names of the hierarchies that belong to each dimension in the workspace. For information about the object with this role, see "DIM_HIERARCHIES Valueset". |
DIM_LEVELS |
Lists the full names of the levels that belong to each dimension in the workspace. For information about the object with this role, see "DIM_LEVELS Valueset". |
DIM_ATTRIBUTES |
Lists the full names of the attributes that belong to each dimension in the workspace. For information about the object with this role, see "DIM_ATTRIBUTES Valueset". |
AW_NAMES |
Records the name of the workspace object that implements each logical cube, measure, dimension, and attribute. For other logical objects, there is no single corresponding workspace object, so the value is NA. For information about the object with this role, see "AW_NAMES Variable". |
AW_COMPSPECS |
Records, for each dimension, the names of all AGGMAP objects that reference the dimension. For information about the object with this role, see "AW_COMPSPECS Variable". |
AW_LOOPSPECS |
Records, for each cube, the name of its composite. For information about the object with this role, see "AW_LOOPSPECS Variable". |
The AW$ROLE property indicates the function (or role) that is performed by the workspace object. For features class objects, roles provide various types of supplementary data for logical objects such as descriptions.
For many roles, there is a single features class object in a standard form workspace. However, for the roles that have MEMBER in their names, there is one object for each dimension.
Table A-6 lists the possible values and describes each role that applies to features class objects.
Table A-6 Role Property Values: Features Class
| Role Property Value | Role Description |
|---|---|
ALL_DESCRIPTIONS |
Records short, long, and plural descriptions for all objects. For information about the object with this role, see "ALL_DESCRIPTIONS Variable". |
ATTR_INHIER |
Indicates whether a given attribute is associated with a given hierarchy. For information about the object with this role, see "ATTR_INHIER Variable". |
DEFAULT_HIER |
Records the full name of the default hierarchy for each dimension. For information about the object with this role, see "DEFAULT_HIER Relation". |
MEMBER_CREATEDBY |
Records the entity that created each member of a given dimension. For information about the object with this role, see "Member_Createdby Variable". |
MEMBER_FAMILYREL |
Records the family relation for each hierarchy of a given dimension. For information about the object with this role, see "Member_Familyrel Relation". |
MEMBER_GID |
Records the grouping id for each hierarchy of a given dimension. For information about the object with this role, see "Member_Gid Variable". |
MEMBER_INHIER |
Indicates whether a given member of a dimension is in a given hierarchy. For information about the object with this role, see "Member_Inhier Variable". |
OBJ_CREATEDBY |
Records the entity that created each object. For information about the object with this role, see "OBJ_CREATEDBY Variable". |
OBJ_STATE |
Records the current state of each object that has ever been registered. For information about the object with this role, see "OBJ_STATE Variable". |
VERSION |
Records the number of the standard form version under which the workspace is being managed. For information about the object with this role, see "VERSION Variable". |
VISIBLE |
Indicates whether a given object should be made visible to the user by Oracle OLAP enabling utilities. For information about the object with this role, see "VISIBLE Variable". |
The AW$ROLE property indicates the function (or role) that is performed by the workspace object. For Extensions class objects, roles are for internal use of Oracle OLAP utilities such as DBMS_AWM and the enablers.
DBAs and users must not create, modify, or depend on objects that are in the Extensions class. The AW$ROLE property, and all properties, for objects in this class are for proprietary use only. Oracle makes no commitment to maintain the roles and relationships of these objects.
Because the standard form conventions have no conventions that govern the names of workspace objects, documentation cannot refer to the objects by name. Instead, the objects are discussed using the values of their AW$ROLE properties as descriptors.
For example, we refer to the cubedef dimension, the aw_names variable, and the default_hier relation. These references are to the workspace objects whose AW$ROLE property is set to CUBEDEF, AW_NAMES, and DEFAULT_HIER, respectively. The actual names of the workspace objects for most classes are typically similar to, but not identical to, their roles.
The sections that follow describe each object that has a role in the standard form conventions.
The objects in the implementation class provide the implementation for the logical objects in a given workspace. In general, they hold the data that users see as dimensions and measures. Implementation class objects differ from workspace to workspace. For example, one workspace might have measures called SALES and COST, while another workspace might have measures called BUDGET and ACTUAL.
The cubedef, measuredef, and dimdef objects implement cubes, measures, and dimensions respectively. In addition, each of these objects have implementation class helper objects. An overview of the objects is provided in the section "Logical Model and Workspace Objects".
The rest of this section describes each of the implementation class objects. Note that the examples in this section show the properties required by the standard form. If you examine a workspace that was created by Analytic Workspace Manager or the DBMS_AWM package, you might find some additional properties on various objects. These are not required for compliance with the standard form.
For information about the values that should be assigned to the properties, see Chapter 8.
To list all the objects that have a given role, limit the NAME dimension to all the objects that have that role and then report the values of the NAME dimension. For example, execute the following OLAP DML commands to list all the cubedef objects.
LIMIT name TO OBJ(PROPERTY 'AW$ROLE') EQ 'CUBEDEF' REPORT name NAME -------------- UNITS_CUBE PRICE_CUBE
A cube is implemented by a cubedef dimension. It also has a loopspec composite.
A logical cube is implemented by a workspace dimension that has the value CUBEDEF in its AW$ROLE property. The values of a given cubedef dimension are the names of the logical dimensions of the cube.
A cubedef dimension has no parent, so its AW$PARENT_NAME property is set to NA. A logical cube is the parent of the measures that belong to it.
The following is a full description of a cubedef dimension called UNITS_CUBE.
FULLDSC units_cube DEFINE UNITS_CUBE DIMENSION TEXT LD IMPLEMENTATION UNITS_CUBE Cube PROPERTY 'AGGMAPLIST' 'GLOBAL_AW.GLOBAL!UNITS_CUBE_AGGMAP_AWCREATEDDEFAULT_1' PROPERTY 'AW$CLASS' 'IMPLEMENTATION' PROPERTY 'AW$CREATEDBY' 'AW$CREATE' PROPERTY 'AW$LASTMODIFIED' '03SEP03_15:28:35' PROPERTY 'AW$LOADPRGS' 'GLOBAL_AW.GLOBAL!___GET.CUBE.DATA_UNITS_CUBE_1' PROPERTY 'AW$LOGICAL_NAME' 'UNITS_CUBE' PROPERTY 'AW$LOOPSPEC' 'GLOBAL_AW.GLOBAL!UNITS_CUBE_COMPOSITE' PROPERTY 'AW$PARENT_NAME' NA PROPERTY 'AW$ROLE' 'CUBEDEF'
The following is a report that shows the values of the UNITS_CUBE dimension. The values are the names of the dimdef dimensions that implement the cube's logical dimensions.
REPORT units_cube UNITS_CUBE -------------- CHANNEL CUSTOMER PRODUCT TIME
A logical cube has a loopspec composite, which facilitates efficient data access for the cube's measures. The loopspec composite is particularly useful when looping through sparse data is required. For information about composites, see the Oracle OLAP DML Reference.
Typically, the loopspec composite includes all the dimensions of the cube, except for any time dimension that might be present. The parent of a loopspec is the logical cube that it supports.
The following is a full description of a loopspec composite for the logical cube called UNITS_CUBE. The composite includes all the dimensions that are listed as values of the cubedef dimension, except for the TIME dimension.
FULLDSC units_cube_composite DEFINE UNITS_CUBE_COMPOSITE COMPOSITE <CUSTOMER PRODUCT CHANNEL> LD IMPLEMENTATION Composite for UNITS_CUBE cube PROPERTY 'AW$CLASS' 'IMPLEMENTATION' PROPERTY 'AW$CREATEDBY' 'AW$CREATE' PROPERTY 'AW$LASTMODIFIED' '03SEP03_15:28:40' PROPERTY 'AW$PARENT_NAME' 'UNITS_CUBE' PROPERTY 'AW$ROLE' 'LOOPSPEC' PROPERTY 'AW$STATE' 'CREATED'
The following is a report that shows the first nine values of this loopspec composite.
REPORT units_cube_composite CUSTOMER PRODUCT CHANNEL ---------- ---------- ---------- 51 13 2 51 14 2 51 15 2 51 16 2 65 17 2 65 18 2 65 19 2 65 20 2 61 20 2 . . . . . . . . .
A measure is implemented by a measuredef object. A measure also has a compspec aggmap, which provides aggregation rules for the measure.
A logical measure is implemented by a workspace object that has the value MEASUREDEF in its AW$ROLE property. The measuredef object can be a variable, formula, or relation.
The values of the measuredef object are the values of the logical measure, and its parent is the logical cube.
The following is a full description of a measuredef object for the logical measure called UNITS. The object is a formula that is dimensioned by the dimensions of the parent cube, which is called UNITS_CUBE. The formula includes fully qualified object names, but this type of specification is optional.
FULLDSC units DEFINE UNITS FORMULA DECIMAL <TIME CUSTOMER PRODUCT CHANNEL> LD IMPLEMENTATION UNITS Measure in UNITS_CUBE Cube EQ aggregate( GLOBAL_AW.GLOBAL!UNITS_VARIABLE using GLOBAL_AW.GLOBAL!UNITS_CUBE_AGGMAP_AWCREATEDDEFAULT_1) PROPERTY 'AW$CLASS' -'IMPLEMENTATION' PROPERTY 'AW$COMPSPEC' 'GLOBAL_AW.GLOBAL!UNITS_CUBE_AGGMAP_AWCREATEDDEFAULT_1' PROPERTY 'AW$CREATEDBY' 'AW$CREATE' PROPERTY 'AW$LASTMODIFIED' '03SEP03_15:28:41' PROPERTY 'AW$LOGICAL_NAME' 'UNITS' PROPERTY 'AW$PARENT_NAME' 'UNITS_CUBE' PROPERTY 'AW$ROLE' 'MEASUREDEF' PROPERTY 'AW$STATE' 'CREATED'
The AW$COMPSPEC property holds the name of the measure's compspec aggmap. If this property value is NA, then measure values that are not stored will not be calculated; they will be NA.
A logical measure can have a compspec object, which is an aggmap that specifies the rules for calculating aggregates. For information about aggmaps, see the Oracle OLAP DML Reference.
The following is a full description of a compspec aggmap for the logical measure called UNITS. The aggmap includes fully qualified object names, but this type of specification is optional.
FULLDSC units_cube_aggmap_awcreateddefault_1 DEFINE UNITS_CUBE_AGGMAP_AWCREATEDDEFAULT_1 AGGMAP LD IMPLEMENTATION Default aggmap created by dbms_awm.refresh_awcube for UNITS_CUBE cube AGGMAP RELATION GLOBAL_AW.GLOBAL!CHANNEL_PARENTREL OPERATOR SUM PRECOMPUTE(NA) RELATION GLOBAL_AW.GLOBAL!CUSTOMER_PARENTREL OPERATOR SUM PRECOMPUTE(NA) RELATION GLOBAL_AW.GLOBAL!PRODUCT_PARENTREL OPERATOR SUM PRECOMPUTE(NA) RELATION GLOBAL_AW.GLOBAL!TIME_PARENTREL OPERATOR SUM PRECOMPUTE(NA) AGGINDEX NO END PROPERTY 'AW$CLASS' 'IMPLEMENTATION' PROPERTY 'AW$CREATEDBY' 'AW$CREATE' PROPERTY 'AW$LASTMODIFIED' '03SEP03_15:29:24' PROPERTY 'AW$PARENT_NAME' 'UNITS_CUBE' PROPERTY 'AW$ROLE' 'COMPSPEC' PROPERTY 'AW$STATE' 'CREATED'
A dimension is implemented by a dimdef object. In addition, a dimension has one each of the following supporting objects:
Hierlist dimension
Levellist dimension
Member_levelrel relation
Member_parentrel relation
Hier_levels valueset
Optionally, a dimension can have one or more attrdef objects.
For each of these objects, its AW$ROLE property records the object's function. For example, the AW$ROLE property of a hierlist dimension is set to HIERLIST. In addition, the AW$PARENT property for each of these objects contains the name of the logical dimension to which the object belongs.
If a dimension does not have a hierarchy, or it does not have levels, or it has neither, then these supporting objects exist but they are not populated.
A logical dimension is implemented by a workspace dimension that has the value DIMDEF in its AW$ROLE property. The values of a given dimdef dimension are the values of the logical dimension.
A dimdef dimension has no parent, so its AW$PARENT_NAME property is set to NA. The AW$TYPE property is set to TIME for time dimensions, and it is set to NA for all other dimensions.
The following is a full description of a dimdef dimension for the logical dimension called PRODUCT.
FULLDSC product DEFINE PRODUCT DIMENSION TEXT LD IMPLEMENTATION PRODUCT Dimension PROPERTY 'AW$CLASS' 'IMPLEMENTATION' PROPERTY 'AW$CREATEDBY' 'AW$CREATE' PROPERTY 'AW$LASTMODIFIED' '03SEP03_15:27:42' PROPERTY 'AW$LOGICAL_NAME' 'PRODUCT' PROPERTY 'AW$PARENT_NAME' NA PROPERTY 'AW$ROLE' 'DIMDEF' PROPERTY 'AW$STATE' 'ACTIVE' PROPERTY 'AW$TYPE' NA
The following is a report that shows sample values of this dimdef dimension from all the levels. This is an embedded totals dimension. In this example, the use of surrogate keys ensures uniqueness among the values from all levels. When surrogate keys are not used, another strategy must be used to insure uniqueness. For example, you can use the level as a prefix, such as ITEM.46 and FAMILY.7. The example includes an attrdef variable and member_levelrel relation to clarify the results.
LIMIT product TO '46'
LIMIT product ADD ANCESTORS USING product_parentrel
REPORT DOWN product W 25 <product_long_description product_levelrel>
ALL_LANGUAGES: AMERICAN_AMERICA
-----------------PRODUCT_HIERLIST------------------
------------------PRODUCT_ROLLUP-------------------
PRODUCT PRODUCT_LONG_DESCRIPTION PRODUCT_LEVELREL
-------------- ------------------------- -------------------------
46 Standard Mouse ITEM
7 Accessories FAMILY
3 Software/Other CLASS
1 Total Product TOTAL_PRODUCT
A hierlist dimension lists the names of the hierarchies of its parent dimension. That is, the values of the hierlist dimension are the names of hierarchies, such as the CALENDAR and FISCAL hierarchies for a time dimension. The hierarchies do not have one-to-one implementations as workspace objects, so the names refer to logical hierarchies not to workspace objects.
The following is a full description of a hierlist dimension called TIME_HIERLIST.
DEFINE TIME_HIERLIST DIMENSION TEXT LD IMPLEMENTATION List of Hierarchies for TIME PROPERTY 'AW$CLASS' 'IMPLEMENTATION' PROPERTY 'AW$CREATEDBY' 'AW$CREATE' PROPERTY 'AW$LASTMODIFIED' '03SEP03_15:27:47' PROPERTY 'AW$PARENT_NAME' 'TIME' PROPERTY 'AW$ROLE' 'HIERLIST' PROPERTY 'AW$STATE' 'CREATED'
The following is a report that shows the values of this hierlist dimension. TIME has one hierarchy, which is named CALENDAR.
REPORT time_hierlist TIME_HIERLIST -------------- CALENDAR
A levellist dimension lists the names of the levels of its parent dimension. That is, the values of the levellist dimension are the names of levels, such as the CITY, STATE, and COUNTRY levels for a geography dimension. The levels do not have one-to-one implementations as workspace objects, so the names refer to logical levels not to workspace objects. The logical level for each dimension value is identified in the dimension's MEMBER_LEVELREL relation.
The following is a full description of a levellist dimension called TIME_LEVELLIST.
FULLDSC time_levellist DEFINE TIME_LEVELLIST DIMENSION TEXT LD IMPLEMENTATION List of levels for TIME PROPERTY 'AW$CLASS' 'IMPLEMENTATION' PROPERTY 'AW$CREATEDBY' 'AW$CREATE' PROPERTY 'AW$LASTMODIFIED' '03SEP03_15:27:47' PROPERTY 'AW$LOGICAL_NAME' NA PROPERTY 'AW$PARENT_NAME' 'TIME' PROPERTY 'AW$ROLE' 'LEVELLIST' PROPERTY 'AW$STATE' 'CREATED'
The following is a report that shows the values of this levellist dimension. The levels are YEAR, QUARTER, and MONTH.
REPORT time_levellist TIME_LEVELLIST -------------- YEAR QUARTER MONTH
A member_levelrel relation records the level for each value of the relation's parent dimension. For example, for a geography dimension, the member_levelrel relation might record the fact that BOSTON belongs to the CITY level and IOWA belongs to the STATE level.
The following is a full description of a member_levelrel relation called TIME_LEVELREL.
FULLDSC time_levelrel DEFINE TIME_LEVELREL RELATION TIME_LEVELLIST <TIME> LD IMPLEMENTATION Level of each dimension member for TIME PROPERTY 'AW$CLASS' 'IMPLEMENTATION' PROPERTY 'AW$CREATEDBY' 'AW$CREATE' PROPERTY 'AW$LASTMODIFIED' '03SEP03_15:27:47' PROPERTY 'AW$PARENT_NAME' 'TIME' PROPERTY 'AW$ROLE' 'MEMBER_LEVELREL' PROPERTY 'AW$STATE' 'CREATED'
The following is a report that shows sample values of this member_levelrel relation. The levels are MONTH, QUARTER, and YEAR.
LIMIT time TO '75' LIMIT time ADD ANCESTORS USING time_parentrel REPORT DOWN time W 15 time_levelrel TIME TIME_LEVELREL -------------- --------------- 75 MONTH 83 QUARTER 85 YEAR
A member_parentrel relation records the parent dimension value for each value of the relation's parent dimension. For example, for a geography dimension, the member_parentrel relation might record the fact that the parent of BOSTON is MASSACHUSETTS, and the parent of IOWA is USA.
The following is a full description of a member_parentrel relation called TIME_PARENTREL.
FULLDSC time_parentrel DEFINE TIME_PARENTREL RELATION TIME <TIME TIME_HIERLIST> PROPERTY 'AW$CLASS' 'IMPLEMENTATION' PROPERTY 'AW$CREATEDBY' 'AW$CREATE' PROPERTY 'AW$LASTMODIFIED' '03SEP03_15:27:47' PROPERTY 'AW$PARENT_NAME' 'TIME' PROPERTY 'AW$ROLE' 'MEMBER_PARENTREL' PROPERTY 'AW$STATE' 'CREATED'
The following is a report that shows the values of this member_parentrel relation. The parent of a given value can be different, depending on which hierarchy is being considered.
REPORT DOWN time W 20 time_parentrel
---TIME_PARENTREL---
---TIME_HIERLIST----
TIME CALENDAR
-------------- --------------------
75 83
83 85
85 NA
A hier_levels valueset lists the levels that are included in each hierarchy of the parent dimension.
The following is a full description of a hier_levels valueset called TIME_HIER_LEVELS.
FULLDSC time_hier_levels DEFINE TIME_HIER_LEVELS VALUESET TIME_LEVELLIST <TIME_HIERLIST> LD IMPLEMENTATION Ordered from Bottom to Top list of levels in a hierarchy for TIME PROPERTY 'AW$CLASS' 'IMPLEMENTATION' PROPERTY 'AW$CREATEDBY' 'AW$CREATE' PROPERTY 'AW$LASTMODIFIED' '03SEP03_15:27:47' PROPERTY 'AW$PARENT_NAME' 'TIME' PROPERTY 'AW$ROLE' 'HIER_LEVELS' PROPERTY 'AW$STATE' 'CREATED'
The following command present the list of levels for each hierarchy, as recorded in this hier_levels valueset.
REPORT W 25 VALUES(time_hier_levels)
TIME_HIERLIST VALUES(TIME_HIER_LEVELS)
-------------- -------------------------
CALENDAR MONTH
QUARTER
YEAR
A logical attribute is implemented by a workspace object that has the value attrdef in its AW$ROLE property. The attrdef object can be a variable, formula, or relation. The values of the attrdef object are the values of the logical attribute, and its parent is the logical dimension to which it belongs.
The AW$TYPE property indicates whether Oracle OLAP has a special use for the attribute. Property values that indicate such a special use are DEFAULT_ORDER, END_DATE, TIME_SPAN, MEMBER_LONG_DESCRIPTION, MEMBER_SHORT_DESCRIPTION, and MEMBER_VISIBLE. If the value is USER or NA, then the attribute has no special meaning for Oracle OLAP.
An attrdef object must be dimensioned by its parent dimdef dimension. In addition, it can be dimensioned by the hierlist dimension or the ALL_LANGUAGES dimension, or both.
The following is a full description of an attrdef object called TIME_LONG_DESCRIPTION. This long description attribute is implemented as a variable.
FULLDSC time_long_description DEFINE TIME_LONG_DESCRIPTION VARIABLE TEXT <TIME TIME_HIERLIST ALL_LANGUAGES> LD IMPLEMENTATION LONG_DESCRIPTION Attribute for TIME Dimension PROPERTY 'AW$CLASS' 'IMPLEMENTATION' PROPERTY 'AW$CREATEDBY' 'AW$CREATE' PROPERTY 'AW$LASTMODIFIED' '03SEP03_15:28:25' PROPERTY 'AW$LOGICAL_NAME' 'LONG_DESCRIPTION' PROPERTY 'AW$PARENT_NAME' 'TIME' PROPERTY 'AW$ROLE' 'ATTRDEF' PROPERTY 'AW$STATE' 'CREATED' PROPERTY 'AW$TYPE' 'Long Description'
The following is a report that shows selected values of this attrdef object at each level.
LIMIT time TO time_levelrel EQ 'YEAR'
LIMIT time KEEP LAST 1
LIMIT time ADD DESCENDANTS USING time_parentrel
REPORT DOWN time W 25 time_long_description
ALL_LANGUAGES: AMERICAN_AMERICA
--TIME_LONG_DESCRIPTION--
------TIME_HIERLIST------
TIME CALENDAR
-------------- -------------------------
119 2004
115 Q1-04
116 Q2-04
103 Jan-04
104 Feb-04
105 Mar-04
106 Apr-04
107 May-04
108 Jun-04
Catalogs class objects hold information about the logical objects in the workspace. Catalog class objects include a list of all the cubes in the workspace, a list of all the measures in the workspace, a list of all the dimensions in the workspace, and other lists that can facilitate the work of various utilities. A given workspace has a single instance of each Catalog class object. DBMS_AWM creates these objects using the role as the name, so that the all_languages dimension is named ALL_LANGUAGES. For this reason, the names of objects in the CATALOGS class are shown here in capital letters to indicate actual names.
In this section, Catalogs class objects are discussed in the following groups:
The Catalogs class includes a set of dimensions, each of which lists all the objects of a given kind. For example, the ALL_MEASURES dimension lists all the logical measures.
The ALL_CUBES dimension lists the full names of all the logical cubes in the workspace. The following is a full description of an ALL_CUBES dimension.
FULLDSC all_cubes DEFINE ALL_CUBES DIMENSION TEXT LD CATALOGS List of all cubes in the aw PROPERTY 'AW$CLASS' 'CATALOGS' PROPERTY 'AW$CREATEDBY' 'AW$CREATE' PROPERTY 'AW$LASTMODIFIED' '04DEC02_13:09:14' PROPERTY 'AW$ROLE' 'ALL_CUBES' PROPERTY 'AW$STATE' 'CREATED'
The following is a report of the values of this ALL_CUBES dimension.
REPORT W 30 all_cubes ALL_CUBES ------------------------------ GLOBAL_AW.UNITS_CUBE.CUBE GLOBAL_AW.PRICE_CUBE.CUBE
The ALL_MEASURES dimension lists the full names of all the logical measures in the workspace.
A full description for this dimension is similar to those presented for the ALL_CUBES dimension in "ALL_CUBES Dimension". The following is a report of the values of an ALL_MEASURES dimension.
REPORT W 40 all_measures ALL_MEASURES ---------------------------------------- GLOBAL_AW.UNITS_CUBE.UNITS.MEASURE GLOBAL_AW.PRICE_CUBE.UNIT_COST.MEASURE GLOBAL_AW.PRICE_CUBE.UNIT_PRICE.MEASURE
The ALL_DIMENSIONS dimension lists the full names of all the logical dimensions in the workspace.
A full description for this dimension is similar to those presented for the ALL_CUBES dimension in "ALL_CUBES Dimension". The following is a report of the values of an ALL_DIMENSIONS dimension.
REPORT W 40 all_dimensions ALL_DIMENSIONS ---------------------------------------- GLOBAL_AW.CHANNEL.DIMENSION GLOBAL_AW.CUSTOMER.DIMENSION GLOBAL_AW.PRODUCT.DIMENSION GLOBAL_AW.TIME.DIMENSION
The ALL_HIERARCHIES dimension lists the full names of all the hierarchies in the workspace.
A full description for this dimension is similar to those presented for the ALL_CUBES dimension in "ALL_CUBES Dimension". The following is a report of the values of an ALL_HIERARCHIES dimension.
REPORT W 45 all_hierarchies ALL_HIERARCHIES --------------------------------------------- GLOBAL_AW.CHANNEL.CHANNEL_ROLLUP.HIERARCHY GLOBAL_AW.CHANNEL.AW$NONE.HIERARCHY GLOBAL_AW.CUSTOMER.SHIPMENTS_ROLLUP.HIERARCHY GLOBAL_AW.CUSTOMER.MARKET_ROLLUP.HIERARCHY GLOBAL_AW.CUSTOMER.AW$NONE.HIERARCHY GLOBAL_AW.PRODUCT.PRODUCT_ROLLUP.HIERARCHY GLOBAL_AW.PRODUCT.AW$NONE.HIERARCHY GLOBAL_AW.TIME.CALENDAR.HIERARCHY GLOBAL_AW.TIME.AW$NONE.HIERARCHY
Hierarchies with a simple name of AW$NONE indicate that a dimension has no hierarchy.
The ALL_LEVELS dimension lists the full names of all the levels in the workspace.
A full description for this dimension is similar to those presented for the ALL_CUBES dimension in "ALL_CUBES Dimension". The following is a report of the values of an ALL_LEVELS dimension.
REPORT W 40 all_levels ALL_LEVELS ---------------------------------------- GLOBAL_AW.CHANNEL.ALL_CHANNELS.LEVEL GLOBAL_AW.CHANNEL.CHANNEL.LEVEL GLOBAL_AW.CHANNEL.AW$NONE.LEVEL GLOBAL_AW.CUSTOMER.ALL_CUSTOMERS.LEVEL GLOBAL_AW.CUSTOMER.REGION.LEVEL GLOBAL_AW.CUSTOMER.WAREHOUSE.LEVEL GLOBAL_AW.CUSTOMER.TOTAL_MARKET.LEVEL GLOBAL_AW.CUSTOMER.MARKET_SEGMENT.LEVEL GLOBAL_AW.CUSTOMER.ACCOUNT.LEVEL GLOBAL_AW.CUSTOMER.SHIP_TO.LEVEL GLOBAL_AW.CUSTOMER.AW$NONE.LEVEL GLOBAL_AW.PRODUCT.TOTAL_PRODUCT.LEVEL GLOBAL_AW.PRODUCT.CLASS.LEVEL GLOBAL_AW.PRODUCT.FAMILY.LEVEL GLOBAL_AW.PRODUCT.ITEM.LEVEL GLOBAL_AW.PRODUCT.AW$NONE.LEVEL GLOBAL_AW.TIME.YEAR.LEVEL GLOBAL_AW.TIME.QUARTER.LEVEL GLOBAL_AW.TIME.MONTH.LEVEL GLOBAL_AW.TIME.AW$NONE.LEVEL
The ALL_ATTRIBUTES dimension lists the full names of all the attributes in the workspace.
A full description for this dimension is similar to those presented for the ALL_CUBES dimension in "ALL_CUBES Dimension". The following is a report of the values of an ALL_ATTRIBUTES dimension.
REPORT W 50 all_attributes ALL_ATTRIBUTES -------------------------------------------------- GLOBAL_AW.CHANNEL.LONG_DESCRIPTION.ATTRIBUTE GLOBAL_AW.CHANNEL.SHORT_DESCRIPTION.ATTRIBUTE GLOBAL_AW.CUSTOMER.LONG_DESCRIPTION.ATTRIBUTE GLOBAL_AW.CUSTOMER.SHORT_DESCRIPTION.ATTRIBUTE GLOBAL_AW.PRODUCT.LONG_DESCRIPTION.ATTRIBUTE GLOBAL_AW.PRODUCT.SHORT_DESCRIPTION.ATTRIBUTE GLOBAL_AW.PRODUCT.PACKAGE.ATTRIBUTE GLOBAL_AW.TIME.LONG_DESCRIPTION.ATTRIBUTE GLOBAL_AW.TIME.SHORT_DESCRIPTION.ATTRIBUTE GLOBAL_AW.TIME.END_DATE.ATTRIBUTE GLOBAL_AW.TIME.TIME_SPAN.ATTRIBUTE
The ALL_OBJECTS dimension lists the full names of all the logical objects in the workspace.
The following is a full description of an ALL_OBJECTS dimension.
FULLDSC all_objects DEFINE ALL_OBJECTS DIMENSION CONCAT (ALL_DIMENSIONS ALL_CUBES ALL_MEASURES ALL_HIERARCHIES ALL_LEVELS ALL_ATTRIBUTES) LD CATALOGS List of all objects in the aw PROPERTY 'AW$CLASS''CATALOGS' PROPERTY 'AW$CREATEDBY' 'AW$CREATE' PROPERTY 'AW$LASTMODIFIED' '04DEC02_13:07:35' PROPERTY 'AW$ROLE' 'ALL_OBJECTS' PROPERTY 'AW$STATE' 'CREATED'
ALL_OBJECTS is a concat dimension of the ALL_CUBES, ALL_MEASURES, ALL_HIERARCHIES, ALL_LEVELS, and ALL_ATTRIBUTES dimensions. Its dimension members are a concatenated list of the members of those dimensions, as shown by this example.
LIMIT all_cubes TO FIRST 2 LIMIT all_measures TO FIRST 2 LIMIT all_hierarchies TO FIRST 2 LIMIT all_levels TO FIRST 2 LIMIT all_attributes TO FIRST 2 LIMIT all_objects TO all_cubes LIMIT all_objects ADD all_measures LIMIT all_objects ADD all_hierarchies LIMIT all_objects ADD all_levels LIMIT all_objects ADD all_attributes REPORT W 70 all_objects ALL_OBJECTS ---------------------------------------------------------------------- <ALL_CUBES: GLOBAL_AW.UNITS_CUBE.CUBE> <ALL_CUBES: GLOBAL_AW.PRICE_CUBE.CUBE> <ALL_MEASURES: GLOBAL_AW.UNITS_CUBE.UNITS.MEASURE> <ALL_MEASURES: GLOBAL_AW.PRICE_CUBE.UNIT_COST.MEASURE> <ALL_HIERARCHIES: GLOBAL_AW.CHANNEL.CHANNEL_ROLLUP.HIERARCHY> <ALL_HIERARCHIES: GLOBAL_AW.CHANNEL.AW$NONE.HIERARCHY> <ALL_LEVELS: GLOBAL_AW.CHANNEL.ALL_CHANNELS.LEVEL> <ALL_LEVELS: GLOBAL_AW.CHANNEL.CHANNEL.LEVEL> <ALL_ATTRIBUTES: GLOBAL_AW.CHANNEL.LONG_DESCRIPTION.ATTRIBUTE> <ALL_ATTRIBUTES: GLOBAL_AW.CHANNEL.SHORT_DESCRIPTION.ATTRIBUTE>
The Catalogs class includes dimensions that list types and roles that are supported by the current version of the standard form. In addition, there is a dimension that lists the languages supported by the current analytic workspace.
The ALL_OBJTYPES dimension lists all the object types that are supported in the current version of the standard form. The following report lists the types.
REPORT all_objtypes ALL_OBJTYPES -------------- CUBE MEASURE DIMENSION LEVEL HIERARCHY ATTRIBUTE
The ALL_DESCTYPES dimension lists all the description types that are recognized in the current version of the standard form. The following report lists the types.
REPORT all_desctypes ALL_DESCTYPES -------------- SHORT LONG PLURAL
The ALL_ATTRTYPES dimension lists all the attribute types that are recognized in the current version of the standard form. The following report lists the types.
REPORT W 40 all_attrtypes ALL_ATTRTYPES ---------------------------------------- DEFAULT_ORDER END_DATE TIME_SPAN MEMBER_LONG_DESCRIPTION MEMBER_SHORT_DESCRIPTION MEMBER_VISIBLE USER
The AW_ROLES dimension lists all the roles that are recognized in the current version of the standard form. The following report lists the roles.
REPORT W 30 aw_roles AW_ROLES ------------------------------ LANGUAGEDEF ADTVIEWLIST ADTLIST ADTTBLLIST ADTREL ADTTBLREL ADTLMTMAP DIMDEF MEMBER_CREATEDBY LEVELLIST MEMBER_LEVELREL LEVEL_CREATEDBY LEVELCOLLIST LEVELCOLNUM LEVELCOLMAP HIERLIST HIER_CREATEDBY MEMBER_INHIER MEMBER_PARENTREL ATTRDEF SRCCOMPOSITE SRCLVLOWNER SRCLVLTBL SRCLVLCOL SRCLVLPNTCOL MEMBER_FAMILYREL HIER_LEVELS MEMBER_GID ALL_LANGUAGES ALL_DIMENSIONS ALL_CUBES ALL_MEASURES ALL_HIERARCHIES ALL_LEVELS ALL_ATTRIBUTES AW_ROLES ALL_DESCTYPES ALL_OBJTYPES ALL_OBJECTS AW_NAMES AW_COMPSPECS AW_LOOPSPECS
The ALL_LANGUAGES dimension lists all the languages that are implemented in the current analytic workspace. The following report lists the single language that is implemented in a sample workspace. Language names should follow Globalization Support standards.
REPORT W 30 all_languages ALL_LANGUAGES ------------------------------ AMERICAN_AMERICA
The Catalogs class includes valuesets that list the measures in each cube, as well as the hierarchies, levels, and attributes in each dimension. These lists are specific to a given workspace.
The CUBE_MEASURES valueset lists the measures that belong to each cube in the current analytic workspace. The valueset is dimensioned by ALL_CUBES, so that each cube has its own list. The following is a full description of a CUBE_MEASURES valueset in a sample workspace.
FULLDSC cube_measures DEFINE CUBE_MEASURES VALUESET ALL_MEASURES <ALL_CUBES> PROPERTY 'AW$CLASS' 'CATALOGS' PROPERTY 'AW$CREATEDBY' 'AW$CREATE' PROPERTY 'AW$LASTMODIFIED' '03SEP03_15:27:47' PROPERTY 'AW$ROLE' 'CUBE_MEASURES' PROPERTY 'AW$STATE' 'CREATED'
The following commands present the list of measures associated with each cube.
LCOLWIDTH=30 "Widen the label column
REPORT W 40 VALUES(cube_measures)
ALL_CUBES VALUES(CUBE_MEASURES)
------------------------------ ----------------------------------------
GLOBAL_AW.UNITS_CUBE.CUBE GLOBAL_AW.UNITS_CUBE.UNITS.MEASURE
GLOBAL_AW.PRICE_CUBE.CUBE GLOBAL_AW.PRICE_CUBE.UNIT_COST.MEASURE
GLOBAL_AW.PRICE_CUBE.UNIT_PRICE.MEASURE
The DIM_HIERARCHIES valueset lists the hierarchies that belong to each dimension in the current analytic workspace. The valueset is dimensioned by ALL_DIMENSIONS, so that each dimension has its own list. The following commands present the list of hierarchies for each dimension.
REPORT W 45 VALUES(dim_hierarchies)
ALL_DIMENSIONS VALUES(DIM_HIERARCHIES)
------------------------------ ---------------------------------------------
GLOBAL_AW.CHANNEL.DIMENSION GLOBAL_AW.CHANNEL.CHANNEL_ROLLUP.HIERARCHY
GLOBAL_AW.CUSTOMER.DIMENSION GLOBAL_AW.CUSTOMER.SHIPMENTS_ROLLUP.HIERARCHY
GLOBAL_AW.CUSTOMER.MARKET_ROLLUP.HIERARCHY
GLOBAL_AW.PRODUCT.DIMENSION GLOBAL_AW.PRODUCT.PRODUCT_ROLLUP.HIERARCHY
GLOBAL_AW.TIME.DIMENSION GLOBAL_AW.TIME.CALENDAR.HIERARCHY
The DIM_LEVELS valueset lists the levels that belong to each dimension in the current analytic workspace. The valueset is dimensioned by ALL_DIMENSIONS, so that each dimension has its own list. The following commands present the list of levels for each dimension.
REPORT W 45 VALUES(dim_levels)
ALL_DIMENSIONS VALUES(DIM_LEVELS)
------------------------------ ---------------------------------------------
GLOBAL_AW.CHANNEL.DIMENSION GLOBAL_AW.CHANNEL.ALL_CHANNELS.LEVEL
GLOBAL_AW.CHANNEL.CHANNEL.LEVEL
GLOBAL_AW.CUSTOMER.DIMENSION GLOBAL_AW.CUSTOMER.ALL_CUSTOMERS.LEVEL
GLOBAL_AW.CUSTOMER.REGION.LEVEL
GLOBAL_AW.CUSTOMER.WAREHOUSE.LEVEL
GLOBAL_AW.CUSTOMER.TOTAL_MARKET.LEVEL
GLOBAL_AW.CUSTOMER.MARKET_SEGMENT.LEVEL
GLOBAL_AW.CUSTOMER.ACCOUNT.LEVEL
GLOBAL_AW.CUSTOMER.SHIP_TO.LEVEL
GLOBAL_AW.PRODUCT.DIMENSION GLOBAL_AW.PRODUCT.TOTAL_PRODUCT.LEVEL
GLOBAL_AW.PRODUCT.CLASS.LEVEL
GLOBAL_AW.PRODUCT.FAMILY.LEVEL
GLOBAL_AW.PRODUCT.ITEM.LEVEL
GLOBAL_AW.TIME.DIMENSION GLOBAL_AW.TIME.YEAR.LEVEL
GLOBAL_AW.TIME.QUARTER.LEVEL
GLOBAL_AW.TIME.MONTH.LEVEL
The DIM_ATTRIBUTES valueset lists the attributes that belong to each dimension in the current analytic workspace. The valueset is dimensioned by ALL_DIMENSIONS, so that each dimension has its own list. The following commands present the list of attributes for a dimension called TIME.
REPORT W 46 VALUES(dim_attributes)
ALL_DIMENSIONS VALUES(DIM_ATTRIBUTES)
------------------------------ ----------------------------------------------
GLOBAL_AW.CHANNEL.DIMENSION GLOBAL_AW.CHANNEL.LONG_DESCRIPTION.ATTRIBUTE
GLOBAL_AW.CHANNEL.SHORT_DESCRIPTION.ATTRIBUTE
GLOBAL_AW.CUSTOMER.DIMENSION GLOBAL_AW.CUSTOMER.LONG_DESCRIPTION.ATTRIBUTE
GLOBAL_AW.CUSTOMER.SHORT_DESCRIPTION.ATTRIBUTE
GLOBAL_AW.PRODUCT.DIMENSION GLOBAL_AW.PRODUCT.LONG_DESCRIPTION.ATTRIBUTE
GLOBAL_AW.PRODUCT.SHORT_DESCRIPTION.ATTRIBUTE
GLOBAL_AW.PRODUCT.PACKAGE.ATTRIBUTE
GLOBAL_AW.TIME.DIMENSION GLOBAL_AW.TIME.LONG_DESCRIPTION.ATTRIBUTE
GLOBAL_AW.TIME.SHORT_DESCRIPTION.ATTRIBUTE
GLOBAL_AW.TIME.END_DATE.ATTRIBUTE
GLOBAL_AW.TIME.TIME_SPAN.ATTRIBUTE
The Catalogs class includes variables and formulas that list the objects that support various other objects.
The AW_NAMES variable is dimensioned by ALL_OBJECTS. It contains the name of the workspace object that implements each logical object. If no workspace object implements a given logical object, the value is NA.
The following is a full description of an AW_NAMES variable.
FULLDSC aw_names DEFINE AW_NAMES VARIABLE TEXT <ALL_OBJECTS> PROPERTY 'AW$CLASS' 'CATALOGS' PROPERTY 'AW$CREATEDBY' 'AW$CREATE' PROPERTY 'AW$LASTMODIFIED' '04DEC02_13:09:14' PROPERTY 'AW$ROLE' 'AW_NAMES' PROPERTY 'AW$STATE' 'CREATED'
The AW_COMPSPECS variable is dimensioned by ALL_DIMENSIONS. For each logical dimension, the AW_COMPSPECS variable contains the names of the AGGMAP objects that must be modified when the dimension is modified.
The following is a full description of an AW_COMPSPECS variable.
FULLDSC aw_compspecs DEFINE AW_COMPSPECS VARIABLE TEXT <ALL_DIMENSIONS> PROPERTY 'AW$CLASS' 'CATALOGS' PROPERTY 'AW$CREATEDBY' 'AW$CREATE' PROPERTY 'AW$LASTMODIFIED' '04DEC02_13:09:14' PROPERTY 'AW$ROLE' 'AW_COMPSPECS' PROPERTY 'AW$STATE' 'CREATED'
The AW_LOOPSPECS variable is dimensioned by ALL_CUBES. It contains the name of the workspace composite for each cube.
The following is a full description of an AW_LOOPSPECS variable.
FULLDSC aw_loopspecs DEFINE AW_LOOPSPECS VARIABLE TEXT <ALL_CUBES> PROPERTY 'AW$CLASS' 'CATALOGS' PROPERTY 'AW$CREATEDBY' 'AW$CREATE' PROPERTY 'AW$LASTMODIFIED' '04DEC02_13:09:14' PROPERTY 'AW$ROLE' 'AW_LOOPSPECS' PROPERTY 'AW$STATE' 'CREATED'
Features class objects hold information about specific logical objects and the workspace objects that implement them. For example, one object stores the descriptions of all the logical objects, while another indicates whether the object is intended to be visible to the user.
The ALL_DESCRIPTIONS variable contains the short, long, and plural descriptions of various logical objects. For search convenience it is dimensioned by a composite.
The following is a full description of an ALL_DESCRIPTIONS variable.
FULLDSC all_descriptions DEFINE ALL_DESCRIPTIONS VARIABLE TEXT <SPARSE <ALL_OBJECTS ALL_DESCTYPES ALL_LANGUAGES>> LD FEATURES Descriptions for all objects PROPERTY 'AW$CLASS' 'FEATURES' PROPERTY 'AW$CREATEDBY' 'AW$CREATE' PROPERTY 'AW$LASTMODIFIED' '04DEC02_13:09:14' PROPERTY 'AW$ROLE' 'ALL_DESCRIPTIONS' PROPERTY 'AW$STATE' 'CREATED'
The following report shows sample values for ALL_DESCRIPTIONS.
report w 30 down all_dimensions all_descriptions
ALL_LANGUAGES: AMERICAN_AMERICA
--------ALL_DESCRIPTIONS--------
---------ALL_DESCTYPES----------
ALL_DIMENSIONS SHORT LONG PLURAL
------------------------------ ---------- ---------- ----------
GLOBAL_AW.CHANNEL.DIMENSION Channel NA CHANNEL
GLOBAL_AW.CUSTOMER.DIMENSION Customer NA CUSTOMER
GLOBAL_AW.PRODUCT.DIMENSION Product NA PRODUCT
GLOBAL_AW.TIME.DIMENSION Time NA TIME
The ATTR_INHIER variable is a boolean variable that indicates whether a given attribute is associated with a given hierarchy. The variable is dimensioned by ALL_ATTRIBUTES and ALL_HIERARCHIES.
The following is a full description of an ATTR_INHIER variable.
FULLDSC attr_inhier DEFINE ATTR_INHIER VARIABLE BOOLEAN <ALL_ATTRIBUTES ALL_HIERARCHIES> LD FEATURES Indicates if each attribute participates in each hierarchy PROPERTY 'AW$CLASS' 'FEATURES' PROPERTY 'AW$CREATEDBY' 'AW$CREATE' PROPERTY 'AW$LASTMODIFIED' '04DEC02_13:09:14' PROPERTY 'AW$ROLE' 'ATTR_INHIER' PROPERTY 'AW$STATE' 'CREATED'
The DEFAULT_HIER relation records the full name of the default hierarchy for each dimension. The base dimension for the relation is ALL_DIMENSIONS.
The following is a full description of a DEFAULT_HIER relation.
FULLDSC default_hier DEFINE DEFAULT_HIER RELATION ALL_HIERARCHIES <ALL_DIMENSIONS> LD FEATURES Default hierarchy for each dimension PROPERTY 'AW$CLASS' 'FEATURES' PROPERTY 'AW$CREATEDBY' 'AW$CREATE' PROPERTY 'AW$LASTMODIFIED' '04DEC02_13:09:14' PROPERTY 'AW$ROLE' 'DEFAULT_HIER' PROPERTY 'AW$STATE' 'CREATED'
The following report shows the default hierarchy for each dimension.
REPORT W 45 default_hier ALL_DIMENSIONS DEFAULT_HIER ------------------------------ --------------------------------------------- GLOBAL_AW.CHANNEL.DIMENSION GLOBAL_AW.CHANNEL.CHANNEL_ROLLUP.HIERARCHY GLOBAL_AW.CUSTOMER.DIMENSION GLOBAL_AW.CUSTOMER.SHIPMENTS_ROLLUP.HIERARCHY GLOBAL_AW.PRODUCT.DIMENSION GLOBAL_AW.PRODUCT.PRODUCT_ROLLUP.HIERARCHY GLOBAL_AW.TIME.DIMENSION GLOBAL_AW.TIME.CALENDAR.HIERARCHY
The VISIBLE variable is a boolean that indicates whether the Oracle OLAP enabler utilities should expose or ignore the objects that are registered. The variable is dimensioned by ALL_OBJECTS so that each object has its own setting.
The following is a full description of a VISIBLE variable.
FULLDSC visible DEFINE VISIBLE VARIABLE BOOLEAN <ALL_OBJECTS> LD FEATURES Is the object visible PROPERTY 'AW$CLASS' 'FEATURES' PROPERTY 'AW$CREATEDBY' 'AW$CREATE' PROPERTY 'AW$LASTMODIFIED' '04DEC02_13:09:14' PROPERTY 'AW$ROLE' 'VISIBLE' PROPERTY 'AW$STATE' 'CREATED'
The member_inhier variable is a boolean variable that indicates whether a given member of a dimension is in a given hierarchy. There is one of these variables for each dimension in the workspace, and that dimension is the variable's parent.
The following is a full description of a member_inhier variable for the TIME dimension.
FULLDSC time_inhier DEFINE TIME_INHIER VARIABLE BOOLEAN <TIME TIME_HIERLIST> LD FEATURES Indicator of whether each dimension member participates in a hierarchy for TIME PROPERTY 'AW$CLASS' 'FEATURES' PROPERTY 'AW$CREATEDBY' 'AW$CREATE' PROPERTY 'AW$LASTMODIFIED' '03SEP03_15:27:47' PROPERTY 'AW$PARENT_NAME' 'TIME' PROPERTY 'AW$ROLE' 'MEMBER_INHIER' PROPERTY 'AW$STATE' 'CREATED'
The member_createdby variable records the entity that created each member of a given dimension. There is one of these variables for each dimension in the workspace, and that dimension is the variable's parent.
The following is a full description of a member_createdby variable for a dimension called TIME.
FULLDSC time_createdby DEFINE TIME_CREATEDBY VARIABLE TEXT <TIME> LD FEATURES Creator of each dimension member for TIME PROPERTY 'AW$CLASS' 'FEATURES' PROPERTY 'AW$CREATEDBY' 'AW$CREATE' PROPERTY 'AW$LASTMODIFIED' '03SEP03_15:27:47' PROPERTY 'AW$PARENT_NAME' 'TIME' PROPERTY 'AW$ROLE' 'MEMBER_CREATEDBY' PROPERTY 'AW$STATE' 'CREATED'
The member_familyrel relation records the ancestors of a given member of a dimension. There is one of these relations for each dimension in the workspace, and that dimension is the variable's parent. These relations are for internal use.
The following is a full description of a member_familyrel relation for the TIME dimension.
FULLDSC time_familyrel DEFINE TIME_FAMILYREL RELATION TIME <TIME TIME_LEVELLIST TIME_HIERLIST> LD FEATURES Family/Ancestry structure for TIME PROPERTY 'AW$CLASS' 'FEATURES' PROPERTY 'AW$CREATEDBY' 'AW$CREATE' PROPERTY 'AW$LASTMODIFIED' '03SEP03_15:27:47' PROPERTY 'AW$PARENT_NAME' 'TIME' PROPERTY 'AW$ROLE' 'MEMBER_FAMILYREL' PROPERTY 'AW$STATE' 'CREATED'
The member_gid variable records the level depth of a given member of a dimension, within a given hierarchy. There is one of these relations for each dimension in the workspace, and that dimension is the variable's parent. These relations are for internal use.
The following is a full description of a member_gid relation for the TIME dimension.
FULLDSC time_gid DEFINE TIME_GID VARIABLE INTEGER <TIME TIME_HIERLIST> LD FEATURES Grouping id value for TIME PROPERTY 'AW$CLASS' 'FEATURES' PROPERTY 'AW$CREATEDBY' 'AW$CREATE' PROPERTY 'AW$LASTMODIFIED' '03SEP03_15:27:47' PROPERTY 'AW$PARENT_NAME' 'TIME' PROPERTY 'AW$ROLE' 'MEMBER_GID' PROPERTY 'AW$STATE' 'CREATED'
The OBJ_CREATEDBY variable records the entity that created each object that is registered in the standard form. The variable is dimensioned by ALL_OBJECTS.
The following is a full description of the OBJ_CREATEDBY variable.
FULLDSC obj_createdby DEFINE OBJ_CREATEDBY VARIABLE TEXT <ALL_OBJECTS> LD FEATURES Creator of each object PROPERTY 'AW$CLASS' 'FEATURES' PROPERTY 'AW$CREATEDBY' 'AW$CREATE' PROPERTY 'AW$LASTMODIFIED' '04DEC02_13:09:14' PROPERTY 'AW$ROLE' 'OBJ_CREATEDBY' PROPERTY 'AW$STATE' 'CREATED'
The OBJ_STATE variable records the state for each registered object in the standard form. The variable is dimensioned by ALL_OBJECTS. The value for each object is either UNDER_CONSTRUCTION or ACTIVE.
The following is a full description of the OBJ_CREATEDBY variable.
FULLDSC obj_state DEFINE OBJ_STATE VARIABLE TEXT <ALL_OBJECTS> LD FEATURES State of each object PROPERTY 'AW$CLASS' 'FEATURES' PROPERTY 'AW$CREATEDBY' 'AW$CREATE' PROPERTY 'AW$LASTMODIFIED' '04DEC02_13:09:14' PROPERTY 'AW$ROLE' 'OBJ_STATE' PROPERTY 'AW$STATE' 'CREATED'
Extensions class objects are defined and maintained by the Oracle OLAP utilities. They are proprietary extensions to the standard form, and there is no commitment on the part of Oracle to maintain them from release to release.
Do not define, modify, or depend on objects in the extensions class.
|
 Copyright © 2002, 2003 Oracle Corporation All Rights Reserved. |
|