| 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 |
This chapter describes the objects created in a standard form analytic workspace. It serves as a guide to your own analytic workspace, and you can examine the property sheets of the objects described here by opening the Object View in Analytic Workspace Manager.
This chapter contains the following topics:
As described in Chapter 6, there are several methods for creating analytic workspaces. All of these methods create analytic workspaces with the same basic characteristics. These characteristics include compliance with the database standard form conventions.
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, described in "Overview of SQL Access", also rely on database standard form, as do some PL/SQL packages, such as DBMS_AW_UTILITIES.
OLAP DML properties (which begin with AW$) must be defined on these objects. The property values are metadata for the object, and identify its relationships with other objects in the analytic workspace.
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.
The Create Analytic Workspace wizard in Analytic Workspace Manager creates analytic workspaces in standard form. By using the Object View to browse the workspace objects, you can gain familiarity with standard form.
Not all of the objects required by standard form are currently used by the analytic workspace tools. These objects are not described in this chapter, and you can ignore them at this time.
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 standard form is 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.
Figure 8-1 shows the basic objects that implement this model in a standard form analytic workspace.
Figure 8-1 Standard Form Implementation of the Basic Logical Model
Some of the tools in Analytic Workspace Manager require additional properties and objects, which are defined in the analytic workspace.
Some property values identify build parameters in DBMS_AWM procedures. If you created your analytic workspace by running DBMS_AWM directly, then you will immediately recognize these values. If you used Analytic Workspace Manager or Oracle Warehouse Builder, which generated the calls to DBMS_AWM, then you can see the choices made for you.
Standard form does not specify a naming convention for workspace objects. However, DBMS_AWM creates objects with standardized names that typically identify the role of the object within the analytic workspace. This chapter identifies objects by the value of their AW$ROLE property and identifies the standardized names given by DBMS_AWM. When creating an analytic workspace, you may choose to add prefixes to these names.
The term "standard form" is thus used loosely in this chapter to refer to both the convention and its implementation by DBMS_AWM.
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 ---------------------------------------- GLOBAL_AW.UNITS_CUBE.UNITS.MEASURE GLOBAL_AW.PRICE_CUBE.UNIT_COST.MEASURE GLOBAL_AW.PRICE_CUBE.UNIT_PRICE.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-2:
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 name NAME ------------ UNITS UNIT_COST UNIT_PRICE
The FULLDSC command lists all the properties and their values:
FULLDSC units DEFINE UNITS FORMULA DECIMAL <TIME PRODUCT CUSTOMER CHANNEL> EQ aggregate(GLOBAL_AW!UNITS_STORED using GLOBAL_AW!- GLOBAL.DEFAULTAGGMAP1.AGGREGATIONDEFINITION COUNTVAR GLOBAL_AW!- UNITS_COUNTVAR) PROPERTY 'AW$CLASS' 'IMPLEMENTATION' PROPERTY 'AW$CREATEDBY' 'AW$XML' PROPERTY 'AW$LASTMODIFIED' '25SEP03_14:01:54' PROPERTY 'AW$LOGICAL_NAME' 'UNITS' PROPERTY 'AW$PARENT_NAME' 'UNITS_CUBE' PROPERTY 'AW$ROLE' 'MEASUREDEF' PROPERTY 'AW$STATE' 'VALID_MEMBER'
Or you can use the OBJ function to get the value of a specific property:
SHOW OBJ(PROPERTY 'AW$PARENT_NAME', 'UNITS') UNITS_CUBE
The dimensions of a cube are typically hierarchical in nature and thus have levels and hierarchies. Dimensions in an analytic workspace are frequently called embedded total dimensions because they contain members at all levels, and thus are used to define measures with aggregate data. Dimension members are acquired from multiple level columns of a relational dimension table.
An embedded total dimension has, in addition to the dimension object, at least one level and one hierarchy. A flat dimension does not require them.
All dimensions have a default order attribute, as described in "Standard Form Attributes". Time attributes must have end date and time span attributes.
For additional information about dimensions, refer to "Implementation Class Objects".
A dimdef dimension (that is, a dimension used in a cube) in an analytic workspace has the name defined in the metadata, such as TIME or PRODUCT, and may have a prefix specified in the build. The dimension has a TEXT data type unless you redefine it before loading the dimension members. Dimension members may have a level prefix added to the source values.
The analytic workspace dimension members may be exactly the same as those in the relational dimension table, or they may have a level prefix. The prefix is an option in the build. Example 8-1 shows how the Global PRODUCT dimension members would appear if a prefix were specified in the build. (The Global star schema provides surrogate keys, so no prefix is actually needed to assure unique dimension members across levels.)
All dimension members are sorted during the load process. For the Time dimension, the members are sorted by level and by end-date within the levels. This order is required to support time-series analysis, which is based on the relative position of time periods within the dimension. Other dimensions are sorted by level and alphanumerically by dimension member within the levels. A default order attribute identifies the original order in which the dimension members were loaded into the analytic workspace.
Table 8-1 describes the OLAP DML properties of a dimdef dimension. For descriptions of the properties independent of the object type, refer to Appendix A.
Table 8-1 Dimdef Dimension Properties
| Property | Value |
|---|---|
AW$CLASS |
IMPLEMENTATION |
AW$CREATEDBY |
Creator of the dimension; the Refresh wizard requires a value of AW$CREATE, which indicates that the dimension was created by AWCREATE programs |
AW$LASTMODIFIED |
Date and time the dimension was last accessed by an analytic workspace tool |
AW$LOGICAL_NAME |
OLAP Catalog source name |
AW$PARENT_NAME |
NA |
AW$ROLE |
DIMDEF |
AW$STATE |
ACTIVE |
AW$TYPE |
Time for a Time dimension, otherwise NA |
DESCRIPTION |
Optional description of the dimension |
LOAD_TYPE |
Type of load performed for last refresh, either FULL_LOAD_ADDITIONS_ONLY or FULL_LOAD, as specified by DBMS_AWM.CREATE_AWDIMLOAD_SPEC; used by AWCREATE programs |
SOURCE_NAME |
OLAP Catalog source dimension; used by AWCREATE programs and the active catalog |
SOURCE_OWNER |
Owner of OLAP Catalog source metadata; used by AWCREATE programs and the active catalog |
UNIQUE_RDBMS_KEY |
YES if source dimension tables provided unique keys across levels, or NO if level names were prefixed to the keys to assure uniqueness, as specified by DBMS_AWM.SET_AWDIMLOAD_SPEC_PARAMETER; used by AWCREATE programs |
DISPLAY_NAME |
OLAP Catalog source display name or DBMS_AWM.SET_AWDIMLOAD_SPEC_PARAMETER setting; used by AWCREATE programs |
P_DISPLAY_NAME |
OLAP Catalog plural display name or DBMS_AWM.SET_AWDIMLOAD_SPEC_PARAMETER setting. |
Standard form metadata for dimensions is stored in these objects:
ALL_DIMENSIONS dimension
ALL_DESCRIPTIONS variable
AW_NAMES variable
DIM_LEVELS valueset
The ALL_DIMENSIONS dimension contains the names of all dimensions in this format:
workspace.dimension.DIMENSION
For example: GLOBAL_AW.PRODUCT.DIMENSION
ALL_DIMENSIONS is a base dimension of the ALL_OBJECTS concat dimension. ALL_OBJECTS dimensions ALL_DESCRIPTIONS and AW_NAMES, so these catalogs have an entry for each measure.
The ALL_DESCRIPTIONS variable contains short, long, and plural names for the dimensions. All objects have a short name acquired from the metadata, but may or may not have long and plural names.
The following objects support dimension hierarchies:
Hierlist dimension
Member_parentrel relation
Member_gid variable
Member_inhier variable
The values of the member_parentrel relation, member_gid variable, and member_inhier variable can be different for different hierarchies, so the hierlist dimension is used to define these objects.
For additional information about hierarchies, refer to "Implementation Class Objects" and "Features Class Objects".
A hierlist dimension stores the names of the hierarchies defined for a particular dimension. The names of the hierarchies are acquired from the OLAP Catalog. This text dimension typically has a name of dimdef_HIERLIST.
Example 8-2 shows the contents of CUSTOMER_HIERLIST in the GLOBAL analytic workspace.
Table 8-2 describes the OLAP DML properties of a hierlist dimension. For descriptions of the properties independent of the object type, refer to Appendix A.
Table 8-2 Hierlist Dimension Properties
| Property | Value |
|---|---|
AW$CLASS |
IMPLEMENTATION |
AW$CREATEDBY |
Creator of the dimension; the Refresh wizard requires a value of AW$CREATE, which indicates that the dimension was created by AWCREATE programs |
AW$LASTMODIFIED |
Date and time the dimension was last accessed by an analytic workspace tool |
AW$PARENT_NAME |
Dimdef dimension |
AW$ROLE |
HIERLIST |
AW$STATE |
CREATED |
A member_parentrel relation defines the hierarchical relationship among dimension members by identifying the parent of each member. This relation provides the essential hierarchical support for the dimension. This information is acquired from the relational dimension table. The parent relation is named dimdef_PARENTREL.
A member_parentrel relation is a type of self-relation, in which the only valid values are dimension members. Example 8-3 shows the member_parentrel relation for the CHANNEL dimension in the GLOBAL analytic workspace. The relation defines a two-level hierarchy in which 1 is the parent of 2, 3, and 4.
Table 8-3 describes the OLAP DML properties of a member_parentrel relation. For descriptions of the properties independent of the object type, refer to Appendix A.
Table 8-3 Member_Parentrel Relation Properties
| Property | Value |
|---|---|
AW$CLASS |
IMPLEMENTATION |
AW$CREATEDBY |
Creator of the relation; the Refresh tool requires a value of AW$CREATE, which indicates that the relation was created by AWCREATE programs |
AW$LASTMODIFIED |
Date and time the relation was last accessed by an analytic workspace tool |
AW$PARENT_NAME |
Dimdef dimension |
AW$ROLE |
MEMBER_PARENTREL |
AW$STATE |
CREATED |
Member_gid variables improve the performance of views for the OLAP API. This integer variable identifies the depth in the hierarchy of each dimension member. This information is generated by the GROUPINGID command in the OLAP DML; refer to its entry in the Oracle OLAP DML Reference for information about its contents. The standard name for a member_gid variable is dimdef_GID.
Example 8-4 shows the member_gid variable for the CHANNEL dimension in the GLOBAL analytic workspace. It shows that channels 2, 3, and 4 are at the base level (0) and channel 1 is one level deep (1).
Table 8-4 describes the OLAP DML properties of a member_gid variable. For descriptions of the properties independent of the object type, refer to Appendix A.
Table 8-4 Member_GID Variable Properties
| Property | Value |
|---|---|
AW$CLASS |
FEATURES |
AW$CREATEDBY |
Creator of the variable; the Refresh tool requires a value of AW$CREATE, which indicates that the variable was created by AWCREATE programs |
AW$LASTMODIFIED |
Date and time the variable was last accessed by an analytic workspace tool |
AW$PARENT_NAME |
Dimdef dimension |
AW$ROLE |
MEMBER_GID |
AW$STATE |
CREATED |
Member_inhier variables are used to improve the performance of views for the OLAP API. This Boolean variable identifies whether a dimension member belongs to a level that is included in a particular hierarchy. The information is acquired from the OLAP Catalog metadata, and typically is useful only for dimensions with multiple hierarchies. The standard name for a member_inhier variable is dimension_INHIER.
Example 8-5 shows the contents of the member_inhier variable for the CUSTOMER dimension of the GLOBAL analytic workspace. YES indicates that the dimension member is in the hierarchy; NA indicates that it is not in the hierarchy.
Example 8-5 CUSTOMER Member_Inhier Variable in GLOBAL
LIMIT customer TO customer_levelrel EQ 'SHIP_TO' "Select base-level members
LIMIT customer KEEP FIRST 1 "Keep just the first one
LIMIT customer ADD ANCESTORS USING customer_parentrel "Add its ancestors
REPORT DOWN customer W 15 customer_inhier
--------CUSTOMER_INHIER--------
-------CUSTOMER_HIERLIST-------
CUSTOMER SHIPMENTS MARKET_SEGMENT
-------------- --------------- ---------------
46 yes yes
21 yes NA
22 NA yes
10 yes NA
5 NA yes
1 yes NA
7 NA yes
Table 8-5 describes the OLAP DML properties of a member_inhier variable. For descriptions of the properties independent of the object type, refer to Appendix A.
Table 8-5 Member_Inhier Variable Properties
| Property | Value |
|---|---|
AW$CLASS |
FEATURES |
AW$CREATEDBY |
Creator of the variable; the Refresh tool requires a value of AW$CREATE, which indicates that the variable was created by AWCREATE programs |
AW$LASTMODIFIED |
Date and time the variable was last accessed by an analytic workspace tool |
AW$PARENT_NAME |
Dimdef dimension |
AW$ROLE |
MEMBER_INHIER |
AW$STATE |
CREATED |
Standard form metadata for hierarchies is stored in these objects:
ALL_HIERARCHIES dimension
ALL_DESCRIPTIONS variable
DIM_HIERARCHIES valueset
DEFAULT_HIER relation
The ALL_HIERARCHIES dimension contains the names of all hierarchies in this format:
workspace.dimension.hierarchy.HIERARCHY
For example: GLOBAL_AW.CUSTOMER.SHIPMENTS.HIERARCHY
ALL_HIERARCHIES is a base dimension of the ALL_OBJECTS concat dimension. ALL_OBJECTS dimensions ALL_DESCRIPTIONS and AW_NAMES. ALL_DESCRIPTIONS provides values for the hierarchies, but AW_NAMES does not.
The ALL_DESCRIPTIONS variable contains short, long, and plural names for the hierarchies. All objects have a short name acquired from the metadata, but may or may not have long and plural names.
Levels are the basis of dimension hierarchies. A level belongs to one or more hierarchies. These objects support level definitions:
Levellist dimension
Member_levelrel relation
Member_familyrel relation
For additional information about levels, refer to "Implementation Class Objects" and "Features Class Objects".
A levellist dimension stores the names of all levels for all hierarchies defined for a particular dimension. The information is acquired from the OLAP Catalog. This text dimension typically has the name dimdef_LEVELLIST.
Example 8-6 shows the CUSTOMER levellist dimension in GLOBAL, which contains the levels for both the SHIPMENTS and MARKET_SEGMENT hierarchies.
Table 8-6 describes the properties of a levellist dimension. For descriptions of the properties independent of the object type, refer to Appendix A.
Table 8-6 Levellist Dimension Properties
| Property | Value |
|---|---|
AW$CLASS |
IMPLEMENTATION |
AW$CREATEDBY |
Creator of the level; the Refresh tool requires a value of AW$CREATE, which indicates that the level was created by AWCREATE programs |
AW$LASTMODIFIED |
Date and time the level was last accessed by an analytic workspace tool |
AW$LOGICAL_NAME |
NA |
AW$PARENT_NAME |
Dimdef dimension |
AW$ROLE |
LEVELLIST |
AW$STATE |
CREATED |
A member_levelrel relation identifies the level of each dimension member. It facilitates the selection of dimension members by level. The information is acquired from the relational fact tables. This text dimension typically has the name dimdef_LEVELREL.
Example 8-7 shows the CUSTOMER member_levelrel relation in GLOBAL.
Example 8-7 CUSTOMER Member_Levelrel Relation in GLOBAL
LIMIT customer TO '62' "Select customer 62 LIMIT customer ADD ANCESTORS USING customer_parentrel "Add ancestors REPORT DOWN customer W 20 customer_levelrel CUSTOMER CUSTOMER_LEVELREL -------------- -------------------- 62 SHIP_TO 21 WAREHOUSE 27 ACCOUNT 10 REGION 6 MARKET_SEGMENT 1 ALL_CUSTOMERS 7 TOTAL_MARKET
Table 8-7 describes the OLAP DML properties of a member_levelrel relation. For descriptions of the properties independent of the object type, refer to Appendix A.
Table 8-7 Member_Levelrel Relation Properties
| Property | Value |
|---|---|
AW$CLASS |
IMPLEMENTATION |
AW$CREATEDBY |
Creator of the level; the Refresh tool requires a value of AW$CREATE, which indicates that the level was created by AWCREATE programs |
AW$LASTMODIFIED |
Date and time the level was last accessed by an analytic workspace tool |
AW$PARENT_NAME |
Dimdef dimension |
AW$ROLE |
MEMBER_LEVELREL |
AW$STATE |
CREATED |
Member_familyrel relations improve the performance of views for Oracle Discoverer. It provides a crosstab with the full parentage of each dimension member within a single row. The standard name for a family relation is dimdef_FAMILYREL.
Example 8-8 shows the CUSTOMER family relation in GLOBAL.
Example 8-8 CUSTOMER Family Relation in GLOBAL
LIMIT customer TO '78' "Select customer 78
LIMIT customer ADD ANCESTORS USING customer_parentrel "Add the ancestors
LIMIT customer_hierlist TO 'SHIPMENTS' "Select the SHIPMENTS hierarchy
REPORT customer_familyrel
CUSTOMER_HIERLIST: SHIPMENTS
-----------------------------CUSTOMER_FAMILYREL-----------------------------
----------------------------------CUSTOMER----------------------------------
CUSTOMER_LEVEL
LIST 78 21 31 10 2 1 7
-------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
TOTAL_MARKET NA NA NA NA NA NA NA
MARKET_SEGMENT NA NA NA NA NA NA NA
ACCOUNT NA NA NA NA NA NA NA
ALL_CUSTOMERS 1 1 NA 1 NA 1 NA
REGION 10 10 NA 10 NA NA NA
WAREHOUSE 21 21 NA NA NA NA NA
SHIP_TO 78 NA NA NA NA NA NA
Table 8-8 describes the OLAP DML properties of a member_familyrel relation. For descriptions of the properties independent of the object type, refer to Appendix A.
Table 8-8 Member_Familyrel Relation Properties
| Property | Value |
|---|---|
AW$CLASS |
FEATURES |
AW$CREATEDBY |
Creator of the relation; the Refresh tool requires a value of AW$CREATE, which indicates that the relation was created by AWCREATE programs |
AW$LASTMODIFIED |
Date and time the relation was last accessed by an analytic workspace tool |
AW$PARENT_NAME |
Dimdef dimension |
AW$ROLE |
MEMBER_FAMILYREL |
AW$STATE |
CREATED |
Standard form metadata for levels is stored in these objects:
ALL_LEVELS dimension
ALL_DESCRIPTIONS variable
DIM_LEVELS valueset
The ALL_LEVELS dimension contains the names of all levels in this format:
workspace.dimension.level.LEVEL
For example: GLOBAL_AW.TIME.Quarter.LEVEL
ALL_LEVELS is a base dimension of the ALL_OBJECTS concat dimension. ALL_OBJECTS dimensions ALL_DESCRIPTIONS and AW_NAMES. ALL_DESCRIPTIONS provides values for the levels, but AW_NAMES does not.
Attributes are defined as variables, usually with a text data type. They provide information about the dimension members, and are typically acquired from relational dimension tables. An attribute is dimensioned by a dimdef dimension, a hierlist dimension, and the ALL_LANGUAGES dimension.
Dimension members are sorted during a load, and an attribute named dimension_ORDER identifies the original order in which they were fetched, row by row, into the analytic workspace.
Table 8-9 describes the OLAP DML properties for attributes. For descriptions of the properties independent of the object type, refer to Appendix A.
Table 8-9 Attribute Properties
| Property | Value |
|---|---|
AW$CLASS |
IMPLEMENTATION |
AW$CREATEDBY |
Creator of the attribute; the Refresh tool requires a value of AW$CREATE, which indicates that the attribute was created by AWCREATE programs |
AW$LASTMODIFIED |
Date and time the attribute was last accessed by an analytic workspace tool |
AW$LOGICAL_NAME |
OLAP Catalog source attribute name |
AW$PARENT_NAME |
Dimdef dimension |
AW$ROLE |
ATTRDEF |
AW$STATE |
CREATED |
AW$TYPE |
Long Description, Short Description, Time Span, End Date, and DEFAULT_ORDER are currently used as special attribute types |
SOURCE_DATATYPE |
The basic data type of the source column, such as VARCHAR2 or DATE; used by AWCREATE programs |
SOURCE_DIMNAME |
OLAP Catalog source dimension name; used by AWCREATE programs |
SOURCE_NAME |
OLAP Catalog source attribute name; used by AWCREATE programs and the active catalog |
SOURCE_OWNER |
Owner of OLAP Catalog source metadata; used by AWCREATE programs and the active catalog |
For additional information about standard form attributes, refer to the "Implementation Class Objects" and "Catalogs Class Objects".
The ALL_LANGUAGES dimension enables an analytic workspace to support multiple languages. It initially has one member, which identifies the database (and thus the analytic workspace) territory and language, for example, AMERICAN_AMERICA.
Table 8-10 describes the OLAP DML properties of the ALL_LANGUAGES dimension. For descriptions of the properties independent of the object type, refer to Appendix A.
Standard form metadata for attributes is stored in these objects:
ALL_ATTRIBUTES dimension
ALL_DESCRIPTIONS variable
AW_NAMES variable
DIM_ATTRIBUTES valueset
The ALL_ATTRIBUTES dimension contains the names of all attributes in this format:
workspace.dimension.attribute.ATTRIBUTE
For example: GLOBAL_AW.TIME.End_Date.ATTRIBUTE
ALL_ATTRIBUTES is a base dimension of the ALL_OBJECTS concat dimension. ALL_OBJECTS dimensions ALL_DESCRIPTIONS and AW_NAMES, so these catalogs have an entry for each attribute.
Each measure is defined by two workspace objects: a variable and a formula.
For additional information about standard form measures, refer to "Implementation Class Objects" and "Extensions Class Objects".
A measure variable initially contains only base-level data, which is typically acquired from a relational fact table. If you deploy an aggregation plan, then the variable also contains precalculated aggregate levels.
A measure variable has a DECIMAL data type unless you redefined it before loading data during the initial build. The standard name for a measure variable is measuredef_VARIABLE.
Table 8-11 describes its properties. For descriptions of the properties independent of the object type, refer to Appendix A.
Table 8-11 Measuredef_VARIABLE Properties
| Property | Value |
|---|---|
AW$CLASS |
EXTENSION |
AW$CREATEDBY |
Creator of the cube; the Refresh tool requires a value of AW$CREATE, which indicates that the object was created by AWCREATE programs |
AW$LASTMODIFIED |
Date and time the cube was last accessed by an analytic workspace tool |
AW$PARENT_NAME |
Name of the measure |
AW$ROLE |
VARIABLE |
AW$SEGWDTH_CMD |
CHGDFN command for defining the segment size |
AW$STATE |
CREATED |
A measuredef formula calculates the aggregate data using a set of aggregation rules stored in an aggmap. Its standard name is the name of the measure. Table 8-12 describes its properties. For descriptions of the properties independent of the object type, refer to Appendix A.
Table 8-12 Measuredef Formula Properties
| Property | Value |
|---|---|
AW$CLASS |
IMPLEMENTATION |
AW$COMPSPEC |
Name of the current deployed aggmap |
AW$CREATEDBY |
Creator of the measure; some tools may require a value of AW$CREATE, which indicates that the measure was created by AWCREATE programs |
AW$LASTMODIFIED |
Date and time the measure was last accessed by an analytic workspace tool |
AW$LOGICAL_NAME |
OLAP Catalog measure name |
AW$PARENT_NAME |
Cube name |
AW$ROLE |
MEASUREDEF |
AW$STATE |
CREATED |
MEASCOLS |
Name of the analytic workspace catalog (named cube.MSCL) that identifies the source column in the fact table; used by AWCREATE programs |
SOURCE_CUBENAME |
Name of the OLAP Catalog source cube; used by AWCREATE programs |
SOURCE_NAME |
OLAP Catalog source measure; used by AWCREATE programs and the active catalog |
SOURCE_OWNER |
Owner of OLAP Catalog source metadata; used by AWCREATE programs and the active catalog |
Standard form metadata for measures is stored in these objects:
ALL_MEASURES dimension
ALL_DESCRIPTIONS variable
AW_NAMES variable
CUBE_MEASURES valueset
The ALL_MEASURES dimension contains the names of all measures in this format:
workspace.cube.measure.MEASURE
For example: GLOBAL_AW.UNITS_CUBE.UNITS.MEASURE
ALL_MEASURES is a base dimension of the ALL_OBJECTS concat dimension. ALL_OBJECTS dimensions ALL_DESCRIPTIONS and AW_NAMES, so these catalogs have an entry for each measure.
The ALL_DESCRIPTIONS variable contains short, long, and plural names for the measures. All objects have a short name acquired from the metadata, but may or may not have long and plural names.
Cubes are implemented as text dimensions that list the names of the dimensions (sometimes called the edges) of the cube. A default aggregation map and composite dimension are also defined for all measures in the cube.
For additional information about standard form cubes, refer to "Implementation Class Objects".
The cubedef dimension lists the names of the dimdef dimensions, such as TIME and PRODUCT, for measures in the cube. The standard name for this dimension is the name of the logical cube, such as UNITS_CUBE. The name has a prefix if you specified one in the build options.
Example 8-9 shows the cubedef dimension for the UNITS_CUBE in GLOBAL.
Table 8-13 describes the OLAP DML properties of a cubedef dimension. For descriptions of the properties independent of the object type, refer to Appendix A.
Table 8-13 Cubedef Properties
| Property | Value |
|---|---|
AGGMAPLIST |
Single- or multiline text string with the names of all aggmaps defined for this cube, used by AWCREATE programs |
AW$CLASS |
IMPLEMENTATION |
AW$CREATEDBY |
Creator of the cube; the Refresh tool and the Aggregation Plan tool requires a value of AW$CREATE, which indicates that the object was created by AWCREATE programs |
AW$LASTMODIFIED |
Date and time the cube was last accessed by an analytic workspace tool |
AW$LOADPRGS |
Name of the OLAP DML program used to fetch data from the relational schema into the analytic workspace |
AW$LOGICAL_NAME |
Logical name of the source cube, such as a cube defined in the OLAP Catalog |
AW$LOOPSPEC |
The workspace composite used to define variables for this cube |
AW$PARENT_NAME |
NA |
AW$ROLE |
CUBEDEF |
DISPLAY_NAME |
OLAP Catalog source display name |
FORMDIMS |
Ordered list of dimensions for measure formulas; used by AWCREATE programs |
LOADNAME |
The name of the load program used to populate the cube; used by AWCREATE programs |
LOADTYPE |
LOAD_DATA when data is loaded as part of the build, or LOAD_PROGRAM if the DML load program is created but not run; these are keywords for DBMS_AWM.CREATE_AWCUBELOAD_SPEC and are used by AWCREATE programs |
SOURCE_NAME |
OLAP Catalog source cube; used by AWCREATE programs and the active catalog |
SOURCE_OWNER |
Owner of OLAP Catalog source metadata; used by AWCREATE programs and the active catalog |
SYS_DIMS |
Ordered list of dimensions for measure variables, usually Time followed by a composite of all other dimensions; used by AWCREATE programs |
SYS_DIMSML |
Alphabetized list of dimensions for the cube; used by AWCREATE programs |
A default aggmap is created for each cube, which specifies runtime aggregation across all dimensions. This aggmap is initially referenced by the formulas for all measures associated with the cube. When you create and deploy aggregation plans using the wizards in Analytic Workspace Manager, you create new aggmaps and change the formulas for specified measures.
The standard name for default aggmaps is cubedef_AGGMAP_AWCREATEDDEFAULT_1, for example, UNITS_CUBE_AGGMAP_AWCREATEDDEFAULT_1.
Table 8-14 describes the properties of a comspec aggmap. For descriptions of the properties independent of the object type, refer to Appendix A.
Table 8-14 Comspec Aggmap Properties
| Property | Value |
|---|---|
AW$CLASS |
IMPLEMENTATION |
AW$COUNTVARCMD |
Name of the integer variable used when the aggmap calculates an average; otherwise NA |
AW$CREATEDBY |
Creator of the aggmap; some tools may require a value of AW$CREATE, which indicates that the aggmap was created by AWCREATE programs |
AW$LASTMODIFIED |
Date and time the aggmap was last accessed by an analytic workspace tool |
AW$PARENT_NAME |
Workspace cube name |
AW$ROLE |
COMSPEC |
AW$STATE |
CREATED |
ISDFLTAGGMAP |
YES for the default aggregation map, or NO for aggmaps created after the initial build; used during a data refresh |
Using the Create Analytic Workspace wizard, you can accept the default composite or define a composite yourself.
A default composite is named cubedef_COMPOSITE and consists of all dimensions of the cube except Time. The dimensions are ordered from the one with the most members to the one with the least members.
A custom composite has the name and characteristics that you assigned to it.
Table 8-15 describes the properties of a loopspec composite dimension. For descriptions of the properties independent of the object type, refer to Appendix A.
Table 8-15 Loopspec Composite Properties
| Property | Value |
|---|---|
AW$CLASS |
IMPLEMENTATION |
AW$CREATEDBY |
Creator of the composite; some tools may require a value of AW$CREATE, which indicates that the composite was created by AWCREATE programs |
AW$LASTMODIFIED |
Date and time the composite was last accessed by an analytic workspace tool |
AW$PARENT_NAME |
Cubedef dimension |
AW$ROLE |
LOOPSPEC |
AW$STATE |
CREATED |
For additional information about loopspec composites, refer to "Implementation Class Objects".
Standard form metadata for cubes is stored in these objects:
ALL_CUBES dimension
ALL_DESCRIPTIONS variable
AW_NAMES variable
CUBE_MEASURES valueset
The ALL_CUBES dimension contains the names of all cubes in this format:
workspace.cube.CUBE
For example: GLOBAL_AW.UNITS_CUBE.CUBE
ALL_CUBES is a base dimension of the ALL_OBJECTS concat dimension. ALL_OBJECTS dimensions ALL_DESCRIPTIONS and AW_NAMES, so these catalogs have an entry for each cube.
The ALL_DESCRIPTIONS variable contains short, long, and plural names for the cubes. All objects have a short name acquired from the metadata, but may or may not have long and plural names.
Database standard form requires a Catalogs class of objects. These objects hold information about the objects in the analytic workspace that implement the logical model. Table 8-16 describes these objects. For additional information about the catalogs, refer to "Catalogs Class Objects".
Table 8-16 Standard Form Catalogs
| Catalog | Object Type | Contents |
|---|---|---|
ALL_ATTRIBUTES |
Dimension | The full name of each attribute of each workspace dimension in the form schema.dimension.attribute.ATTRIBUTE |
ALL_CUBES |
Dimension | The full name of each workspace cube (that is, a dimension whose values are the dimensions of a cube) in the form schema.cube.CUBE |
ALL_DESCRIPTIONS |
Variable | Contains the short, long, and plural descriptions of the logical objects |
ALL_DIMENSIONS |
Dimension | The full name of each workspace data dimension (that is, dimensions used in data cubes) in the form schema.dimension.DIMENSION |
ALL_HIERARCHIES |
Dimension | The full name of each hierarchy of each workspace dimension in the form schema.dimension.hierarchy.HIERARCHY |
ALL_LEVELS |
Dimension | The full name of each level of each workspace dimension in the form schema.dimension.level.LEVEL |
ALL_MEASURES |
Dimension | The full name of each workspace measure (that is, a formula that returns a fully solved measure) in the form schema.measure.MEASURE. |
ALL_OBJECTS |
Concat dimension | ALL_DIMENSIONS, ALL_CUBES, ALL_MEASURES, ALL_HIERARCHIES, ALL_LEVELS, and ALL_ATTRIBUTES |
AW_NAMES |
Variable | The names of the analytic workspace objects that implement each logical object defined by the source metadata |
CUBE_MEASURES |
Valueset | A list of measures that belong to each cube |
DEFAULT_HIER |
Relation | The full name of the default hierarchy for each dimension |
DIM_ATTRIBUTES |
Valueset | A list of attributes that belong to each dimension |
DIM_HIERARCHIES |
Valueset | A list of hierarchies that belong to each dimension |
DIM_LEVELS |
Valueset | A list of levels that belong to each dimension |
The build process creates numerous objects within an analytic workspace to support the enabler for the OLAP API and BI Beans, and the active catalog. The enabler also creates some objects. Table 8-17 describes the catalogs used by the OLAP API.
|
Note: The OLAP API Enabler catalogs may change or disappear in future software releases. |
Table 8-17 OLAP API Enabler Catalogs
| Catalog | Object Type | Contents |
|---|---|---|
__SYS_HIERCJT |
Conjoint | The combinations of dimension hierarchies for which a fact view is required; created for transient use during enablement |
__SYS_HIERCJT_BUILD |
Conjoint | The combinations of dimension hierarchies for which a fact view is required; created for transient use during cube refresh |
cube_NEWSNAPSHOT_DIM |
Dimension | An integer dimension for cube_NEWSNAPSHOT_VAR |
cube_SNAPSHOT_DIM |
Dimension | An integer dimension for cube_SNAPSHOT_VAR |
OLAP_SYS_ADTDIM |
Dimension | The names of the object types used by the OLAP_TABLE function to generate the views |
OLAP_SYS_ADTTBLDIM |
Dimension | The names of the table types used by the OLAP_TABLE function to generate the views |
OLAP_SYS_CUBENAME_DIM |
Dimension | The names of the cubes in the analytic workspace |
OLAP_SYS_CUBEVIEW_DIM |
Dimension | The names of the fact views defined for the analytic workspace; used by the ALL_OLAP2_CUBE_ENABLED_VIEW active catalog |
OLAP_SYS_DIMNAME_DIM |
Dimension | The names of the dimensions in the analytic workspace |
OLAP_SYS_DIMVIEW_DIM |
Dimension | The name of the relational dimension view for each hierarchy; used by the ALL_OLAP2_DIM_ENABLED_VIEW active catalog |
OLAP_SYS_VIEWDIM |
Dimension | The names of the relational views defined for the analytic workspace |
OLAP_SYS_ADTREL |
Relation | The name of the object type used by the OLAP_TABLE function for each relational view |
OLAP_SYS_ADTTBLREL |
Relation | The name of the table type used by the OLAP_TABLE function for each relational view |
OLAP_SYS_CUBENAME_REL |
Relation | The analytic workspace cube represented by each fact view |
OLAP_SYS_DIMNAME_REL |
Relation | The analytic workspace dimension represented by each dimension view. |
OLAP_SYS_CUBEVALSET |
Valueset | The names of fact views during cube refresh; otherwise, NA |
OLAP_SYS_DIMVALSET |
Valueset | The names of dimension views during dimension refresh; otherwise, NA |
cube_NEWSNAPSHOT_VAR |
Variable | Identifies the dimensions and hierarchies associated with a cube at the time that the cube is being enabled for the OLAP API. If this variable is identical to cube_SNAPSHOT_VAR, then the views are still current and do not need to be regenerated. |
cube_SNAPSHOT_VAR |
Variable | Identifies dimensions and hierarchies associated with a cube for the views currently generated for the OLAP API. |
cube_SYS_ENABLE |
Variable | A multiline text string with the names of the object type, table type, and views generated by the enabler for an analytic workspace cube |
dimension_SYS_ENABLE |
Variable | A multiline text string with the names of the object type, table type, and views generated by the enabler for an analytic workspace dimension |
OLAP_SYS_CUBEADTNAME_VAR |
Variable | The object type used by the OLAP_TABLE function to generate each fact view |
OLAP_SYS_CUBEAWOWNER_VAR |
Variable | The schema owner of each fact view; used by the ALL_OLAP2_CUBE_ENABLED_VIEW active catalog |
OLAP_SYS_CUBEHIERCOMBO_VAR |
Variable | An integer value for each combination of dimension hierarchies represented by a fact view; used by the ALL_OLAP2_CUBE_ENABLED_VIEW active catalog |
OLAP_SYS_CUBEHIERCOMBOSTR_VAR |
Variable | Text strings that identify the dimensions and hierarchies represented by each fact view; used by the ALL_OLAP2_CUBE_ENABLED_VIEWS active catalog |
OLAP_SYS_CUBENAME_VAR |
Variable | The analytic workspace cube represented by each fact view; used by the ALL_OLAP2_CUBE_ENABLED_VIEW active catalog |
OLAP_SYS_CUBETBLNAME_VAR |
Variable | The table type used by the OLAP_TABLE function to generate each fact view |
OLAP_SYS_CUBEUSERVIEW_VAR |
Variable | New names assigned to workspace cubes using the CWM2_OLAP_CUBE.SET_CUBE_NAME procedure, or NA when new names have not been defined; used by the ALL_OLAP2_CUBE_ENABLED_VIEW active catalog |
OLAP_SYS_DIMADTNAME_VAR |
Variable | The name of the object type used by the OLAP_TABLE function for each dimension view |
OLAP_SYS_DIMAWOWNER_VAR |
Variable | The schema owner of each dimension view; used by the ALL_OLAP2_DIM_ENABLED_VIEW active catalog |
OLAP_SYS_DIMHIERNAME_VAR |
Variable | The name of the hierarchy represented by each dimension view; used by the ALL_OLAP2_DIM_ENABLED_VIEW active catalog |
OLAP_SYS_DIMHIERPOS_VAR |
Variable | The numerical position of each hierarchy in the dimension_HIERLIST hierarchy dimension. |
OLAP_SYS_DIMNAME_VAR |
Variable | The analytic workspace name of the dimension represented by each dimension view; used by the ALL_OLAP2_DIM_ENABLED_VIEW active catalog |
OLAP_SYS_DIMTBLNAME_VAR |
Variable | The name of the table type used by the OLAP_TABLE function for each dimension view. |
OLAP_SYS_DIMUSERVIEW_VAR |
Variable | New names assigned to workspace cubes using the CWM2_OLAP_DIMENSION.SET_DIMENSION_NAME procedure, or NA when new names have not been defined; used by the ALL_OLAP2_DIM_ENABLED_VIEW active catalog |
OLAP_SYS_LIMITMAP |
Variable | The limit map used by the OLAP_TABLE function for each relational view |
Several catalogs are used during the build and refresh process, and currently persist in the analytic workspace. Some of them are also used transiently during the enablement process for Oracle Discoverer. Table 8-18 describes the AWCREATE catalogs.
|
Note: TheAWCREATE catalogs may change or disappear in future software releases. |
Table 8-18 AWCREATE Catalogs
| Catalog | Object Type | Contents |
|---|---|---|
dimension_SRCCOMPOSITE |
Composite | A composite dimension composed of the dimension_HIERLIST, dimension_LEVELLIST, and dimension_LEVELCOLLIST dimensions |
cube_HIERCJT |
Conjoint | The names of the hierarchies for the dimensions of the cube |
dimension_LEVELCOLLIST |
Dimension | Integer values |
cube_HIERCJT.DMKY |
Variable | The name of the key column of source dimension table for the dimensions of the cube |
cube_HIERCJT.DMLV |
Variable | The name of the dimension level at which data is stored |
cube_HIERCJT.FT |
Variable | The name of the fact table that is the source for the cube |
cube_HIERCJT.HC |
Variable | Integer values |
cube_measure.MSCL |
Variable | The name of the source column for the measure |
dimension_attribute_SRCATTRCOL |
Variable | The names of the source columns for the attributes by hierarchy and level |
dimension_attribute_SRCATTROWNER |
Variable | The name of the schema owner of the source dimension table for the attributes by hierarchy and level |
dimension_attribute_SRCATTRTBL |
Variable | The name of the source dimension table for the attributes by hierarchy and level |
dimension_LEVELCOLMAP |
Variable | The source dimension value corresponding to each dimension member in the analytic workspace; the values can acquire a prefix during the build |
dimension_SRCLVLCOL |
Variable | The name of the source column for the level |
dimension_SRCLVLOWNER |
Variable | The name of the schema owner of the source dimension table |
dimension_SRCLVLPNTCOL |
Variable | The name of the source column for the parent level |
dimension_SRCLVLTBL |
Variable | The name of the source dimension table |
|
 Copyright © 2002, 2003 Oracle Corporation All Rights Reserved. |
|