Example: Query an Analytic Workspace Cube

Example 3-1 Query the Active Catalog for Information about a Workspace Cube

The following statements create the dimensions in the analytic workspace XADEMO.MY_AW.

execute dbms_awm.create_awdimension 
           ('XADEMO','CHANNEL','XADEMO', 'MY_AW', 'AW_CHAN');
execute dbms_awm.create_awdimension 
          ('XADEMO','PRODUCT','XADEMO', 'MY_AW', 'AW_PROD');
execute dbms_awm.create_awdimension 
          ('XADEMO','GEOGRAPHY','XADEMO', 'MY_AW', 'AW_GEOG');
execute dbms_awm.create_awdimension 
          ('XADEMO','TIME','XADEMO', 'MY_AW', 'AW_TIME');

You can view the logical dimensions in the analytic workspace with the following query.

SQL>select * from ALL_OLAP2_AW_DIMENSIONS;

AW_OWNER  AW_NAME  AW_LOGICAL_NAME  AW_PHYSICAL_OBJECT SOURCE_OWNER  SOURCE_NAME
-------- --------  ---------------  ------------------ -----------   ----------
XADEMO     MY_AW      AW_CHAN       AW_CHAN            XADEMO        CHANNEL
XADEMO     MY_AW      AW_PROD       AW_PROD            XADEMO        PRODUCT
XADEMO     MY_AW      AW_GEOG       AW_GEOG            XADEMO        GEOGRAPHY
XADEMO     MY_AW      AW_TIME       AW_TIME            XADEMO        TIME

The following statement creates the cube.

execute dbms_awm.create_awcube 
          ('XADEMO','ANALYTIC_CUBE','XADEMO', 'MY_AW', 'MY_ANALYTIC_CUBE');

You can view the logical cube in the analytic workspace with the following query.

SQL>select * from ALL_OLAP2_AW_CUBES;

AW_OWNER AW_NAME AW_LOGICAL_NAME  AW_PHYSICAL_OBJECT  SOURCE_OWNER  SOURCE_NAME
-------- ------- ---------------  ------------------  ------------  -----------
XADEMO   MY_AW   MY_ANALYTIC_CUBE MY_ANALYTIC_CUBE    XADEMO       ANALYTIC_CUBE

The following query returns the analytic workspace cube with its associated dimensions.

SQL>select * from ALL_OLAP2_AW_CUBE_DIM_USES;

AW_OWNER AW_NAME AW_LOGICAL_NAME  DIMENSION_ DIMENSION_ DIMENSION_   DIMENSION_
                                  AW_OWNER   AW_NAME    SOURCE_OWNER SOURCE_NAME
-------- ------- ---------------- ---------  ---------- ------------ -----------
XADEMO   MY_AW   MY_ANALYTIC_CUBE  XADEMO     AW_CHAN    XADEMO       CHANNEL
XADEMO   MY_AW   MY_ANALYTIC_CUBE  XADEMO     AW_GEOG    XADEMO       GEOGRAPHY
XADEMO   MY_AW   MY_ANALYTIC_CUBE  XADEMO     AW_PROD    XADEMO       PRODUCT
XADEMO   MY_AW   MY_ANALYTIC_CUBE  XADEMO     AW_TIME    XADEMO       TIME