译者说明：欢迎访问我的Blog: http://blog.csdn.net/daidaoke2001/
译文中的错误或不当之处望不吝指出，这也是我坚持翻译工作的最大动力。
我的Email：[email protected]
如需转载，请事先通知。
第四章 透 视 组 件
最近几年，OLAP和数据仓库技术迅速普及和实用化。OLAP已经存在很长时间了，但直到最近它才被大规模应用于商业领域中。今天，您几乎找不到一种不提及OLAP或数据仓库技术的计算机或信息系统的行业杂志。（如果您不了解OLAP，请参考“OLAP简要概述”一节）
从许多方面来说，OLAP的普及都不奇怪。在最近40年中，各公司已经变得相当善于获取大量的交易信息，和研究其中的数据，以便能够使他们的核心商业系统正常运行，并开发出新产品。一旦获取了这些信息，它就成为了一个可以立刻被使用的有价值资源――许多商业人员都需要浏览各种报告，以帮助他们对市场活动，销售业绩，以及生产过程等进行决策。但是对于这些公司中的商业决策者来说，不幸的是，这些资源总是被严密的锁在名为中央IT部门的封闭仓库中。公司中的IT雇员们整天忙碌于保证公司核心商业系统的正常运行，，通常都没有时间来生成那些无数人渴望看到的报告，而且对于这些报告的需求也常常是模糊和不能明确定义的。即使IT部门提供了这些报告，它们也常常是很不灵活的。实际上，即使是作一个很简单的改动的要求，例如，只是需要根据另一个字段来进行分组，从而可能需要IT部门生成一个新的报表――很不幸，也会是一个痛苦而漫长的过程。
OLAP和数据仓库技术承诺能在一定程度上解决这些问题，这些技术让IT组织关注那些他们最擅长的事情，而为那些需要查询数据的人提供易用而强大的工具，使他们能够自己生成任何一段所需要的数据。IT组织将致力于提取，合并，以及清理数据；设计一个符合商业思维模式的cube结构；并开发将每日交易数据装载到OLAP服务器中的批处理程序。而用户可以使用“交叉分析”功能来查找他们特定问题的答案――例如，“在我的地区里的每个州中，平均每人的销售额是多少？”或者，“与去年同期相比，我的产品的销售额增长或减少了多少？”
透视表组件就是这样一种常被归类为“商业智能”工具中的一个客户端工具，这些客户端工具都具有易用和功能强大的特性。那些读过漫画《呆伯特法则》的人可能会认为“商业智能”的提法是自相矛盾的（译者注：作者的意思是说，商业是不可能智能的），但是这个词确实能恰当的描述这些工具所希望提供的功能。存储在OLAP系统中的大量数据在被转换成发布在报表中的信息之前，都是没有用处的，这些报表极可能成为某个制定商业决策的人员的有用信息。您可以使用透视表组件来创建基于web的强大数据分析系统，允许您的用户在web浏览器中排序，过滤并重新对数据进行分组。许多公司都在研究使用web浏览器和企业内部网站点来将信息传递给雇员的简便方法。但是，静态形式的数据，和那些陈旧的打印报表一样表现能力有限。透视表控件不仅提供交互式的数据分析功能，还提供简便的安装和升级功能。易于分布的特性主要归功于微软IE的自动代码下载机制，它能够被用于一个web页面上的所有COM组件。（在第12章中我们将详细讨论如何分布。）
注释
虽然我们一直都在讨论HTML和web页面，但您应该知道，透视表控件不仅能运行在web页面中，也能运行在微软的vb页面，以及第一章中列出的其它控件容器中。不过，本章的大部分内容都只会着眼于如何在web页面上使用透视表控件，因为许多开发者发现用户对此有极大的需求。
本章主要讲述透视表组件概念性的内容，最开始将解释“为什么透视表报表会十分有用”。接下来，您会看到透视表控件能够接纳的各种类型的数据。此外，您也能读到关于透视表组件的关键数据源之一－－OLAP的技术的简要概述。之后，您会学习透视表控件中各元素的术语，并了解它们是如何映射到每种类型数据源的底层元素的。最后，您会学些如何使用透视表控件来为您的解决方案添加分析功能。
本章不会完整的讲述如何在设计环境中交互的创建一个透视表。透视表控件的帮助文件对这个问题有非常全面的介绍；您可以通过点击控件工具箱上的帮助按钮来查看它。而对于将微软Excel2000中的透视表报表发布到透视表控件中的相关信息，可以查看Excel的帮助主题，该主题标题为：“关于在web上显示微软Excel的透视表报表。”
第一章 概 述
透视表组件通过组合Excel中的透视表动态报表功能和外部数据序列（也叫做查询表）功能，从而能够为表格数据源和OLAP数据源提供交互数据分析功能。透视表组件的输出通常被称为“交叉表”，因为它显示了各类别交叉点的总和（合计）。例如，您可以采用在行上显示各年，在列上显示客户性别，这样相交叉的方式来显示按产品系列分组的销售信息。在使用一个表格数据源时，透视表控件除了可以显示合计之外，也可以显示任何合计值的详细组成数据行，还可以使用普通列表来显示整个表格数据。
在我谈论透视表控件时，我通常总是立刻进行演示。因为企图描述它到底是什么样子，总是比简单显示它的运行状态要困难的多。这个控件内部的技术细节非常深奥，但是它的使用方法却十分自然和直观。许多商业分析者可以非常高效的使用这个控件，但是对于它是如何工作的却一点儿都不知道。他们只知道它能帮助他们找到问题的答案。因此，我建议您打开随书光盘上Chap04文件夹下的PivotTableIntro.htm文件，在我描述这个控件的功能的同时使用这个例子。当您打开这个文件后，例子页面会如图4－1所示。
图4－1。一个透视表报表的例子。
这个报表的数据源来自微软SQL Server的OLAP服务的一个例子cube。其中包含了一个虚构的名为Foodmart的杂货连锁店的销售信息。我已经将这个cube导出到随书光盘上的Data\Sales.cub文件中，这样即使您的机器上没有OLAP服务器，您也可以使用它了；不过，这个cube文件当然比基于服务的cube要慢一些。
例子页面在初始化时将透视表控件配置为显示1997和1998年所进行的各种促销活动带来的销售总额的报表。使用这个报表，您可以回答类似这样的问题：“1997年最成功的促销活动是哪次？”或者“哪次促销活动中销售了最多数量的饮料产品”对于第一个问题，右键点击1997年那一列中的任何数字，然后选择“降序排列”命令，或者选择1997年那一列中任何数字，然后点击工具条上的降序按钮排列即可。报表会立刻重新排序，并在第一行显示出最高销售量的促销活动，如图4－2所示。
图4－2。一个经过排序的透视表报表。
位于列表的首位是非促销活动的销量，说明非促销活动比任何一次促销活动都卖出了多的多的产品。“现金换取彩票”的促销活动位于第二位，但是请注意在1998年并没有进行这种促销活动。这个发现可能会使市场主管调查为什么没有在1998年进行这种促销活动的，毕竟，它可是1997年最成功的促销活动。
最常见的分析技巧之一就是查找一段感兴趣的数据的更多细节。在OLAP的术语中，这常被称为“钻取”。实际上，透视表控件使您能够轻松的使用这个技巧。例如，假设一个报表显示了1997年“现金换彩票”促销活动带来的销售额，而您需要了解是否这种促销活动在某个特定的季节特别有效。换句话说，您需要了解在1997年的4个季度中这种促销活动分别带来的销售额。为了显示这个详细情况，可以双击1997年列的列表前，或者点击标签左边的加号。报表会展开，以显示1997年的4个季度，以及促销活动在每个季度各自带来的销售额，如图4－3所示。
图4－3。一个经过钻取的透视表报表。
您可以发现几乎所有的销售额都发生在第一季度，这表明这种促销方式在刚刚推出时非常收欢迎，但是随着时间的流逝人们逐渐不再对它感兴趣了，这可能能够解释为什么在1998年没有进行这种促销活动的原因。
您可以使用页面上的快速透视界面来快速生成数据的不同切面，不过如果是浏览更加复杂的报表，请使用从名为透视表字段列表的浮动窗口（如图4－4所示）中将项目拖到报表中的方法。
图4－4。透视表字段列表。
图4－5。一个复杂的透视表报表。
显然透视表组件对于销售分析工作非常有用，不过您也可以使用它来对横跨多个类别的任何类型的数字数据进行汇总。如果和前一章中介绍的图表组件结合起来，那么它将成为一个强大的分析工具。在第7章中我们会看到一个真实的例子。
附录：英文原文
The PivotTable Component
During the last few years, OLAP and data warehousing technology have exploded in popularity and practicality. OLAP has been around for quite some time, but it did not achieve a critical mass in the business world until recently. Today, you can hardly pick up a computer or information systems trade journal that does not mention OLAP or data warehousing. (For those of you not familiar with OLAP, see "A Brief Overview of OLAP.")
In many ways, the OLAP explosion is not a surprise. During the last four decades, corporations have become extremely adept at capturing large amounts of transactional and research data in order to run their core business systems and develop new products. Once that information has been captured, it's a valuable resource just waiting to be tapped—most businesspeople want to see summaries to help them make decisions about marketing campaigns, sales efforts, production processes, and so on. Unfortunately for the business decision makers in these corporations, this resource often is locked safely in that impenetrable vault known as the centralized IT department. The IT employees have their hands full keeping the core business systems running and usually do not have the time to generate the myriad desired reports, the requests for which are often vaguely phrased and poorly defined. If and when the IT department does produce these reports, the reports tend to be inflexible. In fact, requesting a simple change such as grouping data by a different field might require the IT department to generate a new report—unfortunately, with a painfully slow turnaround.
OLAP and data warehousing promise to alleviate some of these problems by letting IT groups concentrate on what they do best and by putting simple yet powerful tools in the hands of those with questions about the data, enabling them to generate any slice of data needed. The IT group can spend its time extracting, consolidating, and cleaning the data; designing a cube structure that matches the business's mental model; and developing batch programs to load daily transactional data into the OLAP servers. Users can "slice and dice" to find answers to their particular questions—for instance, "How much did we sell per capita for each state in my region?" or, "How much have the sales of my product increased or decreased compared to this time last year?"
The PivotTable component is one of those simple yet powerful client tools often categorized as "business intelligence" tools. While those who read the comic strip Dilbert on a regular basis might consider "business intelligence" an oxymoron, it's a good description of what these tools aim to deliver. The mass of data stored in an OLAP system is useless until it is translated into information presented in a report, which hopefully becomes useful knowledge to someone making a business decision. You can use the PivotTable component to build powerful, web-based data analysis systems that allow your users to sort, filter, and regroup data within the web browser. Many corporations are discovering that web browsers and intranet sites provide an easy way to convey information to employees. However, static representations of data are as limiting as those obsolete printed reports. Not only does the PivotTable control deliver interactive data analysis, but it also offers easy installation and upgrading. Much of the ease of deployment can be attributed to Microsoft Internet Explorer's automatic code download mechanism, which is used for all COM components on a web page. (We'll discuss deployment in greater detail in Chapter 12.)
NOTE
Despite all this talk of HTML and web pages, you should realize that the PivotTable control runs in Microsoft Visual Basic forms and the other control containers listed in Chapter 1 as well as it runs in web pages. However, most of the discussions in this chapter focus on using the PivotTable control on a web page simply because many developers find this immensely appealing.
This chapter will discuss the conceptual details of the PivotTable component, starting with an explanation of why a PivotTable report is useful. Next, you will see the various types of data that the PivotTable control can consume. Plus, you'll get a brief overview of OLAP technology, one of the key data sources for the PivotTable component. After that, you will learn about the terminology of the elements in the PivotTable control and see how they map to the underlying elements in each type of data source. Finally, you'll discover how to use the PivotTable control to add analysis features to your solution.
This chapter will not explicitly cover creating a PivotTable interactively in a designer. The PivotTable control's help file covers this topic quite thoroughly; you can view it by clicking the control's Help toolbar button. For information on publishing Microsoft Excel 2000 PivotTable reports to the PivotTable control, see the Excel help topic entitled, "About displaying Microsoft Excel PivotTable reports on the Web."
Combining the PivotTable dynamic report and external data range (which is also known as query table) features in Excel, the PivotTable component provides interactive data analysis of both tabular and OLAP data sources. The output of the control is commonly referred to as a cross tabulation because it shows summary (aggregated) values for an intersection of categories. For example, you can display sales information grouped by product line within years down the rows, intersected with customer gender across the columns. When using a tabular data source, the PivotTable control can also show detail data rows for any aggregate value in addition to the summarized aggregates, or it can simply display all the tabular data in a flat list.
Whenever I talk about the PivotTable control, I usually jump right into a demonstration. Trying to explain what it does is infinitely harder than simply showing it in action. The technology behind the control is incredibly abstract, but its use is actually quite natural and intuitive. Many business analysts can use the control quite effectively but cannot describe what it does with any degree of accuracy. They know only that it can help them obtain answers to their questions. For that reason, I encourage you to open the file PivotTableIntro.htm from the Chap04 folder on the companion CD and experiment with it as I describe what this control can do. When opened, the sample page looks like Figure 4-1.
Figure 4-1. A sample PivotTable report.
The data source for this report is a sample cube that comes with Microsoft SQL Server OLAP Services. It contains sales information for a fictitious grocery chain named Foodmart. I have exported this cube to the Data\Sales.cub file on your companion CD so that you can use it without needing an OLAP server on your machine; however, this cube file is naturally slower than a server-based cube.
The sample page initially configures the PivotTable control to display a report of the sales amounts attributed to various promotions offered in 1997 and 1998. Using this report, you can answer questions such as, "What was the most successful promotion in 1997?" or, "Which promotions helped to sell the most product in the Drink product family?" To answer the first question, right-click any number in the 1997 column and choose the Sort Descending command, or select any number in the 1997 column and click the Sort Descending toolbar button. The report immediately re-sorts to show the promotions with the highest sales first, as shown in Figure 4-2.
Figure 4-2. A sorted PivotTable report.
The No Promotion item is first in the list, meaning that more products are bought in response to no promotion than any particular promotion. The Cash Register Lottery promotion is next, but notice that it was not run in 1998. This discovery might lead a marketing manager to investigate why it was not continued, since it was the most successful promotion in 1997.
One of the most typical analysis techniques is to ask for more detail about an interesting piece of data. In OLAP terminology, this is often called drilling down. In fact, the PivotTable control lets you easily perform this technique. For example, suppose that a report displays the sales attributed to the Cash Register Lottery promotion in 1997 and you want to know whether that promotion was more effective during a particular season. In other words, you want to know how the sales attributed to that promotion break down into the four quarters of 1997. To show the detail, double-click the 1997 column label or click the plus sign (+) to the left of the label. The report expands to show the four quarters of 1997 and the sales amounts attributed to each, as Figure 4-3 illustrates.
Figure 4-3. A drilled-down PivotTable report.
You can see that almost all the sales occurred in the first quarter, indicating that the promotion was popular when first introduced but waned over time, probably explaining why it was not run in 1998.
You can use the Quick Pivot interface on the page to quickly generate different cuts of the data, but to view more complex reports, drag items from the floating window called the PivotTable Field List (shown in Figure 4-4) to the report.
The field list displays all the totals and fields available in the data source. You can use this list to nest fields within each other, add more totals to the report, or put more fields in the filter area to restrict the data shown. For example, drag the Store field and drop it to the right of the list of promotion names to see how the promotion fared by country, state, and city. Next, drag the Sales Count total to the center of the report (where all the numbers are) to see the quantity sold in addition to the dollar amount. Finally, drag the Gender field to the right of the Product field at the top of the report. Once you have dropped it, click the small drop-down button at the right of the field label, choose "M" to show only the sales attributed to men, and click the OK button. The final report should look like Figure 4-5.
Figure 4-4. The PivotTable Field List.
Figure 4-5. A complex PivotTable report.
The PivotTable component is obviously useful for sales analysis, but you can also use it to summarize any type of numeric data across many categories. When combined with the Chart component described in the previous chapter, it can be quite a powerful analysis tool. We will see a real-world example of this in Chapter 7.
本文地址：http://com.8s8s.com/it/it44115.htm