oracle OLAP - OLAP_TABLE

最近在使用Oracle OLAP方面有些心得，写一下笔记：

Oracle data warehouse有很多实现方法，我知道的有两种：

一种是ROLAP，基于fact table, dimension table,这些table都基于relational table，也就是通常的二维表，在这些二维表的基础上，加上materialized view和partition，提供了summary的统计信息和detail 信息，使用报表开发工具，可以让应用透明化，放佛就是在操作cube和dimension。这种方法简单，操作快，但是灵活度不够，需求变更后，summary信息也需要重新定义与抽取。

第二种是MOLAP，非relational table而是基于多维存储格式的，这种MOLAP是orace database的集成的一种Option，一般的报表开发工具（基于SQL)不能直接操作（查询和更新）这类存储格式，我们必须在使用SQL查询这类存储之前，先将这类存储以view的形式展现出来，然后通过SQL操作这类views，得到summary和detail的信息。这类OLAP实现了真正CUBE（魔方）的存储，定义了维度后，summary和detail信息都可以由CUBE自己计算出来，用存储代替了运行时计算，提高了性能，MS SQL SERVER的SSAS也是类似的概念。

无论是哪种方法，materialized view和partition，都可以利用来提高性能，尤其是partitioned materialized view.

这里，着重讲下MOLAP的报表开发实现方法，ETL，建模都是和ROLAP差不多的。可以使用OWB(Oracle Warehouse Builder)和AWM(Analytic Workspace Manager).在对MOLAP做展现的时候，需要对MOLAP实现扁平化，也就是以view的形式（或者用materialized view）讲CUBE和Dimenions的定义，具体数据展现出来，方便SQL查询（这里针对ORACLE10G，oracle11g已经自动将这些VIEW创建好了）。当然如果有DISCOVER OLAP PLUS的BISE组件，也可以直接用这类展现工具来展示。创建这些VIEW主要使用到了OLAP_TABLE这个函数，这里是OLAP_TABLE的具体使用方法：

OLAP_TABLE中，唯一要注意的地方，就是他有两种实现方法，一种是使用LIMIT MAP，一种是使用FETCH 命令，这里主要看下使用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' )) ;

注释：这里有四个参数，第一个参数用来传递schema,analytic workspace和结果集的保存时空，

    第二个参数用来指定预定义的表，这些预定义的表决定了返回结果集的数据类型

    第三个参数用来指定FETCH 命令，如果需要传递第四个参数，那么第三个参数就不需要传递

    第四个参数用来指定limit map，这限制了整个返回结果集，可以分为8个参数来传递，这8个参数是为了突破4000个最大字符的限制，也可以使用analystic workspace的变量来突破4000字的限制

LEWIS1： MOLAP所在的schema

SDACLASSIFICATION: AW名字

DURATION SESSION/QUERY: 结果集在SESSION/QUERY保存

这里的FROM字句，都可以从all_olap2_aw_phys_obj来查询获得。

顺便分享下这几类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

看下四个dimension的例子：

    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 WITH  
                           HIERARCHY WIP_parentrel
                           familyrel wip_status
                           from
                           wip_familyrel(wip_levellist''WIP'')
                           ATTRIBUTE wip_name from wip_Long_description
                          '
              ))
;

这里涉及到hierarchy，level的表述，大体的concepts是先从dimension出发->hierarch(xxx_parentrel)->level(familyrel)->attributes(xxx_long_description),从ALL_OLAP2_AW_PHYS_OBJ可以用like字句查询xxx_parentrel,xxx_familyrel,xxx_levellist来确定每个字段，这些字段是被mapping到了最后view的字段上。

当然，上面的4个例子，主要是为了演示用，如果在正式环境里面，需要建立ADTS,Materialized view, partition materialized view, materialized view index 来存储MOLAP的数据：

ADTs例子：

OLAP_TABLE可以在运行时自动定义数据类型，也可以预先定义好表的数据类型来返回数据集，上面的4个例子都是自动型的，缺点是每次都要重新编译查询，性能慢；我们在生产环境中，都要先用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 as

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
                          '
              ));

 

举个MV的例子：

create materialized view classification_output

build immediate

refresh complete on demand

disable query rewrite (如果这里是enable query rewrite就创建不成功，不知道为何？）

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' )) ;

这里使用了materialized view之后，每次使用SQL查询，直接就查询Materialized view了，给MV加索引，加partition可以提高性能，当然必须对各个dimension做定义，然后使用join来做切片，转轴等操作。这些都取决于你如何定义cube view, dimension view