oracle 11g 在数据仓库方面也有不少新功能，尤其是新的多维数据集组织的cube (也就是常说的MOLAP).
在开始本文之前，建议先查看otn 上关于介绍oracle 11g 新功能的data warehouse 部分，由oracle ace 总监Arup Nanda 写的， 目前otn 的中文站点已经有翻译版的, 地址如下:
http://www.oracle.com/technology/global/cn/pub/articles/oracle-database-11g-top-features/11g-dw-olap.html?_template=/ocom/print
这篇文章主要介绍上面这篇文章中的一些实验和没有覆盖到的地方. (记住一定先看上面这篇文章)
导入数据
首先从http://www.oracle.com/technology/products/bi/olap/doc_sample_schemas/global_11g_schema.zip
下载它的示例数据， 之后使用一个具有DBA 权限的用户建立global 用户，用户名一定要是global (除非你修改里面的一些脚本), 执行
SQL> @global_11g_install
它会要求你输入system 的密码和你希望global 用户的密码
之后就会导入global schema . 一共8个表，默认应该是成功并且没有警告的.
不要执行第二个脚本 global_11g_create_cubes.sql, 虽然它global_11g_readme.html 说明里说要执行第二个脚本，但这个脚本有问题，会失败. 执行了也没关系.
使用AWM
伴随oracle 11g 发布的还有一个AWM (Analysis Workspace Manager) 工具，版本为11.1.0.7 , 这是个JAVA 工具，需要JDK 5.0 以上， 你可以到下面地址下载:
http://www.oracle.com/technology/global/cn/products/bi/olap/index.html
oracle 11g 引入了 分析工作区 (aw) 来管理跟OLAP 有关的对象， 而awm 就是一个图形化的用来管理aw 的工具.
解压awm 之后， 在命令行下执行
java –jar awm11.1.0.7.0B.jar
awm 启动后，点击 File –> Connect Database , 选Global 作为你要连接的数据库，输入刚才输入的global 用户名和密码. 之后打开分析工作区 ， 你应该看到global 工作分析区下面有dimensions , cubes 文件夹，你点开后里面应该是空的(otn 的示例上有是因为执行了上面第二个脚本，但是它给出的下载的文件里这个脚本有问题而没有执行，所以应该是空的).
现在可以导入dimension 和cube 的定义，右键点击Dimension 文件夹，选择 从模板创建 , 然后倒入CHANNEL , CUSTOMER , PRODUCT , TIME dimension , 文件就在global_schema 文件夹下的templates 下， 对cube 也执行一样的操作导入PRICE_CUBE 和 UNIT_CUBE .
填充Cube 数据
完成上面操作之后，你应该有了4个dimension 和2个cube , 但是里面都没有数据，你可以使用两种方法从table 里面导入数据,
1. 用pl/sql , 在sqlplus 里面使用 exec dbms_cube.build(‘PRICE_CUBE’) ;
2, 还是使用awm , 在awm 的cube 文件夹下的PRICE_CUBE 上点右键，选择 维护立方体 .
awm 会让你选择维护的cube 和dimension , 点击Next 之后，再选择 立即运行 ， 如果你想看运行的到底是什么，你可以选择保存文件.
之后你可以在Build log 对话框里面查看Output 列，
<CommandOutput>
<CubeLoad
Name="MAP1"
SQL="SELECT /*+ bypass_recursive_check cursor_sharing_exact no_expand */
T16_MONTH_ID ALIAS_114,
T13_ITEM_ID ALIAS_115,
SUM(T19_UNIT_PRICE) ALIAS_116,
SUM(T19_UNIT_COST) ALIAS_117
FROM
(
SELECT
T1.ITEM_ID T19_ITEM_ID,
T1.MONTH_ID T19_MONTH_ID,
T1.UNIT_PRICE T19_UNIT_PRICE,
T1.UNIT_COST T19_UNIT_COST
FROM
GLOBAL.PRICE_FACT T1 )
T19,
(
SELECT
T1.MONTH_ID T16_MONTH_ID
FROM
GLOBAL.TIME_DIM T1 )
T16,
(
SELECT
T1.ITEM_ID T13_ITEM_ID
FROM
GLOBAL.PRODUCT_DIM T1 )
T13
WHERE
((T16_MONTH_ID = T19_MONTH_ID)
AND (T13_ITEM_ID = T19_ITEM_ID)
AND (T16_MONTH_ID = T19_MONTH_ID)
AND (T13_ITEM_ID = T19_ITEM_ID) )
GROUP BY
(T13_ITEM_ID, T16_MONTH_ID)
ORDER BY
T13_ITEM_ID ASC NULLS LAST ,
T16_MONTH_ID ASC NULLS LAST "
LOADED="2523"
REJECTED="0"
</CommandOutput>
完成之后你就可以看到cube 里面的数据了.
查看数据
Oracle 11 g引入一个新的函数cube_table 让我们使用sql 的语法就可以查看到MOLAP 的数据，语法如下
cube 语法: select * from table(cube_table(‘<schema>.<cube_name>’))
示例 PRICE_CUBE : select * from table(cube_table(‘GLOBAL.PRICE_CUBE’)) (如果你是global 登录的，可以不加前面的global.)
dimension 语法: select * from table(cube_table(‘<schema>.<dimension>;<hierarchy>’))
其中hierarchy 部分是可选的, 比如对于global 用户的product dimension:
select * from table(cube_table(‘global.product’));
如果dimension 只有一个hierarchy ，oracle 当然会选择这个，如果dimension 有两个或两个以上的hierarchy , oracle 使用的就是你指定的默认的那个, time dimension 就有CALENDAR 和FISCAL 两个hierarchy , calendar 就是默认的那个，所以你查询time dimension 时没有指定hierarchy , 实际就是查询的calendar hierarchy.
对于每一个cube 和dimension ，oracle 都会创建相应的view ， 所以你查询cube 和 dimension 的时候，也是查询的对应的view.
比如global schema 就会默认的创建下面这些view (在你建立cube 的时候) : product_view , product_parmary_view , time_view , time_calendar_view, time_fiscal_view , price_cube_view.
每个dimension 会默认创建 <dimension_name>_view 和 <dimension_name>_<hierarchy_name>_view , cube 则会对应 <cube_name>_cube_view
所以你执行select * from table(cube_table(xxx)); 的时候实际上就是查询的view
比如其中price_cube_view 的定义就是如下:
1
| <p>  CREATE OR REPLACE FORCE VIEW "GLOBAL"."PRICE_CUBE_VIEW" ("UNIT_COST", "UNIT_PRICE", "TIME", "PRODUCT") AS <br />  SELECT <br />    "UNIT_COST", <br />    "UNIT_PRICE",     </p><p> "TIME",     </p><p> "PRODUCT"   FROM TABLE(CUBE_TABLE('GLOBAL.PRICE_CUBE') ) ;</p> |
cube 的过滤，排序和聚合
更复杂一点的sql:
SELECT * FROM TABLE(CUBE_TABLE(
‘global.price_cube HIERARCHY product primary HIERARCHY time calendar’))
where TIME=’TOTAL_TOTAL’
ORDER BY PRODUCT
;
SELECT SUM(UNIT_COST),SUM(UNIT_PRICE),PRODUCT FROM TABLE(CUBE_TABLE(
‘global.price_cube HIERARCHY product primary HIERARCHY time calendar’))
where TIME=’TOTAL_TOTAL’
GROUP BY PRODUCT
ORDER BY PRODUCT
;
跟普通的sql 基本一样，除了cube_table 里面的写法需要注意一点外, 如果你喜欢通过GUI 界面来看的话，awm 里面右键点击cube 选查看数据. 还可以点击维度展开 , 下面还有图表.
在查讯构建器里面（上图中 文件 菜单那个下面那个按钮）还可以选择构建复杂的.
这个界面倒是跟owb 里面的完全一样，估计用的同一代码
最原始的模型应该是从essbase 借鉴过来的，界面布局和操作方式都跟essbase 非常像.
如何管理cube
并没有直接的pl/sql 可以来删除或者修改cube , 目前dbms_cube 包里面就三个函数 build() , import_xml , validate_xml(). build() 是用来填充数据的 . 如果你对cube 的定义不满意的话，要修改或者删除的话，一般都是通过awm , otn 上面给出的那个global_schema 文件里倒是有一个删除的例子，手工拼接xml ， 然后dbms_cube.import(xml).
set serverout on
declare xmlCLOB clob; v_i number; type t_dims is table of all_cube_dimensions%rowtype; type t_cubes is table of all_cubes%rowtype; v_dims t_dims; v_cubes t_cubes; v_cmd varchar2(400);
begin -- Check if the AW exists
dbms_output.put_line('... checking for GLOBAL AW in GLOBAL schema'); SELECT COUNT(*) INTO v_i FROM all_aws WHERE owner = 'GLOBAL' AND aw_name = 'GLOBAL';
-- Delete the AW if it exists if v_i > 0 then dbms_output.put_line('... deleting GLOBAL AW in GLOBAL schema'); dbms_lob.createtemporary(xmlCLOB,TRUE); dbms_lob.open(xmlCLOB, DBMS_LOB.LOB_READWRITE); dbms_lob.writeappend(xmlCLOB, 9, '<Metadata'); dbms_lob.writeappend(xmlCLOB, 16, ' Version="1.1">'); dbms_lob.writeappend(xmlCLOB, 5, ' <AW'); dbms_lob.writeappend(xmlCLOB, 19, ' Action="REMOVE"'); dbms_lob.writeappend(xmlCLOB, 18, ' Owner="GLOBAL"'); dbms_lob.writeappend(xmlCLOB, 19, ' Name="GLOBAL"/>'); -- Loop over each of the Dimensions in the AW and delete them select * bulk collect into v_dims from all_cube_dimensions where owner = 'GLOBAL' and aw_name = 'GLOBAL';
for i in 1 .. v_dims.count loop v_cmd := '<' || initcap(v_dims(i).dimension_type) || 'Dimension'; dbms_lob.writeappend(xmlCLOB, length(v_cmd), v_cmd); dbms_lob.writeappend(xmlCLOB, 19, ' Action="REMOVE"'); dbms_lob.writeappend(xmlCLOB, 18, ' Owner="GLOBAL"'); v_cmd := ' Name="' || v_dims(i).dimension_name || '"/>'; dbms_lob.writeappend(xmlCLOB, length(v_cmd), v_cmd); end loop;
-- Loop over each of the cubes and delete them
select * bulk collect into v_cubes from all_cubes where owner = 'GLOBAL' and aw_name = 'GLOBAL'; for i in 1 .. v_cubes.count loop dbms_lob.writeappend(xmlCLOB, 7, ' <Cube'); dbms_lob.writeappend(xmlCLOB, 19, ' Action="REMOVE"'); dbms_lob.writeappend(xmlCLOB, 18, ' Owner="GLOBAL"'); v_cmd := ' Name="' || v_cubes(i).cube_name || '"/>'; dbms_lob.writeappend(xmlCLOB, length(v_cmd), v_cmd); end loop; -- Finish off the removal tags
dbms_lob.writeappend(xmlCLOB, 11, '</Metadata>'); dbms_lob.close(xmlCLOB); dbms_output.put_line(xmlCLOB); -- dbms_cube.import_xml(xmlCLOB); commit; else dbms_output.put_line('... GLOBAL AW in GLOBAL schema not found'); end if; exception when others then dbms_output.put_line(' '); dbms_output.put_line('# ERROR: Installation failed.'); dbms_output.put_line('# Please ensure that the analytic workspace GLOBAL is not currently open.'); raise_application_error(-20000, 'Can not delete analytic workspace that is currently in use.');
end;
/
我把dbms_cube.import_xml() 那一行注释掉了，输出类似如下:
<Metadata Version="1.1"> <AW Action="REMOVE" Owner="GLOBAL" Name="GLOBAL"/><StandardDimension Action="REMOVE" Owner="GLOBAL" Name="CHANNEL"/><StandardDimension Action="REMOVE" Owner="GLOBAL" Name="CUSTOMER"/><TimeDimension Action="REMOVE" Owner="GLOBAL" Name="TIME"/><StandardDimension Action="REMOVE" Owner="GLOBAL" Name="PRODUCT"/> <Cube Action="REMOVE" Owner="GLOBAL" Name="PRICE_CUBE"/></Metadata>
你可以看到它执行删除操作就是通过Action = "REMOVE" .
最后
关于cube 的数据储存和刷新 下一篇介绍，另外还有关于oracle 11g 的OLAP 新特性 更频繁地执行查询重写 ,
分区中的陈旧程度检查 otn上都有介绍, 大家可以去看看
参考资料
http://www.oracle.com/technology/global/cn/pub/articles/oracle-database-11g-top-features/11g-dw-olap.html?_template=/ocom/print
otn 上介绍oracle 11g olap 新功能的文章.
http://www.oracle.com/technology/products/bi/olap/collateral/OLAP_11g_MV_query_rewrite.html?_template=/ocom/print
介绍cube mv 的刷新方式的文章
http://www.dba-oracle.com/t_olap_dimensions_cubes.htm
11g 之前的一篇介绍olap 概念的文章, 由oracle ace Mark Rittman 所写， 其中有关于如何在pl/sql 中创建和维护cube 的代码（awm 操作起来还是更简单些）
