| Oracle® OLAP Reference 10g Release 2 (10.2) Part Number B14350-01 |
|
|
View PDF |
| Oracle® OLAP Reference 10g Release 2 (10.2) Part Number B14350-01 |
|
|
View PDF |
If your analytic workspace must support ad hoc SQL queries and applications that do not use the OLAP API, you need to create views that present the workspace data in relational format.
Once you have created an analytic workspace cube and refreshed and aggregated its data, you can use the DBMS_AWM enablement procedures to create and maintain a set of views that can be queried with standard SQL. The DBMS_AWM enablement views include:
An embedded total dimension view for each dimension hierarchy.
An embedded total fact view for each combination of dimension hierarchies.
When you refresh a dimension or cube because of metadata change for its hierarchies, you must regenerate its enablement views. When you refresh a dimension or cube because of data changes, you can continue to use the pre-existing views.
If the enablement views do not provide the data in a useful format for your application, you can create your own views. Refer to Chapter 34, "OLAP_TABLE" for more information.
You can use DBMS_AWM enablement procedures to generate the enablement 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 create and run the enablement scripts for an analytic workspace:
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.
The enablement process automatically provides system-generated names for the enablement views. To provide your own view names, call REFRESH_AWDIMENSION_VIEW_NAME and REFRESH_AWCUBE_VIEW_NAME, then call SET_AWDIMENSION_VIEW_NAME and SET_AWCUBE_VIEW_NAME.
Call the CREATE_AWDIMENSION_ACCESS procedure for each of the cube's dimensions. 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, optionally, an OLAP Catalog dimension that maps to the views.
Call the CREATE_AWCUBE_ACCESS procedure. 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, optionally, 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 step 2 of "Procedure: Generate and Run the Enablement Scripts".
If you want to specify your own view names, follow step 3 of "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, optionally, the OLAP Catalog metadata. The scripts delete any previous views and 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 relational access to a cube in an analytic workspace. |
|
CREATE_AWCUBE_ACCESS_FULL Procedure |
Enables relational access to a cube in an analytic workspace. |
|
CREATE_AWDIMENSION_ACCESS Procedure |
Creates a script that enables relational access to a dimension in an analytic workspace. |
|
CREATE_AWDIMENSION_ACCESS_FULL Procedure |
Enables relational 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 for a cube. |
|
REFRESH_AWDIMENSION_VIEW_NAME Procedure |
Creates metadata in the analytic workspace to support user-defined view names for a dimension. |
|
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 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.
The CREATE_AWDIMENSION_ACCESS and CREATE_AWDIMENSION_ACCESS_FULL procedures create metadata in the analytic workspace related to enablement. This metadata includes a set of default names for the enablement views.
If you want to specify your own view names, you must refresh this metadata by calling REFRESH_AWDIMENSION_VIEW_NAME. Then call SET_AWDIMENSION_VIEW_NAME to specify the names of the views.
Whenever you re-create the views, new view names are generated. If you have previously created your own names, the refresh process uses them as the basis for the new names.
The default view name for a dimension 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 CREATE_AWCUBE_ACCESS and CREATE_AWCUBE_ACCESS_FULL procedures create metadata in the analytic workspace related to enablement. This metadata includes a set of default names for the enablement views.
If you want to specify your own view names, you must refresh this metadata by calling REFRESH_AWCUBE_VIEW_NAME. Then call SET_AWCUBE_VIEW_NAME to specify the names of the views.
Whenever you re-create the views, new view names are generated. If you have previously created your own names, the refresh process uses them as the basis for the new names.
The default view name for a cube 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 |
|---|---|---|
|
|
|
2 |
|
|
|
1 |
|
|
|
1 |
|
|
|
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 |
|---|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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. |
|
|
A column that stores information used by the single-row functions. See Chapter 30, "OLAP_EXPRESSION" and "Limit Map: ROW2CELL Clause". |
|
|
100 sequentially numbered empty columns with a data type of |
|
|
100 sequentially numbered empty columns with a data type of |
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;
create or replace directory myscripts as '/users/myxademo/myscripts';
execute cwm2_olap_manager.begin_log ('MYSCRIPTS' , '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.refresh_awdimension_view_name
('AWUSR', 'AWTEST', 'awprod');
exec dbms_awm.refresh_awdimension_view_name
('AWUSR', 'AWTEST', 'awchan');
exec dbms_awm.refresh_awdimension_view_name
('AWUSR', 'AWTEST', 'awgeog');
exec dbms_awm.refresh_awdimension_view_name
('AWUSR', 'AWTEST', 'awtime');
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.refresh_awcube_view_name
('AWUSR', 'AWTEST', 'awcube');
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',
'MYSCRIPTS', 'awprod_views.sql', 'w');
exec dbms_awm.create_AWdimension_access
('AWUSR', 'AWTEST', 'awchan', 'olap',
'MYSCRIPTS', 'awchan_views.sql', 'w');
exec dbms_awm.create_AWdimension_access
('AWUSR', 'AWTEST', 'awgeog', 'olap',
'MYSCRIPTS', 'awgeog_views.sql', 'w');
exec dbms_awm.create_AWdimension_access
('AWUSR', 'AWTEST', 'awtime', 'olap',
'MYSCRIPTS', 'awtime_views.sql', 'w');
-- ENABLE CUBE
exec dbms_awm.create_AWcube_access
('AWUSR', 'AWTEST', 'awcube', 'olap',
'MYSCRIPTS', '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 query shows 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 query shows 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 for the analytic workspace cube. The scripts produced by this example are described as follows.
