| Oracle® OLAP Application Developer's Guide 10g Release 2 (10.2) Part Number B14349-02 |
|
|
View PDF |
| Oracle® OLAP Application Developer's Guide 10g Release 2 (10.2) Part Number B14349-02 |
|
|
View PDF |
An analytic workspace that conforms to database standard form has objects that implement a logical model for cubes, dimensions, and measures. 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 for users who want to add objects manually to a standard form analytic workspace. It has the following sections:
|
Note: Database Standard Form 10g has a large Extensions class, which is not documented nor supported for public use. Nonetheless, these Extensions class objects are required to support OLAP tools. Customizing is thus more difficult than in Oracle9i. |
Just as a relational schema can be set up in countless ways, the design of an analytic workspace can be structured in as many ways as there are application developers. However, when an application is created to run against analytic workspaces, it requires one particular design so that it can locate particular objects and identify their role within the workspace. The design for the tools available through Analytic Workspace Manager is called database standard form.
Analytic Workspace Manager and the current generation of tools can only be used with database standard form analytic workspaces. Database standard form (or simply, standard form) stipulates:
Certain objects must exist in the analytic workspace. These objects and properties are used by tools in Analytic Workspace Manager that perform tasks such as aggregation, data refresh, and applications enablement. The Active Catalogs and the DBMS_AWM PL/SQL package, described in the Oracle OLAP Reference, require database standard.
Specific OLAP DML properties must be defined on these analytic workspace objects. The property values are metadata for the object, and provide information about its role in the logical model, its logical name, its relationships with other objects, and so forth. Standard form properties begin with AW$.
Objects must be registered in workspace catalogs. OLAP tools query these metadata catalogs to get information about how the logical cubes, measures, and dimensions are instantiated in the analytic workspace. When you define objects using the tools in Analytic Workspace Manager, the tools also maintain the catalogs. However, when you define objects manually, as described in some chapters of this guide, you must also maintain the properties and the catalogs for the tools to be aware of the new objects.
These rules impose the logical dimensional model of cubes, measures, dimensions, levels, hierarchies, and attributes on an analytic workspace.
Standard form analytic workspaces are created by all of the methods described in Chapter 3. By using the Object View to browse the workspace objects, you can gain familiarity with standard form.
Standard form conventions do not govern the names of workspace objects, so 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, this guide refers 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, but not identical, to their roles. Roles and the AW$ROLE property are discussed under each logical object type.
Standard form enables you to discover the names of logical objects and the names of the physical workspace objects that implement the logical model.
You can acquire information about an analytic workspace by querying its standard form catalogs. These catalogs are implemented as dimensions, variables, relations, and valuesets in the analytic workspace. Some of these objects are in the Catalogs class, and others are in the Extensions class.
The ALL_OBJECTS dimension is a catalog that contains the names of all logical objects. ALL_OBJECTS is a concat dimension, that is, it is a concatenated list of the members of other simple dimensions. Separate dimensions for each logical object type contain the names of logical objects, for example, the ALL_HIERARCHIES dimension contains the names of all hierarchies, and the ALL_LEVELS dimension contains the names of all levels. You can query these dimensions to discover the logical model implemented by an analytic workspace.
For example, the following command displays the names of all measures in the analytic workspace.
REPORT W 40 all_measures ALL_MEASURES ---------------------------------------- PRICE_AND_COST_CUBE.UNIT_PRICE.MEASURE PRICE_AND_COST_CUBE.UNIT_COST.MEASURE UNITS_CUBE.UNITS.MEASURE UNITS_CUBE.SALES.MEASURE
ALL_OBJECTS and its simple dimensions (such as ALL_LEVELS) are used in dimensional catalogs that are implemented as variables, relations, and valuesets.
Refer to "Catalogs Class Objects" for more information about standard form catalogs.
By querying the standard form properties attached to workspace objects, you can discover the relationship between the logical model and the physical objects that implement the model.
You can query the properties on a particular object, or limit the NAME dimension to objects with particular properties or property values. The NAME dimension contains the names of all objects in an analytic workspace. By limiting the status of the NAME dimension, you can limit the scope of commands that otherwise act on all objects.
All objects have the following properties, which are described in Table A-1.
AW$CLASSAW$CREATEDBYAW$LASTMODIFIEDAW$ROLEThe following commands show how you can use the AW$ROLE property to discover the names of measuredef objects:
LIMIT name TO OBJ(PROPERTY 'AW$ROLE') EQ 'MEASUREDEF' REPORT W 40 name NAME ---------------------------------------- PRICE_AND_COST_CUBE_UNIT_PRICE PRICE_AND_COST_CUBE_UNIT_COST UNITS_CUBE_UNITS UNITS_CUBE_SALES
The FULLDSC command lists all the properties and their values.
FULLDSC units_cube_units DEFINE UNITS_CUBE_UNITS FORMULA DECIMAL <TIME CUSTOMER PRODUCT CHANNEL> EQ aggregate(this_aw!UNITS_CUBE_UNITS_STORED using this_aw!OBJ1176965843) PROPERTY 'AW$CLASS' 'IMPLEMENTATION' PROPERTY 'AW$CREATEDBY' 'AW$XML' PROPERTY 'AW$LASTMODIFIED' '10MAY05_11:05:51' PROPERTY 'AW$LOGICAL_NAME' 'UNITS' PROPERTY 'AW$MEASUREDEF' NA PROPERTY 'AW$PARENT_NAME' 'UNITS_CUBE' PROPERTY 'AW$ROLE' 'MEASUREDEF' PROPERTY 'AW$STATE' 'VALID_MEMBER' PROPERTY 'COLUMN_NAME' 'MEASURE_51' PROPERTY 'DATA_TYPE' 'DECIMAL' PROPERTY 'DESCRIPTION' - 'LANG=AMERICAN:Units Sold- LANG=FRENCH:Unités Vendues - LANG=DUTCH:Verkochte Eenheden ' PROPERTY 'DISPLAYNAME' - 'LANG=AMERICAN:Units Sold- LANG=FRENCH:Unités Vendues - LANG=DUTCH:Verkochte Eenheden ' PROPERTY 'IS_SOLVETARGET' yes
Or you can use the OBJ function to get the value of a specific property:
SHOW OBJ(PROPERTY 'AW$PARENT_NAME', 'UNITS_CUBE_UNITS') UNITS_CUBE
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.
It is important to remember that this appendix describes a logical metadata model that is imposed on an analytic workspace. It does not describe the inherent relationships among workspace objects, such as the relationship between variables and formulas and their dimensions, or among dimensions in a workspace relation.
Within the logical model of standard form are parent-child relationships among objects. Only cubes (cubedef objects) and dimensions (dimdef objects) have no parents other than the analytic workspace itself. All other objects in the logical model are descendants of these 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 "Role Property Values for Implementation Class 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.
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.
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 parent object and the role. The following is an example of a full name for an attribute whose simple name is TIME_SPAN and whose parent object is a logical dimension named TIME.
TIME.TIME_SPAN.ATTRIBUTE
The final component of a full name is the object type. In this example, it is ATTRIBUTE.
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.
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 a schema.
Cube. The logical names of measures must be unique within a cube.
Dimension. The logical names of hierarchies, levels, and 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.
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:
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.
Properties specific to implementation class objects.
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.
All workspace objects that are part of the standard form have four system properties.
Table A-1 lists the system properties and describes each one.
Table A-1 System Properties
| Property | Description |
|---|---|
|
|
The class of the workspace object. Possible values are |
|
|
The entity that created the workspace object. For example, if it was created by |
|
|
The date and time when the workspace object was last registered. |
|
|
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 state of the workspace object with respect to the standard form, for example, |
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-2 lists the implementation class properties and describes each one.
Table A-2 Implementation Class Properties
| Property | Description |
|---|---|
|
|
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 |
|
|
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 |
|
|
For objects with role If the role is If the role is |
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-3 lists the possible values and describes each role.
Table A-3 Role Property Values: Implementation Class
| Role Property Value | Role Description |
|---|---|
|
|
Implements a cube whose logical name is in the |
|
|
Implements a measure whose logical name is in the |
|
|
Implements a dimension whose logical name is in the |
|
|
Lists the names of the hierarchies of the dimension whose name is in the |
|
|
Lists the names of the levels of the dimension whose name is in the |
|
|
Records the level for each member of the dimension whose name is in the |
|
|
Records the parent for each member of the dimension whose name is in the |
|
|
Lists the levels that are included in each hierarchy of the dimension whose name is in the |
|
|
Implements an attribute whose logical name is in the |
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-4 lists the possible values and describes each role.
Table A-4 Role Property Values: Catalogs Class
| Role Property Value | Role Description |
|---|---|
|
|
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". |
|
|
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". |
|
|
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". |
|
|
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" |
|
|
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". |
|
|
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". |
|
|
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". |
|
|
Lists the types of descriptions currently supported by the standard form: |
|
|
Lists all the attribute types that are currently supported by the standard form. These are valid values for the |
|
|
Lists the language_territory for the analytic workspace. For information about the object with this role, see "ALL_LANGUAGES Dimension". |
|
|
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 Relation". |
|
|
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 Relation". |
|
|
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 Relation". |
|
|
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 Relation". |
|
|
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 |
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-5 lists the possible values and describes each role that applies to features class objects.
Table A-5 Role Property Values: Features Class
| Role Property Value | Role Description |
|---|---|
|
|
Records short, long, and plural descriptions for all objects. For information about the object with this role, see "ALL_DESCRIPTIONS Variable". |
|
|
Records the full name of the default hierarchy for each dimension. For information about the object with this role, see "DEFAULT_HIER Relation". |
|
|
Records the entity that created each member of a given dimension. For information about the object with this role, see "Member_Createdby Variable". |
|
|
Records the family relation for each hierarchy of a given dimension. For information about the object with this role, see "Member_Familyrel Relation". |
|
|
Records the grouping id for each hierarchy of a given dimension. For information about the object with this role, see "Member_Gid Variable". |
|
|
Indicates whether a given member of a dimension is in a given hierarchy. For information about the object with this role, see "Member_Inhier Valueset". |
|
|
Records the entity that created each object. For information about the object with this role, see "OBJ_CREATEDBY Variable". |
|
|
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". |
|
|
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.
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 "Standard Form Implementation of the Logical Model".
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 Table A-1 and Table A-2.
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 W 20 name NAME -------------------- PRICE_AND_COST_CUBE UNITS_CUBE
Be sure to reset NAME afterward:
LIMIT name TO ALL
A cube is implemented by a cubedef dimension. It is owned by the analytic workspace; it has no parent objects.
A cubedef dimension is the parent of one or more measuredef objects, and is typically the parent of two Extensions class objects:
composite. A composite that dimensions the measure_stored variable.
aggregationdfn. An aggmap that stores the aggregation rules for the cube.
Figure A-1 shows the relationships among the primary objects that compose a cube.
Figure A-1 Parent-Child Relationships in a Cube
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 PROPERTY 'ALLDIMENSIONS' - 'TIME- CUSTOMER- PRODUCT- CHANNEL' PROPERTY 'AW$CLASS' 'IMPLEMENTATION' PROPERTY 'AW$CREATEDBY' 'AW$XML' PROPERTY 'AW$CUBEDEF' NA PROPERTY 'AW$LASTMODIFIED' '10MAY05_11:05:51' PROPERTY 'AW$LOGICAL_NAME' 'UNITS_CUBE' PROPERTY 'AW$ROLE' 'CUBEDEF' PROPERTY 'AW$STATE' 'VALID_MEMBER' PROPERTY 'DENSEDIMENSIONS' - 'TIME- CUSTOMER- PRODUCT- CHANNEL' PROPERTY 'DESCRIPTION' - 'LANG=AMERICAN:Units Cube- LANG=FRENCH:Cube en Unités - LANG=DUTCH:Kubus van Eenheden ' PROPERTY 'DISPLAYNAME' - 'LANG=AMERICAN:Units Cube- LANG=FRENCH:Cube en Unités - LANG=DUTCH:Kubus van Eenheden '
The following report 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 -------------- TIME CUSTOMER PRODUCT CHANNEL
A measure is implemented by a measuredef object. Every measure has one cubedef as its parent.
A measuredef object is typically the parent of two Extensions class objects:
measure_countvar. A variable used by some aggregation operators.
measure_stored. A variable used to store the data 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 calculates fully solved data that is stored in a variable named UNITS_CUBE_UNITS_STORED.
FULLDSC units_cube_units DEFINE UNITS_CUBE_UNITS FORMULA DECIMAL <TIME CUSTOMER PRODUCT CHANNEL> EQ aggregate(this_aw!UNITS_CUBE_UNITS_STORED using this_aw!OBJ1176965843) PROPERTY 'AW$CLASS' 'IMPLEMENTATION' PROPERTY 'AW$CREATEDBY' 'AW$XML' PROPERTY 'AW$LASTMODIFIED' '10MAY05_11:05:51' PROPERTY 'AW$LOGICAL_NAME' 'UNITS' PROPERTY 'AW$MEASUREDEF' NA PROPERTY 'AW$PARENT_NAME' 'UNITS_CUBE' PROPERTY 'AW$ROLE' 'MEASUREDEF' PROPERTY 'AW$STATE' 'VALID_MEMBER' PROPERTY 'COLUMN_NAME' 'MEASURE_51' PROPERTY 'DATA_TYPE' 'DECIMAL' PROPERTY 'DESCRIPTION' - 'LANG=AMERICAN:Units Sold- LANG=FRENCH:Unités Vendues - LANG=DUTCH:Verkochte Eenheden ' PROPERTY 'DISPLAYNAME' - 'LANG=AMERICAN:Units Sold- LANG=FRENCH:Unités Vendues - LANG=DUTCH:Verkochte Eenheden ' PROPERTY 'IS_SOLVETARGET' yes
A dimension is implemented by a dimdef object. The dimdef object is the parent of one each of the following supporting objects:
hierlist dimension
levellist dimension
member_levelrel relation
member_parentrel relation
hier_levels valueset
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.
Optionally, a dimdef object can have one or more attrdef objects as its children.
For enablement for OracleBI Beans, a dimdef object requires one each of these Features class objects as its children:
member_inhier
member_familyrel
member_gid
A data refresh uses the Features class member_createdby object.
Figure A-2 shows the relationships among the primary objects that compose dimensions.
Figure A-2 Parent-Child Relationships in a Dimension
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 TIME.
FULLDSC time DEFINE TIME DIMENSION TEXT PROPERTY 'AW$CLASS' 'IMPLEMENTATION' PROPERTY 'AW$CREATEDBY' 'AW$XML' PROPERTY 'AW$DIMDEF' NA PROPERTY 'AW$LASTMODIFIED' '10MAY05_11:05:29' PROPERTY 'AW$LOGICAL_NAME' 'TIME' PROPERTY 'AW$ROLE' 'DIMDEF' PROPERTY 'AW$STATE' 'VALID_MEMBER' PROPERTY 'AW$TYPE' 'TIME' PROPERTY 'COLUMN_NAME_ET' 'ET_COL_25' PROPERTY 'COLUMN_NAME_GID' 'GID_COL_27' PROPERTY 'COLUMN_NAME_PRNTET' 'PET_COL_26' PROPERTY 'COLUMN_NAME_PRNTGID' 'PGID_COL_28' PROPERTY 'DATA_TYPE' 'TEXT' PROPERTY 'DEFAULT_HIERARCHY' 'CALENDAR_YEAR' PROPERTY 'DESCRIPTION' - 'LANG=AMERICAN:Time- LANG=FRENCH:Temps - LANG=DUTCH:Tijd ' PROPERTY 'DISPLAYNAME' - 'LANG=AMERICAN:Time- LANG=FRENCH:Temps - LANG=DUTCH:Tijd ' PROPERTY 'PLURAL_DESCRIPTION' - 'LANG=AMERICAN:Time- LANG=FRENCH:Temps - LANG=DUTCH:Tijd ' PROPERTY 'SORT_ATTRIBUTE' 'END_DATE'
The following report 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 QUARTER.142 and YEAR.145. The example includes an attrdef variable and member_levelrel relation to describe the selected dimension members.
LIMIT time TO '131'
LIMIT time ADD ANCESTORS USING time_parentrel
REPORT DOWN time W 25 <time_long_description time_levelrel>
-------------------ALL_LANGUAGES-------------------
---------------------AMERICAN----------------------
TIME TIME_LONG_DESCRIPTION TIME_LEVELREL
-------------- ------------------------- -------------------------
131 May-05 MONTH
142 Q2-05 QUARTER
145 2005 YEAR
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.
FULLDSC time_hierlist DEFINE TIME_HIERLIST DIMENSION TEXT PROPERTY 'AW$CLASS' 'IMPLEMENTATION' PROPERTY 'AW$CREATEDBY' 'AW$XML' PROPERTY 'AW$HIERLIST' NA PROPERTY 'AW$LASTMODIFIED' '18MAR04_10:40:51' PROPERTY 'AW$PARENT_NAME' 'TIME' PROPERTY 'AW$ROLE' 'HIERLIST'
The following report shows the values of this hierlist dimension. TIME has one hierarchy, which is named CALENDAR_YEAR.
REPORT time_hierlist TIME_HIERLIST -------------- CALENDAR_YEAR
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_HIERLIST DIMENSION TEXT PROPERTY 'AW$CLASS' 'IMPLEMENTATION' PROPERTY 'AW$CREATEDBY' 'AW$XML' PROPERTY 'AW$LASTMODIFIED' '18MAR04_10:40:51' PROPERTY 'AW$LEVELLIST' NA PROPERTY 'AW$LEVEL_MONTH' 'MONTH' PROPERTY 'AW$LEVEL_QUARTER' 'QUARTER' PROPERTY 'AW$LEVEL_YEAR' 'YEAR' PROPERTY 'AW$PARENT_NAME' 'TIME' PROPERTY 'AW$ROLE' 'LEVELLIST'
The following report shows the values of this levellist dimension.
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> PROPERTY 'AW$CLASS' 'IMPLEMENTATION' PROPERTY 'AW$CREATEDBY' 'AW$XML' PROPERTY 'AW$LASTMODIFIED' '03SEP03_15:27:47' PROPERTY 'AW$PARENT_NAME' 'TIME' PROPERTY 'AW$ROLE' 'MEMBER_LEVELREL'
The following report shows sample values of a 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 MASSACHUSETTS 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$XML' PROPERTY 'AW$LASTMODIFIED' '03SEP03_15:27:47' PROPERTY 'AW$MEMBER_PARENTREL' NA PROPERTY 'AW$PARENT_NAME' 'TIME' PROPERTY 'AW$ROLE' 'MEMBER_PARENTREL'
The following report shows the values of a 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$XML' PROPERTY 'AW$LASTMODIFIED' '03SEP03_15:27:47' PROPERTY 'AW$PARENT_NAME' 'TIME' PROPERTY 'AW$ROLE' 'HIER_LEVELS'
The following report shows the list of levels for each hierarchy in the TIME dimension.
REPORT W 25 VALUES(time_hier_levels)
TIME_HIERLIST VALUES(TIME_HIER_LEVELS)
-------------- -------------------------
CALENDAR_YEAR 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.
DEFINE TIME_LONG_DESCRIPTION VARIABLE TEXT <TIME ALL_LANGUAGES> PROPERTY '$NATRIGGER' 'if this_aw!ALL_LANGUAGES eq \'AMERICAN\' then NA else this_aw!TIME_LONG_DESCRIPTION(this_aw!ALL_LANGUAGES \'AMERICAN\')' PROPERTY 'AW$ATTRDEF' NA PROPERTY 'AW$CLASS' 'IMPLEMENTATION' PROPERTY 'AW$CREATEDBY' 'AW$XML' PROPERTY 'AW$LASTMODIFIED' '10MAY05_11:05:30' PROPERTY 'AW$LNG_ATTRIBUTE' yes PROPERTY 'AW$LOGICAL_NAME' 'LONG_DESCRIPTION' PROPERTY 'AW$PARENT_NAME' 'TIME' PROPERTY 'AW$ROLE' 'ATTRDEF' PROPERTY 'AW$STATE' 'VALID_MEMBER' PROPERTY 'AW$TYPE' 'MEMBER_LONG_DESCRIPTION' PROPERTY 'COLUMN_NAME' 'ATTRIBUTE_29' PROPERTY 'DATA_TYPE' 'TEXT' PROPERTY 'DESCRIPTION' - 'LANG=AMERICAN:Long Description- LANG=FRENCH:Description Longue - LANG=DUTCH:Lange Beschrijving ' PROPERTY 'DISPLAYNAME' - 'LANG=AMERICAN:Long Description- LANG=FRENCH:Description Longue - LANG=DUTCH:Lange Beschrijving '
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
--TIME_LONG_DESCRIPTION--
------ALL_LANGUAGES------
TIME AMERICAN
-------------- -------------------------
145 2005
141 Q1-05
142 Q2-05
143 Q3-05
144 Q4-05
127 Jan-05
128 Feb-05
129 Mar-05
.
.
.
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$XML' PROPERTY 'AW$LASTMODIFIED' '04DEC02_13:09:14' PROPERTY 'AW$ROLE' 'ALL_CUBES'
The following report shows the values of this ALL_CUBES dimension.
REPORT W 20 all_cubes ALL_CUBES -------------------- PRICE_CUBE.CUBE UNITS_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 report shows the values of an ALL_MEASURES dimension.
REPORT W 40 all_measures ALL_MEASURES ---------------------------------------- PRICE_AND_COST_CUBE.UNIT_PRICE.MEASURE PRICE_AND_COST_CUBE.UNIT_COST.MEASURE UNITS_CUBE.UNITS.MEASURE UNITS_CUBE.SALES.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 report shows the values of an ALL_DIMENSIONS dimension.
REPORT W 20 all_dimensions ALL_DIMENSIONS -------------------- PRODUCT.DIMENSION TIME.DIMENSION CHANNEL.DIMENSION CUSTOMER.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 report shows the values of an ALL_HIERARCHIES dimension.
REPORT W 35 all_hierarchies ALL_HIERARCHIES ----------------------------------- CUSTOMER.AW$NONE.HIERARCHY CUSTOMER.SHIPMENTS.HIERARCHY CUSTOMER.MARKET_SEGMENT.HIERARCHY PRODUCT.AW$NONE.HIERARCHY PRODUCT.PRIMARY.HIERARCHY TIME.AW$NONE.HIERARCHY TIME.CALENDAR_YEAR.HIERARCHY CHANNEL.AW$NONE.HIERARCHY CHANNEL.PRIMARY.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 report shows the values of an ALL_LEVELS dimension.
REPORT W 30 all_levels
ALL_LEVELS
------------------------------
CUSTOMER.AW$NONE.LEVEL
CUSTOMER.TOTAL_CUSTOMER.LEVEL
CUSTOMER.REGION.LEVEL
CUSTOMER.WAREHOUSE.LEVEL
CUSTOMER.TOTAL_MARKET.LEVEL
CUSTOMER.MARKET_SEGMENT.LEVEL
CUSTOMER.ACCOUNT.LEVEL
CUSTOMER.SHIP_TO.LEVEL
PRODUCT.AW$NONE.LEVEL
TIME.AW$NONE.LEVEL
TIME.YEAR.LEVEL
TIME.QUARTER.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 report shows the values of an ALL_ATTRIBUTES dimension.
REPORT W 40 all_attributes
ALL_ATTRIBUTES
----------------------------------------
CUSTOMER.LONG_DESCRIPTION.ATTRIBUTE
CUSTOMER.SHORT_DESCRIPTION.ATTRIBUTE
PRODUCT.SHORT_DESCRIPTION.ATTRIBUTE
PRODUCT.PACKAGE.ATTRIBUTE
PRODUCT.BUYER.ATTRIBUTE
PRODUCT.MARKETING_MANAGER.ATTRIBUTE
PRODUCT.LONG_DESCRIPTION.ATTRIBUTE
TIME.END_DATE.ATTRIBUTE
TIME.TIME_SPAN.ATTRIBUTE
TIME.LONG_DESCRIPTION.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_ATTRIBUTES ALL_HIERARCHIES ALL_LEVELS - ALL_SOLVES ALL_SOLVEDFNS ALL_SOLVEGROUPS ALL_MODELS ALL_MEASUREFOLDERS) UNIQUE PROPERTY 'AW$CLASS' 'CATALOGS' PROPERTY 'AW$CREATEDBY' 'AW$XML' PROPERTY 'AW$LASTMODIFIED' '10MAY05_11:04:57' PROPERTY 'AW$ROLE' 'ALL_OBJECTS' PROPERTY 'LAST_COLUMN_ID' 78
ALL_OBJECTS is a concat dimension of the ALL_CUBES, ALL_MEASURES, ALL_HIERARCHIES, ALL_LEVELS, and ALL_ATTRIBUTES dimensions from the Catalogs class. It also includes dimensions from the Extensions class. 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 40 all_objects ALL_OBJECTS ---------------------------------------- PRICE_AND_COST_CUBE.CUBE UNITS_CUBE.CUBE PRICE_AND_COST_CUBE.UNIT_PRICE.MEASURE PRICE_AND_COST_CUBE.UNIT_COST.MEASURE CUSTOMER.AW$NONE.HIERARCHY CUSTOMER.SHIPMENTS.HIERARCHY CUSTOMER.AW$NONE.LEVEL CUSTOMER.TOTAL_CUSTOMER.LEVEL CUSTOMER.LONG_DESCRIPTION.ATTRIBUTE CUSTOMER.SHORT_DESCRIPTION.ATTRIBUTE
The Catalogs class includes dimensions that list types and languages that are supported by the current version of the standard form.
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 standard form. The following report lists the types.
REPORT W 40 all_attrtypes ALL_ATTRTYPES ---------------------------------------- END_DATE TIME_SPAN MEMBER_LONG_DESCRIPTION MEMBER_SHORT_DESCRIPTION USER
The ALL_LANGUAGES dimension lists the language that is implemented in the current analytic workspace. ALL_LANGUAGES by default contains the value of the database language. You can add support for any number of additional languages, as allowed by the database character set.
Your ability to change the status of ALL_LANGUAGES is controlled by the LOCK_LANGUAGE_DIMS option, which must set to NO for the status to change. By default, it is set to YES.
LOCK_LANGUAGE_DIMS=NO LIMIT all_languages TO ALL REPORT all_languages ALL_LANGUAGES -------------- AMERICAN FRENCH DUTCH LIMIT all_languages TO 1 LOCK_LANGUAGE_DIMS=YES
The Catalogs class includes relations that indicate the parent-child relationships among various logical objects. These lists are specific to a given workspace.
The CUBE_MEASURES relation identifies the cube to which each measure belongs. The values of the relation must be listed in the ALL_CUBES dimension. The following is a full description of a CUBE_MEASURES relation in a sample analytic workspace.
FULLDSC cube_measures DEFINE CUBE_MEASURES RELATION ALL_CUBES <ALL_MEASURES> PROPERTY 'AW$CLASS' 'CATALOGS' PROPERTY 'AW$CREATEDBY' 'AW$XML' PROPERTY 'AW$LASTMODIFIED' '03SEP03_15:27:47' PROPERTY 'AW$ROLE' 'CUBE_MEASURES'
The following report identifies the measures associated with each cube.
REPORT W 40 DOWN all_measures W 30 cube_measures ALL_MEASURES CUBE_MEASURES ---------------------------------------- ------------------------------ PRICE_AND_COST_CUBE.UNIT_PRICE.MEASURE PRICE_AND_COST_CUBE.CUBE PRICE_AND_COST_CUBE.UNIT_COST.MEASURE PRICE_AND_COST_CUBE.CUBE UNITS_CUBE.UNITS.MEASURE UNITS_CUBE.CUBE UNITS_CUBE.SALES.MEASURE UNITS_CUBE.CUBE
The DIM_HIERARCHIES relation identifies the dimension to which each hierarchy belongs. The values of the relation must be listed in the ALL_DIMENSIONS dimension. The following is a full description of the DIM_HIERARCHIES relation in a sample analytic workspace.
FULLDSC dim_hierarchies DEFINE DIM_HIERARCHIES RELATION ALL_DIMENSIONS <ALL_HIERARCHIES> PROPERTY 'AW$CLASS' 'CATALOGS' PROPERTY 'AW$CREATEDBY' 'AW$XML' PROPERTY 'AW$LASTMODIFIED' '16AUG04_09:43:27' PROPERTY 'AW$ROLE' 'DIM_HIERARCHIES'
The following report identifies the dimension for each hierarchy.
REPORT W 35 DOWN all_hierarchies W 20 dim_hierarchies ALL_HIERARCHIES DIM_HIERARCHIES ----------------------------------- -------------------- CUSTOMER.AW$NONE.HIERARCHY CUSTOMER.DIMENSION CUSTOMER.SHIPMENTS.HIERARCHY CUSTOMER.DIMENSION CUSTOMER.MARKET_SEGMENT.HIERARCHY CUSTOMER.DIMENSION PRODUCT.AW$NONE.HIERARCHY PRODUCT.DIMENSION PRODUCT.PRIMARY.HIERARCHY PRODUCT.DIMENSION TIME.AW$NONE.HIERARCHY TIME.DIMENSION TIME.CALENDAR_YEAR.HIERARCHY TIME.DIMENSION CHANNEL.AW$NONE.HIERARCHY CHANNEL.DIMENSION CHANNEL.PRIMARY.HIERARCHY CHANNEL.DIMENSION
The DIM_LEVELS relation identifies the dimension to which each level belongs. The values of the relation must be listed in the ALL_DIMENSIONS dimension. The following is a full description of the DIM_LEVELS relation in a sample analytic workspace.
FULLDSC dim_levels DEFINE DIM_LEVELS RELATION ALL_DIMENSIONS <ALL_LEVELS> PROPERTY 'AW$CLASS' 'CATALOGS' PROPERTY 'AW$CREATEDBY' 'AW$XML' PROPERTY 'AW$LASTMODIFIED' '16AUG04_09:43:27' PROPERTY 'AW$ROLE' 'DIM_LEVELS'
The following report identifies the dimension for each level.
REPORT W 35 DOWN all_levels W 20 dim_levels ALL_LEVELS DIM_LEVELS ----------------------------------- -------------------- CUSTOMER.AW$NONE.LEVEL CUSTOMER.DIMENSION CUSTOMER.TOTAL_CUSTOMER.LEVEL CUSTOMER.DIMENSION CUSTOMER.REGION.LEVEL CUSTOMER.DIMENSION CUSTOMER.WAREHOUSE.LEVEL CUSTOMER.DIMENSION CUSTOMER.TOTAL_MARKET.LEVEL CUSTOMER.DIMENSION CUSTOMER.MARKET_SEGMENT.LEVEL CUSTOMER.DIMENSION CUSTOMER.ACCOUNT.LEVEL CUSTOMER.DIMENSION CUSTOMER.SHIP_TO.LEVEL CUSTOMER.DIMENSION PRODUCT.AW$NONE.LEVEL PRODUCT.DIMENSION TIME.AW$NONE.LEVEL TIME.DIMENSION TIME.YEAR.LEVEL TIME.DIMENSION TIME.QUARTER.LEVEL TIME.DIMENSION TIME.MONTH.LEVEL TIME.DIMENSION CHANNEL.AW$NONE.LEVEL CHANNEL.DIMENSION CHANNEL.TOTAL_CHANNEL.LEVEL CHANNEL.DIMENSION CHANNEL.CHANNEL.LEVEL CHANNEL.DIMENSION
The DIM_ATTRIBUTES relation identifies the dimension to which each attribute belongs. The values of the relation must be listed in the ALL_DIMENSIONS dimension. The following is a full description of the DIM_ATTRIBUTES relation in a sample analytic workspace.
FULLDSC dim_attributes DEFINE DIM_ATTRIBUTES RELATION ALL_DIMENSIONS <ALL_ATTRIBUTES> PROPERTY 'AW$CLASS' 'CATALOGS' PROPERTY 'AW$CREATEDBY' 'AW$XML' PROPERTY 'AW$LASTMODIFIED' '16AUG04_09:43:27' PROPERTY 'AW$ROLE' 'DIM_ATTRIBUTES'
The following report identifies the dimension for each attribute.
REPORT W 40 DOWN all_attributes W 20 dim_attributes ALL_ATTRIBUTES DIM_ATTRIBUTES ---------------------------------------- -------------------- CUSTOMER.LONG_DESCRIPTION.ATTRIBUTE CUSTOMER.DIMENSION CUSTOMER.SHORT_DESCRIPTION.ATTRIBUTE CUSTOMER.DIMENSION PRODUCT.SHORT_DESCRIPTION.ATTRIBUTE PRODUCT.DIMENSION PRODUCT.PACKAGE.ATTRIBUTE PRODUCT.DIMENSION PRODUCT.BUYER.ATTRIBUTE PRODUCT.DIMENSION PRODUCT.MARKETING_MANAGER.ATTRIBUTE PRODUCT.DIMENSION PRODUCT.LONG_DESCRIPTION.ATTRIBUTE PRODUCT.DIMENSION TIME.END_DATE.ATTRIBUTE TIME.DIMENSION TIME.TIME_SPAN.ATTRIBUTE TIME.DIMENSION TIME.LONG_DESCRIPTION.ATTRIBUTE TIME.DIMENSION TIME.SHORT_DESCRIPTION.ATTRIBUTE TIME.DIMENSION TIME.TIME_DSO_1.ATTRIBUTE TIME.DIMENSION TIME.MONTH_OF_QUARTER.ATTRIBUTE TIME.DIMENSION TIME.MONTH_OF_YEAR.ATTRIBUTE TIME.DIMENSION TIME.QUARTER_OF_YEAR.ATTRIBUTE TIME.DIMENSION TIME.TIME_DSO_2.ATTRIBUTE TIME.DIMENSION TIME.TIME_DSO_3.ATTRIBUTE TIME.DIMENSION TIME.TIME_DSO_4.ATTRIBUTE TIME.DIMENSION CHANNEL.LONG_DESCRIPTION.ATTRIBUTE CHANNEL.DIMENSION CHANNEL.SHORT_DESCRIPTION.ATTRIBUTE CHANNEL.DIMENSION
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$XML' PROPERTY 'AW$LASTMODIFIED' '04DEC02_13:09:14' PROPERTY 'AW$ROLE' 'AW_NAMES'
The following report identifies the analytic workspace name of each logical dimension.
LIMIT all_objects TO all_dimensions REPORT W 20 DOWN all_objects aw_names ALL_OBJECTS AW_NAMES -------------------- ---------- PRODUCT.DIMENSION PRODUCT TIME.DIMENSION TIME CHANNEL.DIMENSION CHANNEL CUSTOMER.DIMENSION CUSTOMER
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 users.
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$XML' PROPERTY 'AW$LASTMODIFIED' '04DEC02_13:09:14' PROPERTY 'AW$ROLE' 'ALL_DESCRIPTIONS'
The following report shows the display names of the dimensions.
LIMIT all_objects TO all_dimensions
REPORT W 20 DOWN all_objects all_descriptions
ALL_LANGUAGES: AMERICAN
--------ALL_DESCRIPTIONS--------
---------ALL_DESCTYPES----------
ALL_OBJECTS SHORT LONG PLURAL
-------------------- ---------- ---------- ----------
CUSTOMER.DIMENSION Customer Customer Customer
PRODUCT.DIMENSION Product Product Product
TIME.DIMENSION Time Time Time
CHANNEL.DIMENSION Channel Channel Channel
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$XML' PROPERTY 'AW$LASTMODIFIED' '04DEC02_13:09:14' PROPERTY 'AW$ROLE' 'DEFAULT_HIER'
The following report shows the default hierarchy for each dimension.
REPORT W 20 DOWN all_dimensions W 40 default_hier ALL_DIMENSIONS DEFAULT_HIER -------------------- ---------------------------------------- CUSTOMER.DIMENSION CUSTOMER.SHIPMENTS.HIERARCHY PRODUCT.DIMENSION PRODUCT.PRIMARY.HIERARCHY TIME.DIMENSION TIME.CALENDAR_YEAR.HIERARCHY CHANNEL.DIMENSION CHANNEL.PRIMARY.HIERARCHY
The VISIBLE variable is a boolean that indicates whether the Oracle OLAP enablement 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> PROPERTY 'AW$CLASS' 'FEATURES' PROPERTY 'AW$CREATEDBY' 'AW$XML' PROPERTY 'AW$LASTMODIFIED' '10MAY05_11:04:57' PROPERTY 'AW$ROLE' 'VISIBLE'
The following report shows the visibility of objects in a sample analytic workspace.
REPORT W 40 DOWN all_objects visible
ALL_OBJECTS VISIBLE
---------------------------------------- ----------
CUSTOMER.DIMENSION yes
CUSTOMER.AW$NONE.LEVEL no
CUSTOMER.AW$NONE.HIERARCHY no
CUSTOMER.LONG_DESCRIPTION.ATTRIBUTE yes
CUSTOMER.SHORT_DESCRIPTION.ATTRIBUTE yes
CUSTOMER.TOTAL_CUSTOMER.LEVEL yes
CUSTOMER.REGION.LEVEL yes
CUSTOMER.WAREHOUSE.LEVEL yes
.
.
.
The member_inhier valueset stores lists of the dimension members that are in each hierarchy. There is one of these valuesets for each dimension in the workspace, and that dimension is the valueset's parent.
The following is a full description of a member_inhier valueset for the TIME dimension.
FULLDSC time_inhier DEFINE TIME_INHIER VALUESET TIME <TIME_HIERLIST> PROPERTY 'AW$CLASS' 'FEATURES' PROPERTY 'AW$CREATEDBY' 'AW$XML' PROPERTY 'AW$LASTMODIFIED' '18MAR04_14:46:51' PROPERTY 'AW$MEMBER_INHIER' NA PROPERTY 'AW$PARENT_NAME' 'TIME' PROPERTY 'AW$ROLE' 'MEMBER_INHIER'
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$XML' PROPERTY 'AW$LASTMODIFIED' '03SEP03_15:27:47' PROPERTY 'AW$PARENT_NAME' 'TIME' PROPERTY 'AW$ROLE' 'MEMBER_CREATEDBY'
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$XML' PROPERTY 'AW$LASTMODIFIED' '03SEP03_15:27:47' PROPERTY 'AW$MEMBER_FAMILYREL' NA PROPERTY 'AW$PARENT_NAME' 'TIME' PROPERTY 'AW$ROLE' 'MEMBER_FAMILYREL'
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 RELATION GID_DIMENSION <TIME TIME_HIERLIST> PROPERTY 'AW$CLASS' 'FEATURES' PROPERTY 'AW$CREATEDBY' 'AW$XML' PROPERTY 'AW$LASTMODIFIED' '16AUG04_09:45:07' PROPERTY 'AW$MEMBER_GID' NA PROPERTY 'AW$PARENT_NAME' 'TIME' PROPERTY 'AW$ROLE' 'MEMBER_GID'
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> PROPERTY 'AW$CLASS' 'FEATURES' PROPERTY 'AW$CREATEDBY' 'AW$XML' PROPERTY 'AW$LASTMODIFIED' '04DEC02_13:09:14' PROPERTY 'AW$ROLE' 'OBJ_CREATEDBY'
The VERSION variable records the version number of the standard form convention under which the analytic workspace is being managed.
The following is a full description of the VERSION variable.
FULLDSC ___xml_user_aw_version DEFINE ___XML_USER_AW_VERSION VARIABLE TEXT PROPERTY 'AW$CLASS' 'FEATURES' PROPERTY 'AW$CREATEDBY' 'AW$XML' PROPERTY 'AW$LASTMODIFIED' '16MAY05_12:28:55' PROPERTY 'AW$NEWTIMECALCS' yes PROPERTY 'AW$ROLE' 'VERSION' PROPERTY 'AW$VERSION10.1.0.3' NA PROPERTY 'AW$VERSION10.2' NA
The following command shows the current standard form version number.
SHOW ___xml_user_aw_version 10.2
