谈Excel 与OLAP整合应用doc 14页正式版Word格式.docx

谈Excel 与OLAP整合应用doc 14页正式版Word格式.docx

《谈Excel 与OLAP整合应用doc 14页正式版Word格式.docx》由会员分享，可在线阅读，更多相关《谈Excel 与OLAP整合应用doc 14页正式版Word格式.docx（14页珍藏版）》请在冰豆网上搜索。

关于透过OLAP数据来源建立数据透视表

「在线分析处理」（OLAP）是一种组织大型企业数据库的方法。

OLAP数据是由数据库管理员负责组织的，以符合用户分析和管理数据的方法，其好处是只需花较少的时间和精力就可建立所需的报表。

OLAP数据库会按明细数据层次组织数据，它会使用与您在分析数据时所使用的同一种类别。

比方说，某公司产品在全世界销售信息的数据库可能是由多个单独的字段组成，分别标明国家、地区、县/市和销售地点。

在OLAP数据库中，这种地理信息可以按明细数据层次从低到高排列如下：

这种组织方式使Excel更容易显示较高层次的销售汇总资料（例如，整个国家或地区的总销售额），以及显示出销售情况好或坏的不同地点的明细数据。

在数据透视表或枢纽分析图中，您可以显示不同层次的汇总数据，并且在需要检视更详细的数据时，可以只显示感兴趣的项目。

至于维度和cube又是什么意思呢？

关于数据特定特征的一组层次（例如：

地理区域）称为维度。

这样，有关销售时间的信息就可以组织在具有年、季、月和日层次的时间维度中。

OLAP数据库称为cube，因为它们既包含了汇总数据（例如：

销售或库存资料）也包含了多个维度（例如：

时间、地点和产品）。

OLAP数据库的设计目的，是为了加快撷取数据的速度。

因为在建立或变更数据透视表或枢纽分析图时，OLAP服务器（而不是Excel）会计算汇总值，这样就只有较少数据送到Excel中。

此方法使您可以处理的来源数据比使用非OLAP数据库组织的数据还要多，因为对于非OLAP数据库而言，Excel必须先撷取每一笔记录，然后再计算汇总值。

欲于Excel2000中连接至OLAP数据来源与连接至其它外部数据库是一样的，您也可以使用由MicrosoftSQLServerOLAPServices（MicrosoftOLAP服务器产品）所建立的数据库来进行工作。

Excel还可以使用那些提供了与OLE-DBforOLAP兼容的资料来源驱动程序的协力厂商OLAP产品。

Excel2000只能将您从OLAP资料来源中所撷取的数据显示为数据透视表或枢纽分析图，而不能将OLAP数据显示成外部数据范围。

您可以将OLAP数据透视表和枢纽分析图储存在报表模板中，还可以替OLAP查询建立查询档案。

OLAP查询档的扩展名为.oqy。

在开启.oqy档案时，Excel会显示一个空白的数据透视表，以供您进行版面配置。

透过关系型数据库建立cube

Excel2000提供了「OLAPcube精灵」，以协助您在OLAPcube中组织关系型数据库（例如：

MicrosoftSQLServer）中的资料。

透过关系型数据库查询来建立cube，能让您可以在数据透视表或枢纽分析图中处理以前所不能处理的大量数据，以及加速数据的撷取。

建立和使用这种类型的OLAPcube并不需要有OLAP服务器产品。

如果要了解和使用MicrosoftExcel的这种特性，应熟悉如何透过MicrosoftQuery管理数据库和存取数据透视表或枢纽分析图的外部数据。

如果透过查询建立了OLAPcube，则可以将一组简单的记录转化为结构化的层次或cube，这样，报表就能着重强调所需层次的明细数据。

也可以预先定义报表的汇总值，从而加快报表的计算。

如果要在报表中处理大量外部数据（尤其是在系统资源的范围内，如果记录的数量超过了Excel所能分析的数量时），则可建立OLAPcube。

由于cube允许Excel仅取出目前报表检视所需的数据，因此对于大量数据，透过cube建立和更新报表比取得数据库的整笔记录集要快。

OLAPcube中的数据只能作为数据透视表和枢纽分析图传回到Excel，这一点与查询中的关系数据库记录不同，关系数据库中的记录还可作为工作表上的外部数据范围传回到Excel。

欲建立OLAPcube，首先应在MicrosoftQuery中建立查询，在建立了包含OLAPcube所需所有字段的查询后，就可执行「OLAPcube精灵」建立cube。

在以下的操作步骤中，我们要实际示范如何透过关系型数据库查询来建立OLAPcube：

附注：

Query对于MicrosoftExcel是一个预设的选项功能，它在第一次会自动地安装在您的计算机上，让其您可以使用指令或功能来撷取外部数据。

如果MicrosoftOffice或Excel安装您的计算机上，而Query不却不能使用适用，则请您先安装MicrosoftQuery。

1.于Excel2000中，从「数据」菜单中选取「取得外部数据」指令，再选取「新增数据库查询」指令。

2.当「选择数据来源」对话框出现时，使用鼠标按一下【在线分析处理】（OLAP）Cubes索引卷标以便切换至图表2所示的页面。

图表2

3.使用鼠标连按两下<

新数据来源>

选项。

4.于标示为1的文字方块中键入一个用来辨识数据来源的名称。

5.从标示为2的下拉式清单方块中，选取数据来源所使用的数据库驱动程序。

如果您没有看到想要的数据库驱动程序，您可能需要替您的数据来源安装ODBC驱动程序或数据来源驱动程序。

6.使用鼠标按一下「连接」按钮来开启Multi_dimensionalConnection对话框。

由于我们要连接至数据库服务器，因此请如图表3所示，选取选项钮○OLAPServer、键入供服务器的名称、然后按一下Next按钮。

图表3

7.从图表4中Database清单方块选取您所要连结的数据库，然后按一下Finish按钮。

图表4

8.如果您不想要每次使用数据来源时，都要输入登入名称与密码，请选取复选框□将我的使用者代号及密码储存在数据来源定义中（S）。

9.确认您的「建立新数据来源」对话框类似图表5所示后，按下「确定」按钮。

图表5

10.至此您已完成建立所需的OLAPCube，而此一OLAPCube也会类似图表6所示显示在清单方块中，请按下「确定」按钮。

图表6

11.接下来会出现图表7所示的画面，询问您要将数据透视表放在哪里，并允许您进行相关设定。

完成各项设定后，请按一下「完成」按钮。

图表7

12.现在您的Excel画面应如图表8所示，您必须决定数据透视表组织数据的方式，以便制作出数据来源为OLAPCube的数据透视表（类似图表9所示）。

图表8

图表9

您可以将OLAP数据透视表和枢纽分析图储存在报表模板中，还可以为OLAP查询建立查询档案。

OLAP查询档的扩展名为.oqy。

在开启.oqy档案时，Excel会显示一个空白的数据透视表，以供您进行版面配置。

建立离线时使用的cube档案

您也可以使用「离线cube精灵」来建立具有OLAPcube中数据子集的档案。

离线cube档案能让您在未连接上网络的情况下使用OLAP数据。

因此，当您不要对其他使用者提供存取原始数据库的权限时，您可以使用这些档案，以便使OLAP数据能在网络或Web上使用。

欲建立离线cube档案，必须拥有支持此特性的OLAPProvider，例如MicrosoftSQLServerOLAPServices。

如果数据透视表或枢纽分析图的资料来源是服务器上的OLAP数据库，则可以将来源数据复制到本机磁盘个别的档案中，此档案就称为离线cube档案（.cub）。

透过使用该档案，使用者可以在网络中断连接时继续变更报表，或在OLAP服务器无法使用时继续进行动作。

利用此档案，也可以使OLAP数据库中的数据在网络分享目录中得以使用，从而让其它使用者可以透过该档案建立报表。

离线cube档案的另外一个好处是，如果需要经常变更报表，则使用离线cube档案可以加快变更的速度（尤其是当OLAP服务器的网络联机十分缓慢时）。

但是，通常OLAP数据库都十分巨大，因此要慎重选择离线cube档案中的数据。

第一次储存大型离线cube档案时通常会需要较长时间。

是否可以建立和使用离线cube档案取决于所使用的OLAPProvider是否支持该特性。

在Excel对MicrosoftSQLServerOLAPServices数据库的支持中包含了支持建立离线cube档案，但对于其它OLAPProvider则可能不支持该特性。

如果所使用的OLAPProvider不允许使用者建立离线cube档案，则「数据透视表」工具列之「数据透视表」菜单中的「主从架构设定」指令将在根据其OLAP数据库所建立的报表中无法使用。

欲替来源数据为OLAP数据库的报表建立离线cube档案，请依下列步骤进行：

1.请注意，由于如果要执行「离线cube精灵」，首先必须先建立来源数据为OLAP数据库的数据透视表或枢纽分析图，然后透过报表建立离线cube档案。

因此现在请您先自行依前一小节所述的方式去建立一个来源数据为OLAP数据库的数据透视表或枢纽分析图。

2.请使用鼠标按一下要替其建立离线cube档案的数据透视表。

对于枢纽分析图，请使用鼠标按一下相关的数据透视表。

3.请如图表10所示，从「数据透视表」工具列的按一下「数据透视表」菜单中的选取「主从架构设定」指令。

图表10

图表11

4.当您见到图表11所示的「主从架构设定」对话框时，请执行下列操作之一：

Ø

如果以前没有替报表建立过离线cube档案，则请按一下「建立本机数据文件」按钮。

如果报表已存在离线cube档案，则请选取选项钮○本机数据文件，然后按一下「编辑本机数据文件」按钮。

此处我们假设您尚未替报表建立过离线cube档案，因此请按一下「建立本机数据文件」按钮。

此举将开启图表12所示的「离线Cube精灵」，请按一下Next按钮。

图表12

图表13

5.如图表13所示，「离线Cube精灵」显示了服务器cube中所有可用的维度与层次来让您选择。

就选择维度而言，您可以使用鼠标按一下维度旁的＋，以便按层次顺序从最高层到最低阶层检视层次。

如果不希望在档案中包含某个维度，则可清除该维度左侧的复选框。

选择了维度之后，必须选择维度中的层次（如图表14所示）。

在每个维度中，可以透过选择或清除层次所对应到的多重标记来指定要包含于档案中的明细数据层次。

包含的维度和层次越多，离线cube档案的规模也会越大，尤其当包含了较低层次时更是如此，因为该层次的明细数据量可能大大超过较高层次的数据量。

可以忽略较低层次，但不能跳至维度中的层次。

例如，如果「地理」维度中包含「国家或地区」、「地区」和「县/市」层次，且已包含了「县/市」层次，则不能忽略「地区」或「国家或地区」层次。

在来源数据为离线cube档案的报表中，如果需要，可以只强调「县/市」层次而隐藏「地区」层次的数据。

选取维度与层次之后，请按一下Next按钮。

图表14

图表15

6.如图表15所示，您现在必须从每个维度的最更多别中选择所需的数据项。

按一下维度旁的＋可检视维度中最高层次的所有项目。

例如，如果「读者居住地」维度的最高层次为「读者居住城市」，则会看到「台北市」、「台北县」、「高雄市」﹒﹒﹒等。

如果要在档案中忽略某一项，则请清除该数据项左侧的复选框。

图表16

7.如图表16所示，请指定离线cube档案的存放位置与文件名称。

于MicrosoftExcel中，离线cube档案的预设扩展名为.cub。

当开启数据来源为离线cube档案的报表时，Excel会认为离线cube档案系位于此步骤中所指定的存放位置。

如果离线cube档案不位于该位置，Excel会启动使用者浏览来当助您寻找该档案。

储存离线cube档案可能需要较长时间，而且在存盘期间不能于Excel中进行其它工作。

如果您想要放弃存盘，请按一下CreateCubeProgress对话框中的Stop按钮（如图表17所示）。

图表17

结语

经过本文的说明，相信您已非常了解如何整合使用Excel2000与MicrosoftSQLServerOLAPServices，并利用此特性来大幅强化企业的决策支持分析能力。

如果您希望对MicrosoftSQLServerOLAPServices，请参阅相关书籍、期刊、白皮书、与网站，我们下次再见。