Skip Headers

Oracle® OLAP Application Developer's Guide
10g Release 1 (10.1)

Part Number B10333-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

8 Exploring a Standard Form Analytic Workspace

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:


See Also:

Appendix A for the complete database standard form specification.

8.1 About Workspaces Created Using OLAP Tools

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.

8.1.1 About Database Standard Form

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.


See Also:

Appendix A for a full description of the database standard form convention.

8.1.2 Standard Form Implementation of the Logical Model

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

Standard form implementation of the basic logical model
Description of the illustration sflogic.gif

8.1.3 Additional Requirements for OLAP Tools

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.

8.2 Querying a Standard Form Analytic Workspace

Standard form enables you to discover the names of logical objects and the names of the physical workspace objects that implement the logical model.

8.2.1 Querying the Standard Form Catalogs

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.

8.2.2 Querying Properties

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$CLASS
AW$CREATEDBY
AW$LASTMODIFIED
AW$ROLE

The 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

8.3 Standard Form Dimensions

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".

8.3.1 Dimdef Dimension

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.

8.3.1.1 Contents of an Analytic Workspace Dimension

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.

Example 8-1 Global Products with Level Prefixes

LIMIT product TO product_levelrel EQ 'ITEM'
LIMIT product KEEP FIRST 3
LIMIT product ADD ANCESTORS USING product_parentrel
REPORT W 20 product
 
PRODUCT
--------------------
ITEM.13
ITEM.14
ITEM.15
FAMILY.4
CLASS.2
TOTAL_PRODUCT.1

8.3.1.2 Properties of an Analytic Workspace Dimdef Dimension

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.

8.3.2 Standard Form Metadata for Dimensions

Standard form metadata for dimensions is stored in these objects:

  • ALL_DIMENSIONS dimension

  • ALL_DESCRIPTIONS variable

  • AW_NAMES variable

  • DIM_LEVELS valueset

8.3.2.1 ALL_DIMENSIONS Dimension

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.

8.3.2.2 ALL_DESCRIPTIONS Variable for Dimensions

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.

8.3.2.3 AW_NAMES Variable for Dimensions

The AW_NAMES measure provides the fully qualified name of the workspace dimension object in this format:

schema.workspace!dimension

For example: GLOBAL_AW.GLOBAL!PRODUCT

8.3.2.4 DIM_LEVELS Valueset

The DIM_LEVELS valueset identifies the levels defined for each dimension.

8.4 Standard Form Hierarchies

The following objects support dimension hierarchies:

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".

8.4.1 Hierlist Dimension

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.

8.4.1.1 Contents of a Hierlist Dimension

Example 8-2 shows the contents of CUSTOMER_HIERLIST in the GLOBAL analytic workspace.

Example 8-2 GLOBAL Hierlist Dimension for CUSTOMER

REPORT W 20 customer_hierlist
 
CUSTOMER_HIERLIST
--------------------
SHIPMENTS
MARKET_SEGMENT

8.4.1.2 Properties of a Hierlist Dimension

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

8.4.2 Member_Parentrel Relation

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.

8.4.2.1 Contents of a Member_Parentrel Relation

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.

Example 8-3 CHANNEL Member_Parentrel Relation in GLOBAL

REPORT DOWN channel W 20 channel_parentrel
 
               -CHANNEL_PARENTREL--
               --CHANNEL_HIERLIST--
CHANNEL           CHANNEL_ROLLUP
-------------- --------------------
1              NA
2              1
3              1
4              1

8.4.2.2 Properties of a Member_Parentrel Relation

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

8.4.3 Member_Gid Variable

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.

8.4.3.1 Contents of a Member_GID Variable

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).

Example 8-4 CHANNEL Member_Gid in Global

REPORT DOWN channel W 20 channel_gid
 
               ----CHANNEL_GID-----
               --CHANNEL_HIERLIST--
CHANNEL           CHANNEL_ROLLUP
-------------- --------------------
1                                 1
2                                 0
3                                 0
4                                 0

8.4.3.2 Properties of a Member_Gid Variable

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

8.4.4 Member_Inhier Variable

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.

8.4.4.1 Contents of a Member_Inhier Variable

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

8.4.4.2 Properties of a Member_Inhier Variable

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

8.4.5 Standard Form Metadata for Hierarchies

Standard form metadata for hierarchies is stored in these objects:

  • ALL_HIERARCHIES dimension

  • ALL_DESCRIPTIONS variable

  • DIM_HIERARCHIES valueset

  • DEFAULT_HIER relation

8.4.5.1 ALL_HIERARCHIES Dimension

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.

8.4.5.2 ALL_DESCRIPTIONS Variable for Hierarchies

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.

8.4.5.3 DIM_HIERARCHIES Valueset

The DIM_HIERARCHIES valueset identifies the hierarchies defined for each dimension.

8.4.5.4 DEFAULT_HIER Relation

The DEFAULT_HIER relation identifies the default hierarchy for each dimension.

8.5 Standard Form Levels

Levels are the basis of dimension hierarchies. A level belongs to one or more hierarchies. These objects support level definitions:

For additional information about levels, refer to "Implementation Class Objects" and "Features Class Objects".

8.5.1 Levellist Dimension

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.

8.5.1.1 Contents of a Levellist Dimension

Example 8-6 shows the CUSTOMER levellist dimension in GLOBAL, which contains the levels for both the SHIPMENTS and MARKET_SEGMENT hierarchies.

Example 8-6 CUSTOMER Levellist Dimension in GLOBAL

REPORT W 20 customer_levellist
 
CUSTOMER_LEVELLIST
--------------------
TOTAL_MARKET
MARKET_SEGMENT
ACCOUNT
ALL_CUSTOMERS
REGION
WAREHOUSE
SHIP_TO

8.5.1.2 Properties of a Levellist Dimension

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

8.5.2 Member_Levelrel Relation

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.

8.5.2.1 Contents of a Level Relation

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

8.5.2.2 Properties of a Member_Levelrel Relation

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

8.5.3 Member_Familyrel Relation

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.

8.5.3.1 Contents of a Family Relation

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

8.5.3.2 Properties of a Member_Familyrel Relation

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

8.5.4 Standard Form Metadata for Levels

Standard form metadata for levels is stored in these objects:

  • ALL_LEVELS dimension

  • ALL_DESCRIPTIONS variable

  • DIM_LEVELS valueset

8.5.4.1 ALL_LEVELS Dimension

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.

8.5.4.2 ALL_DESCRIPTIONS Variable for Levels

The ALL_DESCRIPTIONS variable contains short, long, and plural names for the levels. All levels have a short name acquired from the metadata, but may or may not have long and plural names.

8.5.4.3 DIM_LEVELS Valueset

The DIM_LEVELS valueset identifies the levels defined for each dimension.

8.6 Standard Form Attributes

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".

8.6.1 ALL_LANGUAGES Dimension

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.

Table 8-10 ALL_LANGUAGES Dimension Properties

Property Value
AW$CLASS CATALOG
AW$CREATEDBY AW$CREATE
AW$LASTMODIFIED Date and time
AW$ROLE ALL_LANGUAGES
AW$STATE CREATED

8.6.2 Standard Form Metadata for Attributes

Standard form metadata for attributes is stored in these objects:

  • ALL_ATTRIBUTES dimension

  • ALL_DESCRIPTIONS variable

  • AW_NAMES variable

  • DIM_ATTRIBUTES valueset

8.6.2.1 ALL_ATTRIBUTES Dimension

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.

8.6.2.2 ALL_DESCRIPTIONS Variable for Attributes

The ALL_DESCRIPTIONS variable contains short, long, and plural names for the attributes. All objects have a short name acquired from the metadata, but may or may not have long and plural names.

8.6.2.3 AW_NAMES Variable for Attributes

The AW_NAMES measure provides a name for each attribute in this format:

schema.workspace!attribute

For example: GLOBAL_AW.GLOBAL!TIME_END_DATE

8.7 Standard Form Measures

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".

8.7.1 Measure Variable

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

8.7.2 Measuredef Formula

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

8.7.3 Standard Form Metadata for Measures

Standard form metadata for measures is stored in these objects:

  • ALL_MEASURES dimension

  • ALL_DESCRIPTIONS variable

  • AW_NAMES variable

  • CUBE_MEASURES valueset

8.7.3.1 ALL_MEASURES Dimension

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.

8.7.3.2 ALL_DESCRIPTIONS Variable for Measures

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.

8.7.3.3 AW_NAMES Variable for Measures

The AW_NAMES measure provides a name for each measure in this format:

schema.workspace!measure

For example: GLOBAL_AW.GLOBAL!UNITS

8.7.3.4 CUBE_MEASURES Valueset

The CUBE_MEASURES valueset identifies the measures for each cube.

8.8 Standard Form Cubes

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".

8.8.1 Cubedef Dimension

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.

8.8.1.1 Contents of a Cubedef Dimension

Example 8-9 shows the cubedef dimension for the UNITS_CUBE in GLOBAL.

Example 8-9 Units Cube Dimension in GLOBAL

REPORT units_cube
 
UNITS_CUBE
--------------
CHANNEL
CUSTOMER
PRODUCT
TIME

8.8.1.2 Properties of a Cubedef Dimension

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

8.8.2 Comspec Aggregation Map

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

8.8.3 Loopspec Composite Dimension

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".

8.8.4 Standard Form Metadata for Cubes

Standard form metadata for cubes is stored in these objects:

  • ALL_CUBES dimension

  • ALL_DESCRIPTIONS variable

  • AW_NAMES variable

  • CUBE_MEASURES valueset

8.8.4.1 ALL_CUBES Dimension

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.

8.8.4.2 ALL_DESCRIPTIONS Variable for Cubes

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.

8.8.4.3 AW_NAMES Variable for Cubes

The AW_NAMES measure provides a name for each cube in this format:

schema.workspace!cube

For example: GLOBAL_AW.GLOBAL!UNITS_CUBE

8.8.4.4 CUBE_MEASURES Valueset

The CUBE_MEASURES valueset identifies the measures for each cube.

8.9 Standard Form Catalogs

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

8.10 OLAP API Enabler Catalogs

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

8.11 AWCREATE Catalogs

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:

The AWCREATE 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