查询 OLAP 11g 多维数据集

目的

在本教程中，您将查询使用 Analytic Workspace Manager 11g (AWM 11g) 创建的 OLAP 数据。 使用 SQL Developer，您可以直接使用 SQL 查询 OLAP 多维数据集。

了解如何创建多维数据集数据的分析报表（包括存储量度和计算量度），以及如何应用利用独特的多维数据集特性的技巧。

还可以了解如何使用多维数据集 MV 自动将针对关系事实表的 SQL 汇总查询重新写入多维数据集。

所需时间：

大约 60 分钟

主题

本教程包括下列主题：

	概述
	情景
	前提条件
	使用 SQL Developer 连接到 OLAPTRAIN 模式
	执行简单的 OLAP 多维数据集查询
	在查询中使用级别和成员条件
	向查询中添加计算量度
	在查询中利用多维数据集的嵌入式汇总特性
	在查询中执行下钻
	使用参数化下钻
	使用多维数据集 MV 进行查询重写
	更多信息

查看屏幕截图

将鼠标置于此图标上以加载和查看本教程的所有屏幕截图。（警告：此操作会同时加载所有屏幕截图，网速较慢时，响应时间可能会比较长。）

注：此外，您还可以在下列步骤中将鼠标放在每个单独的图标上，从而仅加载和查看与该步骤相关的屏幕截图。

概述

通过一组关系视图，SQL 可以直接访问 Oracle OLAP 多维数据集数据。这些视图将 OLAP 多维数据集表示为一个具有以下特性的星型模式：

- 多维数据集视图扮演事实表的角色。

- 维视图和层次视图扮演维表的角色。

OLAP 多维数据集公开的星型设计与传统的基于表的星型模型非常类似。维视图围绕一个或多个多维数据集视图形成星群。然而，两者之间存在两个主要差别：

- 星型模式中的事实表存储详细数据（称为叶），而多维数据集视图显示很多汇总级别。

- 多维数据集中的计算只显示为多维数据集视图中的列，等式的计算在 OLAP 引擎中进行。

这些差别影响您查询数据的方式。使用星型查询，可以通过组合聚合函数（如 sum）和 GROUP BY 子句来聚合数据。使用 OLAP 查询，只需将所需的数据（存储的或计算的）选择为列。通常，不一定需要聚合函数，因为多维数据集已经对数据进行了汇总。

对于大多数基于多维数据集的查询，有四个基本步骤：

1. 选择量度和维属性。
2. 联接多维数据集和维视图。
3. 应用量度和维属性条件。
4. 使用“All”筛选器以便对不包含维的列利用汇总。

返回主题列表

情景

本教程的 OLAP 数据是使用 构建 OLAP11g 多维数据集教程中的步骤创建的。有关本教程中使用的 OLAP 模型的信息，以及有关如何创建 OLAP 11g 多维数据集的分步说明，请单击该链接。

返回主题列表

前提条件

开始本教程之前，您应该：

返回主题列表

使用 SQL Developer 连接到 OLAPTRAIN 模式

在本教程中，使用 SQL Developer 来查询 OLAP 数据，但也可以使用任何 SQL 工具。

要连接到 OLAPTRAIN 模式，请执行以下步骤：

1.	启动 SQL Developer。
2.	选择 View > Connections 在导航器窗格中显示 Connections 选项卡。
3.	在 Connections 选项卡中，右键单击 Connections 节点并从菜单中选择 New Connection。
4.	在 New / Select Database Connection 窗口中，输入连接名、用户名和口令。然后，在 Oracle 选项卡中，输入或选择以下选项： 注： - Username 为 olaptrain。 - Password 是安装 OLAPTRAIN 模式时创建的口令。 - Hostname 是安装有 Oracle 数据库的服务器的主机名。
5.	单击 Connect。该连接出现在导航器中。
6.	选择 olaptrain > Views 以 olaptrain 模式显示 OLAP 数据的多维数据集视图。 注：当使用 AWM 11g 创建多维数据集时，将由 Oracle OLAP 自动创建和维护多维数据集视图。 接下来，您将使用这些视图直接查询在“构建 OLAP 11g 多维数据集”教程中创建或使用示例模式安装程序创建的 OLAP 数据。

返回主题列表

执行简单的 OLAP 多维数据集查询

在本主题中，打开一个 .sql 文件，该文件包含很多针对 OLAP 数据的 SQL 查询。

然后，检查和运行一个非常简单的 OLAP 查询，该查询返回处于 DEPARTMENT 级别的产品的 SALES 总计。

1.	选择 File > Open，然后导航到将 SQL 查询文件下载到的文件夹。然后，打开 cube_queries.sql。 结果：cube_queries.sql 的内容出现在 Enter SQL Statement 窗格中。
2.	通过单击 X 关闭 local (olaptrain) 选项卡。
3.	垂直调整 SQL 语句窗格的大小以便您可以查看第一个查询。然后，在 Enter SQL Statement 窗格的右上角，从列表中选择 olaptrain 连接。 结果：现在，可以针对 olaptrain 模式执行此 .sql 文件中的查询。 查询说明： - 只选择 Sales 作为列。没有应用任何 SQL 聚合函数。 - 使用 Product 维层次中的级别 DEPARTMENT 筛选产品成员。 - 在 WHERE 子句中限定所有维，即使仅选择了 Product 维也是如此。在 OLAP 多维数据集查询中，查询中 没有选择的维需要一个“ALL”条件 — 该条件指定每个维列的最高级别层次值 — 以便利用多维数据集已经计算的汇总。
4.	将光标放在查询中间的某个位置。 例如：
5.	选择 Execute 工具（或按 F9）。 结果：该查询应该返回三行，结果应如下所示： 通过使用“All”筛选器，在 OLAP 引擎中执行聚合。几乎立即返回数据。

返回主题列表

在查询中使用级别和成员条件

在第一个查询中，对 Product 维（这是唯一选定的维）使用“级别”条件。在 OLAP 查询中，您可以将级别条件应用于所有维，在下面这个示例中您会看到这种情况。

每个 OLAP 层次和维视图都包含一个 LEVEL_NAME 列。该列中的值是您在 AWM 中对维建模时创建的 OLAP 层次 Level 对象的名称。通过在 WHERE 子句中为该列指定值，您便可以筛选数据以只包含在该层次中处于指定级别的那些维成员。

如下所示，您可以使用 SQL Developer 的 Connections 导航器检查 OLAP 多维数据集、维和层次视图，包括查询的列名称和值。只需展开要检查的视图即可。还可以通过双击所需的视图然后选择 Data 选项卡来查看任何列的数据值。

如导航窗格中的 Connections 选项卡所示，OLAP 将列名称截断为 24 个字符。因此，它有助于在查询中使用列之前查看列的名称。

以下查询为处于 CLASS 级别的 Channel、处于 DEPARTMENT 级别的 Product、日历年 2007 的 QUARTERS 以及 ALL REGIONS 返回 SALES。

1.	在 Enter SQL Statement 窗口中，向下滚动以查看第二个查询： 查询说明： (A) 选择了四个维之中的三个 — Channel、Product 和 Time。 (B) 对于每个所选择的维，使用维层次视图，在每个案例中选择 Long Description 列。（注意，列名称被截断为 24 个字符，与 SQL Developer 导航窗格中的显示完全匹配。） (C) 虽然 Geography 维不在 SELECT 语句中，但是在 FROM 子句中指定了 Geography 层次视图。这种方法使您能够使用维的层次视图（而不是多维数据集视图）为缺少的维指定“ALL”条件。 (D) 由于 Geography 维不在查询中，因此需要使用“ALL”条件来利用 Geography 上的多维数据集聚合。这是使用以下 Level 条件在 WHERE 子句中指定的： g.level_name = 'ALL_REGIONS' (E) Level 条件用来使用适当的维层次视图和适当的层次级别值筛选查询中三个维的数据： c.level_name = 'CLASS' p.level_name = 'DEPARTMENT' t.level_name = 'CALENDAR_QUARTER' (F) 在 Time 维上的筛选通过应用“Member”条件进一步缩小。这种类型的条件选择一个特定的维成员，而不是处于特定级别的所有成员。该成员条件为： t.calendar_year_long_descr = 'CY2007' (E) 对于 Time 维，级别条件（选择处于“QUARTER”级别的所有成员）以及成员条件（从此子集中选择日历年长描述为“CY2007”的所有成员）的组合只返回日历年 2007 的那些季度。
2.	就像之前在上一主题中所做的一样，将光标放在查询中间的某个位置。
3.	选择 Execute 工具（或按 F9）。 结果：查询结果应该如下所示： 向下滚动以查看其余结果。

返回主题列表

向查询中添加计算量度

多维数据集视图将计算显示为列，这大大简化了分析查询的规范。计算量度的列将数据显示为完全解决。但是，计算是在 OLAP 引擎中进行的，并通过多维数据集视图传递。

例如，在 SALES_CUBE_VIEW 中只有 SALES 和 QUANTITY 量度是存储量度。所有其他量度列都是计算量度。

OLAP 计算在所有汇总级别都很正确，即使在聚合规则比较复杂的情况下也是如此。

在下面的这个查询中，将向上一个查询添加三个 OLAP 计算量度。

1.	在 Enter SQL Statement 窗口中，向下滚动以查看第三个查询： 三个计算量度已添加到 SELECT 语句中。 round(s.SALES_YTD) as ytd round(s.SALES_YTD_PY_PCT_CHG, 2) as ytd_PY_pct_chg how_is_sales_ytd 不需要进行其他修改。计算量度是在 OLAP 引擎中计算的，并且只通过多维数据集视图传递。
2.	将光标放在查询中间的某个位置，并按 F9。 结果：查询执行得非常快，就像四个量度都已经存储一样。查询结果应该如下所示： 向下滚动以查看其余结果。 注：有关如何使用 AWM 11g 创建计算量度的更多信息，请参阅 构建 OLAP11g 多维数据集。

返回主题列表

在查询中利用多维数据集的嵌入式汇总特性

由于 OLAP 多维数据集的嵌入式汇总特性，您可以轻松地同时查询多个级别，以在某个维的任何汇总级别中选择值。由于 OLAP 模型的该特性：

	可以选择任何维成员 — 无论汇总级别如何。
	复杂聚合规则（例如，平衡）和计算自动在 OLAP 引擎中得到解析。

在下面的这个示例中，对上一查询进行修改以返回以下时间各个级别的数据：Month、Quarter 和 Year in 2007。

1.	在 Enter SQL Statement 窗口中，向下滚动以查看第四个查询： 注意修改 Time 维的筛选器的方式： - 在上一查询中，返回 2007 年的所有季度成员。 - 在该查询中，使用 Long Description 属性应用多级别“Member”条件。该条件在 Calendary Year 层次中各个不同级别上选择三个时间维成员： t.long_description in ('CY2007', 'Q3-CY2007', 'Nov-2007') 。
2.	将光标放在查询中间的某个位置，并按 F9。 结果：所有计算都非常正确，并且查询性能未受影响。 查询结果应该如下所示： 向下滚动以查看其余结果。

返回主题列表

在查询中执行下钻

由于维层次属于数据模型的一部分，因此可以将下钻指定为查询的一部分，以便返回指定父成员的子集。可以对任何类型的层次进行下钻，包括基于级别、越级、不齐整以及基于值的层次。

在本例中，对上一查询进行修改以便在 Product 和 Geography 维上显示下钻。具体地说就是，该查询：

	下钻“ALL_PRODUCTS”成员以便返回其子集 — Product 的 Department 成员。
	下钻“ALL_REGIONS”成员以便返回其子集 — Geography 的 Regional 成员。
	选择该层次中最高级别（而不是在 Class 级别）上的 Channel 成员。

1.

在 Enter SQL Statement 窗口中，向下滚动以查看第五个查询： 查询说明： - 在上一查询中，没有选择 Geography 维。 - 在该查询中，包括了 Geography，但 Channel 不在 SELECT 语句中。因此，必须将“ALL”条件应用于 Channel 维。 - 通过指定 Geography 层次视图 PARENT 列的成员在 Geography 维上执行下钻： G.PARENT = "ALL_REGIONS" 。该条件返回 All Regions 的子集，它们是 Geography 的 Regional 成员。 - 通过指定 Product 层次视图 PARENT 列的成员在 Product 维上执行下钻： P.PARENT = "ALL_PRODUCTS" 。该条件返回 All Products 的子集，它们是 Product 的 Department 成员。

2.

将光标放在查询中间的某个位置，并按 F9。 查询结果应该如下所示： 向下滚动以查看其余结果。注意 Product 和 Geography 上的下钻如何选择适当的层次子集使所有这些计算都非常正确。

返回主题列表

使用参数化下钻

您也可以在 OLAP 多维数据集查询中使用参数化下钻。只需对 PARENT 列使用一个替代参数以使用户能够输入一个有效的维成员值。

在下面的查询示例中，使用替代参数以使用户能够输入一个有效的 Time 维成员值。

1.	在 Enter SQL Statement 窗口中，向下滚动以查看第六个查询： 查询说明： - 在该查询中，从查询中删除了 Geography，但包含了 Channel。因此，将“ALL”条件应用于 Geography 维。 - 采用与上一查询相同的方法在 Channel 和 Product 维上执行下钻。 - 此外，使用该层次视图的 PARENT 列在 Time 维上执行参数化下钻。另外，使用 nvl 函数，以便在不提供值时，自动将“ALL_YEARS”成员用作父值。
2.	将光标放在查询中间的某个位置，并按 F9。 结果：出现 Enter Bind Values 窗口。 注：由于使用了 nvl 函数，因此，在没有输入值时将指定“ALL_YEARS”。
3.	不要在 Value 框中输入任何内容，单击 Apply。 查询结果应该如下所示： 注： - 该查询返回 Time 维中每一年的数据 — 这是 Calendar Year 层次中 ALL_YEARS 的子集。 - 另请注意，对于 CY2005，YTD 计算没有数据值。这是正确的，因为 2005 是此数据模型中的第一年（没有 2004）。
4.	再次将光标放在查询中间的某个位置，并按 F9。然后，在 Enter Bind Values 窗口的 Value 框中输入 CY2007，如下所示：
5.	单击 Apply。 查询结果应该如下所示： 显示 2007 年各个季度（CY2007 的子集）的数据。

返回主题列表

使用多维数据集 MV 进行查询重写

在 Oracle 数据库 11g 的物化视图 功能扩展中，可以将 OLAP 多维数据集表示为按多维数据集组织的物化视图（多维数据集 MV）。查询优化器自动识别何时现有多维数据集 MV 可以且应该用来满足针对详细的事实表发出的 SQL 查询。多维数据集 MV 表示一个重要的汇总空间，它的优点是便于管理且能够提高查询性能。

在本主题中，您将针对 OLAPTRAIN 模式运行一组汇总 SQL 查询。这些汇总查询是从针对 olaptrain 模式的 Oracle BI Answers 即时查询会话中捕获的，反映了常规 BI 工具所生成的汇总查询。

首先，关闭查询重写，看一看如何针对事实表执行汇总查询。然后，打开重写并再次运行查询。打开重写后执行查询时，您将：

	观察数据库如何自动将汇总查询重新写入 OLAP 多维数据集 MV
	比较 SQL 汇总查询与 OLAP 多维数据集 MV 查询的性能。

注：

- 有关如何启用多维数据集 MV 的查询重写的信息，请参阅 构建 OLAP11g 多维数据集中的 启用多维数据集 MV 的查询重写主题。

- 要执行本主题中的步骤，您必须已完成（可选）“创建 OLAP11g 多维数据集”OBE 中的 启用多维数据集 MV 的查询重写主题或按照 前提条件一节所述安装了示例模式 AW。

执行以下步骤：

1.	在导航器窗格的 Connections 选项卡中，选择 olaptrain 连接下的 Materialized Views 节点。 结果：出现按多维数据集组织的物化视图（CB$ 表）。每个多维数据集和关联的维层次各有一个 MV。这些多维数据集 MV 是由 Oracle 11g 数据库的 OLAP 选件自动创建和维护的。
2.	选择 File > Open，然后导航到将 SQL 查询文件下载到的文件夹。然后，打开 summary_queries.sql。 在 Enter SQL Statement 窗格的右上角，从列表中选择 olaptrain 连接，然后垂直调整 SQL 语句窗格的大小，以便该窗格至少占据 SQL Developer 窗口空间的一半，如下所示：
3.	要确保关闭多维数据集 MV 的查询重写，请单击 ALTER materialized VIEW cb$sales_cube disable query rewrite 语句，然后按 F9。
4.	向下滚动以查看第一个汇总查询，如下所示： 该查询返回 Quantity 和 Sales by Year 以及 Product Category。
5.	将光标放在查询中间，并按 F6 以显示查询的解释计划，如下所示： 解释计划表明该查询将事实表联接到该查询中指定的维表，然后执行对事实表的全表扫描，以便返回请求的数据。
6.	仍然将光标放在查询中间，并按 F9 执行查询。 查询结果应该如下所示： 记下查询性能，即在 SQLStatement 窗格顶部显示的值，如上所示。在本例中，查询的运行时间大约为 3.05 秒。您观察到的性能具体取决于与数据库服务器配置有关的很多因素。
7.	在第二个汇总查询中单击，该查询用于返回 Sales by Year、Department、Class 和 Country。 a. 按 F6 显示解释计划。和所有汇总查询一样，将执行事实表的全表扫描，以便返回请求的数据。 b. 按 F9 执行查询，如下所示。再次记录您的查询时间。
8.	使用上述相同方法，执行第三个和第四个查询。记录每个查询的时间。 在本示例中： 第 3 个汇总查询的执行如下所示： 第 4 个汇总查询的执行如下所示：
9.	在 SQL 文件中向上滚动，并通过执行以下操作，启用该会话的查询重写，并写入 OLAP 多维数据集 MV： a. 单击 ALTER SESSION SET query_rewrite_integrity=stale_tolerated 语句，然后按 F9。 b. 单击 ALTER SESSION SET query_rewrite_enabled = force 语句，然后按 F9。 c. 单击 ALTER materialized VIEW cb$sales_cube enable query rewrite 语句，然后按 F9。
10.	要确认查询将重写，请在第一个查询中单击并按 F6 以显示解释计划，如以下屏幕截图所示： 现在，汇总查询将重新写入多维数据集 MV。
11.	按 F9 执行查询并记下提高的性能。
12	向下滚动，并在第二个汇总查询中单击。按 F6 确认重写，然后按 F9 执行查询。
13.	执行其余查询。记录每个查询的时间。 注：通常，多维数据集 MV 重写的查询时间比针对关系事实表的汇总查询快 10 到 50 倍。
14.	使用完查询文件之后，关闭它们并退出 SQL Developer。

返回主题列表

更多信息

要获得与 OLAP 有关的培训的更多信息，请使用以下链接：

	Oracle 数据库 11g：OLAP 基础（Oracle 大学课堂教学）
	构建 OLAP11g 多维数据集 (OBE)
	结合使用 Oracle OLAP 11g 和 Oracle BI 企业版 (OBE)
	基于 OLAP 11g 多维数据集创建交互式 APEX 报表 (OBE)

返回主题列表

将鼠标置于该图标上可以隐藏所有的屏幕截图。