Oracle OLAP-OLAP_TABLE

I have some experiences in using Oracle OLAP recently. Take a note:

Oracle data warehouse has many implementation methods. I know two methods:

One is ROLAP, which is based on fact table and dimension table. These tables are based on relational Tables, that is, general partition tables. Based on these partition tables, materialized view and partition are added, the statistical information and detail information of summary are provided. The report development tool can make the application transparent, and the Buddha is operating the cube and dimension. This method is simple, fast, but not flexible enough. After the requirement is changed, the summary information needs to be redefined and extracted.

The second is MOLAP, which is based on multi-dimensional storage format rather than relational table. MOLAP is an Option integrated with objective e database. It is a general report development tool (based on SQL) you cannot directly operate (query and update) such storage formats. Before using SQL to query such storage, we must first display the storage in view format and then operate such views through SQL, get the summary and detail information. This type of OLAP implements real CUBE Storage. After dimensions are defined, the summary and detail information can be calculated by the CUBE itself and replaced by runtime computing with storage, improved performance, MS
SQL SERVER SSAS is similar.

Regardless of the method, materialized view and partition can be used to improve performance, especially partitioned materialized view.

Here, we will focus on molap's report development implementation methods. ETL and modeling are similar to ROLAP. You can use owb (Oracle warehouse builder) and AWM (analytic Workspace Manager ). when presenting molap, You Need To flat molap. In this way, you can define cube and dimenions in the form of view (or use materialized view). The specific data is displayed, facilitate SQL query (here we have created these views for Oracle10g and oracle11g ). If discover exists
The BISE component of OLAP plus can also be directly presented using such presentation tools. The olap_table function is used to create these views. Here is the specific usage of olap_table:

The only note in olap_table is that it has two implementation methods: Limit map and fetch, here we mainly look at the example of using limit map:

Select *
From table (olap_table (
'Lewis1. sdaclassification duration session ','','',
'Measure xunits from output_units
Dimension xtime from time
Dimension xpersons from persons
Dimension xwip from WIP
Dimension xreports from reports
ROW2CELL r2c '));

Note: There are four parameters. The first parameter is used to pass schema, analytic workspace, and result set storage time and space,

The second parameter is used to specify pre-defined tables. These pre-defined tables determine the Data Type of the returned result set.

The third parameter is used to specify the FETCH Command. If the fourth parameter needs to be passed, the third parameter does not need to be passed.

The fourth parameter is used to specify the limit map, which limits the entire returned result set and can be passed by eight parameters to exceed the limit of 4000 characters, you can also use analystic workspace variables to exceed the limit of 4000 words.

LEWIS1: schema of MOLAP

SDACLASSIFICATION: AW name

Duration session/QUERY: The result set is saved in SESSION/QUERY.

The FROM statement can be obtained FROM all_olap2_aw_phys_obj.

By the way, we will share the following types of Active Catalog Views:

A) analytic workspace view:

All_OLAP2_AWS;

All_OLAP2_AW_phys_obj;

All_OLAP2_AW_phys_obj_prop;

All_OLAP2_AW_Attributes;

All_OLAP2_AW_Catalog_measures;

All_OLAP2_AW_Catalogs

B) Cube views:

All_OLAP2_AW_Cubes;

All_OLAP2_AW_Cube_Dim_Uses;

All_OLAP2_AW_Cube_Measures;

All_OLAP2_AW_Cube_Agg_lvl;

All_OLAP2_AW_Cube_Agg_meas;

All_OLAP2_AW_Cube_Agg_op;

All_OLAP2_AW_Cube_Agg_Specs;

C) dimension views:

All_olap2_aw_dimensions;

All_olap2_aw_dim_hier_lvl_ord;

All_olap2_aw_dim_levels

Let's take a look at the four dimension examples:

Select *
From table (olap_table (
'Lewis1. sdaclassification duration query ',
'','',
'Dimension reports FROM REPORTS
WITH
HIERARCHY REPORTS_PARENTREL
FAMILYREL Type, MODULE FROM
Reports_familyrel (reports_levellist ''type ''),
Reports_familyrel (reports_levellist ''module '')
LABEL reports_short_description
ATTRIBUTE Report_Name from Reports_Long_description
'
))
Order by type, module, reports;

Select *
From table (olap_table (
'Lewis1. SDACLASSIFICATION duration query ',
'','',
'Dimension time FROM time
WITH
HIERARCHY time_PARENTREL
FAMILYREL year, month, day FROM
Time_familyrel (time_levellist ''year ''),
Time_familyrel (time_levellist ''month ''),
Time_familyrel (time_levellist ''day '')
LABEL time_short_description
ATTRIBUTE time_Name from time_Long_description
'
))
Order by time, year, month, day

Select *
From table (olap_table (
'Lewis1. SDACLASSIFICATION duration query ',
'','',
'Dimension persons FROM persons
WITH
HIERARCHY persons_PARENTREL
FAMILYREL mentor, mentee FROM
Persons_familyrel (persons_levellist ''mentor ''),
Persons_familyrel (persons_levellist '''mentee '')
Label persons_short_description
Attribute persons_name from persons_long_description
'
))
Order by mentor, mentee;

Select *
From table (olap_table (
'Lewis1. sdaclassification duration query ',
'','',
'Dimension wip FROM wip
HIERARCHY WIP_parentrel
Familyrel wip_status
From
Wip_familyrel (wip_levellist ''wip '')
ATTRIBUTE wip_name from wip_Long_description
'
))
;

Hierarchy and level expressions are involved here. The general concepts is to start from dimension-> hierarch (xxx_parentrel)-> level (familyrel)-> attributes (xxx_long_description ), from ALL_OLAP2_AW_PHYS_OBJ, you can use the like clause to query xxx_parentrel, xxx_familyrel, and xxx_levellist to determine each field. These fields are mapped to the last view field.

Of course, the above four examples are mainly used for demonstration. If you need to create an ADTS, Materialized view, partition materialized view, and materialized view index in the formal environment to store MOLAP data:

ADTs example:

OLAP_TABLE can automatically define the data type at run time, or pre-define the data type of the table to return the dataset. The above four examples are automatic, the disadvantage is that the query needs to be re-compiled every time, and the performance is slow. In the production environment, we need to predefine the data structure with ADTs:

Create type reports_row as object (reports_id varchar2 (20), reports_type varchar2 (20), reports_module varchar2 (20), reports_name varchar2 (40 ));

Create type reports_tab as table of reports_row;

Create or replace view report_dim_view

Select reports_id, reports_type, reports_module, reports_name
From table (olap_table (
'Lewis1. SDACLASSIFICATION duration query ',
'Reports _ tab ','',
'Dimension reports_id FROM REPORTS
WITH
HIERARCHY REPORTS_PARENTREL
FAMILYREL reports_Type, reports_MODULE FROM
Reports_familyrel (reports_levellist ''type ''),
Reports_familyrel (reports_levellist ''module '')
LABEL reports_short_description
ATTRIBUTE Report_Name from Reports_Long_description
'
));

 

For example:

Create materialized view classification_output

Build immediate

Refresh complete on demand

Disable query rewrite (if this is enable query rewrite, it cannot be created successfully. Why ?)

As

Select *
From table (olap_table (
'Lewis1. sdaclassification duration session ','','',
'Measure Xunits from OUTPUT_UNITS
Dimension Xtime from TIME
Dimension Xpersons from PERSONS
Dimension Xwip from WIP
Dimension xreports from reports
Row2cell r2c '));

After materialized view is used here, materialized view is directly queried every time you use SQL query. Adding an index to the MV and partition can improve the performance. Of course, you must define each dimension, then, use join to perform slice, pivot, and other operations. These depend on how you define the cube view and dimension view.