| Oracle® OLAP Reference 10g Release 1 (10.1) Part Number B10334-02 |
|
|
View PDF |
| Oracle® OLAP Reference 10g Release 1 (10.1) Part Number B10334-02 |
|
|
View PDF |
The DBMS_AWM package provides stored procedures for creating an analytic workspace cube from a star schema and enabling it for access by the OLAP API. The DBMS_AWM package is used by Analytic Workspace Manager. This chapter explains how to work with the DBMS_AWM procedures directly.
This chapter contains the following topics:
If your data is stored in a star or snowflake schema, then you can use the DBMS_AWM package to simplify the process of loading it into an analytic workspace.
The first step is to create OLAP Catalog metadata that describes the functionality of your schema in multidimensional terms, that is, as a cube with dimensions, attributes, and measures. You can then use the DBMS_AWM package to instantiate these objects in an analytic workspace, create relational views of the workspace objects, and optionally generate a secondary set of OLAP Catalog metadata that maps to the workspace views.
|
Note: Analytic workspaces created by theDBMS_AWM procedures are in database standard form, ensuring compatibility with related Oracle OLAP tools and utilities. See Oracle OLAP Application Developer's Guide for information about standard form. |
The DBMS_AWM package provides a feature–rich set of APIs that you can use to manage analytic workspaces. To effectively use these APIs, you will need to understand how the APIs work together to move data from a relational source to a multidimensional target and how they establish relational access to that target.
The basic flow of events involves the creation of three separate logical cubes:
Relational Source Cube. This cube must exist before you call any of the DBMS_AWM procedures. The cube's metadata is defined within the OLAP Catalog. Its data is unsolved (lowest level only) and stored in a star schema.
Multidimensional Target Cube. DBMS_AWM procedures define and populate this cube from the relational source cube. The cube's standard form metadata is defined in the analytic workspace. Its data is stored in the workspace, typically with full or partial summarization.
Relational Target Cube. DBMS_AWM procedures define this cube from the multidimensional target cube. The cube's metadata is defined within the OLAP Catalog. Its data is stored in the analytic workspace and accessed through relational views. The views present the data as fully solved (embedded totals for all level combinations).
The basic process of creating and enabling an analytic workspace with the DBMS_AWM package is illustrated in Figure 1-1.
Figure 1-1 Creating and Enabling an Analytic Workspace with DBMS_AWM
Before you can use the DBMS_AWM procedures, you must create a cube in the OLAP Catalog and map it to the source fact table and dimension tables. The source tables must be organized in a basic star or snowflake schema.
You can use Enterprise Manager, or you can write scripts that use the CWM2 PL/SQL packages, as described in Chapter 2. You can also use Oracle Warehouse Builder to create OLAP Catalog metadata.
This cube is the Relational Source Cube identified in Figure 1-1.
For each dimension of a cube defined in the OLAP Catalog, you must run a set of procedures in the DBMS_AWM package to accomplish the following general tasks:
Create a dimension load specification, which contains instructions for populating the dimension in the analytic workspace. The load specification may include a filter that identifies criteria for selecting data from the source dimension tables.
Create containers for the dimension in an analytic workspace.
Use the dimension load specification to populate the dimension in the analytic workspace from the source dimension tables.
After creating the cube's dimensions, run another set of procedures to create and populate the cube itself.
Create a cube load specification, which contains instructions for populating the cube's measures in the analytic workspace. The load specification may include a filter that identifies criteria for selecting data from the source fact table.
Create a composite specification, which contains instructions for ordering the cube's dimensions and storing sparse data in the analytic workspace.
Add the composite specification to the cube load specification.
Create containers for the cube in an analytic workspace.
Use the cube load specification to populate the cube's measures in the analytic workspace from the source fact table.
This cube is the Multidimensional Target Cube identified in Figure 1-1.
For the workspace cube, run a set of procedures to accomplish the following:
Once you have created, populated, and aggregated the cube in an analytic workspace, run another set of procedures to enable relational access. The enablement process consists of generating and running a set of enablement scripts. These scripts create the relational views that use the OLAP_TABLE function to access the workspace cube. The scripts may also create an OLAP Catalog cube that maps to the views.
The cube created by the enablement scripts is the Relational Target Cube identified in Figure 1-1.
To enable a workspace cube, you can either generate the scripts and run them yourself or you can use a one-step procedure to create and run the scripts automatically.
Two sets of views reveal metadata related to analytic workspaces. The Active Catalog views reveal metadata stored within analytic workspaces. The Analytic Workspace Maintenance views reveal metadata stored within the OLAP Catalog.
These views use OLAP_TABLE functions to return information about logical standard form objects within analytic workspaces. For example, you could query an Active Catalog view to obtain information about the dimensionality of a workspace cube. The Active Catalog view names have the prefix ALL_OLAP2_AW. For more information, see Chapter 3.
These views return information about building and maintaining analytic workspace cubes. For example, you could query an Analytic Workspace Maintenance view to obtain information about the load specifications associated with an analytic workspace dimension or cube. The Analytic Workspace Maintenance view names have the prefix ALL_AW. For more information, see Chapter 4.
The procedures in the DBMS_AWM package support methods on several types of logical entities. These entities are described in Table 1-1.
Table 1-1 Logical Entities in the DBMS_AWM Package
The methods you can perform on a dimension are described in Table 1-2.
Table 1-2 Methods on Dimensions in DBMS_AWM
| Method | Description | Procedure |
|---|---|---|
| Create | Create containers in an analytic workspace for a dimension defined in the OLAP Catalog. | CREATE_AWDIMENSION Procedure |
| Refresh | Use a dimension load specification to populate an analytic workspace dimension from the dimension tables of an OLAP Catalog dimension. | REFRESH_AWDIMENSION Procedure |
| Create access | Create a script to enable relational access to a dimension in an analytic workspace. | CREATE_AWCUBELOAD_SPEC Procedure |
| Delete access | Create a script to disable relational access to a dimension in an analytic workspace. | DELETE_AWDIMENSION_ACCESS Procedure |
| Set view name | Specify new names for the relational views of a dimension in an analytic workspace. | SET_AWDIMENSION_VIEW_NAME Procedure |
The methods you can perform on a cube are described in Table 1-3.
Table 1-3 Methods on Cubes in DBMS_AWM
| Method | Description | Procedure |
|---|---|---|
| Create | Create containers in an analytic workspace for a cube defined in the OLAP Catalog. | CREATE_AWCUBE Procedure |
| Refresh | Use a cube load specification to populate the measures of an analytic workspace cube from the fact table of an OLAP Catalog cube. | REFRESH_AWCUBE Procedure |
| Aggregate | Use an aggregation specification to aggregate the cube in the analytic workspace. | AGGREGATE_AWCUBE Procedure |
| Create Access | Create a script to enable relational access to a cube in an analytic workspace. | CREATE_AWCUBE_ACCESS Procedure |
| Delete access | Create a script to disable relational access to a cube in an analytic workspace | DELETE_AWCUBE_ACCESS Procedure |
| Set view name | Specify new names for the relational views of a cube's data in an analytic workspace. | SET_AWCUBE_VIEW_NAME Procedure |
The methods you can perform on a dimension load specification are described in Table 1-4.
Table 1-4 Methods on Dimension Load Specifications in DBMS_AWM
| Method | Description | Procedure |
|---|---|---|
| Create/Delete | Create or delete a dimension load specification. | CREATE_AWDIMLOAD_SPEC Procedure
DELETE_AWDIMLOAD_SPEC Procedure |
| Reset information | Change various components of a dimension load specification. | SET_AWDIMLOAD_SPEC_DIMENSION Procedure
SET_AWDIMLOAD_SPEC_LOADTYPE Procedure SET_AWDIMLOAD_SPEC_NAME Procedure SET_AWDIMLOAD_SPEC_PARAMETER Procedure |
| Add/Delete filter | Add or remove a filter from a dimension load specification. | ADD_AWDIMLOAD_SPEC_FILTER Procedure
DELETE_AWDIMLOAD_SPEC_FILTER Procedure |
The methods you can perform on a cube load specification are described in Table 1-5.
Table 1-5 Methods on Cube Load Specifications in DBMS_AWM
| Method | Description | Procedure |
|---|---|---|
| Create/Delete | Create or delete a cube load specification. | CREATE_AWCUBELOAD_SPEC Procedure
DELETE_AWCUBELOAD_SPEC Procedure |
| Reset information | Change various components of a cube load specification. | SET_AWCUBELOAD_SPEC_CUBE Procedure
SET_AWCUBELOAD_SPEC_LOADTYPE Procedure SET_AWCUBELOAD_SPEC_NAME Procedure SET_AWCUBELOAD_SPEC_PARAMETER Procedure |
| Add/Delete filter | Add or remove a filter from a cube load specification. | ADD_AWCUBELOAD_SPEC_FILTER Procedure
DELETE_AWCUBELOAD_SPEC_FILTER Procedure |
| Add/Delete composite specification | Add or remove a composite specification from a cube load specification. | ADD_AWCUBELOAD_SPEC_COMP Procedure
DELETE_AWCUBELOAD_SPEC_COMP Procedure |
The methods you can perform on an aggregation specification are described in Table 1-6.
Table 1-6 Methods on Aggregation Specifications in DBMS_AWM
| Method | Description | Procedure |
|---|---|---|
| Create/Delete | Create or delete an aggregation specification. | CREATE_AWCUBEAGG_SPEC Procedure
DELETE_AWCUBEAGG_SPEC_MEASURE Procedure |
| Set operator | Set the aggregation operator for a dimension. | SET_AWCUBEAGG_SPEC_AGGOP Procedure |
| Add/Delete levels | Add or remove levels from an aggregation specification. | ADD_AWCUBEAGG_SPEC_LEVEL Procedure
DELETE_AWCUBEAGG_SPEC_LEVEL Procedure |
| Add/Delete measures | Add or remove measures from an aggregation specification. | ADD_AWCUBEAGG_SPEC_MEASURE Procedure
DELETE_AWCUBEAGG_SPEC_MEASURE Procedure |
The methods you can perform on a composite specification are described in Table 1-7.
Table 1-7 Methods on Composite Specifications in DBMS_AWM
| Method | Description | Procedure |
|---|---|---|
| Create/Delete | Create or delete a composite specification. | CREATE_AWCOMP_SPEC Procedure
|
| Reset information | Change the name of the composite specification or associate it with a different cube. | SET_AWCOMP_SPEC_CUBE Procedure
SET_AWCOMP_SPEC_NAME Procedure |
| Add/Delete members | Add or remove members from the specification. Members can be dimensions or composites. | ADD_AWCOMP_SPEC_MEMBER Procedure
DELETE_AWCOMP_SPEC_MEMBER Procedure |
| Reset member information | Change information about members of the specification. | SET_AWCOMP_SPEC_MEMBER_NAME Procedure
SET_AWCOMP_SPEC_MEMBER_POS Procedure SET_AWCOMP_SPEC_MEMBER_SEG Procedure |
| Add composite members | Add members to a composite in the specification. | ADD_AWCOMP_SPEC_COMP_MEMBER Procedure |
Once you have defined a dimension in the OLAP Catalog for your source dimension table, you can create the dimension in the analytic workspace.
Only one workspace dimension may be created from a given dimension in the OLAP Catalog. For example, if you have used the OLAP Catalog PRODUCT dimension as the source for the PROD_AW dimension in an analytic workspace, you cannot create another dimension PROD_AW2 from the same source dimension in the same workspace.
|
Note: CREATE_AWDIMENSION opens the analytic workspace with read/write access. It updates the workspace, but it does not execute a SQL COMMIT.
The analytic workspace must already exist before you call |
Example 1-1 shows the procedure calls for defining and populating workspace objects for the XADEMO.CHANNEL dimension. The load specification includes a filter condition that causes only the row for 'DIRECT' to be loaded.
Example 1-1 Creating the CHANNEL Dimension in an Analytic Workspace
--- SET UP set serveroutput on execute cwm2_olap_manager.set_echo_on; execute cwm2_olap_manager.begin_log ('/users/myxademo/myscripts' , 'channel.log'); --- CREATE THE ANALYTIC WORKSPACE execute dbms_aw.execute ('aw create ''myaw'''); --- CREATE AND POPULATE THE DIMENSION execute dbms_awm.create_awdimension ('XADEMO', 'CHANNEL', 'MYSCHEMA','MYAW', 'AW_CHAN'); execute dbms_awm.create_awdimload_spec ('CHAN_LOAD', 'XADEMO', 'CHANNEL', 'FULL_LOAD'); execute dbms_awm.add_awdimload_spec_filter ('CHAN_LOAD', 'XADEMO', 'CHANNEL', 'XADEMO', 'XADEMO_CHANNEL', '''CHAN_STD_CHANNEL'' = ''DIRECT''' ); execute dbms_awm.refresh_awdimension ('MYSCHEMA', 'MYAW', 'AW_CHAN', 'CHAN_LOAD'); --- COMMIT AND WRAP UP commit; execute cwm2_olap_manager.set_echo_off; execute cwm2_olap_manager.end_log
When you query the Active Catalog view ALL_OLAP2_AW_DIMENSIONS, you will see the following row.
AW_OWNER AW_NAME AW_LOGICAL_NAME SOURCE_OWNER SOURCE_NAME ---------- ---------- ------------------- ------------- ----------- MYSCHEMA MYAW AW_CHAN XADEMO CHANNEL
CREATE_AWDIMENSION ensures that the generic standard form objects that support dimensions exist in the workspace, and it registers the specified dimension in the workspace. However, the metadata that defines the logical structure of this particular dimension is not instantiated in the workspace until you call REFRESH_AWDIMENSION.
For example, if you have just created a dimension AW_PROD in a workspace MYAW in XADEMO from a source dimension XADEMO.PRODUCT, you can query the Active Catalog to check the workspace.
SQL>select * from ALL_OLAP2_AW_DIMENSIONS WHERE AW_LOGICAL_NAME in 'AW_PROD'; AW_OWNER AW_NAME AW_LOGICAL_NAME SOURCE_OWNER SOURCE_NAME ---------- --------------- ------------------------- --------------- ----------- XADEMO MYAW AW_PROD XADEMO PRODUCT
The following query shows that there are no levels associated with the dimension. The levels, hierarchies, attributes, and descriptions will be instantiated when the dimension is refreshed.
SQL>select * from ALL_OLAP2_AW_DIM_LEVELS where AW_LOGICAL_NAME in 'AW_PROD'; no rows selected
You must refresh a dimension whenever changes occur in the source dimension tables. These changes could be additions or deletions of dimension members, for example removing a product from a Product dimension, or they could be changes to the dimension's metadata, such as adding a Day level to a time dimension.
When you refresh a dimension, you must also refresh each cube in which it participates.
When you refresh a dimension because of structural metadata changes to its hierarchies, you must re-enable the dimension and its related cubes. When you refresh a dimension because of data changes, you do not need to re-enable.
When you refresh a dimension whose cube has associated stored summaries in the analytic workspace (the result of an aggregation specification), you must also reaggregate the cube.
Once you have defined a cube in the OLAP Catalog for your star schema, you can create the cube in the analytic workspace.
You must call CREATE_AWDIMENSION to create each of the cube's dimensions before calling CREATE_AWCUBE to create the cube. To populate the cube, you must call REFRESH_AWDIMENSION to populate each of the cube's dimensions before calling REFRESH_AWCUBE to refresh the cube's measures. On subsequent refreshes, you only need to refresh the dimensions that have changed.
Within an analytic workspace, dimensions can be shared by more than one cube. When creating a new workspace cube, you will only call CREATE_AWDIMENSION for OLAP Catalog dimensions that have not been used as the source for dimensions of cubes that already exist in the workspace.
|
Note: CREATE_AWCUBE opens the analytic workspace with read/write access. It updates the workspace, but it does not execute a SQL COMMIT.
The analytic workspace must already exist before you call |
Example 1-2 shows the procedure calls for creating and populating the XADEMO.ANALYTIC_CUBE cube in an analytic workspace.
Example 1-2 Creating the ANALYTIC_CUBE Cube in an Analytic Workspace
--- SET UP set serveroutput on execute cwm2_olap_manager.set_echo_on; execute cwm2_olap_manager.begin_log ('/users/myxademo/myscripts' , 'anacube.log'); --- CREATE THE ANALYTIC WORKSPACE execute dbms_aw.execute ('aw create ''myaw'''); --- CREATE AND REFRESH THE DIMENSIONS execute dbms_awm.create_awdimension ('XADEMO','CHANNEL','MYSCHEMA', 'MYAW', 'AW_CHAN'); execute dbms_awm.create_awdimension ('XADEMO','GEOGRAPHY','MYSCHEMA','MYAW', 'AW_GEOG'); execute dbms_awm.create_awdimension ('XADEMO','PRODUCT','MYSCHEMA', 'MYAW', 'AW_PROD'); execute dbms_awm.create_awdimension ('XADEMO','TIME','MYSCHEMA', 'MYAW', 'AW_TIME'); execute dbms_awm.refresh_awdimension ('MYSCHEMA', 'MYAW', 'AW_CHAN'); execute dbms_awm.refresh_awdimension ('MYSCHEMA', 'MYAW', 'AW_PROD'); execute dbms_awm.refresh_awdimension ('MYSCHEMA', 'MYAW', 'AW_GEOG'); execute dbms_awm.refresh_awdimension ('MYSCHEMA', 'MYAW', 'AW_TIME'); --- CREATE AND REFRESH THE CUBE execute dbms_awm.create_awcube ('XADEMO', 'ANALYTIC_CUBE','MYSCHEMA', 'MYAW','AW_ANACUBE'); execute dbms_awm.create_awcubeload_spec ('AC_CUBELOADSPEC', 'XADEMO', 'ANALYTIC_CUBE', 'LOAD_DATA'); execute dbms_awm.refresh_awcube ('MYSCHEMA', 'MYAW', 'AW_ANACUBE', 'AC_CUBELOADSPEC'); --- COMMIT AND WRAP UP commit; execute cwm2_olap_manager.set_echo_off; execute cwm2_olap_manager.end_log
When you query the Active Catalog view ALL_OLAP2_AW_CUBES , you will see the following row.
AW_OWNER AW_NAME AW_LOGICAL_NAME SOURCE_OWNER SOURCE_NAME ---------- ---------- ------------------- ------------- ------------- MYSCHEMA MYAW AW_ANACUBE XADEMO ANALYTIC_CUBE
The measures in the source fact table may have numeric, text, or date data types. When REFRESH_AWCUBE loads the data into a workspace cube, it converts the RDBMS data types to types that are native to analytic workspaces. The data type conversion is described in Table 1-8.
If a source measure has a data type not described in Table 1-8, the measure is ignored by REFRESH_AWCUBE and none of its data or metadata is loaded into the analytic workspace.
CREATE_AWCUBE ensures that the generic standard form objects that support cubes exist in the workspace, and it registers the specified cube in the workspace. However, the metadata that defines the logical structure of this particular cube is not instantiated in the workspace until you call REFRESH_AWCUBE.
For example, if you have just created a cube AW_ANACUBE in a workspace MYAW in MYSCHEMA from the source cube XADEMO.ANALYTIC_CUBE, you can query the Active Catalog to check the workspace.
SQL>select * from ALL_OLAP2_AW_CUBES where AW_LOGICAL_NAME in 'AW_ANACUBE'; AW_OWNER AW_NAME AW_LOGICAL_NAME SOURCE_OWNER SOURCE_NAME ---------- ----------- --------------------- --------------- -------------- MYSCHEMA MYAW AW_ANACUBE XADEMO ANALYTIC_CUBE
The following query shows that there are no measures associated with the cube. The measures, dimensions, and descriptions will be instantiated when the cube is refreshed.
SQL>select * from ALL_OLAP2_AW_CUBE_MEASURES where AW_CUBE_NAME in 'AW_ANACUBE'; no rows selected
You must refresh a cube whenever changes occur in the source fact table. These changes could be additions or deletions of data, for example updating sales figures, or they could be changes to the cube's metadata, such as adding a measure or renaming a description.
When you refresh a cube, you must first refresh any of its dimensions that have changed.
When you refresh a cube because of structural metadata changes to its dimension hierarchies, you must re-enable the cube and its related dimensions. When you refresh a cube because of data changes, you do not need to re-enable.
Everytime you refresh a cube that has an associated aggregation specification, you must reaggregate the cube.
If you make changes to the composite specification associated with a cube, you must drop the cube and re-create it in the analytic workspace. You cannot refresh a cube with a modified composite specification.
A composite is an object that is used to store sparse data compactly in a variable in an analytic workspace. A composite consists of a list of dimension-value combinations in which one value is taken from each of the dimensions on which the composite is based. Only the combinations for which data exists are included in the composite.
Composites are maintained automatically by the OLAP engine. With composites, you can keep your analytic workspace size to a minimum and promote good performance. For more information on composites, see the Oracle OLAP DML Reference. For information on managing sparsity and optimizing performance in your analytic workspaces, see the Oracle OLAP Application Developer's Guide
For example, you might have some products in your analytic cube that are not sold in all regions. The data cells for those combinations of PRODUCT and GEOGRAPHY would be empty. In this case, you might choose to define PRODUCT and GEOGRAPHY as a composite. The OLAP DML syntax for defining the dimensionality of the Costs measure in this cube could be as follows.
DEFINE prod_geog COMPOSITE <product geography> DEFINE costs VARIABLE INTEGER <time channel prod_geog<product geography>>
To specify that a cube's data be loaded into an analytic workspace using this definition of the cube's dimensionality, you would define a composite specification for the cube. The composite specification would define the following expression.
<time channel prod_geog<product geography>>
Each member of a composite specification has a name, a type, and a position. Table 1-9 identifies this information for the preceding example.
Table 1-9 Composite Spec Members for XADEMO.ANALYTIC_CUBE
| Member | Type | Position |
|---|---|---|
TIME |
dimension | 1 |
CHANNEL |
dimension | 2 |
PROD_GEOG |
composite | 3 |
PRODUCT |
dimension | 4 |
GEOGRAPHY |
dimension | 5 |
Dimension order determines how the cube's data is stored and accessed in the analytic workspace. The first dimension in the dimension's definition is the fastest-varying and the last is the slowest-varying.
By default, REFRESH_AWCUBE defines a workspace cube's dimensionality with Time as the fastest varying dimension followed by a composite of all the other dimensions. The dimensions in the composite are ordered according to their size. The dimension with the most members is first and the dimension with the least members is last. For example, the default dimensionality of the ANALYTIC_CUBE in an analytic workspace would be as follows.
<time comp_name<geography, product, channel>>
You can override the default dimensionality by specifying a composite specification and including it in the cube load specification.
For information on ordering dimensions and specifying segment size for dimension storage, see the Oracle OLAP Application Developer's Guide.
The statements in Example 1-3 create a composite specification called comp1 for the ANALYTIC_CUBE.
Example 1-3 Defining a Cube's Dimensionality in an Analytic Workspace
exec dbms_awm.create_awcomp_spec
('comp1', 'xademo', 'analytic_cube');
exec dbms_awm.add_awcomp_spec_member
('comp1', 'xademo', 'analytic_cube', 'comp1_time', 'dimension',
'xademo', 'time');
exec dbms_awm.add_awcomp_spec_member
('comp1', 'xademo', 'analytic_cube', 'comp1_channel', 'dimension',
'xademo', 'channel');
exec dbms_awm.add_awcomp_spec_member
('comp1', 'xademo', 'analytic_cube', 'comp1_prod_geog', 'composite');
exec dbms_awm.add_awcomp_spec_comp_member
('comp1', 'xademo', 'analytic_cube', 'comp1_prod_geog',
'comp1_product' ,'dimension', 'xademo', 'product');
exec dbms_awm.add_awcomp_spec_comp_member
('comp1', 'xademo', 'analytic_cube', 'comp1_prod_geog',
'comp1_geography' ,'dimension', 'xademo', 'geography');
exec dbms_awm.add_awcubeload_spec_comp
('my_cube_load', 'xademo', 'analytic_cube', 'comp1');
You can modify a composite specification by applying it to a different cube or giving it a different name. You can rename, move, and change the segment size of a primary member of a composite specification. However, you cannot rename, move, or change the segment size of a member of a composite. To edit the composite itself, you must delete it and define a new composite.
Suppose that you wanted to make Channel, instead of Time, the fastest varying dimension of the cube in the analytic workspace. You could reposition Channel in the composite specification as follows.
exec dbms_awm.set_awcomp_spec_member_pos
('comp1', 'xademo', 'analytic_cube', 'comp1_channel', 1);
The DBMS_AWM package allows you to store aggregate data for level combinations of measures in a workspace cube.
Stored aggregates in an analytic workspace are similar to materialized views for relational data. However, a workspace cube is always presented as fully solved with embedded totals when enabled for SQL access by an application. If you do not preaggregate any of the workspace data, all the aggregate data is still available but it must be calculated on the fly.
Preaggregating some or all of your workspace data will improve query performance in most circumstances. For information on choosing an aggregation strategy, refer to the Oracle OLAP Application Developer's Guide
|
Note: The aggregation process (AGGREGATE_AWCUBE) opens the analytic workspace with read/write access. It updates the workspace, but it does not execute a SQL COMMIT. |
The cube refresh process stores detail data in the workspace and sets up the structures to support dynamic aggregation. If you want to preaggregate some or all of your data, you must create an aggregation specification and run a separate aggregation procedure for the workspace cube.
Example 1-4 shows sample procedure calls for preaggregating the Costs and Quota measures of the analytic workspace cube AC2, which was created from XADEMO.ANALYTIC_CUBE.
The quarter totals (level 'L2' of TIME) for product groups (level 'L3' of PRODUCT), product divisions (level 'L2' of PRODUCT), and all channels (level 'STANDARD-2' of CHANNEL) are calculated and stored in the analytic workspace.
Example 1-4 Preaggregating Costs and Quota in an Analytic Workspace
execute dbms_awm.create_awcubeagg_spec
('AGG1', 'MYSCHEMA', 'MYAW', 'AC2');
execute dbms_awm.add_awcubeagg_spec_level
('AGG1', 'MYSCHEMA', 'MYAW', 'AC2', 'PRODUCT', 'L3');
execute dbms_awm.add_awcubeagg_spec_level
('AGG1', 'MYSCHEMA', 'MYAW', 'AC2', 'PRODUCT', 'L2');
execute dbms_awm.add_awcubeagg_spec_level
('AGG1', 'MYSCHEMA', 'MYAW', 'AC2', 'CHANNEL', 'STANDARD_2');
execute dbms_awm.add_awcubeagg_spec_level
('AGG1', 'MYSCHEMA', 'MYAW', 'AC2', 'TIME', 'L2');
execute dbms_awm.add_awcubeagg_spec_measure
('AGG1', 'XADEMOAW', 'UK', 'AC2', 'XXF_COSTS');
execute dbms_awm.add_awcubeagg_spec_measure
('AGG1', 'XADEMOAW', 'UK', 'AC2', 'XXF_QUOTA');
execute dbms_awm.aggregate_awcube('MYSCHEMA', 'MYAW', 'AC2', 'AGG1');
The following statements show the measures and the PRODUCT levels in the aggregation plan in the analytic workspace.
execute dbms_aw.execute ('aw attach MYSCHEMA.MYAW ro');
execute dbms_aw.execute ('fulldsc agg1');
DEFINE AGG1 DIMENSION TEXT LD List of Measures which use this AggPlan PROPERTY 'AW$CLASS' - 'IMPLEMENTATION' PROPERTY 'AW$CREATEDBY' - 'AW$CREATE' PROPERTY 'AW$LASTMODIFIED' - '.* PROPERTY 'AW$LOGICAL_NAME' - 'AGG1' PROPERTY 'AW$PARENT_NAME' - 'AC2' PROPERTY 'AW$ROLE' - 'AGGDEF' PROPERTY 'AW$STATE' - 'ACTIVE'
execute dbms_aw.execute('rpr agg1')
AGG1 -------------- XXF.COSTS XXF.QUOTA
execute dbms_aw.execute('fulldsc agg1_product');
DEFINE AGG1_PRODUCT VALUESET PRODUCT_LEVELLIST LD List of Levels for this AggPlan PROPERTY 'AW$AGGOPERATOR' - 'SUM' PROPERTY 'AW$CLASS' - 'IMPLEMENTATION' PROPERTY 'AW$CREATEDBY' - 'AW$CREATE' PROPERTY 'AW$LASTMODIFIED' - '.* PROPERTY 'AW$PARENT_CUBE' - 'AC2' PROPERTY 'AW$PARENT_DIM' - 'PRODUCT' PROPERTY 'AW$PARENT_NAME' - 'AGG1' PROPERTY 'AW$ROLE' - 'AGGDEF_LEVELS' PROPERTY 'AW$STATE' - 'ACTIVE'
execute dbms_aw.execute('shw values(agg1_product)');
L3 L2
An aggregation method specifies the operation used to summarize the data by level. The default aggregation method is addition. For example, sales data is typically aggregated over time by adding the values for each time period.
The OLAP Catalog supports a set of aggregation methods, which may be included to the definition of a cube. These aggregation methods are listed in Table 1-10.
When a workspace cube is refreshed, the aggregation operators specified in the OLAP Catalog are converted to the corresponding operators supported by the OLAP DML RELATION command. These operators are incorporated in the aggregation map that controls dynamic aggregation for the cube.
To specify a different operator for your stored aggregates, you can use the SET_AWCUBEAGG_SPEC_AGGOP procedure. This procedure enables you to specify any of the operators supported by the OLAP DML RELATION command to preaggregate your data.
|
Note: TheDBMS_AWM package currently does not support weighted aggregation operators. For example, if the OLAP Catalog specifies a weighted sum or weighted average for aggregation along one of the cube's dimensions, it is converted to the scalar equivalent (sum or average) when the cube is refreshed in the analytic workspace. Weighted operators specified by SET_AWCUBEAGG_SPEC_AGGOP are similarly converted. |
The OLAP Catalog and corresponding OLAP DML aggregation operators are described in Table 1-10.
Table 1-10 Aggregation Operators
| OLAP Catalog | OLAP DML | DML Abbvr | Description |
|---|---|---|---|
SUM |
SUM |
SU |
Sum. Adds data values (default) |
SCALED SUM |
SSUM |
SS |
Converted to Sum. |
WEIGHTED SUM |
WSUM |
WS |
Converted to Sum. |
AVERAGE |
AVERAGE |
AV |
Average. Adds data values, then divides the sum by the number of data values that were added together. |
HIERARCHICAL AVERAGE |
HAVERAGE |
HA |
Hierarchical Average. Adds data values, then divides the sum by the number of the children in the dimension hierarchy. |
WEIGHTED AVERAGE |
WAVERAGE |
WA |
Converted to Average. |
HWAVERAGE |
HW |
Converted to Hierarchical Average. | |
MAX |
MAX |
MA |
Maximum. The largest data value among the children of any parent data value. |
MIN |
MIN |
MI |
Minimum. The smallest data value among the children of any parent data value. |
FIRST |
FIRST |
FI |
First. The first non-NA data value. |
HFIRST |
HF |
Hierarchical First. The first data value that is specified by the hierarchy, even if that value is NA. | |
LAST |
LAST |
LA |
Last. The last non-NA data value. |
HLAST |
HL |
Hierarchical Last. The last data value that is specified by the hierarchy, even if that value is NA. | |
AND |
AND |
AN |
(Boolean variables only) If any child data value is FALSE, then the data value of its parent is FALSE. A parent is TRUE only when all of its children are TRUE. |
OR |
OR |
OR |
(Default for Boolean variables) If any child data value is TRUE, then the data value of its parent is TRUE. A parent is FALSE only when all of its children are FALSE. |
COUNT |
NO |
Converted to NOAGG. | |
NOAGG |
NO |
Do not aggregate any data for this dimension. |
Once you have created an analytic workspace cube and refreshed and aggregated its data, you can generate views that will allow applications to access that data using standard SQL. The DBMS_AWM procedures that generate the views are known as the OLAP API Enabler procedures. They generate views and OLAP Catalog metadata in the format required by the OLAP API and BI Beans, as follows.
An embedded total dimension view for each dimension hierarchy.
An embedded total fact view for each combination of dimension hierarchies.
If your analytic workspace will support different applications, then you need to generate views that conform to their requirements. You can use the OLAP_TABLE function, described in Chapter 26, to generate views in a variety of different formats.
To enable a workspace cube, you can either generate the scripts and run them yourself or you can use a one-step procedure to create and run the scripts automatically.
Use the following steps to enable a workspace cube for access by the OLAP API and BI Beans:
Determine how your system is configured to write to files. The enabler procedures accept either a directory object or a directory path. If you specify a directory object, make sure that your user ID has been granted the appropriate access rights to it. If you specify a path, make sure that it is the value of the UTL_FILE_DIR initialization parameter for the instance.
Run the REFRESH_AWCUBE and REFRESH_AWDIMENSION procedures to refresh the cube. These procedures create metadata in the analytic workspace to track the generations of enablement view names.
NOTE: If you use some other process to refresh the cube (for example, the OLAP Analytic Workspace Java API), this metadata is not created. If you want to specify your own names for the enablement views (as described in the following step), you must create this metadata by calling the REFRESH_AWDIMENSION_VIEW_NAME and REFRESH_AWCUBE_VIEW_NAME procedures.
The enablement process automatically provides system-generated names for the enablement views. To provide your own view names, call the SET_AWDIMENSION_VIEW_NAME and SET_AWCUBE_VIEW_NAME procedures.
Call the CREATE_AWDIMENSION_ACCESS procedure for each of the cube's dimensions. Set the access_type parameter to OLAP. Each procedure call will create an enablement script in a directory that you specify. The script will contain statements that create the dimension views and an OLAP Catalog dimension that maps to the views.
Call the CREATE_AWCUBE_ACCESS procedure. Set the access_type parameter to OLAP. This procedure call will create an enablement script in a directory that you specify. The script will contain statements that create the fact views and an OLAP Catalog cube that maps to the views.
Run the enablement scripts. The scripts will delete any previous generation of views and metadata before creating new views and metadata.
To create and run the enablement scripts automatically, use the following steps:
Refresh the cube and its dimensions in the analytic workspace, as described in "Procedure: Generate and Run the Enablement Scripts".
Call CREATE_AWDIMENSION_ACCESS_FULL for each of the cube's dimensions. This procedure creates the enablement scripts in temporary memory and runs the scripts to create the dimension views and OLAP Catalog metadata. The scripts delete any previous views and OLAP Catalog metadata before creating new views and metadata.
Call the procedure CREATE_AWCUBE_ACCESS_FULL to create the fact views for the cube. This procedure accomplishes the same basic steps as the corresponding procedure for dimensions.
The OLAP API enabler procedures are listed in Table 1-11.
Table 1-11 The OLAP API Enabler Procedures
| Procedure | Description |
|---|---|
| CREATE_AWCUBE_ACCESS Procedure |
Creates a script that enables access to a cube in an analytic workspace. |
| CREATE_AWCUBE_ACCESS_FULL Procedure |
Enables access to a cube in an analytic workspace. |
| CREATE_AWDIMENSION_ACCESS Procedure |
Creates a script that enables access to a dimension in an analytic workspace. |
| CREATE_AWDIMENSION_ACCESS_FULL Procedure |
Enables access to a dimension in an analytic workspace. |
| DELETE_AWCUBE_ACCESS Procedure |
Creates a script that deletes the enablement views and metadata for a cube in an analytic workspace. |
| DELETE_AWCUBE_ACCESS_ALL Procedure |
Deletes the enablement views and metadata for a cube in an analytic workspace. |
| DELETE_AWDIMENSION_ACCESS Procedure |
Creates a script that deletes the enablement views and metadata for a dimension in an analytic workspace. |
| DELETE_AWDIMENSION_ACCESS_ALL Procedure |
Deletes the enablement views and metadata for a dimension in an analytic workspace. |
| REFRESH_AWCUBE_VIEW_NAME Procedure |
Creates metadata in the analytic workspace to support user-defined view names. (Not for use with REFRESH_AWCUBE) |
| REFRESH_AWDIMENSION_VIEW_NAME Procedure |
Creates metadata in the analytic workspace to support user-defined view names. (Not for use with REFRESH_AWDIMENSION) |
| SET_AWCUBE_VIEW_NAME Procedure |
Replaces the system-generated names for the views of an analytic workspace cube. |
| SET_AWDIMENSION_VIEW_NAME Procedure |
Replaces the system-generated names for the views of an analytic workspace dimension. |
The REFRESH_AWDIMENSION and REFRESH_AWCUBE procedures create metadata in the analytic workspace related to enablement. This metadata includes a set of default names for the views that will be created by the enablement scripts.
Whenever you refresh, new view names are generated. If you have previously created your own names (SET_AWDIMENSION_VIEW_NAME and SET_AWCUBE_VIEW_NAME), the refresh process uses them as the basis for the new names.
If you refresh and there has been no change to the source cube's metadata, you do not need to re-create the enablement scripts.
The enablement procedures automatically delete any previous generation of views and OLAP Catalog metadata. However, in some circumstances, you might want to drop the views and metadata without re-creating them. In particular, if you drop the workspace cube or the workspace itself, you will need to clean up the orphaned views and metadata.
In this case, you can run the DELETE_AWDIMENSION_ACCESS and DELETE_AWCUBE_ACCESS procedures to generate scripts that will drop the views and metadata that enable relational access to the cube. These scripts do not delete any enablement metadata that is stored within the analytic workspace.
To delete all the enablement views and metadata for a dimension or a cube, use DELETE_AWCUBE_ACCESS_ALL and DELETE_AWDIMENSION_ACCESS_ALL.
REFRESH_AWDIMENSION constructs default names for the views. You can override the default names by calling SET_AWDIMENSION_VIEW_NAME.
The default view name is: aaaa_bbbbb_ccccc_ddddd#view, where:
aaaa is the first four characters of the analytic workspace owner
bbbbb is the first five characters of the analytic workspace name
ccccc is the first five characters of the analytic workspace dimension name
ddddd is the first five characters of the analytic workspace hierarchy name
# is an automatically-generated sequence number between 1 and 9,999 to ensure uniqueness.
Default names are also generated for the abstract objects (ADTs) populated by OLAP_TABLE. For example, the workspace dimension AWGEOG, in a workspace called AWTEST in the XADEMO schema could have the following system-generated names for the STANDARD hierarchy.
| Default Name | Description |
|---|---|
XADE_AWTES_AWGE0_STAND34VIEW |
Name of the relational view |
XADE_AWTES_AWGEOG34OBJ |
Name of the abstract object that defines a row in the abstract table of objects populated by OLAP_TABLE |
XADE_AWTES_AWGEOG34TBL |
Name of the abstract table type populated by OLAP_TABLE |
The REFRESH_AWCUBE procedure constructs default names for the views. You can override the default names by calling SET_AWCUBE_VIEW_NAME.
The default view name is: aaaa_bbbbb_cccccccc#view, where:
aaaa is the first four characters of the analytic workspace owner
bbbbb is the first five characters of the analytic workspace name
cccccccc is the first eight characters of the analytic workspace cube name
# is an automatically-generated sequence number between 1 and 9,999 to ensure uniqueness.
Default names are also generated for the abstract objects (ADTs) populated by OLAP_TABLE. For example, the workspace cube AWCUBE, in a workspace called AWTEST in the XADEMO schema could have the following system-generated names.
| Default Name | Description |
|---|---|
XADE_AWTES_AWCUBE8VIEW |
Name of the relational fact view for the first hierarchy combination. |
XADE_AWTES_AWCUBE9VIEW |
Name of the relational fact view for the second hierarchy combination. |
XADE_AWTES_AWCUBE10VIEW |
Name of the relational fact view for the third hierarchy combination. |
XADE_AWTES_AWCUBE11VIEW |
Name of the relational fact view for the fourth hierarchy combination. |
XADE_AWTES_AWCUBE7OBJ |
Name of the abstract object that defines a row in the abstract table of objects populated by OLAP_TABLE |
XADE_AWTES_AWCUBE7TBL |
Name of the abstract table type populated by OLAP_TABLE |
The enablement process generates a separate view for each dimension hierarchy. For example, a workspace cube with the four dimensions shown in Table 1-12 would have six separate dimension views since two of the dimensions have two hierarchies.
Table 1-12 Sample Dimension Hierarchies
| Dimensions | Hierarchies | Number of Views |
|---|---|---|
geography |
standard
|
2 |
product |
standard |
1 |
channel |
standard |
1 |
time |
standard
|
2 |
The dimension views are level-based, and they include the full lineage of every level value in every row. This type of dimension table is considered solved, because the fact table related to this dimension includes embedded totals for all level combinations.
Each dimension view contains the columns described in Table 1-13.
Table 1-13 Dimension View Columns
| Column | Description |
|---|---|
| ET key | The embedded-total key column stores the value of the lowest populated level in the row. |
| Parent ET key | The parent embedded-total key column stores the parent of each ET key value. |
| GID | The grouping ID column identifies the hierarchy level associated with each row, as described in "Grouping ID Column". |
| Parent GID | The parent grouping ID column stores the parent of each GID value. |
| level columns | A column for each level of the dimension hierarchy. These columns provide the full ancestry of each dimension member within a single row. |
| level attribute columns | A column for each level attribute. |
For a standard geography hierarchy with levels for TOTAL_US, REGION, and STATE, the dimension view would contain columns like the ones that follow. Level attribute columns would also be included.
GID PARENT_GID ET KEY PARENT_ET_KEY TOTAL_US REGION STATE --- ---------- ------ ------------ -------- ------ ----- 0 1 MA Northeast USA Northeast MA 0 1 NY Northeast USA Northeast NY 0 1 GA Southeast USA Southeast GA 0 1 CA Southwest USA Southwest CA 0 1 AZ Southwest USA Southwest AZ 1 3 Northeast USA USA Northeast 1 3 Southeast USA USA Southeast 1 3 Southwest USA USA Southwest 3 NA USA NA USA
The GID identifies the hierarchy level associated with each row by assigning a zero to each non-null value and a one to each null value in the level columns. The resulting binary number is the value of the GID.
For example, a GID of 1 is assigned to a row with the following three levels.
TOTAL_US REGION STATE -------- ------ ----- USA Southwest 0 0 1
A GID of 3 is assigned to a row with the following five levels.
TOTAL_GEOG COUNTRY REGION STATE CITY ---------- ------- ------- ------ ------- World USA Northeast 0 0 0 1 1
The CREATE_AWCUBE_ACCESS procedure generates a separate view for each dimension/hierarchy combination. For example, an analytic workspace cube with the four dimensions shown in Table 1-12, would have four separate fact views, one for each hierarchy combination show in Table 1-14.
Table 1-14 Sample Dimension/Hierarchy Combinations
| Geography Dim | Product Dim | Channel Dim | Time Dim |
|---|---|---|---|
geography/standard |
product/standard |
channel/standard |
time/standard |
geography/standard |
product/standard |
channel/standard |
time/ytd |
geography/consolidated |
product/standard |
channel/standard |
time/standard |
geography/consolidated |
product/standard |
channel/standard |
time/ytd |
The fact views are fully solved. They contain embedded totals for all level combinations. Each view has columns for the cube's measures, and key columns that link the fact view with its associated dimension views.
Each fact view contains the columns described in Table 1-15.
Table 1-15 Fact View Columns
| Column | Description |
|---|---|
| ET key for each dimension/hierarchy | The ET key columns are foreign keys that map to the primary keys of the associated dimension tables, and are used to join the measure table with the dimension tables. |
| GID for each dimension/hierarchy | The GID column provides grouping IDs needed by the OLAP API for optimal response time. It is identical to the GID column of the associated dimension table. |
| measure columns | Columns for each of the cube's measures. |
R2C |
Information needed to dynamically calculate custom measures. See the ROWTOCELL keyword described in Table 26-3, "Components of the OLAP_TABLE Limit Map". |
CUST_MEAS_TEXTn |
100 sequentially numbered empty columns with a data type of VARCHAR2(1000).
These columns return predefined custom measures with a text data type. These custom measures result from the execution of a formula within the analytic workspace and are managed by procedures in the |
CUST_MEAS_NUMn |
100 sequentially numbered empty columns with a data type of NUMBER(38,6).
These columns return predefined custom measures with a numeric data type. These custom measures result from the execution of a formula within the analytic workspace and are managed by procedures in the |
The following example creates, refreshes, and enables a cube AWUSR.AWTEST based on the source cube XADEMO.ANALYTIC_CUBE.
Example 1-5 Create, Refresh, and Enable a Cube
-- SET UP
set serveroutput on size 1000000
execute cwm2_olap_manager.set_echo_on;
execute cwm2_olap_manager.begin_log ('/users/awuser/scripts' , 'awtest.log');
--- CREATE AW
execute dbms_aw.execute ('aw create ''AWTEST''');
-- CREATE DIMENSIONS
execute dbms_awm.create_awdimension
('XADEMO','CHANNEL', 'AWUSR', 'AWTEST', 'AWCHAN');
execute dbms_awm.create_awdimension
('XADEMO','GEOGRAPHY', 'AWUSR', 'AWTEST', 'AWGEOG');
execute dbms_awm.create_awdimension
('XADEMO','PRODUCT', 'AWUSR', 'AWTEST', 'AWPROD');
execute dbms_awm.create_awdimension
('XADEMO','TIME', 'AWUSR', 'AWTEST', 'AWTIME');
-- CREATE CUBE
execute dbms_awm.create_awcube
('XADEMO', 'ANALYTIC_CUBE','AWUSR', 'AWTEST','AWCUBE');
-- REFRESH DIMENSIONS
execute dbms_awm.refresh_awdimension ('AWUSR', 'AWTEST', 'AWCHAN');
execute dbms_awm.refresh_awdimension ('AWUSR', 'AWTEST', 'AWGEOG');
execute dbms_awm.refresh_awdimension ('AWUSR', 'AWTEST', 'AWPROD');
execute dbms_awm.refresh_awdimension ('AWUSR', 'AWTEST', 'AWTIME');
-- REFRESH CUBE
execute dbms_awm.refresh_awcube ('AWUSR', 'AWTEST', 'AWCUBE');
-- SET DIMENSION VIEW NAMES
exec dbms_awm.set_awdimension_view_name
('AWUSR', 'AWTEST', 'awprod', 'standard', 'prod_std_view');
exec dbms_awm.set_awdimension_view_name
('AWUSR', 'AWTEST', 'awchan', 'standard', 'chan_std_view');
exec dbms_awm.set_awdimension_view_name
('AWUSR', 'AWTEST', 'awgeog', 'consolidated', 'geog_csd_view');
exec dbms_awm.set_awdimension_view_name
('AWUSR', 'AWTEST', 'awgeog', 'standard', 'geog_std_view');
exec dbms_awm.set_awdimension_view_name
('AWUSR', 'AWTEST', 'awtime', 'standard', 'time_std_view');
exec dbms_awm.set_awdimension_view_name
('AWUSR', 'AWTEST', 'awtime', 'ytd', 'time_ytd_view');
-- SET CUBE VIEW NAMES
exec dbms_awm.set_awcube_view_name
('AWUSR', 'AWTEST', 'awcube', 1, 'AWCUBE_view1');
exec dbms_awm.set_awcube_view_name
('AWUSR', 'AWTEST', 'awcube', 2, 'AWCUBE_view2');
exec dbms_awm.set_awcube_view_name
('AWUSR', 'AWTEST', 'awcube', 3, 'AWCUBE_view3');
exec dbms_awm.set_awcube_view_name
('AWUSR', 'AWTEST', 'awcube', 4, 'AWCUBE_view4');
-- ENABLE DIMENSIONS
exec dbms_awm.create_AWdimension_access
('AWUSR', 'AWTEST', 'awprod', 'olap',
'/users/awuser/scripts', 'awprod_views.sql', 'w');
exec dbms_awm.create_AWdimension_access
('AWUSR', 'AWTEST', 'awchan', 'olap',
'/users/awuser/scripts', 'awchan_views.sql', 'w');
exec dbms_awm.create_AWdimension_access
('AWUSR', 'AWTEST', 'awgeog', 'olap',
'/users/awuser/scripts', 'awgeog_views.sql', 'w');
exec dbms_awm.create_AWdimension_access
('AWUSR', 'AWTEST', 'awtime', 'olap',
'/users/awuser/scripts', 'awtime_views.sql', 'w');
-- ENABLE CUBE
exec dbms_awm.create_AWcube_access
('AWUSR', 'AWTEST', 'awcube', 'olap',
'/users/awuser/scripts', 'awcube_views.sql', 'w');
-- COMMIT and WRAPUP
commit;
execute cwm2_olap_manager.end_log;
The following queries show the resulting workspace cube and dimensions with their source cubes and dimensions in the OLAP Catalog.
select * from all_olap2_aw_dimensions where AW_OWNER = 'AWUSER'; AW_OWNER AW_NAME AW_LOGICAL_NAME AW_PHYSICAL_OBJECT SOURCE_OWNER SOURCE_NAME --------- --------- --------------- ------------------ ------------- --------------- AWUSER AWTEST AWCHAN AWCHAN XADEMO CHANNEL AWUSER AWTEST AWGEOG AWGEOG XADEMO GEOGRAPHY AWUSER AWTEST AWPROD AWPROD XADEMO PRODUCT AWUSER AWTEST AWTIME AWTIME XADEMO TIME select * from all_olap2_aw_CUBEs where AW_OWNER = 'AWUSER'; AW_OWNER AW_NAME AW_LOGICAL_NAME AW_PHYSICAL_OBJECT SOURCE_OWNER SOURCE_NAME -------- -------- --------------- ------------------- ------------- --------------- AWUSER AWTEST AWCUBE AWCUBE XADEMO ANALYTIC_CUBE
The following queries show the system names and user names for the dimension enablement views.
select * from all_aw_dim_ENABLED_VIEWS where AW_OWNER = 'AWUSER'; AW_OWNER AW_NAME DIMENSION_ HIERARCHY_ SYSTEM_VIEWNAME USER_VIEWNAME -------- -------- ---------- ---------- ---------------------------- ---------------- AWUSER AWTEST AWCHAN STANDARD AWUS_AWTES_AWCHA_STAND144VIEW CHAN_STD_VIEW AWUSER AWTEST AWGEOG CONSOLIDATED AWUS_AWTES_AWGEO_CONSO145VIEW GEOG_CSD_VIEW AWUSER AWTEST AWGEOG STANDARD AWUS_AWTES_AWGEO_STAND146VIEW GEOG_STD_VIEW AWUSER AWTEST AWPROD STANDARD AWUS_AWTES_AWPRO_STAND147VIEW PROD_STD_VIEW AWUSER AWTEST AWTIME STANDARD AWUS_AWTES_AWTIM_STAND148VIEW TIME_STD_VIEW AWUSER AWTEST AWTIME YTD AWUS_AWTES_AWTIM_YTD149VIEW TIME_YTD_VIEW
The following queries show the system names and user names for the cube enablement views. Included are the hierarchy combination numbers, in this case 1 - 4, and the hierarchy strings, consisting of each unique combination of dimension hierarchies for this cube.
select * from all_aw_CUBE_ENABLED_VIEWS where AW_OWNER = 'AWUSER';
AW_OWN AW_NA CUBE_NAM HIER HIERCOMBO_STR SYSTEM_VIEWNAME USER_VIEWNAME
------ ------ -------- ---- ----------------------------------- ----------------------- -----------
AWUSER AWTEST AWCUBE 1 DIM:AWCHAN/HIER:STANDARD;DIM:AWGEOG AWUS_AWTES_AWCUBE151VIEW AWCUBE_VIEW1
/HIER:CONSOLIDATED;DIM:AWPROD/HIER:
STANDARD;DIM:AWTIME/HIER:STANDARD
AWUSER AWTEST AWCUBE 2 DIM:AWCHAN/HIER:STANDARD;DIM:AWGEOG AWUS_AWTES_AWCUBE152VIEW AWCUBE_VIEW2
/HIER:CONSOLIDATED;DIM:AWPROD/HIER:
STANDARD;DIM:AWTIME/HIER:YTD
AWUSER AWTEST AWCUBE 3 DIM:AWCHAN/HIER:STANDARD;DIM:AWGEOG AWUS_AWTES_AWCUBE153VIEW AWCUBE_VIEW3
/HIER:STANDARD;DIM:AWPROD/HIER:STAN
DARD;DIM:AWTIME/HIER:STANDARD
AWUSER AWTEST AWCUBE 4 DIM:AWCHAN/HIER:STANDARD;DIM:AWGEOG AWUS_AWTES_AWCUBE154VIEW AWCUBE_VIEW4
/HIER:STANDARD;DIM:AWPROD/HIER:STAN
DARD;DIM:AWTIME/HIER:YTD
The final step is to run the enablement scripts to generate the views and OLAP Catalog metadata for the analytic workspace cube. The scripts produced by this example are described as follows.
| Directory | Script | Description |
|---|---|---|
/users/awuser/scripts |
awprod_views.sql |
Creates an abstract object, a table of objects, and a view for the PRODUCT dimension. Also creates and validates an OLAP Catalog dimension AWUSER.AWPROD that maps to the view. |
/users/awuser/scripts |
awchan_views.sql |
Creates an abstract object, a table of objects, and a view for the CHANNEL dimension. Also creates and validates an OLAP Catalog dimension AWUSER.AWCHAN that maps to the view. |
/users/awuser/scripts |
awgeog_views.sql |
Creates an abstract object, a table of objects, and a view for each hierarchy of the GEOGRAPHY dimension. Also creates and validates an OLAP Catalog dimension AWUSER.AWGEOG that maps to the view. |
/users/awuser/scripts |
awtime_views.sql |
Creates an abstract object, a table of objects, and a view for each hierarchy of the TIME dimension. Also creates and validates an OLAP Catalog dimension AWUSER.AWTIME that maps to the view. |
/users/awuser/scripts |
awcube_views.sql |
Creates an abstract object, a table of objects, and a separate view for each hierarchy combination of the AWCUBE cube. Also creates and validates an OLAP Catalog cube AWUSER.AWCUBE that maps to the view. |
|
 Copyright © 2003 Oracle Corporation All Rights Reserved. |
|