文章
商务智能
使用 Oracle Warehouse Builder 进行高级维度设计
作者：Yuli Vasiliev
了解如何不依赖向导，更灵活地构建维度对象。
2011 年 4 月发布
Oracle Warehouse Builder 11g 第 2 版支持对维度对象进行各种操作，包括在可视化环境中定义维度对象，随后将其部署到 Oracle 数据库中的关系模式或分析工作区，以及根据您在映射中定义的逻辑为其填充数据。在向导的帮助下，即使是像多维数据集这样的复杂对象也能在几分钟内构建完成，大多数时候只需点击几下鼠标。随后，您总是可以使用相应的对象编辑器根据您的需求对向导构建的对象进行编辑。
尽管 Oracle Warehouse Builder 提供的向导可以代您处理大量繁琐的工作，但具体项目的需求可能还需要您亲自动手完成一些手动工作，包括编码在内。例如，可能需要指定表函数中的 PL/SQL 代码，以便执行转换或动态生成一组源行。
本文介绍除利用向导能完成的任务外，在 Oracle Warehouse Builder 中还可以完成的一些常见任务。具体来说，本文介绍如何为数据仓库添加小时、日、月和年级别的时序分析，以便分析 Web 流量数据。“多维数据集开发新手入门”一文介绍了如何构建用于分析 Web 站点输出流量的数据仓库，本文对其中使用的示例进行了扩展。为了演练本文中的示例，您需要先完成之前这篇文章中的一些步骤，并以此作为起点。
使用维度组织和重新组织数据
请回忆一下，使用 Warehouse Builder 的 Time Dimension 向导创建的时间维度允许您在年、季度、月和日级别进行时序分析。然而，您可能不仅需要在年到日的级别上进行时间分析，还需要在时、分乃至秒级别上进行此类分析。例如，如果您的数据仓库关注流量统计信息，那么添加小时级别应该是一种合理的做法，如同“多维数据集开发新手入门”中的示例一样。为实现这一目标，您可能会尝试修改向导构建的时间维度。尽管这可能并不容易。我们将创建另一个时间维度，只包含小时级别的数据。以下步骤说明如何完成此操作：
在 Projects Navigator 中，展开 MY_PROJECT->Databases->Oracle->TARGET_MDL 节点，然后右键单击 Dimensions 节点。在弹出菜单中，选择 New Dimension。
在 Create Dimension 主屏幕的 Name and Description 屏幕中，指定维度的名称：CLOCK_DM。
在 Storage Type 屏幕中，选择 ROLAP: Relational storage。
在 Dimension Attributes 屏幕中，将 NAME 属性的 Length 特性修改为 2。
在 Levels 屏幕中，指定唯一的级别：HOUR。
在 Create Dimension 向导完成后，CLOCK_DM 对象应出现在 Projects Navigator 的 MY_PROJECT->Databases->Oracle->TARGET_MDL->Dimensions 节点下。现在，双击此对象，在 Dimension Editor 中打开。
在 Dimension Editor 中，转到 Hierarchies 选项卡并删除 STANDARD 层次结构。
最后，部署 CLOCK_DM 维度以及 Warehouse Builder 随之隐式创建的对象。因此，您必须先部署 CLOCK_DM_TAB 表、CLOCK_DM_SEQ 序列，然后再部署 CLOCK_DM 维度。
下一步是为新创建的维度加载数据。您可能已经猜到，这里讨论的 CLOCK_DM 维度应该只包含 24 个成员（每个成员对应于一天中的一个小时）。
与往常一样，可通过定义了数据流和从源到目标的转换的映射来为一个维度加载数据。因此，您首先需要做的就是定义一个源。与上一篇文章中的映射不同，在本例中，我们不需要借助外部数据源来了解一天的 24 小时。我们将为 CLOCK_DM 维度动态生成维度成员：24 个双字符值，每个值代表 24 小时时钟中的一个小时。
在考虑由数据库动态生成的某些非持久性数据时，我们通常会想到从 DUAL 表中进行选择。但您或许能回想起，DUAL 表仅包含一行。因此，为了生成 24 个值，您可能希望将其置于循环之中。这可利用表函数来实现，这个函数将以行集合（实际上，也就是变长数组）的形式生成并返回这 24 个值。以下步骤说明了如何定义这样的变长数组，随后定义表函数：
在 Projects Navigator 中，展开 MY_PROJECT->Databases->Oracle->TARGET_MDL->User Defined Types 节点，然后右键单击 Varrays 节点。在弹出菜单中，选择 New Varray。
在 Create Varray 对话框中，指定名称（例如 HOURS_VAR），然后单击 OK。此时应显示 Varray 编辑器，转到 Details 选项卡，对设置进行如下更改：
Data Type: VARCHAR2 Length: 2 Size: 24
现在部署新创建的变长数组。为此，在 Projects Navigator 中，右键单击 MY_PROJECT->Databases->Oracle->TARGET_MDL->User Defined Types->HOURS_VAR 节点，然后从弹出菜单中选择 Deploy…。
您现在可以创建表函数了。在 Projects Navigator 中，展开 MY_PROJECT->Databases->Oracle->TARGET_MDL->Transformations 节点，然后右键单击 Table Functions。在弹出菜单中，选择 New Table Function。此时应显示 Create Table Function 向导的第一个屏幕。
在 Name and Description 屏幕中，为所创建的表函数提供一个名称，例如 HOURS_GEN。
在 Return Type 屏幕中，选择此前创建并已部署的 OURS_VAR 变长数组。可在 Collections->Private->TARGET_MDL->Varrays 节点下找到这个变长数组。
在 Implementation 屏幕中，单击 Code Editor 按钮，对表函数进行如下修改：
--initialize variables here s VARCHAR2(2); -- main body BEGIN NULL; FOR i IN 0..23 LOOP --Fetch rows from the input cursor. --Process the rows. --Assign the row to the output_buffer record -- Return rows with piperow statement. SELECT TRIM(LEADING ' ' FROM TO_CHAR('00' + i, '09')) INTO s FROM DUAL; PIPE ROW (s); END LOOP RETURN; EXCEPTION WHEN OTHERS THEN NULL; -- enter any exception code here RETURN; END;
在 Create Table Function 向导完成后，在 Projects Navigator 中右键单击新创建的表函数，此函数应出现在 MY_PROJECT->Databases->Oracle->TARGET_MDL->Transformations->Table Functions 节点下，然后从弹出菜单中选择 Deploy… 。
现在，您已经创建并部署了表函数 HOURS_GEN，接下来就可以继续设计将加载 LOCK_DM 维度的映射。步骤如下：
在 Projects Navigator 中，展开 MY_PROJECT->Databases->Oracle->TARGET_MDL 节点，然后右键单击 Mappings 节点。在弹出菜单中，选择 New Mapping 启动 Create Mapping 对话框。在该对话框中，指定映射名称（例如 LOCK_MAP），然后单击 OK。此时，应显示 Mapping Editor 画布。
在 Projects Navigator 中，选择 HOURS_GEN 表函数对象，并将其拖放到映射画布上。对 CLOCK_DM 维度对象执行相同的操作。
在映射画布中，将 HOURS_GEN 操作符的 VALUE 属性连接到 CLOCK_DM 操作符的 NAME 属性。然后，将 HOURS_GEN 操作符的 VALUE 属性连接到 CLOCK_DM 操作符的 DESCRIPTION 属性。
现在您就可以部署映射了。在 Projects Navigator 中，右键单击 MY_PROJECT->Databases->Oracle->TARGET_MDL->Mapping 节点下的 CLOCK_MAP 对象，然后从弹出菜单中选择 Deploy…。
成功部署后，您就可以执行映射了，该操作应为 CLOCK_DM 维度填充数据。为此，右键单击 CLOCK_MAP 对象，然后从弹出菜单中选择 Start…。
更改多维数据集定义
现在，您已经创建并填充了 CLOCK_DM 维度，接下来应如何将其包括到 Traffic 多维数据集中呢？Oracle Warehouse Builder 提供了 Cube Editor 来处理这样的任务。而且，您还需要更改基础事实表，这是在 Table Editor 中完成的。以下步骤说明了如何完成此操作：
在 Projects Navigator 中，展开 MY_PROJECT->Databases->Oracle->TARGET_MDL->Cubes 节点，然后双击 TRAFFIC 节点。这将在 Cube Editor 中打开此对象。
在 Cube Editor 中，转到 Dimensions 选项卡，将 LOCK_DM 维度添加到已选维度列表中。然后，为 CLOCK_DM 维度选择级别：HOUR。
下一步是更改 traffic_tab 基础表。在 Projects Navigator 中，双击 MY_PROJECT->Databases->Oracle->TARGET_MDL->Tables 节点下的 TRAFFIC_TAB 对象，这将在 Table Editor 中打开此对象。
在 Table Editor 中，转到 Columns 选项卡，添加类型为 NUMBER 的 CLOCK_DM 列，将 Precision 和 Scale 设为 0。
在 Table Editor 中，转到 Keys 选项卡，单击 Add Constraint 按钮。添加 TRAFFIC_CLOCK_DM_FK 键，将其类型定义为 Foreign Key。接下来，单击 Reference 字段，然后单击该字段右侧的省略号按钮，调用 Key Selector 对话框。在该对话框中，选择 CLOCK_DM_TAB 节点下的 CLOCK_DM_T_DIMENSION_KEY_PK，然后单击 OK。返回 Table Editor 的 Keys 选项卡后，单击 Local Columns 字段，选择 CLOCK_DM。
在 Table Editor 中，转到 Indexes 选项卡，单击 Add Index 按钮。此时，一个新索引应出现在列表中确保新创建的索引的 Key Columns 值设置为 CLOCK_DM。同时应将 Type 字段设置为 Bitmap。
在 Project Navigator 中，右键单击 TRAFFIC_TAB 对象，然后从弹出菜单中选择 Deploy...。如果您看到因生成有效升级计划时出现问题而导致部署中止的消息，则需要执行 grant_upgrade_privileges.sql 脚本，此脚本可在 OWB_ORACLE_HOME/owb/rtp/sql 文件夹中找到。可在一个 SQL*Plus 会话中运行此脚本，以 sysdba 身份连接并指定目标模式作为参数（在这个具体示例中，如果您沿用了此处建议的名称，则应为 owbtarget）。
返回 Cube Editor，转到 Physical Bindings 选项卡。在映射画布中，将 TRAFFIC 操作符的 CLOCK_DM 属性连接到 TRAFFIC_TAB 操作符的 CLOCK_DM 属性。
在 Project Navigator 中，右键单击 TRAFFIC 对象，然后从弹出菜单中选择 Deploy...。
现在，您已经将 CLOCK_DM 维度包括到多维数据集结构中了，但仍需基于这样的更改重新填充多维数据集。请回忆一下，已经在 TRAFFIC_MAP 映射中定义了 Traffic 多维数据集的 ETL 逻辑。我们将根据需要更改此映射。
在 Projects Navigator 中，展开 MY_PROJECT->Databases->Oracle->TARGET_MDL->Mappings 节点，然后双击 TRAFFIC_MAP 节点。
从 Component Palette 中，将 Expression 操作符拖放到映射画布上。
在映射画布中，绘制一条从 JOINER 操作符的 DATETIME 输出属性到 EXPRESSION 操作符的 INGRP1 组的连线。
在映射画布中，双击 EXPRESSION 操作符的标题，转到 Expression Editor 对话框中的 Output Attributes 选项卡，在其中定义类型为 VARCHAR 的 HOUR 属性，使其长度为 2，并指定以下表达式：TO_CHAR(INGRP1.DATETIME, ‘HH24’)。
在映射画布中，找到 JOINER 与 AGGREGATOR 操作符之间的 EXPRESSION 操作符，双击该 EXPRESSION 操作符的标题，打开 Aggregator Editor 对话框。然后，转到该对话框的 Input Attributes 选项卡，在其中定义类型为 VARCHAR 的 HOUR 属性，使其长度为 2。接下来，转到 Output Attributes 选项卡，在其中定义相同类型的 HOUR 属性，并指定表达式：INGRP1.HOUR。
在映射画布中，绘制一条从 EXPRESSION 操作符的 HOUR 输出属性到 AGGREGATOR 操作符的 HOUR 输入属性的连线。
在映射画布中，找到与多维数据集绑定的 TRAFFIC 操作符。右键单击该操作符的标题，在弹出菜单中选择 Synchronize。在 Deployment 对话框中，单击 OK 开始同步。
下一步是部署修改后的映射。为此，在 Project Navigator 中，右键单击 TRAFFIC_MAP 对象，然后选择 Deploy…
最后一步是执行映射。为此，在 Project Navigator 中，右键单击 TRAFFIC_MAP 对象，然后选择 Start…
完成上述步骤后，您应该已将 Traffic 多维数据集更改为包括 CLOCK_DM 维度，并重新加载了更新后的数据集。
利用 SQL 获得答案
拥有数据仓库是一件好事，但如何才能利用所有那些已经创建并填充了数据的维度对象呢？无论如何，最终目标都是令您能够解答业务问题。就像数据库中存储的大多数对象一样，维度对象的主要访问工具也是 SQL。Oracle 数据库提供的高级功能可将业务查询转为 SQL，以便从数据仓库中获得答案。此外，在处理针对多维数据集的事实表和与之联接的维度表发出的联接查询时，Oracle 数据库的查询优化器会应用强大的优化技术。可通过以下 GROUP BY 的扩展显著简化此类 SQL 查询的创建：CUBE、ROLLUP、GROUPING 和 GROUPING SETS。
我们来看一个示例。假设您需要了解连续数天内每天中每个小时生成的流量值，以便确定负载较高的时间。以下查询展示了解答此问题的方法：
SELECT g.region_name region
, c.name hour
, TO_CHAR(SUM(tr.out_traffic), '999G999G990') traffic
FROM time_dm_tab t
, clock_dm_tab c
, traffic_tab tr
, geography_dm_tab g
WHERE t.day_start_date = tr.time_dm
AND c.id = tr.clock_dm
AND g.country_id = tr.geography_dm
AND tr.time_dm BETWEEN TO_DATE('01-JAN-2011','dd-MON-yyyy')
AND TO_DATE('11-JAN-2011','dd-MON-yyyy')
GROUP BY GROUPING SETS(g.region_name, c.name)
ORDER BY c.name;
上述查询利用 GROUP BY 的扩展 GROUPING SETS，因此您只能看到以小时级别生成的汇总信息，以及各区域在指定时间段内的流量值总和。结果可能如下所示：
REGION HO TRAFFIC
------------------------- --- --------------------
00 32,096,531
01 512,096
02 48,032
03 512,096
04 48,032
05 28,457
06 48,032
07 560,128
08 48,032
09 76,489
10 560,128
11 48,032
12 48,032
13 48,032
14 28,457
15 48,032
16 1,024,192
17 48,032
18 28,457
19 144,096
20 32,560,595
21 96,064
22 56,914
23 48,032
EU 34,546,935
AS 924,809
NA 33,293,276
27 rows selected.
总结
在本文中，您通过几个示例了解了如何不依赖于 Warehouse Builder 向导定义自己的时间维度和对应的 ETL 逻辑。具体来说，您了解了如何更改一个多维数据集中由向导构建的维度。最后，您看到了一个如何将业务查询转换为 SQL 的示例，该示例在一个 SQL 查询中聚合了多个数据维度。
Yuli Vasiliev 是一名软件开发人员、自由撰稿人和顾问，目前专攻开源开发、Java 技术、业务智能 (BI)、数据库和面向服务的架构 (SOA)。他是《Oracle Business Intelligence:The Condensed Guide to Analysis and Reporting》（Packt，2010 年）的作者，另外还撰写了其他一系列关于 Oracle 技术的图书。。
Printer View